Подсчет с использованием логики ИЛИ для нескольких критериев в Excel

Обычно вы можете использовать функцию СЧЁТЕСЛИМН для подсчета ячеек на основе одного или нескольких условий с логикой И в Excel. Бывало ли у вас такое, что нужно было подсчитать более одного значения из одного столбца или диапазона ячеек? Это означает подсчет с несколькими условиями и логикой ИЛИ. В этом случае вы можете применить функции СУММ и СЧЁТЕСЛИМН вместе или использовать функцию СУММПРОИЗВ.

• Подсчет ячеек с условиями ИЛИ в Excel
  • Подсчет ячеек с условиями ИЛИ с использованием функций СУММ и СЧЁТЕСЛИМН
  • Подсчет ячеек с условиями ИЛИ с использованием функции СУММПРОИЗВ
• Подсчет ячеек с несколькими наборами условий ИЛИ в Excel
  • Подсчет ячеек с двумя наборами условий ИЛИ с использованием функций СУММ и СЧЁТЕСЛИМН
  • Подсчет ячеек с несколькими наборами условий ИЛИ с использованием функции СУММПРОИЗВ

Подсчет ячеек с условиями ИЛИ в Excel

Например, у меня есть диапазон данных, как показано на скриншоте ниже, и теперь я хочу подсчитать количество продуктов, которые являются «Карандаш» или «Линейка». Здесь я рассмотрю две формулы для решения этой задачи в Excel.

Подсчет ячеек с условиями ИЛИ с использованием функций СУММ и СЧЁТЕСЛИМН

В Excel, чтобы подсчитать с несколькими условиями ИЛИ, вы можете использовать функции СУММ и СЧЁТЕСЛИМН с массивом констант. Общий синтаксис следующий:

• диапазон: Диапазон данных содержит критерии, по которым вы подсчитываете ячейки;
• критерий1, критерий2, критерий3…: Условия, на основе которых вы хотите подсчитывать ячейки.

Чтобы подсчитать количество продуктов, которые являются «Карандаш» или «Линейка», пожалуйста, скопируйте или введите приведенную ниже формулу в пустую ячейку, а затем нажмите клавишу Enter, чтобы получить результат:

Объяснение формулы:

=СУММ(СЧЁТЕСЛИ(B2:B13;{"Карандаш";"Линейка"}))

• {"Карандаш";"Линейка"}: Сначала вам нужно упаковать все условия в массив констант, например: {"Карандаш";"Линейка"}, разделяя элементы точкой с запятой.
• СЧЁТЕСЛИ(B2:B13;{"Карандаш";"Линейка"}): Эта функция СЧЁТЕСЛИ получит отдельные подсчеты для «Карандаша» и «Линейки», и вы получите результат: {2;3}.
• СУММ(СЧЁТЕСЛИ(B2:B13;{"Карандаш";"Линейка"}))=СУММ({2;3}): Наконец, эта функция СУММ суммирует все элементы в массиве и возвращает результат: 5.

Советы: Вы также можете использовать ссылки на ячейки для критериев, пожалуйста, примените следующую формулу массива, а затем нажмите клавиши Ctrl + Shift + Enter вместе, чтобы получить правильный результат:

Подсчет ячеек с условиями ИЛИ с использованием функции СУММПРОИЗВ

Вот еще одна формула, созданная с помощью функции СУММПРОИЗВ, которая может помочь подсчитать ячейки с логикой ИЛИ. Общий синтаксис следующий:

• диапазон: Диапазон данных содержит критерии, по которым вы подсчитываете ячейки;
• критерий1, критерий2, критерий3…: Условия, на основе которых вы хотите подсчитывать ячейки.

Пожалуйста, скопируйте или введите следующую формулу в пустую ячейку, и нажмите клавишу Enter, чтобы вернуть результат:

Объяснение формулы:

=СУММПРОИЗВ(1*(B2:B13={"Карандаш";"Линейка"}))

• B2:B13={"Карандаш";"Линейка"}: Это выражение сравнивает каждый критерий «Карандаш», «Линейка» с ячейками диапазона B2:B13. Если критерий выполнен, он возвращает ИСТИНА, в противном случае ЛОЖЬ, вы получите результат: {ИСТИНА;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ}.
• 1*(B2:B13={"Карандаш";"Линейка"}): Умножение преобразует логические значения – ИСТИНА и ЛОЖЬ в 1 и 0, поэтому получится результат: {1;0;0;0;0;0;0;1;0;0;1;0}.
• СУММПРОИЗВ(1*(B2:B13={"Карандаш";"Линейка"}))= СУММПРОИЗВ({1;0;0;0;0;0;0;1;0;0;1;0}): Наконец, функция СУММПРОИЗВ суммирует все числа в массиве, чтобы получить результат: 5.

