Суммпроизв с условием в excel

Суммпроизв с условием в excel

Стандартное использование

Базовый синтаксис нашей функции прост:

=СУММПРОИЗВ( Массив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) спокойно считает по данным даже из неоткрытой книги!

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

Из названия понятно, что СУММПРОИЗВ отвечает за перемножение значений в указанных диапазонах, а потом суммирование полученных чисел. Аргументы достаточно просты – это массивы, которые надо перемножить, затем просуммировать. Их может быть сколько угодно, и разделяются они «;» . Только помните, что диапазоны с данными должны быть одинаковые по длине и все вертикальные, или горизонтальные.

Читайте также:  Программа определяющая температуру компьютера

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

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

Ставим знак «=» в ячейку D14 , пишем «СУММПРОИЗВ» и в скобках указываем сначала первый массив: В2:В10 , потом второй: С2:С10 .

Нажимайте «Enter» . Мы рассчитали нужное значение без промежуточных результатов и, как видите, два числа совпали.

В А15 я расписала, как считает функция. Она умножает по строкам числа в столбцах В и С , а потом их суммирует.

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

Обратите внимание, чтобы функция правильно работала, в диапазонах, которые Вы указываете не должно быть объединенных ячеек. То есть мне нужно повторить грушу в В6 , В7 , В8 .

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

(B:B=»Яблоко») – то есть нам нужно из столбца В искать только этот фрукт;

(С:С=»Турция») – чтобы оно было привезено из это страны.

Можете еще добавлять условия. Разделяются они «*» , это что-то вроде «И» . Если Вам не подходит выделение всего столбца полностью, тогда можете указать диапазон, например, (B4:B12=»Яблоко») . Также вместо «Яблоко» можно поставить ссылку на ячейку, в которой находится нужное значение: (B:B=»В4″) , причем ее лучше сделать абсолютной – $В$4 .

Затем ставьте «;» и указывайте столбец, значения из которого нужно суммировать: F:F (или F4:F12 ).

В результате получится, сколько мы за все время заплатили за яблоки привезенные из Турции, но это только за 1 кг.

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

Чтобы получить результат не за килограмм, а за столько, сколько мы завезли, нужно добавить в формулу еще один массив, с завезенным количеством: D:D . Тогда цена закупки умножится на число в столбце завезено, все прибавится и будет результат.

Давайте теперь подсчитаем, сколько мы получили за проданные яблоки по цене их реализации. Для этого в формуле оставляем условия, но меняем массивы на G:G;E:E . Учитывая, что мы не продали весь завезенный товар, доход есть и чистая прибыль: 6120-5100=1020 рублей.

Посчитаем тоже и для завезенной из Украины груши. На ней мы заработали немного больше.

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

Чтобы узнать, сколько получилось с Катиных продаж за январь, нужно написать формулу:

То есть из столбца С выбираем имя продавца, D – месяц, и значения в F суммируем.

Изменяем условия и считаем продажи у остальных продавцов.

В функции СУММПРОИЗВ в условиях можно добавлять еще и сравнение. Добавляется оно к общим условиям через «*» .

Читайте также:  Как удалить услугу вам звонили

Например, рассчитаем, сколько мы получили за яблоки из Турции проданных меньше или равно 10 кг. В аргументы допишем: Е:Е . Вы можете использовать знаки: , = .

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

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

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

Вычисление суммы произведения

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

Способ 1: использование математической формулы

Большинству пользователей известно, что в Экселе можно выполнить значительное количество математических действий просто поставив знак «=» в пустой ячейке, а далее записав выражение по правилам математики. Этот способ можно использовать и для нахождения суммы произведений. Программа, согласно математическим правилам, сразу подсчитывает произведения, а уже потом складывает их в общую сумму.

  1. Устанавливаем знак «равно» (=) в ячейке, в которой будет выводиться результат производимых вычислений. Записываем туда выражение суммы произведений по следующему шаблону:

Например, таким способом можно рассчитать выражение:

Способ 2: работа со ссылками

Вместо конкретных чисел в этой формуле можно указывать ссылки на ячейки, в которых они расположены. Ссылки можно вписать вручную, но удобнее это делать, выделив после знака «=», «+» или «*» соответствующую ячейку, в которой содержится число.

    Итак, сразу записываем выражение, где вместо чисел указаны ссылки на ячейки.

  • Затем, чтобы произвести подсчет, кликаем по кнопке Enter. Результат вычисления будет выведен на экран.
  • Конечно, данный вид вычисления довольно простой и интуитивно понятный, но в случае, если в таблице очень много значений, которые нужно перемножить, а потом сложить, этот способ может занять очень много времени.

    Способ 3: использование функции СУММПРОИЗВ

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

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

    Синтаксис данной функции имеет такой вид:

    Аргументами этого оператора являются диапазоны данных. При этом они сгруппированы по группам множителей. То есть, если отталкиваться от шаблона, о котором мы говорили выше (a1*b1*…+a2*b2*…+a3*b3*…+…), то в первом массиве располагаются множители группы a, во втором – группы b, в третьем – группы c и т.д. Данные диапазоны обязательно должны быть однотипными и равными по длине. Располагаться они могут, как вертикально, так и горизонтально. Всего данный оператор может работать с количеством аргументов от 2 до 255.

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

    Читайте также:  Пуски протона в 2019 году

      Выделяем ячейку на листе, в которой будет выводиться итоговый результат. Жмем на кнопку «Вставить функцию». Она оформлена в виде пиктограммы и располагается слева от поля строки формул.

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

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

  • После этих действий программа самостоятельно производит все требуемые вычисления и выводит итоговый результат в ту ячейку, которая была выделена в первом пункте данной инструкции.
  • Способ 4: применение функции по условию

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

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

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

    После того, как все данные внесены, жмем кнопку «OK».

    Приложение производит вычисление. В расчет берутся только строчки, в которых присутствует имя «Парфенов Д.Ф», то есть то, что нам и нужно. Результат вычислений отображается в предварительно выделенной ячейке. Но результат равен нулю. Это связано с тем, что формула в том виде, в котором она существует сейчас, работает некорректно. Нам нужно её немного преобразовать.

  • Для того чтобы преобразовать формулу, выделяем ячейку с итоговым значением. Выполняем действия в строке формул. Аргумент с условием берем в скобки, а между ним и другими аргументами точку с запятой меняем на знак умножения (*). Жмем на кнопку Enter. Программа производит подсчет и на этот раз выдает уже корректное значение. Мы получили общую сумму заработной платы за три месяца, которая причитается сотруднику предприятия Парфенову Д. Ф.
  • Таким же образом можно применять условия не только к тексту, но и к числам с датами, добавляя знаки условия « », «=», « ».

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

    Отблагодарите автора, поделитесь статьей в социальных сетях.

    Ссылка на основную публикацию
    Сталкер зов припяти лучшее оружие в игре
    S.T.A.L.K.E.R.: Call of Pripyat 4,260 уникальных посетителей 105 добавили в избранное "Уникальная модель пистолета СИП-т М200. Была выпущена малой партией...
    Соевый соус стебель бамбука классический отзывы
    Всем доброго дня!Много мнений по этому поводу, как вы считаете, соевый соус или морская соль, что менее вредно для организма....
    Соевый соус ямаса отзывы
    Полное наименование: Соевый Соус классический (натурально сваренный) Изготовитель: Yamasa Corporation Все характеристики Соевый соус Yamasa: Результаты теста Достоинства Безопасный Не...
    Сталкер зов припяти много оружия
    Для Всех любителей отличного отечественного шутера S.T.A.L.K.E.R.Зов Припяти представлен новый Оружейный мод Автоматы Штурмовые винтовки:1. АК-472. АКS-47 тактический3. АК-113 "Монгол"4....
    Adblock detector