Счетчики с логикой ИЛИ для нескольких критериев в Excel
Обычно вы можете использовать функцию СЧЁТЕСЛИМН для подсчета ячеек на основе одного или нескольких условий с логикой И в Excel. Вы когда-нибудь сталкивались с ситуацией, когда вам нужно было подсчитать более одного значения из одного столбца или диапазона ячеек? Это означает подсчет с несколькими условиями и логикой ИЛИ. В этом случае вы можете применить функции СУММ и СЧЁТЕСЛИМН вместе или использовать функцию СУММПРОИЗВ.
Подсчет ячеек с условиями ИЛИ в Excel
Например, у меня есть ряд данных, как показано на скриншоте ниже, теперь я хочу подсчитать количество продукта, который является «Карандашом» или «Линейкой», здесь я рассмотрю две формулы для решения этой задачи в Excel.
Подсчет ячеек с условиями ИЛИ с помощью функций СУММ и СЧЁТЕСЛИМН
В Excel для подсчета с несколькими условиями ИЛИ вы можете использовать функции СУММ и СЧЁТЕСЛИМН с константой массива, общий синтаксис:
- range: Диапазон данных содержит критерии, по которым производится подсчет ячеек;
- criterion1, criterion2, criterion3…: Условия, на основе которых вы хотите подсчитывать ячейки.
Чтобы подсчитать количество продуктов, которые являются «Карандашом» или «Линейкой», скопируйте или введите приведенную ниже формулу в пустую ячейку, а затем нажмите Enter ключ для получения результата:
Пояснение к формуле:
= СУММ (СЧЁТЕСЛИМН (B2: B13; {"Карандаш"; "Линейка"}))
- {"Карандаш", "Линейка"}: Во-первых, вы должны упаковать все условия в константу массива следующим образом: {"Pencil", "Ruler"}, разделите элементы запятыми.
- СЧЁТЕСЛИМН (B2: B13; {"Карандаш", "Линейка"}): Эта функция СЧЁТЕСЛИМН будет получать отдельные подсчеты для «Карандаш» и «Линейка», и вы получите следующий результат: {2,3}.
- СУММ (СЧЁТЕСЛИМН (B2: B13; {"Карандаш", "Линейка"})) = СУММ ({2,3}): Наконец, эта функция СУММ суммирует все элементы в массиве и возвращает результат: 5.
Советы: Вы также можете использовать ссылки на ячейки для критериев, примените формулу массива ниже, а затем нажмите Shift + Ctrl + Enter вместе, чтобы получить правильный результат:
Подсчет ячеек с условиями ИЛИ с помощью функции СУММПРОИЗВ
Вот еще одна формула, созданная функцией СУММПРОИЗВ, которая также может помочь подсчитать ячейки с помощью логики ИЛИ. Общий синтаксис:
- range: Диапазон данных содержит критерии, по которым производится подсчет ячеек;
- criterion1, criterion2, criterion3…: Условия, на основе которых вы хотите подсчитывать ячейки.
Скопируйте или введите следующую формулу в пустую ячейку и нажмите 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; 0,0 , 0,1; 0,0; 0,0; 0,1; 0,0; XNUMX}.
- SUMPRODUCT(1*(B2:B13={"Pencil","Ruler"}))= SUMPRODUCT({1,0;0,0;0,0;0,1;0,0;1,0;0,0;0,1;0,0;0,0;0,1;0,0}): Наконец, функция СУММПРОИЗВ складывает все числа в массиве, чтобы получить результат: 5.
Подсчет ячеек с несколькими наборами условий ИЛИ в Excel
Иногда вам может потребоваться подсчитать ячейки с двумя или более наборами условий ИЛИ, в этом случае вы можете использовать либо СУММ, и СЧЁТЕСЛИ с константой массива, либо СУММПРОИЗВ с функциями ПОИСКПОЗ ISNUMBER.
Подсчет ячеек с двумя наборами условий ИЛИ с помощью функций СУММ и СЧЁТЕСЛИМН
Чтобы иметь дело только с двумя наборами критериев ИЛИ, вам просто нужно добавить еще одну константу массива в формулу СЧЁТЕСЛИМН.
Например, у меня есть диапазон данных, как показано на скриншоте ниже, теперь я хочу подсчитать людей, заказавших «Карандаш» или «Линейку», и сумма будет либо <100, либо> 200.
Введите или скопируйте следующую формулу в ячейку и нажмите Enter ключ для получения результата, см. снимок экрана:
Внимание: В формуле вы должны использовать точку с запятой для второй константы массива, которая создает вертикальный массив.
Подсчет ячеек с несколькими наборами условий ИЛИ с помощью функции СУММПРОИЗВ
Вышеупомянутая формула работает только для двух наборов критериев ИЛИ. Если вам нужно произвести подсчет с большим количеством критериев, вам может помочь сложная формула СУММПРОИЗВ вместе с функциями ПОИСКПОЗ.
Возьмите, например, приведенные ниже данные, чтобы подсчитать людей, которые заказали «Карандаш» или «Линейка», а статус - «Доставлено» или «В пути» и подписано «Бобом» или «Эко», вам следует применить сложную формулу .
Скопируйте или введите приведенную ниже формулу в пустую ячейку и нажмите Enter ключ, чтобы получить расчет, см. снимок экрана:
Пояснение к формуле:
=SUMPRODUCT(ISNUMBER(MATCH(B2:B13,{"Pencil","Ruler"},0))*ISNUMBER(MATCH(C2:C13,{"Delivered","In transit"},0))*ISNUMBER(MATCH(D2:D13,{"Bob","Eko"},0)))
ЕЧИСЛО (ПОИСКПОЗ (B2: B13; {"Карандаш", "Линейка"}, 0)):
- ПОИСКПОЗ (B2: B13, {"Карандаш", "Линейка"}, 0): Эта функция ПОИСКПОЗ используется для сравнения каждой ячейки в диапазоне B2: B13 с соответствующей константой массива. Если совпадение найдено, возвращается относительное положение значения в массиве, в противном случае отображается значение ошибки. Итак, вы получите следующий список массивов: {1; # N / A; # N / A; 2; # N / A; 1; # N / A; 2; 1; # N / A; 2; # N / A}.
- ISNUMBER(MATCH(B2:B13,{"Pencil","Ruler"},0))= ISNUMBER({1;#N/A;#N/A;2;#N/A;1;#N/A;2;1;#N/A;2;#N/A}): Функция ЕЧИСЛО преобразует числа в ИСТИНА, а значения ошибок в ЛОЖЬ следующим образом: {ИСТИНА; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ИСТИНА; ИСТИНА; ЛОЖЬ; ИСТИНА; ЛОЖЬ}.
Вышеупомянутая логика также может быть применена ко второму и третьему выражениям ISNUMBER.
SUMPRODUCT(ISNUMBER(MATCH(B2:B13,{"Pencil","Ruler"},0))*ISNUMBER(MATCH(C2:C13,{"Delivered","In transit"},0))*ISNUMBER(MATCH(D2:D13,{"Bob","Eko"},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}).
- Наконец, функция СУММПРОИЗВ суммирует все числа в массиве, чтобы получить результат: 3.
Используемая относительная функция:
- СУММА:
- Функция СУММ в Excel возвращает сумму предоставленных значений.
- СЧЁТЕСЛИ:
- Функция СЧЁТЕСЛИ - это статистическая функция в Excel, которая используется для подсчета количества ячеек, соответствующих критерию.
- СУММПРОИЗВ:
- Функцию СУММПРОИЗВ можно использовать для умножения двух или более столбцов или массивов вместе, а затем получения суммы произведений.
- НОМЕР:
- Функция Excel ЕЧИСЛО возвращает ИСТИНА, если ячейка содержит число, и ЛОЖЬ, если нет.
- СООТВЕТСТВИЕ:
- Функция ПОИСКПОЗ в Microsoft Excel ищет определенное значение в диапазоне ячеек и возвращает относительное положение этого значения.
Другие статьи:
- Подсчет уникальных числовых значений на основе критериев
- На листе Excel вы можете столкнуться с проблемой, связанной с подсчетом количества уникальных числовых значений на основе определенного условия. Например, как я могу подсчитать уникальные значения Qty продукта «Футболка» из отчета, как показано на скриншоте ниже? В этой статье я покажу несколько формул для решения этой задачи в Excel.
- Подсчет ячеек, соответствующих двум или более критериям
- В Excel для подсчета количества ячеек на основе двух или нескольких критериев функция СЧЁТЕСЛИМН может помочь вам быстро и легко решить эту задачу.
- Подсчитайте количество строк с несколькими критериями ИЛИ
- Чтобы подсчитать количество строк с несколькими критериями в разных столбцах, с логикой ИЛИ, вам может помочь функция СУММПРОИЗВ. Например, у меня есть отчет о продукте, как показано на скриншоте ниже, теперь я хочу подсчитать строки, где продукт - «Футболка» или цвет - «Черный». Как справиться с этой задачей в Excel?
Лучшие инструменты для работы в офисе
Kutools for Excel - поможет вам выделиться из толпы
Kutools для Excel может похвастаться более чем 300 функциями, Гарантия того, что то, что вам нужно, находится на расстоянии одного клика...
Вкладка Office - включение чтения и редактирования с вкладками в Microsoft Office (включая Excel)
- Одна секунда для переключения между десятками открытых документов!
- Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
- Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
- Добавляет эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.