Подсчет ячеек с несколькими наборами условий ИЛИ в Excel

Иногда вам может потребоваться подсчитать ячейки с двумя или более наборами условий ИЛИ, в этом случае вы можете использовать либо СУММ и СЧЁТЕСЛИМН с массивом констант, либо СУММПРОИЗВ с функциями ЕЧИСЛО и ПОИСКПОЗ.

Подсчет ячеек с двумя наборами условий ИЛИ с использованием функций СУММ и СЧЁТЕСЛИМН

Для работы только с двумя наборами условий ИЛИ просто добавьте другой массив констант в формулу СЧЁТЕСЛИМН.

Например, у меня есть диапазон данных, как показано на скриншоте ниже, и теперь я хочу подсчитать людей, которые заказали «Карандаш» или «Линейку», и Сумма либо <100, либо>200.

Пожалуйста, введите или скопируйте следующую формулу в ячейку, а затем нажмите клавишу Enter, чтобы получить результат, см. скриншот:

ПримечаниеВ формуле вы должны использовать точку с запятой для второго массива констант, который создает вертикальный массив.

Подсчет ячеек с несколькими наборами условий ИЛИ с использованием функции СУММПРОИЗВ

Вышеуказанная формула работает только для двух наборов условий ИЛИ, если вам нужно подсчитать с большим количеством критериев, сложная формула СУММПРОИЗВ вместе с функциями ЕЧИСЛО и ПОИСКПОЗ может вам помочь.

Возьмем данные ниже в качестве примера, чтобы подсчитать людей, которые заказали "Карандаш" или "Линейку", и статус либо "Доставлено", либо "В пути", и подписано "Бобом" или "Эко", вам следует применить сложную формулу.

Пожалуйста, скопируйте или введите приведенную ниже формулу в пустую ячейку, а затем нажмите клавишу Enter, чтобы получить расчет, см. скриншот:

Объяснение формулы:

=СУММПРОИЗВ(ЕЧИСЛО(ПОИСКПОЗ(B2:B13;{"Карандаш";"Линейка"};0))*ЕЧИСЛО(ПОИСКПОЗ(C2:C13;{"Доставлено";"В пути"};0))*ЕЧИСЛО(ПОИСКПОЗ(D2:D13;{"Боб";"Эко"};0)))

ЕЧИСЛО(ПОИСКПОЗ(B2:B13;{"Карандаш";"Линейка"};0)):

• ПОИСКПОЗ(B2:B13;{"Карандаш";"Линейка"};0): Эта функция ПОИСКПОЗ используется для сравнения каждой ячейки в диапазоне B2:B13 с соответствующим массивом констант. Если совпадение найдено, она возвращает относительное положение значения в массиве, в противном случае отображается значение ошибки. Таким образом, вы получите список массивов: {1;#Н/Д;#Н/Д;2;#Н/Д;1;#Н/Д;2;1;#Н/Д;2;#Н/Д}.
• ЕЧИСЛО(ПОИСКПОЗ(B2:B13;{"Карандаш";"Линейка"};0))= ЕЧИСЛО({1;#Н/Д;#Н/Д;2;#Н/Д;1;#Н/Д;2;1;#Н/Д;2;#Н/Д}): Функция ЕЧИСЛО преобразует числа в ИСТИНЫ, а значения ошибок в ЛОЖЬ: {ИСТИНА;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ИСТИНА;ИСТИНА;ЛОЖЬ;ИСТИНА;ЛОЖЬ}.

Вышеупомянутая логика также применима ко второму и третьему выражениям ЕЧИСЛО.

СУММПРОИЗВ(ЕЧИСЛО(ПОИСКПОЗ(B2:B13;{"Карандаш";"Линейка"};0))*ЕЧИСЛО(ПОИСКПОЗ(C2:C13;{"Доставлено";"В пути"};0))*ЕЧИСЛО(ПОИСКПОЗ(D2:D13;{"Боб";"Эко"};0))):

• Затем эти три массива перемножаются внутри СУММПРОИЗВ, который автоматически преобразует значения ИСТИНА и ЛОЖЬ в 1 и 0 как часть математической операции, например: СУММПРОИЗВ({1;0;0;1;0;1;0;1;1;0;1;0}*{1;1;0;0;1;1;0;1;1;0;1;1} *{1;0;0;0;0;0;0;1;0;0;1;0})=СУММПРОИЗВ({1;0;0;0;0;0;0;1;0;0;1;0}).
• Наконец, функция СУММПРОИЗВ суммирует все числа в массиве, чтобы получить результат: 3.

Связанные используемые функции:

Больше статей: