Sumproduct Excel как использовать

Обновлено: 06.07.2024

Когда вы впервые слышите название функции СУММПРОИЗВ в Excel, оно выглядит как какая-то бесполезная формула, которая может выполнять некоторые предопределенные операции суммирования и произведения. Но на самом деле это далеко не так. На самом деле это очень полезная функция, если вам нужно проанализировать данные двух или нескольких массивов в Excel.

Оглавление

Как Excel определяет функцию СУММПРОИЗВ:

Microsoft Excel определяет формулу СУММПРОИЗВ следующим образом: «Возвращает сумму произведений соответствующих диапазонов или массивов». Но эта функция способна на гораздо большее, чем ее определение.

Синтаксис функции СУММПРОИЗВ в Excel:

Основной синтаксис этой формулы довольно прост, как показано ниже:

Здесь Массив1, Массив2 и Массив3 — это три диапазона ячеек или массивы. Все массивы должны иметь одинаковое количество элементов. Минимальное количество массивов, которые можно использовать в функции СУММПРОИЗВ, равно 2, а максимальное — 30.

ПРИМЕЧАНИЕ. Если какой-либо массив содержит нечисловое значение, то он обрабатывается как 0.

Примеры формулы СУММПРОИЗВ в Excel:

Если у вас есть таблица, как показано на изображении ниже:

Теперь, если вы хотите применить формулу СУММПРОИЗВ к этим значениям, ваша формула должна быть:

Это даст вам результат = 935. Итак, СУММПРОИЗВ внутренне умножил каждый элемент обоих массивов, а затем добавил их друг к другу.

Пример 1.: (1x11)+(2x12)+(3x13)+(4x14)+(5x15)+(6x16)+(7x17)+(8x18)+(9x19)+(10x20) = 935

ФУНКЦИЯ СУММПРОИЗВ путем изменения арифметических операторов:

Еще одно преимущество формулы СУММПРОИЗВ. СУММПРОИЗВ также можно использовать с определяемыми пользователем математическими операторами вместо операции умножения по умолчанию. Пример прояснит это:

Запись функции УМНОЖЕНИЕ СУММПРОИЗВ в следующем виде: =СУММПРОИЗВ(B2:B11*C2:C11) «Заменена запятая знаком умножения»

Это имеет смысл, так как Excel указывает использовать оператор умножения между соответствующими элементами массива, а затем складывать их (как в примере 1). Таким образом, это также даст 935 в случае приведенного выше примера.

То же самое можно сделать с помощью формулы =СУММПРОИЗВ(B2:B11;C2:C11)

Но использование других математических операторов в функции СУММПРОИЗВ может помочь вам настроить операцию между элементами массива.

Таким образом, СУММПРОИЗВ ОТДЕЛЕНИЯ можно использовать как: =СУММПРОИЗВ(B2:B11/C2:C11)

В контексте приведенного выше примера это будет реализовано как: (1÷11)+(2÷12)+(3÷13)+(4÷14)+(5÷15)+(6÷16) +(7÷17)+(8÷18)+(9÷19)+(10÷20)

И дает и выводит = 3,312285968

СУММПРОИЗВ ДОПОЛНЕНИЯ может использоваться как: =СУММПРОИЗВ(B2:B11+C2:C11)

И Microsoft Excel реализует это как:

Вывод будет: 210

Аналогично СУММПРОИЗВ ВЫЧИТАНИЯ может использоваться как: =СУММПРОИЗВ(B2:B11-C2:C11)

Это будет реализовано внутри как:

И приведет к значению = -100

Использование критериев в расчете СУММПРОИЗВ:

Теперь наступает лучшая часть использования функции Excel SUMPRODUCT. Формула СУММПРОИЗВ также может использоваться при выполнении выборочных операций с данными на основе определенных критериев.

Например: у вас есть набор данных, как показано на изображении выше. И вы хотите выполнить УМНОЖЕНИЕ СУММПРОИЗВ двух диапазонов массивов, но только для категории с именем «Волокно».

Тогда формула должна быть такой: =СУММПРОИЗВ((C2:C11*D2:D11)*(B2:B11="Волокно"))

Excel выполнит поиск категории «волокно» в заданном диапазоне. И будет выполнять СУММПРОИЗВ только для значений категории «волокно», например: (2x12)+(8x18), что дает результат 168.

Итак, речь шла об использовании функции СУММПРОИЗВ в Excel.

Подпишитесь и станьте частью нашей семьи, насчитывающей более 15 000 человек!

Теперь подпишитесь на Excel Trick и получите бесплатную копию нашей электронной книги "200+ ярлыков Excel" (формат для печати), которая резко повысит вашу продуктивность.

Функция СУММПРОИЗВ в Excel умножает диапазоны или массивы и возвращает сумму произведений. Это звучит скучно, но СУММПРОИЗВ — невероятно универсальная функция, которую можно использовать для подсчета и суммирования, как СЧЁТЕСЛИМН или СУММЕСЛИМН, но с большей гибкостью. Внутри СУММПРОИЗВ можно легко использовать другие функции для дальнейшего расширения функциональности.

массив1 – первый массив или диапазон, который необходимо умножить, а затем добавить.
массив2 – [необязательно] второй массив или диапазон для умножения, а затем добавления.

Функция СУММПРОИЗВ умножает массивы и возвращает сумму произведений. Если указан только один массив, СУММПРОИЗВ просто суммирует элементы массива. Можно указать до 30 диапазонов или массивов.

При первом знакомстве с СУММПРОИЗВ он может показаться скучным, сложным и даже бессмысленным. Но СУММПРОИЗВ — это удивительно универсальная функция со множеством применений. Поскольку он изящно обрабатывает массивы, вы можете использовать его для обработки диапазонов ячеек умными и элегантными способами.

Массивы и Excel 365

Это запутанная тема, но ее необходимо рассмотреть. Функцию СУММПРОИЗВ можно использовать для создания формул массива, которые не требуют управления + сдвиг + ввод. Это ключевая причина, по которой СУММПРОИЗВ так широко используется для создания более сложных формул. Одна из проблем с формулами массива заключается в том, что они обычно возвращают неверные результаты, если они не вводятся с помощью клавиш Ctrl + Shift + Enter. Это означает, что если кто-то забудет использовать CSE при проверке или настройке формулы, результат может внезапно измениться, даже если фактическая формула не изменилась. Использование СУММПРОИЗВ означает, что формулы будут работать в любой версии Excel без специальной обработки.

В Excel 365 обработчик формул изначально обрабатывает массивы. Это означает, что вы часто можете использовать функцию СУММ вместо СУММПРОИЗВ в формуле массива с тем же результатом, и нет необходимости вводить формулу особым образом. Однако, если та же формула открыта в более ранней версии Excel, потребуется сочетание клавиш Ctrl + Shift + Enter.

Суть в том, что СУММПРОИЗВ является более безопасным вариантом, если рабочий лист будет использоваться в любой версии Excel до Excel 365, даже если рабочий лист был создан в Excel 365.

Классический пример СУММПРОИЗВ

"Классический" пример СУММПРОИЗВ иллюстрирует, как можно вычислить сумму напрямую без вспомогательного столбца. Например, на листе ниже вы можете использовать СУММПРОИЗВ, чтобы получить сумму всех чисел в столбце F, вообще не используя столбец F:

Для выполнения этого расчета функция СУММПРОИЗВ использует значения в столбцах D и E непосредственно следующим образом:

Результат аналогичен суммированию всех значений в столбце F. Формула вычисляется следующим образом:

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

СУММПРОИЗВ для условных сумм и подсчетов

Предположим, что у вас есть данные о заказе в формате A2:B6, где "Статус" находится в столбце A, а "Продажи" - в столбце B:

A	B
1	Штат	Продажи
2	UT	75
3	CO	100
4	TX	125
5	СО	125
6	TX	150

Используя СУММПРОИЗВ, вы можете подсчитать общий объем продаж для Техаса ("Техас") по следующей формуле:

И вы можете суммировать общий объем продаж для Техаса ("Техас") по следующей формуле:

Примечание. Двойное отрицание — это распространенный прием, используемый в более сложных формулах Excel для преобразования значений ИСТИНА и ЛОЖЬ в единицы и нули.

Для приведенного выше примера с суммой вот виртуальное представление двух массивов, впервые обработанных функцией СУММПРОИЗВ:

массив1	массив2
ЛОЖЬ	75
ЛОЖЬ	100
ИСТИНА	125
ЛОЖЬ	125
ИСТИНА	150

Каждый массив содержит 5 элементов. Массив1 содержит значения TRUE/FALSE, которые являются результатом выражения A2:A6="TX", а массив2 содержит значения в B2:B6. Каждый элемент массива1 будет умножен на соответствующий элемент в массиве.2 Однако в текущем состоянии результат будет нулевым, поскольку Значения TRUE и FALSE в массиве1 будут оцениваться как нулевые. Нам нужно, чтобы элементы в массиве1 были числовыми, и здесь пригодится двойное отрицание.

Двойное отрицание (--)

Двойное отрицание (--) — это один из нескольких способов привести значения ИСТИНА и ЛОЖЬ к их числовым эквивалентам, 1 и 0.У нас есть 1 и 0, мы можем выполнять различные операции с массивами с помощью логической логики. В таблице ниже показан результат в array1, основанный на приведенной выше формуле, после того, как двойное отрицательное значение (--) изменило значения TRUE и FALSE на 1 и 0.

массив1	массив2	Продукт
0	*	75	=	0
0	*	100	=	0< /td>
1	*	125	=	125
0	*	125	=	0
1	*	150	=	150
Сумма				275

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

СУММПРОИЗВ затем умножает массив1 и массив2 вместе, в результате чего получается один массив:

Наконец, СУММПРОИЗВ возвращает сумму всех значений в массиве, 275. Этот пример более подробно раскрывает вышеизложенные идеи.

Сокращенный синтаксис

Вы часто будете видеть формулу, описанную выше, написанную по-другому:

Здесь СУММПРОИЗВ передан только один массив. Результат тот же, но синтаксис более компактный, потому что нет необходимости использовать двойное отрицание (--). Это работает, потому что математическая операция умножения (*) автоматически преобразует значения TRUE и FALSE из (A2:A6="TX") в 1 и 0. Вы часто будете видеть этот синтаксис в формулах СУММПРОИЗВ, потому что он позволяет использовать логическую алгебру для создания логики, необходимой в более сложных сценариях.

Игнорирование пустых ячеек

Чтобы игнорировать пустые ячейки с помощью СУММПРОИЗВ, можно использовать такое выражение, как диапазон<>"". В приведенном ниже примере формулы в F5 и F6 игнорируют ячейки в столбце C, которые не содержат значения:

СУММПРОИЗВ с другими функциями

СУММПРОИЗВ может напрямую использовать другие функции. Вы можете увидеть, что СУММПРОИЗВ используется с функцией ДЛСТР для подсчета общего количества символов в диапазоне или с такими функциями, как ЕПУСТО, ИСТЕКСТ и т. д. Обычно это не функции массива, но когда им задан диапазон, они создают «результирующий массив». Поскольку СУММПРОИЗВ создан для работы с массивами, он может выполнять вычисления непосредственно с массивами. Это может быть хорошим способом сэкономить место на листе, устраняя необходимость во «вспомогательном» столбце.

Например, предположим, что у вас есть 10 различных текстовых значений в формате A1:A10, и вы хотите подсчитать общее количество символов для всех 10 значений. Вы можете добавить вспомогательный столбец в столбец B, который использует эту формулу: ДЛСТР(A1) для вычисления символов в каждой ячейке. Затем вы можете использовать SUM, чтобы сложить все 10 чисел. Однако, используя СУММПРОИЗВ, вы можете написать такую формулу:

При использовании с диапазоном, например A1:A10, LEN возвращает массив из 10 значений. Затем СУММПРОИЗВ просто суммирует все значения и возвращает результат без необходимости использования вспомогательного столбца.

Функция СУММПРОИЗВ относится к категории математических и тригонометрических функций Excel. Функции Список наиболее важных функций Excel для финансовых аналитиков. Эта шпаргалка охватывает сотни функций, которые важно знать аналитику Excel. Функция умножит соответствующие компоненты заданного массива, а затем вернет сумму произведений. Он используется для расчета средневзвешенного значения.

Как финансовый аналитик Финансовый аналитик Описание работы Приведенное ниже описание работы финансового аналитика дает типичный пример всех навыков, образования и опыта, необходимых для найма на работу аналитика в банке, учреждении или корпорации. Выполняйте финансовое прогнозирование, отчетность и отслеживайте операционные показатели, анализируйте финансовые данные, создавайте финансовые модели. СУММПРОИЗВ — очень удобная функция, поскольку она может обрабатывать массивы по-разному и помогает сравнивать данные в двух или более двух диапазонах. Это также помогает в расчете данных с несколькими критериями.

Формула суммированного произведения

=СУММПРОИЗВ(массив1,[массив2],[массив3],…)

Функция СУММПРОИЗВ использует следующие аргументы:

Массив1 (обязательный аргумент) — это первый массив или диапазон, который мы хотим умножить, а затем добавить.
Массив2, Массив 3 (необязательный аргумент) — это второй (или третий) массив или диапазон, который мы хотим умножить и впоследствии добавить.

Как использовать функцию СУММПРОИЗВ Excel?

Чтобы понять, как использовать функцию СУММПРОИЗВ, рассмотрим несколько примеров:

Пример 1

Предположим, что нам даны следующие данные:

Мы хотим узнать общий объем продаж для Западного региона. Используемая формула:

Причина, по которой мы предоставили двойное отрицательное значение (- -), заключается в том, чтобы заставить Excel преобразовывать значения ИСТИНА и ЛОЖЬ в 1 и 0.

Виртуальное представление двух массивов, впервые обработанных функцией СУММПРОИЗВ, приведено ниже:

Первый массив содержит значения TRUE/FALSE, которые являются результатом выражения C4:C12="WEST", а второй массив содержит содержимое D4:D12. Каждый элемент в первом массиве будет умножен на соответствующий элемент во втором массиве.

Однако в текущем состоянии результат СУММПРОИЗВ будет равен нулю, так как значения ИСТИНА и ЛОЖЬ будут рассматриваться как нули. Нам нужно, чтобы элементы в массиве 1 были числовыми, поэтому нам нужно преобразовать их в 1 и 0. Вот тут-то и появляется двойное отрицание, поскольку оно будет интерпретировать ИСТИНА как 1, а ЛОЖЬ как 0.

Мы получаем следующий результат:

Пример 2: Средневзвешенное значение

Функция СУММПРОИЗВ часто используется для вычисления средневзвешенного значения, при котором каждому значению присваивается вес. Предположим, нам даны следующие данные:

Мы получаем следующий результат:

Примечания о функции СУММПРОИЗВ в Excel

Дополнительные ресурсы

Спасибо, что прочитали руководство CFI по функции СУММПРОИЗВ в Excel. Потратив время на изучение и освоение этих функций Excel, вы значительно ускорите свой финансовый анализ. Чтобы узнать больше, ознакомьтесь с этими дополнительными ресурсами CFI:

Функции Excel для финансов Excel для финансов В этом руководстве по Excel для финансов представлены 10 основных формул и функций, которые необходимо знать, чтобы стать отличным финансовым аналитиком в Excel.
Усовершенствованные формулы Excel, которые необходимо знать Усовершенствованные формулы Excel, которые необходимо знать Эти расширенные формулы Excel очень важны для понимания и выведут ваши навыки финансового анализа на новый уровень. Загрузите нашу бесплатную электронную книгу Excel!
Сочетания клавиш Excel для ПК и Mac Ярлыки Excel для ПК Mac Сочетания клавиш Excel — список наиболее важных и распространенных сочетаний клавиш MS Excel для пользователей ПК и Mac, специалистов в области финансов и бухгалтерского учета. Сочетания клавиш ускоряют ваши навыки моделирования и экономят время. Изучите редактирование, форматирование, навигацию, ленту, специальную вставку, работу с данными, редактирование формул и ячеек и другие сочетания клавиш.

Бесплатное руководство по Excel

Чтобы овладеть искусством работы с Excel, ознакомьтесь с БЕСПЛАТНЫМ ускоренным курсом CFI по Excel. Основы Excel — формулы для финансов Вы ищете ускоренный курс по Excel? Получите бесплатное обучение Excel для карьеры в области корпоративных финансов и инвестиционно-банковской деятельности от Института корпоративных финансов. , который научит вас, как стать опытным пользователем Excel. Изучите самые важные формулы, функции и сочетания клавиш, чтобы уверенно проводить финансовый анализ.

Запустите бесплатный курс CFI по Excel прямо сейчас Основы Excel - формулы для финансов Вы ищете ускоренный курс Excel? Пройдите бесплатное обучение Excel, чтобы начать карьеру в сфере корпоративных финансов и инвестиционно-банковских услуг, от Института корпоративных финансов.

СУММПРОИЗВ — это функция Excel, которая умножает диапазон ячеек или массивов и возвращает сумму произведений. Сначала он умножает, а затем складывает значения входных массивов. Это «математическая/триггерная функция». Его можно ввести как часть формулы в ячейку рабочего листа.Это очень находчивая функция, которую можно использовать по-разному в зависимости от требований пользователя.

Синтаксис

Синтаксис функции СУММПРОИЗВ: –

=СУММПРОИЗВ(массив1, [массив2,….. массив_n])

массив1 — этот параметр является первым массивом или диапазоном, который будет умножен, а затем добавлен
массив2….массив_n — эти параметры - это второй и последующие массивы или диапазоны, которые будут умножаться, а затем добавляться. Это необязательные параметры.

Как работает СУММПРОИЗВ

Давайте рассмотрим очень простой пример, чтобы попытаться понять, как работает функция СУММПРОИЗВ. Предположим, у нас есть 2 массива — и . Если мы используем функцию СУММПРОИЗВ для этих двух массивов, формула будет выглядеть так –

Выход после ввода этой формулы в ячейку будет равен 72. Давайте разберемся с математикой, лежащей в основе этого результата. Функция СУММПРОИЗВ вычисляет произведение следующим образом:

Как мы видим, функция СУММПРОИЗВ умножает, а затем складывает входные массивы. Входные массивы также могут быть записаны с точки зрения ссылки на ячейку. Итак, если массив поместить в ячейки A3:B4 и поместить в D4:E5, то функция будет иметь вид –

Когда мы вставим эту формулу в ячейку, результат будет таким же. Возвращаемое значение функции и в этом случае будет равно 72.

Свойства СУММПРОИЗВ

Поняв основы работы и использования функции СУММПРОИЗВ, давайте взглянем на некоторые свойства этой функции. Вот некоторые свойства функции СУММПРОИЗВ, которые следует знать перед ее использованием:

Некоторые примеры функции СУММПРОИЗВ

Давайте рассмотрим еще несколько примеров, чтобы лучше понять функцию СУММПРОИЗВ, а также оценить универсальность этой функции.

Предположим, у нас есть данные ячеек, которые содержат данные в текстовом формате в ячейках B4:B21, и мы хотим узнать общее количество символов в данных. Затем мы можем использовать функцию СУММПРОИЗВ для вычисления требуемого значения. Формула для этого будет –

В этой формуле есть еще одна функция (LEN), интегрированная с функцией СУММПРОИЗВ. Формула сначала вычисляет длину всех символов в ячейке данных, а затем добавляет их все.

Давайте рассмотрим другой пример. Представьте себе случай, когда у вас есть данные об инвентаризации товаров в магазине одежды. Доступны данные о стоимости различных товаров, таких как рубашки, джинсы, футболки и т. д., и их соответствующем количестве. Вы должны рассчитать среднюю стоимость продукта в этом магазине. Цена указана в формате A2:A16, а соответствующее количество — в формате B2:B16. Формула для расчета требуемого средневзвешенного значения будет следующей:

Полученное значение даст среднюю стоимость всех товаров, объединенных в этом магазине.

Еще один способ использования СУММПРОИЗВ – подсчет. Чтобы лучше понять это, давайте взглянем на другой пример. Предположим, у нас есть данные о количестве подписчиков для различных торговых точек в штате, и мы хотим узнать общее количество подписчиков для города. Мы можем использовать функцию СУММПРОИЗВ, чтобы вычислить количество подписчиков для города.

Допустим, у нас есть данные о розничных магазинах в Махараштре в A2:A16 и соответствующее количество подписчиков, зарегистрированных в розничном магазине в B2:B16. Чтобы вычислить общее количество подписчиков в городе, скажем, в Мумбаи, мы будем использовать формулу –

Эта формула даст общее количество подписчиков в Мумбаи. Обратите внимание, что в этой формуле используется условный оператор «=». Подписчики розничных магазинов в Мумбаи добавляются только благодаря этому условному оператору.

Эта формула работает по принципу ИСТИНА и ЛОЖЬ. При этом условии все розничные продажи в Мумбаи становятся ИСТИННЫМИ, а все остальные розничные продажи становятся ЛОЖНЫМИ. Затем аргумент B2:B16 просто вычисляет сумму значений, помеченных как ИСТИНА, что в данном случае относится к городу Мумбаи. В конечном итоге из наших данных мы получаем общее количество подписчиков в Мумбаи.

Заключение

В этой статье мы познакомились с функцией СУММПРОИЗВ. Затем мы рассмотрели, как работает эта функция, а также ее универсальность. Эту функцию можно использовать в большом количестве случаев.Он обрабатывает массивы простым способом и прост в использовании, если вы понимаете, как работает функция.

Автор

Харшита с энтузиазмом занимается наставничеством для абитуриентов JEE и помогает сделать блог Magoosh интересным и эффективным источником для обучения. Она любит задачи и программирование. Она также любит музыку и (думает, что она) неплохо танцует.

Читайте также: