Lagerbestands-Vergleich

Lagerbestände abgleichen, ob Produkte in beiden Lägern gelistet sind

Ein Unternehmen hat zwei Läger, wo grundsätzlich die gleichen Artikel vorrätig gehalten werden. In der Administration der Läger wird wöchentlich ausgewertet, ob dieses oder jenes Produkt umgelagert werden kann. Dazu erstellt jedes Lager am Beginn der Woche eine Bestandsliste als Excel-Datei. Diese beiden Files dienen als Grundlage der jeweiligen Entscheidung.

Grundsätzlich gilt, dass in jeder Datei alle Produkte, die ständig im entsprechenden Lager vorrätig gehalten werden, in der Liste enthalten sind. Die überwiegende Zahl der Verkaufsgüter ist in beiden Lägern im Bestand gelistet (ob nun vorrätig oder nicht), wenige sind nur in einem Lager auf Dauer bevorratet. Der Bestand 0 bedeutet im Normalfall, dass das Produkt weiterhin zum Portfolio des entsprechenden Lagers gehört.

Vorarbeiten

Bei einer einmaligen Aktion wäre es das einfachste, die erforderlichen Daten einfach per Hand zu kopieren und dann entsprechend auszuwerten. Da das Ganze aber jede Woche geschehen soll, ist ein gewisses Maß an Automatisierung sinnvoll und hilfreich. Das könnte per VBA erledigt werden, aber seit der Version Excel 2010 gibt es eine Möglichkeit, welche ohne Programmierung auskommt. Es ist eine Kombination zwischen einer klassischen PivotTable (für die Auswertung) und dem neueren Add-In Power Query (für den Daten-“Import“). Voraussetzungen dafür sind mindestens Windows 7 oder Windows Server 2008 R2 sowie Excel 2010 (Professional Plus mit Software Assurance) oder das Standard-Excel ab Version 2013. Das Add-In können Sie sich bei Microsoft herunterladen, eine Installationsanleitung ist dort auch zu finden. – Falls noch nicht geschehen, installieren Sie nun das Add-In Power Query. Und sollten Sie die beiden Lagerbestands-Files noch nicht heruntergeladen und/oder entpackt haben, dann tun Sie das jetzt.

Legen Sie nun eine neue, leere Excel-Datei an. Ein Klick auf den Menüpunkt Power Query und im Menüband wählen Sie das zweite Symbol: Aus Datei. Im Aufklappmenü ist die oberste Möglichkeit die richtige. Suchen und öffnen Sie nun die Datei Lager_1.xlsx. Markieren Sie das einzige angezeigte Tabellenblatt Lager 1 und danach ein Klick auf die Schaltfläche Laden. Umgehend wird ein neues Tabellenblatt angelegt und darin eine Intelligente Tabelle erstellt. Das Blatt benennen Sie um in Lager 1, die Tabelle bekommt von Ihnen den Namen qry_Lager_1. – Sie erkennen, dass im rechten Teil des Excel-Fensters statt der Zellen der Bereich Arbeitemappenabfragen zu sehen ist. Dort werden alle durchgeführten Queries (Abfragen) aufgeführt.

Führen Sie nun in gleicher Weise die Abfrage für die Datei Lager_2.xlsx durch und benennen Die das Blatt in Lager 2, die Tabelle in qry_Lager_2. Sollten Sie mit dem umbenennen der Abfrage-Tabellen Probleme haben, dann bedenken Sie, dass eine Zelle der Liste die aktive sein muss und im Kontextmenü Tabellentools der Menüpunkt Entwurf aktiviert sein sollte. Dann können Sie ganz links in der Gruppe Eigenschaften den Tabellennamen wunschgemäß anpassen.

▲ nach oben …

Intermezzo

So weit, so gut. Es stellt sich vielleicht für Sie die Frage, wo der Unterschied zu den ursprünglichen Daten ist; außer der anderen Farbgebung, welche durch die Intelligente Tabelle bewirkt wird. Und der etwas anderen Umgebung, bedingt durch die Arbeitsmappenabfragen, dem Regie-Zentrum. Der entscheidende Unterschied ist folgender: Jeder Schritt der Abfrage, der Query wird aufgezeichnet und das gilt auch für die künftigen Schritte. Und bei jeder Aktualisierung werden die gleichen Schritte, die gleichen Abläufe durchgeführt, wobei zwar auf die Source-Datei mit dem identischen Namen und Pfad zugegriffen wird aber natürlich aktualisierte Werte verwendet werden.

