Разберем, казалось бы, простую функцию СУММПРОИЗВ (SUMPRODUCT). В конце будет просто бомба для тех, кто строит отчеты в одних файлах, а хранит данные - в других.
Из русскоязычного написания функции можно догадаться, что СУММПРОИЗВ - это сумма произведений. Классический и самый примитивный способ её использования - перемножить значения из двух или более диапазонов и затем просуммировать. Посмотрим, как это работает.
1. Прямой способ использования функции СУММПРОИЗВ (SUMPRODUCT)
Представим ситуацию: есть данные по продажам товара (модель, количество, цена). Задача - посчитать общую стоимость всего проданного товара.
Расчет без использования функций: добавить столбец, в котором будет перемножаться количество и цена >> просуммировать полученные в этом столбце значения.
Функция СУММПРОИЗВ сделает это в одной ячейке:
Синтаксис функции очень прост - через точку с запятой перечисляются все массивы, которые должны быть перемножены:
= СУММПРОИЗВ (массив1; [массив2]; [массив3];...)
При этом есть пара ограничений:
- Размеры всех массивов должны быть одинаковыми
- Количество массивов - от 2 до 255
Как правило, большинство пользователей, которые знакомы с Excel на базовом уровне, либо не пользуются данной функцией, либо используют её только в описанном выше случае. Но дальше-то начинается самое интересное.
2. Выполнение арифметических операций внутри функции СУММПРОИЗВ
Среди массивов могут встречаться такие, которые сначала нужно просуммировать (или вычесть, или разделить) прежде, чем перемножать.
Например, такая ситуация: есть количество заказов, есть цена товара (отличная для каждого города), есть стоимость доставки. Задача: прибавить к цене стоимость доставки >> полученное помножить на количество заказов.
В этом случае заменяем точку с запятой на необходимый нам арифметический оператор (*,/,+,-). Если требуется настроить порядок вычислений при использовании арифметики внутри функции следует использовать круглые скобки, как в обычной арифметике.
Без использования функции пришлось бы формулу прописывать вот так:
=B2*(C2+D2)+B3*(C3+D3)+B4*(C4+D4)+B5*(C5+D5)+B6*(C6+D6)+B7*(C7+D7)
Согласитесь, что это прошлый век:)
Идем дальше.
3. Сумма произведений при соблюдении заданного условия
Пример: имеем данные заказов по разным городам и разным месяцам. Требуется: быстро и одной формулой получить общую стоимость по одному городу/месяцу.
Для решения этой задачи мы накладываем условие на заданный массив через знак равно. Важный нюанс в данном случае - массив и его условие должны заключаться в скобки, в противном случае Excel поймет вас неправильно.
Пример:
4. Проверка количества выполненных условий
Пример: имеем план продаж товара и фактически продажи в разрезе по городам. Задача одним движением определить, в скольких городах план был выполнен или перевыполнен.
Решение на скриншоте ниже. Здесь мы накладываем сравнение двух массивов через знак больше или равно (больше плана или равно плану). При этом сравнение умножаем на 1 - это нужно для того, чтобы Excel перевел результат логического сравнение (ИСТИНА или ЛОЖЬ) в число (1 или 0).
Если бы нам требовалось посчитать не количество, а сумму продаж, мы умножали бы не на единицу, а не массив с фактом продаж.
При необходимости проверки большего количества условий, выполняемых одновременно (аналог логическому оператору И), добавляем их в формулу через знак умножения и не забываем взять новое условие в круглые скобки.
5. Проверка выполнения одного из нескольких условий
Теперь мы хотим посчитать факт продаж по группам городов из того же примера. Например, для группы, состоящей из Москвы и Санкт-Петербурга (условно назовем "столицы", хотя это не важно и нигде не используется).
С точки зрения логики выражение должно звучать следующим образом: если проверяемый город - это Москва ИЛИ Санкт-Петербург, то учитываем факт продаж, в противном случае - не учитываем. Т.е. нам достаточно, чтобы проверяемое значение соответствовало только одному из двух допустимых (МСК/СПБ)
В синтаксисе функции СУММПРОИЗВ логический оператор ИЛИ заменим знаком "+" и получим следующее решение:
6. Бомба! Работаем с закрытой книгой.
Ситуация: есть файл с базой данных. Файл всегда лежит в одном и том же месте, всегда называется одинаково, внутри файла всегда одна и та же табличная структура хранения данных. Файл регулярно дополняется свежими данными (например, добавляются строки)
Печальная ситуация: каждый раз для формирования нового отчета, приходится или копировать файл себе, или открывать его и забирать оттуда данные - всё это движения, которые затормаживают процесс.
Элегантное решение: все отчеты настраиваются заранее и в необходимых местах ссылаются на файл источник. Проблема в том, что в Excel очень мало функций, которые умеют работать с закрытыми книгами. Но СУММПРОИЗВ умеет!
Для этого в ссылках на массивы нужно прописывать не только адрес ячейки и лист, но и путь к необходимому файлу. Это не очень удобно, поэтому Excel научился добавлять путь самостоятельно. Для этого всего лишь нужно:
- Открыть файл источник (у меня он будет называться "суммпроизв.xlsx"
- Прописать формулу с сылкой на этот файл. Например так:
=СУММПРОИЗВ([суммпроизв.xlsx]Лист1!$A$1:$A$2;[суммпроизв.xlsx]Лист1!$B$1:$B$2)
- Закрыть файл источник >> в этот момент эксель сам преобразует формулу и добавит в нее путь к файлу. Например, так:
=СУММПРОИЗВ('C:\Users\Excellent\Desktop\[суммпроизв.xlsx]Лист1'!$A$1:$A$2;'C:\Users\Excellent\Desktop\[суммпроизв.xlsx]Лист1'!$B$1:$B$2)
- Готово! Вы великолепны.
Подписывайтесь, рассказывайте, если у вас возникают трудности - попробуем вместе разобраться, что не так. Любые другие вопросы по Excel также приветствуются - разберемся вместе
