Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1404to1408
1404to1408
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

ABC Analyse per Formel

Forumthread: ABC Analyse per Formel

ABC Analyse per Formel
06.02.2015 11:30:23
Martin
Hallo liebe Community,
ich arbeite schon länger daran, eine ABCD - Analyse unter folgenden Restriktionen zu bekommen:
- Keine vorherige Sortierung der Werte (Sortierung muss in Formel enthalten sein)
- Keine Verwendung von Pivots/ VBA
- Die ABC Analyse klassifiziert die Artikel anhand kumulierter Werte bis zum vorgegebenen Grenzwert. Ich erwähne das, da alle anderen ABC-Analysen die (per Formel ohne vorherige Sortierung) hier vorgestellt wurden, dieses nicht machen. Es werden lediglich EINZELTEILE auf Grund ihres prozentualen Umsatzanteils klassifiziert - was per Definition FALSCH ist :)
Die hier im "Herber-Forum" bisher veröffentlichten Lösungen stellen keine ABC-Analysen dar!!
https://www.herber.de/forum/archiv/1184to1188/1184542_Formel_fuer_ABC_Analyse_gesucht.
https://www.herber.de/forum/archiv/1168to1172/1168906_ABC_Analyse.html
https://www.herber.de/forum/archiv/1312to1316/1315204_ABCKlassifizierung.html

In einem anderen Forum habe ich eine funktionierende Lösung gefunden. Diese Lösung möchte ich gerne um eine Kategorie "D" erweitern und somit eine ABCD Analyse zu erhalten. Allerdings versteh ich die Formel nicht und komme gerade überhaupt nicht weiter. Ein Beispiel mit der funktionierenden ABC Analyse ist angehängt.
Die Einteilung für die ABCD Analyse soll wie folgt aussehen:
A-Teile 65%
B-Teile 20%
C-Teile 10%
D-Teile 5%
...des Umsatzes
https://www.herber.de/bbs/user/95603.xlsx

Anzeige

9
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: ABC Analyse per Formel
06.02.2015 12:14:08
EtoPHG
Hallo Martin,
Gewagte Behauptung:Die hier im "Herber-Forum" bisher veröffentlichten Lösungen stellen keine ABC-Analysen dar!! ...ich lass mich aber nicht mehr auf Diskussionen bezgl. dieses Themas ein!
Deine Formel müsste wohl lauten: 
=ZEICHEN(69-VERGLEICH(-D2;-{100\95\90\80}%))

Gruess Hansueli

Anzeige
AW: ABC Analyse per Formel
09.02.2015 16:22:56
Martin
Hallo Hansueli,
kleine Änderung - große Wirkung :)
Vielen Dank für deine Hilfe!!!
PS: Nur gewagte Behauptungen finden auch Gehör ;-)
Die Behauptung bezieht sich lediglich auf die angeführten Beispiele.

AW: ABC Analyse per Formel
06.02.2015 12:16:42
yummi
Hallo Martin,
was willst du denn in der Spalte D noch zusätzlich darstellen?
Soll A - C so bleiben?
Gruß
yummi

Anzeige
Wer redet von Spalte D? Ausser Dir! (owT)
06.02.2015 12:21:58
Spalte

einer musste das ja mal ansprechen ;-)
06.02.2015 12:49:47
yummi
hatte ich erst falsch verstanden, kurz nach abschicken war mir dann auch klar :-))

Welche Formel verstehst du denn nicht, ...
06.02.2015 12:35:48
Luc:-?
…Martin,
die der Zwischenrechnung oder die ABC-Fml? Bei der Zwischenrechnung entspricht %%% /10^6 bzw *10^-6, ZEILE(B$2:B$31) einer lfdNr von 2…31 über alle Werte, die durch diese Operation so verkleinert wird, dass sie der Sortierung dienen kann ohne das Ergebnis nennenswert zu beeinflussen. Dadurch ergibt sich quasi eine RangfolgenZahl von 0…1 mit 0 für den größten UmsatzAnteil bis gg 1 für den kleinsten.
Diese Operation ist erforderlich, weil VERGLEICH mit von…bis-Bereichen arbeiten soll, was nur bei sortierter Datenfolge möglich ist (auf ähnliche Weise könnte wohl auch mit VERWEIS gearbeitet wdn).
VERGLEICH vglt dann den negativierten ReihenfolgeWert mit den ebenso negativierten GruppenWerten in %. Da das 3.Argument weggelassen wird, wird hierfür 1 verwendet, d.h., „VERGLEICH sucht nach dem größten Wert, der ≤ dem Wert für Suchkriterium ist → die Werte im Argument Suchmatrix müssen in aufsteigender Reihenfolge angeordnet sein“.
In der Original-ABC-Fml wdn offensichtlich obere Grenzwerte verwendet, weshalb hier 100 auftaucht. Das solltest du bei der Formulierung der MatrixKonstanten mit den Grenzwerten beachten. Wenn man hier die 100 ergänzen würde, hättest du nämlich eine ABCDE-Analyse, wobei fast alle Daten E wären, nur 2 wären A bzw D. ZEICHEN gibt dann nur noch den entsprd GBn des Alfabets aus. Bei Erhöhung der GruppenAnzahl muss natürlich auch der Minuend erhöht wdn, damit als kleinster Wert der ASCII-ZeichenCode von A herauskommt.
Gruß, Luc :-?
Besser informiert mit …

Anzeige
AW: Welche Formel verstehst du denn nicht, ...
12.02.2015 17:50:02
Martin
Hallo Luc,
vielen Dank für die Erklärung.
Die zweite Formel zur Einteilung in die Kategorien A, B und C mittels Vergleich und Zeichen ist mir nun klar.
Die erste Formel, in der offensichtlich die Sortierung stattfindet versteh ich allerdings noch nicht:
=SUMMENPRODUKT((B$2:B$31+ZEILE(B$2:B$31)%%%)*(B$2:B$31+ZEILE(B$2:B$31)%%%>B2+ZEILE(B2)%%%)) /SUMMENPRODUKT(B$2:B$31+ZEILE(B$2:B$31)%%%)
-Wieso benötige ich überhaupt Summenprodukt, wo ich doch nur 1 Array benutze?
-Welche Funktion hat die Funktion Zeile hier? ...B$2:B$31+ZEILE(B$2:B$31)...
-Was bezwecken die drei Prozentzeichen?
Vielen Dank für die Hilfe

Anzeige
SUMMENPRODUKT wird zum Sortieren ...
12.02.2015 19:01:09
Luc:-?
…benötigt, Martin,
das hatte ich doch alles erklärt! Nun hast du dir so lange Zeit gelassen und doch nichts verstanden! :-/
Was ist an %%% → /10^6 bzw *10^-6 nicht zu verstehen?
Und was an ZEILE(B$2:B$31) entspricht einer lfdNr von 2…31 über alle Werte, die durch diese Operation so verkleinert wird, dass sie der Sortierung dienen kann ohne das Ergebnis nennenswert zu beeinflussen nicht?
Gruß, Luc :-?
Besser informiert mit …

Anzeige
AW: SUMMENPRODUKT wird zum Sortieren ...
13.02.2015 10:40:35
Martin
Wieso kann Summenprodukt sortieren?
Vielen Dank erst mal, für die Mühe :)
Deine Erklärung ist zum Anwenden völlig ausreichend. Sie gibt allerdings keinen Aufschluss darüber, wie Excel "funktioniert".
Ich würde gerne einmal die Berechnung selbst (auf Papier) durchführen und auf das selbe Ergebnis wie die Formeln in Spalte D des Beispiels. Erst dann kann ich das, was Excel hier macht nachvollziehen.
Hat jemand die Muse, eine solche Rechnung zu zeigen?
Vielen Dank schon mal...
Anzeige
;

Forumthreads zu verwandten Themen

@Hans W. Herber: 1 Million Beiträge - Glückwunsch
mpb

Hallo Hans,
wenn ich die URLs richtig interpretiere, haben wir im Forum soeben die Zahl von 1 Million Beiträgen überschritten.
Herzlichen Glückwunsch zu dem Erfolg Deines "Babys"!
Gruß
Martin
07.08.2008 16:23:47
Thread-ID: 1000004
3 Beiträge
Caption der aktiven UserForm abfragen
Martin

Hallo liebe Excelianer,
besteht die Möglichkeit aus einem VBA-Modul heraus die aktive UserForm abzufragen? Bislang verwende ich folgende zwei Makros für zwei verschiedene UserFormen, die ich gerne zu einem Makro vereinen würde, weil deren Inhalt gleich ist:
Sub AK() 'für Userform "Te...
07.08.2008 17:06:21
Thread-ID: 1000037
3 Beiträge
Anzeige
automatisch Berechnen funktioniert nicht
t4z

