Посчитать количество ячеек если строка соблюдает внутренние критерии в Excel

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

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

В показанном примере, формула в ячейке G5 является:

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

В этом случае мы просто сравним значения в столбце С со значениями в столбце D, используя логическое выражение:

Поскольку мы имеем дело с диапазонами (массивами), результат представляет собой массив значений ИСТИНА, ЛОЖЬ:

Для того, чтобы превратить их в единицы и нули, мы используем двойной отрицательный оператор (также называемый двойной унарный):

Который переведет массив в формат 1 и 0:

Который затем обрабатывается СУММПРОИЗВ. Поскольку существует только один массив, СУММПРОИЗВ просто суммирует элементы массива и возвращает число.

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

У вас есть таблица, содержащая результаты спортивных матчей. У вас есть четыре столбца: хозяева поля, команда гостей, счет команды хозяев, счет команды гостей. Для данной команды, вы хотите, чтобы рассчитывались только матчи (строки), где команда выиграла у себя дома. Легко подсчитать матчи (строки), где командой была команда хозяев поля, но как рассчитать только выигрыши?

Функция СУММПРОИЗВ может обрабатывать операции над массивами (вспомним операции, которые имеют дело с диапазонами) изначально.

В примере, формула в ячейке Н5:

Функция СУММПРОИЗВ запрограммирована для обработки массивов изначально. Это поведение по умолчанию заключается в умножении соответствующих элементов в одном или более массивов вместе, а затем суммируются результаты. Когда дается один массив, она рассчитывает сумму элементов в массиве.

В этом примере мы используем два логических выражения внутри одного аргумента массива. Мы могли бы поместить каждое выражение в отдельный аргумент, но тогда мы должны были бы превращать логические значения ИСТИНА, ЛОЖЬ в единицы и нули с другим оператором.

Используя оператор умножения для умножения двух массивов вместе, Excel автоматически превращает логические значения в единицы и нули.

После того, как два логических выражения вычисляются, формула выглядит следующим образом:

После того, как два массива умножаются, формула выглядит следующим образом: 

СУММПРОИЗВ просто суммирует элементы массива и рассчитывает сумму.

Если вы хотите считать строки, где совпадают два (или более) критерия, вы можете использовать формулу, основанную на функции СЧЁТЕСЛИМН.

В приведенном примере мы хотим подсчитать количество заказов с цветом «синий» и количеством > 15. Формула в ячейке F5 является:

Функция СЧЁТЕСЛИМН принимает несколько критериев в парах - каждая пара содержит один диапазон и соответствующий критерий для этого диапазона. Чтобы создать счет, все условия должны совпадать. Чтобы добавить дополнительные условия, просто добавьте еще одну пару диапазон / критерий.

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

СУММПРОИЗВ является более мощным и гибким, чем СЧЁТЕСЛИМН, и он работает со всеми версиями Excel, но это не так быстро, с большими наборами данных.

Если вам необходимо суммировать количество комбинаций критериев в большем наборе данных, то следует рассмотреть сводные таблицы. Сводные таблицы представляют собой быстрый и гибкий инструмент для составления отчетов, которые могут суммировать данные по-разному.

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

В показанном примере формула в G9 является:

Примечание: ПОИСКПОЗ и ЕНД позволяют формуле легко масштабировать, чтобы обращаться с большим количеством исключений, так как вы можете легко расширить диапазон, чтобы включить дополнительные "НЕ" значения.

Первое выражение внутри СУММПРОИЗВ сравнивает значения в столбце C "Пол" со значением в F5, "Мужской":

(Пол = F5)

Результатом является массив значений ИСТИНА, ЛОЖЬ:

Где ИСТИНА соответствует "Мужской".

Второе выражение внутри СУММПРОИЗВ проверяет значения в столбце D, группы со значениями в G5:G6 - "A" и "Б". Этот тест обрабатывается с ПОИСКПОЗ и ЕНД:

Функция ПОИСКПОЗ используется для сопоставления каждого значения в указанном диапазоне "Группы" со значениями в G5:G6 - "A" и "Б". Если совпадение завершается успешно, ПОИСКПОЗ рассчитывает число. Если совпадение не найдено, ПОИСКПОЗ возвращает # N/A. Результатом является массив:

Так как значения # N/A соответствуют "не А или Б", ЕНД используется для "обратного" массива:

Теперь ИСТИНА соответствует «не А или Б".

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

СУММПРОИЗВ затем рассчитывает сумму, 2, представляющую "Два мужчины не в группе А или Б".

Для подсчета строк с использованием нескольких критериев в разных колонках - логике ИЛИ - вы можете использовать функцию СУММПРОИЗВ.

В показанном примере формула в H5 является:

В приведенном примере мы хотим считать строки, где цвет "синий", или домашнее животное "собака".

Функция СУММПРОИЗВ работает с массивами изначально, для первого критерия мы используем:

Это возвращает массив значений ИСТИНА, ЛОЖЬ:

Для второго критерия, мы используем:

Который возвращает:

Эти два массива затем соединяются с добавлением (+), который автоматически превращает истинные значения ИСТИНА в 1 и 0, чтобы создать массив вроде этого:

Мы не можем просто добавить эти значения с СУММПРОИЗВ потому, что это удвоит подсчет строк с "синий" и "собака". Таким образом, мы используем "> 0" вместе с двойным минусом (-), чтобы превратить все значения в 1 или 0:

Что превращает этот массив в:

СУММПРОИЗВ затем рассчитывает сумму всех элементов.

Приведенный пример показывает тесты для простого равенства, но вы можете заменить эти заявления с другими логическими тестами по мере необходимости. Например, для подсчета строк, где ячейки в столбце A содержат "красный" или ячейки в колонке B содержат "синий", вы могли бы использовать формулу следующим образом:

Приведенный пример показывает тесты для простого равенства, но вы можете заменить эти заявления с другими логическими тестами по мере необходимости. Например, для подсчета строк, где ячейки в столбце A содержат "красный" или ячейки в колонке B содержат "синий", вы могли бы использовать формулу следующим образом: