Суммпроизв что за функция
history 3 апреля 2013 г.
Функция СУММПРОИЗВ() , английская версия SUMPRODUCT(), не так проста, как кажется с первого взгляда: помимо собственно нахождения суммы произведений, эта функция может использоваться для подсчета и суммирования значений на основе критериев, а также, в некоторых случаях, избавить от необходимости применений формул массива.
Существует несколько вариантов применения функции СУММПРОИЗВ() :
Нахождение суммы произведений элементов массивов
В этом разделе показан синтаксис функции СУММПРОИЗВ() и раскрыт ее потенциал для других применений.
Таким образом, можно найти сумму произведений 3-х, 4-х и т.д. массивов.
Аргументы, которые являются массивами, должны иметь одинаковые размерности (в нашем случае это массивы по 4 элемента). В противном случае функция СУММПРОИЗВ() возвращает значение ошибки #ЗНАЧ!.
В формуле =СУММПРОИЗВ(A3:A6;B3:B6) функция СУММПРОИЗВ() трактует нечисловые элементы массивов как нулевые. Однако, как показано ниже, функцию можно использовать для подсчета текстовых значений.
Синтаксис функции СУММПРОИЗВ() позволяет не просто указывать в качестве аргумента определенный диапазон, но и осуществлять арифметические действия перед операцией суммирования. Например, записав:
Оказывается, что в качестве аргумента этой функции можно указать не только произведение массивов ( A3:A6*B3:B6 ), но и использовать другие функции и даже применить к массивам операции сравнения, т.е. использовать ее для сложения чисел, удовлетворяющих определенным условиям.
Суммирование и подсчет значений удовлетворяющих определенным критериям
Итак, задача подсчета значений больше 2 решается следующим образом: =СУММПРОИЗВ(—(A3:A6>2))
Запись >2 является критерием, причем можно указать любые операции сравнения ( =; =).
Критерии можно указывать в форме ссылки: =СУММПРОИЗВ(—(A3:A6>G8)) – ячейка G 8 должна содержать число 2.
Функцию СУММПРОИЗВ() можно использовать для отбора значений по нескольким критериям (с множественными условиями). Как известно, 2 критерия могут образовывать разные условия:
В файле примера приведены решения подобных задач.
СУММПРОИЗВ() – как формула массива
Совет : Дополнительную информацию об этой функции можно ]]> подчерпнуть здесь (английский язык). ]]>
СУММПРОИЗВ (функция СУММПРОИЗВ)
Функция СУММПРОИВ ВОЗВРАЩАЕТ сумму продуктов соответствующих диапазонов или массивов. По умолчанию операция умножения, но возможна с добавлением, вычитанием и делением.
В этом примере мы используем СУММПРОИВ для возврата общего объема продаж для данного элемента и его размера:
SumPRODUCT соответствует всем экземплярам элемента Y/Size M и суммирует их, поэтому в данном примере «21 плюс 41» равен 62.
Синтаксис
Чтобы использовать операцию по умолчанию (умножение):
Аргументы функции СУММПРОИЗВ описаны ниже.
Первый массив, компоненты которого нужно перемножить, а затем сложить результаты.
От 2 до 255 массивов, компоненты которых нужно перемножить, а затем сложить результаты.
Выполнение других арифметических операций
Примечание: Если вы используете арифметические операторы, заключите аргументы массива в скобки и используйте скобки для группировки аргументов массива для управления порядком арифметических операций.
Примечания
Аргументы, которые являются массивами, должны иметь одинаковые размерности. В противном случае функция СУММПРОИЗВ возвращает значение ошибки #ЗНАЧ!. Например, =СУММПРОИВ(C2:C10;D2:D5) возвращает ошибку, так как диапазоны не одного размера.
В функции СУММПРОИВТ ненумерические записи массива обрабатывают их так, как если бы они были нулями.
Для лучшей производительности не следует использовать суммпроив с полными ссылками на столбцы. Рассмотрим функцию =СУММПРОИВ(A:A;B:B), чтобы умножить 1 048 576 ячеек в столбце A на 1 048 576 ячеек в столбце B перед их добавлением.
Пример 1
Пример 2
В следующем примере sumPRODUCT возвращает суммарные чистую сумму продаж по агенту продаж, у которых есть как общие продажи, так и расходы по агенту. В этом случае мы используем таблицу Excel, в которой используются структурированные ссылки вместо стандартных Excel диапазонов. Здесь вы увидите, что диапазоны «Продажи», «Расходы» и «Агент» имеют ссылку по имени.
Формула: =СУММПРОИМ(((Таблица1[Продажи])+(Таблица1[Расходы]))*(Таблица1[Агент]=B8)) и возвращает сумму всех продаж и расходов агента, указанных в ячейке B8.
Пример 3
В этом примере мы хотим получить общую сумму по конкретному товару, проданного в конкретном регионе. В данном случае, сколько вишней было продается в восточном регионе?
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Функция СУММПРОИЗВ в Excel с примерами ее использования
СУММПРОИЗВ в Excel – любимая функция бухгалтеров, т.к. она чаще всего используется для расчета заработной платы. Хотя она бывает, полезна и во многих других сферах.
По названию можно догадаться, что команда отвечает за суммирование произведений. Произведения при этом считаются либо диапазонами, либо целыми массивами.
Синтаксис СУММПРОИЗВ
Аргументами функции СУММПРОИЗВ являются массивы, т.е. заданные диапазоны. Их может быть сколько угодно. Перечисляя их через точку с запятой, мы задаем количество массивов, которые надо сначала перемножить, а затем просуммировать. Единственное условие: массивы должны быть равными по длине и однотипными (т.е. либо все горизонтальные, либо все вертикальные).
Простейший пример использования функции
Чтобы стало понятно, как и что считает команда, рассмотрим простой пример. Имеем таблицу с указанными длинами и ширинами прямоугольников. Нам нужно сосчитать сумму площадей всех прямоугольников. Если не пользоваться данной функцией, придется произвести промежуточные действия и сосчитать площадь каждого прямоугольника, а только потом сумму. Как мы и сделали.
Обратите внимание, что нам не понадобился массив с промежуточными итогами. В аргументах функции мы использовали только массивы с длиной и шириной, а функция их автоматически перемножила и просуммировала, выдав тот же результат = 70.
СУММПРОИЗВ с условием
Функция СУММПРОИЗВ в естественном виде почти не используется, потому что подсчет суммы произведений может редко пригодиться на производстве. Одно из популярных применений формулы СУММПРОИЗВ – для вывода значений, удовлетворяющих заданным условиям.
Рассмотрим пример. Имеем таблицу затрат небольшой компании за один расчетный месяц. Необходимо сосчитать общую сумму потраченных средств за январь и февраль по всем статьям расходов.
Для расчета затрат по канцелярии в январе-месяце, используем нашу функцию и указываем в начале 2 условия. Каждое из них заключаем в скобки, а между ними ставим знак «звездочка», подразумевающий союз «и». Получаем следующий синтаксис команды:
В итоге получилось, что в январе на канцелярские принадлежности было затрачено 3700 рублей. Протянем формулу на остальные строки и заменим в каждой из них условия (заменив месяц или статью расходов).
Функция СУММПРОИЗВ в 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
Функция СУММПРОИЗВ в 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];…)