Excel: Что можно сделать с помощью функции SUMPRODUCT?

Название, конечно, несколько вводит в заблуждение. Эта функция не выдает произведение (результат умножения) нескольких сумм (результат сложения). Вместо этого функция делает обратное: она объединяет несколько произведений для получения общего итога. В Excel функция SUMPRODUCT помогает работать с любыми данными — от небольших таблиц до огромных матриц.

Если вам нужно перемножить несколько значений в Excel, а затем объединить результаты, функция SUMPRODUCT идеально подходит. Например, если у вас в рабочем листе есть несколько матриц и вы хотите сложить их вместе, это очень легко сделать с помощью функции SUMPRODUCT. Допустим, у нас есть таблица с двумя столбцами: SUMPRODUCT позволяет умножить два значения в каждой строке друг на друга, а затем сложить их.

SUMPRODUCT — это матричная формула. Обычно, если вы хотите использовать функцию в качестве матричной формулы, вы должны подтвердить ввод формулы с помощью сочетания клавиш [Ctrl] + [Shift] + [Enter]. Но с SUMPRODUCT этого делать не нужно, поскольку функция предназначена для обработки матриц. Поэтому Excel не требует специальной команды.

Функция SUMPRODUCT в Excel имеет относительно простую структуру. В общем случае необходимо указать только диапазон, который будет включен в расчет.

Формула должна включать как минимум один массив. Вы можете сделать до 256 записей. Excel предоставляет вам ряд вариантов заполнения параметров. Вы можете ввести матрицу через ссылку на ячейку, сослаться на нужный диапазон с именем или ввести массив (набор значений) непосредственно в формулу.

• Диапазон ячеек: =SUMPRODUCT(A2:A6,B2:B6)
• Имя: =SUMPRODUCT(Array1,Array2)
• Array: =SUMPRODUCT({15,27,12,16,22},{2,5,1,2,3})

Соответствующие диапазоны ячеек всегда должны быть симметричными: Например, если второй массив содержит одну строку или значение меньше, чем в первом диапазоне, будет выдано сообщение об ошибке.

Функция SUMPRODUCT обладает рядом преимуществ. Очевидное преимущество заключается в том, что вы можете сэкономить несколько шагов вычислений, связав умножение и сложение. SUMPRODUCT также можно комбинировать с другими функциями. Функция предлагает несколько приемов, которые интересны для решения более сложных задач.

Например, SUMPRODUCT имеет встроенный элемент, подобный функции SUMIF. Он позволяет выбрать, какие значения в матрицах должны быть добавлены к вычислению. Все, что вам нужно сделать, это несколько изменить синтаксис функции:

Мы добавили сравнение к первому аргументу. Соответствующие ячейки перемножаются и добавляются к итогу только в том случае, если данные соответствуют значению в ячейке A14. Кроме знака равенства, выполняющего сравнение, добавлены знаки умножения. В обычном синтаксисе диапазоны можно разделить просто с помощью запятой. Excel часто уже знает, как обрабатывать данные. Однако если вы включаете сравнение, функция требует четкой идентификации операции вычисления. В противном случае возникает ошибка.

Вы также можете использовать звездочку для умножения. Как уже упоминалось, функция работает только в том случае, если указанные диапазоны имеют одинаковое количество ячеек. Однако вы можете захотеть каждый раз умножать на одно и то же значение. Теоретически для этого нужно использовать массив с одинаковым значением в каждой ячейке. Вместо этого можно ввести значение только один раз в одной строке, а затем использовать «*» в формуле.

В качестве другого примера, предположим, что вы хотите сложить различные ячейки, затем умножить их на значение, а затем снова просуммировать результаты. Есть два способа реализовать это с помощью Excel:

Вы можете снова использовать символ умножения. Значения в большом диапазоне сначала складываются друг с другом, а затем умножаются на второй диапазон.

В этой формуле можно снова использовать запятую. Тем не менее, вы должны явно указать Excel, что сначала нужно просуммировать отдельные диапазоны.

Конечно, функцию SUMPRODUCT можно комбинировать с другими функциями Excel. Таким образом, вы можете включить несколько этапов в одну формулу и не использовать вспомогательные ячейки для хранения промежуточных результатов. Например, можно напрямую округлить результат функции. Важно отметить, что для работы этой функции массивы должны быть одинаковой длины.

В этом примере SUMPRODUCT находится внутри функции ROUND и служит первым параметром.