Формула Excel, чтобы показать топ X процент массива

259
PeterH

Используя простой набор данных ниже, начиная с A1, с заголовками:

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

Я хочу знать, в какое время выполнено% 80 заказов.

В этом примере будет возвращено 7, так как это наибольшее число из восьми, поэтому% 80 заказов выполняется за 7 дней или меньше.

Какую формулу я мог бы использовать, чтобы получить это, учитывая:

  1. Временной массив будет содержать 100 секунд времени заказа.
  2. Массив времени не всегда будет отсортирован по порядку.
  3. Я бы предпочел не использовать вспомогательный столбец или VBA, он должен содержаться в одной ячейке.
  4. Я могу использовать формулу массива, просто помните, что массив может различаться по размеру, поэтому массив должен учитывать это.

Я пытался использовать, =PERCENTILE.EXC(B2:B11,0.8)но это восстановило значение 401,4

Я тоже пытался =PERCENTILE.INC(B2:B11,0.8)но вернул 105.6

2

1 ответ на вопрос

3
robinCTS

Решение довольно простое. Требуется SMALL()функция:

Worksheet Screenshot

Введите следующую формулу в D2:

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

Обратите внимание, что LARGE()функцию также можно использовать, но она не так элегантна:

=LARGE(B2:B11,ROWS(B2:B11)*(1-0.8)+1)
Я только что получил что-то подобное, моя полная формула теперь `= SMALL (ЕСЛИ (Raw_Data! $ G $ 2: $ G $ 3942 = Pivot! $ B4, Raw_Data! $ I $ 2: $ I $ 3942," "), COUNTIF (Raw_Data! G: G, Pivot! B4) * $ U $ 2) `введен в виде массива. где COUNTIF заменяет ROWS и т. д., а U2 вместо 0.8 PeterH 5 лет назад 0
Я также использую IF в столбце «Заказ», так как хочу получить максимальный процент процентов для всех типов заказов и т. Д. Спасибо за ваш вклад, приму как правильный PeterH 5 лет назад 0
@PeterH Никаких проблем. Вы также можете использовать функцию `LARGE ()`, как вы можете видеть из редактирования моего ответа. robinCTS 5 лет назад 0
@PeterH, вы также можете использовать это, `= МАЛЕНЬКИЙ (H115: H124, ROWS (INDIRECT (" 1:10 ")) * 0.8)` где от "1:10" до "1: N", где N - число значения в диапазоне. Rajesh S 5 лет назад 0
@ fixer1234 Обычно я делаю (по крайней мере, для более сложных решений), но я понимаю вашу точку зрения. Если бы вы не подняли этот вопрос, я, возможно, все еще считал бы прямое использование функции SMALL () слишком базовым для объяснения, даже если PeterH был новичком, как это можно понять только из справки по формулам Excel. Однако то, что является основным и очевидным для нас, не обязательно так для других ;-) robinCTS 5 лет назад 0

Похожие вопросы