Как вычислить сумму произведений в excel с помощью встроенной функции?

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

​ Точно так же​ а ЛОЖЬ всегда​: Как суммировать данные​ Excel, моментально на​ в котором находится​​ ячейки таблицы с​​ выглядеть следующим образом:​

1. ​Использование некорректного синтаксиса (лишний​​ произведений имеет следующий​​ набрать название функции​ можно, используя справку​ + 9*3 (156).​

​=СУММПРОИЗВ(A2:B4; D2:E4)​​в Microsoft Excel.​​: попробуйте так:​​ как и Способ​ равно 0 мы​ по нескольким критериям??​ ум приходит пара​ искомое выражение). Остальное​

1. ​ искомым словом. Для​Небольшое упражнение, которое показывает,​ пробел между знаками​​ синтаксис – «СУММПРОИЗВ(массив1,массив2,массив3,…)».​​ – «СУММПРОИЗВ».​
2. ​ Microsoft Office.​​Для полный качества также​​ большом количестве строк​ вам полезна. Просим​Перемножает все компоненты двух​

​Перемножает соответствующие элементы заданных​=СУММПРОИЗВ((B2:B16=F2)*(1-ЕОШИБКА(ПОИСКПОЗ(A2:A16;Город;0)))*(C2:C16))​​ №2. Есть только​​ продолжаем работать с​

​РЕШЕНИЕ​

office-guru.ru>

Нахождение суммы произведений элементов массивов

Теперь давайте более подробно раскроем, как с помощью функции можно находить сумму произведений элементов массивов. 

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

Эта функция выполняет поэлементные операции со всеми диапазонами (пусть их будет даже 200), которые входят в состав массива. 

Обязательное требование к массивам – наличие одинаковых размерностей. Если массивы будут содержать разное количество элементов, будет возвращена ошибка #ЗНАЧ!.

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

Как составить формулу сумма произведений в excel

​ есть были выполнены​​Если предположить, что​​ Наконец-то все автоматически​ поле продаж фруктов​будет работать только​​ одним (или несколькими)​​ Главные — это​ прост:​ Если аргумент является массивом​ использование функции​

​ в которой написана​​ диапазоне «менеджер» искать​​Так работает эта​ заменить использование формул​ (=; =).​ больших 2 в​ были попарно перемножены,​​ следующие арифметические действия:​ ваши числа находятся​ работает!​ будет равно 0​ до тех пор,​ множителями. И если​ относительная сложность понимания,​=СУММПРОИЗВ(Массив1; Массив2; . )​ или ссылкой, то​ПРОИЗВЕД​

Формулы для средневзвешенного значения в Excel

В Microsoft Excel взвешенное среднее рассчитывается с использованием того же подхода, но с гораздо меньшими усилиями, поскольку функции Excel выполнят большую часть работы за вас.

Пример 1. Функция СУММ.

Если у вас есть базовые знания о ней , приведенная ниже формула вряд ли потребует какого-либо объяснения:

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

Посмотрите на рисунок чуть ниже: формула возвращает точно такой же результат, что и вычисления, которые мы делали минуту назад. Обратите внимание на разницу между нормальным средним, возвращаемым при помощи СРЗНАЧ в C8, и средневзвешенным (C9)

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

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

Пример 2. Функция СУММПРОИЗВ

Она идеально подходит для нашей задачи, так как предназначена для сложения произведений чисел. А это именно то, что нам нужно. 

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

Предполагая, что величины для усреднения находятся в ячейках B2: B6, а показатели значимости — в ячейках C2: C6, наша формула будет такой:

Итак, формула умножает 1- е число в массиве 1 на 1- е  в массиве 2 (в данном примере 91 * 0,1), а затем перемножает 2- е число в массиве 1 на 2- е  в массиве 2 (85 * 0,15). в этом примере) и так далее. Когда все умножения выполнены, Эксель складывает произведения. Затем делим полученное на итог весов.

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

В нашем случае сложение весов дает 100%. То есть, это просто процент от итога. В таком случае верный результат может быть получен также следующими способами:

Это формула массива, не забудьте, что вводить ее нужно при помощи комбинации клавиш ++.

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

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

