Резюме
Функция СУММПРОИЗВ умножает диапазоны или массивы и возвращает сумму произведений. Это звучит скучно, но СУММПРОИЗВ - это невероятно универсальная функция, которую можно использовать для подсчета и суммирования, как СЧЁТЕСЛИМН или СУММЕСЛИМН, но с большей гибкостью. Другие функции можно легко использовать внутри SUMPRODUCT для дальнейшего расширения функциональности.
Цель
Умножьте, затем суммируйте массивыВозвращаемое значение
Результат умножения и суммирования массивовСинтаксис
= СУММПРОИЗВ (массив1; (массив2);…)Аргументы
- array1 - первый массив или диапазон для умножения, а затем добавления.
- array2 - (необязательно) Второй массив или диапазон для умножения, а затем добавления.
Версия
Excel 2003Примечания по использованию
Функция СУММПРОИЗВ работает с массивами, но не требует для ввода обычного синтаксиса массива (Ctrl + Shift + Enter). Функция СУММПРОИЗВ предназначена для умножения, а затем суммирования массивов. Если указан только один массив, SUMPRODUCT просто просуммирует элементы в массиве. Может быть поставлено до 30 массивов.
Когда вы впервые сталкиваетесь с SUMPRODUCT, это может показаться скучным, сложным и даже бессмысленным. Но СУММПРОИЗВ - удивительно универсальная функция, имеющая множество применений. Поскольку он будет обрабатывать массивы изящно и без жалоб, вы можете использовать его для обработки диапазонов ячеек умными и элегантными способами (см. Ссылки на примеры формул на этой странице).
Чтобы проиллюстрировать, как работает SUMPRODUCT, вот несколько распространенных примеров.
СУММПРОИЗВ для условных сумм и отсчетов
Предположим, у вас есть данные о заказе в A2: B6, с State в столбце A, Продажи в столбце B:
| А | B | |
| 1 | государство | Продажи |
| 2 | UT | 75 |
| 3 | CO | 100 |
| 4 | TX | 125 |
| 5 | CO | 125 |
| 6 | TX | 150 |
Используя СУММПРОИЗВ, вы можете подсчитать общий объем продаж в Техасе («Техас») по следующей формуле:
=SUMPRODUCT(--(A2:A6="TX"))
И вы можете суммировать общие продажи в Техасе ("Техас") по следующей формуле:
=SUMPRODUCT(--(A2:A6="TX"),B2:B6)
Примечание: пусть вас не смущает двойное отрицание. Это обычный прием, используемый в более сложных формулах Excel для приведения значений ИСТИНА и ЛОЖЬ к единицам и нулям. Подробнее см. Ниже…
Для приведенного выше примера суммы, вот виртуальное представление двух массивов, сначала обработанных СУММПРОИЗВОМ:
| array1 | array2 |
| ЛОЖНЫЙ | 75 |
| ЛОЖНЫЙ | 100 |
| ПРАВДА | 125 |
| ЛОЖНЫЙ | 125 |
| ПРАВДА | 150 |
Каждый массив состоит из 5 элементов. Первый массив содержит значения ИСТИНА / ЛОЖЬ, которые являются результатом выражения A2: A6 = "TX", а второй массив - это содержимое B2: B6. Каждый элемент в первом массиве будет умножен на соответствующий элемент во втором массиве. Однако в текущем состоянии результат SUMPRODUCT будет равен нулю, поскольку значения TRUE и FALSE будут обрабатываться как нулевые. Нам нужно, чтобы элементы в array1 были числовыми - они должны быть «принуждены» к единицам и нулям. Вот тут-то и появляется двойное отрицание.
Используя двойное отрицание - (двойное унарное, для технических типов), мы можем преобразовать ИСТИНА / ЛОЖЬ в числовые значения один и ноль, как показано в виртуальном представлении ниже. Последний столбец «Продукт» представляет собой результат умножения двух массивов вместе. Суммарный результат 275 - это значение, которое возвращает СУММПРОИЗВ.
| array1 | array2 | Товар | ||
| 0 | * | 75 | знак равно | 0 |
| 0 | * | 100 | знак равно | 0 |
| 1 | * | 125 | знак равно | 125 |
| 0 | * | 125 | знак равно | 0 |
| 1 | * | 150 | знак равно | 150 |
| Сумма | 275 | |||
При использовании синтаксиса фигурных скобок для массивов пример после приведения выглядит следующим образом:
=SUMPRODUCT((0,0,1,0,1),(75,100,125,125,150))
и вот так после умножения:
=SUMPRODUCT((0,0,125,0,150))
Этот пример более подробно развивает идеи, представленные выше.
СУММПРОИЗВ с другими функциями
SUMPRODUCT может напрямую использовать другие функции. Вы можете увидеть, что функция SUMPRODUCT используется с функцией LEN для подсчета общего количества символов в диапазоне или с такими функциями, как ISBLANK, ISTEXT и т. Д. Обычно это не функции массива, но когда им задается диапазон, они создают «массив результатов». Поскольку SUMPRODUCT создан для работы с массивами, он может выполнять вычисления с массивами напрямую. Это может быть хорошим способом сэкономить место на листе, устраняя необходимость во вспомогательном столбце.
Например, предположим, что у вас есть 10 различных текстовых значений в A1: A10, и вы хотите подсчитать общее количество символов для всех 10 значений. Вы можете добавить вспомогательный столбец в столбец B, который использует эту формулу: LEN (A1) для вычисления символов в каждой ячейке. Затем вы можете использовать СУММ, чтобы сложить все 10 чисел. Однако, используя СУММПРОИЗВ, вы можете написать такую формулу:
=SUMPRODUCT(LEN(A1:A10))
При использовании с диапазоном наподобие A1: A10, LEN вернет массив из 10 значений. Затем SUMPRODUCT просто просуммирует все значения и вернет результат, без вспомогательного столбца.
См. Ниже примеры многих других способов использования SUMPRODUCT.
Заметки:
- SUMPRODUCT обрабатывает нечисловые элементы в массивах как нули.
- Аргументы массива должны быть одинакового размера. В противном случае SUMPRODUCT сгенерирует #VALUE! значение ошибки.
- Логические тесты внутри массивов создадут значения ИСТИНА и ЛОЖЬ. В большинстве случаев вы захотите привести их к 1 и 0.
- SUMPRODUCT часто может напрямую использовать результат других функций (см. Примеры формул ниже)
