Примеры формул СУММПРОИЗВ с несколькими условиями в Excel
Для поиска значений по нескольким условиям эффективно использовать функцию СУММПРОИЗВ. Данная функция поддерживается во всех версиях Excel. А если использовать функцию СУММПРОИЗВ в формулах вместе с другими функциями – это позволить выполнять поиск не только для чисел, но и для текста.
Как работает функция СУММПРОИЗВ при поиске по нескольким условиям
Ниже на рисунке представлена таблица отделов и их бюджетов. Допустим нам необходимо создать формулу, которая после выбора пользователем двух условий: региона и отдела, будет возвращать соответственную сумму бюджета. В данной формуле нельзя просто использовать функцию ВПР так как она ищет только одно значение по одному условию. В этом примере необходимо найти сразу 2 искомых значения, так как названия регионов и отделов многократно повторяются в исходной таблице.
Чтобы получить значение из строки, содержащей одновременно 2 искомых значения, можно воспользоваться формулой:
Функция СУММПРОИЗВ приводит к тому, что проверяется значение каждой ячейки в определенных диапазонах A2:A15 и B2:B15 с указанными искомыми значениями в ячейках G4 и G5 – соответственно. В зависимости от результата сравнения ячеек возвращаются таблицы из логических значений ИСТИНА или ЛОЖЬ. В процессе перемножения таблиц в формуле, значение ИСТИНА воспринимается как число 1, а ЛОЖЬ – как число 0. Диапазон в третей паре скобок в первом аргументе функции СУММПРОИЗВ не содержит операторов сравнения, так как это диапазон итоговых значений, одно из которых правильное и будет возвращено функцией.
Если в результате сравнения значение ячеек в столбцах «Регион» или «Отдел» будет возвращено значение ЛОЖЬ, в тоже время расчетная сумма для данной строки будет равна нулю =0. Так как логическое значение ЛОЖЬ заменяется на число 0. Если же вместо этого будут совпадать названия региона и отдела, тогда в результате обоих сравнений будет возвращено число 1. Обе единицы будут перемножены между собой, что также равно 1. А затем эта же единица будет перемножена на числовое значение ячейки в столбце «Бюджет» и таким образом итоговый результат будет возвращен формулой.
В примере, изображенном на рисунке функция СУММПРОИЗВ считывает данные со строки листа №11 и выполняет арифметическую операцию умножения 1*1*697 697=697 697. Это же число суммируется с результатами умножения в других строках, в которых все равны 0. Так как результате умножения хоть-бы на один ноль (возвращаемый значением ЛОЖЬ при сравнении) в итоге получаем все равно 0. То есть как например в последней строке таблицы 1*0*930 133=0.
Поиск текста функцией СУММПРОИЗВ с несколькими условиями в Excel
Функцию СУММПРОИЗВ можно использовать в описаны выше способ только тогда, когда возвращаемое значение является числом. Если должен был быть возвращенный текст тогда все текстовые строки были бы посчитаны как нули, а функция постоянно возвращала бы итоговое значение 0.
Однако можно соединить функцию СУММПРОИЗВ с функциями ИНДЕКС и СТРОКА в одну формулу. Благодаря этому возвращаемый результат будет текстом. Если, например, должно быть возвращено имя и фамилия руководителя отдела можно использовать следующую формулу:
В данной формуле вместо диапазонов ячеек из столбца D используется функция СТРОКА возвращающая номер для каждой текущей строки исходной таблицы. Функция СУММПРОИЗВ анализирует строку листа Excel под номером 11 и снова выполняет арифметическую операцию умножение: 1*1*12=12. После чего данный результат умножения используется как аргумент для функции ИНДЕКС, которая охватывает целый столбец C:C, так как функция СТРОКА возвращает номера строк рабочего листа Excel, а не исходной таблицы.