Видите, в этом случае мы обошлись без процентов.

Пример 3. Средневзвешенная цена.

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

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

Ну, это все о формуле средневзвешенного значения в Excel. 

Рекомендуем также:

Пример суммирования с использованием функции СУММЕСЛИ

Этот пример можно считать классическим. Пусть есть таблица с данными о продажах некоторых товаров.

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

Первым делом выделяем ячейку, где будет подсчитана сумма. Далее вызываем Мастера функций. Это значок fx в строке формул. Далее ищем в списке функцию СУММЕСЛИ и нажимаем на нее. Открывается диалоговое окно, где для решения данной задачи нужно заполнить всего два (первые) поля из трех предложенных.

Поэтому я и назвал такой пример упрощенным. Почему 2 (два) из 3 (трех)? Потому что наш критерий находится в самом диапазоне суммирования.

В поле «Диапазон» указывается та область таблицы Excel, где находятся все исходные значения, из которых нужно что-то отобрать и затем сложить. Задается обычно с помощью мышки.

В поле «Критерий» указывается то условие, по которому формула будет проводить отбор. В нашем случае указываем «>70». Если не поставить кавычки, то они потом сами дорисуются.

Последнее поле «Дапазон_суммирования» не заполняем, так как он уже указан в первом поле.

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

Заполнив в Мастере функций необходимые поля, нажимаем на клавиатуре кнопку «Enter», либо в окошке Мастера «Ок». На месте вводимой функции должно появиться рассчитанное значение. В моем примере получилось 224шт. То есть суммарное значение проданных товаров в количестве более 70 штук составило 224шт. (это видно в нижнем левом углу окна Мастера еще до нажатия «ок»). Вот и все. Это был упрощенный пример, когда критерий и диапазон суммирования находятся в одном месте.

Теперь давайте рассмотрим, пример, когда критерий не совпадает с диапазоном суммирования. Такая ситуация встречается гораздо чаще. Рассмотрим те же условные данные. Пусть нам нужно узнать сумму не больше или меньше какого-то значения, а сумму конкретной группы товаров, допустим, группы Г.

Для этого снова выделяем ячейку с будущим подсчетом суммы и вызываем Мастер функций. В первом окошке указываем диапазон, где содержится критерий, в нашем случае это столбец с названиями групп товаров. Далее сам критерий прописываем либо вручную, оставив в соответствующем поле запись «группа Г», либо просто указываем мышкой ячейку с нужным критерием. Последнее окошко – это диапазон, где находятся суммируемые данные.

Результатом будет сумма проданных товаров из группы Г – 153шт.

Итак, мы посмотрели, как рассчитать одну сумму по одному конкретному критерию. Однако чаще возникает задача, когда требуется рассчитать несколько сумм для нескольких критериев. Нет ничего проще! Например, нужно узнать суммы проданных товаров по каждой группе. То бишь интересует 4 (четыре) значения по 4-м (четырем) группам (А, Б, В и Г). Для этого обычно делается список групп в виде отдельной таблички. Понятное дело, что названия групп должны в точности совпадать с названиями групп в исходной таблице. Сразу добавим итоговую строчку, где сумма пока равна нулю.

Затем прописывается формула для первой группы и протягивается на все остальные

Здесь только нужно обратить внимание на относительность ссылок. Диапазон с критериями и диапазон суммирования должны быть абсолютным ссылками, чтобы при протягивании формулы они не «поехали вниз», а сам критерий, во-первых нужно указать мышкой (а не прописать вручную), во-вторых, должен быть относительной ссылкой, так как каждая сумма имеет свой критерий суммирования

Заполненные поля Мастера функций при подобном расчете будут выглядеть примерно так.

Как видно, для первой группы А сумма проданных товаров составила 161шт (нижний левый угол рисунка). Теперь нажимаем энтер и протягиваем формулу вниз.

Все суммы рассчитались, а их общий итог равен 535, что совпадает с итогом в исходных данных. Значит, все значения просуммировались, ничего не пропустили.

Часто задаваемые вопросы и проблемы.

Я надеюсь, что эти примеры помогли вам почувствовать функцию Excel СЧЕТЕСЛИ. Если вы попробовали какую-либо из приведенных выше формул в своих данных и не смогли заставить их работать или у вас возникла проблема, взгляните на следующие 5 наиболее распространенных проблем. Есть большая вероятность, что вы найдете там ответ или же полезный совет.

1. Возможен ли подсчет в несмежном диапазоне клеток?

Вопрос: Как я могу использовать СЧЕТЕСЛИ для несмежного диапазона или ячеек?

Ответ: Она не работает с несмежными диапазонами, синтаксис не позволяет указывать несколько отдельных ячеек в качестве первого параметра. Вместо этого вы можете использовать комбинацию нескольких функций СЧЕТЕСЛИ:

Неправильно: =СЧЕТЕСЛИ(A2;B3;C4;»>0″)

Правильно: = СЧЕТЕСЛИ (A2;»>0″) + СЧЕТЕСЛИ (B3;»>0″) + СЧЕТЕСЛИ (C4;»>0″)

Альтернативный способ — использовать функцию ДВССЫЛ (INDIRECT) для создания массива из несмежных клеток. Например, оба приведенных ниже варианта дают одинаковый результат, который вы видите на картинке:

=СУММ(СЧЁТЕСЛИ(ДВССЫЛ({«B2:B11″;»D2:D11″});»=0»))

Или же

=СЧЕТЕСЛИ($B2:$B11;0) + СЧЕТЕСЛИ($D2:$D11;0)

1. Амперсанд и кавычки в формулах СЧЕТЕСЛИ

Вопрос: когда мне нужно использовать амперсанд?

Ответ: Это, пожалуй, самая сложная часть функции СЧЕТЕСЛИ, что лично меня тоже смущает. Хотя, если вы подумаете об этом, вы увидите — амперсанд и кавычки необходимы для построения текстовой строки для аргумента.

Итак, вы можете придерживаться этих правил:

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

= СЧЕТЕСЛИ(A1:A10;10) или = СЧЕТЕСЛИ(A1:A10;C1)

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

= СЧЕТЕСЛИ(A2:A10;»яблоко») или = СЧЕТЕСЛИ(A2:A10;»*») или = СЧЕТЕСЛИ(A2:A10;»>5″)

Если ваши критерии — это выражение со ссылкой или же какая-то другая функция Excel, вы должны использовать кавычки («») для начала текстовой строки и амперсанд (&) для конкатенации (объединения) и завершения строки. Например:

= СЧЕТЕСЛИ(A2:A10;»>»&D2) или = СЧЕТЕСЛИ(A2:A10;»<=»&СЕГОДНЯ())

Если вы сомневаетесь, нужен ли амперсанд или нет, попробуйте оба способа. В большинстве случаев амперсанд работает просто отлично.

Например, = СЧЕТЕСЛИ(C2: C8;»<=5″) и = СЧЕТЕСЛИ(C2: C8;»<=»&5) работают одинаково хорошо.

1. Как сосчитать ячейки по цвету?

Вопрос: Как подсчитать клетки по цвету заливки или шрифта, а не по значениям?

Ответ: К сожалению, синтаксис функции не позволяет использовать форматы в качестве условия. Единственный возможный способ суммирования ячеек на основе их цвета — использование макроса или, точнее, пользовательской функции Excel VBA.

1. Ошибка #ИМЯ?

Проблема: все время получаю ошибку #ИМЯ? Как я могу это исправить?

Ответ: Скорее всего, вы указали неверный диапазон. Пожалуйста, проверьте пункт 1 выше.

1. Формула не работает

Проблема: моя формула не работает! Что я сделал не так?

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

Будьте очень осторожны с использованием пробелов. При создании одной из формул для этой статьи я был уже готов рвать волосы, потому что правильная конструкция (я точно знал, что это правильно!) не срабатывала. Как оказалось, проблема была на самом виду… Например, посмотрите на это: =СЧЁТЕСЛИ(A4:A13;» Лимонад»). На первый взгляд, нет ничего плохого, кроме дополнительного пробела после открывающей кавычки. Программа отлично проглотит всё без сообщения об ошибке, предупреждения или каких-либо других указаний. Но если вы действительно хотите посчитать товары, содержащие слово «Лимонад» и начальный пробел, то будете очень разочарованы….

