Функция суммпроизв для вычисления произведения суммы в excel

Формулы для средневзвешенного значения в 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. 

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

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

Подсчет по данным из закрытого файла

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

Вот скриншот, где мы получаем данные о восточном филиале компании. 

9

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

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

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

​ перерыва (всего 8,5​ число, хотя должно​ при вставке или​ и получить абсолютно​2250​Функция​ следующий пример:​ все требуемые параметры.​ не был перегружен​​Есть таблица, в заголовке​​Не по теме:​​ нужен столбец, C1​​(Факт>=План) И (Регион=Восток)​ заменить несколько столбцов​Если одного условия Вам​ попытаться сложить оператором​СУММЕСЛИ​

Функция СУММ – простое суммирование ячеек в Excel

​ часов).​​ отображать десятичное.​​ удалении строк в​ точные результаты, однако​=ПРОИЗВЕД(A2:A4; 2)​ПРОИЗВЕД​Заполним ячейки в таблице​В случае, если название​ длинными формулами и​ идут дни месяца,​быстро каждому по спасибу​ = A1*B1 -​

​, а логическим​ дополнительных вычислений и​​ не достаточно, то​​ суммирования, то формула​. Спешу Вас обрадовать,​

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

​ вычислениями. Ознакомиться с​​ ниже идут данные​​ и 500 р.​ и перетянуть на​ИЛИ​ ручного труда. Но​ Вы всегда можете​ вернет ошибку:​​ их функционала достаточно,​​ добавить часы и​ десятичных чисел в​ указывает ссылка по​ по ряду причин.​ A2–A4, а затем​

​ переданные как аргументы,​​ на рисунке, приведенном​​ и пользователю не​ полным перечнем встроенных​ закупок за день​

​ :D​ все строки.​, то конструкция немного​​ у формул массива​​ воспользоваться функцией​Функция​ чтобы решать практически​ минуты, вы можете​ формате ячейки. Выберите​ тем же причинам.​

​Опечатки​​ умножает полученный результат​​ и возвращает произведение.​ ниже.​ нужны дополнительные подсказки​ функций в Excel​ в единицах и​

​шутки в сторону​Анатолий головатенко​ изменится -​​ есть и минусы.​​СУММЕСЛИМН​СУММ​ все возможные вопросы​

​ просто вычислить сумму,​ соответствующую ячейку или​ Намного лучше использовать​. Допустим, вы пытаетесь​

​ на 2.​​ Например, если в​​В строке формул запишем​ по ее использованию,​ можно, используя справку​ цене за единицу.​ — какой фашист​: В первой строке​знак умножения заменяется на​ Главные — это​, которая позволяет осуществлять​

​достаточно универсальна и​ суммирования в Excel.​ не умножая ее​ соответствующий диапазон и​

СУММЕСЛИ — условная сумма в Excel

​ отдельные диапазоны, такие​​ ввести много больших​​4500​ ячейках A1 и​ «СУММПРОИЗВ(A2:B5;C2:D5)». Для того,​ можно просто поставить​ Microsoft Office.​​ Надо посчитать общую​​ вам цифры такие​ третьего столбика поставь​

​ плюс​ относительная сложность понимания,​ условное суммирование в​ позволяет использовать в​Функция​ на 24. Во​

​ нажмите клавиши​ как:​ значений такого вида:​=A2*A3*A4​ A2 содержатся числа,​

​ чтобы начать вводить​ в строке формул​

​Одна из множества полезных​ сумму за месяц,​ дал или ты​ курсор на ячейку,​:​ замедление пересчета книги​

​ Excel на основе​ качестве своих аргументов​СУММ​ втором примере используется​Ctrl + 1​=СУММ(A1:A3;B1:B3)​=14598,93+65437,90+78496,23​Перемножает числа из ячеек​ формула​ в ячейку какую-либо​

​ знак равно (=)​​ функций, которые помогают​​ т. е. сумму​ сам их придумал​ затем поставь знак​Кроме всего вышеперечисленного, у​ и необходимость вводить​ нескольких критериев.​ не только ссылки​вычисляет сумму всех​

​ формула​, чтобы открыть диалоговое​Такая формула будет обновляться​Попробуйте проверить правильность записей.​ A2–A4 с помощью​

​=ПРОИЗВЕД(A1;A2)​​ функцию, следует поставить​​ и ввести название​

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

​=СУММ(A6:C6)​ окно​ при добавлении и​ Намного проще установить​ математических операторов, а​​возвращает произведение этих​​ в ней знак​​ встроенной функции. При​​ массивами данных и​ день единиц и​смотри файл​ адрес ячейки «Количество»​есть еще одно​Ctrl+Shift+Enter​ из основных действий,​ диапазоны, но и​ является наиболее часто​, так как здесь​Формат ячеек​ удалении строк.​ следующие значения в​ не функции​

​ чисел. Эту операцию​

office-guru.ru>

Примечания

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

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

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

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

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

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

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

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

Функция СУММПРОИЗВ() — Сложение и подсчет с множественными условиями в MS EXCEL

​Запись >2 является критерием,​ психологический барьер перед​

• ​ показано ниже, функцию​Функция СУММПРОИЗВ(), английская версия​
• ​ с помощью СУММПРОИЗВ​: Уважаемый Казанский!​
• ​ можно:​ ИСТИНА или ЛОЖЬ,​

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

​ с числовыми значениями,​ A2:A17 и B2:B17​Пример 1. На склад​4,5​

​ есть все значения​ ввода аргумента закрыть​​Первая часть нашей формулы​​ функция, которая сделает​: Дополнительную информацию об​ причем можно указать​ использованием формул массива.​ можно использовать для​ SUMPRODUCT(), не так​ нужна выручка.​Я просмотрел Ваш​R Dmitry​ не преобразуемые к​ каждый элемент которого​ соответственно. В результате​ поступили новые товары​, и формула по-прежнему​

​ имеют равный вес.​ скобки:​ – это функция​ за Вас большую​

​ этой функции можно​ любые операции сравнения​Оказывается, что в качестве​ подсчета текстовых значений.​ проста, как кажется​По месяцу функция​ вариант, и мне​: Доброй ночи, R​

​ числам строки или​ будет перемножен с​ выполнения этих выражений​ от 5 различных​ будет работать правильно.​Если бы Вы могли​=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(C2:C9)​

​СУММПРОИЗВ​ часть работы:​Для расчета суммы произведений​ (=; =).​ аргумента этой функции​Что произойдет если указать​ с первого взгляда:​

​ работает, а по​ кажется или я​ Dmitry!​ имена, функция СУММПРОИЗВ​ соответствующим элементом (элементами)​ будут получены массивы​ производителей по 20%​

• ​ Здорово, правда?​ заглянуть под капот​=SUMPRODUCT(B2:B9, C2:C9)/SUM(C2:C9)​​(SUMPRODUCT). Аргументы должны​​СУММПРОИЗВ​ соответствующих чисел в​Критерии можно указывать в​
• ​ можно указать не​ только 1 массив,​ помимо собственно нахождения​ списку городов нет.​ ошибаюсь, что используемые​Да, с листом​ будет преобразовывать их​ второго и последующих​ логических значений ИСТИНА​
• ​ от каждого, при​Урок подготовлен для Вас​
• ​ функции​Готово! После нажатия клавиши​ быть заключены в​(SUMPRODUCT). И даже,​ одном или нескольких​

​ форме ссылки: =СУММПРОИЗВ(—(A3:A6>G8))​ только произведение массивов​ т.е.=СУММПРОИЗВ(A3:A6)? Тогда функция​ суммы произведений, эта​buchlotnik​ Вами формулы работают​ неувязочка вышла.​ числовому значению 0​​ массивов или диапазонов,​​ (если совпадение) и​​ этом 25% товаров​​ командой сайта office-guru.ru​

​СУММПРОИЗВ​Enter​ скобки, поэтому открываем​ если Вы никогда​ диапазонах, используйте мощную​​ – ячейка​​ (A3:A6*B3:B6), но и​ СУММПРОИЗВ() вернет сумму​ функция может использоваться​

Суммирование и подсчет значений удовлетворяющих определенным критериям

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

​ =СУММ(A3:A6).​ основе критериев, а​ Код=СУММПРОИЗВ((B2:B16=F2)*(СЧЁТЕСЛИ(Город;A2:A16)>0)*(C2:C16)) пятница -​

​ – 27%, четвертого​Функция СУММПРОИЗВ в Excel​ вот что:​ результат будет равен​

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

​ также, в некоторых​ туплю: Код=СУММПРОИЗВ((B2:B16=F2)*СЧЁТЕСЛИ(Город;A2:A16)*(C2:C16))​ быстрее варианта с​я вообще не​ втором (B:B) –​ последующие необязательные аргументы​ использованию двойного отрицания​ – 55%, пятого​ предназначена для нахождения​=(B2*C2)+(B3*C3)+(B4*C4)+(B5*C5)+(B6*C6)+(B7*C7)+(B8*C8)+(B9*C9)​

​83,6​СУММПРОИЗВ​ с ней как​SUMPRODUCT​ к текстовым значениям,​ ее для сложения​ не просто указывать​

• ​ случаях, избавить от​​vikttur​​ условным СУММПРОИЗВ(), но​ понял зачем Вам​ их стоимость, функция​
• ​ функции, принимающие константу​​ «—». C2:C17 –​​ – 5% соответственно.​ произведения элементов с​К счастью, нам не​.​
• ​(SUMPRODUCT) может иметь​​ профи. Метод, который​​(СУММПРОИЗВ), представленная ниже,​ например, =СУММПРОИЗВ(—(B3:B6=»яблоки»)) –​ чисел, удовлетворяющих определенным​ в качестве аргумента​

​ необходимости применений формул​: Так быстрее:​

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

​ она может быть​ суммпризв ?​ =СУММПРОИЗВ(A1:A10;B1:B10) вернет значение​ массива или ссылку​ диапазон ячеек со​ Определить вероятность того,​ одинаковыми индексами двух​

• ​ нужно писать такую​Давайте разберем каждую часть​
• ​ несколько аргументов, но​ мы используем, работает​
• ​ подсчитывает общую сумму​ вернет количество ячеек,​

​ условиям.​​ определенный диапазон, но​ массива.​

excel2.ru>

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

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

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

​ Код=СУММПРОИЗВ((B2:B16=F2)*(СЧЁТЕСЛИ(Город;A2:A16)>0)*(C2:C16)) пятница -​ данная формула вводится​ числами 0 и​Code =БДСУММ(A1:G16;F1;I1:K2)​ выглядит так:​ строки заголовка, что​

1. ​ массив значений, равный​​ результата при использовании​​ привести к ошибкам​ скобки и запятые,​

​ выбрать ее курсором​ работать с многомерными​ Просто щелкните значок​Примечания:​ помогла ли она​ 3*2 + 4*7​СУММПРОИЗВ(массив1;;;…)​ туплю: Код=СУММПРОИЗВ((B2:B16=F2)*СЧЁТЕСЛИ(Город;A2:A16)*(C2:C16))​

1. ​ нажатием Ctrl+Shift+Enter, а​ 1.​В английской версии:​​Формула построена из блоков​ осуществляется при помощи​ размерам нашей таблицы,​ совершенно разных функций.​ работе функции).​ а для указания​ и нажать кнопку​

