<\/p>\n
Mit einer ABC-Analyse teilen Sie verschiedene Objekte in die drei Klassen A, B und C auf. Bei diesen Objekten kann es sich beispielsweise um Kunden, Kundengruppen, Produkte oder auch Produktgruppen handeln. Im Mittelpunkt steht jedoch meist der Umsatz des Objekts beziehungsweise der Gruppe von Objekten. Die Aufteilung erfolgt nach Wertanteilen der ermittelten Werte am Gesamtwert. Wie Sie dies mit Access anstellen, zeigt der vorliegende Beitrag.<\/b><\/p>\n
Technisch betrachtet verläuft eine ABC-Analyse wie folgt: Sie summieren die Umsätze der einzelnen Kunden, sortieren diese absteigend, ermitteln den prozentualen Anteil am Gesamtumsatz und kumulieren diesen Wert dann in einer weiteren Spalte.<\/p>\n
Schließlich gruppieren Sie die Kunden nach den kumulierten Prozentwerten entsprechend den gewünschten Grenzen für die Gruppen A, B und C. Beispiele für solche Grenzen sind 80% für Gruppe A, 15% für Gruppe B und 5% für Gruppe C. Sie können jedoch auch 70%, 20% und 10% oder andere Kombinationen als Grenzwerte verwenden.<\/p>\n
Auf der anderen Seite ermitteln Sie im nächsten Schritt, wie viele Kunden prozentual den drei Gruppen A<\/b>, B <\/b>und C <\/b>angehören.<\/p>\n
Zusammengefasst werden diese Zahlen dann in einem entsprechenden Diagramm angezeigt – mehr dazu im Beitrag Diagramme zur ABC-Analyse <\/b>(www.access-im-unternehmen.de\/881<\/b>).<\/p>\n
Beispieldaten<\/b><\/p>\n
Die Beispieldaten stammen wieder einmal aus der Südsturm-Datenbank. Für dieses Beispiel benötigen wir daraus zunächst die Tabellen tblKunden<\/b>, tblBestellungen <\/b>und tblBestelldetails<\/b>.<\/p>\n
Technische Umsetzung<\/b><\/p>\n
Im ersten Schritt erstellen wir eine Abfrage namens qryKundenNachUmsatz<\/b>, welche die Umsätze der einzelnen Kunden summiert und diese absteigend nach dem Umsatz sortiert (s. Bild 1). Diese Abfrage enthält die drei Tabellen tblKunden<\/b>, tblBestellungen <\/b>und tblBestelldetails<\/b>. Aus der Tabelle tblKunden <\/b>benötigen wir nur die Felder KundeID <\/b>und Firma<\/b>.<\/p>\n
![\"pic001.png\"](\"..\/fileadmin\/_temp_\/2013_02\/ABCAnalyse-web-images\/pic001.png\")
<\/p>\n
Bild 1: Abfrage zum Ermitteln der Umsätze<\/span><\/b><\/p>\n Die Tabelle tblBestellungen <\/b>sorgt lediglich dafür, dass die Bestellpositionen den einzelnen Kunden zugeordnet werden können. Die Tabelle tblBestellpositionen <\/b>liefert die Felder zur Berechnung des Umsatzes einer jeden Bestellposition: Einzelpreis<\/b>, Anzahl <\/b>und Rabatt<\/b>.<\/p>\n Der Umsatz wird aus diesen drei Feldern mit folgender Formel ermittelt:<\/p>\n Damit die Abfrage nicht jede einzelne Position ermittelt, sondern diese gleich zum Gesamtumsatz der einzelnen Kunden summiert, aktivieren Sie die Anzeige der Zeile Funktion <\/b>des Abfrageentwurfs (Klick mit der rechten Maustaste in das Entwurfsraster, dann Auswahl des Kontextmenüeintrags Summen<\/b>).<\/p>\n Für die beiden Spalten KundeID <\/b>und Firma <\/b>können Sie den Standardwert Gruppieren <\/b>in der Zeile Funktionen <\/b>beibehalten. Der Inhalt des Feldes Umsatz <\/b>soll hingegen summiert werden, daher legen Sie dafür den Wert Summe <\/b>fest. Schließlich stellen Sie in der Zeile Sortierung <\/b>für die Spalte Umsatz <\/b>den Wert Absteigend <\/b>fest. Der Wechsel in die Datenblattansicht der Abfrage liefert das Ergebnis aus Bild 2 – die Umsätze werden summiert und in der richtigen Reihenfolge angezeigt.<\/p>\n Bild 2: Umsatzsummen in absteigender Reihenfolge<\/span><\/b><\/p>\n Prozentualer Anteil<\/b><\/p>\n Nun benötigen wir den prozentualen Anteil des Umsatzes am Gesamtumsatz. Also fügen wir der Abfrage ein weiteres berechnetes Feld hinzu. Dieses soll den Inhalt des Feldes Umsatz <\/b>durch den gesamten Umsatz aller Kunden dividieren. Der besseren Übersichtlichkeit halber fügen wir in einer weiteren Spalte zunächst ein Feld namens Gesamtumsatz <\/b>ein, das die folgende Berechnungsformel verwendet:<\/p>\n Hier kommt also eine Domänenfunktion zum Einsatz, welche die Summe der Umsätze aller Datensätze der Tabelle tblBestelldetails <\/b>ermittelt. Diese Spalte versehen Sie in der Zeile Funktion <\/b>wiederum mit dem Eintrag Gruppierung<\/b>. In dieser Abfrage bringen wir nun noch den prozentualen Anteil des Umsatzes des aktuellen Kunden am Gesamtumsatz unter. Dazu verwenden Sie die folgende Berechnungsformel:<\/p>\n Hier greifen wir wieder auf die Formel zurück, die wir bereits zum Summieren des Umsatzes je Kunde verwendet haben. Legen Sie für dieses Feld wieder den Wert Summe <\/b>in der Zeile Funktion <\/b>fest, damit die Werte je Gruppe (also je Kunde) summiert werden – das Ergebnis zeigt Bild 3.<\/p>\n Bild 3: Umsätze mit Gesamtumsatz und prozentualem Anteil am Gesamtumsatz<\/span><\/b><\/p>\n Warum aber nutzen wir nicht einfach den Namen des bereits berechneten Ausdrucks, also Umsatz <\/b>statt [Einzelpreis]*[Anzahl]*(1-[Rabatt])<\/b> Normalerweise funktioniert das, aber nicht in Abfragen mit Gruppierungen. Hier gibt es nur zwei Möglichkeiten: Entweder Sie geben den kompletten Ausdruck nochmals an (wie in diesem Beispiel) oder Sie verwenden eine erste Abfrage, die den Ausdruck ermittelt und benennt, und erstellen dann eine zweite Abfrage, welche diesen Ausdruck mit dem entsprechenden Namen weiterverwendet.<\/p>\n Prozente kumulieren<\/b><\/p>\n Nun wollen wir in einer weiteren Spalte die Prozentwerte kumulieren, um zu ermitteln, wie viele der umsatzstärksten Kunden nötig sind, um beispielsweise 80% des Umsatzes abzudecken, und wieviele die folgenden 15% und die letzten 5% bedienen. Diese sollen dann auch noch in die drei Gruppen A<\/b>, B <\/b>und C <\/b>einsortiert werden.<\/p>\n Das Ergebnis soll schließlich wie in Bild 4 aussehen. Die vorletzte Zeile heißt Kumuliert <\/b>und kumuliert alle bisherigen prozentualen Anteile am Umsatz auf. Die letzte Zeile heißt Gruppe <\/b>und liefert die Werte A<\/b>, B <\/b>und C<\/b>.<\/p>\n Bild 4: Kumulierte prozentuale Anteile und Einteilung in Gruppen<\/span><\/b><\/p>\n Diese Abfrage heißt qryABCDynamisch <\/b>und verwendet die bereits erstellte Abfrage qryKundenNachUmsatz <\/b>als Datenherkunft (s. Bild 5). Ziehen Sie die drei Felder Firma<\/b>, Umsatz <\/b>und ProzentVomGesamtumsatz <\/b>in das Entwurfsraster der Abfrage.<\/p>\n Bild 5: Neue Abfrage, die auf der Abfrage qryKundenNachUmsatz basiert und zwei weitere Felder hinzufügt.<\/span><\/b><\/p>\n Dann fügen Sie zwei neue Felder hinzu, die wiederum berechnete Ausdrücke sind. Das erste Feld namens Kumuliert <\/b>bedarf einer Erläuterung, denn dieses enthält eine Unterabfrage. Diese verwendet wiederum die Abfrage qryKundenNachUmsatz <\/b>als Datenherkunft – also genau wie die Hauptabfrage qryABCDynamisch<\/b>.<\/p>\n Da wir die Datensätze der Haupttabelle und der Untertabelle anhand des Feldes ProzentVomGesamtumsatz <\/b>abgleichen möchten (wie genau, erfahren Sie gleich im Anschluss), müssen wir eine der beiden Instanzen dieser Abfrage mit einem alternativen Namen versehen, in diesem Falle t1 <\/b>für die Datenherkunft der Unterabfrage.<\/p>\n Die Unterabfrage liefert die Summe des Werts des Feldes ProzentVomGesamtumsatz <\/b>für alle Datensätze der Unterabfrage (hier t1 <\/b>genannt), deren Feld ProzentVomGesamtumsatz <\/b>einen Wert enthält, der größer oder gleich dem Wert dieses Feldes des aktuellen Datensatzes der Hauptabfrage ist. Bild 6 verdeutlicht diesen Zusammenhang: Sie zeigt die Haupt- und die Unterabfrage der Abfrage qryABCDynamisch<\/b>. Dabei wird etwa dem ersten Datensatz die Summe aller Werte des Feldes ProzentVomGesamtumsatz <\/b>der Unterabfrage t1 <\/b>hinzugefügt, deren Wert größer oder gleich dem aktuellen Wert in der Hauptabfrage qryKundenNachUmsatz <\/b>ist – und dies entspricht lediglich dem ersten Datensatz von t1<\/b>. Für den vierten Datensatz hingegen liefert die Unterabfrage bereits die Summe der Werte des Feldes ProzentVomGesamtumsatz <\/b>für alle Datensätze, deren Prozentsatz größer oder gleich dem Wert des Feldes ProzentVomGesamtumsatz <\/b>des vierten Datensatzes der Abfrage qryKundenNachUmsatz <\/b>ist.<\/p>\n Bild 6: Kumulieren der prozentualen Anteile am Umsatz<\/span><\/b><\/p>\n <\/p>\n Der Ausdruck für das Feld Kumuliert <\/b>sieht entsprechend wie folgt aus:<\/p>\n Der Clou ist die Verwendung des gleichen Feldes aus der Haupt- und der Unterabfrage im Vergleichsausdruck, wobei die jeweils zu verwendende Datenherkunft durch t1 <\/b>(Unterabfrage) und qryKundenNachUmsatz <\/b>(Hauptabfrage) referenziert wird.<\/p>\n Damit erhalten wir nun die kumulierten Werte.<\/p>\n Einteilung in die Gruppen A, B und C<\/b><\/p>\n Fehlt noch der letzte Schritt – die Einteilung der Kunden in die Gruppen A<\/b>, B <\/b>und C <\/b>entsprechend der vorgegebenen Prozentgrenzen. Im vorliegenden Fall soll der erste Grenzwert 80% sein und der zweite 95%. Der folgende Ausdruck wertet den Inhalt des Feldes Kumuliert<\/b> aus und prüft, ob der Wert kleiner als 0,8 <\/b>oder 0,95 <\/b>ist. Im ersten Fall liefert der Ausdruck den Wert A<\/b>, im zweiten den Wert B <\/b>und in allen anderen Fällen den Wert C<\/b>:<\/p>\n Wenn Sie die Abfrage nun ausführen, werden Sie feststellen, dass die Berechnung bereits bei den hier vorliegenden gut 2.000 Datensätzen relativ lange dauert. Da diese Analyse gelegentlich auch für wesentlich größere Datenmengen eingesetzt werden soll, müssen wir im Anschluss noch ein wenig an der Performance arbeiten.<\/p>\n Prozentuale Anteile der Gruppen A, B und C ermitteln<\/b><\/p>\n Zuvor schauen wir uns jedoch noch an, wie Sie die prozentualen Anteile der Kunden an den einzelnen Gruppen ermitteln können.<\/p>\n Das Ergebnis sollte etwa wie in Bild 7 aussehen. Die Abfrage sollte drei Datensätze liefern, die jeweils den prozentualen Anteil der Anzahl der Kunden für die drei Gruppen A<\/b>, B <\/b>und C <\/b>enthält.<\/p>\n Bild 7: Prozentualer Anteil der Kunden je Gruppe<\/span><\/b><\/p>\n Dazu benötigen wir wieder zwei aufeinander aufbauende Abfragen. Die erste heißt qryAnzahlKundenNachGruppe <\/b>und verwendet die zuvor erstellte Abfrage qryABCDynamisch <\/b>als Datenherkunft (s. Bild 8). Davon ziehen Sie das Feld Gruppe<\/b> in das Entwurfsraster – weitere Felder benötigen wir nicht. Allerdings arbeiten wir auch hier wieder mit Gruppierungen. Für das Feld Gruppe <\/b>stellen Sie die Zeile Funktion <\/b>auf Gruppierung <\/b>ein.<\/p>\n Bild 8: Ermittlung der Anzahl der Kunden je Gruppe<\/span><\/b><\/p>\n Das zweite Feld soll die Anzahl der Datensätze je Gruppe liefern. Dazu stellen Sie den Ausdruck auf AnzahlGruppe: Gruppe <\/b>ein und legen für die Zeile Funktion <\/b>den Wert Anzahl <\/b>fest. Schließlich soll eine weitere Spalte die Gesamtzahl der Kunden über alle Gruppen liefern. Dies erledigt wiederum eine Domänenabfrage, die wie folgt aussieht:<\/p>\n Dieser Ausdruck wird wiederum einfach gruppiert.<\/p>\n Die zweite Abfrage qryProzentKundenNachGruppe <\/b>sieht im Entwurf wie in Bild 9 aus. Sie verwendet die zuvor erstellte Abfrage qryAnzahlKundenNachGruppe <\/b>als Datenherkunft. Das erste Feld Gruppe <\/b>liefert schlicht den Namen der Gruppe, also A<\/b>, B <\/b>und C<\/b>. Das zweite Feld ProzentGruppe<\/b> berechnet den prozentualen Anteil der Anzahl der Kunden je Gruppe an der Gesamtzahl der Kunden. Dies erledigt der folgende Ausdruck:<\/p>\n Bild 9: Ermittlung des prozentualen Anteils der Kunden je Gruppe<\/span><\/b><\/p>\n Wie nicht anders zu erwarten, benötigt diese Abfrage noch ein wenig länger als die vorherigen, da diese ja aufeinander aufbauen.<\/p>\n Ergebnis interpretieren<\/b><\/p>\n Damit hätten wir nun in einigen kleinen Schritten das gewünschte Ergebnis ermittelt. Dieses fällt im vorliegenden Fall recht gut aus: Die umsatzstärksten Kunden machen gut ein Drittel aller Kunden aus. Das bedeutet, dass auch der Wegfall von einem oder mehreren Kunden der Gruppe A keine gravierenden Auswirkungen auf den Umsatz hat.<\/p>\n In vielen Fällen sieht dies anders aus: Dort enthält die Gruppe A mit dem umsatzstärksten Kunden vielleicht nur zwei oder drei Kunden. Fällt dort ein Kunde weg, bricht der Umsatz gleich drastisch zusammen. Wäre dies hier der Fall, könnte man entsprechende Schlüsse daraus ziehen: Beispielsweise sollte man erstens besonders gut mit den wenigen Kunden der Kategorie A <\/b>umgehen, andererseits aber versuchen, diese Gruppe zu vergrößern.<\/p>\n Andersherum könnte man prüfen, wie viel Aufwand die Pflege der Kunden der Kategorie C erfordert, die nur wenig Umsatz generieren.<\/p>\n Performance optimieren<\/b><\/p>\n Wie bereits erwähnt, ist die aktuelle Lösung der aufeinander aufbauenden Abfragen nicht besonders performant – Sie zeigte jedoch recht anschaulich, wie die Berechnung erfolgen kann.<\/p>\n Im zweiten Beispiel schauen wir uns eine ABC-Analyse der verschiedenen Artikel in Bezug auf den damit generierten Umsatz an. Dabei wollen wir diesmal auf VBA setzen. Ganz ohne Abfrage geht es jedoch nicht, denn diese haben schon ihre Stärken – so ist beispielsweise das Summieren von Werten nach Gruppierungen in einer einfachen Abfrage normalerweise eine performante Lösung. Wenn jedoch wie im vorherigen Beispiel noch Domänenfunktionen, Unterabfragen et cetera vorkommen, wird die Abfrage schnell sehr langsam.<\/p>\n Im zweiten Beispiel sollen nun die prozentualen Anteile der einzelnen Artikel an den Gruppen A<\/b>, B <\/b>und C <\/b>ermittelt werden. Diesmal verzichten wir auf jegliche Zwischenergebnisse wie die prozentualen Anteile etwa eines einzelnen Artikels am Gesamtumsatz – wir wollen am Ende schlicht und einfach drei Zahlenwerte erhalten, welche die prozentuale Verteilung der Artikel auf die Gruppen A<\/b>, B <\/b>und C <\/b>liefern.<\/p>\n Dazu erstellen wir zunächst eine Abfrage, die uns den Großteil der Arbeit abnimmt – nämlich das Gruppieren der Umsätze nach den Artikeln und das Sortieren nach den Gruppensummen. Diese Abfrage heißt qryArtikelNachUmsatz <\/b>und sieht im Entwurf wie in Bild 10 aus. Das erste Feld liefert die eigentlich wichtigen Informationen und erhält den folgenden berechneten Ausdruck:<\/p>\n Bild 10: Abfrage zum Ermitteln und Sortieren der Umsätze nach Artikeln<\/span><\/b><\/p>\n Die Funktion ist im Screenshot auf Ausdruck <\/b>eingestellt. Beim Erstellen der Abfrage können Sie jedoch auch den folgenden Ausdruck in der Zeile Feld <\/b>eingeben und dann den Wert in der Zeile Funktion <\/b>auf Summe <\/b>einstellen:<\/p>\n Access ändert dies nach dem Speichern der Abfrage automatisch in die Version aus dem Screenshot. Stellen Sie für dieses Feld eine absteigende Sortierung ein.<\/p>\n Das Ergebnis der Abfrage sieht schließlich wie in Bild 11 aus. Mehr als diese Zahlen benötigen Sie nicht – für die ABC-Analyse an sich ist es zunächst irrelevant, welche Artikel wie zum Ergebnis beitragen. Wenn man sich später die Artikel genauer ansehen möchte, kann man dies mithilfe entsprechender Abfragen erledigen.<\/p>\n Bild 11: Ermitteln und Sortieren der Umsätze je Artikel<\/span><\/b><\/p>\n Mit diesem Ergebnis begeben wir uns nun zum VBA-Editor. Dort erstellen Sie eine Prozedur, die schlicht und einfach das Ergebnis aus Bild 12 im Direktfenster ausgibt.<\/p>\n Bild 12: Ausgabe des Ergebnisses der ABC-Analyse im Direktfenster<\/span><\/b><\/p>\n Dabei können Sie sogar noch die Prozent-Grenzen für die einzelnen Gruppen dynamisch festlegen. Dies erledigen Sie beim Aufruf der Prozedur durch die Übergabe entsprechender Parameter:<\/p>\n Die hier verwendeten Werte 80 <\/b>und 15 <\/b>geben die Intervalle für die beiden Gruppen A <\/b>und B <\/b>an, das Intervall für die Gruppe C <\/b>wird aus der Differenz von 100% <\/b>und den beiden für die ersten Intervalle angegebenen Prozentzahlen ermittelt.<\/p>\nUmsatz: [Einzelpreis]*[Anzahl]*(1-[Rabatt])<\/pre>\n
![\"pic002.png\"](\"..\/fileadmin\/_temp_\/2013_02\/ABCAnalyse-web-images\/pic002.png\")
<\/p>\nGesamtumsatz: DomSumme("Einzelpreis*Anzahl*(1-Rabatt)";"tblBestelldetails")<\/pre>\nProzentVomGesamtumsatz: [Einzelpreis]*[Anzahl]*(1-[Rabatt])\/[Gesamtumsatz]<\/pre>\n
![\"pic003.png\"](\"..\/fileadmin\/_temp_\/2013_02\/ABCAnalyse-web-images\/pic003.png\")
<\/p>\n![\"pic004.png\"](\"..\/fileadmin\/_temp_\/2013_02\/ABCAnalyse-web-images\/pic004.png\")
<\/p>\n![\"pic005.png\"](\"..\/fileadmin\/_temp_\/2013_02\/ABCAnalyse-web-images\/pic005.png\")
<\/p>\n![\"pic006.png\"](\"..\/fileadmin\/_temp_\/2013_02\/ABCAnalyse-web-images\/pic006.png\")
<\/p>\nKumuliert: (SELECT Sum(ProzentVomGesamtumsatz) FROM qryKundenNachUmsatz AS t1 WHERE t1.ProzentVomGesamtumsatz >= qryKundenNachUmsatz.ProzentVomGesamtumsatz)<\/pre>\n
Gruppe: Wenn([Kumuliert]<0,8;"A";Wenn([Kumuliert]<0,95;"B";"C"))<\/pre>\n
![\"pic007.png\"](\"..\/fileadmin\/_temp_\/2013_02\/ABCAnalyse-web-images\/pic007.png\")
<\/p>\n![\"pic008.png\"](\"..\/fileadmin\/_temp_\/2013_02\/ABCAnalyse-web-images\/pic008.png\")
<\/p>\nAnzahlKunden: DomAnzahl("KundeID";"tblKunden")<\/pre>\n![\"pic009.png\"](\"..\/fileadmin\/_temp_\/2013_02\/ABCAnalyse-web-images\/pic009.png\")
<\/p>\nProzentGruppe: [AnzahlGruppe]\/[AnzahlKunden]<\/pre>\n
![\"pic010.png\"](\"..\/fileadmin\/_temp_\/2013_02\/ABCAnalyse-web-images\/pic010.png\")
<\/p>\nUmsatz: Summe([Einzelpreis]*[Anzahl]*(1-[Rabatt]))<\/pre>\n
Summe([Einzelpreis]*[Anzahl]*(1-[Rabatt]))<\/pre>\n
![\"pic011.png\"](\"..\/fileadmin\/_temp_\/2013_02\/ABCAnalyse-web-images\/pic011.png\")
<\/p>\n![\"pic012.png\"](\"..\/fileadmin\/_temp_\/2013_02\/ABCAnalyse-web-images\/pic012.png\")
<\/p>\nPublic Sub Test_ABCArtikel()\r\n ABCArtikel 80, 15\r\nEnd Sub<\/pre>\n