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

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

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

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

Поиск в Google предложил следующее, но Excel не принимает формат формулы (может быть, потому что это 2007 год?)

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

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

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

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

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


excel median worksheet-function
person CoolUserName    schedule 12.04.2009    source источник

Ответы (4)


arrow_upward
9
arrow_downward

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

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

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

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

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

Приведенная выше формула возвращает 3,5, что, я думаю, вы и хотели.

person Cody Hatch    schedule 12.04.2009
comment
Мне пришлось сделать это формулой массива, чтобы она работала. т.е. {=MEDIAN(IF(A:A = "x",IF(B:B<>"",B:B))} у меня работает (операторы else не нужны). Тем не менее, отличный ответ! - person Therkel; 06.09.2016

arrow_upward
7
arrow_downward

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

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

person Doc    schedule 18.08.2011

arrow_upward
5
arrow_downward

Есть еще один способ, не использующий формулу массива, требующую операции CtrlShiftEnter. Он использует функцию Aggregate(), предлагаемую в Excel 2010, 2011 и более поздних версиях. Метод также работает для минимальных, максимальных и различных процентилей. 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), конечно, для всех остальных процентилей, а также для максимума и минимума с использованием большой или малой функции с соответствующими аргументами.

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

Боб Джордан

person user2092957    schedule 13.11.2014
comment
Если вы хотите избежать массивов (как обычно делаю я), решение Боба работает хорошо и может быть настроено с учетом любого количества критериев. - person NikG; 29.04.2015
comment
Это сработало для меня. Чем меньше массивов я работаю, тем лучше - person noiivice; 10.05.2018

arrow_upward
1
arrow_downward

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

{=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). «Обычная» функция — КВАРТИЛЬ.ВКЛ, а для более старых версий Excel — просто КВАРТИЛЬ.

person Stormbringer    schedule 27.07.2017