У сьогоднішній статті ми розглянемо одну з найбільш популярних і потужних функцій Excel - СУММПРОИЗВ. Спочатку функція може здатися вам не такою корисною, як багато описують. Але варто почати працювати зі списками або масивами даних, користь СУММПРОИЗВ стає очевидною.
СУММПРОИЗВ - синтаксис формули
Синтаксис формули Excel суми творів досить простий. Функція бере в якості аргументів один або більше масивів даних і повертає суму добутків відповідних значень.
Синтаксис функції виглядає наступним чином: = СУММПРОИЗВ (спісок1; спісок2 ...)
Тобто, якщо у вас є масив даних в одній колонці і - в інший, і ви скористалися функцією СУММПРОИЗВ, ви отримаєте результат 120 (тому що 2 * 5 + 3 * 10 + 4 * 20 = 120).
СУММПРОИЗВ і масиви
Давайте припустимо, що у вас є таблиця продажів з колонками Ім'я продавця, Регіон і Сума продажів. І вам необхідно дізнатися, на яку суму зробив продажів той чи інший агент. Це просто, можна скористатися функцією СУММЕСЛИ і вказати в якості критерію підсумовування, необхідне нам ім'я агента.
Питання досить просто вирішуємо з однією умовою, але що якщо кількість умов зростає і нам необхідно дізнатися суму продажів конкретного агента в конкретному регіоні. У нас є три шляхи:
Насправді у нас є ще один, прихований шлях, скористатися СУММПРОИЗВ.
Використання СУММПРОИЗВ в формулах масиву
Якщо припустити, що дані знаходяться в діапазоні A2: C21, з іменами агентів в колонці A, регіонами - в колонці B і продажами - в колонці C, то формула СУММПРОИЗВ буде виглядати наступним чином:
= СУММПРОИЗВ (- - (A2: A21 = "Агент Сміт"); - - (B2: B21 = "Захід"); C2: C21)
Давайте розберемося, як працює формула:
- Частина формули (- - (A2: A21 = "Агент Сміт" шукає Агента Сміта в діапазоні A2: A21 і повертає масив з одиницями і нулями (одиниця, якщо осередок містить Агента Сміта, і нуль - якщо немає).
- Частина - - (B2: B21 = "Захід") робить те ж саме, тільки повертає одиницю, якщо осередок містить Захід.
- C2: C21 - просто повертає масив з продажами
Якщо ви перемножити всі три масиви і потім просумміруете твори, вийти шуканий результат.
Післямова
Вам також можуть бути цікаві такі статті
А ось питаннячко такий, другий варіант який ви описали повторює формулу СУММЕСЛІМН, так от не стикалися ви яка буде працювати швидше? і де можна докладніше почитати про використання «- -» ніколи раніше з цим не стикався?
Сергій, однозначно, формула СУММЕСЛІМН буде працювати швидше, так як вбудовані формули спритніше, ніж призначені для користувача
СУММПРОИЗВ - це призначена для користувача функція?
Тут я маю на увазі, що функцію СУММПРОІЗ ми заточуємо під функціонал СУММЕСЛІМН
Доброго дня.
Одна з переваг функції СУММПРОІЗ на відміну від СУММЕСЛИ - це робота з закритим файлом, тобто СУММПРОІЗ розраховує результат, використовуючи дані з іншої робочої книги, навіть якщо вона закрита.
Все було добре, поки довжина діапазону даних (спісок1; спісок2 ...) не стала змінної. Вирішила стандартне написання діапазону замінити на назву колонки таблиці, наприклад, (- - (табл1 [Агент] = "Агент Сміт») ....), Де табл1 - таблиця в іншому файлі. Поки цей інший файл відкритий, все працює. Але при закритому файлі будь Update призводить СУММПРОІЗ до помилки (Ref). Або я щось роблю неправильно?
Добрий день, Лілія
На жаль, це слабке місце Excel. Він не здатний витягати динамічні діапазони з закритих книг. Є пропозиція проводити всі обчислення на стороні закритої книги, а в книзі, де це буде використовуватися, поміщати тільки посилання на осередок закритою книги