что значит суммпроизв в excel

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

Функция СУММПРОИВ ВОЗВРАЩАЕТ сумму продуктов соответствующих диапазонов или массивов. По умолчанию операция умножения, но возможна с добавлением, вычитанием и делением.

В этом примере мы используем СУММПРОИВ для возврата общего объема продаж для данного элемента и его размера:

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 рублей. Протянем формулу на остальные строки и заменим в каждой из них условия (заменив месяц или статью расходов).

Источник

Использование СУММПРОИЗВ для суммирования произведений соответствующих значений в одном или нескольких массивах

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

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

Чтобы создать формулу, введите =СУММПРОИВЕД(B3:B6;C3:C6) и нажмите ввод.

Каждая ячейка в столбце B умножается на соответствующую ячейку в той же строке столбца C, и результаты добавляются. Общая сумма для продуктов составляет 21,60 долларов США.

Чтобы создать более длинную формулу, которая дает такой же результат, введите =B3*C3+B4*C4+B5*C5+B6*C6 и нажмите ввод.

После нажатия ввода результат будет таким же: 21,60 долларов США. Ячейка B3 умножается на C3, а ее результат добавляется к результату ячейки B4, умноженной на ячейку C4 и так далее. Представьте, что если у вас тысячи (или даже сотни) строк, то насколько проще было бы использовать суммпроидуц.

Длина столбцов или строк (массивов) должна быть равной.

Если текст, а не число, находится в ячейке, он будет обработан как нуль.

Пример

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

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Источник

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 критерия могут образовывать разные условия:

В файле примера приведены решения подобных задач.

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

Совет : Дополнительную информацию об этой функции можно ]]> подчерпнуть здесь (английский язык). ]]>

Источник

Функция СУММПРОИЗВ в 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

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

Источник