Всем привет! 👋
Сегодня рассмотрим удобный способ умножить значения в одном диапазоне ячеек на значения в другом и затем сложить их.
Обзор поможет не только освоить суть функции СУММПРОИЗВ, но и покажет интересные альтернативные методы решения задачи.
▶️ Постановка задачи
Для целей данного примера рассмотрим следующий набор данных:
где, например, Параметр 1 может быть количеством товара, а Параметр 2 - суммой за единицу.
🎯 Задача: Определить итоговую сумму.
📢 Скачать файл с примером вы можете в конце статьи 🔽
▶️ Как не надо...
Если вы новичок в Excel, то пожалуй решили бы поставленную задачу следующим образом:
1. Добавили дополнительный столбец
2. Нашли бы произведение произведение параметров по каждой строке:
3. Нашли бы сумму по диапазону:
Но, не правда ли это немного утомительно!? 🤔
А потому, рассмотрим способы, как решить задачу проще...
▶️ Способ 1. Функция СУММПРОИЗВ
Итак, как уже рассмотрено выше наши действия сводились к тому, что мы определяли произведение и сумму.
Так почему бы не выполнить описанные выше действия за один раз.
Да, и для этой цели Excel имеет такую замечательную функцию как СУММПРОИЗВ:
📝 Данная функция способна перемножать массивы и возвращать сумму произведений.
⚙️ Как это работает:
- Функция идентифицирует массивы: в данном случае это A2:A6 и B2:B6.
- Каждая ячейка первого массива умножается на соответствующую ячейку второго массива. Например, ячейка A2 умножается на ячейку B2, ячейка A3 на ячейку B3 и так далее.
- После умножения функция суммирует все результаты.
- Далее, возвращает сумму этих произведений.
🔔 Обратите внимание: если массивы в формуле имеют разные размеры, Excel вернет ошибку.
Таким образом, мы получили нужный результат, и главное - никаких дополнительных столбцов!
Но если мы уж говорим о массивах, то почему бы ни рассмотреть альтернативный подход...
▶️ Способ 2. Массив+Функция СУММ
Напомним 🔽
- Массивы - это структуры данных, которые позволяют хранить и обрабатывать множество значений в одной ячейке или диапазоне ячеек. Эти значения могут представлять собой числа, текст или другие данные.
- Массивы позволяют выполнять разнообразные операции, такие как вычисления, фильтрацию и анализ данных, сразу над большим объемом информации, делая работу более эффективной.
- Массивы могут быть одномерными (содержатся в одной строке или столбце) или двумерными (содержатся в нескольких строках и столбцах).
Отлично! То что нужно! 😉
А следовательно решение задачи можно представить в виде:
📝 Представленная формула: =СУММ(A2:A6*B2:B6) — это формула массива, которая перемножает элементы диапазонов A2:A6 и B2:B6 вместе, а затем суммирует произведения.
⚙️ Как это работает:
- Аргумент в скобках (A2:A6*B2:B6) в этой формуле выполняет умножение между соответствующими значениями в диапазонах A2:A6 и B2:B6.
- Затем функция СУММ берет все эти произведения и суммирует их:
🔔 Обратите внимание, что это формула массива, и если вы используете версии Excel до Excel 365, вам нужно будет нажать Ctrl+Shift+Enter после ее ввода, а не просто нажать Enter.
Но и это еще ни предел! 😉
Конечно же, поставленную задачу можно решить при помощи макроса.
▶️ Способ 3. Решение макросом
Способ подойдет если требуемая операция вычисления входит в состав приложения или же вы попросту хотите защитить формулы от их случайного изменения при этом по каким либо причинам не прибегая к защите листа:
Макрос найдет последнюю заполненную строку в столбцах A и B, умножит соответствующие значения в этих строках и выведет сумму в ячейке B8:
В результате пользователь получить нужный результат, при этом на листе не будет никаких формул.
Как вам такой подход? Пиши 💬 ответ в комментариях!
📍 Наши рекомендации 🔽