Функция СУММПРОИЗВ в Excel — применение, синтаксис, примеры

Функция СУММПРОИЗВ — подробное описание

Несмотря на кажущуюся на первый взгляд простоту, эта функция не так проста. А также спектр его применения. Это потому, что он выполняет несколько действий одновременно. Таким образом, с его помощью вы можете суммировать значения на основе определенных критериев. Это также позволяет вам избавиться от необходимости использовать формулы массива в вашей работе, которые чаще всего приходится использовать для стандартной функции СУММ.

Сами методы следующие:

1. Определение суммы произведений массива.
2. Проверка стоимости на соответствие определенным критериям, получение их произведений и суммирование.
3. Используйте вместо формулы массива.

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

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

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

Давайте рассмотрим наиболее простой для понимания пример использования этой функции. А дальше мы будем постепенно усложнять задачу, демонстрируя все новые и новые возможности ее использования.

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

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

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

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

 

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

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

Кроме того, эта формула очень часто используется для отображения значений в зависимости от определенных условий.

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

Чтобы сделать это с помощью этой функции, вам нужно использовать ее и вначале отметить два условия. Каждое из условий заключено в круглые скобки. Поскольку в нашем случае должны выполняться одновременно два критерия, мы использовали символ звездочки (*). Синтаксис можно увидеть на этом скриншоте. Вам просто нужно подставить соответствующие значения для вашего случая в нужные места. И готово!

Давайте подробнее рассмотрим аргументы:

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

Итоговый результат 3700 руб. Если распространить эту формулу на остальные строки и заменить термины в каждой из них, можно получить конкретные значения для другого отчетного периода или другой статьи расходов. К сожалению, лучшего способа автоматизировать этот процесс нет. Придется вручную изменить некоторые индикаторы. Но это жизнь. Правда, если вы научитесь писать макросы, вы сможете достичь любого уровня автоматизации при работе с электронными таблицами.

Сравнение в функции СУММПРОИЗВ

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

Мы используем те же аргументы в функции, но добавляем к ним оператор сравнения. То есть в нашем случае — D: D <= 1000. В результате расчетов цифра составила 1000.

Что это за индикатор? И это та тысяча, на которую в начале года купили карандаши. И после добавления еще одного критерия ответ был следующий.

Теперь давайте снова расширим формулу на все оставшиеся ячейки и частично изменим информацию. Далее мы увидим общую сумму, потраченную по каждой статье расходов.

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

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

= СУММПРОИЗВ (Матрица1; Матрица2; …)

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

Количество доступных диапазонов ограничено 255. Но этого более чем достаточно. Главное требование — они должны быть одного размера.

По сути, функция похожа на это выражение.

= B2 * DO2 + B3 * DO3 + B4 * DO4 + B5 * DO5

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

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

Нахождение суммы произведений элементов массивов

Теперь давайте более подробно объясним, как можно использовать функцию для нахождения суммы произведений элементов массива.

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

Эта функция выполняет поэлементные операции со всеми диапазонами (даже если их 200), которые являются частью массива.

Обязательным требованием к массивам является их одинаковый размер. Если массивы содержат другое количество элементов, будет возвращена ошибка # ЗНАЧ!.

Как правило, эта формула не предназначена для использования с нечисловыми значениями. В таких случаях они будут считаны равными нулю. Но в некоторых случаях вы можете работать с текстовыми значениями. Как правило, это когда дело касается выполнения определенных условий. Разберемся в этой теме подробнее.

Как использовать СУММЕСЛИ – Примеры.

Определив синтаксис, давайте теперь попробуем использовать функцию СУММЕСЛИ на практике.

СУММЕСЛИ больше, меньше или равно

Давайте взглянем на некоторые формулы СУММЕСЛИ, которые вы можете использовать для сложения значений, больших, меньших или равных заданному значению.

Поясним примеры на скриншоте выше:

• Пример 1. Добавляет значения больше 6 в диапазон ячеек A5: A15.
• Пример 2. Складывает значения в диапазоне ячеек B5: B15, если в соответствующем столбце A меньше 8.
• Пример 3. Складывает значения в ячейках A5: A15, которые равны значению в ячейке C4. Два варианта написания
• Пример 4. Складывает значения в ячейках B5: B15, если соответствующая ячейка в столбце A отличается от значения в ячейке C4.
• Пример 5. Добавляет значения больше или равные 8 в диапазоне A5: A15.
• Пример 6. Складывает значения в ячейках B5: B15, если соответствующее значение в столбце A меньше или равно 8.

СУММЕСЛИ с текстовыми критериями

Помимо чисел, функция СУММЕСЛИ позволяет добавлять значения в зависимости от того, содержит ли соответствующая ячейка в другом столбце определенный текст или нет.

Поясним примеры на скриншоте выше:

• Пример 1. Складывает значения ячеек B2: B6, если соответствующая ячейка в столбце A содержит в точности слово вишня и никаких других слов или символов. Ячейки, содержащие вишню и яблоки, не будут включены.
• Пример 2. Складывает значения ячеек B2: B6, если соответствующая ячейка в столбце A содержит слово «шня» отдельно или в сочетании с любыми другими словами или символами. Ячейки, содержащие «вишню» и «вишню», будут добавлены, потому что они содержат «шня».
• Пример 3. Складывает значения ячеек B2: B6, если соответствующая ячейка в столбце A содержит значение, отличное от Cherry».
• Пример 4. Складывает значения ячеек B2: B6, если соответствующая ячейка в столбце A не содержит слова «шня» ни отдельно, ни в сочетании с любым другим словом. В этом случае будет отображаться только сумма яблок.

Как и большинство других функций Excel, СУММЕСЛИ не чувствительна к регистру, что означает, что Cherry, Cherry и Cherry дадут точно такой же результат.

Используйте операторы сравнения со ссылками на ячейки

Если вы хотите пойти еще дальше и получить универсальную формулу СУММЕСЛИ, вы можете заменить числовое или текстовое значение в критериях ссылкой на ячейку, например:

= СУММЕСЛИ (A2: A8; «<>» & F1; C2: C8)

В этом случае вам не нужно изменять формулу условной суммы на основе других критериев — просто введите новое значение в ссылочную ячейку.

Формулы СУММЕСЛИ с подстановочными знаками

Если вы намереваетесь условно оценивать ячейки на основе текстовых критериев и хотите получить совпадение с промежуточной суммой, вам нужно будет использовать подстановочные знаки в формуле СУММЕСЛИ. Доступны следующие подстановочные знаки:

• Звездочка (*): обозначает любое количество символов
• Знак вопроса (?): Обозначает символ в определенной позиции.
• Амперсанд (&): обозначает добавление текста. В следующем примере показано, как это работает.

Поясним примеры на скриншоте выше:

• Пример 1. Складывает значения ячеек B2: B6, если соответствующая ячейка в столбце A содержит частичное совпадение, то есть в начале и в конце любого количества символов.
• Пример 2. Складывает значения ячеек B2: B6, если соответствующая ячейка в столбце A содержит частичное совпадение, то есть последняя буква может быть любой. В этом примере я намеренно ошибся и написал вишну.
• Пример 3. Добавляет значения ячеек B2: B6, если соответствующая ячейка в столбце A содержит частичное совпадение, критерии включают ссылку на ячейку, заключенную в звездочки, обратите внимание на использование амперсанда (&) до и после ссылки на ячейку для объединения строки.

Если вы хотите добавить значения, состоящие ровно из 4 букв, используйте следующую формулу:

= СУММЕСЛИ (A2: A8; «????»; C2: C8)

Суммирование и подсчет значений отвечающих критериям

Если мы напишем такую ​​формулу, результат будет равен нулю: = СУММПРОИЗВ (A3: A6> 2). Если вы перейдете к строке формулы и выберете там часть, написанную в скобках, затем нажмите F9, вы получите матрицу: ИСТИНА: ИСТИНА и ИСТИНА: ЛОЖЬ.

Если получим такой результат, значит, все нормально.

Здесь необходимо учитывать один нюанс: если значение ИСТИНА равно единице, а ЛОЖЬ равно нулю, это не означает, что с этими числами можно выполнять какие-либо математические операции. Для этого нужно поставить два знака минус рядом друг с другом. В этом случае массив будет преобразован в числовую форму, и вы сможете выполнять с ним математические операции.

Вопрос в том, как можно использовать критерии по отношению к текстовым значениям. Вот простая формула, после которой все сразу становится понятно.

= SUMPRODOTTO (- (B3: B6 = «яблоки»))

То есть мы заключаем слово в кавычки, а затем просто определяем необходимые ячейки для этого текстового значения. Точно так же вы можете использовать логические операторы для управления диапазоном по нескольким критериям.

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

Чтобы проверить, выполняются ли одновременно несколько условий, необходимо умножить критерии друг на друга или сложить их (используйте знак +). То есть он использует логические операторы, описанные выше.

Примечания

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

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

• Для лучшей производительности не используйте sum с полными ссылками на столбцы. Рассмотрим функцию = SUMMIN (A: A; B: B), чтобы умножить 1 048 576 ячеек в столбце A на 1 048 576 ячеек в столбце B перед их добавлением.

Суммирование и подсчет значений удовлетворяющих определенным критериям

Попробуем подсчитать количество значений больше 2 в диапазоне A3: A6, содержащем значения 4, 8, 6, 1.

Если мы напишем формулу = СУММПРОИЗВ (A3: A6> 2), мы получим результат 0. Выбрав A3: A6> 2 в строке формул и нажав клавишу F9, мы получим массив {ИСТИНА: ИСТИНА: ИСТИНА: ЛОЖЬ. }, что говорит о том, что мы движемся в правильном направлении: в диапазоне A3: A6 только первые 3 значения больше 2. Хотя ИСТИНА равно 1, а ЛОЖЬ равно 0, мы не получим 3, потому что для преобразования ИСТИНА / FALSE в числовую форму, к ним необходимо применить арифметическую операцию. Для этого вы можете, например, применить операцию двойного отрицания (-), которая позволит вам преобразовать массив в числовую форму {1: 1: 1: 0}.

Следовательно, проблема подсчета значений больше 2 решается следующим образом: = СУММПРОИЗВ (- (A3: A6> 2))

Вместо двойного отрицания вы можете использовать другие формулы: = СУММПРОИЗВ (1 * (A3: A6> 2)) или = СУММПРОИЗВ (0+ (A3: A6> 2)) или даже так = СУММПРОИЗВ ((A3: A6> 2) ^ 1) .

Запись> 2 является критерием, и можно указать любую операцию сравнения (<; <=; >=; =).

Критерий можно указать в виде ссылки: = СУММПРОИЗВ (- (A3: A6> G8)) — ячейка G 8 должна содержать цифру 2.

Критерии также могут применяться к текстовым значениям, например = СУММПРОИЗВ (- (B3: B6 = «яблоки»)) — вернет количество ячеек, содержащих слово яблоки (подробнее, например, в статье Подсчет значений с несколькими критериями (Часть 1. Условие И)) .

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

• Или состояние. Например, подсчитайте ячейки, содержащие значение яблоки ИЛИ груши = СУММПРОИЗВ ((B3: B6 = «яблоки») + (B3: B6 = «груши»)) ;
• Условие I. Например, подсчитайте количество значений больше 2 и меньше 5: = СУММПРОИЗВ ((A3: A6> 2) * (A3: A6 <5))
• Условие I. Например, найдите сумму чисел больше 2 и меньше 5: = СУММПРОИЗВ ((A3: A6> 2) * (A3: A6 <5) * (A3: A6))

Образец файла содержит решения аналогичных проблем.

Пример 1

Чтобы создать формулу выше, введите = SUMPROVE (C2: C5; D2: D5) и нажмите Enter. Каждая ячейка в столбце C умножается на соответствующую ячейку в той же строке в столбце D, и результаты сокращаются. Общая стоимость товаров — 78,97 долларов США.

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

СУММПРОИЗВ как формула массива

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

= СУММПРОИЗВ (—ПРОСТОЙ (D2: D23)) — в этом случае в диапазоне ищутся пустые ячейки, которые затем подсчитываются.

= СУММПРОИЗВ ((A3: A6> AVERAGE (A3: A6)) * (A3: A6)) — здесь выбираются значения больше среднего арифметического, после чего вычисляется их сумма.

Формулы для средневзвешенного значения в Excel

В Microsoft Excel средневзвешенное значение рассчитывается с использованием того же подхода, но с гораздо меньшими усилиями, поскольку функции Excel делают большую часть работы за вас.

Пример 1. Функция СУММ.

Если у вас есть базовое понимание этого, следующая формула вряд ли потребует каких-либо пояснений:

= СУММ (B2 * C2; B3 * C3; B4 * C4; B5 * C5; B6 * C6) / СУММ (C2: C6)

По сути, он выполняет те же вычисления, что и описанные выше, за исключением того, что вы указываете ссылки на ячейки вместо чисел.

Посмотрите на изображение ниже — формула возвращает точно такой же результат, как и вычисления, которые мы провели минуту назад. Обратите внимание на разницу между нормальным средним значением, возвращаемым C8 AVERAGE, и средневзвешенным значением (C9).

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

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

Пример 2. Функция СУММПРОИЗВ

он идеально подходит для нашей задачи, так как предназначен для сложения произведений чисел. И это именно то, что нам нужно.

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

= СУММПРОИЗВ (диапазон_значений; диапазон_весов) / СУММ (диапазон_весов)

Предполагая, что значения для усреднения находятся в ячейках B2: B6, а значения значимости находятся в ячейках C2: C6, наша формула будет выглядеть следующим образом:

= СУММ ПРОДУКТ (B2: B6; C2: C6) / СУММ (C2: C6)

Затем формула умножает 1-е число в массиве 1 на 1-е число в массиве 2 (91 * 0,1 в этом примере), затем умножает 2-е число в массиве 1 на 2-е число в массиве 2 (85 * 0,15) в этом примере) и скоро. Когда все умножения будут выполнены, Excel сложит продукты. Затем делим результат на сумму весов.

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

В нашем случае добавление весов дает 100%. То есть это всего лишь процент от общей суммы. В этом случае правильный результат также можно получить следующими способами:

= СУММ (B2 * C2; B3 * C3; B4 * C4; B5 * C5; B6 * C6)

{= СУММ (B2: B6 * C2: C6)}

Это формула массива, не забудьте ввести ее, используя комбинацию клавиш CTRL + SHIFT + ENTER.

= СУММПРОИЗВ (B2: B6; C2: C6)

Однако при использовании функций СУММ или СУММПРОИЗВ веса не обязательно должны быть 100%. Однако их необязательно выражать в процентах.

Например, вы можете создать шкалу приоритета / важности и присвоить определенное количество баллов каждому элементу, как показано на следующем рисунке:

Понимаете, в данном случае мы обошлись без процентов.