Sumproduct в русском excel

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

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

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

СУММПРОИЗВ соответствует всем экземплярам элемента Y/size M и суммирует их, поэтому для этого примера 21 плюс 41 равняется 62.

Синтаксис

Чтобы использовать операцию по умолчанию (умножение), выполните указанные ниже действия.

= СУММПРОИЗВ (массив1; [массив2]; [массив3];. )

Аргументы функции СУММПРОИЗВ описаны ниже.

Первый массив, компоненты которого нужно перемножить, а затем сложить результаты.

От 2 до 255 массивов, компоненты которых нужно перемножить, а затем сложить результаты.

Выполнение других арифметических операций

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

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

Примечания

Аргументы, которые являются массивами, должны иметь одинаковые размерности. В противном случае функция СУММПРОИЗВ возвращает значение ошибки #ЗНАЧ!. Например, если = СУММПРОИЗВ (C2: C10; D2: D5), будет возвращена ошибка, так как диапазоны имеют одинаковый размер.

СУММПРОИЗВ обрабатывает нечисловые элементы массива так, как если бы они были нулевыми.

Пример 1

Чтобы создать формулу с помощью нашего примера списка выше, введите = СУММПРОИЗВ (C2: C5; D2: D5) и нажмите клавишу Ввод. Каждая ячейка в столбце C умножается на соответствующую ячейку в той же строке в столбце D, и результаты будут добавлены в нее. Общая сумма для сходить составляет $78,97.

Чтобы ввести более длинную формулу, которая приводит к тому же результату, введите = C2 * D2 + C3 * D3 + C4 * D4 + C5 * D5 и нажмите клавишу Ввод. После нажатия клавиши Ввод результат будет таким же: $78,97. Ячейка C2 умножается на D2, и ее результат добавляется в результат ячейки С3 и т. д.

Пример 2

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

Формула: = СУММПРОИЗВ (((Таблица1 [Sales]) + (Таблица1 [продажи])) * (Table1 [агент] = B8)) и возвращает сумму всех продаж и расходов для агента, указанного в ячейке B8.

Пример 3

В этом примере мы хотели бы вернуть сумму определенного элемента, проданного в определенном регионе. В этом случае количество вишний в регионе “Восток” продавалось?

Формула: = СУММПРОИЗВ ((B2: B9 = B12) * (C2: C9 = C12) * D2: D9). Сначала число вхождений Востока умножается на число совпадающих вхождений вишни. Наконец, она суммирует значения соответствующих строк в столбце Sales. Чтобы увидеть, как Excel вычисляет это, выделите ячейку с формулой, а затем перейдите к формулам > Вычислить формулу > оценить.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

См. также

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

Источник: support.office.com

Как не забивать гвозди микроскопом с функцией СУММПРОИЗВ

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

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

=СУММПРОИЗВ( Массив1 ; Массив2 ; . )

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

По сути, то, что делает эта функция можно выразить формулой:

= B2*C2 + B3*C3 + B4*C4 + B5*C5

Технически, перемножаемых массивов (диапазонов) может быть не два, а три или больше (до 255). Главное, чтобы они были одного размера. Удобно, но ничего особенно. Однако, использовать СУММПРОИЗВ только так – забивать гвозди микроскопом, ибо, на самом деле, она умеет гораздо больше.

Читайте также:  Как excel сохранить как картинку

Работа с массивами без 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

База Знаний: Функции Calc. SUMPRODUCT

Функция возвращает сумму произведений соответствующих элементов массивов.

Синтаксис функции:

=SUMPRODUCT(array1; array2; … array30)

  • array1; array2; … array30 — до 30 массивов или диапазонов одинакового размера, соответствующие элементы которых должны быть перемножены.

Функция SUMPRODUCT возвращает для всех элементов массивов. Возможно использовать функцию SUMPRODUCT для вычисления скалярного произведения двух векторов.

Функция SUMPRODUCT расценивает каждый параметр от array1 до array30 как формулу массива, но не должна вводиться как формула массива. Другими словами она может быть введена клавишей ↵ Enter , вместо комбинации клавиш для ввода формул массива Ctrl + ⇧ Shift + ↵ Enter (или со СНЯТЫМ флажком Массив , если используется Мастер формул).

Функция SUMPRODUCT может также использоваться для суммирования ячеек, которые удовлетворяют заданному условию — см. «Условный подсчёт и суммирование» и пример на рис. 1.

  • возвращается A1*F1 + B1*G1 + A2*F2 + B2*G2.

  • когда формула =SUMPRODUCT(ABS(A1:A6)) введена как «обычная», а не как формула массива, возвращает сумму абсолютных значений в ячейках A1:A6. Функция SUMPRODUCT заставляет ABS (A1:A6) вычисляться как формула массива.

  • формула =SUMPRODUCT(A1:A6=“красный”; B1_B6=“большой”; C1:C6) возвращает сумму ячеек в диапазона C1:C6, соответствующие элементы для которых в столбце A имеют значение «красный», а в столбце B«большой».

Источник: wiki.harlamenkov.ru

Функция СУММПРОИЗВ в Excel

Добрый день!

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

Читайте также:  Excel замена

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

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

= СУММПРОИЗВ(ваш_массив1;[ваш_массив2];[ваш_массив3];…и т.д.), где

  • ваш_массив1 – является обязательным критерием, в нем указываются те компоненты которые необходимо перемножить, а после результаты будут сложены;
  • ваш_массив2, ваш_массив3…. и т.д. – является необязательным критерием, но если количество ваших массивов, которые нужно перемножить и сложить полученные результаты, от 2 до 255 тогда они будут обязательны.

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

Возьмем таблицу по продажам фруктов и произведем вычисления с помощью функции СУММПРОИЗВ. Как видно для вычислений нужного нам значения была написана формула: =СУММПРОИЗВ((B2:B11=$H$5)*(D2:D11=$H$6);F2:F11), работу которой рассмотрим поподробнее. Вычисления «B2:B11=$H$5» означают что в массиве данных нужно выбрать всё с критерием «$H$5» или «Яковлев С.И.». Следующим действием «D2:D11=$H$6» мы отбираем в массиве данные со значением «$H$6» или «Яблоко». При совпадении значений в этих диапазонах происходит суммирование в массиве «F2:F11» и мы получаем результат.

Рекомендую в формулах использовать абсолютные ссылки, когда критериями у вас выступает значение в ячейках, это снизит риск возникновения ошибки в формулах во много раз.

В этом примере задачку можно решить и другими способами, с помощью других функций или цепочки вычислений, но часто это всё лишнее и намного проще написать формулу один раз и получить нужный результат. Теперь мы немного усложним наше задание и добавим еще условия в нашу задачку, такие как дата, нам вдруг стало интересно, сколько же яблок продал наш менеджер за последние две недели марта. Этот пример рассмотрим еще и потому что могут быть изменения в критериях товара, сейчас у нас написано «Яблоко», а ведь можно приписать сорт и всё изменится «Яблоко «Голд»» и тогда наша формула не сработает. Для решения нашей задачи нам нужна формула следующего вида: =СУММПРОИЗВ((B2:B11=$H$5)*(D2:D11=$H$6)*(E2:E11>=$H$9)*(E2:E11 =$H$9) и (E2:E11 =» (больше и равно) и «

“Бедность и богатство – суть слова для обозначения нужды и изобилия. Следовательно, кто нуждается, тот не богат, а кто не нуждается, тот не беден.

Демокрит

Источник: topexcel.ru

База Знаний: Функции Calc. SUMPRODUCT

Функция возвращает сумму произведений соответствующих элементов массивов.

Синтаксис функции:

=SUMPRODUCT(array1; array2; … array30)

  • array1; array2; … array30 — до 30 массивов или диапазонов одинакового размера, соответствующие элементы которых должны быть перемножены.

Функция SUMPRODUCT возвращает для всех элементов массивов. Возможно использовать функцию SUMPRODUCT для вычисления скалярного произведения двух векторов.

Функция SUMPRODUCT расценивает каждый параметр от array1 до array30 как формулу массива, но не должна вводиться как формула массива. Другими словами она может быть введена клавишей ↵ Enter , вместо комбинации клавиш для ввода формул массива Ctrl + ⇧ Shift + ↵ Enter (или со СНЯТЫМ флажком Массив , если используется Мастер формул).

Функция SUMPRODUCT может также использоваться для суммирования ячеек, которые удовлетворяют заданному условию — см. «Условный подсчёт и суммирование» и пример на рис. 1.

  • возвращается A1*F1 + B1*G1 + A2*F2 + B2*G2.

  • когда формула =SUMPRODUCT(ABS(A1:A6)) введена как «обычная», а не как формула массива, возвращает сумму абсолютных значений в ячейках A1:A6. Функция SUMPRODUCT заставляет ABS (A1:A6) вычисляться как формула массива.

  • формула =SUMPRODUCT(A1:A6=“красный”; B1_B6=“большой”; C1:C6) возвращает сумму ячеек в диапазона C1:C6, соответствующие элементы для которых в столбце A имеют значение «красный», а в столбце B«большой».
Читайте также:  Как в эксель убрать пробелы между цифрами

Источник: wiki.harlamenkov.ru

Sumproduct в русском excel

Функция СУММПРОИЗВ – секретное оружие Excel

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

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

Задача – сформировать по ней отчет, из которого было бы видно суммарную выручку по каждому наименованию и заказчику. Посмотрите как красиво эта задача решается с помощью одной формулы(!) :

Т.е. все что надо сделать – это ввести названия товаров в ячейках E3:E8 и названия заказчиков в ячейки F2:H2, а потом ввести в ячейку F3 формулу с нашей функцией:

=СУММПРОИЗВ( ($A$2:$A$20=$E3) * ($B$2:$B$20=F$2) * ($C$2:$C$20) )

Говоря простым языком, эта функция суммирует значения стоимости из диапазона $C$2:$C$20, если значения диапазона $A$2:$A$20 (наименования) равны $E3 (грейпфрут), а значения диапазона $B$2:$B$20 (заказчики) равны F$2 (Ланит). Все, что осталось сделать – это скопировать эту формулу на все ячейки отчета (F2:H8).

Другими словами синтаксис этой функции можно представить так:

=СУММПРОИЗВ( (условие1) * (условие2) * (что_суммировать) )

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

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

Проблема в том, что если файл Бюджет.xls в данный момент не открыт, то формула при пересчете листа выдает ошибку #ЗНАЧ. Но, если добавить в формулу двойное бинарное отрицание (два знака минус подряд) перед аргументами, и немного ее изменить, то она будет работать даже при закрытом файле Бюджет.xls . Формула должна иметь следующий вид:

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

НОВОСТИ ФОРУМА
Рыцари теории эфира
01.10.2019 – 05:20: ВОСПИТАНИЕ, ПРОСВЕЩЕНИЕ, ОБРАЗОВАНИЕ – Upbringing, Inlightening, Education ->
[center][Youtube]69vJGqDENq4[/Youtube][/center][center]14:36[/center]Osievskii Global News
29 сент. Отправлено 05:20, 01.10.2019 г.’ target=_top>Просвещение от Вячеслава Осиевского – Карим_Хайдаров.
30.09.2019 – 12:51: ВОСПИТАНИЕ, ПРОСВЕЩЕНИЕ, ОБРАЗОВАНИЕ – Upbringing, Inlightening, Education ->
[center][Ok]376309070[/Ok][/center][center]11:03[/center] Отправлено 12:51, 30.09.2019 г.’ target=_top>Просвещение от Дэйвида Дюка – Карим_Хайдаров.
30.09.2019 – 11:53: ВОСПИТАНИЕ, ПРОСВЕЩЕНИЕ, ОБРАЗОВАНИЕ – Upbringing, Inlightening, Education ->
[center][Youtube]VVQv1EzDTtY[/Youtube][/center][center]10:43[/center]

интервью Раввина Борода https://cursorinfo.co.il/all-news/rav.
мой телеграмм https://t.me/peshekhonovandrei
мой твиттер https://twitter.com/Andrey54708595
мой инстаграм https://www.instagram.com/andreipeshekhonow/

[b]Мой комментарий:
Андрей спрашивает: Краснодарская синагога – это что, военный объект?
– Да, военный, потому что имеет разрешение от Росатома на манипуляции с радиоактивными веществами, а также иными веществами, опасными в отношении массового поражения. Именно это было выявлено группой краснодарцев во главе с Мариной Мелиховой.

[center][Youtube]CLegyQkMkyw[/Youtube][/center]
[center]10:22 [/center]

Доминико Риккарди: Россию ждёт страшное будущее (хотелки ЦРУ):
https://tainy.net/22686-predskazaniya-dominika-rikardi-o-budushhem-rossii-sdelannye-v-2000-godu.html

Завещание Алена Даллеса / Разработка ЦРУ (запрещено к ознакомлению Роскомнадзором = Жид-над-рус-надзором)
http://av-inf.blogspot.com/2013/12/dalles.html

[center][b]Сон разума народа России [/center] [center][Youtube]CLegyQkMkyw[/Youtube][/center]
[center]10:22 [/center]

Доминико Риккарди: Россию ждёт страшное будущее (хотелки ЦРУ):
https://tainy.net/22686-predskazaniya-dominika-rikardi-o-budushhem-rossii-sdelannye-v-2000-godu.html

Завещание Алена Даллеса / Разработка ЦРУ (запрещено к ознакомлению Роскомнадзором = Жид-над-рус-надзором)
http://av-inf.blogspot.com/2013/12/dalles.html

[center][b]Сон разума народа России [/center]

Источник: bourabai.kz