Ein Unternehmen hat zwei Läger, wo grund­sät­zlich die gle­ichen Artikel vor­rätig gehal­ten wer­den. In der Admin­is­tra­tion der Läger wird wöchentlich aus­gew­ertet, ob dieses oder jenes Pro­dukt umge­lagert wer­den kann. Dazu erstellt jedes Lager am Beginn der Woche eine Bestand­sliste als Excel-Datei. Diese bei­den Files dienen als Grund­lage der jew­eili­gen Entschei­dung.

Grund­sät­zlich gilt, dass in jed­er Datei alle Pro­duk­te, die ständig im entsprechen­den Lager vor­rätig gehal­ten wer­den, in der Liste enthal­ten sind. Die über­wiegende Zahl der Verkauf­s­güter ist in bei­den Lägern im Bestand gelis­tet (ob nun vor­rätig oder nicht), wenige sind nur in einem Lager auf Dauer bevor­ratet. Der Bestand 0 bedeutet im Nor­mal­fall, dass das Pro­dukt weit­er­hin zum Port­fo­lio des entsprechen­den Lagers gehört.

Vorarbeiten

Bei ein­er ein­ma­li­gen Aktion wäre es das ein­fach­ste, die erforder­lichen Dat­en ein­fach per Hand zu kopieren und dann entsprechend auszuw­erten. Da das Ganze aber jede Woche geschehen soll, ist ein gewiss­es Maß an Automa­tisierung sin­nvoll und hil­fre­ich. Das kön­nte per VBA erledigt wer­den, aber seit der Ver­sion Excel 2010 gibt es eine Möglichkeit, welche ohne Pro­gram­mierung auskommt. Es ist eine Kom­bi­na­tion zwis­chen ein­er klas­sis­chen Piv­ot­Table (für die Auswer­tung) und dem neueren Add-In Pow­er Query (für den Daten-„Import”). Voraus­set­zun­gen dafür sind min­destens Win­dows 7 oder Win­dows Serv­er 2008 R2 sowie Excel 2010 (Pro­fes­sion­al Plus mit Soft­ware Assur­ance) oder das Stan­dard-Excel ab Ver­sion 2013. Das Add-In kön­nen Sie sich bei Microsoft herun­ter­laden, eine Instal­la­tion­san­leitung ist dort auch zu find­en. – Falls noch nicht geschehen, instal­lieren Sie nun das Add-In Pow­er Query. Und soll­ten Sie die bei­den Lagerbe­stands-Files noch nicht herun­terge­laden und/oder ent­packt haben, dann tun Sie das jet­zt.

Leg­en Sie nun eine neue, leere Excel-Datei an. Ein Klick auf den Menüpunkt Pow­er Query und im Menüband wählen Sie das zweite Sym­bol: Aus Datei. Im Aufk­lapp­menü ist die ober­ste Möglichkeit die richtige. Suchen und öff­nen Sie nun die Datei Lager_1.xlsx. Markieren Sie das einzige angezeigte Tabel­len­blatt Lager 1 und danach ein Klick auf die Schalt­fläche Laden. Umge­hend wird ein neues Tabel­len­blatt angelegt und darin eine Intel­li­gente Tabelle erstellt. Das Blatt benen­nen Sie um in Lager 1, die Tabelle bekommt von Ihnen den Namen qry_Lager_1. – Sie erken­nen, dass im recht­en Teil des Excel-Fen­sters statt der Zellen der Bere­ich Arbeit­emap­pen­abfra­gen zu sehen ist. Dort wer­den alle durchge­führten Queries (Abfra­gen) aufge­führt.

Führen Sie nun in gle­ich­er Weise die Abfrage für die Datei Lager_2.xlsx durch und benen­nen Die das Blatt in Lager 2, die Tabelle in qry_Lager_2. Soll­ten Sie mit dem umbe­nen­nen der Abfrage-Tabellen Prob­leme haben, dann bedenken Sie, dass eine Zelle der Liste die aktive sein muss und im Kon­textmenü Tabel­len­tools der Menüpunkt Entwurf aktiviert sein sollte. Dann kön­nen Sie ganz links in der Gruppe Eigen­schaften den Tabel­len­na­men wun­schgemäß anpassen.

▲ nach oben …

Intermezzo

So weit, so gut. Es stellt sich vielle­icht für Sie die Frage, wo der Unter­schied zu den ursprünglichen Dat­en ist; außer der anderen Far­bge­bung, welche durch die Intel­li­gente Tabelle bewirkt wird. Und der etwas anderen Umge­bung, bed­ingt durch die Arbeitsmap­pen­abfra­gen, dem Regie-Zen­trum. Der entschei­dende Unter­schied ist fol­gen­der: Jed­er Schritt der Abfrage, der Query wird aufgeze­ich­net und das gilt auch für die kün­fti­gen Schritte. Und bei jed­er Aktu­al­isierung wer­den die gle­ichen Schritte, die gle­ichen Abläufe durchge­führt, wobei zwar auf die Source-Datei mit dem iden­tis­chen Namen und Pfad zuge­grif­f­en wird aber natür­lich aktu­al­isierte Werte ver­wen­det wer­den.

