ABC Analysis
First 2 spreadsheets, Provide Rang for Umsatz & Menge
1) Calc. Range from 2 columns, Menge & Umsatz
=WENNFEHLER(RANG(B2;B_Umsatz_dBer;0);"")
Umsatz_dBer = Range in the adjacent column for all Umsatz
alternative : =WENNFEHLER(RANG(B2;$B$2:$B$20;0);"")
Menge
=WENNFEHLER(RANG(B2;$B$2:$B$20;0);"")
2) Do the analysis for Umsatz & Menge in another (2) spreadsheets. Do formula to order how many rang there are and depending in what cell you start in eg. 1 = 3 or row 3
=WENN('Spreadsheet for Menge'!A2="";"";ZEILE()-2)
=for Umsatz, link the first cell A3 to the cell where the formula sits directly above
3) Pull the Article ID
=SVERWEIS(A3;UmsatzErgebnis_dBer;3;FALSCH)
=WENNFEHLER(SVERWEIS(B3;A_Produkte_dBer;2;FALSCH);"")
4) Pull the amount for Umsatz
=WENNFEHLER(RUNDEN(SVERWEIS(A3;B_UmsatzErgebnis_dBer;2;FALSCH);0);"")
5) Pull the total down to cummulate % Umsatz
=WENNFEHLER(F4+E5;"")
6) Define which category A, B or C
=WENNFEHLER(VERWEIS(F3;Y_KlassenProzent_Ber;Y_KlassenBezeichnung_Ber);"")
Lookup cummulative % in Table for ABC definitions (4 lines)and search or give back according to column for A, B or C.
7) complete the table by providing data about Menge
=SVERWEIS(B3;ABCAnalyse_Menge_Ber;3;FALSCH)
Look up Material ID in the
=WENNFEHLER(H3/B_MengeGesamt_vZ;"")
Give back %
=WENNFEHLER(J4+I5;"")
Give back cummulative %
8) Now you are ready to create a new table that you will use for the diagram.
First column use a formula to detect the rang from your very simple, first and original tables:
=WENN('B. Rang - Umsatz'!A2="";"";ZEILE()-2)
DIAGRAM DATA
1) again you start with a new table and after forming your headings, provide a formula in starting cell
RANG ARTIKEL A-Produkte B-Produkte C-Produkte
=WENN('B. Rang - Umsatz'!A2="";"";ZEILE()-2)
Lookup up the Artikel Name
=WENN(ISTTEXT(A3);"";INDEX(ABCAnalyse_Artikel_dBer;ZEILE()-2;1))
A-Produkte
=WENN(SVERWEIS(B3;ABCAnalyse_Ergebnis_dBer;5;FALSCH)="A";SVERWEIS(B3;ABCAnalyse_Ergebnis_dBer;4;FALSCH);NV())
B-Produkte
=WENN(SVERWEIS(B3;ABCAnalyse_Ergebnis_dBer;5;FALSCH)="B";SVERWEIS(B3;ABCAnalyse_Ergebnis_dBer;4;FALSCH);NV())
C-Produkte
=WENN(SVERWEIS(B3;ABCAnalyse_Ergebnis_dBer;5;FALSCH)="C";SVERWEIS(B3;ABCAnalyse_Ergebnis_dBer;4;FALSCH);NV())
Rang | Artikel | A-Produkte | B -Produkte | C-Produkte |
1 | HIJ234 | 14,9% | #NV | #NV |
2 | KLM236 | 29,5% | #NV | #NV |
3 | DEF991 | 39,2% | #NV | #NV |
4 | KLM235 | 48,4% | #NV | #NV |
5 | ABC124 | 56,5% | #NV | #NV |
6 | DEF993 | 64,2% | #NV | #NV |
7 | KLM239 | 70,4% | #NV | #NV |
8 | DEF988 | 75,4% | #NV | #NV |
9 | ABC126 | 79,7% | #NV | #NV |
10 | KLM237 | #NV | 83,4% | #NV |
11 | ABC127 | #NV | 85,3% | #NV |
12 | KLM234 | #NV | 87,1% | #NV |
13 | HIJ231 | #NV | 88,8% | #NV |
14 | DEF990 | #NV | 90,4% | #NV |
15 | KLM238 | #NV | 91,9% | #NV |
16 | ABC125 | #NV | 93,2% | #NV |
17 | ABC123 | #NV | 94,4% | #NV |
18 | HIJ232 | #NV | #NV | 95,5% |
19 | HIJ233 | #NV | #NV | 96,5% |
20 | DEF987 | #NV | #NV | 97,4% |
21 | DEF994 | #NV | #NV | 98,3% |
22 | HIJ236 | #NV | #NV | 98,9% |
23 | DEF989 | #NV | #NV | 99,4% |
24 | DEF992 | #NV | #NV | 99,6% |
25 | ABC128 | #NV | #NV | 99,9% |
26 | HIJ235 | #NV | #NV | 100,0% |
For the graph itself you can ignore the column rang and take columns for Product or Article Name, and A, B, & C.
You have pulled %´s so your Y axis will reflect cummulative % and first available Saule diagram.