Am 14. September 2017 haben wir eine überarbeitete Fassung unserer Datenschutzrichtlinie veröffentlicht. Wenn Sie video2brain.com weiterhin nutzen, erklären Sie sich mit diesem überarbeiteten Dokument einverstanden. Bitte lesen Sie es deshalb sorgfältig durch.

Excel-Tipps: Jede Woche neu

Excel-Tipp 18: Visuelle ABC-Analyse durchführen

LinkedIn Learning kostenlos und unverbindlich testen!

Jetzt testen Alle Abonnements anzeigen
René Martin hat in seiner Arbeit als Trainer zahllose Excel-Tipps gesammelt. Mit diesen Tipps lassen sich typische Anwendungsaufgaben schneller und eleganter lösen oder Fallstricke vermeiden. Dieses Wissen gibt er Ihnen in kleinen Häppchen Woche für Woche weiter. Seien Sie gespannt, welchen kleinen Leckerbissen er in der neuen Woche für Sie bereithält. Und mehr als 10 Minuten brauchen Sie nicht zu investieren.
05:54
  Lesezeichen setzen

Transkript

"Also lautet ein Beschluss, dass der Mensch was lernen muss, nicht allein das ABC bringt den Menschen in die Höh', nicht allein im Schreiben, Lesen übt sich ein vernünftig Wesen, nicht allein in Rechnungssachen soll der Mensch sich Mühe machen, sondern auch der Weisheit Lehren muss man mit Vergnügen hören". Kennen Sie es? Klar, Wilhelm Busch, der Lehrer Lämpel, der von Max und Moritz geärgert wird. Nein, Streiche will ich hier keine machen. Mein Name ist René Martin, und ich unterrichte ja selbst, aber eine ABC-Analyse möchte ich durchführen, eine dynamische und visuelle ABC-Analyse. Verstanden? "Der Worte sind genug gewechselt, lasst mich endlich auch Taten sehen", eben, lassen Sie uns in Excel anschauen, wovon ich spreche. Übrigens, das Goethe-Gedicht endet mit, "indes ihr Komplimente drechselt, kann etwas Nützliches geschehen". Eine kleine ABC-Analyse. Wie habe ich denn das gemacht? Wenn ich jetzt hier in Excel in der Registerkarte "Formeln" auf "Neu berechnen" klicke oder wenn ich die Funktionstaste F9 drücke, dann werden die Daten neu berechnet, Sie sehen hier "ZUFALLSBEREICH", eine zufällige Zahl wird berechnet, die Daten werden neu sortiert und entsprechend ihrer Werte neu formatiert. Noch einmal. Ich berechne neue Zahlen und Sie sehen hier die Breite der dunklen-, der mittellilanen und der helllilanen Bereiche verändern sich. Nun, "ZUFALLSBEREICH" ist nicht unser Thema, sondern die ABC-Analyse, und die sehen Sie hier im Hintergrund, was ich hier gemacht habe, was ich hier geschummelt habe. Ich habe von diesen Umsatzzahlen, die ja hier kreuz und quer drinstehen, die ja hier nicht sortiert sind, die Artikel sind alphabetisch sortiert, ich habe diese Umsatzzahlen mit der Funktion "KGRÖSSTE" sortiert. "KGRÖSSTE" lieferte mir die erstgrößte, zweitgrößte, drittgrößte. Damit ich die jeweils erst-, zweit, drittgrößte bekomme, nehme ich die Funktion "ZEILE" und rechne eben die erste Zeile, zweite Zeile und so weiter, also Zeile liefert hier "=zeile" die Nummer 2, und wenn ich das runterziehe, bekomme ich Nummer 3, Nummer 4, Nummer 5. Und "KGRÖSSTE" von der entsprechenden Zahl "=KGRÖSSTE", gibt natürlich auch "KKLEINSTE", von dieser Zeile ist die "KGRÖSSTE" von diesem Bereich, sollte ich natürlich markieren. Liefert mir die erstgrößte, zweitgrößte, drittgrößte, in dem Fall natürlich die zweitgrößte, deshalb habe ich hier noch "-1" gemacht, das zur Erklärung von diesem "KGRÖSSTE". So, jetzt weiß ich welche die größte, zweitgrößte, drittgrößte Zahl ist und so weiter, ausgehend von der Zahl kann ich nun mit der Funktion "VERGLEICH" aus dieser Spalte die entsprechende Zahl suchen und mir die entsprechende Zeilennummer zurückgeben lassen. Das heißt, er wird die erste Umsatzahl in der Zeile 14 finden, in der Zeile 11, in der Zeile 5 und so weiter, also quasi, in welcher Zeile stehst du. Und jetzt mit so einer Art S-Verweis, mit der Funktion "Index", suche aus der Spalte A über diese Zeilennummer die entsprechende Position, und er liefert mir dann den entsprechenden Artikel. Den brauche ich hier, und jetzt stehen die Artikel so drin, wie ich sie gerne haben möchte, nämlich von groß nach klein sortiert. Unter der Liste habe ich nun hier ausgerechnet "D42" steht die Summe. Insgesamt haben wir, okay, und prozentual der Umsatz, den wir hier haben für den einen Artikel, geteilt durch das Ganze, das heißt, für den Artikel haben wir 4,49 % Beteiligung, 4,46, 4,38 und so weiter, einfach Anteil durch die Summe hier gerechnet. Die Umsatzzahlen werden kumuliert, 4, 8, 13 und so weiter. Und jetzt kommt die ABC-Analyse ins Spiel und die liefert mir, wenn du kleiner bist als 80 %, eine einfache Wenn-Funktion, dann zeige mir die Zahl an, sonst eben nichts mehr. Sie sehen, hier ist mein Break-Even, da wird die Grenze überschritten. Hier der zweite Schritt, wenn du zwischen 80 und 95 bist, das heißt, größer als 80 und kleiner als 95, hätte ich etwas schöner beschriften sollen, das heißt, der Bereich, dann zeige mir hier die Zahlen an, und sowohl größer gleich 80 als auch kleiner als 95. Und der Letzte, das ist der Rest, wenn du echt größer als 90 % bist, dann wiederhole die Zahl, das heißt, jede dieser kumulierten Prozentzahlen taucht entweder hier auf oder hier auf oder in der dritten Spalte hier unten auf. Und das ist auch der Trick, ich verwende jetzt eigentlich drei übereinander gestapelte Säulen, also Säule 1 plus 0 plus 0. Null plus Säule zwei plus Null. Und die liegen einfach hintereinander, die habe ich hier, rechte Maustaste, "Datenreihen formatieren", die habe ich hier mit 100 % übereinander gelagert, einmal die, einmal die. Sie sehen, das sind natürlich echt andere, er markiert auch die anderen, aber die sind hier unten, beziehungsweise hier oben, also kann man schlecht markieren, habe ich also übereinander geschoben und mit drei verschiedenen Farben formatiert, das hat er sowieso von Haus aus gemacht, und damit habe ich eine wunderbare ABC-Analyse. Heißt also, man muss die Zahlen ein bisschen in eine andere Form bringen und setzt dann ein Diagramm auf diese Zahlen auf, sodass er diese Nullwerte eigentlich ausblendet oder ich berechne Nullwerte, die halt hier im Diagramm nicht angezeigt werden.