Excel-Formel, um den oberen X-Prozentsatz eines Arrays anzuzeigen

367
PeterH

Verwenden Sie den folgenden einfachen Datensatz, beginnend mit A1, mit Kopfzeilen:

Order Time a 1 b 1 c 2 d 4 e 4 f 5 g 6 h 7 i 500 j 600

Ich möchte wissen, zu welcher Zeit 80% der Aufträge in erledigt sind.

In diesem Beispiel würde es 7 zurückgeben, da dies die acht größte Zahl ist, sodass 80% der Bestellungen in 7 Tagen oder weniger abgeschlossen sind.

Welche Formel könnte ich verwenden, um dies zu bekommen, wenn man bedenkt:

  1. Das Zeitfeld enthält Hunderte von Bestellzeiten.
  2. Das Zeitfeld wird nicht immer in der Reihenfolge sortiert.
  3. Ich würde es vorziehen, keine Helfer-Säule oder VBA zu verwenden. Sie muss in einer einzelnen Zelle enthalten sein.
  4. Ich kann eine Array-Formel verwenden. Denken Sie daran, dass das Array in der Größe variieren kann, sodass das Array dies berücksichtigen muss.

Ich habe versucht es zu verwenden, =PERCENTILE.EXC(B2:B11,0.8)aber das hat einen Wert von 401.4 bestätigt

Ich habe es auch versucht, =PERCENTILE.INC(B2:B11,0.8)aber das ergab 105,6

2

1 Antwort auf die Frage

3
robinCTS

Die Lösung ist ziemlich unkompliziert. Es erfordert die SMALL()Funktion:

Arbeitsblatt-Screenshot

Geben Sie die folgende Formel ein D2:

=SMALL(B2:B11,ROWS(B2:B11)*0.8)

Beachten Sie, dass die LARGE()Funktion auch verwendet werden kann, aber nicht so elegant ist:

=LARGE(B2:B11,ROWS(B2:B11)*(1-0.8)+1)
Ich hatte gerade so etwas, meine volle Formel ist jetzt `= SMALL (WENN (Raw_Data! $ G $ 2: $ G $ 3942 = Pivot! $ B4, Raw_Data! $ I $ 2: $ I $ 3942," "), COUNTIF (Raw_Data! G: G, Pivot! B4) * $ U $ 2) `als Array eingegeben. wo COUNTIF ROWS usw. ersetzt und U2 anstelle von 0,8 steht PeterH vor 6 Jahren 0
Ich verwende auch WENN in der Spalte Bestellung, da ich für alle Auftragstypen usw. Top x Prozent erhalten möchte. Vielen Dank für Ihre Eingabe, wird als korrekt akzeptiert PeterH vor 6 Jahren 0
@ PeterH Keine Probs. Sie können auch die `LARGE ()` - Funktion verwenden, wie Sie vom Bearbeiten bis zur Antwort sehen können. robinCTS vor 6 Jahren 0
@PeterH, Sie können dies auch verwenden, `= SMALL (H115: H124, ROWS (INDIRECT (" 1:10 ")) * 0.8)` wobei "1:10" bis "1: N" steht, wobei N die Anzahl von ist Werte im Bereich. Rajesh S vor 6 Jahren 0
@ fixer1234 Normalerweise mache ich das (zumindest für die komplizierteren Lösungen), aber ich verstehe, worauf es ankommt. Wenn Sie es nicht angesprochen hätten, hätte ich vielleicht die einfache Verwendung der `SMALL ()` - Funktion für zu einfach gehalten, um sie zu erklären, selbst wenn PeterH ein Anfänger war, wie man es nur aus der Excel-Formelhilfe verstehen kann. Was für uns grundlegend und selbstverständlich ist, ist für andere nicht unbedingt so ;-) robinCTS vor 6 Jahren 0

Verwandte Probleme