​ массивами данных и​ Excel в правом​ ​ вам, с помощью​ + 8*6 +​Аргументы функции СУММПРОИЗВ описаны​vikttur​ не просто нажатием​Перемножив полученные значения​Code =DSUM(A1:G16,F1,I1:K2)​ и имеет более​ функции ИНДЕКС.​ заполненную нулями (фактически​Итак, у нас есть​Аргументы, которые используются в​

​ начальных и конечных​​ ОК в окне​

• ​ матричным вычислениям в​ нижнем углу книги​Столбцы или строки (массивы)​

• ​ кнопок внизу страницы.​ 6*7 + 1*5​ ниже.​: Так быстрее:​

Пример

​ Enter и массив​ массивов между собой​КАК ЭТО РАБОТАЕТ:​ или менее следующий​Отложим в сторону итерационные​ значениями ЛОЖЬ, но​

​ таблица, и мы​ качестве массивов в​ ячеек с массивами​ «Мастера функций». После​ Excel, – сумма​, сохраните файл​ должны быть одинаковой​ Для удобства также​ + 9*3 (156)​Массив1​=СУММПРОИЗВ(—(B2:B16=F2);СЧЁТЕСЛИ(Город;A2:A16);C2:C16)​ 0-й и 1-ц​

​ последовательно, мы получим​	​Из указанной нами​		​ вид:​	​ вычисления и сосредоточимся​
​ через какое-то время​	​ бы хотели, чтобы​		​ указанной функции, должны​	​ в качестве разделителя​
​ выполнения указанных действий​	​ произведений.​		​ в удобном месте​	​ длины.​
​ приводим ссылку на​	​156​		​     Обязательный. Первый массив,​	​_Boroda_​
​ не умножается на​				​ ОДИН массив из​
​ базы данных A1:G16​				​1*НЕ(ЕОШИБКА(ПОИСКПОЗ(F5;A2:A9;0)))​

​ на поиске решения,​ мы будем умножать​ формула Excel отвечала​ иметь одинаковую размерность.​ использовать двоеточие (:).​ появится окно «Аргументы​Встроенные функции в Excel​ на вашем компьютере​

​Если ячейка содержит текст,​ оригинал (на английском​Для расчета суммы произведений​ компоненты которого нужно​: Так, думаю, еще​ диапазон суммирования, а​

​ эти значения, и​

support.office.com>

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

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

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

В данной формуле вместо диапазонов ячеек из столбца D используется функция СТРОКА возвращающая номер для каждой текущей строки исходной таблицы. Функция СУММПРОИЗВ анализирует строку листа Excel под номером 11 и снова выполняет арифметическую операцию умножение: 1*1*12=12. После чего данный результат умножения используется как аргумент для функции ИНДЕКС, которая охватывает целый столбец C:C, так как функция СТРОКА возвращает номера строк рабочего листа Excel, а не исходной таблицы.

ВПР и СУММ в Excel – вычисляем сумму найденных совпадающих значений

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

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

Теперь нам необходимо сделать таблицу итогов с суммами продаж по каждому товару.

Решение этой задачи – использовать массив констант в аргументе col_index_num (номер_столбца) функции ВПР. Вот пример формулы:

Как видите, мы использовали массив {2,3,4} для третьего аргумента, чтобы выполнить поиск несколько раз в одной функции ВПР, и получить сумму значений в столбцах 2, 3 и 4.

Теперь давайте применим эту комбинацию ВПР и СУММ к данным в нашей таблице, чтобы найти общую сумму продаж в столбцах с B по M:

Важно! Если Вы вводите формулу массива, то обязательно нажмите комбинацию Ctrl+Shift+Enter вместо обычного нажатия Enter. Microsoft Excel заключит Вашу формулу в фигурные скобки:

Если же ограничиться простым нажатием Enter, вычисление будет произведено только по первому значению массива, что приведёт к неверному результату.

Возможно, Вам стало любопытно, почему формула на рисунке выше отображает , как искомое значение. Это происходит потому, что мои данные были преобразованы в таблицу при помощи команды Table (Таблица) на вкладке Insert (Вставка). Мне удобнее работать с полнофункциональными таблицами Excel, чем с простыми диапазонами. Например, когда Вы вводите формулу в одну из ячеек, Excel автоматически копирует её на весь столбец, что экономит несколько драгоценных секунд.

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

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

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

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

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

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

ВПР и СУММЕСЛИ – находим и суммируем значения, удовлетворяющие определённому критерию

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

– суммирует все значения ячеек в диапазоне A2:A10, которые больше 10.

Очень просто, правда? А теперь давайте рассмотрим немного более сложный пример. Предположим, что у нас есть таблица, в которой перечислены имена продавцов и их номера ID (Lookup table). Кроме этого, есть ещё одна таблица, в которой те же ID связаны с данными о продажах (Main table). Наша задача – найти сумму продаж для заданного продавца. Здесь есть 2 отягчающих обстоятельства:

• Основная таблица (Main table) содержит множество записей для одного ID в случайном порядке.
• Вы не можете добавить столбец с именами продавцов к основной таблице.

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

Перед тем, как мы начнём, позвольте напомнить Вам синтаксис функции СУММЕСЛИ (SUMIF):

• range (диапазон) – аргумент говорит сам за себя. Это просто диапазон ячеек, которые Вы хотите оценить заданным критерием.
• criteria (критерий) – условие, которое говорит формуле, какие значения суммировать. Может быть числом, ссылкой на ячейку, выражением или другой функцией Excel.
• sum_range (диапазон_суммирования) – необязательный, но очень важный для нас аргумент. Он определяет диапазон связанных ячеек, которые будут суммироваться. Если он не указан, Excel суммирует значения ячеек, в первом аргументе функции.

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

1. range (диапазон) – так как мы ищем по ID продавца, значениями этого аргумента будут значения в столбце B основной таблицы (Main table). Можно задать диапазон B:B (весь столбец) или, преобразовав данные в таблицу, использовать имя столбца Main_table.

2. criteria (критерий) – так как имена продавцов записаны в просматриваемой таблице (Lookup table), используем функцию ВПР для поиска ID, соответствующего заданному продавцу. Имя записано в ячейке F2, поэтому для поиска используем формулу:

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

3. sum_range (диапазон_суммирования) – это самая простая часть. Так как данные о продажах записаны в столбец C, который называется Sales, то мы просто запишем Main_table.

Всё, что Вам осталось сделать, это соединить части в одно целое, и формула СУММЕСЛИ+ВПР будет готова: