Sumproduct excel как пользоваться
Перейти к содержимому

Sumproduct excel как пользоваться

  • автор:

СУММПРОИЗВ (функция СУММПРОИЗВ)

Функция SUMPRODUCT возвращает сумму продуктов соответствующих диапазонов или массивов. Операция по умолчанию — умножение, но также возможны сложение, вычитание и деление.

В этом примере мы будем использовать SUMPRODUCT для возврата общего объема продаж для заданного элемента и размера:

Пример использования функции SUMPRODUCT для возврата общего объема продаж при указании названия продукта, размера и отдельных значений продаж для каждого из них.

SUMPRODUCT сопоставляет все экземпляры Item Y/Size M и суммирует их, поэтому для этого примера 21 плюс 41 равно 62.

Синтаксис

Чтобы использовать операцию по умолчанию (умножение):

=SUMPRODUCT(array1, [array2], [array3], . )

Аргументы функции СУММПРОИЗВ описаны ниже.

Первый массив, компоненты которого нужно перемножить, а затем сложить результаты.

[array2], [array3].

От 2 до 255 массивов, компоненты которых нужно перемножить, а затем сложить результаты.

Выполнение других арифметических операций

Используйте SUMPRODUCT, как обычно, но замените запятые, разделяющие аргументы массива, арифметическими операторами (*, /, +, -). После выполнения всех операций результаты суммируются в обычном режиме.

Примечание: При использовании арифметических операторов рекомендуется заключать аргументы массива в круглые скобки и использовать круглые скобки для группировки аргументов массива для управления порядком арифметических операций.

Примечания

  • Аргументы, которые являются массивами, должны иметь одинаковые размерности. В противном случае функция СУММПРОИЗВ возвращает значение ошибки #ЗНАЧ!. Например, =SUMPRODUCT(C2:C10,D2:D5) вернет ошибку, так как диапазоны не совпадают по размеру.
  • SUMPRODUCT обрабатывает нечисловые записи массива так, как если бы они были нулями.
  • Для максимальной производительности SUMPRODUCT не следует использовать с полными ссылками на столбцы. Рассмотрим значение =SUMPRODUCT(A:A,B:B). Здесь функция умножит 1 048 576 ячеек в столбце A на 1 048 576 ячеек в столбце B перед их добавлением.

Пример 1

Пример функции SUMPRODUCT, используемой для возврата суммы проданных товаров при указании стоимости и количества за единицу.

Чтобы создать формулу с помощью приведенного выше примера списка, введите =SUMPRODUCT(C2:C5,D2:D5) и нажмите клавишу ВВОД. Каждая ячейка в столбце C умножается на соответствующую ячейку в той же строке в столбце D, и результаты добавляются вверх. Общая сумма продуктов составляет $78,97.

Чтобы написать длинную формулу, которая дает тот же результат, введите =C2*D2+C3*D3+C4*D4+C5*D5*D5 и нажмите клавишу ВВОД. После нажатия клавиши ВВОД результат будет таким же: 78,97 долл. США. Ячейка C2 умножается на D2, а ее результат добавляется к результату ячейки C3 раз ячейки D3 и т. д.

Пример 2

В следующем примере используется функция SUMPRODUCT для возврата общего объема чистых продаж по агенту по продажам, где мы имеем как общий объем продаж, так и расходы по агенту. В этом случае мы используем таблицу Excel, в которой вместо стандартных диапазонов Excel используются структурированные ссылки . Здесь вы увидите, что на диапазоны Sales, Expenses и Agent ссылаются по имени.

Пример функции SUMPRODUCT для возврата общего объема продаж по представителям по продажам при условии, что она предоставляется с продажами и расходами для каждого из них.

Формула: =SUMPRODUCT(((Table1[Sales])+(Table1[Expenses]))*(Table1[Agent]=B8)), и она возвращает сумму всех продаж и расходов агента, указанного в ячейке B8.

Пример 3

В этом примере мы хотим вернуть общее количество определенного товара, проданного в заданном регионе. В этом случае, сколько вишни продал восточный регион?

Экзамен по использованию SUMPRODUCT для возврата суммы элементов по регионам. В этом случае количество вишни, проданных в восточном регионе.

Здесь формула: =SUMPRODUCT((B2:B9=B12)*(C2:C9=C12)*D2:D9). Сначала он умножает количество вхождений Востока на количество совпадающих вхождений вишни. Наконец, он суммирует значения соответствующих строк в столбце Продажи. Чтобы узнать, как Excel вычисляет эту функцию, выделите ячейку формулы, а затем перейдите в раздел Формулы > Вычисление формулы > Вычисление.

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

SUMPRODUCT (функція SUMPRODUCT)

Функція SUMPRODUCT повертає суму добутків відповідних діапазонів або масивів. Операція за промовчанням – це множення, але також можливі додавання, віднімання та ділення.

У цьому прикладі ми використовуватимемо функцію SUMPRODUCT, щоб повернути загальний обсяг збуту певного товару та його розмір:

Приклад використання функції SUMPRODUCT для повернення загального обсягу збуту в разі надання імені продукту, розміру та окремих значень збуту для кожного з них.

Функція SUMPRODUCT відповідає всім екземплярам елемента Y/Size M і підсумовує їх, тому для цього прикладу 21 плюс 41 дорівнює 62.

Синтаксис

Щоб використовувати операцію за промовчанням (множення):

=SUMPRODUCT(масив1;[масив2];[масив3];. )

Синтаксис функції SUMPRODUCT має такі аргументи:

Аргумент першого масиву, компоненти якого потрібно помножити, а потім додати.

[масив2], [масив3].

Від 2 до 255 масивів, елементи яких спочатку перемножуються, а отримані добутки підсумовуються.

Виконання інших арифметичних операцій

Використовуйте функцію SUMPRODUCT, як завжди, але замініть коми, що відокремлюють аргументи масиву, на потрібні арифметичні оператори (*, /, +, -). Після виконання всіх операцій результати підсумовуються, як зазвичай.

Примітка.: Якщо використовуються арифметичні оператори, радимо взяти аргументи масиву в дужки та згрупувати аргументи масиву за допомогою дужок для керування порядком арифметичних операцій.

Примітки

  • Аргументи масиву мають мати однакові виміри. Якщо ні, функція SUMPRODUCT повертає #VALUE! . Наприклад, формула =SUMPRODUCT(C2:C10;D2:D5) поверне помилку, оскільки діапазони мають не той самий розмір.
  • Функція SUMPRODUCT обробляє нечислові записи масиву так, ніби вони нулі.
  • Для найкращої продуктивності функція SUMPRODUCT не має використовуватися з повними посиланнями на стовпці. Розглянемо формулу =SUMPRODUCT(A:A;B:B), тут функція помножить 1 048 576 клітинок у стовпці A на 1 048 576 клітинок у стовпці B, перш ніж додавати їх.

Приклад 1

Приклад функції SUMPRODUCT, яка використовується для повернення суми проданих товарів за умови вартості одиниці товару та кількості.

Щоб створити формулу за допомогою наведеного вище зразка списку, введіть =SUMPRODUCT(C2:C5;D2:D5) і натисніть клавішу Enter. Кожна клітинка в стовпці C помножується на відповідну клітинку в тому самому рядку в стовпці D, і результати додаються. Загальна сума продуктів становить $78,97.

Щоб написати довшу формулу, яка дає такий самий результат, введіть =C2*D2+C3*D3+C4*D4+C5*D5 і натисніть клавішу Enter. Після натискання клавіші Enter результат такий самий: $78,97. Клітинку C2 помножено на клітинку D2, і її результат додається до результату клітинки C3,клітинки D3 тощо.

Приклад 2

У наведеному нижче прикладі функція SUMPRODUCT повертає загальний обсяг чистих продажів за агентом збуту, де ми маємо як загальний обсяг збуту, так і витрати за агентом. У цьому випадку ми використовуємо таблицю Excel, яка використовує структуровані посилання замість стандартних діапазонів Excel. Тут ви побачите, що діапазони «Продажі», «Витрати» та «Агент» посилаються на імена.

Приклад функції SUMPRODUCT для повернення загального обсягу збуту за торговим представником у разі надання збуту та витрат для кожного з них.

Формула: =SUMPRODUCT((Таблиця1[Збут])+(Таблиця1[Витрати]))*(Таблиця1[Агент]=B8)), і вона повертає суму всіх збуту та витрат для агента, указаного в клітинці B8.

Приклад 3

У цьому прикладі ми хочемо повернути загальну суму певного товару, проданого певним регіоном. У цьому випадку скільки вишень продав Східний регіон?

Екзаменаційний іспит із використанням функції SUMPRODUCT для повернення суми елементів за регіонами. В такому випадку кількість вишень, проданих у Східному регіоні.

Тут формула: =SUMPRODUCT((B2:B9=B12)*(C2:C9=C12)*D2:D9). Вона спочатку перемножує кількість входжень Сходу на кількість відповідних входжень вишень. Нарешті, підсумовує значення відповідних рядків у стовпці «Продажі». Щоб дізнатися, як excel обчислює це, виділіть клітинку формули, а потім перейдіть до розділу Формули > Обчислити формулу > Обчислити.

Потрібна додаткова довідка?

Ви завжди можете поставити запитання експерту в спільноті Tech у розділі Excel чи отримати підтримку в спільнотах.

Как не забивать гвозди микроскопом с функцией СУММПРОИЗВ

Базовый сценарий применения СУММПРОИЗВ

Базовый синтаксис нашей функции прост: =СУММПРОИЗВ( Массив1 ; Массив2 ; . ) Самый скучный вариант использования этой замечательной функции — применять ее так, как описано в справке — для суммирования попарных произведений ячеек в двух (и более) указанных диапазонах. Например, можно без дополнительного столбца расчитать общую стоимость заказа:
По сути, то, что делает эта функция можно выразить формулой: =B2*C2+B3*C3+B4*C4+B5*C5 Технически, перемножаемых массивов (диапазонов) может быть не два, а три или больше (до 255). Главное, чтобы они были одного размера. Удобно, но ничего особенно. Однако, использовать СУММПРОИЗВ только так — забивать гвозди микроскопом, ибо, на самом деле, она умеет гораздо больше.

Работа с массивами без Ctrl+Shift+Enter

Если вы хоть немного знакомы в Excel с формулами массива, то должны понимать их мощь и красоту. Иногда одна формула массива может заменить несколько столбцов дополнительных вычислений и ручного труда. Но у формул массива есть и минусы. Главные — это относительная сложность понимания, замедление пересчета книги и необходимость вводить эти формулы сочетанием Ctrl+Shift+Enter вместо обычного Enter. И вот как раз с последним может помочь наша функция СУММПРОИЗВ. Нюанс в том, что она умеет работать с массивами по определению, т.е. не требует обязательного нажатия Ctrl+Shift+Enter при вводе. На этом факте основано большинство трюков с использованием СУММПРОИЗВ (SUMPRODUCT) . Давайте, для примера, рассмотрим пару-тройку наиболее характерных сценариев.

Подсчет количества выполненных условий

Подсчет количества условий

Допустим, нам нужно посчитать количество филиалов компании, где план выполнен (т.е. факт больше или равен плану). Это можно сделать одной формулой с СУММПРОИЗВ без дополнительных столбцов:
Умножение на 1, в данном случае, нужно, чтобы преобразовать результаты сравнения плана и факта — логическую ИСТИНУ и ЛОЖЬ в 1 и 0, соответственно.

Проверка нескольких условий

СУММПРОИЗВ по нескольким условиям

Если нужно проверять больше одного условия, то формулу из предыдущего примера нужно будет дополнить еще одним (или несколькими) множителями. И если нужно подсчитывать не количество, а сумму, то умножать можно не на 1, а на диапазон с суммируемыми данными:
Фактически, получается что-то весьма похожее на математическую функцию выборочного подсчета СУММЕСЛИМН (SUMIFS) , которая также умеет проверять несколько условий (до 127) и суммировать по ним значения из заданного диапазона.

Логические связки И и ИЛИ (AND и OR)

СУММЕСЛИ с условиями ИЛИ

Если нужно связывать условия не логическим «И», как в примере выше (Факт>=План) И (Регион=Восток) , а логическим ИЛИ, то конструкция немного изменится — знак умножения заменяется на плюс:

Подсчет по данным из закрытого(!) файла

СУММПРОИЗВ из закрытой книги

Кроме всего вышеперечисленного, у СУММПРОИЗВ есть еще одно неочевидное и весьма полезное свойство — она умеет работать с данными из неоткрытых книг. Если, для сравнения, попробовать подсчитать в другом файле количество филиалов из региона Восток нашей книги и написать вот такое:
. то вторая формула с классической функцией СЧЁТЕСЛИМН (COUNTIFS) будет работать только до тех пор, пока исходный файл открыт. Если его закрыть, то появляется ошибка #ЗНАЧ! Наша же функция СУММПРОИЗВ (SUMPRODUCT) спокойно считает по данным даже из неоткрытой книги!

Ссылки по теме

  • Что такое формулы массива и как они работают
  • Как производить выборочные вычисления по одному или нескольким критериям

Sumproduct excel как пользоваться

Вопросы по покупке sales@onlyoffice.com

Запросы на партнерство partners@onlyoffice.com

Запросы от прессы press@onlyoffice.com

Следите за нашими новостями:

© Ascensio System SIA 2024. Все права защищены

© Ascensio System SIA 2024. Все права защищены

Не пропустите обновление!

Получайте последние новости ONLYOFFICE на ваш email

Имя не указано.

Email не указан.

На ваш адрес электронной почты отправлено сообщение с подтверждением.

В Справочном центре ONLYOFFICE используются файлы cookie для обеспечения максимального удобства работы пользователей. Продолжая использовать этот сайт, вы соглашаетесь с тем, что мы можем сохранять файлы cookie в вашем браузере.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *