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

=СУММПРОИЗВ(—(логическое выражение))

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

Количество строк соответствующих внутренним критериям

Контекст

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

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

=СУММПРОИЗВ(—(C5:C10<D5:D10))

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

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

C5:C10<D5:D10

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

{ЛОЖЬ, ИСТИНА; ЛОЖЬ, ИСТИНА; ЛОЖЬ; ЛОЖЬ}

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

—(C5:C10<D5:D10)

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

{0; 1; 0; 1; 0; 0}

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

Количество, если строка соответствует нескольким внутренним критериям

=СУММПРОИЗВ((логическое1)*(логическое2))

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

Количество строк соответствующих нескольким внутренним критериям

Контекст

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

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

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

=СУММПРОИЗВ((B5:B10=G5)*(D5:D10>E5:E10))

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

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

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

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

=СУММПРОИЗВ(({ЛОЖЬ;ИСТИНА;ИСТИНА;ИСТИНА;ЛОЖЬ;ИСТИНА})*({ИСТИНА;ИСТИНА;ИСТИНА;ЛОЖЬ;ИСТИНА;ИСТИНА}))

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

=СУММПРОИЗВ({0;1;0;0;0;1})

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

Количество ячеек, если совпадают два критерия.

=СЧЁТЕСЛИМН(диапазон1;критерий1;диапазон2;критерий2)

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

Количество ячеек если совпадают два критерия

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

=СЧЁТЕСЛИМН(B5:B12;»синий»;C5:C12;»>15″)

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

СУММПРОИЗВ альтернатива

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

= СУММПРОИЗВ ((B5:B12 = «Синий») * (C5:C12>15))

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

Сводная таблица альтернативы

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

Количество нескольких критериев с НЕ логикой

=СУММПРОИЗВ((rng1=crit1)*ЕНД(ПОИСКПОЗ (rng2;crit2;0)))

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

Количество ячеек, соответствующих нескольким критериям с НЕ логикой

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

=СУММПРОИЗВ((C5:C12=F5)*ЕНД(ПОИСКПОЗ(D5:D12; G5:G6; 0)))

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

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

(Пол = F5)

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

{ИСТИНА; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ИСТИНА; ИСТИНА; ЛОЖЬ; ИСТИНА; ЛОЖЬ}

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

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

ЕНД(ПОИСКПОЗ (Группа; G5:G6;0))

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

{1; 2; # N / A; 1; 2; # N / A; 1; 2; # N / A}

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

{ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ИСТИНА}

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

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

СУММПРОИЗВ ({0; 0; 1; 0; 0; 1; 0; 0; 0})

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

Количество строк, соответствующих сразу нескольким критериям

=СУММПРОИЗВ(—((критерий1)+(критерий2)>0))

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

Количество ячеек соответствующих сразу нескольким критериям

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

= СУММПРОИЗВ (- ((С5: С11 = «синий») + (D5: D11 = «собака»)> 0))

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

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

(C5:C11 = «синий»)

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

{ИСТИНА; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ИСТИНА}

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

(D5:D11 = «собака»)

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

{ИСТИНА; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ}

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

{2; 0; 1; 1; 1; 0; 1}

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

— ({2; 0; 1; 1; 1; 0; 1}> 0)

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

{1; 0; 1; 1; 1; 0; 1}

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

Другие логические тесты

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

= СУММПРОИЗВ (- (ЕЧИСЛО(ПОИСК ( «красный»; A1: A10)) + ЕЧИСЛО (ПОИСК( «синий»; B1: B10))> 0))

Более логические тесты

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

Оцените статью
Добавить комментарий