Использование QUARTILE в сводной таблице Excel для суммирования данных по подгруппам

У меня есть большая таблица данных в электронной таблице Excel, которая, по существу, может считаться совокупностью значений для лиц, идентифицированных как принадлежащие к различным субпопуляциям:

  IndivID   SubPopID  Value
     1          A       33.56
     2          E       42.31
     3          D       16.35
     4          A       50.59
     5          F       80.63
     ...

В этой таблице более 10 000 человек и более 50 субпопуляций.

Я хотел бы рассчитать 5-кратную сводку (Min, Q1, Median, Q3, Max) для каждой из субпопуляций.

Мин и Макс легки в сводной таблице. Но Q1, Q2 (медиана) и Q3 не кажутся возможными.

Вопрос

Есть ли способ вычислить квартили для каждой субпопуляции в сводной таблице в Excel?

Ответ должен выглядеть следующим образом:

SubPopID      Min     Q1     Q2        Q3      Max
   A         3.23    12.06   20.35   28.29     50.59
   B 
   C
   ...

Ответ 1

Мой опыт в том, что вы не можете делать процентили в сводной таблице. Я просто сделал веб-поиск и не вижу ничего, чтобы противоречить этому. Я думаю, что самое лучшее, что вы можете сделать, это получить исходные данные в таблице, чтобы вы могли ссылаться на нее динамически, а затем использовать функцию процентили в другой таблице.

Я видел одну статью об использовании PowerPivot, но доступную только в Excel 2010, и она выглядит тяжело. У меня 2010 год, и когда я столкнулся с подобной проблемой, я все же решил пойти по маршруту, который я предлагаю здесь.

EDIT: Объяснение моей предложенной альтернативы:

Отвечая на ваш вопрос в комментариях, вот как я вычислил процентили из таблицы данных:

Он опирается на одну формулу, которая одинакова для каждой ячейки от F2 до H6. Это формула массива, то есть она вводится с помощью Ctrl-Shift-Enter. Он использует инструкцию IF для фильтрации различных возможных подпотоков. Здесь формула для F2:

=PERCENTILE(IF(Table1[SubPopID]=F$1,Table1[Value],""),$E2/100)

Ответ 2

Если вам нужно проверить условие для более чем одного столбца (a = 1 AND b = 2), вы можете развернуть массив, используя символ *

=PERCENTILE((IF((Table1[SubPopID]=[condition1]) * (Table1[SubPopID2]=[condition2]),Table1[Value]),""),$E2/100)

Ответ 3

Я нашел этот answer, где описано решение.

Он использует тот же самый aproach, как описано Doug Glancy, но связывает его с вычисленным полем в сводной таблице. Ответ дает пример файла excel.