Рассчитайте медиану на основе количества значений.

У меня есть данные о том, сколько у нас конкретных процессоров и их показатели CPU. На основе этого я хочу динамически рассчитывать медиа- и квартильные статистики.

Например, у меня есть таблица, которая выглядит так:

Название                     Количество  Оценки
i7-5820k 3,3 GHz 6 ядер     11         12,996
i7-950 3,1 GHz 4 ядра       4          5595
i7-3820 3,6 GHz 4 ядра      7          8998
i5-3570k 3,4 GHz 4 ядра     1          7153

Что я хочу сделать с данными, так это повторить оценки из первой строки 11 раз, из второй 4 раза и так далее.

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

Отвлекаясь от математики, вы можете использовать следующую массивную формулу :

=MEDIAN(IF(COLUMN(A:Z)<=B2:B5,C2:C5))

CtrlShiftEnter

Примечания:

• IF(COLUMN(A:Z)<=B2:B5,C2:C5) генерирует двумерный массив 4 строки, 26 столбцов, где каждая строка повторяет ячейку в столбце C столько раз, сколько указано в ячейке столбца B; оставшиеся значения в строке – это FALSE.

• Функция MEDIAN игнорирует ячейки FALSE и будет оценивать фактические значения в сгенерированном двумерном массиве.

В Excel есть функция QUARTILE(), которую вы можете использовать. Но ваш набор данных достаточно мал, чтобы вы могли получить ответ почти интуитивно.

Во-первых, переместите строку с процессорами i7-950 в конец списка. Всего 23 процессора, так что медиана – это оценка 11.5-го ранжированного процессора. Аналогично, первый квартиль – это оценка 5.75-го ранжированного процессора.

В вашем случае оба ответа равны 8998. Это немного странно, но связано с распределением ваших данных. И первый, и второй квартиль попадают в одну и ту же “категорию”. Представьте, если бы все в США весили 150 фунтов. Тогда медианный вес и все процентили были бы 150 фунтов.

Вы МОЖЕТЕ утверждать, что ваши значения квартиля и медианы оба находятся между точками данных, и затем интерполировать между точками данных. Тогда вы можете рассчитать квартиль как 3/4 пути между 5595 и 8998. А медиану как середину между 8998 и 12,996.

Я получаю 8197.25 и 10,997.

Чтобы увидеть, что думает функция QUARTILE(), создайте столбец с 11 значениями 12,996, 7 значениями 8998, 1 значением 7153 и 4 значениями 5595.

Затем используйте QUARTILE(диапазон,1) и QUARTILE(диапазон,2), чтобы рассчитать первый квартиль и медиану. Здесь “диапазон” – это список из 23 оценок.

ИЗМЕНЕНИЕ: интерполяция здесь не совсем уместна, потому что оценки процессоров все точные, а не средние значения диапазона оценок. Я ДУМАЮ. Я предполагаю, что вы посмотрели опубликованные значения показателей производительности для каждого процессора.

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

Комментарии приветствуются.

Другой метод вычисления медианы (который не полагается на расширение подсчетов) – это отсортировать строки по значению, добавить столбец для накопительных подсчетов и использовать функции XMATCH и INDEX, чтобы определить значение, соответствующее центральному наблюдению (ям). Учитывая следующее в ячейках A1:C5,

Значение  Количество  Накоп. количество
 5595      4           4
 7153      1           5
 8998      7          12
12996     11          23

Следующая формула вычисляет медиану (пробелы добавлены для читаемости):

IF(ISODD(C5),
  INDEX(A2:A5, XMATCH(C5/2, C2:C5, 1)),
  AVERAGE(
    INDEX(A2:A5, XMATCH(C5/2, C2:C5, 1)),
    INDEX(A2:A5, XMATCH(C5/2+1, C2:C5, 1))))

Вы, вероятно, сможете адаптировать это для первого и третьего квартилей, но это может быть сложно.