Excel mit Factorio lernen (5.Teil): dynamische Bestandsübersicht [optional]

XKCD Tech Support Cheat Sheet

Factorio kennt kein Geld. Ich habe für die folgenden Berechnungen einen Preis festgelegt, der sich aus den Energiekosten für die Herstellung und die dabei verursachte Umweltverschmutzung berechnet. 

Merke, mit Excel geht (fast) alles!

Electronics INC stellt die Produkte her, lagert sie und liefert sie aus, wenn sie benötigt werden. Die Datei mit den Daten ist hier: Lagerbewegungen und Materialstammdaten. 

Dynamische Bestandsübersicht

Aus diesen Daten wird nun die Bestandsübersicht berechnet. Diese Übersicht wird folgende Informationen enthalten:

1. Aus den Stammdaten Materialnummer, Bezeichnung des Materials, Einzelpreis.
2. Für die drei Buchungsblätter jeweils die Stückzahl je Material und den Wert. 
3. Dann die Stückzahl am Jahresende und den Wert.
4. Die Summe der Werte für den Inventurbestand, die Warenein- und Warenausgänge. 
5. Die Gesamtsumme am Jahresende und den Check auf Vollständigkeit.

Lege eine neue Tabelle für die Bestandübersicht an; das Bild zeigt Dir wir das aussehen soll. Die Rahmen sind für die Funktion nicht wichtig, aber es ist etwas übersichtlicher. Die Zellen füllen wir aus den Stammdaten und Lagerbewegungen.

die (noch) leere Bestandsübersicht

Grundsätzlich vermeiden wir das Kopieren von Daten, die schon mal irgendwo eingegeben wurden. Wir arbeiten prinzipiell mit Verweisen! 

Die Materialnummern werden aus den Stammdaten übernommen. Zelle a2 anklicken "=" eintippen und auf das Blatt gehen. Dort dann die Zelle a2 anklicken. Dann wird die Materialnummer übernommen. Mit Autoausfüllen übernimmst Du alle weiteren Materialnummern.

Nun stehen in der ersten Spalte alle Materialnummern. Diese dienen nun als Index um die fehlenden Daten mittels SVERWEIS aus den Stammdaten zu ziehen. Fülle mit dem VERWEIS die Spalte Bezeichnung und Einzelpreis.

Der Bestand zum Inventurzeitpunkt berechnest Du, indem Du aus den Inventurdaten eine Pivottabelle erzeugst: In den Zeilen "Material" und die Werte sollen "Summen von Menge" sein.  Auch diese Daten werden aus den drei Privottabellen mittels SVERWEIS übernommen. 

Das gleich Bild entsteht auch mit Kopieren. Ist aber nicht so flexibel und klappt auch nur, wenn alle Listen alle Materialnummern in der gleichen Reihenfolge aufweisen, sonst werden einfach falsche Zahlen miteinander verrechnet. Kontrollier das mal, wenn Du 50.000 Materialnummern hast. 

Bestandswerte berechnen

Fehlt nur noch die Stückzahl am Jahresende, was aber einfach die Summe der Inventur und der Warenein-ausgänge ist. Beachte die Warenausgänge haben negative Mengen. 

Der Wert eines Materials ist das Produkt aus Einzelpreis mal Stückzahl. Die Spalten einfach mit der Autosumme zusammenaddieren. Wenn alles richtig eingegeben wurde, dann sind Spalten- und Zeilensumme die Gesamtsumme. Das checkst du bitte. Falls es nicht stimmt, dann soll die Zelle rot sein. 

Es gilt das Paretoprinzip der Informatik: in 80% der Fälle sitzt der Fehler 20 cm vor dem Bildschirm. Also wird alles kontrolliert!

Der Vorteil dieser Methode ist, dass die Daten einfach ausgetauscht werden können. Andere Buchungsdaten, Daten der Pivottabelle aktualisieren und fertig. Es wird alles dynamisch berechnet.

Fertige Bestandübersicht von Electronics Inc.

Update 11.03.2022

Markiere in der Bestandübersicht die 15 Zellen in der Spalte Stück. Tippe "=" ein und gehe auf die Pivottabelle in der Du die Werte mit den "Summen von Menge" markierst; mit STRG+UMSCHALT+EINGABETASTE schließt Du die Eingabe ab. Wenn Du dir die Zellen ansiehst wirst Du geschweifte Klammern sehen, die bei Excel ein Array kennzeichnen. Die Daten aus der Pivottabelle werden Zelle für Zelle übernommen, sehr praktisch.

Die erste Lösung hat den Nachteil, dass die neue Tabelle aus den Daten von 4 anderen Blättern zusammengebaut wird. Das geht mit der Kopiererei nur, wenn alle (!) Tabellen genau die gleichen Materiallisten haben. Das klappt hier (hab ich ja so gebaut).

Mehr Flexibilität ist möglich, wenn alle fehlenden Daten mit SVERWEIS aus allen anderen Blättern geholt werden. Dabei ist der Index die Materialnummer. Die Reihenfolge der Materialnummern ist beliebig; einfach mal ausprobieren.