Функция СУММПРОИЗВ в Excel с примерами ее использования
СУММПРОИЗВ в Excel – любимая функция бухгалтеров, т.к. она чаще всего используется для расчета заработной платы. Хотя она бывает, полезна и во многих других сферах.
По названию можно догадаться, что команда отвечает за суммирование произведений. Произведения при этом считаются либо диапазонами, либо целыми массивами.
Синтаксис СУММПРОИЗВ
Аргументами функции СУММПРОИЗВ являются массивы, т.е. заданные диапазоны. Их может быть сколько угодно. Перечисляя их через точку с запятой, мы задаем количество массивов, которые надо сначала перемножить, а затем просуммировать. Единственное условие: массивы должны быть равными по длине и однотипными (т.е. либо все горизонтальные, либо все вертикальные).
Простейший пример использования функции
Чтобы стало понятно, как и что считает команда, рассмотрим простой пример. Имеем таблицу с указанными длинами и ширинами прямоугольников. Нам нужно сосчитать сумму площадей всех прямоугольников. Если не пользоваться данной функцией, придется произвести промежуточные действия и сосчитать площадь каждого прямоугольника, а только потом сумму. Как мы и сделали.
Обратите внимание, что нам не понадобился массив с промежуточными итогами. В аргументах функции мы использовали только массивы с длиной и шириной, а функция их автоматически перемножила и просуммировала, выдав тот же результат = 70.
СУММПРОИЗВ с условием
Функция СУММПРОИЗВ в естественном виде почти не используется, потому что подсчет суммы произведений может редко пригодиться на производстве. Одно из популярных применений формулы СУММПРОИЗВ – для вывода значений, удовлетворяющих заданным условиям.
Рассмотрим пример. Имеем таблицу затрат небольшой компании за один расчетный месяц. Необходимо сосчитать общую сумму потраченных средств за январь и февраль по всем статьям расходов.
Для расчета затрат по канцелярии в январе-месяце, используем нашу функцию и указываем в начале 2 условия. Каждое из них заключаем в скобки, а между ними ставим знак «звездочка», подразумевающий союз «и». Получаем следующий синтаксис команды:
- (А:А=»Январь») – первое условие;
- (Е:Е=»канцелярия») – второе условие;
- D:D – массив, из которого выводится итоговая сумма.
В итоге получилось, что в январе на канцелярские принадлежности было затрачено 3700 рублей. Протянем формулу на остальные строки и заменим в каждой из них условия (заменив месяц или статью расходов).
Сравнение в СУММПРОИЗВ
Одним из условий при использовании команды СУММПРОИЗВ может быть сравнение. Рассмотрим сразу на примере. Предположим, что нам нужно сосчитать не просто все расходы по канцелярии за январь, а только те, которые составляли меньше 1000 рублей (назовем их «мелкие расходы»). Прописываем функцию с теми же аргументами, но дополнительно проставляем оператор сравнения. В данном случае он выглядит как D:D<=1000. Команда выдает ответ: 1000.
И действительно, это та самая тысяча, которая была потрачена в январе на карандаши. Мы задали дополнительно условие сравнения, и при автоматическом возврате значения суммы функция выдала нам такой ответ.
Протянем формулу на остальные ячейки, частично заменив данные. Видим, сколько денег ушло в январе и феврале на мелкие расходы по каждой статье затрат.
Как не забивать гвозди микроскопом с функцией СУММПРОИЗВ
Самый скучный вариант использования этой замечательной функции — применять ее так, как описано в справке — для суммирования попарных произведений ячеек в двух (и более) указанных диапазонах. Например, можно без дополнительного столбца расчитать общую стоимость заказа:
По сути, то, что делает эта функция можно выразить формулой:
Технически, перемножаемых массивов (диапазонов) может быть не два, а три или больше (до 255). Главное, чтобы они были одного размера. Удобно, но ничего особенно. Однако, использовать СУММПРОИЗВ только так — забивать гвозди микроскопом, ибо, на самом деле, она умеет гораздо больше.
Работа с массивами без Ctrl+Shift+Enter
Если вы хоть немного знакомы в Excel с формулами массива, то должны понимать их мощь и красоту. Иногда одна формула массива может заменить несколько столбцов дополнительных вычислений и ручного труда. Но у формул массива есть и минусы. Главные — это относительная сложность понимания, замедление пересчета книги и необходимость вводить эти формулы сочетанием Ctrl+Shift+Enter вместо обычного Enter. И вот как раз с последним может помочь наша функция СУММПРОИЗВ. Нюанс в том, что она умеет работать с массивами по определению, т.е. не требует обязательного нажатия Ctrl+Shift+Enter при вводе.
На этом факте основано большинство трюков с использованием СУММПРОИЗВ (SUMPRODUCT) . Давайте, для примера, рассмотрим пару-тройку наиболее характерных сценариев.
Подсчет количества выполненных условий
Допустим, нам нужно посчитать количество филиалов компании, где план выполнен (т.е. факт больше или равен плану). Это можно сделать одной формулой с СУММПРОИЗВ без дополнительных столбцов:
Умножение на 1, в данном случае, нужно, чтобы преобразовать результаты сравнения плана и факта — логическую ИСТИНУ и ЛОЖЬ в 1 и 0, соответственно.
Проверка нескольких условий
Если нужно проверять больше одного условия, то формулу из предыдущего примера нужно будет дополнить еще одним (или несколькими) множителями. И если нужно подсчитывать не количество, а сумму, то умножать можно не на 1, а на диапазон с суммируемыми данными:
Фактически, получается что-то весьма похожее на математическую функцию выборочного подсчета СУММЕСЛИМН (SUMIFS) , которая также умеет проверять несколько условий (до 127) и суммировать по ним значения из заданного диапазона.
Логические связки И и ИЛИ (AND и OR)
Если нужно связывать условия не логическим «И», как в примере выше (Факт>=План) И (Регион=Восток) , а логическим ИЛИ, то конструкция немного изменится — знак умножения заменяется на плюс:
Подсчет по данным из закрытого(!) файла
Кроме всего вышеперечисленного, у СУММПРОИЗВ есть еще одно неочевидное и весьма полезное свойство — она умеет работать с данными из неоткрытых книг. Если, для сравнения, попробовать подсчитать в другом файле количество филиалов из региона Восток нашей книги и написать вот такое:
. то вторая формула с классической функцией СЧЁТЕСЛИМН (COUNTIFS) будет работать только до тех пор, пока исходный файл открыт. Если его закрыть, то появляется ошибка #ЗНАЧ! Наша же функция СУММПРОИЗВ (SUMPRODUCT) спокойно считает по данным даже из неоткрытой книги!
Функция СУММПРОИЗВ в Excel — применение, синтаксис, примеры
Excel – довольно популярная программа в бухгалтерском учете. И специалисты этой сферы нередко используют функцию СУММПРОИЗВ в своем нелегком деле. Она позволяет им быстро рассчитать заработную плату.
Как может понять догадливый читатель, с помощью такой формулы можно суммировать два произведения. Множителями могут выступать или отдельные диапазоны, или целые массивы данных. Более того, эта формула может использоваться в качестве замены формулы массива. Но давайте обо всем по порядку.
Функция СУММПРОИЗВ — подробное описание
Несмотря на кажущуюся с первого взгляда простоту, эта функция не такая уж и простая. Точно так же, как и спектр ее применения. Все потому, что она выполняет сразу несколько действий. Так, с ее помощью можно суммировать значения, опираясь на определенные критерии. Кроме этого, она позволяет избавиться от необходимости использовать формулы массива в работе, которые очень часто приходится применять для стандартной функции СУММ .
Сами способы следующие:
- Определение суммы произведений массива.
- Проверка значения на предмет соответствия определенным критериям, получение их произведений и суммирования.
- Использование в качестве замены формулы массива.
На самом деле, возможных вариаций методов использования функции СУММПРОИЗВ значительно больше. Опытный пользователь Excel с легкостью сможет комбинировать несколько функций для того, чтобы заставить электронную таблицу работать так, как надо именно ему.
Синтаксис функции СУММПРОИЗВ
В качестве аргументов для этой функции используется набор диапазонов, которые сначала множатся между собой, а потом получившиеся результаты суммируются. Разделение аргументов осуществляется с помощью точки с запятой. Важно учитывать, что массивы не могут быть разного типа. Простыми словами, можно использовать либо только вертикальные, либо только горизонтальные диапазоны.
Давайте приведем наиболее простой для понимания пример применения этой функции. А потом будем постепенно усложнять задачу, демонстрируя все более новые особенности ее использования.
Итак, у нас есть таблица, в которой заранее известны значения длины и ширины разных прямоугольников. Перед нами была поставлена задача определить сумму их площадей. Можно обойтись и без функции СУММПРОИЗВ , рассчитав площадь путем умножения длины на ширины вручную, после чего просуммировать получившиеся значения. В результате, получится такая громоздкая таблица.
1
Но можно значительно упростить задачу, использовав функцию СУММПРОИЗВ , использовав два аргумента: диапазон всех значений длины и диапазон всех значений ширины. После этого программа сама выполнит все необходимые действия.
Видим, что результат использования функции СУММПРОИЗВ получился идентичным.
Внимание! Мы использовали исключительно значения длины и ширины в качестве аргументов функции. Колонка с площадью по каждому осталась для того, чтобы возможно было наглядно сравнить функции СУММ и СУММПРОИЗВ.
2
Функция СУММПРОИЗВ с условием
В чистом виде функция СУММПРОИЗВ используется довольно редко. Особенно в бухгалтерском учете. Тяжело представить ситуацию, что она оказывается непосредственно полезной каждый день для того, чтобы рассчитывать сумму произведений. Впрочем, такие ситуации тоже бывают. Например, если поставлена задача перемножить между собой курс и сумму в долларах, а потом получить общий финансовый показатель, основанный на стоимости каждой продукции в национальной валюте. Но такая задача все равно случается довольно редко.
При этом данная формула очень часто используется для того, чтобы выводить значения, основываясь на определенных условиях.
Значительно проще рассмотреть это на реальном примере. Предположим, у нас есть таблица расходов крошечной организации за один месяц. Перед нами стоит задача определить, сколько денег было потрачено в итоге за два первых месяца года суммарно по всем статьям расходов.
Чтобы выполнить эту задачу с помощью этой функции, необходимо ее использовать и записать два условия в самом начале. Каждое из условий заключается в скобках. Поскольку в нашем случае должно быть соответствие сразу двум критериям, то мы использовали символ звездочки (*). Синтаксис виден на этом скриншоте. Вам достаточно просто подставить соответствующие вашему случаю значения в необходимые места. И дело сделано!
3
Давайте рассмотрим аргументы более подробно:
- Первое условие записывается в первых скобках.
- Второе условие записывается во вторых скобках.
- Третий аргумент – это массив, сумму из которого необходимо извлечь.
Итоговый результат – 3700 рублей. Если распространить эту формулу на остальные строки и заменить условия в каждой из них, можно получить значения, характерные для другого отчетного периода или иной статьи расходов. К сожалению, лучше автоматизировать этот процесс не получится. Придется некоторые показатели править вручную. Но такова жизнь. Правда, если научиться писать макросы, можно добиться абсолютно любых уровней автоматизации при работе с электронными таблицами.
4
Сравнение в функции СУММПРОИЗВ
Сравнение – один из вариантов использования функции СУММПРОИЗВ . Осуществляется оно таким же образом, как и если опираться на критерий. Значительно проще продемонстрировать работу сравнения в функции СУММПРОИЗВ сразу на конкретном примере. Допустим, нам усложнили задачу. Нам теперь нужно не все расходы определить, а только мелкие. Допустим, это те затраты, сумма которых меньше 1000 рублей.
Аргументы в функции используем такие же, но к ним добавляем оператор сравнения. То бишь, в нашем случае – D:D<=1000. В результате вычислений была получена цифра 1000.
5
Что это за показатель? А это та тысяча, которую использовали для покупки карандашей в начале года. А после того, как нами был добавлен дополнительный критерий, ответ получился следующим.
6
Теперь давайте опять формулу протянем на все оставшиеся ячейки и частично изменим информацию. После этого мы увидим общую сумму затраченных средств по каждой статье расходов.
Стандартное использование СУММПРОИЗВ
Конечно, нет ничего более скучного и банального, чем стандартное использование функции СУММПРОИЗВ . И все это очень легко, и понятно сразу. Тем не менее, давайте приведем еще один пример и универсальную формулу этой функции.
=СУММПРОИЗВ(Массив1; Массив2; … )
То есть, функция попарно складывает два числа из каждого массива, а потом перемножает их между собой. Преимущество этой функции в том, что можно обойтись без дополнительных столбцов, как было описано в примере выше. Все нужные операции выполняются автоматически. Все можно выразить всего лишь одной простой формулой.
Количество диапазонов, которые можно использовать, ограничено 255 штуками. Но этого более, чем достаточно. Главное требование – они должны быть одного размера.
7
По сути, функция аналогична такому выражению.
=B2*C2+B3*C3+B4*C4+B5*C5
Логические связки И и ИЛИ (AND и OR)
В примере выше было описано, как можно делать логическую связку И . Но что делать, если нам нужно, чтобы хотя бы одному из критериев соответствовали описываемые ячейки? В таком случае необходимо использовать знак плюса, как показано на этом скриншоте.
8
Нахождение суммы произведений элементов массивов
Теперь давайте более подробно раскроем, как с помощью функции можно находить сумму произведений элементов массивов.
Синтаксис такой же самый, потому что фактически эта формула и работает с массивами данных. Именно поэтому она имеет огромные перспективы сразу в разных областях деятельности.
Эта функция выполняет поэлементные операции со всеми диапазонами (пусть их будет даже 200), которые входят в состав массива.
Обязательное требование к массивам – наличие одинаковых размерностей. Если массивы будут содержать разное количество элементов, будет возвращена ошибка #ЗНАЧ!.
В целом, эта формула не предназначена для использования с нечисловыми значениями. В таких случаях они будут читаться, как ноль. Но в некоторых случаях можно работать с текстовыми значениями. Как правило, это если речь идет о соответствии определенным условиям. Давайте раскроем эту тему более подробно.
Суммирование и подсчет значений отвечающих критериям
В случае, если нами будет записана такая формула, результат будет нулевым: =СУММПРОИЗВ(A3:A6>2) . Если перейти в строку формул, и там выделить ту часть, которая написана в скобках, а потом нажать на F9, то будет получен массив: ИСТИНА:ИСТИНА и ИСТИНА:ЛОЖЬ.
Если мы получаем такой результат, то значит, все хорошо.
Здесь надо учитывать один нюанс: если значение ИСТИНА равняется единице, а ЛОЖЬ – нулю, это еще не значит, что с этими числами можно выполнять какие-либо математические операции. Чтобы это сделать, необходимо использовать два знака минуса, которые находятся рядом. В этом случае массив будет переведен в числовую форму, и с ним можно выполнять математические операции.
Вопрос, а как возможно использование критериев применительно к текстовым значениям. Вот простая формула, после которой все сразу становится понятно.
То есть, мы облачаем слово в кавычки, после чего просто приравниваем нужные ячейки к этому текстовому значению. Точно так же можно использовать логические операторы, чтобы проверять диапазон на предмет соответствия нескольким критериям.
Проверка нескольких условий
Чтобы осуществить проверку на предмет соответствия сразу нескольким условиям, необходимо умножать критерии между собой или складывать их (использовать знак +). То бишь, применять описанные выше логические операторы.
СУММПРОИЗВ как формула массива
Функция СУММПРОИЗВ может использоваться и в качестве формулы массива. Когда это нужно делать? Прежде всего, когда необходимо осуществлять операции с большим количеством значений. В этом случае можно не использовать формулу массива СУММ , а просто заменить ее функцией СУММПРОИЗВ . Есть несколько примеров, как можно это использовать на практике.
=СУММПРОИЗВ(—ЕПУСТО(D2:D23)) – в этом случае ищутся пустые ячейки в диапазоне, подсчет которых осуществляется потом.
=СУММПРОИЗВ((A3:A6>СРЗНАЧ(A3:A6))*(A3:A6)) – здесь осуществляется отбор значений, которые больше среднего арифметического, после чего подсчитывается их сумма.
Подсчет по данным из закрытого файла
А теперь главная фишка. После того, как вы про нее узнаете, вы окончательно полюбите эту функцию. Дело в том, что она умеет находить информацию в тех файлах, которые не открыты на данный момент. Как известно, многие остальные функции умеют осуществлять поиск лишь в тех книгах, которые были ранее открыты на компьютере. В этом случае пользователь может даже их не открывать. Это особенно удобно, когда ресурсов компьютера мало (например, таблица большая, открыто много параллельных нужных вкладок в браузере, а оперативной памяти на компьютере мало). В этом случае эта функция будет как нельзя кстати.
Вот скриншот, где мы получаем данные о восточном филиале компании.
9
Как оно работает в других формулах (вернее, не работает)? Если документ, из которого черпается информация, не открыт в данный момент, то появляется ошибка #ЗНАЧ! В случае же с использованием функции СУММПРОИЗВ , если попытаться считать данные из файла, это получится сделать. Это открывает широчайшие возможности, поскольку дает возможность пользоваться недорогими компьютерами для выполнения сложных вычислений или сравнений.
Таким образом, функция СУММПРОИЗВ дает возможность использовать целый спектр возможностей электронных таблиц, начиная теми функциями, для которых она создавалась и заканчивая нестандартными способами применения. Как видим, нет ничего сложного. Ею пользоваться значительно легче, чем массивами функций (хотя и в той теме нет ничего сложного). Тем не менее, функция СУММПРОИЗВ дает возможность полностью избежать необходимости использовать формулу массива для вычислений подобного рода. И это не может не радовать.
Формула СУММПРОИЗВ ЕСЛИ — Excel и Google Таблицы
Функция СУММПРОИЗВ используется для умножения массивов чисел и суммирования результирующего массива.
Чтобы создать «Sumproduct If», мы будем использовать функцию SUMPRODUCT вместе с функцией IF в формуле массива.
СУММПРОИЗВ, ЕСЛИ
Комбинируя СУММПРОИЗВ и ЕСЛИ в формуле массива, мы можем создать функцию «СУММПРОИЗВ ЕСЛИ», которая работает аналогично встроенной функции СУММЕСЛИ. Давайте рассмотрим пример.
У нас есть список продаж, осуществленных менеджерами в разных регионах с соответствующими ставками комиссионных:
Предположим, нас просят рассчитать размер комиссии для каждого менеджера так:
Для этого мы можем вложить функцию ЕСЛИ с менеджер как наши критерии внутри функции СУММПРОИЗВ:
При использовании Excel 2022 и более ранних версий необходимо ввести формулу, нажав CTRL + SHIFT + ВВОД чтобы заключить формулу в фигурные скобки (см. верхнее изображение).
Как работает формула?
Формула работает, оценивая каждую ячейку в нашем диапазоне критериев как ИСТИНА или ЛОЖЬ.
Расчет общей комиссии для Оливии:
Затем функция IF заменяет каждое значение на FALSE, если его условие не выполняется.
Теперь функция СУММПРОИЗВ пропускает значения ЛОЖЬ и суммирует оставшиеся значения (2077,40).
СУММПРОИЗВ, ЕСЛИ с несколькими критериями
Чтобы использовать СУММПРОИЗВ ЕСЛИ с несколькими критериями (аналогично тому, как работает встроенная функция СУММЕСЛИМН), просто вложите больше функций ЕСЛИ в функцию СУММПРОИЗВ, например:
(CTRL + SHIFT + ВВОД)
(CTRL + SHIFT + ВВОД)
Другой подход к SUMPRODUCT IF
Часто в Excel есть несколько способов добиться желаемых результатов. Другой способ рассчитать «суммарный результат если» — это включить критерии в функция СУММПРОИЗВ как массив с использованием двойного унарного типа:
Этот метод использует двойной унарный (-) для преобразования массива ИСТИНА ЛОЖЬ в нули и единицы. SUMPRODUCT затем умножает преобразованные массивы критериев вместе:
Секреты и уловки:
- По возможности всегда блокируйте (F4) свои диапазоны и входные данные формул, чтобы разрешить автоматическое заполнение.
- Если вы используете Excel 2022 или новее, вы можете вводить формулу без Ctrl + Shift + Enter.
СУММПРОИЗВ, ЕСЛИ в Google Таблицах
Функция СУММПРОИЗВ, ЕСЛИ работает в Google Таблицах точно так же, как и в Excel: