=СУММПРОИЗВ(—(логическое выражение))
Для подсчета строк в таблице, которые соответствуют внутренним, рассчитанным критериям, без использования вспомогательного столбца, вы можете использовать функцию СУММПРОИЗВ.
Контекст
Представьте, что у вас есть таблица показателей продаж для нескольких продуктов. У вас есть столбцы для продажи в прошлом месяце и столбец для продаж в текущем месяце. Вы хотите считать продукты (строки), где текущие продажи меньше, чем продажи в прошлом месяце. Вы не можете использовать СЧЁТЕСЛИМН для этого, потому что СЧЁТЕСЛИМН работает только с парой диапазонов критериев. Одним из вариантов: добавить вспомогательный столбец, который вычитает продажи в прошлом месяце от продаж в этом месяце, а затем использовать СЧЁТЕСЛИ для подсчета отрицательных результатов. Но что, если вы не хотите (или не можете) добавить вспомогательный столбец? В этом случае, вы можете использовать СУММПРОИЗВ.
В показанном примере, формула в ячейке 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))
Более логические тесты
Вы также можете добавить больше, чем два теста внутри функции СУММПРОИЗВ.