С помощью Excel, учитывая сгруппированные данные, как оценить квартили?

Question

Предположим, у меня есть следующие сгруппированные данные по массе собак:

Mass         Frequency

0 to 5          13

6 to 10         28

11 to 15        47

16 to 20        21

21 to 25        11

25 to 30         6

Как я могу использовать Excel для оценки первого квартиля?

Я знаю, как сделать ось (используя точечную диаграмму с точками, соединенными плавной кривой), и я могу использовать огив и мои глаза (и, возможно, линейку на экране), чтобы приблизительно найти первый квартиль. Но может ли Excel дать мне более точный ответ?

Я не хочу использовать функцию "Добавить линию тренда", потому что линия тренда на самом деле не является уклоном (линия тренда не проходит через все точки).

Answer 1

Графически вы хотите создать гистограмму с установленными "квартирами" для квартилей. Вы можете посмотреть, как это сделать.

Кроме того, вы можете использовать формулу. Я опишу это словами, и вы сможете понять математику.

Есть 126 собак. 25% от этого - 31,5. Вы хотите найти вес, который весит 31,5 самых легких собак меньше, чем.

В вашей самой легкой корзине 13 собак, поэтому они учитываются. Это оставляет 18,5 собак, но в следующем мусорном ведре 28 собак. Итак, вопрос в том, каков вес 18,5-й самой легкой собаки во втором мусорном ведре?

Используя то, что называется линейной интерполяцией, вы можете оценить, что неизвестный вес составляет 18,5/28 пути от 6 до 10 фунтов. Это 6+4 *(18,5/28).

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

Язык здесь немного неловкий, но вы поняли идею. Надеюсь, это поможет.

РЕДАКТИРОВАТЬ: Если вы знаете вес всех отдельных собак, просто ранжируйте их по порядку и рассчитайте средний вес 31-й и 32-й собак.

Answer 2

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

Используйте функцию REPT() для вывода списка чисел, разделенных запятыми, представляющих каждую группу.

=REPT(B2&",",C2)  --- where B2 is your group upper bound and C2 is the group frequency

Внизу объедините каждую из этих строк в одну большую строку:

Скопируйте эту строку и вставьте Special as Values в новый лист. Используйте инструмент « Текст в столбцы» на вкладке «Лента данных », чтобы разбить данные на один столбец для каждого значения. Скопируйте весь диапазон, затем вставьте Special и Transpose, чтобы перевернуть его в вертикальный список значений. Ваши данные должны выглядеть примерно так:

Не стесняйтесь отбрасывать горизонтальный ряд - нам это не нужно. Теперь мы хотим интерполировать некоторые значения в столбце B, используя формулу примерно так:

=(5*COUNTIF($A$4:A5,A5)/COUNTIF($A$4:$A$5000,A5))+(A5-5)

Разбивая это, мы имеем:

    =(5*                         -- your groups are increments of 5
       COUNTIF($A$4:A5,A5)       -- how far down a row is within a group
       /                         
       COUNTIF($A$4:$A$5000,A5)  -- what the frequency is for that group
      )                          
      +(A5-5)                    -- add this result to the lower bound

Теперь вы можете использовать функцию QUARTILE() в этом списке оценочных значений для аппроксимации ваших квартилей:

=QUARTILE($B$5:$B$130,1)