Условная медиана в MS Excel

Я пытаюсь вычислить условную медиану диаграммы, которая выглядит так:

A  |  B
-------
x  |  1
x  |  1
x  |  3
x  |  
y  |  4
z  |  5

Я использую MS Excel 2007. Я знаю инструкцию AVERAGEIF(), но для медианы нет эквивалента. Главный трюк заключается в том, что есть строки без данных - например, 4-й "а" выше. В этом случае я не хочу, чтобы эта строка вообще не учитывалась в расчетах.

Googling предложила следующее, но Excel не примет формат формул (может быть, потому, что он 2007?)

=MEDIAN(IF((A:A="x")*(A:A<>"")), B:B)

Excel дает ошибку, говоря, что что-то не так с моей формулой (что-то связано с условием *), я также пробовал следующее, но в вычислениях подсчитывает пустые ячейки как:

=MEDIAN(IF(A:A = "x", B:B, "")

Я знаю, что эти формулы возвращают массивы Excel, что означает, что нужно ввести "Ctrl-shift-enter", чтобы заставить его работать правильно.

Как я могу выполнить условную оценку и не считать пустые ячейки?

Ответ 1

Вложенные операторы if.

=MEDIAN(IF(A:A = "x",IF(B:B<>"",B:B, ""),"")

Не так много объяснять - он проверяет, является ли A х. Если это так, он проверяет, не является ли B непустым. Все, что соответствует обоим условиям, вычисляется как часть медианы.

Учитывая следующий набор данных:

A | B
------
x | 
x |     
x | 2
x | 3
x | 4
x | 5

Вышеупомянутая формула возвращает 3.5, что, как я считаю, вам нужно.

Ответ 2

Используйте формулу Googled, но вместо того, чтобы нажимать Enter после ввода ее в строку формулы, нажмите Ctrl + Shift + Enter одновременно (вместо Enter). Это помещает скобки вокруг формулы и будет рассматривать ее как массив.

Будьте осторожны, если вы его отредактируете, вы не сможете снова нажать Enter или формула не будет действительна. Если вы редактируете, вы должны сделать то же самое, когда сделали (Ctrl + Shift + Enter).

Ответ 3

Существует другой способ, который не включает формулу массива, для которой требуется операция CtrlShiftEnter. Он использует функцию Aggregate(), предлагаемую в Excel 2010, 2011 и последующие. Метод также работает для min, max и различных процентилей. Aggregate() позволяет игнорировать ошибки, поэтому трюк состоит в том, чтобы сделать все значения, которые не требуются, вызывают ошибки. Самый простой способ - выполнить поставленную выше задачу:

= Агрегат (16,6, (B: B)/((A: A = "x" ) * (B: B < > "")), 0,5)

Первый и последний параметры задают для сцены 50% -ную процентиль, которая является срединной, вторая говорит, игнорирует все ошибки (включая DIV # 0), а третий говорит, что выбирает данные столбца B и делит его на число, которое является одним для всех непустых значений, которые имеют x в столбце A, и ноль в противном случае. Нули создают деление на нулевое исключение и будут игнорироваться, потому что a/1 = a и a/0 = Div # 0

Метод работает для квартилей (с соответствующим значением p), все остальные процентили, конечно, и для max и min, используя большую или малую функцию с соответствующими аргументами.

Это аналогичная конструкция трюков Sumproduct(), которые так популярны, но которые нельзя использовать ни в каких квантилях или в значениях max min, поскольку они производят нули, которые выглядят как числа для этих функций.

Боб Джордан

Ответ 4

Возможно, чтобы обобщить это немного больше, вместо этого...

{=MEDIAN(IF(A:A="x",IF(B:B<>"",B:B)))}

... вы можете использовать следующее:

{=QUARTILE.EXC(IF(A:A="x",IF(B:B<>"",B:B)),2)}

Обратите внимание, что фигурные скобки относятся к формуле массива; вы не должны размещать скобки в своей формуле, но нажмите CTRL + SHIFT + ENTER (или CMD + SHIFT + ENTER на macOS) при вводе формулы

Тогда вы можете легко получить первый и третий квартили, изменив последнее число от 2 до 1 или 3 соответственно. QUARTILE.EXC - это то, что большинство коммерческого статистического программного обеспечения (например, Minitab) используют кстати. "Регулярная" функция - QUARTILE.INC, или для более старых версий Excel, просто QUARTILE.