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

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

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

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

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

1 ответ1

1

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

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

Медиана только ячеек, соответствующих критериям

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

Результаты Excel для MEDIAN только для ячеек, соответствующих критериям

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

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

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

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

Таблица медиан Excel по различным критериям

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

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

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

Всё ещё ищете ответ? Посмотрите другие вопросы с метками .