☰

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

tag-icon • tag-icon microsoft-excel
Как оценить квартили в Excel, учитывая сгруппированные данные?

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

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 дать мне более строгий ответ?

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

решение1

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

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

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

Используйте РЕПТ

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

Длинная строка

Скопируйте эту строку иСпециальная вставкакакЦенностина новом листе. ИспользуйтеТекст в столбцыинструмент наДанныеВкладка ленты, чтобы разделить данные на один столбец для каждого значения. Скопируйте весь этот диапазон, затемСпециальная вставкаиТранспонироватьчтобы перевернуть это в вертикальный список значений. Ваши данные должны выглядеть примерно так:

транспонированные данные

Можете смело отбросить горизонтальную строку — она нам не нужна. Теперь мы хотим интерполировать некоторые значения в столбце 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)

введите описание изображения здесь

решение2

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

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

Собак 126. 25% из них — 31,5. Вы хотите найти вес, меньше которого будут весить 31,5 самых легких собак.

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

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

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

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

EDIT: Если вам известен вес всех собак, просто расположите их по порядку и вычислите средний вес 31-й и 32-й собак.

Связанный контент