Klicken Sie im Regie-Zentrum einmal doppelt auf den Eintrag für Lager 1. Sofort öffnet sich ein neues Fenster mit dem Abfrage-Editor. Die Daten stellen sich dort auch nicht wirklich anders dar, aber im Regie-Bereich erkennen Sie die einzelnen aufgezeichneten Schritte. Das Ziel in diesem Schritt ist es nun, in irgendeiner Form aber per Query die Nummer des Lagers dort einzutragen. Also eine 1 für Lager 1 bzw. in der anderen Tabelle eine 2. Ein direkter Weg wäre ja zu leicht, 😉 darum ein kleiner Umweg: Wählen Sie erst einmal das Menü Spalte hinzufügen und in der Gruppe Allgemein, klicken Sie auf den DropDown-Pfeil beim Eintrag Indexspalte hinzufügen. Hier bitte auf Benutzerdefiniert… und geben Sie als Startindex die Nummer des Lagers ein, also die 1. Bei der Schrittweite verwenden Sie die 0, denn es soll ja nicht hoch gezählt werden. OK und rasch wird die Spalte Index angelegt und gefüllt. Nun nur noch die Überschrift (Index) in „Lager“ ändern und Sie sind praktisch fertig. Bevor Sie das Fenster schließen, schauen Sie sich bei den Angewendeten Schritten gerne noch einmal an, was alles protokolliert worden ist. – Nun noch ein Wechsel zum Menüpunkt Start und dort den ersten Punkt Schließen & laden aufrufen. Das Fenster wird geschlossen und Sie erkennen die Änderung auch in der „normalen“ Excel-Tabelle.

Gehen Sie anschließend mit der Tabelle qry_Lager_2 genauso vor, nur dass Sie naturgemäß beim Index den Startwert 2 verwenden. Dazu brauchen Sie noch nicht einmal das Arbeitsblatt zu wechseln, denn die Hauptarbeit spielt sich ja im Abfrageeditor ab. Aber natürlich müssen Sie die Arbeitsmappenabfrage Lager 2 öffnen, klar…

Und was haben Sie nun davon? Zwei Tabellen, die eindeutig durch die Lagernummer gekennzeichnet sind. Und -das ist wichtig- die bei geänderten Quelldaten (jedoch mit gleichem Pfad und Filenamen) automatisch den Gegebenheiten angepasst werden. Im nächsten Schritt werden Sie eine dritte Abfrage machen, wo diese beiden Queries zusammengefasst werden, um danach einer PivotTable als Datenbasis zu dienen.

Wir bleiben in Power Query. In der dritten Gruppe (Kombinieren) wählen Sie das Symbol Anfügen. Im oberen Auswahl-Feld verwenden Sie Lager 1 als Tabelle aus, wo weitere Daten angefügt werden sollen. Im unteren Feld ist es dann naturgemäß Lager 2, die an die oben genannte Tabelle angefügt werden soll. Nach einem OK öffnet sich wiederum der Abfrage-Editor. Was Sie jetzt noch machen könnten: Markieren Sie die Spalten Preis und Wert und in der Menü-Gruppe Beliebige Spalte wählen Sie als Datentyp die Währung. Sie sehen zwar im Editor keine Änderung, aber das ist an dieser Stelle auch nicht relevant, Sie sollten nur solch eine Möglichkeit kennen. Nun noch Menü Start und dort Schließen & laden. Diese Abfrage hat den Namen Append1 bekommen und wird die Grundlage für das weitere Handeln sein. Sie können den Namen übrigens nach eigenem 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 einmal sollten Sie prüfen, ob alle Daten korrekt übernommen worden sind. Im Dashboard (rechter Rand) sind ja die drei Abfragen aufgeführt. Und die Summe der geladenen Zeilen der ersten beiden Abfragen muss mit der angegebenen Zahl in der Abfrage Appen1 übereinstimmen. Zusätzlich noch einmal die Kontrolle, ob in der Tabelle die Lagerbezeichnungen auch stimmen, kann nicht schaden. 😉 Danach können Sie dieses Regie-Zentrum schließen, indem Sie auf das X im Titel-Bereich klicken. Das Dashboard nimmt sonst einfach zu viel Platz weg.

