Функция SUMPRODUCT в Excel умножает диапазоны или массивы и возвращает сумму произведений. Это звучит скучно, но СУММПРОИЗВ — невероятно универсальная функция, которую можно использовать для подсчета и суммирования, как COUNTIFS или SUMIFS, но с большей гибкостью. Внутри SUMPRODUCT можно легко использовать другие функции для дальнейшего расширения функциональности.
Цель
Умножить, затем суммировать массивы.
Возвращаемое значение
Результат перемножения и суммирования массивов
Аргументы
- array1 — первый массив или диапазон, который необходимо умножить, а затем добавить.
- array2 — [необязательный] Второй массив или диапазон для умножения, а затем добавления.
Синтаксис
=SUMPRODUCT(array1, [array2], …)
Примечания по использованию
Функция СУММПРОИЗВ умножает массивы и возвращает сумму произведений. Если указан только один массив, SUMPRODUCT просто суммирует элементы массива. Можно указать до 30 диапазонов или массивов.
При первом знакомстве с SUMPRODUCT он может показаться скучным, сложным и даже бессмысленным. Но СУММПРОИЗВ — это удивительно универсальная функция со множеством применений. Поскольку он изящно обрабатывает массивы, вы можете использовать его для обработки диапазонов ячеек умными и элегантными способами.
Рабочий лист
На листе, показанном выше, функция используется для вычисления условной суммы в трех отдельных формулах:
I5=SUMPRODUCT(--(C5:C14="red"),F5:F14) //
I6=SUMPRODUCT(--(B5:B14="tx"),--(C5:C14="red"),F5:F14)
I7=SUMPRODUCT(--(B5:B14="co"),--(C5:C14="blue"),F5:F14)
Результаты видны в ячейках I5, I6 и I7. В приведенной ниже статье объясняется, как можно использовать SUMPRODUCT для вычисления таких условных сумм, а также назначение двойного отрицания (—).
Классический пример
«Классический» пример SUMPRODUCT иллюстрирует, как можно вычислить сумму напрямую без вспомогательного столбца . Например, на листе ниже вы можете использовать функцию, чтобы получить сумму всех чисел в столбце F, вообще не используя столбец F:
Чтобы выполнить этот расчет, СУММПРОИЗВ использует значения в столбцах D и E непосредственно следующим образом:
=SUMPRODUCT(D5:D14,E5:E14) // returns 1612
Результат аналогичен суммированию всех значений в столбце F. Формула вычисляется следующим образом:
=SUMPRODUCT(D5:D14,E5:E14)
=SUMPRODUCT({10;6;14;9;11;10;8;9;11;10},{15;18;15;16;18;18;15;16;18;16})
=SUMPRODUCT({150;108;210;144;198;180;120;144;198;160})
=1612
Такое использование СУММПРОИЗВ может быть удобным, особенно когда нет места (или нет необходимости) для вспомогательного столбца с промежуточным вычислением. Однако наиболее распространенное использование SUMPRODUCT в реальном мире — это применение условной логики в ситуациях, требующих большей гибкости, чем могут предложить такие функции, как SUMIFS и COUNTIFS.
Для условных сумм и подсчетов
Предположим, у вас есть данные о заказе в A2:B6, где State находится в столбце A, а Sales — в столбце B:
| А | Б | |
| 1 | Состояние | Продажи |
| 2 | UT | 75 |
| 3 | СО | 100 |
| 4 | Техас | 125 |
| 5 | СО | 125 |
| 6 | Техас | 150 |
Используя SUMPRODUCT, вы можете подсчитать общий объем продаж для Texas («TX») по следующей формуле:
=SUMPRODUCT(--(A2:A6="TX"))
И вы можете суммировать общие продажи для Texas («TX») по этой формуле:
=SUMPRODUCT(--(A2:A6="TX"),B2:B6)
Примечание. Двойное отрицание — это распространенный прием, используемый в более сложных формулах Excel для приведения значений TRUE и FALSE к единицам и нулям.
Для приведенного выше примера с суммой вот виртуальное представление двух массивов, впервые обработанных SUMPRODUCT:
| array1 | array2 |
| TRUE | 75 |
| TRUE | 100 |
| FALSE | 125 |
| TRUE | 125 |
| FALSE | 150 |
В каждом массиве по 5 элементов. array1 содержит значения TRUE / FALSE, которые являются результатом выражения A2:A6=»TX», а массив2 содержит значения в B2:B6. Каждый элемент array1 будет умножен на соответствующий элемент массива .2 Однако в текущем состоянии результат будет равен нулю , поскольку значения ИСТИНА и ЛОЖЬ в array1 будут оцениваться как нулевые. Нам нужно, чтобы элементы array1 были числовыми, и именно здесь полезно использовать двойное отрицание.
Двойной отрицательный (—)
Двойное отрицание (—) — это один из нескольких способов привести значения TRUE и FALSE к их числовым эквивалентам, 1 и 0. Когда у нас есть 1 и 0, мы можем выполнять различные операции над массивами с помощью логической логики . В таблице ниже показан результат в array1 на основе приведенной выше формулы после того, как двойное отрицательное значение (—) изменило значения TRUE и FALSE на 1 и 0.
| массив1 | массив2 | Продукт | ||
| 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})
Затем СУММПРОИЗВ умножает массив1 и массив2 вместе, в результате чего получается один массив:
=SUMPRODUCT({0,0,125,0,150})
Наконец, SUMPRODUCT возвращает сумму всех значений в массиве, 275. Этот пример расширяет приведенные выше идеи более подробно.
Сокращенный синтаксис в массиве1
Вы часто будете видеть формулу, описанную выше, написанную по-другому, например так:
=SUMPRODUCT((A2:A6="TX")*B2:B6) // returns 275
В Norice все вычисления перенесены в array1 . Результат тот же, но этот синтаксис дает несколько преимуществ. Во-первых, формула более компактна, тем более, что логика усложняется. Это связано с тем, что двойное отрицательное значение (—) больше не требуется для преобразования значений TRUE и FALSE — математическая операция умножения (*) автоматически преобразует значения ИСТИНА и ЛОЖЬ из (A2:A6=»TX») в 1 и 0 . Но самым главным преимуществом является гибкость . При использовании отдельных аргументов операция всегда является умножением, так как SUMPRODUCT возвращает сумму произведений . Это ограничивает формулу логикой И, поскольку умножение соответствует сложению в булевой алгебре.. Перемещение вычислений в один аргумент означает, что вы можете использовать сложение (+) для логики ИЛИ в любой комбинации. Другими словами, вы можете выбирать свои собственные математические операции, которые в конечном итоге определяют логику формулы.
Учитывая вышеперечисленные преимущества, у сокращенного синтаксиса есть один недостаток. СУММПРОИЗВ запрограммирован на игнорирование ошибок, возникающих в результате умножения текстовых значений в массивах, заданных как отдельные аргументы . Это может быть удобно в определенных ситуациях . При сокращенном синтаксисе это преимущество исчезает, так как умножение происходит внутри одного аргумента массива. В этом случае применяется обычное поведение: текстовые значения будут создавать #ЗНАЧ! ошибки.
Примечание. Технически перемещение вычислений в массив1 создает « операцию с массивом », а SUMPRODUCT — одна из немногих функций, которые могут обрабатывать операции с массивом изначально без Control + Shift + Enter в прежних версиях Excel .
Игнорирование пустых ячеек
Чтобы игнорировать пустые ячейки с помощью СУММПРОИЗВ, вы можете использовать такое выражение, как диапазон<>»» . В приведенном ниже примере формулы в F5 и F6 игнорируют ячейки в столбце C, которые не содержат значения:
=SUMPRODUCT(--(C5:C15<>"")) // count
=SUMPRODUCT(--(C5:C15<>"")*D5:D15) // sum
С другими функциями
SUMPRODUCT может напрямую использовать другие функции. Вы можете увидеть, что СУММПРОИЗВ используется с функцией LEN для подсчета общего количества символов в диапазоне или с такими функциями, как ISBLANK, ISTEXT и т. д. Обычно это не функции массива, но когда им задан диапазон, они создают «результирующий массив». Поскольку СУММПРОИЗВ создан для работы с массивами, он может выполнять вычисления непосредственно с массивами. Это может быть хорошим способом сэкономить место на листе, устраняя необходимость во «вспомогательном» столбце.
Например, предположим, что у вас есть 10 различных текстовых значений в формате A1:A10, и вы хотите подсчитать общее количество символов для всех 10 значений. Вы можете добавить вспомогательный столбец в столбец B, который использует эту формулу: LEN(A1) для вычисления символов в каждой ячейке. Затем вы можете использовать SUM, чтобы сложить все 10 чисел. Однако, используя SUMPRODUCT, вы можете написать такую формулу:
=SUMPRODUCT(LEN(A1:A10))
При использовании с диапазоном, например A1:A10, LEN вернет массив из 10 значений. Затем SUMPRODUCT просто суммирует все значения и возвращает результат без необходимости использования вспомогательного столбца.
Ниже приведены примеры многих других способов использования SUMPRODUCT.
Массивы и Excel 365
Это запутанная тема, но она должна быть рассмотрена. Функцию СУММПРОИЗВ можно использовать для создания формул массива , не требующих Ctrl + Shift + Enter . Это ключевая причина, по которой SUMPRODUCT так широко используется для создания более сложных формул. Одна проблема с формулами массива заключается в том, что они обычно возвращают неверные результаты, если они не вводятся с помощью клавиш Ctrl + Shift + Enter.
Это означает, что если кто-то забудет использовать CSE при проверке или настройке формулы, результат может внезапно измениться, даже если фактическая формула не изменилась. Использование СУММПРОИЗВ означает, что формулы будут работать в любой версии Excel без специальной обработки .
В Excel 365 обработчик формул изначально обрабатывает массивы . Это означает, что вы часто можете использовать функцию СУММ вместо СУММПРОИЗВ в формуле массива с тем же результатом, и нет необходимости вводить формулу особым образом. Однако, если та же формула открыта в более ранней версии Excel, потребуется сочетание клавиш Ctrl + Shift + Enter.
Суть в том, что СУММПРОИЗВ является более безопасным вариантом, если рабочий лист будет использоваться в любой версии Excel до Excel 365, даже если рабочий лист был создан в Excel 365.
Примечания
- SUMPRODUCT рассматривает нечисловые элементы в массивах как нули.
- Аргументы массива должны быть одного размера. В противном случае функция сгенерирует ошибку #ЗНАЧ! значение ошибки.
- Логические проверки внутри массивов будут создавать значения TRUE и FALSE. В большинстве случаев вы захотите принудить их к 1 и 0.
- Часто может напрямую использовать результат других функций.