Hi Leute,
kann mir jmd folgendes Phänomen erklären?
Excel-Datei enthält Formeln, die auf andere Zellen innerhalb der Mappe referenzieren. Die Quelldaten ändern sich, jedoch die Formeln nicht. Unter Extras-Optionen-Berechnung ist "automatisch" aktiv. Ein Drücken auf F9 bringt keinen Erfo...
07.08.2008 17:13:07
Thread-ID: 1000040
3 Beiträge
Menü in Excel von VB6.0 aus erzeugen??
VB6.0

Hallo zusammen,
ich hoffe ihr könnt mir bei meinem Anliegen helfen. Ich möchte den VBA-Code in eine dll transportieren (VB 6.0) und scheitere schon an dem Menüaufbau in Excel.
In der dll steht in einer Klasse:
Sub Test_Excel() Set xl = GetObject(, "Visio.Application") Set objMenu...
07.08.2008 20:23:28
Thread-ID: 1000091
13 Beiträge
Anzeige
Eingabe formatiert zurückgeben (Komma und Punkt)
Lemmi

Hallo zusammen,
ich möchte Euch eine wahrscheinlich einfache Frage stellen, die ich mir gerade mal nicht beantworten kann!
Wie stelle ich sicher das auch wenn ein Punkt als Trennzeichen eingegeben worden ist, am Ende immer ein Komma in der Zelle steht?
Eingabe 10,0........Ausgabe...
07.08.2008 21:35:00
Thread-ID: 1000105
5 Beiträge
Formel automatisch durch Wert ersetzen (vba)
Tanja

Hallo liebe Leute,
Ich hab in meiner excel-Mappe, in den Zellen: M2:M14 Formeln, die eine ganze Zahl als Ergebnis erzeugen.
So, und nun möchte ich das 1. Berechnungs-Ergebnis quasi "einfrieren".
Mein Problem: Ich möchte kein Makro per Hand auslösen müssen, sondern der Code soll voll...
07.08.2008 21:59:00
Thread-ID: 1000110
5 Beiträge
Farbige Zellen suchen und Zuordnung auflisten
Excel-Opfer

Hallo zusammen,
ich hab nicht so die Ahnung von Makros und Automationen im Excel, bräuchte allerdings "dringend" eine Vereinfachung meines Excelchaos.
es dreht sich um folgende Problematik:
ich muss diverse Dienstpläne in unsere Personalplanung einarbeiten, leider liefert eine Abtei...
08.08.2008 06:47:00
Thread-ID: 1000143
16 Beiträge
Stelle für VBA-Crack bei Schweizer Bank
Sand

interessantes Job-Angebot für VBA-Crack unter:
http://www.sarasin.ch/internet/iech/index_iech/about_us_iech/about_us_job_vacancies_iech.htm?jobID=76975
schaut's euch mal an.
cheers,
Sand
08.08.2008 13:37:00
Thread-ID: 1000280
2 Beiträge
Anzeige
Copy Paste Problem mit VBA
Chris

Hallo Leute,
ich habe ein Makro geschrieben, das ein aus SAP gezogenes Spreadsheet in ein formatiertes Excel File umwandelt.
Jetzt taucht plötzlich folgendes Problem auf:
Ein String wird in Zelle "A1" geschrieben, der Buchstaben und steuercode enthält.
D.H. ich habe in dieser Ze...
08.08.2008 15:10:35
Thread-ID: 1000317
11 Beiträge
Version DLL auslesen habe ich...aber Datum?
Peter

Hallo mal wieder,
habe n bissl probiert und recherchiert und dann denn folgenden Code gefunden/gebastelt mit dem ich die Versionsnummer einer DLL auslesen kann:
Sub Welche() Set fs = CreateObject("Scripting.FileSystemObject") ofile = "C:\Programme\Microsoft Office\Office12\refedit.dll...
08.08.2008 15:36:00
Thread-ID: 1000331
3 Beiträge
Farben aus Excel 2007 in Excel 2000 übernehmen
Jochen

Das Farbkonzept von Excel 2007 habe ich trotz langer Web-Recherche nicht verstanden. Was muss ich in Excel 2007 tun, damit ich die gleichen Farben angezeigt bekomme, wenn ich eine in Excel 2007 erstellte Datei in Excel 2000 öffne?
Gruß,
Jogi
08.08.2008 15:58:40
Thread-ID: 1000338
2 Beiträge
Anzeige Fortschrifsbalgen abbrechen
Jochen

Hi,
wie kann ich folgenden Code auf meiner userForm per Button unterbrechen, bevor die 30 Sekunden abgelaufen sind ? :
https://www.herber.de/bbs/user/54473.xls
Danke mal
Jochen
09.08.2008 21:10:32
Thread-ID: 1000547
4 Beiträge
Anzeige
Anzeige

Infobox / Tutorial

ABC Analyse in Excel: Schritt-für-Schritt-Anleitung

Schritt-für-Schritt-Anleitung

Um eine ABC Analyse in Excel durchzuführen, kannst du die folgende Formel verwenden, um die Artikel entsprechend ihrem Umsatzanteil zu klassifizieren. Die Einteilung erfolgt in die Kategorien A, B, C und D, basierend auf den prozentualen Anteilen:

  1. Daten vorbereiten: Stelle sicher, dass du eine Liste von Artikeln und ihren Umsatzanteilen hast.

  2. Formel für die ABC Analyse: Trage die folgende Formel in die Zelle ein, wo du die Klassifikation sehen möchtest (angenommen, die Umsatzanteile sind in Spalte B):

    =ZEICHEN(69-VERGLEICH(-B2;-{100\95\90\80}%))

    Diese Formel gibt dir die Klassifikation A, B oder C, basierend auf den vordefinierten Grenzwerten.

  3. Erweiterung für Kategorie D: Um die Kategorie D hinzuzufügen, kannst du die Formel anpassen, sodass sie auch für 5% oder weniger gilt. Nutze dazu eine weitere Formel oder erweitere die bestehende.

  4. Kategorisierung durchführen: Ziehe die Formel nach unten, um die Klassifizierung für alle Artikel zu erhalten.

Häufige Fehler und Lösungen

  • Fehler: Die Formel gibt keine korrekten Kategorien aus.

    • Lösung: Überprüfe, ob die Werte in der Liste korrekt formatiert sind und die Grenzwerte in der Formel stimmen.

  • Fehler: Excel zeigt einen Fehler bei der Anwendung der Formel.

    • Lösung: Stelle sicher, dass die Formel korrekt eingegeben wurde und die richtigen Zellreferenzen verwendet werden.

Alternative Methoden

Wenn du keine Formeln verwenden möchtest, kannst du auch die Bedingte Formatierung in Excel nutzen, um die Artikel visuell zu kategorisieren. So kannst du die Umsatzanteile farblich hervorheben und die Kategorien A, B, C und D visuell darstellen, ohne die Formel zu verwenden.

Praktische Beispiele

Hier ist ein einfaches Beispiel für die ABC Analyse in Excel:

Artikel Umsatzanteil Kategorie
Artikel 1 70% A
Artikel 2 20% B
Artikel 3 8% C
Artikel 4 2% D

In der Kategorie-Spalte verwendest du die oben genannte Formel.

Tipps für Profis

  • Verwendung von SUMMENPRODUKT: Du kannst auch die SUMMENPRODUKT**-Funktion verwenden, um die Daten automatisch zu sortieren, was die ABC Analyse effizienter macht.

  • Dynamische Bereiche: Nutze dynamische Bereiche in deinen Formeln, um die Analyse zu automatisieren, wenn sich die Daten ändern.

FAQ: Häufige Fragen

1. Wie kann ich die ABC Analyse auch für andere Daten verwenden?
Die Formel kann angepasst werden, um andere relevante Kennzahlen zu klassifizieren, solange du die Grenzwerte entsprechend anpasst.

2. Funktioniert diese ABC Analyse auch in älteren Excel-Versionen?
Ja, die genannten Formeln funktionieren in den meisten Excel-Versionen, die die grundlegenden Funktionen unterstützen.

3. Was sind die Voraussetzungen für die korrekte Durchführung der ABC Analyse?
Stelle sicher, dass deine Daten gut strukturiert sind, und dass du die Umsatzanteile korrekt berechnet hast.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige