Pareto großer Datenmengen

[Thomas Schäfer]

Hallo Zusammen!

Ich habe eine Excel mit ca. 60.000 Zeilen auf der Versendungen aufgelistet werden.
Dabei gibt es verschiedene, codierte Routen zu jeder aufgenommenen Sendung. Über Nettoarbeitstage habe ich die Dauer von Auftragseingang bis Wareneingang beim Kunden berechnet. Nun möchte ich wissen, wie die durchschnittliche Dauer auf Route A oder B oder C ist, und dabei jedoch nicht den Durchschnitt über alle Werte, sondern einmal die ersten 5% der sortierten Zeilen (nicht Werte), dann die mittleren 15% und die letzten 80%.

Ich könnte nun mit Filtern arbeiten und händisch zum Ziel kommen, jedoch geht es um viele Routen in verschiedenen Tabellen (pro Land eine).


Ich bin für Anregungen in alle Richtungen von Excel offen und hoffe, dass das Problem verständlich dargestellt ist.

Grüße
Thomas

[Fennek]

Hallo,

zuerst: die Fragestellung klingt interessant und ist -vermutlich- recht gut zu bearbeiten. Aber zumindest für mich wäre es einfacher, wenn eine kleine Bsp-Datei gezeigt würde.

mfg

[Thomas Schäfer]

Hallo Fennek,

danke für die schnelle Rückmeldung! Ich habe einen exemplarischen Auszug in den Anhang gepackt.
Die Dauer - Lead Time - ist in Tagen und wird für mehrere Abschnitte betrachtet, ich vermute jedoch, dass es in der Lösung dann übertragbar ist, daher die eigentliche Lösung nicht beeinflusst.

Grüße
Thomas

[Elex]

Hi

anbei mal ein Vorschlag.

  Beispiel2.xlsx (Größe: 12,68 KB / Downloads: 4)
Gruß Elex

[Fennek]

Hallo,

Excel bietet die Funktion "Quantil", auch in der Form =aggregat(16;1;Daten;%-Wert)

Zuerst für die ganze Tabelle, aber falls gefiltert wird, nur für die gefilterten Werte. Den Autofilter kann man recht gut mit VBA steuern und müßte dann die Ergebnisse des Quantils kopieren.

mfg

[BoskoBiati]

Hi,

hier mal eine Möglichkeit, wobei ich nicht weiß, was die mit 60000 Zeilen macht.

Arbeitsblatt mit dem Namen 'Tabelle1'
	E	F	G	H
3	A	5%	54,3	1,0
4	A	15%	54,3	0,8
5	A	80%	16,2	0,5

Zelle	Formel
G3	{=MITTELWERT(WENN(($A$3:$A$27=$E3)*(B$3:B$27>=AGGREGAT(14;6;B$3:B$27/($A$3:$A$27=$E3);RUNDEN(ZÄHLENWENN($A$3:$A$27;$A4)*5%;)));B$3:B$27))}
H3	{=MITTELWERT(WENN(($A$3:$A$27=$E3)*(C$3:C$27>=AGGREGAT(14;6;C$3:C$27/($A$3:$A$27=$E3);RUNDEN(ZÄHLENWENN($A$3:$A$27;$A4)*5%;)));C$3:C$27))}
G4	{=MITTELWERT(WENN(($A$3:$A$27=$E4)*(B$3:B$27>=AGGREGAT(14;6;B$3:B$27/($A$3:$A$27=$E4);RUNDEN(ZÄHLENWENN($A$3:$A$27;$A4)*15%;)))*(B$3:B$27<AGGREGAT(14;6;B$3:B$27/($A$3:$A$27=$E4);RUNDEN(ZÄHLENWENN($A$3:$A$27;$A4)*5%;)));B$3:B$27))}
H4	{=MITTELWERT(WENN(($A$3:$A$27=$E4)*(C$3:C$27>=AGGREGAT(14;6;C$3:C$27/($A$3:$A$27=$E4);RUNDEN(ZÄHLENWENN($A$3:$A$27;$A4)*15%;)))*(C$3:C$27<AGGREGAT(14;6;C$3:C$27/($A$3:$A$27=$E4);RUNDEN(ZÄHLENWENN($A$3:$A$27;$A4)*5%;)));C$3:C$27))}
G5	{=MITTELWERT(WENN(($A$3:$A$27=$E5)*(B$3:B$27<AGGREGAT(14;6;B$3:B$27/($A$3:$A$27=$E5);RUNDEN(ZÄHLENWENN($A$3:$A$27;$A5)*20%;)));B$3:B$27))}
H5	{=MITTELWERT(WENN(($A$3:$A$27=$E5)*(C$3:C$27<AGGREGAT(14;6;C$3:C$27/($A$3:$A$27=$E5);RUNDEN(ZÄHLENWENN($A$3:$A$27;$A5)*20%;)));C$3:C$27))}

Achtung, Matrixformel enthalten!

Die geschweiften Klammern{} werden nicht eingegeben.

Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

[Thomas Schäfer]

Hallo Zusammen,

vielen Dank für die Rückmeldungen & Lösungsansätze! Die Aggregat Funktion hat zum Ziel geführt - ohne das die hohe Anzahl der Zeilen ein Problem darstellte.

Ich habe zwar die Funktion Aggregat noch nicht voll verstanden - aber morgen ist ja ein Feiertag! :D

Grüße

[Elex]

Hi Thomas,
 
ich kann den Sachverhalt selbst nicht 1:1 testen da mein Excel die AGGREGAT Funktion nicht kennt.
Glaube aber dass es unter bestimmten Voraussetzungen zu einem ungenauen Ergebnis kommen kann.  (Siehe Anhang).
 
Wie du selbst festgestellt hast ist die Formel etwas schwer zu lesen. Kann natürlich auch sein das ich mich täusche, aber ein Test kann nicht schaden.

  Zeilen Prozent.xlsx (Größe: 16,3 KB / Downloads: 2)

[Thomas Schäfer]

Hey Elex,

die Formel in Tabelle 1 funktioniert - läuft auf meinem Laptop leider etwas träge, dennoch vielen Dank!

Grüße