В этом руководстве будет показано, как рассчитать медианное значение значений, соответствующих определенным критериям в Excel и Google Таблицах.
Функция МЕДИАНА
Функция МЕДИАНА вычисляет медианное значение в серии чисел.
= МЕДИАНА (C2: C10)
Однако нет встроенной функции «Median If» для вычисления среднего значения только для чисел, которые соответствуют определенным критериям.
Примечание. Возможно, вы знакомы с функцией СРЗНАЧЕСЛИМН, которая вычисляет среднее значение для чисел, соответствующих определенным критериям. Срединной альтернативы нет.
Median If - формула массива
Итак, для достижения нашей цели по вычислению среднего значения чисел, отвечающих определенным критериям, мы должны использовать формулу массива. В нашей формуле массива мы будем использовать функцию ЕСЛИ внутри функции МЕДИАНА, чтобы мы брали только медиану значений, которые соответствуют критериям, введенным в функцию ЕСЛИ.
Используя наш предыдущий набор данных, возьмем медианное значение для Apple.
= МЕДИАНА (ЕСЛИ ($ A $ 2: $ A $ 10 = $ E2, $ C $ 2: $ C $ 10))
Excel 2022 и более ранние версии
В Office 365 и версиях Excel после 2022 года вы можете просто ввести указанную выше формулу, как обычно (нажав ВХОДИТЬ).
Однако для Excel 2022 и более ранних версий формулу необходимо вводить, нажимая CTRL + SHIFT + ВВОД. После этого вы заметите фигурные скобки вокруг формулы:
{= МЕДИАНА (ЕСЛИ ($ A $ 2: $ A $ 10 = $ E2, $ C $ 2: $ C $ 10))}
Важно: не пишите сами эти фигурные скобки, их нужно вводить только с CTRL + SHIFT + ВВОД.
Как работает формула?
Помните, что это наша формула:
{= МЕДИАНА (ЕСЛИ ($ A $ 2: $ A $ 10 = $ E2, $ C $ 2: $ C $ 10))}
Формула работает, оценивая критерий каждого значения как ИСТИНА или ЛОЖЬ.
= МЕДИАНА (ЕСЛИ ({ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ИСТИНА; ИСТИНА; ЛОЖЬ}, {1287; 1205; 1243; 1482; 1261; 1042; 1090; 1748; 1909}))
Затем функция IF заменяет каждое значение на FALSE, если его условие выполнено.
= МЕДИАНА ({ЛОЖЬ; 1205; ЛОЖЬ; ЛОЖЬ; 1261; ЛОЖЬ; 1090; 1748; ЛОЖЬ})
Теперь функция МЕДИАНА игнорирует значения ЛОЖЬ и вычисляет медиану оставшихся значений (1233 - это среднее из двух средних значений: 1205 и 1261).
Медиана IF - множественные критерии
Вы также можете рассчитать медиану на основе нескольких критериев с помощью логической логики.
Давайте посмотрим на новый набор данных со столбцами Location, Year и Stock:
Вот формула для расчета медианы на основе нескольких критериев:
= МЕДИАНА (ЕСЛИ ((A2: A4 = "B") * (B2: B4 = 2008); C2: C4))
Обратите внимание, что здесь мы умножаем два набора критериев вместе:
(G8: G10 = "b") * (H8: H10 = 2008)
Если оба критерия ИСТИНА, то он будет вычислен как ИСТИНА, но если один (или несколько) критериев - ЛОЖЬ, он будет вычислен как ЛОЖЬ.
Используя эту методологию, вы можете добавить множество различных критериев.
Формула медианы If в Google Таблицах
Все приведенные выше примеры работают в Google Таблицах точно так же, как и в Excel.
