Brauch Hilfe bei meiner Excel Tabelle

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Hallo Patrick,
eine ABC-Analyse hab ich sonst auch noch komplett rumliegen, vielleicht nimmt es dir ja Arbeit ab: https://www.herber.de/bbs/user/35986.xls
Die Formeln erklär ich dir trotzdem gerne:
E2: =RANG(D2;$D$2:$D$15)+ZÄHLENWENN($D$2:D2;D2)-1
Die Formel für "Rang" ist klar. Der Bereich ist in $, um die Formel kopierbar zu halten. das Zählenwenn hat nur einen Bereich in $, beim runterkopieren erweitert sich der zu durchsuchende Bereich auf $D$2:D3, $D$2:D4 usw. und zählt, wie häufig der Rang schon vorkam. Dadurch wird Rangwiederholung vermieden (Rang ergibt zB 1,2,2,4 / Rang+Zählenwenn 1,2,3,4). Da jeder Rang immer mindestens 1.mal vorkommt, am Ende nochmal -1.
Aufschlüsselung von:
F2: =BEREICH.VERSCHIEBEN($A$2;VERGLEICH(KKLEINSTE($E$2:$E$15;ZEILE(A1));$E$2:$E$15;0)-1;0;)
Zeile(A1) liefert beim runterkopieren nacheinander die Werte 1,2,3 usw.
In F5 liefert Zeile(D1) also den Wert 3, die Formel reduziert sich auf:
=BEREICH.VERSCHIEBEN($A$2;VERGLEICH(KKLEINSTE($E$2:$E$15;4);$E$2:$E$15;0)-1;0;)
KKleinste liefert den n-kleinsten Wert in einem Bereich, in F5 also den viertkleinsten Wert aus den Rängen. Dieser Wert ist 4, die Formel wird zu
=BEREICH.VERSCHIEBEN($A$2;VERGLEICH(4;$E$2:$E$15;0)-1;0;)
Vergleich liefert einen Wert, der der Zeile einer Matrix in der dieser Wert steht entspricht. =VERGLEICH(4;$E$2:$E$15;0) liefert den Wert 6. Die gesuchte 4 steht zwar in Zeile 7, aber in der 6ten Zeile der betrachteten Matrix $E$2:$E$15. Davon ziehen wir nochmal 1 ab (siehe unten), ergibt den Wert 5 und die Formel wird zu:
=BEREICH.VERSCHIEBEN($A$2;5;0)
Bereich.verschieben verschiebt von einem Ausgangswert aus erst in Zeilen, dann in Spalten. Von A2 aus 5 nach unten (darum haben wir von der 6 aus dem Vergleich 1 abgezogen) und null nach rechts ergibt A7, und in A7 steht (tataaa) das gesuchte F mit Rang 4.
Luschis Verkürzung
F2:=BEREICH.VERSCHIEBEN($A$2;VERGLEICH(ZEILE(A1);$E$2:$E$15;0)-1;0;)
macht sich zu nutzen, dass der n-kleinste Wert aus einer Rangfolge ohne Wiederholungen immer n ist, so weit hatte ich nicht gedacht :) ansonsten gleiche Funktionsweise.
Danke für die Rückmeldung, freut mich geholfen zu haben!
Gruß,
Klaus M.vdT.

Private Sub SortButton_Click() Dim Gruppeneinteilung As Integer Dim Gruppe As Integer Range("B27:B29").ClearContents For Gruppe = 27 To 29 For Gruppeneinteilung = 3 To 16 If Range("H" & Gruppeneinteilung) = Range("A" & Gruppe) Then Range("B" & Gruppe) = Range("B" & Gruppe) & Range("F" & Gruppeneinteilung) & ", " End If Next Gruppeneinteilung Next Gruppe End Sub

Hallo Patrick,
die Gruppeneinteilung ist meines erachtens korrekt. Ich schlüssel mal auf:
=WENN(SUMME($G$3:G3)/$D$18>100%-$A$22;"C";WENN(SUMME($G$3:G3)/$D$18>100%-$A$22-$A$21;"B";"A"))
=SUMME($G$3:G3)/$D$18
D18 sind die Gesamt-Anteile in %, $G$3:G3 bringt beim runterkopieren die bisher erreichte kumulierte Summe - geteilt durch Gesamtsumme bringt %.
100%-$A$22;"C";
A22 sind 5%, der C-Anteil. Wenn also die bisher kumulierte Summe 95% übersteigt, dann ist dieses Material ein C-Material.
SONST neues Wenn:
100%-$A$22-$A$21
A22 sind 5% (C), A21 sind 25% (B). Wenn die kumulierte Summe also 70% erreicht hat, dann "B", sonst "A".
In der ABC Analyse sind die ersten 70% der kumulierten Anteile A-Teile, die ersten 95% sind B Teile (wenn es keine A Teile sind), der Rest sind C-Teile.
Die Formel zieht genau das auf, aber andersrum (von C nach A). Wenn jetzt das erste Teil im Rang schon 99% ausmacht, gibts natürlich keine A und B Teile mehr. Ob dann in der Auswertung die A oder die B Teile wegfallen, ist eine reine glaubensfrage. Genauso, was passiert wenn alle Produkte genau die selben Anteile und Werte haben? Sagen wir mal, 10 Materialien mit einem Wertanteil von je 10%? Sind dann alle A oder alle C? Die Formeln rechnen stur von oben nach unten und teilen von A bis C auf, ob sinnvoll oder nicht (Material 1-7 = A, Material 8-9 = B, Material 10 = C obwohl alle gleich wertig sind)
- aber in solchen einer Konstellationen macht eine ABC Analyse auch keinen Sinn :)
Wie ich weiter oben schon sage: ist leichter nachzuvollziehen wenn die einzelnen Formelteile ausgelagert sind, aber du wolltest einzellige Formeln.
Übrigens: Der Jahresverbrauch in Stück und % ist falsch - hab mal von Hand gegen gerechnet. Ich muss jetzt ein bisschen arbeiten, ich schau heute mittag mal ob ich die Formel noch korrigiert kriege.
Kommst du mit dem VBA zurecht?
Gruß,
Klaus

Beliebteste Forumthreads (12 Monate)

Brauch Hilfe bei meiner Excel Tabelle

16 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

16
Beiträge zum Forumthread

Beiträge zu diesem Forumthread