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

979
clmno

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

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

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

Изображение 1

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

Изображение 2, суммирование

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

10

2 ответа на вопрос

11
robinCTS

Функция, которую вы ищете 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)))}.)

@AFH Спасибо за редактирование (и upvote ;-)). Интересно, как это случилось? И что еще более важно, как я не заметил это ¯ \\ _ (ツ) _ / ¯ robinCTS 5 лет назад 0
Congrats. Системные боты выбирают сообщения с высоким рейтингом для использования в качестве проверок в очереди просмотра LQP. Этот пост был выбран как «некачественный» пост - почетный знак. :-) fixer1234 5 лет назад 0
@ fixer1234 О, конечно (－‸ლ) я даю * три * альтернативных решения проблемы Y, а затем приступаю к решению проблемы X. И вдобавок ко всему я предлагаю объяснение того, как работает функция `COUNTIF ()`. * Ясно *, что этот ответ - Низкое Качество! В следующий раз я буду придерживаться одной строки, код только ответ. (PS Только что проверил [временную шкалу] (// superuser.com/posts/1336127/timeline). Похоже, вы провалили аудит - вы выбрали `Выглядит нормально` ;-)) robinCTS 5 лет назад 0
2
Rajesh S

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

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

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

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

Похожие вопросы