Что означает функция суммпроизв
Функция СУММПРОИЗВ в Excel — применение, синтаксис, примеры
Excel – довольно популярная программа в бухгалтерском учете. И специалисты этой сферы нередко используют функцию СУММПРОИЗВ в своем нелегком деле. Она позволяет им быстро рассчитать заработную плату.
Как может понять догадливый читатель, с помощью такой формулы можно суммировать два произведения. Множителями могут выступать или отдельные диапазоны, или целые массивы данных. Более того, эта формула может использоваться в качестве замены формулы массива. Но давайте обо всем по порядку.
Функция СУММПРОИЗВ — подробное описание
Сами способы следующие:
На самом деле, возможных вариаций методов использования функции СУММПРОИЗВ значительно больше. Опытный пользователь Excel с легкостью сможет комбинировать несколько функций для того, чтобы заставить электронную таблицу работать так, как надо именно ему.
Синтаксис функции СУММПРОИЗВ
В качестве аргументов для этой функции используется набор диапазонов, которые сначала множатся между собой, а потом получившиеся результаты суммируются. Разделение аргументов осуществляется с помощью точки с запятой. Важно учитывать, что массивы не могут быть разного типа. Простыми словами, можно использовать либо только вертикальные, либо только горизонтальные диапазоны.
Давайте приведем наиболее простой для понимания пример применения этой функции. А потом будем постепенно усложнять задачу, демонстрируя все более новые особенности ее использования.
Видим, что результат использования функции СУММПРОИЗВ получился идентичным.
Внимание! Мы использовали исключительно значения длины и ширины в качестве аргументов функции. Колонка с площадью по каждому осталась для того, чтобы возможно было наглядно сравнить функции СУММ и СУММПРОИЗВ.
Функция СУММПРОИЗВ с условием
В чистом виде функция СУММПРОИЗВ используется довольно редко. Особенно в бухгалтерском учете. Тяжело представить ситуацию, что она оказывается непосредственно полезной каждый день для того, чтобы рассчитывать сумму произведений. Впрочем, такие ситуации тоже бывают. Например, если поставлена задача перемножить между собой курс и сумму в долларах, а потом получить общий финансовый показатель, основанный на стоимости каждой продукции в национальной валюте. Но такая задача все равно случается довольно редко.
При этом данная формула очень часто используется для того, чтобы выводить значения, основываясь на определенных условиях.
Значительно проще рассмотреть это на реальном примере. Предположим, у нас есть таблица расходов крошечной организации за один месяц. Перед нами стоит задача определить, сколько денег было потрачено в итоге за два первых месяца года суммарно по всем статьям расходов.
Чтобы выполнить эту задачу с помощью этой функции, необходимо ее использовать и записать два условия в самом начале. Каждое из условий заключается в скобках. Поскольку в нашем случае должно быть соответствие сразу двум критериям, то мы использовали символ звездочки (*). Синтаксис виден на этом скриншоте. Вам достаточно просто подставить соответствующие вашему случаю значения в необходимые места. И дело сделано!
3
Давайте рассмотрим аргументы более подробно:
Итоговый результат – 3700 рублей. Если распространить эту формулу на остальные строки и заменить условия в каждой из них, можно получить значения, характерные для другого отчетного периода или иной статьи расходов. К сожалению, лучше автоматизировать этот процесс не получится. Придется некоторые показатели править вручную. Но такова жизнь. Правда, если научиться писать макросы, можно добиться абсолютно любых уровней автоматизации при работе с электронными таблицами.
4
Сравнение в функции СУММПРОИЗВ
Аргументы в функции используем такие же, но к ним добавляем оператор сравнения. То бишь, в нашем случае – D:D
5
Что это за показатель? А это та тысяча, которую использовали для покупки карандашей в начале года. А после того, как нами был добавлен дополнительный критерий, ответ получился следующим.
6
Теперь давайте опять формулу протянем на все оставшиеся ячейки и частично изменим информацию. После этого мы увидим общую сумму затраченных средств по каждой статье расходов.
Стандартное использование СУММПРОИЗВ
То есть, функция попарно складывает два числа из каждого массива, а потом перемножает их между собой. Преимущество этой функции в том, что можно обойтись без дополнительных столбцов, как было описано в примере выше. Все нужные операции выполняются автоматически. Все можно выразить всего лишь одной простой формулой.
Количество диапазонов, которые можно использовать, ограничено 255 штуками. Но этого более, чем достаточно. Главное требование – они должны быть одного размера.
7
По сути, функция аналогична такому выражению.
Логические связки И и ИЛИ (AND и OR)
8
Нахождение суммы произведений элементов массивов
Теперь давайте более подробно раскроем, как с помощью функции можно находить сумму произведений элементов массивов.
Синтаксис такой же самый, потому что фактически эта формула и работает с массивами данных. Именно поэтому она имеет огромные перспективы сразу в разных областях деятельности.
Эта функция выполняет поэлементные операции со всеми диапазонами (пусть их будет даже 200), которые входят в состав массива.
Обязательное требование к массивам – наличие одинаковых размерностей. Если массивы будут содержать разное количество элементов, будет возвращена ошибка #ЗНАЧ!.
В целом, эта формула не предназначена для использования с нечисловыми значениями. В таких случаях они будут читаться, как ноль. Но в некоторых случаях можно работать с текстовыми значениями. Как правило, это если речь идет о соответствии определенным условиям. Давайте раскроем эту тему более подробно.
Суммирование и подсчет значений отвечающих критериям
Если мы получаем такой результат, то значит, все хорошо.
Здесь надо учитывать один нюанс: если значение ИСТИНА равняется единице, а ЛОЖЬ – нулю, это еще не значит, что с этими числами можно выполнять какие-либо математические операции. Чтобы это сделать, необходимо использовать два знака минуса, которые находятся рядом. В этом случае массив будет переведен в числовую форму, и с ним можно выполнять математические операции.
Вопрос, а как возможно использование критериев применительно к текстовым значениям. Вот простая формула, после которой все сразу становится понятно.
То есть, мы облачаем слово в кавычки, после чего просто приравниваем нужные ячейки к этому текстовому значению. Точно так же можно использовать логические операторы, чтобы проверять диапазон на предмет соответствия нескольким критериям.
Проверка нескольких условий
Чтобы осуществить проверку на предмет соответствия сразу нескольким условиям, необходимо умножать критерии между собой или складывать их (использовать знак +). То бишь, применять описанные выше логические операторы.
СУММПРОИЗВ как формула массива
=СУММПРОИЗВ(—ЕПУСТО(D2:D23)) – в этом случае ищутся пустые ячейки в диапазоне, подсчет которых осуществляется потом.
=СУММПРОИЗВ((A3:A6>СРЗНАЧ(A3:A6))*(A3:A6)) – здесь осуществляется отбор значений, которые больше среднего арифметического, после чего подсчитывается их сумма.
Подсчет по данным из закрытого файла
А теперь главная фишка. После того, как вы про нее узнаете, вы окончательно полюбите эту функцию. Дело в том, что она умеет находить информацию в тех файлах, которые не открыты на данный момент. Как известно, многие остальные функции умеют осуществлять поиск лишь в тех книгах, которые были ранее открыты на компьютере. В этом случае пользователь может даже их не открывать. Это особенно удобно, когда ресурсов компьютера мало (например, таблица большая, открыто много параллельных нужных вкладок в браузере, а оперативной памяти на компьютере мало). В этом случае эта функция будет как нельзя кстати.
Вот скриншот, где мы получаем данные о восточном филиале компании.
9
Таким образом, функция СУММПРОИЗВ дает возможность использовать целый спектр возможностей электронных таблиц, начиная теми функциями, для которых она создавалась и заканчивая нестандартными способами применения. Как видим, нет ничего сложного. Ею пользоваться значительно легче, чем массивами функций (хотя и в той теме нет ничего сложного). Тем не менее, функция СУММПРОИЗВ дает возможность полностью избежать необходимости использовать формулу массива для вычислений подобного рода. И это не может не радовать.
Что означает функция суммпроизв
Excel SUMPRODUCT функция
Синтаксис функции СУММПРОИЗВ в Excel:
Возвращает результат умножения и суммирования массивов.
Пример 1: базовое использование функции СУММПРОИЗВ
Для решения этой задачи примените следующую формулу:
объяснение: Эта формула SUMPRODUCT(B2:B7,C2:C7) = B2*C2+B3*C3+B4*C4+B5*C5+B6*C6+B7*C7.
А затем нажмите Enter key, ячейки в столбце B умножаются на соответствующие ячейки в той же строке столбца C, и результаты суммируются. Смотрите скриншот:
Пример 2: Суммирование ячеек с несколькими критериями с функцией СУММПРОИЗВ
За исключением функции СУММЕСЛИМН для суммирования значений ячеек на основе нескольких условий, функция СУММПРОИЗВ также может быстро и легко решить эту задачу.
Случай 1. Суммируйте значения ячеек, если они содержат определенный текст в другом столбце.
Предположим, у вас есть таблица данных, как показано на скриншоте ниже, теперь вы хотите рассчитать общую цену, которую продукт KTE и имя Дэвид, как вы могли бы сделать, используя формулу СУММПРОИЗВ в Excel?
1. Введите или скопируйте любую из следующих формул в пустую ячейку:
2. Затем нажмите Enter ключ для получения нужного вам результата:
Случай 2: Суммирование ячеек с несколькими критериями с логикой ИЛИ
Чтобы суммировать ячейки на основе нескольких критериев с логикой ИЛИ, вы должны использовать знак плюса (+), чтобы объединить несколько условий вместе в функции СУММПРОИЗВ.
Например, я хочу суммировать общую цену продукта KTE и KTO в столбце A, как показано ниже:
Вставьте следующую формулу в пустую ячейку:
А затем нажмите Enter key, рассчитана общая стоимость товара KTE и KTO, см. снимок экрана:
Случай 3: Суммирование ячеек с несколькими критериями с логикой ИЛИ и И
В некоторых ситуациях вам может потребоваться суммировать ячейки с помощью логики ИЛИ и И одновременно. Функция SUMPRODUCE также может легко решить эту задачу.
Звездочка (*) используется как оператор И.
Знак плюс (+) используется как оператор ИЛИ.
Давайте посмотрим на пример, для суммирования общей цены, какой продукт составляет KTE и KTO, когда продажа превышает 200.
Вы должны скопировать или ввести следующую формулу:
Затем нажмите Enter ключ для получения нужного вам результата:
Пример 3. Подсчет ячеек с несколькими критериями с помощью функции СУММПРОИЗВ
Как правило, эта функция СУММПРОИЗВ также может помочь нам подсчитать ячейки с несколькими критериями в Excel. Как и в случае значений суммы, вам просто нужно удалить аргумент sum_range из формулы.
Случай 1. Подсчет ячеек с несколькими критериями с логикой И
Здесь я хочу подсчитать ячейки, в которых продукт является KTE, а объем продаж превышает 200. Поэтому, пожалуйста, используйте любую из следующих формул:
А затем нажмите Enter ключ для получения номера результата:
Случай 2: подсчет ячеек с несколькими критериями с логикой ИЛИ
Для подсчета количества продуктов KTE и KTO в столбце A используйте следующую формулу:
А затем нажмите Enter ключ для получения номера товара КТЕ и КТО. Смотрите скриншот:
Случай 3: подсчет ячеек с несколькими критериями с помощью логики ИЛИ и И
Чтобы подсчитать количество товаров КТЕ и КТО, продажи которых превышают 200, необходимо применить следующую формулу:
Затем нажмите Enter ключ, чтобы получить нужный вам результат:
Пример 4: Вычислить средневзвешенное значение с помощью функции СУММПРОИЗВ
Обычно мы можем быстро и легко вычислить среднее значение диапазона ячеек. Но если вы хотите вычислить среднее значение для списка значений с разными уровнями релевантности, это означает вычислить средневзвешенное значение. Нет прямой функции для его получения в Excel. Но функция СУММПРОИЗВ может помочь вам с этим справиться.
Например, у меня есть таблица статистики оценок для ученика, каждая задача имеет разный вес, как показано на скриншоте ниже.
Для расчета средневзвешенного значения используйте следующую формулу:
объяснение: Эта формула: SUMPRODUCT(B2:B6, C2:C6) / SUM(C2:C6) = (B2*C2+B3*C3+B4*C4+B5*C5+B6*C6)/(C2+C3+C4+C5+C6)
А затем нажмите Enter key, средневзвешенное значение было рассчитано сразу, см. снимок экрана:
Лучшие инструменты для работы в офисе
Функция СУММПРОИЗВ в Excel с примерами ее использования
СУММПРОИЗВ в Excel – любимая функция бухгалтеров, т.к. она чаще всего используется для расчета заработной платы. Хотя она бывает, полезна и во многих других сферах.
По названию можно догадаться, что команда отвечает за суммирование произведений. Произведения при этом считаются либо диапазонами, либо целыми массивами.
Синтаксис СУММПРОИЗВ
Аргументами функции СУММПРОИЗВ являются массивы, т.е. заданные диапазоны. Их может быть сколько угодно. Перечисляя их через точку с запятой, мы задаем количество массивов, которые надо сначала перемножить, а затем просуммировать. Единственное условие: массивы должны быть равными по длине и однотипными (т.е. либо все горизонтальные, либо все вертикальные).
Простейший пример использования функции
Чтобы стало понятно, как и что считает команда, рассмотрим простой пример. Имеем таблицу с указанными длинами и ширинами прямоугольников. Нам нужно сосчитать сумму площадей всех прямоугольников. Если не пользоваться данной функцией, придется произвести промежуточные действия и сосчитать площадь каждого прямоугольника, а только потом сумму. Как мы и сделали.
Обратите внимание, что нам не понадобился массив с промежуточными итогами. В аргументах функции мы использовали только массивы с длиной и шириной, а функция их автоматически перемножила и просуммировала, выдав тот же результат = 70.
СУММПРОИЗВ с условием
Функция СУММПРОИЗВ в естественном виде почти не используется, потому что подсчет суммы произведений может редко пригодиться на производстве. Одно из популярных применений формулы СУММПРОИЗВ – для вывода значений, удовлетворяющих заданным условиям.
Рассмотрим пример. Имеем таблицу затрат небольшой компании за один расчетный месяц. Необходимо сосчитать общую сумму потраченных средств за январь и февраль по всем статьям расходов.
Для расчета затрат по канцелярии в январе-месяце, используем нашу функцию и указываем в начале 2 условия. Каждое из них заключаем в скобки, а между ними ставим знак «звездочка», подразумевающий союз «и». Получаем следующий синтаксис команды:
В итоге получилось, что в январе на канцелярские принадлежности было затрачено 3700 рублей. Протянем формулу на остальные строки и заменим в каждой из них условия (заменив месяц или статью расходов).
Примеры формул СУММПРОИЗВ с несколькими условиями в Excel
Для поиска значений по нескольким условиям эффективно использовать функцию СУММПРОИЗВ. Данная функция поддерживается во всех версиях Excel. А если использовать функцию СУММПРОИЗВ в формулах вместе с другими функциями – это позволить выполнять поиск не только для чисел, но и для текста.
Как работает функция СУММПРОИЗВ при поиске по нескольким условиям
Ниже на рисунке представлена таблица отделов и их бюджетов. Допустим нам необходимо создать формулу, которая после выбора пользователем двух условий: региона и отдела, будет возвращать соответственную сумму бюджета. В данной формуле нельзя просто использовать функцию ВПР так как она ищет только одно значение по одному условию. В этом примере необходимо найти сразу 2 искомых значения, так как названия регионов и отделов многократно повторяются в исходной таблице.
Чтобы получить значение из строки, содержащей одновременно 2 искомых значения, можно воспользоваться формулой:
Функция СУММПРОИЗВ приводит к тому, что проверяется значение каждой ячейки в определенных диапазонах A2:A15 и B2:B15 с указанными искомыми значениями в ячейках G4 и G5 – соответственно. В зависимости от результата сравнения ячеек возвращаются таблицы из логических значений ИСТИНА или ЛОЖЬ. В процессе перемножения таблиц в формуле, значение ИСТИНА воспринимается как число 1, а ЛОЖЬ – как число 0. Диапазон в третей паре скобок в первом аргументе функции СУММПРОИЗВ не содержит операторов сравнения, так как это диапазон итоговых значений, одно из которых правильное и будет возвращено функцией.
Если в результате сравнения значение ячеек в столбцах «Регион» или «Отдел» будет возвращено значение ЛОЖЬ, в тоже время расчетная сумма для данной строки будет равна нулю =0. Так как логическое значение ЛОЖЬ заменяется на число 0. Если же вместо этого будут совпадать названия региона и отдела, тогда в результате обоих сравнений будет возвращено число 1. Обе единицы будут перемножены между собой, что также равно 1. А затем эта же единица будет перемножена на числовое значение ячейки в столбце «Бюджет» и таким образом итоговый результат будет возвращен формулой.
В примере, изображенном на рисунке функция СУММПРОИЗВ считывает данные со строки листа №11 и выполняет арифметическую операцию умножения 1*1*697 697=697 697. Это же число суммируется с результатами умножения в других строках, в которых все равны 0. Так как результате умножения хоть-бы на один ноль (возвращаемый значением ЛОЖЬ при сравнении) в итоге получаем все равно 0. То есть как например в последней строке таблицы 1*0*930 133=0.
Поиск текста функцией СУММПРОИЗВ с несколькими условиями в Excel
Функцию СУММПРОИЗВ можно использовать в описаны выше способ только тогда, когда возвращаемое значение является числом. Если должен был быть возвращенный текст тогда все текстовые строки были бы посчитаны как нули, а функция постоянно возвращала бы итоговое значение 0.
Однако можно соединить функцию СУММПРОИЗВ с функциями ИНДЕКС и СТРОКА в одну формулу. Благодаря этому возвращаемый результат будет текстом. Если, например, должно быть возвращено имя и фамилия руководителя отдела можно использовать следующую формулу:
В данной формуле вместо диапазонов ячеек из столбца D используется функция СТРОКА возвращающая номер для каждой текущей строки исходной таблицы. Функция СУММПРОИЗВ анализирует строку листа Excel под номером 11 и снова выполняет арифметическую операцию умножение: 1*1*12=12. После чего данный результат умножения используется как аргумент для функции ИНДЕКС, которая охватывает целый столбец C:C, так как функция СТРОКА возвращает номера строк рабочего листа Excel, а не исходной таблицы.
Функция СУММПРОИЗВ для вычисления произведения суммы в Excel
Функция СУММПРОИЗВ в Excel предназначена для нахождения произведения элементов с одинаковыми индексами двух или более констант массивов или двух и более диапазонов ячеек с числовыми данными, а также последующего суммирования полученных значений, и возвращает итоговую сумму.
Примеры использования функции СУММПРОИЗВ в Excel
Рассматриваемая функция может быть использована для нахождения суммы значений, соответствующих каким-либо критериям, которые указываются в качестве одного или нескольких аргументов данной функции в виде логических выражений.
Расчет вероятности используя вычисление произведение суммы в Excel
Пример 1. На склад поступили новые товары от 5 различных производителей по 20% от каждого, при этом 25% товаров первого поставщика – высшего качества, второго – 18%, третьего – 27%, четвертого – 55%, пятого – 5% соответственно. Определить вероятность того, что случайно выбранный товар окажется высшего качества.
Вид таблицы данных:
Для определения вероятности выбора товара высокого качества необходимо найти сумму произведений вероятностей, которые записаны в ячейки столбцов B:B и C:C соответственно. Для этого используем формулу:
В результате вычислений получаем следующее значение вероятности:
Как вычислить произведение суммы числ по условию в Excel?
Пример 2. В таблице хранятся данные о поставщиках, видах товаров и суммарной стоимости. Определить общую сумму закупок хлеба от первого поставщика без использования функции СУММЕСЛИМН.
Вид таблицы данных:
Для расчета используем формулу:
В качестве первого и второго аргументов функции переданы логически выражения, проверяющие на соответствие указанным значениям («хлеб», «поставщик_1») диапазоны ячеек A2:A17 и B2:B17 соответственно. В результате выполнения этих выражений будут получены массивы логических значений ИСТИНА (если совпадение) и ЛОЖЬ, которые будут преобразованы в массивы числовых значений 1 или 0 благодаря использованию двойного отрицания «—». C2:C17 – диапазон ячеек со значениями стоимости. В результате перемножения элементов и сложения произведений получим следующую суммарную закупочную стоимость хлеба от первого поставщика:
Расчет количества отклонений с помощью функции СУММПРОИЗВ в Excel
Пример 3. В цехе по производству деталей работают несколько работников определенного разряда, для каждого из которых установлена норма выработки за месяц. Определить число сотрудников третьего и выше разряда, не выполнивших свою месячную норму.
Вид таблицы данных:
Для расчета используем следующую формулу:
Первый аргумент указан в виде логического выражения для сравнения значений в диапазонах, содержащих фактическую и требуемую продуктивности для каждого работника. Функция НЕ используется для возврата обратного значения. Второй аргумент указан в виде выражения для проверки разряда каждого сотрудника. Умножение на единицу выполняется для преобразования логических данных к числам.
В результате вычислений получим следующее число не выполнивших норму сотрудников:
Особенности использования функции СУММПРОИЗВ в Excel
Функция имеет следующий синтаксис:
=СУММПРОИЗВ( массив1; [массив2];[массив3];…)