Вычисление медианы столбца для строк, где 2 других столбца соответствуют критериям

Question

К сожалению, Excel не позволяет вычислять медиану в сводных таблицах, поэтому я пытаюсь сделать что-то подобное с формулой.

Для моего примера у меня есть 5 столбцов данных (AE).

Мои столбцы A и B имеют разные переменные классификации данных. Столбец E содержит данные, для которых мне нужна медиана. Однако я хочу выполнить фильтрацию по определенным значениям из A & B, чтобы вычислить медиану для строк только с теми отфильтрованными значениями (как в сводной таблице)

Любой знает лучшую формулу, которая, говоря, когда A = x и когда B = y, вычисляет медиану столбца E. Так что только для строк, которые A = x и B = y, остаются в силе. Я попробовал несколько формул ЕСЛИ, но они не дают правильного ответа.

благодарю вас!

Answer 1

Из файлов справки для MEDIAN мы видим, что «Если массив или ссылочный аргумент содержат текст, логические значения или пустые ячейки, эти значения игнорируются; однако ячейки с нулевым значением включаются». Таким образом, одним из решений было бы (скажем, в столбце F) установить формулу IF, которая возвращает значение в столбце E, если критерии выполнены, и "" или аналогичное, если критерии не выполнены. Тогда просто возьмите медиану столбца F.

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

Что дает требуемый результат:

Это не блестящее решение, но работоспособное. Вы можете очень четко увидеть, какие ячейки были выбраны, поэтому вы можете сразу увидеть, правильно ли вы использовали IF , но недостатком является то, что вы можете предпочесть не использовать столбец таким образом.

"Умнее" метод заключается в использовании формулы массива, но это не дает вам такой визуальной проверки, какие ячейки вы выбрали:

=MEDIAN(IF((A2:A9="Dog")*(B2:B9="Brown"),E2:E9,""))

Не забудьте нажать CONTROL-SHIFT-ENTER, чтобы завершить ввод формулы массива; одних только клавиш ввода недостаточно. IF в формуле массива эффективно выбирает только те ячейки в E2:E9, которые соответствуют критериям, и тогда MEDIAN будет применяться только к тем ячейкам, которые вы хотите. Это означает, что вы можете сделать что-то подобное, чтобы получить таблицу медиан в соответствии с различными критериями:

Будьте внимательны с абсолютными и относительными ссылками на ячейки, чтобы упростить перетаскивание формулы! Например, формула массива в H2 должна быть следующей; обратите внимание, что в H$1 строка фиксирована, но столбец будет меняться при перетаскивании, и наоборот для $G2 .

=MEDIAN(IF(($A$2:$A$9=H$1)*($B$2:$B$9=$G2),$E$2:$E$9,""))

Это дает удобную таблицу результатов.