Преобразование многошаговой суммы логических значений в одну формулу

Question

У меня есть несколько показаний датчика, и я проверяю, находятся ли значения в приемлемом диапазоне.

Для этого я использую IF чтобы проверить, являются ли значения > или < расчетное (среднее) значение. Результаты сохраняются в соответствующих столбцах. Наконец, я суммирую результаты, чтобы подсчитать, сколько из них выходит за пределы (т.е. выше среднего).

Например, Ax сравнивается с Mean .Ax чтобы получить 1 или 0 в If value is outside accepted bounds .Ax:

Тогда сумма If value is outside accepted bounds .Ax выполняется, чтобы получить Number of values outside bound .Ax:

Вопрос
Как мне преобразовать это в одну формулу?

Answer 1

Функция, которую вы ищете - COUNTIF():

Введите следующую формулу в G3 и ctrl-enter/copy-paste/fill-right в G3:I3:

=COUNTIF(A3:A8,">"&D3)

COUNTIF() проверяет каждое значение в первом аргументе на соответствие критериям во втором и подсчитывает, сколько раз оно встречается.

---

Использование COUNTIF() - самое простое и лучшее решение.

Конечно, вы можете использовать более сложную / сложную для понимания формулу, как

=SUMPRODUCT(--(A3:A8>D3))

или массив введен как

{=SUM(--(A3:A8>D3))}

или даже более излишне сложная версия тех.

Тем не менее, использование какого-либо из них в данном конкретном случае не дает никакой выгоды.

На самом деле, поскольку вы, похоже, заинтересованы в сокращении числа вспомогательных столбцов, еще более удачным решением было бы также обойтись без средних вспомогательных столбцов:

Введите следующую формулу в D3 и ctrl-enter/copy-paste/fill-right в D3:F3:

=COUNTIF(A3:A8,">"&AVERAGE(A3:A8))

(И да, эту формулу также можно было бы усложнить для начинающего, преобразовав ее в =SUMPRODUCT(--(A3:A8>AVERAGE(A3:A8))) или {=SUM(--(A3:A8>AVERAGE(A3:A8)))} .)

Answer 2

Функция SUMPRODUCT может решить и вашу проблему.

Запишите эту формулу в G102 и заполните ее справа от G102 до I102:

=SUMPRODUCT(--(A102:A107>D102:D107))

ПРИМЕЧАНИЕ. Настройте адрес ячейки в соответствии с вашими потребностями.