В MS Excel достаточно много функций, которые упрощают расчеты в документах. Я уже писала статью про примеры использования СУММЕСЛИ и СУММЕСЛИМН. Последняя появилась только в Excel 2007, но в более ранних версиях ее отлично может заменить СУММПРОИЗВ , про которую мы сейчас поговорим. Разберемся, как ее применять на самом простом примере, а потом на более сложных.
Из названия понятно, что СУММПРОИЗВ отвечает за перемножение значений в указанных диапазонах, а потом суммирование полученных чисел. Аргументы достаточно просты – это массивы, которые надо перемножить, затем просуммировать. Их может быть сколько угодно, и разделяются они «;» . Только помните, что диапазоны с данными должны быть одинаковые по длине и все вертикальные, или горизонтальные.
Чтобы стало совсем понятно, возьмем вот такой пример. Предположим нужно посчитать, на какую сумму товара было продано. В таблице указано его наименование, количество проданных единиц и цена за одну штуку. Соответственно, необходимо сначала рассчитать, на какую сумму было продано отдельно табуреток, столов, диванов и остального, а потом сложить все числа в столбце D . Как видите, при этом нам нужно сделать один промежуточный расчет, эти значения занесены в D .
2 Функция Excel СУММПРОИЗВ
При использовании функции СУММПРОИЗВ нужно просто правильно указать для нее аргументы, и Вы тогда сразу получите результат.
Ставим знак «=» в ячейку D14 , пишем «СУММПРОИЗВ» и в скобках указываем сначала первый массив: В2:В10 , потом второй: С2:С10 .
Нажимайте «Enter» . Мы рассчитали нужное значение без промежуточных результатов и, как видите, два числа совпали.
В А15 я расписала, как считает функция. Она умножает по строкам числа в столбцах В и С , а потом их суммирует.
Теперь давайте немного усложним задачу, и добавим к аргументам функции дополнительные условия. Пример будет такой: завезены фрукты из разных стран, указано их привезенное и реализованное количество и цена покупки и продажи.
Обратите внимание, чтобы функция правильно работала, в диапазонах, которые Вы указываете не должно быть объединенных ячеек. То есть мне нужно повторить грушу в В6 , В7 , В8 .
Ставим равно, пишем СУММПРОИЗВ и указываем аргументы. Сначала будут условия:
(B:B=»Яблоко») – то есть нам нужно из столбца В искать только этот фрукт;
(С:С=»Турция») – чтобы оно было привезено из это страны.
Можете еще добавлять условия. Разделяются они «*» , это что-то вроде «И» . Если Вам не подходит выделение всего столбца полностью, тогда можете указать диапазон, например, (B4:B12=»Яблоко») . Также вместо «Яблоко» можно поставить ссылку на ячейку, в которой находится нужное значение: (B:B=»В4″) , причем ее лучше сделать абсолютной – $В$4 .
Как использовать функцию СУММПРОИЗВ (SUMPRODUCT)
Затем ставьте «;» и указывайте столбец, значения из которого нужно суммировать: F:F (или F4:F12 ).
В результате получится, сколько мы за все время заплатили за яблоки привезенные из Турции, но это только за 1 кг.
Полученную формулу можете вставить и в любую другую ячейку, изменить условия и значение также будет подсчитано.
Чтобы получить результат не за килограмм, а за столько, сколько мы завезли, нужно добавить в формулу еще один массив, с завезенным количеством: D:D . Тогда цена закупки умножится на число в столбце завезено, все прибавится и будет результат.
Давайте теперь подсчитаем, сколько мы получили за проданные яблоки по цене их реализации. Для этого в формуле оставляем условия, но меняем массивы на G:G;E:E . Учитывая, что мы не продали весь завезенный товар, доход есть и чистая прибыль: 6120-5100=1020 рублей.
Посчитаем тоже и для завезенной из Украины груши. На ней мы заработали немного больше.
Использовать СУММПРОИЗВ можно для разных расчетов. Есть такая таблица: здесь указано, какой продавец, в каком месяце, что продал и на какую сумму.
Чтобы узнать, сколько получилось с Катиных продаж за январь, нужно написать формулу:
То есть из столбца С выбираем имя продавца, D – месяц, и значения в F суммируем.
Изменяем условия и считаем продажи у остальных продавцов.
В функции СУММПРОИЗВ в условиях можно добавлять еще и сравнение. Добавляется оно к общим условиям через «*» .
Например, рассчитаем, сколько мы получили за яблоки из Турции проданных меньше или равно 10 кг. В аргументы допишем: Е:Е, = .
Используем эту же формулу для груш из Украины и Турции. Если сравнить полученные значения с предыдущими расчетами, то выйдет что яблоки в основном покупали меньше 10 килограмм, а для груш это только половина проданных.
Надеюсь, данные примеры использования функции СУММПРОИЗВ в Excel Вам понятны. Добавляйте к диапазонам значений дополнительные условия и сравнения, чтобы получать производить расчеты.
Источник: comp-profi.com
Функция СУММПРОИЗВ() — Сложение и подсчет с множественными условиями в EXCEL
Функция СУММПРОИЗВ() , английская версия SUMPRODUCT(), не так проста, как кажется с первого взгляда: помимо собственно нахождения суммы произведений, эта функция может использоваться для подсчета и суммирования значений на основе критериев, а также, в некоторых случаях, избавить от необходимости применений формул массива.
Существует несколько вариантов применения функции СУММПРОИЗВ() :
- нахождение суммы произведений элементов списка (массива);
- суммирование и подсчет значений, удовлетворяющих определенным критериям;
- замена формул массива (в некоторых случаях).
Нахождение суммы произведений элементов массивов
В этом разделе показан синтаксис функции СУММПРОИЗВ() и раскрыт ее потенциал для других применений.
Пусть имеется 2 диапазона чисел A3:A6 и B3:B6 , содержащие соответственно 2 массива чисел : и . Записав формулу =СУММПРОИЗВ(A3:A6;B3:B6) , получим 123. Результат получен поэлементным перемножением всех элементов двух массивов, а затем сложением полученных произведений. То есть были выполнены следующие арифметические действия: 4*7 + 8*6 + 6*7 + 1*5= 123
Таким образом, можно найти сумму произведений 3-х, 4-х и т.д. массивов.
Аргументы, которые являются массивами, должны иметь одинаковые размерности (в нашем случае это массивы по 4 элемента). В противном случае функция СУММПРОИЗВ() возвращает значение ошибки #ЗНАЧ!.
В формуле =СУММПРОИЗВ(A3:A6;B3:B6) функция СУММПРОИЗВ() трактует нечисловые элементы массивов как нулевые. Однако, как показано ниже, функцию можно использовать для подсчета текстовых значений.
Что произойдет если указать только 1 массив, т.е. =СУММПРОИЗВ(A3:A6) ? Тогда функция СУММПРОИЗВ() вернет сумму элементов, т.е. будет эквивалентна функции СУММ() : =СУММ(A3:A6) .
Синтаксис функции СУММПРОИЗВ() позволяет не просто указывать в качестве аргумента определенный диапазон, но и осуществлять арифметические действия перед операцией суммирования. Например, записав:
- =СУММПРОИЗВ(A3:A6*2) , получим сумму произведений =38 (каждый элемент массива из A3:A6 был умножен на 2, затем все произведения просуммированы);
- =СУММПРОИЗВ(A3:A6*B3:B6) , получим результат суммы произведений – 123 (все элементы массивов были попарно перемножены, а затем сложены, т.е. A3*B3+ A4*B4+ A5*B5+ A6*B6), т.е. эта запись эквивалента формула =СУММПРОИЗВ(A3:A6;B3:B6) ;
- =СУММПРОИЗВ(A3:A6+B3:B6) , получим сумму элементов из двух диапазонов;
- =СУММПРОИЗВ(A3:A6/B3:B6 ), получим сумму попарных отношений всех элементов, т.е. 4/7 + 8/6 + 6/7 + 1/5= 2,9619
Аналогичные вычисления можно выполнить и с функцией СУММ() , только для этого нужно ее ввести как формулу массива , т.е. после ввода функции в ячейку вместо ENTER нажать CTRL+SHIFT+ENTER : =СУММ(A3:A6/B3:B6)
Прелесть функции СУММПРОИЗВ() в том, что после ввода функции в ячейку можно просто нажать ENTER , что снимает некий психологический барьер перед использованием формул массива .
Оказывается, что в качестве аргумента этой функции можно указать не только произведение массивов ( A3:A6*B3:B6 ), но и использовать другие функции и даже применить к массивам операции сравнения, т.е. использовать ее для сложения чисел, удовлетворяющих определенным условиям.
Суммирование и подсчет значений удовлетворяющих определенным критериям
Попробуем подсчитать число значений больших 2 в диапазоне A3:A6 , содержащий значения 4, 8, 6, 1.
Если мы запишем формулу =СУММПРОИЗВ(A3:A6>2) , то получим результат 0. Выделив в Строке формул A3:A6>2 и нажав клавишу F9 , получим массив , который говорит, что мы движемся в правильном направлении: в диапазоне A3:A6 больше 2 только первые 3 значения. Хотя значению ИСТИНА соответствует 1, а ЛОЖЬ – 0, мы не получим 3, т.к. для перевода значений ИСТИНА/ЛОЖЬ в числовую форму требуется применить к ним арифметическую операцию. Для этого можно, например, применить операцию двойного отрицания (—), что позволит привести массив в числовую форму .
Итак, задача подсчета значений больше 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. Условие И)) .
Функцию СУММПРОИЗВ() можно использовать для отбора значений по нескольким критериям (с множественными условиями). Как известно, 2 критерия могут образовывать разные условия:
- Условие ИЛИ . Например, подсчитать ячеек содержащих значение яблоки ИЛИ груши =СУММПРОИЗВ((B3:B6=»яблоки»)+ (B3:B6=»груши») ) ;
- Условие И . Например, подсчитать количество значений больше 2 и меньше 5: =СУММПРОИЗВ((A3:A6>2)* (A3:A6 )
- Условие И . Например, найти сумму Чисел больше 2 и меньше 5: =СУММПРОИЗВ((A3:A6>2)* (A3:A6 A3:A6 ) )
В файле примера приведены решения подобных задач.
СУММПРОИЗВ() – как формула массива
В ряде случаев (когда нужно подсчитать или сложить значения, удовлетворяющие определенным критериям) можно заменить использование формул массива функцией СУММПРОИЗВ() , например:
- =СУММПРОИЗВ(—ЕПУСТО(D2:D23)) подсчет пустых ячеек в диапазоне;
- =СУММПРОИЗВ(НАИБОЛЬШИЙ(A:A;)) сумма 3-х наибольших значений ;
- =СУММПРОИЗВ((A3:A6>СРЗНАЧ(A3:A6))*(A3:A6)) сумма значений, которые больше среднего .
Совет : Дополнительную информацию об этой функции можно подчерпнуть здесь (английский язык).
Источник: excel2.ru
Как не забивать гвозди микроскопом с функцией СУММПРОИЗВ
Базовый синтаксис нашей функции прост: =СУММПРОИЗВ( Массив1 ; Массив2 ; . ) Самый скучный вариант использования этой замечательной функции — применять ее так, как описано в справке — для суммирования попарных произведений ячеек в двух (и более) указанных диапазонах. Например, можно без дополнительного столбца расчитать общую стоимость заказа:
По сути, то, что делает эта функция можно выразить формулой: =B2*C2+B3*C3+B4*C4+B5*C5 Технически, перемножаемых массивов (диапазонов) может быть не два, а три или больше (до 255). Главное, чтобы они были одного размера. Удобно, но ничего особенно. Однако, использовать СУММПРОИЗВ только так — забивать гвозди микроскопом, ибо, на самом деле, она умеет гораздо больше.
Работа с массивами без Ctrl+Shift+Enter
Если вы хоть немного знакомы в Excel с формулами массива, то должны понимать их мощь и красоту. Иногда одна формула массива может заменить несколько столбцов дополнительных вычислений и ручного труда. Но у формул массива есть и минусы.
Главные — это относительная сложность понимания, замедление пересчета книги и необходимость вводить эти формулы сочетанием Ctrl+Shift+Enter вместо обычного Enter. И вот как раз с последним может помочь наша функция СУММПРОИЗВ. Нюанс в том, что она умеет работать с массивами по определению, т.е. не требует обязательного нажатия Ctrl+Shift+Enter при вводе. На этом факте основано большинство трюков с использованием СУММПРОИЗВ (SUMPRODUCT) . Давайте, для примера, рассмотрим пару-тройку наиболее характерных сценариев.
Подсчет количества выполненных условий
Допустим, нам нужно посчитать количество филиалов компании, где план выполнен (т.е. факт больше или равен плану). Это можно сделать одной формулой с СУММПРОИЗВ без дополнительных столбцов:
Умножение на 1, в данном случае, нужно, чтобы преобразовать результаты сравнения плана и факта — логическую ИСТИНУ и ЛОЖЬ в 1 и 0, соответственно.
Проверка нескольких условий
Если нужно проверять больше одного условия, то формулу из предыдущего примера нужно будет дополнить еще одним (или несколькими) множителями. И если нужно подсчитывать не количество, а сумму, то умножать можно не на 1, а на диапазон с суммируемыми данными:
Фактически, получается что-то весьма похожее на математическую функцию выборочного подсчета СУММЕСЛИМН (SUMIFS) , которая также умеет проверять несколько условий (до 127) и суммировать по ним значения из заданного диапазона.
Логические связки И и ИЛИ (AND и OR)
Если нужно связывать условия не логическим «И», как в примере выше (Факт>=План) И (Регион=Восток) , а логическим ИЛИ, то конструкция немного изменится — знак умножения заменяется на плюс:
Подсчет по данным из закрытого(!) файла
Кроме всего вышеперечисленного, у СУММПРОИЗВ есть еще одно неочевидное и весьма полезное свойство — она умеет работать с данными из неоткрытых книг. Если, для сравнения, попробовать подсчитать в другом файле количество филиалов из региона Восток нашей книги и написать вот такое:
. то вторая формула с классической функцией СЧЁТЕСЛИМН (COUNTIFS) будет работать только до тех пор, пока исходный файл открыт. Если его закрыть, то появляется ошибка #ЗНАЧ! Наша же функция СУММПРОИЗВ (SUMPRODUCT) спокойно считает по данным даже из неоткрытой книги!
Ссылки по теме
- Что такое формулы массива и как они работают
- Как производить выборочные вычисления по одному или нескольким критериям
Источник: www.planetaexcel.ru