Функция квартиля Excel не работает

Question

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

Например, давайте использовать этот набор данных: {4,6,8,12,14,16}

В этом примере правильные значения:

• Медиана: 10
• Q1: 6
• Q3: 14

Однако, когда вы используете Excel, вы получите следующие результаты с помощью median() и quartile.exc():

• Медиана: 10
• q1: 5.5
• Q3: 14,5

Я неправильно подхожу к этому, или Excel просто не оборудован для обработки квартилей с четным числом?

Answer 1

Встроенная в Excel функция квартиля использует интерполяцию для вычисления квартилей. Ну, как он находит 5.5 и 14.5 в вашем примере? Учитывая, что ваш размер выборки (n) равен 6, он вычисляет первый квантиль следующим образом:

 = (n + 1) / 4 = 7 / 4 = 1.75

Когда 1,75 попадает между значениями 1 и 2, Excel интерполирует данные для получения результата 5.5.

Он рассчитывает третий квантиль следующим образом:

 = 3 * (n + 1) / 4 = 21 / 4 = 5.25

Так как 5.25 попадает между значениями 5 и 6, Excel интерполирует данные для получения результата 14.5.

Простой макрос может быть написан для достижения желаемых результатов. Использование функции ROUND() для указанных выше значений 1,75 и 5,25 сгенерирует Q1 и Q3 как 2-й и 5-й элементы вашего набора данных, а именно 6 и 14.

Относительно того, почему Excel ведет себя так, нет универсального соглашения о выборе квартильных значений. В Excel используется метод 2, а в вашем примере - метод 1.

Answer 2

Краткое объяснение состоит в том, что Excel рассчитывает квартили как процентили. Это действительно очень отличается от того, как мы обычно думаем о квартилях (как медианы верхней / нижней половины данных). Вот краткое объяснение того, как Excel делает то, что он делает, используя ваши данные в качестве примера. Я не могу быть уверен на 100%, что это именно тот алгоритм, который использует Excel, но это даст те же результаты.

1. Excel назначает PERCENTILES каждому значению в массиве.
   P(4) = 0; Р (6) = 0,20; Р (8) = 0,40; ... ; Р (16) = 1

2. Затем Excel проверяет, где запрошенный процентиль попадает в массив. Для Q1 0,25 падает между 6 и 8.

3. Excel затем линейно интерполирует между этими значениями на основе процентиля.
   0,25 процентиля на 0,05 процентиля выше 0,20 процентиля.
   0,05/(P(8) -P(6)) = 0,05/0,20 = 1/4
   Таким образом, 25-й процентиль составляет 1/4 от 6 до 8. Таким образом, 6.5 является возвращаемым значением. (Я понимаю, что вы набрали 5.5, но я проверил ваши данные в Excel, а 6.5 вернул квартиль. Аналогичным образом, 13,5 возвращается для Q3 вместо 14,5.)

Это, конечно, странный способ расчета квартиля и его нельзя найти на странице Википедии о квартилях.

Теперь, чтобы найти квартиль так, как вы хотите - у меня есть два предложения.

1. Попробуйте надстройку пакета статистики. Я не установил его здесь на моем рабочем компьютере, но стоит попробовать, чтобы он возвращал значения квартилей, отличные от возвращаемых функцией листа.

2. Вы можете использовать взломанную формулу. Это грязно, но я думаю, что это захватывает то, что вы ищете.

Для Q1 вы можете использовать:

=IF(ISEVEN(ROUNDDOWN(COUNT(A1:A8)/2,0)),AVERAGE(SMALL(A1:A8,ROUNDDOWN(COUNT(A1:A8)/2,0)/2),SMALL(A1:A8,ROUNDDOWN(COUNT(A1:A8)/2,0)/2+1)),SMALL(A1:A8,ROUNDUP(ROUNDDOWN(COUNT(A1:A8)/2,0)/2,0)))

Для Q3 вы можете использовать:

=IF(ISEVEN(ROUNDDOWN(COUNT(A1:A8)/2,0)),AVERAGE(LARGE(A1:A8,ROUNDDOWN(COUNT(A1:A8)/2,0)/2),LARGE(A1:A8,ROUNDDOWN(COUNT(A1:A8)/2,0)/2+1)),LARGE(A1:A8,ROUNDUP(ROUNDDOWN(COUNT(A1:A8)/2,0)/2,0)))

Answer 3

В Excel 2010 представлены QUARTILE.INC и QUARTILE.EXC.

QUARTILE.INC аналогичен старой функции Excel QUARTILE и выполняет интерполяцию на основе N-1, тогда как QUARTILE.EXC сопоставляет функцию, используемую в Minitab и некоторых других пакетах статистики, и выполняет интерполяцию на основе N+1.

Обратите внимание, что ни один из них не дает ожидаемых значений. Это можно сделать путем интерполяции на основе N, но вы, вероятно, думаете об оригинальном методе Тьюки, который является одним из нескольких методов "шарнира" для определения квартилей.

Если вы хотите узнать больше, я написал обширное руководство по расчету квартилей, Quartiles for Box Plots, с акцентом на использование Excel. Статья в Википедии, цитируемая где-то еще в этой теме, довольно упрощенная.