Excel mit Factorio lernen (7.Teil): ABC-Analyse

Auch in Factorio gibt es wichtigere und nicht so wichtige Materialien. Am Schluss wird zwar alles irgendwie mal gebraucht, aber wann, was und wieviel davon ändert sich im Spielverlauf ständig.

Zur ABC-Analyse hat ein Projektteam meiner Studentinnen für die Weiterentwicklung meines Moduls "logistische Datenanalyse" eine gut verständliche Erklärung geschrieben.

Darstellung der Daten einer realen ABC-XYZ-Analyse mit allen Artikeln eines Händlers.
Es ist leicht zu erkennen, dass die meisten Artikel wenig Beitrag bringen (grün) aber
gleichzeitig sehr variabel (hier Var.Koeff. über 100) sind. Diese werden als CZ klassifiziert.

Aus den Materialstammdaten und den Lagerbuchungen wurde für das Jahr 2019 die Bestandsübersicht erstellt.  Wenn Du den 5. bzw. den 6. Teil bearbeitet hast, dann kennst Du die Bestandübersicht schon. Hier brauchen wir nur die Werte am Jahresende, wie im Bild zu sehen. 

Bestände zum Jahresende: Der markierte Bereich
muss nach absteigenden Werten Sortiert werden.

Als erstes werden die Materialien nach absteigenden Werten sortiert (Sortieren von Daten). Füge nun in Spalte G den % Anteil des Materials an der Summe. Diese %-Anteile werden in der nächsten Spalte kumuliert.
Das ist eine Iteration: G2=F2 → Gn+1=Fn+1 + Gn

Die Summe muss natürlich 100% ergeben. Wie schon mehrfach geübt, checkst Du das und färbst die Zelle G17 grün, wenn die Summe in G16 100% ist; sonst eben rot.

Die Materialien sollen nach A,B,C klassifiziert werden. Die Klassifikation muss natürlich flexibel sein. Wie immer werden die Parameter in einer kleinen Tabelle abgelegt. und wird dann mit Wenn-Dann abfragen in Spalte H eingegeben. 

Doch was ist, wenn weitere Klassen benötigt werden. 15 Artikel sind natürlich nur ein "Toy-Example" realistische Artikelzahlen gehen in die Tausende. In "Logistische Datenanalyse" benutzen wir eine Datensatz mit 50.000 Artikeln. Weil die Wenn-Dann Abfrage immer länger wird, wird das dann schnell nervenaufreibend.

ABC-Analyse der Lagerbestände mit Wenn-Dann
Die Grenzen der Klassen mal ändern und sehen, was passiert. 

Viel kompakter geht auch

Markiere den Bereich mit den Daten A1:E16 und füge dann eine Pivottabelle ein; Zeilen= Material und die Werte in den Zellen sind die Werte/€. 

Rechter Mausklick auf die Werte, dann "Sortieren → Nach Größe absteigend" 

Rechter Mausklick auf die Werte, dann "Werte anzeigen als → % der Gesamtsumme"

In Spalte C die Werte kumukieren; einfach die Formel Das ist eine Iteration: B4=C4 → Bn+1=Cn+1 + Gn aus der letzten Tabelle kopieren, die Verweise passen sich automatisch an.

ABC-Analyse mit Pivortabelle und S-Verweis

Die Materialien sollen auch hier nach A,B,C klassifiziert werden. Wie immer werden wie Parameter in einer kleinen Tabelle abgelegt. 

Die Klassifikation wird jetzt mit dem S-Verweis durchgeführt:                  D4=SVERWEIS(C4;$B$23:$C$26;2;WAHR)

Beachte, dass der "Bereich_Verweis" hier WAHR ist, sodass die Suche nun nicht exakt sein muss. Dann musst Du nur das nur noch die Zellen bis D18 ziehen. Du siehst, dass die gleiche Klassifikation entsteht.

Nun füge in die Klassifikationstabelle eine neue Zeile ein. Damit kannst Du dann eine 4. Klasse festlegen. Der Trick ist, dass die Erweiterung der Klassifikationstabelle vom S-Verweis automatisch angepasst wird:                D4=SVERWEIS(C4;$B$23:$C$27;2;WAHR)

Alle anderen Zellen der Klassifikation passen dann sofort. 

Projektarbeit

Maier K, Nikolaus N, Knaub C and Schnorr R (2016), "Logistische Datenanalyse – VerbessernSie die Erfolgswahrscheinlichkeit dernächsten Generation!". Thesis at: Hochschule Kaiserslautern. Pirmasens, July, 2016. [BibTeX] [URL]