Добрый день!
Эту статью я хочу посвятить, не побоюсь этого слова, одной из самых полезных функций для экономистов всех рангов и мастей, это функция СУММПРОИЗВ в Excel. Я кстати при первоначальном знакомстве, совсем ее проигнорировал, не поняв всего волшебства ее возможностей. Она показалась мне какой-то бесполезной и ненужной, тем более что я мог суммировать элементы массивов я мог и с помощью других функций Excel. Функция СУММПРОИЗВ в Excel содержит в себе одновременно элементы таких функций как ЕСЛИ, СУММ, СУММЕСЛИ и СУММЕСЛИМН, а также может манипулировать вычислениями в 255 массивах, что согласитесь, отличная возможность.
Возможности и варианты когда применяется функция СУММПРОИЗВ в Excel большая и позволяет попросту отбросить множество отборочных и промежуточных вычислений.
Для начала рассмотрим правильный синтаксис, который нужен что бы функция СУММПРОИЗВ в Excel отлично работала:
= СУММПРОИЗВ(ваш_массив1;[ваш_массив2];[ваш_массив3];…и т.д.), где
- ваш_массив1 – является обязательным критерием, в нем указываются те компоненты которые необходимо перемножить, а после результаты будут сложены;
- ваш_массив2, ваш_массив3…. и т.д. – является необязательным критерием, но если количество ваших массивов, которые нужно перемножить и сложить полученные результаты, от 2 до 255 тогда они будут обязательны.
Ну как вы знаете лучше всего учится на примерах, хоть на своих, хоть на чужих, кроме ошибок. Поэтому давайте рассмотрим, как наша функция СУММПРОИЗВ в Excel работает в условия приближенных к боевым.
Возьмем таблицу по продажам фруктов и произведем вычисления с помощью функции СУММПРОИЗВ. Как видно для вычислений нужного нам значения была написана формула: =СУММПРОИЗВ((B2:B11=$H$5)*(D2:D11=$H$6);F2:F11), работу которой рассмотрим поподробнее. Вычисления «B2:B11=$H$5» означают что в массиве данных нужно выбрать всё с критерием «$H$5» или «Яковлев С.И.». Следующим действием «D2:D11=$H$6» мы отбираем в массиве данные со значением «$H$6» или «Яблоко». При совпадении значений в этих диапазонах происходит суммирование в массиве «F2:F11» и мы получаем результат.
Рекомендую в формулах использовать абсолютные ссылки, когда критериями у вас выступает значение в ячейках, это снизит риск возникновения ошибки в формулах во много раз.
В этом примере задачку можно решить и другими способами, с помощью других функций или цепочки вычислений, но часто это всё лишнее и намного проще написать формулу один раз и получить нужный результат. Теперь мы немного усложним наше задание и добавим еще условия в нашу задачку, такие как дата, нам вдруг стало интересно, сколько же яблок продал наш менеджер за последние две недели марта. Этот пример рассмотрим еще и потому что могут быть изменения в критериях товара, сейчас у нас написано «Яблоко», а ведь можно приписать сорт и всё изменится «Яблоко «Голд»» и тогда наша формула не сработает. Для решения нашей задачи нам нужна формула следующего вида: =СУММПРОИЗВ((B2:B11=$H$5)*(D2:D11=$H$6)*(E2:E11>=$H$9)*(E2:E11<=$H$11);F2:F11). Как видите каркас формулы сохранился, но с небольшими изменениями, добавлены ссылки на условия даты, то есть временные границы: (E2:E11>=$H$9) и (E2:E11<=$H$11). Единственное отличие, это использование знаков сравнения «>=» (больше и равно) и «<=» (меньше и равно), это необходимо для корректной работы формулы.
Если же вам не нужно указывать диапазон, а надо просто произвести поиск по датам, то достаточно просто указать знак «=». Тогда произведется отбор только по заданным датам.
Ну, вот вы и ознакомились с возможностями, и как используется функция СУММПРОИЗВ в Excel, хотя это и обзорная статья и многое я не смог рассказать, а значит, у вас есть шанс поэкспериментировать и открыть другие возможности этой потрясающей функции. Добавив в свою формулу разнообразные формулы условий и критериев, вы получите мощнейший инструмент для получения нужного результата.
Был рад вас приветствовать на моей странице. Жду ваши замечания или комментарии, пожелания или вопросы, а также не забывайте делиться статьей с другими и ставить лайк!
До встречи на страницах TopExcel!
«Бедность и богатство – суть слова для обозначения нужды и изобилия. Следовательно, кто нуждается, тот не богат, а кто не нуждается, тот не беден.
«
Демокрит