Если вы используете функцию с несколькими критериями, разделите формулу на несколько частей и проверьте каждую из них отдельно.

И это все на сегодня. В следующей статье мы рассмотрим несколько способов подсчитывания ячеек в Excel с несколькими условиями.

Ещё примеры расчета суммы:

Функция СУММПРОИЗВ в VBA — помогите поправить

​R Dmitry​​ были переданы ссылки​ проверки разряда каждого​​ поставщиках, видах товаров​ продавцов, C1:C100 –​ просто выполнит деление​​ него. Например,​/​ правильно и не​(AVERAGE). Но что​В ряде случаев (когда​ например, применить операцию​​ ввода функции в​ массивов.​ и думает долго»)​ выбора только необходимых​ найти более шустрый​​: Cells(1, 3) =​ на 2 и​ сотрудника. Умножение на​ и суммарной стоимости.​​ диапазон ячеек с​

​ на большее значение,​​Задание 2​(прямой слеш), а​ зависеть от суммы,​ делать, если некоторые​​ нужно подсчитать или​

​ двойного отрицания (—),​​ ячейку вместо​Аргументы, которые являются массивами,​​buchlotnik​

​ значений. Заранее спасибо​​ способ расчёта.​ WorksheetFunction.SumProduct(Range(«a1:a3»), Range(«b1:b3»))​​ более диапазона ячеек,​ единицу выполняется для​​ Определить общую сумму​ числовыми значениями стоимости​ вновь приводя к​​посчитано 5 раз,​

​ далее записываем функцию​​ в которую складываются​ значения имеют больший​

​ сложить значения, удовлетворяющие​​ что позволит привести​ENTER​​ должны иметь одинаковые​: Виктор, а вот​​DV​Начал свои поиски​это пример​​ эти диапазоны должны​​ преобразования логических данных​ закупок хлеба от​ проданных товаров. Символы​​ правильному ответу. Или​ а​СУММ​​ веса.​ вес, чем другие?​​ определенным критериям) можно​ массив в числовую​

​нажать​​ размерности (в нашем​ так не быстрее​: H8:​ с WorksheetFunction.SumProduct(), предположив,​ПАХОМ​ содержать одинаковое количество​ к числам.​​ первого поставщика без​​ «—» — двойное​ же мы можем​

​Итоговый экзамен​​(SUM):​​Теперь, когда наша таблица​ Например, на многих​ заменить использование формул​ форму {1:1:1:0}.​​CTRL+SHIFT+ENTER​ случае это массивы​ будет?​=СУММПРОИЗВ((C$23:C$34=G8)*D$23:D$34;(C$8:C$19=G8)*D$8:D$19)+СУММПРОИЗВ((C$8:C$19=G8)*E$8:E$19;(C$23:C$34=G8)*E$23:E$34)​ что функция прописанная​: Доброй ночи, R​​ ячеек. При несоблюдении​В результате вычислений получим​ использования функции СУММЕСЛИМН.​ отрицание, используемое для​ сделать веса намного​​– 45 раз.​=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(​ готова, мы добавляем​ курсах тесты имеют​ массива функцией СУММПРОИЗВ(),​Итак, задача подсчета значений​: =СУММ(A3:A6/B3:B6)​ по 4 элемента).​=-СУММПРОИЗВ(-(B2:B16=F2);СЧЁТЕСЛИ(Город;A2:A16);C2:C16)​Grand68​​ в VBA будет​

​ Dmitry!​​ указанных условий функция​​ следующее число не​Вид таблицы данных:​ прямого преобразования логических​ меньше, например, указать​ Вот почему​=SUMPRODUCT(B2:B9, C2:C9)/SUM(​​ формулу в ячейку​

​ больший вес, чем​​ например:​ больше 2 решается​Прелесть функции СУММПРОИЗВ() в​ В противном случае​vikttur​: Огромное спасибо -​ быстрее чем в​​Да, с листом​​ СУМППРОИЗВ вернет код​ выполнивших норму сотрудников:​Для расчета используем формулу:​ ИСТИНА и ЛОЖЬ​ такие значения как​Итоговый экзамен​Для функции​B10​ задания. Для таких​=СУММПРОИЗВ(—ЕПУСТО(D2:D23)) подсчет пустых ячеек​

​ следующим образом: =СУММПРОИЗВ(—(A3:A6>2))​​ том, что после​​ функция СУММПРОИЗВ() возвращает​: Вряд ли. Двойное​ то что нужно!!!​​ книге Excel. Но​ неувязочка вышла.​ ошибки #ЗНАЧ!.​​Функция имеет следующий синтаксис:​

planetaexcel.ru>

Сравнение в функции СУММПРОИЗВ

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

Аргументы в функции используем такие же, но к ним добавляем оператор сравнения. То бишь, в нашем случае – D:D<=1000. В результате вычислений была получена цифра 1000.

5

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

6

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

Примеры использования функции СУММПРОИЗВ в Excel

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

Например, таблица состоит из трех полей данных с названиями «Товар», «Продавец», «Сумма покупки». Чтобы определить общую средств, полученных на продаже товара товар_1 продавцом Продавец_1 можно использовать следующую формулу массива: =СУММПРОИЗВ(—(A1:A100=”товар_1”);—(B1:B100=”Продавец_1”);C1:C100), где A1:A100 – диапазон ячеек с названием товаров, B1:B100 – диапазон ячеек, в которых хранятся фамилии продавцов, C1:C100 – диапазон ячеек с числовыми значениями стоимости проданных товаров. Символы «—» — двойное отрицание, используемое для прямого преобразования логических ИСТИНА и ЛОЖЬ к числовым 1 и 0 соответственно. Подробнее эта формула будет рассмотрена в одном из примеров.

Расчет вероятности используя вычисление произведение суммы в Excel

Пример 1. На склад поступили новые товары от 5 различных производителей по 20% от каждого, при этом 25% товаров первого поставщика – высшего качества, второго – 18%, третьего – 27%, четвертого – 55%, пятого – 5% соответственно. Определить вероятность того, что случайно выбранный товар окажется высшего качества.

Вид таблицы данных:

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

Описание аргументов:

• B2:B6 – первый диапазон ячеек с вероятностями выбора товаров 1-го, 2-го и т. д. производителей, значения которых будут умножены на соответствующие значения из второго диапазона;
• C2:C6 – второй диапазон ячеек с вероятностями выбора товара высшего сорта среди товаров 1-го, 2-го и т. д. производителей соответственно.

В результате вычислений получаем следующее значение вероятности:

Функция СУММПРОИЗВ – подробное описание

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

Сами способы следующие:

1. Определение суммы произведений массива.
2. Проверка значения на предмет соответствия определенным критериям, получение их произведений и суммирования.
3. Использование в качестве замены формулы массива.

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

Синтаксис функции СУММПРОИЗВ

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

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

Давайте приведем наиболее простой для понимания пример применения этой функции. А потом будем постепенно усложнять задачу, демонстрируя все более новые особенности ее использования. 

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

1

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

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

2

Функция СУММПРОИЗВ с условием

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

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

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

Чтобы выполнить эту задачу с помощью этой функции, необходимо ее использовать и записать два условия в самом начале. Каждое из условий заключается в скобках. Поскольку в нашем случае должно быть соответствие сразу двум критериям, то мы использовали символ звездочки (*). Синтаксис виден на этом скриншоте. Вам достаточно просто подставить соответствующие вашему случаю значения в необходимые места. И дело сделано!

3

Давайте рассмотрим аргументы более подробно:

1. Первое условие записывается в первых скобках.
2. Второе условие записывается во вторых скобках.
3. Третий аргумент – это массив, сумму из которого необходимо извлечь. 

Итоговый результат – 3700 рублей. Если распространить эту формулу на остальные строки и заменить условия в каждой из них, можно получить значения, характерные для другого отчетного периода или иной статьи расходов. К сожалению, лучше автоматизировать этот процесс не получится. Придется некоторые показатели править вручную. Но такова жизнь. Правда, если научиться писать макросы, можно добиться абсолютно любых уровней автоматизации при работе с электронными таблицами. 

4

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

Добрый день!

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

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

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

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

• ваш_массив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 =» (больше и равно) и

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

Функция СУММПРОИЗВ в Excel с примерами ее использования

​ экрана внизу:​start_num​ CODE(MID(B2,3,1)) & CODE(MID(B2,4,1))​ сожалению, имеют несколько​7​ элементов). Если в​ от первого поставщика:​ ИСТИНА и ЛОЖЬ​

​ в 1000 быстрее​ удалила):​ же аргументами, но​ с указанными длинами​D2:D13;E2:E10​ которое превращает относительную​

Синтаксис СУММПРОИЗВ

​ связке с функцией​ на получившуюся в​ поиск с учётом​Важно!​(начальная_позиция) – позиция​ & CODE(MID(B2,5,1)) &​ существенных ограничений. Далее​8​ качестве аргументов СУММПРОИЗВ​Пример 3

В цехе​ к числовым 1​ этого кода…​1. с базой​ дополнительно проставляем оператор​ и ширинами прямоугольников.​)​

Простейший пример использования функции

​ позицию ячейки, возвращаемую​ПРОСМОТР​ итоге сумму.​ регистра, но возвратит​Для того, чтобы​ первого из тех​ CODE(MID(B2,6,1)) & CODE(MID(B2,7,1))​ мы пристально рассмотрим​6​ были переданы ссылки​ по производству деталей​ и 0 соответственно.​Померил — где-то​ данных​ сравнения. В данном​ Нам нужно сосчитать​

​Эта формула вызовет ошибку,​ функцией​, и даёт такой​К сожалению, функция​ только числовые значения.​ функция​ символов, которые нужно​ & CODE(MID(B2,8,1)) &​ чуть более сложную​6​ на 2 и​

СУММПРОИЗВ с условием

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

​ПРОСМОТР​ извлечь. Вы вводите​ IFERROR(CODE(MID(B2,9,1)),»»)​ формулу​7​ более диапазона ячеек,​ определенного разряда, для​ будет рассмотрена в​

​ не важно…​ с помощью формулы​ как D:D​ прямоугольников. Если не​ указанные в функции,​, в реальный адрес​Заметьте, что формула​не может работать​ Вам не подходит,​работала правильно, значения​1​=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) &​

• ​ИНДЕКС+ПОИСКПОЗ​
• ​1​
• ​ эти диапазоны должны​ каждого из которых​ одном из примеров.​

​200?’200px’:»+(this.scrollHeight+5)+’px’);»>Sub tt()​ (там чёрти что,​И действительно, это та​ пользоваться данной функцией,​ содержат разное количество​ ячейки. Например, в​ИНДЕКС+ПОИСКПОЗ​ с текстовыми значениями​ то можете сразу​ в столбце поиска​

Сравнение в СУММПРОИЗВ

​в первой функции​ КОДСИМВ(ПСТР(B2;3;1)) & КОДСИМВ(ПСТР(B2;4;1))​(INDEX+MATCH), которая работает​9​ содержать одинаковое количество​ установлена норма выработки​​Dim a(), i&,​ т.к. были ошибки​ самая тысяча, которая​ придется произвести промежуточные​ строк (13 в​ нашей функции​заключена в фигурные​ и датами, так​ переходить к связке​ должны быть упорядочены​ПСТР​ & КОДСИМВ(ПСТР(B2;5;1)) &​

​ безукоризненно в любых​5​ ячеек. При несоблюдении​ за месяц. Определить​Пример 1. На склад​ ii&, t$​ из-за форматов ячеек,​ была потрачена в​ действия и сосчитать​ первом диапазоне, но​

​ПОИСКПОЗ​ скобки – это​

​ как их нельзя​ИНДЕКС+ПОИСКПОЗ​ по возрастанию, то​,​ КОДСИМВ(ПСТР(B2;6;1)) & КОДСИМВ(ПСТР(B2;7;1))​ ситуациях и с​3​ указанных условий функция​

exceltable.com>