Klick­en Sie im Regie-Zen­trum ein­mal dop­pelt auf den Ein­trag für Lager 1. Sofort öffnet sich ein neues Fen­ster mit dem Abfrage-Edi­tor. Die Dat­en stellen sich dort auch nicht wirk­lich anders dar, aber im Regie-Bere­ich erken­nen Sie die einzel­nen aufgeze­ich­neten Schritte. Das Ziel in diesem Schritt ist es nun, in irgen­dein­er Form aber per Query die Num­mer des Lagers dort einzu­tra­gen. Also eine 1 für Lager 1 bzw. in der anderen Tabelle eine 2. Ein direk­ter Weg wäre ja zu leicht, 😉 darum ein klein­er Umweg: Wählen Sie erst ein­mal das Menü Spalte hinzufü­gen und in der Gruppe All­ge­mein, Klick­en Sie auf den Drop­Down-Pfeil beim Ein­trag Indexs­palte hinzufü­gen▼. Hier bitte auf Benutzerdefiniert… und geben Sie als Startin­dex die Num­mer des Lagers ein, also die 1. Bei der Schrit­tweite ver­wen­den Sie die 0, denn es soll ja nicht hoch gezählt wer­den. OK und rasch wird die Spalte Index angelegt und gefüllt. Nun nur noch die Über­schrift (Index) in „Lager” ändern und Sie sind prak­tisch fer­tig. Bevor Sie das Fen­ster schließen, schauen Sie sich bei den Angewen­de­ten Schrit­ten gerne noch ein­mal an, was alles pro­tokol­liert wor­den ist. – Nun noch ein Wech­sel zum Menüpunkt Start und dort den ersten Punkt Schließen & laden aufrufen. Das Fen­ster wird geschlossen und Sie erken­nen die Änderung auch in der „nor­malen” Excel-Tabelle.

Gehen Sie anschließend mit der Tabelle qry_Lager_2 genau­so vor, nur dass Sie naturgemäß beim Index den Startwert 2 ver­wen­den. Dazu brauchen Sie noch nicht ein­mal das Arbeits­blatt zu wech­seln, denn die Haup­tar­beit spielt sich ja im Abfrageed­i­tor ab. Aber natür­lich müssen Sie die Arbeitsmap­pen­abfrage Lager 2 öff­nen, klar…

Und was haben Sie nun davon? Zwei Tabellen, die ein­deutig durch die Lager­num­mer gekennze­ich­net sind. Und ‑das ist wichtig- die bei geän­derten Quell­dat­en (jedoch mit gle­ichem Pfad und File­na­men) automa­tisch den Gegeben­heit­en angepasst wer­den. Im näch­sten Schritt wer­den Sie eine dritte Abfrage machen, wo diese bei­den Queries zusam­menge­fasst wer­den, um danach ein­er Piv­ot­Table als Daten­ba­sis zu dienen.

Wir bleiben in Pow­er Query. In der drit­ten Gruppe (Kom­binieren) wählen Sie das Sym­bol Anfü­gen. Im oberen Auswahl-Feld ver­wen­den Sie Lager 1 als Tabelle aus, wo weit­ere Dat­en ange­fügt wer­den sollen. Im unteren Feld ist es dann naturgemäß Lager 2, die an die oben genan­nte Tabelle ange­fügt wer­den soll. Nach einem OK öffnet sich wiederum der Abfrage-Edi­tor. Was Sie jet­zt noch machen kön­nten: Markieren Sie die Spal­ten Preis und Wert und in der Menü-Gruppe Beliebige Spalte wählen Sie als Daten­typ die Währung. Sie sehen zwar im Edi­tor keine Änderung, aber das ist an dieser Stelle auch nicht rel­e­vant, Sie soll­ten nur solch eine Möglichkeit ken­nen. Nun noch Menü Start und dort Schließen & laden. Diese Abfrage hat den Namen Append1 bekom­men und wird die Grund­lage für das weit­ere Han­deln sein. Sie kön­nen den Namen übri­gens nach eigen­em Geschmack anpassen, hier sollte er aber so bleiben. – Geben Sie bitte der erzeugten Tabelle den Namen qry_Zusammenfassung.

▲ nach oben …

Umsetzung in einer Pivot-Tabelle

Zuerst ein­mal soll­ten Sie prüfen, ob alle Dat­en kor­rekt über­nom­men wor­den sind. Im Dash­board (rechter Rand) sind ja die drei Abfra­gen aufge­führt. Und die Summe der gelade­nen Zeilen der ersten bei­den Abfra­gen muss mit der angegebe­nen Zahl in der Abfrage Appen1 übere­in­stim­men. Zusät­zlich noch ein­mal die Kon­trolle, ob in der Tabelle die Lager­beze­ich­nun­gen auch stim­men, kann nicht schaden. 😉 Danach kön­nen Sie dieses Regie-Zen­trum schließen, indem Sie auf das X im Titel-Bere­ich Klick­en. Das Dash­board nimmt son­st ein­fach zu viel Platz weg.

Erforder­lichen­falls Klick­en Sie in den Tabel­len­bere­ich, dann Tabel­len­tools | Entwurf und in der Gruppe Tools die Auswahl Mit Piv­ot­Table zusam­men­fassen. Natür­lich kön­nen Sie auch den Weg über Ein­fü­gen und dann Piv­ot­Table wählen, das Ergeb­nis ist iden­tisch. Im fol­gen­den Fen­ster wählen Sie die Möglichkeit, den Piv­ot­Table-Bericht im gle­ichen Arbeits­blatt zu platzieren. Und auch wenn dort Quelle ste­ht (Excel 2013), es ist natür­lich das Ziel gemeint; hier: H7:

Die Piv­ot-Auswer­tung in das aktuelle Blatt inte­gri­eren

Welche Felder brauchen Sie? Auf jeden Fall die Pro­duk­t­Nr., klar. Wenn Sie dort nur das Häkchen set­zen, dann erken­nt Excel automa­tisch die Zahlen und wird daraus im Bere­ich Werte die Summe bilden. Das ist natür­lich falsch, also ziehen Sie entwed­er das Feld direkt in den Bere­ich Zeilen oder vom Bere­ich Werte dort hin. Ide­al­er­weise ändern Sie in H7 auch gle­ich die Über­schrift in Pro­duk­t­Nr. oder ähn­lich. Dass jet­zt alle vork­om­menden Pro­duk­t­num­mern in Spalte H aufge­führt sind ist von Excel so vorge­se­hen.

Eigentlich reicht das für den gewoll­ten Zweck schon aus, denn es soll ja (nur) ver­glichen wer­den, welche Pro­duk­te in einem Lager gelis­tet sind, im anderen Lager aber nicht. Dabei spielt der Warenbe­stand bei dieser Fragestel­lung keine Rolle. Aber der besseren Klarheit wegen soll auch noch die Pro­duk­t­beze­ich­nung mit angezeigt wer­den. Also ziehen Sie das Feld Pro­dukt auch in die Zeilen. Das Ganze sieht nun so aus:

Auch der Pro­duk­t­name wurde in die PT über­nom­men

So wirk­lich über­sichtlich ist das ja nun wirk­lich nicht. Mit eini­gen weni­gen Schrit­ten wird sich das aber ändern. Erst ein­mal im Menü Piv­ot­Table-Tools | Entwurf in der Gruppe Lay­out das Sym­bol Bericht­slay­out und dort die Auswahl In Tabel­len­for­mat anzeigen wählen. Anschließend in der gle­ichen Gruppe Tei­l­ergeb­nisse und hier wiederum Tei­l­ergeb­nisse nicht anzeigen. Und da auch kein Gesamtergeb­nis erforder­lich ist, deak­tivieren Sie auch dieses.

Im vor­erst let­zten Schritt begren­zen Sie die Anzeige in dieser Spalte auf die Pro­duk­te des Lager 1, denn es soll ja aufgezeigt wer­den, welche Pro­duk­te zwar in Lager 1 aber nicht in Lager 2 gelis­tet sind. Ziehen Sie dazu das Feld Lager in den Bere­ich Fil­ter. Ihr Dash­Board sieht nun so aus:

Derzeit­ige Sicht auf das Regie-Zen­trum (mit geset­ztem Fil­ter)

Sofort wird in H5:I5 ein Ein­trag vorgenom­men. Klick­en Sie in I5 auf die Schalt­fläche ▼ und wählen Sie als Fil­ter den Wert 1 aus. Überzeu­gen Sie sich gerne davon, dass nun nur noch Pro­duk­te des Lager 1 angezeigt wer­den. Sie erken­nen es schon an der Menge der Ein­träge. – Es bleibt jedoch die Frage, wie aus den vorhan­de­nen Dat­en die Fragestel­lung beant­wortet  wer­den soll. Dazu bedarf es eines kleinen Tricks. In ein­er Hil­f­ss­palte wird berech­net, wie oft die Pro­duk­t­num­mer in der gesamten Tabelle qry_Zusammenfassung in Spalte A vorkommt.

Obwohl Spalte G noch keine Dat­en enthält, soll­ten Sie der Optik wegen eine zusät­zliche Spalte ein­fü­gen, damit die Piv­ot-Auswer­tung anschließend in Spalte I begin­nt. Klick­en Sie nun in G1 und tra­gen Sie eine mehr oder weniger sin­nvolle Über­schrift ein. Beispiel­sweise L 1|2. In G2 schreiben Sie nun diese Formel:
=ZÄHLENWENN([ProduktNr.]; A2)
Dank der Intel­li­gen­ten Tabelle wird die Formel sofort nach unten kopiert und berech­net. Meis­tens ste­ht eine 2 als Ergeb­nis dort, weil das Pro­dukt in bei­den Lägern gelis­tet ist. Aber beispiel­sweise die Pro­duk­t­num­mer 14 bildet da schon eine Aus­nahme, hier ist eine 1 berech­net wor­den, weil dieser Artikel nur in Lager 1 gelis­tet ist.

Die logis­che Fol­gerung: Es bedarf in der Piv­ot-Auswer­tung eines zweit­en Fil­ters, der nur die Artikel aufzeigt, die in Spalte G eine 1 ste­hen haben. Aber warum ist die neu angelegte Spalte L 1|2 nicht in der Auf­stel­lung der Felder? Eigentlich logisch, denn eine Piv­ot­Table sollte einen sta­tis­chen Zus­tand darstellen, der sich nur nach Auf­forderung aktu­al­isiert. Und das ist auch das Stich­wort. In den Piv­ot­Table-Tools | Analysieren ist bei der Gruppe Dat­en die Schalt­fläche Aktu­al­isieren. Dort wählen Sie den entsprechen­den Unter­punkt und schon wird auch dieses Feld angezeigt. – Ziehen Sie es in den Bere­ich Fil­ter und set­zen Sie danach auch hier die 1 als zu fil­tern­den Wert, da alle Pro­duk­te angezeigt wer­den sollen, die nur in 1 Lager vorhan­den sind. Und da ja vorher auf das Lager 1 gefiltert wor­den ist, bleiben auch nur noch diese Pro­duk­te über. Jet­zt noch in I2 eine kleine erk­lärende Über­schrift und das Wesentliche ist geschafft:

Die fer­tige Liste für Lager 1

Für Lager 2 ist prak­tisch das gle­iche Vorge­hen ange­sagt. Hier noch ein­mal in Stich­punk­ten eine To-Do-Liste:

• Klick in die Tabelle qry_Zusammenfassung
• Piv­ot­Table erstellen, als Ziel das aktuelle Blatt Zelle L7
• Das (automa­tisch einge­blendete) Regie-Zen­trum Arbeitsmap­pen­abfra­gen aus­blenden, damit mehr Platz ist
• Das Feld Pro­duk­t­Nr. in den Bere­ich Zeilen ziehen
• Das Feld Pro­dukt eben­falls in den Bere­ich Zeilen ziehen, unter­halb der Pro­duk­t­Nr.
• Menü Entwurf | Gruppe Lay­out | Bericht­slay­out | Tabel­len­for­mat
• Gruppe Lay­out | Tei­l­ergeb­nisse | Nicht anzeigen
• Gruppe Lay­out | Gesamtergeb­nisse | Zeilen und Spal­ten deak­tiviert
• Feld Lager in den Bere­ich Fil­ter ziehen, dann Lager 2 aktivieren
• Feld L 1|2 in den Bere­ich Fil­ter ziehen, dann die 1 (!) aktivieren
• Über­schrift von I2:J2 nach L2 kopieren und inhaltlich anpassen

Fer­tig. Damit ist die Auf­gabe gelöst. Aus Grün­den der Ästhetik soll­ten Sie vielle­icht noch das zulet­zt erstellte Tabel­len­blatt in Piv­ot-Auswer­tung umbe­nen­nen und gerne auch das nicht mehr benötigte Blatt Tabelle1 löschen. Diesen Stand der Dinge kön­nen Sie in dieser Datei mit Ihrem Werk ver­gle­ichen.

Exper­i­men­tieren Sie gerne noch etwas herum. Ins­beson­dere soll­ten Sie die wöchentlichen Bestands­dat­en verän­dern und dann die neuen Ergeb­nisse erken­nen. Beacht­en Sie dabei, dass Sie die bish­eri­gen Quell­dat­en umbe­nen­nen und die neuen Dat­en mit den vorgegebe­nen File­na­men verse­hen müssen. Und danach natür­lich in Pow­er­Query als auch Piv­ot­Table aktu­al­isieren. Und lassen Sie sich nicht daran hin­dern, auch andere Auswer­tungsmöglichkeit­en zu erkun­den.

▲ nach oben …

Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?

Dann würde ich mich über einen Beitrag Ihrer­seits z.B. 4,00 € freuen …