СУММПРОИЗВ() является мощным инструментом для работы с массивами данных. Функция позволяет суммировать значения из выбранных диапазонов, осуществлять условное суммирование и даже выполнять сложные математические операции с массивами.
Сегодня рассмотрим несколько интересных примеров использования функции СУММПРОИЗВ(), которые помогут оптимизировать работу в Excel и достичь более точных и удобных результатов.
📢 Файл с примерами решений размещен в конце статьи 🔽
⏩ Суммирование значений в нескольких диапазонах
Часто возникает необходимость суммировать значения из нескольких разных диапазонов.
Вместо того, чтобы писать отдельные формулы для каждого диапазона, можно использовать функцию СУММПРОИЗВ которая позволяет объединить их в одну формулу.
Например, формула =СУММПРОИЗВ($B$3:$B$12; C3:C12) определит сумму продаж товаров, основываясь на соответствующих значениях числа продаж из диапазона C3:C12 для Магазина 1:
Функция СУММПРОИЗВ выполняет следующие шаги:
- Проходит по каждому элементу диапазона $B$3:$B$12 и соответствующему элементу диапазона C3:C12.
- Умножает стоимость товара на соответствующее число продаж.
- Суммирует результаты умножения для всех товаров в конкретном магазине.
В итоге формула возвращает общую сумму продаж всех товаров для указанного магазина на основе стоимостей и числа продаж.
Для определения общей суммы продаж по всем магазинам применим формулу:
В данной формуле используется операция умножения (*), которая применяется к каждому элементу диапазона стоимостей товаров $B$3:$B$12 и соответствующему элементу диапазона чисел продаж C3:G12.
Результатом этой операции является новый диапазон значений, содержащий произведения соответствующих элементов.
⏩ Совместное использование СУММПРОИЗВ - ПОИСКПОЗ и СМЕЩ
Усложним задачу. Требуется определить сумму продаж для конкретного магазина за определенный период.
Пояснение к формуле:
= СМЕЩ(A1;ПОИСКПОЗ(D14;A2:A11;0);1;D15-D14+1)
=СМЕЩ(A1;ПОИСКПОЗ(D14;A2:A11;0);ПОИСКПОЗ(D13;B1:G1;0);D15-D14+1)
Таким образом, формула возвращает сумму произведений значений из динамического диапазона, начиная со стартовой ячейки, определенной функцией СМЕЩ, и включая заданное количество строк и цены из другого динамического диапазона, начиная со стартовой ячейки, определенной функцией СМЕЩ, и включая заданное количество строк.
📽 См. видео пример работы функции 🔽
⏩ СУММПРОИЗВ и двойное отрицание
В данном случае используется для условного суммирования значений на основе заданного условия.
Разберем формулу по частям:
- E1 - это ячейка, с которой мы сравниваем значения.
- < - это оператор сравнения, указывающий на условие "меньше".
- B2:B11 - это диапазон данных, в котором мы ищем значения, удовлетворяющие условию.
Чтобы понять, как работает формула, необходимо знать, что операторы сравнения (<, >, =, и т.д.) возвращают логическое значение TRUE или FALSE в зависимости от выполнения условия. В данном случае, если значение в ячейке E1 меньше значений в диапазоне B2:B11, то условие будет истинным и вернется значение TRUE, в противном случае - FALSE.
Далее, используется двойное отрицание --, которое преобразует логические значения TRUE и FALSE в числовые значения 1 и 0 соответственно. Таким образом, условие TRUE станет числом 1, а условие FALSE - 0.
Функция СУММПРОИЗВ суммирует все числовые значения в диапазоне, полученном после применения двойного отрицания. Это означает, что будет выполнено условное суммирование только тех значений, где условие E1 < B2:B11 истинно (равно 1).
Как еще можно применять двойное отрицание