Erforderlichenfalls klicken Sie in den Tabellenbereich, dann Tabellentools | Entwurf und in der Gruppe Tools die Auswahl Mit PivotTable zusammenfassen. Natürlich können Sie auch den Weg über Einfügen und dann PivotTable wählen, das Ergebnis ist identisch. Im folgenden Fenster wählen Sie die Möglichkeit, den PivotTable-Bericht im gleichen Arbeitsblatt zu platzieren. Und auch wenn dort Quelle steht (Excel 2013), es ist natürlich das Ziel gemeint; hier: H7:

Die Pivot-Auswertung in das aktuelle Blatt integrieren

Die Pivot-Auswertung in das aktuelle Blatt integrieren

Welche Felder brauchen Sie? Auf jeden Fall die ProduktNr., klar. Wenn Sie dort nur das Häkchen setzen, dann erkennt Excel automatisch die Zahlen und wird daraus im Bereich Werte die Summe bilden. Das ist natürlich falsch, also ziehen Sie entweder das Feld direkt in den Bereich Zeilen oder vom Bereich Werte dort hin. Idealerweise ändern Sie in H7 auch gleich die Überschrift in ProduktNr. oder ähnlich. Dass jetzt alle vorkommenden Produktnummern in Spalte H aufgeführt sind ist von Excel so vorgesehen.

Eigentlich reicht das für den gewollten Zweck schon aus, denn es soll ja (nur) verglichen werden, welche Produkte in einem Lager gelistet sind, im anderen Lager aber nicht. Dabei spielt der Warenbestand bei dieser Fragestellung keine Rolle. Aber der besseren Klarheit wegen soll auch noch die Produktbezeichnung mit angezeigt werden. Also ziehen Sie das Feld Produkt auch in die Zeilen. Das Ganze sieht nun so aus:

Auch der Produktname wurde in die PT übernommen

Auch der Produktname wurde in die PT übernommen

So wirklich übersichtlich ist das ja nun wirklich nicht. Mit einigen wenigen Schritten wird sich das aber ändern. Erst einmal im Menü PivotTable-Tools | Entwurf in der Gruppe Layout das Symbol Berichtslayout und dort die Auswahl In Tabellenformat anzeigen wählen. Anschließend in der gleichen Gruppe Teilergebnisse und hier wiederum Teilergebnisse nicht anzeigen. Und da auch kein Gesamtergebnis erforderlich ist, deaktivieren Sie auch dieses.

Im vorerst letzten Schritt begrenzen Sie die Anzeige in dieser Spalte auf die Produkte des Lager 1, denn es soll ja aufgezeigt werden, welche Produkte zwar in Lager 1 aber nicht in Lager 2 gelistet sind. Ziehen Sie dazu das Feld Lager in den Bereich Filter. Ihr DashBoard sieht nun so aus:

Derzeitige Sicht auf das Regie-Zentrum (mit gesetztem Filter)

Derzeitige Sicht auf das Regie-Zentrum (mit gesetztem Filter)

Sofort wird in H5:I5 ein Eintrag vorgenommen. Klicken Sie in I5 auf die Schaltfläche und wählen Sie als Filter den Wert 1 aus. Überzeugen Sie sich gerne davon, dass nun nur noch Produkte des Lager 1 angezeigt werden. Sie erkennen es schon an der Menge der Einträge. – Es bleibt jedoch die Frage, wie aus den vorhandenen Daten die Fragestellung beantwortet  werden soll. Dazu bedarf es eines kleinen Tricks. In einer Hilfsspalte wird berechnet, wie oft die Produktnummer in der gesamten Tabelle qry_Zusammenfassung in Spalte A vorkommt.

Obwohl Spalte G noch keine Daten enthält, sollten Sie der Optik wegen eine zusätzliche Spalte einfügen, damit die Pivot-Auswertung anschließend in Spalte I beginnt. Klicken Sie nun in G1 und tragen Sie eine mehr oder weniger sinnvolle Überschrift ein. Beispielsweise L 1|2. In G2 schreiben Sie nun diese Formel:
=ZÄHLENWENN([ProduktNr.]; A2)
Dank der Intelligenten Tabelle wird die Formel sofort nach unten kopiert und berechnet. Meistens steht eine 2 als Ergebnis dort, weil das Produkt in beiden Lägern gelistet ist. Aber beispielsweise die Produktnummer 14 bildet da schon eine Ausnahme, hier ist eine 1 berechnet worden, weil dieser Artikel nur in Lager 1 gelistet ist.

Die logische Folgerung: Es bedarf in der Pivot-Auswertung eines zweiten Filters, der nur die Artikel aufzeigt, die in Spalte G eine 1 stehen haben. Aber warum ist die neu angelegte Spalte L 1|2 nicht in der Aufstellung der Felder? Eigentlich logisch, denn eine PivotTable sollte einen statischen Zustand darstellen, der sich nur nach Aufforderung aktualisiert. Und das ist auch das Stichwort. In den PivotTable-Tools | Analysieren ist bei der Gruppe Daten die Schaltfläche Aktualisieren. Dort wählen Sie den entsprechenden Unterpunkt und schon wird auch dieses Feld angezeigt. – Ziehen Sie es in den Bereich Filter und setzen Sie danach auch hier die 1 als zu filternden Wert, da alle Produkte angezeigt werden sollen, die nur in 1 Lager vorhanden sind. Und da ja vorher auf das Lager 1 gefiltert worden ist, bleiben auch nur noch diese Produkte über. Jetzt noch in I2 eine kleine erklärende Überschrift und das Wesentliche ist geschafft:

Die fertige Liste für Lager 1

Die fertige Liste für Lager 1

Für Lager 2 ist praktisch das gleiche Vorgehen angesagt. Hier noch einmal in Stichpunkten eine To-Do-Liste:

  • Klick in die Tabelle qry_Zusammenfassung
  • PivotTable erstellen, als Ziel das aktuelle Blatt Zelle L7
  • Das (automatisch eingeblendete) Regie-Zentrum Arbeitsmappenabfragen ausblenden, damit mehr Platz ist
  • Das Feld ProduktNr. in den Bereich Zeilen ziehen
  • Das Feld Produkt ebenfalls in den Bereich Zeilen ziehen, unterhalb der ProduktNr.
  • Menü Entwurf | Gruppe Layout | Berichtslayout | Tabellenformat
  • Gruppe Layout | Teilergebnisse | Nicht anzeigen
  • Gruppe Layout | Gesamtergebnisse | Zeilen und Spalten deaktiviert
  • Feld Lager in den Bereich Filter ziehen, dann Lager 2 aktivieren
  • Feld L 1|2 in den Bereich Filter ziehen, dann die 1 (!) aktivieren
  • Überschrift von I2:J2 nach L2 kopieren und inhaltlich anpassen

Fertig. Damit ist die Aufgabe gelöst. Aus Gründen der Ästhetik sollten Sie vielleicht noch das zuletzt erstellte Tabellenblatt in Pivot-Auswertung umbenennen und gerne auch das nicht mehr benötigte Blatt Tabelle1 löschen. Diesen Stand der Dinge können Sie in dieser Datei mit Ihrem Werk vergleichen.

Experimentieren Sie gerne noch etwas herum. Insbesondere sollten Sie die wöchentlichen Bestandsdaten verändern und dann die neuen Ergebnisse erkennen. Beachten Sie dabei, dass Sie die bisherigen Quelldaten umbenennen und die neuen Daten mit den vorgegebenen Filenamen versehen müssen. Und danach natürlich in PowerQuery als auch PivotTable aktualisieren. Und lassen Sie sich nicht daran hindern, auch andere Auswertungsmöglichkeiten zu erkunden.

▲ nach oben …

Dieser Beitrag wurde unter Musterlösungen, Ohne Makro/VBA, Pivot, Power Query, Tabelle und Zelle abgelegt und mit , , , , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.