Стандартне використання
Базовий синтаксис нашої функції простий:
= СУММПРОИЗВ (массів1; массів2;.)
Найнудніше варіант використання цієї чудової функції - застосовувати її так, як описано в довідці - для підсумовування попарних творів осередків в двох (і більше) зазначених діапазонах. Наприклад, можна без додаткового стовпця розрахувати загальну вартість замовлення:
По суті, те, що робить ця функція можна виразити формулою:
= B2 * C2 + B3 * C3 + B4 * C4 + B5 * C5
Технічно, перемножуєте масивів (діапазонів) може бути не два, а три або більше (до 255). Головне, щоб вони були одного розміру. Зручно, але нічого особливо. Однак, використовувати СУММПРОИЗВ тільки так - забивати цвяхи мікроскопом, бо, насправді, вона вміє набагато більше.
Робота з масивами без Ctrl + Shift + Enter
Якщо ви хоч трохи знайомі в Excel з формулами масиву, то повинні розуміти їх міць і красу. Іноді одна формула масиву може замінити декілька стовпців додаткових обчислень і ручної праці. Але у формул масиву є і мінуси. Головні - це відносна складність розуміння, уповільнення перерахунку книги і необхідність вводити ці формули поєднанням Ctrl + Shift + Enter замість звичайного Enter. І ось якраз з останнім може допомогти наша функція СУММПРОИЗВ. Нюанс в тому, що вона вміє працювати з масивами за визначенням, тобто не вимагає обов'язкового натискання Ctrl + Shift + Enter при введенні.
На цьому факті засновано більшість трюків з використанням СУММПРОИЗВ (SUMPRODUCT). Давайте, для прикладу, розглянемо пару-трійку найбільш характерних сценаріїв.
Підрахунок кількості виконаних умов
Припустимо, нам потрібно порахувати кількість філій компанії, де план виконаний (тобто факт більше або дорівнює плану). Це можна зробити однією формулою з СУММПРОИЗВ без додаткових стовпців:
Множення на 1, в даному випадку, потрібно, щоб перетворити результати порівняння плану і факту - логічну істину і БРЕХНЯ в 1 і 0, відповідно.
Перевірка декількох умов
Якщо потрібно перевіряти більше одного умови. то формулу з попереднього прикладу потрібно буде доповнити ще одним (або декількома) множителями. І якщо потрібно підраховувати не кількість, а суму, то множити годі й на 1, а на діапазон з сумовною даними:
Фактично, виходить щось вельми схоже на математичну функцію вибіркового підрахунку СУММЕСЛІМН (SUMIFS). яка також вміє перевіряти кілька умов (до 127) і підсумувати по ним значення із заданого діапазону.
Логічні зв'язки І і АБО (AND і OR)
Якщо потрібно пов'язувати умови не логічним "І", як в прикладі вище (Факт> = План) І (Регіон = Схід). а логічним АБО. то конструкція трохи зміниться - знак множення замінюється на плюс.
Підрахунок за даними з закритого (!) Файлу
Крім усього перерахованого вище, у СУММПРОИЗВ є ще одне неочевидне і вельми корисна властивість - вона вміє працювати з даними з невідкритих книг. Якщо, для порівняння, спробувати підрахувати в іншому файлі кількість філій з регіону Схід нашої книги і написати ось таке:
то друга формула з класичною функцією СЧЁТЕСЛІМН (COUNTIFS) буде працювати тільки до тих пір, поки вихідний файл відкритий. Якщо його закрити, то з'являється помилка # значить! Наша ж функція СУММПРОИЗВ (SUMPRODUCT) спокійно вважає за даними навіть з нерозкритого книги!