Подсчет значений с множественными критериями (Часть 2. Условие ИЛИ) в MS EXCEL

Произведем подсчет строк таблицы, значения которых удовлетворяют сразу двум критериям, которые образуют Условие ИЛИ. Например, в таблице с перечнем Фруктов и их количеством на складе, отберем строки, в которых в столбце Фрукты значится Персики ИЛИ строки с остатком на складе не менее 57 (ящиков). Т.е. Партии Персиков отбираются в любом случае, а к ним добавляются партии любых фруктов с остатком на складе не менее 57 (ящиков).

В качестве исходной таблицы возьмем таблицу с двумя столбцами: текстовым «Фрукты» и числовым «Количество на складе» (См. файл примера).

Задача

Подсчитаем строки, в которых в столбце Фрукты значится Персики ИЛИ строки с остатком на складе не менее 57 (ящиков). Отбираются только те строки, у которых в поле Фрукты значение Персики ИЛИ строки, у которых в поле Количество ящиков на складе значение >=57 (как бы совершаетcя 2 прохода по таблице: сначала критерий применяется только по полю Фрукты, затем по полю Количество ящиков на складе, строки в которых оба поля удовлетворяют критериям во второй проход не учитываются, чтобы не было задвоения)).

Для наглядности, строки в таблице, удовлетворяющие критериям, выделяются Условным форматированием с правилом =ИЛИ($A2=$D$2;$B2>=$E$2)

Для удобства создадим 3 динамических диапазона:

  1. Количество: =СМЕЩ(пример1!$B$2;;;СЧЁТЗ(пример1!$A$2:$A$15))
  2. Фрукты: =СМЕЩ(пример1!$A$2;;;СЧЁТЗ(пример1!$A$2:$A$15))
  3. Таблица: =СМЕЩ(пример1!$A$1;;;СЧЁТЗ(пример1!$A$1:$A$15);2)

Это позволит в дальнейшем добавлять значения в исходную таблицу и не переписывать формулы для подсчета значений.

Подсчет можно реализовать множеством формул, приведем несколько:

  • Формула =СЧЁТЕСЛИ(Фрукты;D2)+СЧЁТЕСЛИ(Количество;">="&E2)-СЧЁТЕСЛИМН(Фрукты;D2;Количество;">="&E2) с помощью 2-х функций СЧЁТЕСЛИ() подсчитывает строки удовлетворяющие каждому из критериев, затем вычитается количество строк удовлетворяющих обоим критериям одновременно (функция СЧЁТЕСЛИМН()).
  • Вместо 2-х функций СЧЁТЕСЛИ() можно использовать формулу =СУММПРОИЗВ((Фрукты=D2)+(Количество>=E2))-СЧЁТЕСЛИМН(Фрукты;D2;Количество;">="&E2)
  • Формула =БСЧЁТ(Таблица;B1;D13:E15) требует предварительного создания таблички с условиями. Заголовки этой таблицы должны в точности совпадать с заголовками исходной таблицы. Размещение условий в разных строках соответствует Условию ИЛИ (см. статью Функция БСЧЁТ()).
  • Также можно использовать формулу =БСЧЁТА(Таблица;A1;D13:E15) с теми же условиями, но нужно заменить столбец для подсчета строк, он должен быть текстовым, т.е. А

Альтернативным решением, является использование Расширенного фильтра, с той же табличкой условий, что и для функций БСЧЁТА() и БСЧЁТ()


В случае необходимости, можно задавать другие условия отбора. Например, подсчитать строки, в которых в столбце Фрукты значится Персики ИЛИ строки с остатком на складе не более 57 (ящиков).

Это потребует незначительного изменения формул (условие ">="&E2 нужно переписать как "<="&E2)

На листе Пример2 файла примера приведено универсальное решение, которое позволяет не модифицировать формулы, а лишь менять знаки сравнения.

Примечание: подсчет значений с множественными критерями также рассмотрен в статьях Подсчет значений с множественными критериями (Часть 1. Условие И)Часть3Часть4.

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 5 (1 оценка)