☰

Excel MEDIAN для ARRAY с несколькими условными выражениями, использующими функции IF и AND

tag-icon • tag-icon microsoft-excel array microsoft-excel-365 if
Excel MEDIAN для ARRAY с несколькими условными выражениями, использующими функции IF и AND

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

Я знаю, что мне нужно обработать функцию MEDIAN как ARRAY (ctrl + shft + enter) с помощью функции IF, и это работает нормально, если я использую только одно условие в моем операторе IF. Однако, как только я пытаюсь включить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

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

решение1

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

Конкретно:

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

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

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

решение2

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

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

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

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

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

Связанный контент