Excel МЕДИАНА по МАССИВУ с несколькими условными выражениями с использованием функций ЕСЛИ и И

Я хочу определить МЕДИАНУ значений в одном столбце на основе условий выполнения в двух других столбцах. Я использую MS Excel.

Я знаю, что мне нужно обработать функцию МЕДИАНА как МАССИВ (ctrl + shft + enter) с помощью функции ЕСЛИ, и это работает нормально, если я использую только одно условие в своем операторе ЕСЛИ. Однако как только я пытаюсь объединить 2 условия с помощью функции И, я получаю медиану 0 (т. е. это не работает).

Как рассчитать медиану значений в одном столбце на основе условий совпадения более чем в одном столбце?

Пример :

Допустим, я хочу найти медиану значений в C, когда A = 1 и B = x:

      A   B   C
1   x   10
1   x   20
1   y   30
1   y   40
2   x   10
2   x   20
2   y   30
2   y   40
3   x   10
3   x   20
3   y   30
3   y   40
4   x   10
4   x   20
4   y   30
4   y   40
5   x   10
5   x   20
5   y   30
5   y   40

#Here is my attempt using IF and AND:
{=MEDIAN(IF(AND(A2:A21=1,B2:B21="x"),C2:C21))}    #DEOSN'T WORK: should be 15, but it's 0

#subcomponents of above work fine on their own:
{=MEDIAN(IF(A2:A21=1,C2:C21)) }    #equals 25 as expected
{=MEDIAN(IF(B2:B21="x",C2:C21))}   #equals 15 as expected

Как мне заставить эту работу работать и/или есть ли другой подход, который мне следует использовать??

Источник

2 ответа

Избегайте этой функции, используйте вложенныеIFвместо этого.

Конкретно:

      =MEDIAN(IF(A2:A21=1,IF(B2:B21="x",C2:C21)))

Проблема в том, что в контексте массива это не работает так, как вы намереваетесь - он не обрабатывает каждую пару элементов и не создает массив, а объединяет И все элементы обоих массивов, чтобы дать один скалярный результат.

Ваша исходная формула оценивает всеANDвызов одного вывода "FALSE" (потому что не каждый элемент в обоих массивах удовлетворяет сравнению).

Источник

Я не знаю, почему это работает, но, повозившись, я обнаружил, что моей цели достигает следующее:

      {=MEDIAN(IF((A2:A21=1)*(B2:B21="x"),C2:C21))}   #gives correct 15

(Опять же, не забывая обрабатывать его как массив, используя ctrl + shft + enter)

Я предполагаю, чтоANDфункция выдает только одинTRUEилиFALSEпо умолчанию и, следовательно, не может работать в этом контексте (что требует создания массива из единиц и нулей). Однако если я обрабатываю каждый столбец как отдельный массив, я создаю строку из 1 и 0 для каждого подусловия. Если я затем умножу несколько условных массивов, результатом будет сам массив единиц и нулей, в котором единицы существуют только тогда, когда желаемое условие было истинным для обоих соответствующих подусловий.

Я уверен, что кто-то сможет подтвердить/объяснить это в комментарии.

Источник
Другие вопросы по тегам