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

Чтобы выполнить этот тип усреднения, все, что вам нужно помнить, это то, что среднее значение рассчитывается путем суммирования всех значений в диапазоне и последующего деления этой суммы на количество элементов в этом диапазоне. Функция СУММ позволяет легко вычислить сумму, а функцию СЧЁТ можно использовать для определения количества элементов в диапазоне. Как исключить два самых низких значения из диапазона? Вы можете использовать функцию МАЛЕНЬКИЙ.

Рассмотрим следующую формулу, которая предполагает, что вы хотите найти скорректированное среднее значение диапазона A10: A14:

=(SUM(A10:A14)-SMALL(A10:A14,1)-SMALL(A10:A14,2))/(COUNT(A10:A14)-2)

Функция МАЛЕНЬКИЙ используется для определения двух наименьших значений в диапазоне, и они вычитаются из общей суммы диапазона. Полученное значение затем делится на КОЛИЧЕСТВО значений в диапазоне.

Также обратите внимание, что значение COUNT уменьшено на 2, чтобы компенсировать тот факт, что вы игнорируете два самых низких значения.

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

Следующее делает свое дело:

=AVERAGE(IF(A10:A14>SMALL(A10:A14,2),A10:A14))

Поскольку это формула массива, вам нужно ввести ее, нажав Ctrl + Shift + Enter, а не просто нажав Enter. В этой формуле по-прежнему используется функция МАЛЕНЬКИЙ, но для возврата результата также используется фактическая функция СРЕДНИЙ. Поскольку это формула массива, она проверяет каждое из значений в массиве (диапазоне) и рассматривает их для использования в среднем только в том случае, если они больше второго наименьшего значения в массиве.

Хотя формула массива короче, чем более длинная обычная формула, следует иметь в виду одно предостережение: формула массива приведет к нежелательному результату, если существует двусторонняя «связь» во втором наименьшем значении в диапазоне, или трехсторонняя связь с наименьшим значением. Например, если усредняются значения 3, 2, 10, 3 и 7, то формула массива даст среднее значение 8,5. Зачем? Потому что только значения 10 и 7 выше второго по величине значения, а среднее из этих двух составляет 8,5.

Если вы используете более длинную формулу, впервые представленную выше, то возвращается среднее значение 6,666667, что является средним значением 10, 3 и 7.

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

Этот совет (10697) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365. Вы можете найти версию этого совета для старого интерфейса меню Excel здесь:

link: / excel-Excluding_Values_from_Averaging [Исключение значений из усреднения].