Сравнение диапазона со значением не приводит к массиву при использовании внутри SUM()

Question

Что-то в функции SUM() в Excel мне не понятно.

Я могу суммировать диапазон, как это: =SUM(A2:A4) .

Я могу суммировать список диапазонов: =SUM(A2, A3:A4) .

Я могу суммировать массив: =SUM({1, 2, 3}) (возвращает 6).

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

=SUM(-{TRUE,TRUE,TRUE}) (возвращает -3).

НО, следующее не сработает, и я не знаю почему:

=SUM(--(A2:A7>0.5))

Но , не будет работать , я имею в виду результат либо сообщение об ошибке, 0 или 1 Я ожидал, что результат будет работать как COUNTIF() , где результат сообщит о количестве экземпляров в A2:A7 , значение которых> 0,5.

Насколько я понимаю, --(A2:A7>0.5) должен возвращать массив, или я так думал.

Например, используя SUMPRODUCT() , которая принимает диапазоны или массивы, я могу сделать =SUMPRODUCT(A2:A4, B2:B4, --(A2:A4>0.5)) и результат будет являться дополнительным результатом тех столбцов, где значение в столбце A > 0,5.

Я знаю о SUMIFS() и не ищу решения. Мне просто любопытно и я ищу какое-то объяснение того, что на самом деле возвращает что-то вроде --(a2:a7>0.5) , и почему функция SUM() не может с этим справиться.

Answer 1

Действительно =(a2:a7>0.5) вернет массив значений.

Элементы массива будут TRUE или FALSE или ошибкой, если ячейка содержит ошибку.

--(a2:a7>0.5) вернет массив 1 , 0 или ошибку, если применимо.

sum(--(a2:a7>0.5) , при обычном вводе, будет возвращать разные значения в зависимости от расположения формулы относительно a2:a7. Это значение будет равно 1 , 0 , сообщению об ошибке в массиве или #VALUE! ошибка.

sum(--(a2:a7>0.5) при вводе в виде формулы массива, удерживая Ctrl + Shift при нажатии Enter, отобразит сумму массива. Однако, если массив содержит ошибку, тогда формула вернет ошибку.

Answer 2

Все это происходит от того, что происходит, когда вы используете диапазон из нескольких ячеек в формуле, где ожидается одна ячейка / значение. При этом Excel использует одну ячейку в диапазоне нескольких ячеек, соответствующую строке ячейки, в которую вводится формула, если диапазон является столбцом (или его частью), или ячейкой, соответствующей столбцу ячейка формулы, если диапазон представляет собой строку (или ее часть). Он не возвращает массив.

Если соответствующая строка / столбец отсутствует или указан двумерный диапазон, то #VALUE! используется вместо

Так, например, если вы введете формулу =--(A2:A7>0.5) в C5 , Excel оценит ее как =--(A5>0.5) , так как сравнения обычно принимают отдельные значения, и вы указали несколько ячеек спектр. Если вы введете формулу в A9 , она будет оцениваться как =--(#VALUE!>0.5) .

Теперь, поскольку SUM() может принимать в качестве аргументов либо диапазоны / массивы из нескольких ячеек, либо отдельные ячейки / значения в сочетании с тем фактом, что при сравнении, как правило, между отдельными значениями они возвращают одно значение, Excel оценивает =SUM(--(A2:A7>0.5)) as =SUM(--(A5>0.5)) (при вводе в любом месте в $5:$5 , конечно).

Вот почему вы получите либо 0 либо 1 (или ошибку, если соответствующая ячейка в $A:$A содержит ошибку), если формула введена в любом месте в $2:$7 , и #VALUE! в противном случае ошибка (так как =SUM(--(#VALUE!>0.5)) оценивается как # #VALUE!).

Причина, по которой работает =SUMPRODUCT(A2:A4, B2:B4, --(A2:A4>0.5)) заключается в том, что SUMPRODUCT() предназначен для работы с аргументами диапазона / массива из нескольких ячеек. (Тем не менее, он все равно будет работать с отдельными ячейками / значениями / однозначными массивами.) Таким образом, он рассматривает сравнения как многозначные с результатом массива.

---

Есть два способа заставить SUM() работать желаемым образом:

1. Метод INDEX()

Этот метод использует тот факт, что INDEX() ожидает многосотовый диапазон в качестве первого аргумента, и что INDEX(array,0) (или мой предпочтительный INDEX(array,)) возвращает весь массив. Таким образом, сравнение в INDEX(--(A2:A7>0.5),) является многозначным, и =SUM(INDEX(--(A2:A7>0.5),)) работает как требуется.

2. Метод формулы массива

Сравнение и другие операторы можно принудительно рассматривать как многозначные, введя формулу =SUM(--(A2:A7>0.5)) в качестве формулы массива. Это делается с помощью Ctrl+Shift+Enter вместо просто Enter при вводе формулы.