У Яны есть рабочий лист со списком возрастов в столбце A. Возраст начинается с 1 и продолжается до 100. В столбце B у нее указано количество людей в каждом из этих возрастов. Ян нужна формула, которая скажет ей средний возраст этой группы людей.

На первый взгляд вы можете подумать, что для вычисления медианы можно использовать функцию МЕДИАНА. Эта функция отлично работает, если у вас есть простой список значений. Например, если вы рассчитываете медианное значение только для возрастов, то MEDIAN будет работать нормально. Однако в случае Яны ей нужно среднее значение для возраста людей, а не для самих возрастов. Другими словами, медиана должна быть взвешена по количеству людей каждого возраста. Функция МЕДИАНА не может справиться с таким требованием.

Следует отметить, что средний возраст будет отличаться от среднего возраста для группы людей. Среднее значение проще всего рассчитать, умножив возраст на количество людей каждого возраста. Например, в столбце C вы можете поместить формулу, такую ​​как = A1 * B1, а затем скопировать ее в столбец. Сложите значения в столбцах B и C, а затем разделите сумму в столбце C на сумму в столбце B. Результатом будет средний возраст для списка людей.

С другой стороны, средний возраст — это возраст, в котором половина людей находится ниже этого возраста, а половина — выше этого возраста. Средний возраст лучше всего рассчитать с помощью формулы массива, например следующей:

=MATCH(SUM($B$1:$B$100)/2,SUMIF($A$1:$A$100, "<="&$A$1:$A$100,$B$1:$B$100))

Это единственная формула, вводимая нажатием Ctrl + Shift + Enter. Функция СУММЕСЛИ в формуле используется для создания массива совокупного числа людей, которые меньше или равны каждому возрасту. Часть формулы SUM дает среднее значение общей частоты возрастов. Затем функция ПОИСКПОЗ используется для поиска среднего значения в массиве накопленных частот. Это дает «порядковый номер» в начальном массиве, и, поскольку массив состоит из всех возрастов от 1 до 100, этот индексный номер соответствует среднему возрасту.

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (10595) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365.