Для поиска значений по нескольким условиям эффективно использовать функцию СУММПРОИЗВ. Данная функция поддерживается во всех версиях Excel. А если использовать функцию СУММПРОИЗВ в формулах вместе с другими функциями – это позволить выполнять поиск не только для чисел, но и для текста.
Как работает функция СУММПРОИЗВ при поиске по нескольким условиям
Ниже на рисунке представлена таблица отделов и их бюджетов. Допустим нам необходимо создать формулу, которая после выбора пользователем двух условий: региона и отдела, будет возвращать соответственную сумму бюджета. В данной формуле нельзя просто использовать функцию ВПР так как она ищет только одно значение по одному условию. В этом примере необходимо найти сразу 2 искомых значения, так как названия регионов и отделов многократно повторяются в исходной таблице.
Чтобы получить значение из строки, содержащей одновременно 2 искомых значения, можно воспользоваться формулой:
Функция СУММПРОИЗВ приводит к тому, что проверяется значение каждой ячейки в определенных диапазонах A2:A15 и B2:B15 с указанными искомыми значениями в ячейках G4 и G5 – соответственно. В зависимости от результата сравнения ячеек возвращаются таблицы из логических значений ИСТИНА или ЛОЖЬ. В процессе перемножения таблиц в формуле, значение ИСТИНА воспринимается как число 1, а ЛОЖЬ – как число 0. Диапазон в третей паре скобок в первом аргументе функции СУММПРОИЗВ не содержит операторов сравнения, так как это диапазон итоговых значений, одно из которых правильное и будет возвращено функцией.
Если в результате сравнения значение ячеек в столбцах «Регион» или «Отдел» будет возвращено значение ЛОЖЬ, в тоже время расчетная сумма для данной строки будет равна нулю =0. Так как логическое значение ЛОЖЬ заменяется на число 0. Если же вместо этого будут совпадать названия региона и отдела, тогда в результате обоих сравнений будет возвращено число 1. Обе единицы будут перемножены между собой, что также равно 1. А затем эта же единица будет перемножена на числовое значение ячейки в столбце «Бюджет» и таким образом итоговый результат будет возвращен формулой.
В примере, изображенном на рисунке функция СУММПРОИЗВ считывает данные со строки листа №11 и выполняет арифметическую операцию умножения 1*1*697 697=697 697. Это же число суммируется с результатами умножения в других строках, в которых все равны 0. Так как результате умножения хоть-бы на один ноль (возвращаемый значением ЛОЖЬ при сравнении) в итоге получаем все равно 0. То есть как например в последней строке таблицы 1*0*930 133=0.
Поиск текста функцией СУММПРОИЗВ с несколькими условиями в Excel
Функцию СУММПРОИЗВ можно использовать в описаны выше способ только тогда, когда возвращаемое значение является числом. Если должен был быть возвращенный текст тогда все текстовые строки были бы посчитаны как нули, а функция постоянно возвращала бы итоговое значение 0.
Однако можно соединить функцию СУММПРОИЗВ с функциями ИНДЕКС и СТРОКА в одну формулу. Благодаря этому возвращаемый результат будет текстом. Если, например, должно быть возвращено имя и фамилия руководителя отдела можно использовать следующую формулу:
В данной формуле вместо диапазонов ячеек из столбца D используется функция СТРОКА возвращающая номер для каждой текущей строки исходной таблицы. Функция СУММПРОИЗВ анализирует строку листа Excel под номером 11 и снова выполняет арифметическую операцию умножение: 1*1*12=12. После чего данный результат умножения используется как аргумент для функции ИНДЕКС, которая охватывает целый столбец C:C, так как функция СТРОКА возвращает номера строк рабочего листа Excel, а не исходной таблицы.
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще…Меньше
Функция СУММПРОИВ ВОЗВРАЩАЕТ сумму продуктов соответствующих диапазонов или массивов. По умолчанию операция умножения, но возможна с добавлением, вычитанием и делением.
В этом примере мы используем СУММПРОИВ для возврата общего объема продаж для данного элемента и его размера:
SumPRODUCT соответствует всем экземплярам элемента Y/Size M и суммирует их, поэтому в данном примере «21 плюс 41» равен 62.
Синтаксис
Чтобы использовать операцию по умолчанию (умножение):
=СУММПРОИВ(массив1;[массив2];[массив3];…)
Аргументы функции СУММПРОИЗВ описаны ниже.
|
Аргумент |
Описание |
|---|---|
|
массив1 Обязательно |
Первый массив, компоненты которого нужно перемножить, а затем сложить результаты. |
|
[массив2], [массив3],… Необязательно |
От 2 до 255 массивов, компоненты которых нужно перемножить, а затем сложить результаты. |
Выполнение других арифметических операций
Используйте функцию СУММПРОИВ, как обычно, но вместо запятых, разделяющих аргументы массива, используйте нужные арифметические операторы (*, /, +, -). После выполнения всех операций результаты суммются обычным образом.
Примечание: Если вы используете арифметические операторы, заключите аргументы массива в скобки и используйте скобки для группировки аргументов массива для управления порядком арифметических операций.
Примечания
-
Аргументы, которые являются массивами, должны иметь одинаковые размерности. В противном случае функция СУММПРОИЗВ возвращает значение ошибки #ЗНАЧ!. Например, =СУММПРОИВ(C2:C10;D2:D5) возвращает ошибку, так как диапазоны не одного размера.
-
В функции СУММПРОИВТ ненумерические записи массива обрабатывают их так, как если бы они были нулями.
-
Для лучшей производительности не следует использовать суммпроив с полными ссылками на столбцы. Рассмотрим функцию =СУММПРОИВ(A:A;B:B), чтобы умножить 1 048 576 ячеек в столбце A на 1 048 576 ячеек в столбце B перед их добавлением.
Пример 1
Чтобы создать формулу на примере выше, введите =СУММПРОИВ(C2:C5;D2:D5) и нажмитеввод . Каждая ячейка в столбце C умножается на соответствующую ячейку в той же строке столбца D, и результаты сбавляются. Общая сумма продуктов составляет 78,97 долларов США.
Чтобы ввести более длинную формулу, которая дает такой же результат, введите =C2*D2+C3*D3+C4*D4+C5*D5 и нажмите ввод . После нажатия ввод результат будет таким же: 78,97 долларов США. Ячейка C2 умножается на D2, а ее результат добавляется к результату ячейки C3, умноженной на ячейку D3 и так далее.
Пример 2
В следующем примере sumPRODUCT возвращает суммарные чистую сумму продаж по агенту продаж, у которых есть как общие продажи, так и расходы по агенту. В этом случае мы используем таблицу Excel, в которой используются структурированные ссылки вместо стандартных Excel диапазонов. Здесь вы увидите, что диапазоны «Продажи», «Расходы» и «Агент» имеют ссылку по имени.
Формула: =СУММПРОИМ(((Таблица1[Продажи])+(Таблица1[Расходы]))*(Таблица1[Агент]=B8)) и возвращает сумму всех продаж и расходов агента, указанных в ячейке B8.
Пример 3
В этом примере мы хотим получить общую сумму по конкретному товару, проданного в конкретном регионе. В данном случае, сколько вишней было продается в восточном регионе?
Вот формула: =СУММПРОИВ((B2:B9=B12)*(C2:C9=C12)*D2:D9). Сначала оно умножает количество вхождений восточного на количество совпадающих вишней. Наконец, она суммирует значения соответствующих строк в столбце Продажи. Чтобы узнать, Excel вычисляет формулу, выйдите из ячейки формулы, а затем перейдите в > Вычислить формулу >.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
См. также
Выполнение условных вычислений в диапазонах ячеек
Сумм на основе нескольких критериев с помощью СУММЕ ЕСЛИМЕСЯ
Подсчет на основе нескольких критериев с помощью функции СЧЁТЕФС
Среднее значение на основе нескольких критериев с помощью функции ССВЕIFS
Нужна дополнительная помощь?
Skip to content
В статье объясняются основные и расширенные способы использования функции СУММПРОИЗВ в Excel. Вы найдете ряд примеров формул для сравнения массивов, условного суммирования и подсчета ячеек по нескольким условиям, расчета средневзвешенного значения и многое другое.
Когда вы впервые слышите название СУММПРОИЗВ, оно может звучать как какая-то бесполезная формула, выполняющая обычную операцию суммы произведений. Но это определение не показывает даже крошечной части того, на что способна функция Excel СУММПРОИЗВ (на английском – SUMPRODUCT).
На самом деле, СУММПРОИЗВ — это удивительно универсальная функция со множеством применений. Благодаря своей уникальной способности разумно и элегантно обрабатывать массивы, Она чрезвычайно полезна, если не незаменима, когда речь идет о сравнении данных в двух или более диапазонах и подсчёте данных с несколькими условиями. Следующие примеры раскроют всю мощь этой функции.
- Функция СУММПРОИЗВ в Excel — синтаксис и использование
- Логика работы СУММПРОИЗВ в Excel
- Как работает СУММПРОИЗВ с несколькими условиями
- Как работает формула СУММПРОИЗВ с одним условием
- Как пересчитать, суммировать или найти среднее по нескольким условиям
- 1. Формула с логикой И
- 2. Формула с логикой ИЛИ
- 3. Формула с логикой И и ИЛИ
- Формула СУММПРОИЗВ для средневзвешенного значения
- СУММПРОИЗВ как альтернатива формулам массива
- Excel СУММПРОИЗВ — примеры расширенных формул
Функция СУММПРОИЗВ в Excel — синтаксис и использование
Технически функция СУММПРОИЗВ в Excel перемножает числа в указанных диапазонах и возвращает сумму этих произведений.
Синтаксис функции СУММПРОИЗВ прост и понятен:
СУММПРОИЗВ(массив1; [массив2]; [массив3]; …)
Где массив1, массив2 и т. д. — это непрерывные диапазоны ячеек или массивов чисел, элементы которых вы хотите перемножить, а затем сложить.
Минимальное количество массивов равно 1. В этом случае формула СУММПРОИЗВ просто складывает все элементы и возвращает их сумму.
Максимальное количество массивов — 255 в Excel 365-2007 и 30 в более ранних версиях Excel.
Хотя СУММПРОИЗВ работает с массивами, она не требует использования формулы массива Ctrl + Shift + Enter. Вы вводите формулу обычным способом, нажимая клавишу Enter.
Примечания:
- Все массивы в формуле СУММПРОИЗВ должны иметь одинаковое количество строк и столбцов, иначе вы получите ошибку #ЗНАЧ!.
- Если какой-либо аргумент массива содержит нечисловые значения, они будут рассматриваться как нули.
- Если массив является логическим тестом, он дает значения ИСТИНА и ЛОЖЬ. В большинстве случаев вам нужно преобразовать их в 1 и 0 с помощью двойного отрицания (—).
- СУММПРОИЗВ не поддерживает подстановочные знаки * и ?.
Логика работы СУММПРОИЗВ в Excel
Чтобы получить общее представление о том, как работает функция СУММПРОИЗВ в Excel, рассмотрим следующий пример.
Предположим, у вас есть количество товаров в ячейках A2:A4, их цены в ячейках B2:B4, и вы хотите узнать их общую стоимость. Если бы вы делали школьный тест по математике, вы бы умножали количество на цену каждого предмета, а затем складывали промежуточные итоги. В Microsoft Excel вы можете получить сумму произведений с помощью одной формулы:
=СУММПРОИЗВ(B2:B5;C2:C5)
Следующий скриншот показывает ее в действии:
Вот что происходит «под капотом» с точки зрения математики:
- Формула берет 1-е число из 1-го массива (цену) и умножает его на 1-е число из 2-го массива (количество), затем берет 2-е число из 1-го массива и умножает его на 2 е число из 2-го массива, и так далее.
- Когда все элементы перемножаются, формула суммирует произведения и возвращает итоговую сумму.
Другими словами, наша формула СУММПРОИЗВ выполняет следующие математические операции:
=С2*B2 + С3*B3 + С4*B4 + С5*B5
Как использовать СУММПРОИЗВ в Excel с условиями – примеры формул
Только подумайте, сколько времени вы могли бы сэкономить, если бы ваша таблица содержала не 4 строки данных, а сотни или тысячи строк!
Перемножение соответствующих значений двух или более диапазонов, а затем нахождение суммы этих произведений — это самое простое и наиболее очевидное использование СУММПРОИЗВ в Excel, хотя далеко не единственное. Главное достоинство этой функции заключается в том, что она может делать гораздо больше, чем ее обычные заявленные возможности.
Далее в этом руководстве вы найдете несколько примеров формул, демонстрирующих более продвинутые и интересные способы использования, так что, пожалуйста, продолжайте читать.
Сумма произведений с несколькими условиями
Обычно в Microsoft Excel есть несколько способов выполнить одну и ту же задачу. Но когда дело доходит до сравнения двух или более массивов, особенно по нескольким условиям, СУММПРОИЗВ является наиболее эффективным, если не единственным решением. Ну, либо СУММПРОИЗВ, либо формула массива.
Предположим, что у вас есть список товаров в столбце A, запланированные данные о продажах в столбце B и фактические продажи в столбце C. Ваша цель — выяснить, сколько товаров из всего ассортимента продано меньше, чем планировалось. Для этого можно использовать один из следующих вариантов формулы СУММПРОИЗВ:
=СУММПРОИЗВ(—(C2:C10<B2:B10))
или
=СУММПРОИЗВ((C2:C10<B2:B10)*1)
Где C2:C10 — реальные продажи, а B2:B10 — запланированные продажи.
Но что, если у вас несколько условий? Допустим, вы хотите посчитать, сколько раз яблоки продавались хуже, чем планировалось. Решение состоит в том, чтобы добавить еще один критерий в формулу:
=СУММПРОИЗВ((C2:C10<B2:B10)*(A2:A10=»яблоки»))
Или вы можете использовать следующий синтаксис:
=СУММПРОИЗВ(—(C2:C10<B2:B10);—(A2:A10=»яблоки»))
А теперь давайте уделим минутку и разберёмся, что на самом деле делают приведенные выше формулы. Я считаю, что это достойное вложение времени, потому что многие другие формулы СУММПРОИЗВ работают с той же логикой.
Как работает формула СУММПРОИЗВ с одним условием
Для начала давайте разберем более простой случай. Нужно сравнить числа в двух столбцах построчно и сообщить, сколько раз числа колонки C меньше, чем B (то есть, план продаж не выполнен):
=СУММПРОИЗВ(—(C2:C10<B2:B10))
Если вы выделите часть (C2:C10<B2:B10) в строке формул и нажмете F9, чтобы просмотреть вычисленные значения, вы увидите следующую картину:
=СУММПРОИЗВ(—({ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ}))
Здесь мы имеем массив логических значений ИСТИНА и ЛОЖЬ, где ИСТИНА означает, что указанное условие выполнено (т. е. значение в столбце С меньше значения в колонке В в той же строке), а ЛОЖЬ указывает, что условие не выполнено.
Двойное отрицание (—) преобразует логические значения ИСТИНА и ЛОЖЬ в единицы и нули: {0:1:0:0:1:0:1:0:0}.
Другой способ преобразовать логические значения в числа — умножить массив на 1:
=СУММПРОИЗВ((C2:C10<B2:B10)*1)
В любом случае, поскольку в данном случае в формуле СУММПРОИЗВ использован только один массив, она просто складывает единицы в результирующем массиве, и мы получаем желаемое количество. Несложно, не так ли?
Как работает формула СУММПРОИЗВ с несколькими условиями
Когда формула СУММПРОИЗВ Excel содержит два или более массивов, она перемножает соответствующие элементы всех массивов, а затем находит сумму этих произведений.
Как вы помните, мы использовали следующие формулы, чтобы узнать, во сколько раз количество реальных продаж (столбец C) было меньше запланированных продаж (столбец B) яблок (столбец A):
=СУММПРОИЗВ((C2:C10<B2:B10)*(A2:A10=»яблоки»))
или:
=СУММПРОИЗВ(—(C2:C10<B2:B10);—(A2:A10=»яблоки»))
Единственное техническое различие между этими формулами заключается в способе преобразования ИСТИНА и ЛОЖЬ в 1 и 0 с помощью двойного отрицания или операции умножения. В результате получаем два массива единиц и нулей:
=СУММПРОИЗВ({0:1:0:0:1:0:1:0:0};{1:1:0:0:0:0:0:0:0})
Операция умножения, выполняемая СУММПРОИЗВ, объединяет эти числа в один массив. А поскольку умножение на ноль всегда дает 0, а 1 появляется только при выполнении обоих условий, то, следовательно, учитываются только строки, в которых выполнены оба условия:
Как пересчитать, суммировать или найти среднее по нескольким условиям
В Excel 2003 и более ранних версиях, в которых ещё не было функций ЕСЛИ с условиями, одним из наиболее распространенных применений функции СУММПРОИЗВ было условное суммирование или подсчет ячеек с несколькими критериями. Начиная с Excel 2007, Microsoft представила серию функций, специально разработанных для таких задач, — СУММЕСЛИМН, СЧЁТЕСЛИМН и СРЗНАЧЕСЛИ.
Но даже в современных версиях Excel формула СУММПРОИЗВ может быть достойной альтернативой, например, для условного суммирования и подсчета ячеек с логикой ИЛИ. Ниже вы найдете несколько примеров формул, демонстрирующих эту способность в действии.
Формула СУММПРОИЗВ с логикой И
Предположим, у вас есть следующий набор данных, где в столбце A перечислены регионы, в столбце B — товары, а в столбце C — данные о продажах:
Вам нужно получить количество, сумму и среднее значение продаж яблок в северном регионе.
В последних версиях Excel 2016, 2013, 2010 и 2007 задачу можно легко выполнить с помощью формул СУММЕСЛИМН, СЧЁТЕСЛИМН и СРЗНАЧЕСЛИ. Если вы не ищете легких путей или все еще используете Excel 2003 или более раннюю версию, вы можете получить желаемый результат с помощью СУММПРОИЗВ.
Чтобы сделать ваши формулы СУММПРОИЗВ более гибкими, мы указываем нужный регион и товар в отдельных ячейках, а затем ссылаемся на эти ячейки в своей формуле, как показано на скриншоте ниже:
- Чтобы подсчитать количество продаж яблок для Севера :
=СУММПРОИЗВ(—(A3:A13=F2); —(B3:B13=F3))
- Суммируем продажияблок для Севера:
=СУММПРОИЗВ(—(A3:A13=F2); —(B3:B13=F3); C3:C13)
- Чтобы найти средний размер продажи яблок на Севере:
Чтобы вычислить среднее значение, мы просто делим сумму на количество следующим образом:
=СУММПРОИЗВ(—(A3:A13=F2); —(B3:B13=F3); C3:C13)/СУММПРОИЗВ(—(A3:A13=F2); —(B3:B13=F3))
Формула СУММПРОИЗВ с логикой ИЛИ
Напомним, что логика ИЛИ означает, что выполняется хотя бы одно из нескольких условий. Чтобы суммировать или подсчитывать ячейки с помощью логики ИЛИ, используйте знак плюс (+) между массивами.
В формулах СУММПРОИЗВ Excel, а также в формулах массива символ плюс действует как оператор ИЛИ, который указывает Excel вернуть ИСТИНА, если ЛЮБОЕ из условий в данном выражении оценивается как ИСТИНА.
На следующем скриншоте показана такая формула в действии:
Чтобы подсчитать количество всех продаж апельсинов и лимонов независимо от региона, использована следующая формула:
=СУММПРОИЗВ((B3:B13=F2)+(B3:B13=H2))
Она означает следующее: подсчитывать ячейки, если B3:B13=»апельсины» ИЛИ B3:B13=»лимоны».
Чтобы найти общие продажи апельсинов и лимонов , добавьте еще один аргумент, содержащий диапазон продаж :
=СУММПРОИЗВ((B3:B13=F2)+(B3:B13=H2); C3:C13)
Формула СУММПРОИЗВ с логикой И и ИЛИ
Во многих ситуациях вам может понадобиться условно подсчитать или суммировать ячейки с помощью логики И и ИЛИ одновременно. Даже в последних версиях Excel серия функций ЕСЛИ на это не способна.
Одним из возможных решений является объединение двух или более функций СУММЕСЛИМН+СУММЕСЛИМН или СЧЁТЕСЛИМН+СЧЁТЕСЛИМН .
Другой способ — использовать функцию СУММПРОИЗВ Excel, где:
- Знак умножения (*) используется как оператор И.
- Плюс (+) используется как оператор ИЛИ.
Для лучшего понимания, рассмотрим следующие примеры.
Чтобы сделать формулы немного более компактными, вы можете записать переменные в отдельные ячейки — «Регион» в F1 и «Товары» в F2 и H2 — и ссылаться на них в своей формуле:
Сосчитаем, сколько раз яблоки и лимоны были проданы в Северном регионе. Для этого составим формулу со следующей логикой:
=СУММПРОИЗВ((A3:A13=F2)*((B3:B13=F3)+(B3:B13=H3)))
Чтобы суммировать продажи яблок и лимонов в Северном регионе, возьмите приведенную выше формулу и добавьте массив Продажи с логикой «И»:
=СУММПРОИЗВ((A3:A13=F2)*((B3:B13=F3)+(B3:B13=H3))*C3:C13)
Формула СУММПРОИЗВ для средневзвешенного значения
Еще одним распространенным использованием СУММПРОИЗВ в Excel является вычисление средневзвешенного значения, где каждому числу присваивается определенный вес.
Общая формула СУММПРОИЗВ для средневзвешенного значения выглядит следующим образом:
СУММПРОИЗВ( значения ; веса )/СУММ( веса )
Если предположить, что значения находятся в ячейках B2:B7, а веса — в ячейке C2:C7, формула средневзвешенного СУММПРОИЗВ будет выглядеть следующим образом:
=СУММПРОИЗВ(B2:B7;C2:C7)/СУММ(C2:C7)
Думаю, на этом этапе у вас не возникнет трудностей с пониманием логики формулы. Находим сумму произведений процента выполнения на коэффициент важности и делим на сумму коэффициентов важности.
СУММПРОИЗВ как альтернатива формулам массива
Даже если вы читаете эту статью в ознакомительных целях и детали, скорее всего, потускнеют в вашей памяти, помните только один ключевой момент — функция СУММПРОИЗВ в Excel работает с массивами.
Какие преимущества это дает вам? По сути, вы сможете легко управлять своими формулами, не нажимая Ctrl + Shift + Enter каждый раз, когда вы вводите новую или редактируете существующую формулу массива.
В качестве примера мы можем взять простую формулу массива, которая подсчитывает все символы в заданном диапазоне ячеек. Более подробно этот пример рассмотрен в статье Как посчитать количество знаков в Excel.
=СУММПРОИЗВ(ДЛСТР( диапазон ))
или
=СУММПРОИЗВ(ДЛСТР(A2:A5))
Функция ДЛСТР в результате обработки диапазона ячеек возвращает массив значений – число символов в каждой из ячеек. Далее СУММПРОИЗВ складывает эти элементы массива и возвращает общее количество символов.
Excel СУММПРОИЗВ — примеры расширенных формул
Теперь, когда вы знаете синтаксис и логику функции СУММПРОИЗВ в Excel, вы можете изучить более сложные и более мощные формулы, в которых СУММПРОИЗВ используется совместно с другими функциями Excel.
Вот как мы это уже делали в более ранних публикациях на нашем сайте:
- СУММПРОИЗВ для ВПР по нескольким критериям — как выполнять поиск по нескольким критериям и возвращать совпадающие числа.
- «Левый» ВПР для числовых значений – извлекаем из столбца слева от столбца поиска числовые значения и суммируем их.
- Как подсчитать символы в Excel — формулы для подсчета всего или только определенных символов в диапазоне.
- СУММПРОИЗВ для подсчета слов в Excel — формулы для получения общего количества слов в определенном диапазоне или подсчета только определенных слов.
- Как подсчитать различные значения — узнайте, сколько разных значений появляется в диапазоне (уникальных плюс первые повторяющиеся вхождения).
- Подсчет дубликатов и уникальных значений — формула СУММПРОИЗВ / СЧЁТЕСЛИ для подсчета дубликатов и уникальных значений в столбце или между двумя столбцами.
- Извлечение только чисел из буквенно-цифровых строк — СУММПРОИЗВ в сочетании с 7 другими функциями извлекает все числа из строки число-текст-число.
Содержание
- Функция СУММПРОИЗВ() — Сложение и подсчет с множественными условиями в EXCEL
- Нахождение суммы произведений элементов массивов
- Суммирование и подсчет значений удовлетворяющих определенным критериям
- СУММПРОИЗВ() – как формула массива
- Сумма произведений в excel с условием
- Функция СУММПРОИЗВ() — Сложение и подсчет с множественными условиями в MS EXCEL
- Нахождение суммы произведений элементов массивов
- Суммирование и подсчет значений удовлетворяющих определенным критериям
- СУММПРОИЗВ() – как формула массива
- «СУММПРОИЗВ» в Excel.
- Использование функции СУММПРОИЗВ для суммирования произведений соответствующих значений в одном или нескольких массивах
- Пример
- Функция СУММЕСЛИМН() Сложение с несколькими критериями в MS EXCEL (Часть 2.Условие И)
- Задача1 (1 текстовый критерий и 1 числовой)
- Задача2 (2 числовых критерия)
- Задача3 (2 критерия Дата)
- Задача4 (Месяц)
- Альтернативный вариант
- Cумма произведений с условием (Формулы/Formulas)
- СУММПРОИЗВ массивов, содержащих текстовые значения
- сумм произв если
- сумму произведений с тремя условиями (Формулы/Formulas)
- Умножение с условием + суммирование с условием
Функция СУММПРОИЗВ() — Сложение и подсчет с множественными условиями в EXCEL
history 3 апреля 2013 г.
Функция СУММПРОИЗВ() , английская версия SUMPRODUCT(), не так проста, как кажется с первого взгляда: помимо собственно нахождения суммы произведений, эта функция может использоваться для подсчета и суммирования значений на основе критериев, а также, в некоторых случаях, избавить от необходимости применений формул массива.
Существует несколько вариантов применения функции СУММПРОИЗВ() :
- нахождение суммы произведений элементов списка (массива);
- суммирование и подсчет значений, удовлетворяющих определенным критериям;
- замена формул массива (в некоторых случаях).
Нахождение суммы произведений элементов массивов
В этом разделе показан синтаксис функции СУММПРОИЗВ() и раскрыт ее потенциал для других применений.
Пусть имеется 2 диапазона чисел A3:A6 и B3:B6 , содержащие соответственно 2 массива чисел : <4:8:6:1>и <7:6:7:5>. Записав формулу =СУММПРОИЗВ(A3:A6;B3:B6) , получим 123. Результат получен поэлементным перемножением всех элементов двух массивов, а затем сложением полученных произведений. То есть были выполнены следующие арифметические действия: 4*7 + 8*6 + 6*7 + 1*5= 123
Таким образом, можно найти сумму произведений 3-х, 4-х и т.д. массивов.
Аргументы, которые являются массивами, должны иметь одинаковые размерности (в нашем случае это массивы по 4 элемента). В противном случае функция СУММПРОИЗВ() возвращает значение ошибки #ЗНАЧ!.
В формуле =СУММПРОИЗВ(A3:A6;B3:B6) функция СУММПРОИЗВ() трактует нечисловые элементы массивов как нулевые. Однако, как показано ниже, функцию можно использовать для подсчета текстовых значений.
Что произойдет если указать только 1 массив, т.е. =СУММПРОИЗВ(A3:A6) ? Тогда функция СУММПРОИЗВ() вернет сумму элементов, т.е. будет эквивалентна функции СУММ() : =СУММ(A3:A6) .
Синтаксис функции СУММПРОИЗВ() позволяет не просто указывать в качестве аргумента определенный диапазон, но и осуществлять арифметические действия перед операцией суммирования. Например, записав:
- =СУММПРОИЗВ(A3:A6*2) , получим сумму произведений =38 (каждый элемент массива из A3:A6 был умножен на 2, затем все произведения просуммированы);
- =СУММПРОИЗВ(A3:A6*B3:B6) , получим результат суммы произведений – 123 (все элементы массивов были попарно перемножены, а затем сложены, т.е. A3*B3+ A4*B4+ A5*B5+ A6*B6), т.е. эта запись эквивалента формула =СУММПРОИЗВ(A3:A6;B3:B6) ;
- =СУММПРОИЗВ(A3:A6+B3:B6) , получим сумму элементов из двух диапазонов;
- =СУММПРОИЗВ(A3:A6/B3:B6 ), получим сумму попарных отношений всех элементов, т.е. 4/7 + 8/6 + 6/7 + 1/5= 2,9619
Аналогичные вычисления можно выполнить и с функцией СУММ() , только для этого нужно ее ввести как формулу массива , т.е. после ввода функции в ячейку вместо ENTER нажать CTRL+SHIFT+ENTER : =СУММ(A3:A6/B3:B6)
Прелесть функции СУММПРОИЗВ() в том, что после ввода функции в ячейку можно просто нажать ENTER , что снимает некий психологический барьер перед использованием формул массива .
Оказывается, что в качестве аргумента этой функции можно указать не только произведение массивов ( A3:A6*B3:B6 ), но и использовать другие функции и даже применить к массивам операции сравнения, т.е. использовать ее для сложения чисел, удовлетворяющих определенным условиям.
Суммирование и подсчет значений удовлетворяющих определенным критериям
Попробуем подсчитать число значений больших 2 в диапазоне A3:A6 , содержащий значения 4, 8, 6, 1.
Если мы запишем формулу =СУММПРОИЗВ(A3:A6>2) , то получим результат 0. Выделив в Строке формул A3:A6>2 и нажав клавишу F9 , получим массив <ИСТИНА: ИСТИНА: ИСТИНА: ЛОЖЬ>, который говорит, что мы движемся в правильном направлении: в диапазоне A3:A6 больше 2 только первые 3 значения. Хотя значению ИСТИНА соответствует 1, а ЛОЖЬ – 0, мы не получим 3, т.к. для перевода значений ИСТИНА/ЛОЖЬ в числовую форму требуется применить к ним арифметическую операцию. Для этого можно, например, применить операцию двойного отрицания (—), что позволит привести массив в числовую форму <1:1:1:0>.
Итак, задача подсчета значений больше 2 решается следующим образом: =СУММПРОИЗВ(—(A3:A6>2))
Вместо двойного отрицания можно использовать другие формулы: =СУММПРОИЗВ(1*(A3:A6>2)) или =СУММПРОИЗВ(0+(A3:A6>2)) или даже так =СУММПРОИЗВ((A3:A6>2)^1) .
Запись >2 является критерием, причем можно указать любые операции сравнения ( =; =).
Критерии можно указывать в форме ссылки: =СУММПРОИЗВ(—(A3:A6>G8)) – ячейка G 8 должна содержать число 2.
Критерии можно применять и к текстовым значениям, например, =СУММПРОИЗВ(—(B3:B6=»яблоки»)) – вернет количество ячеек, содержащие слово яблоки (подробнее, например, в статье Подсчет значений с множественными критериями (Часть 1. Условие И)) .
Функцию СУММПРОИЗВ() можно использовать для отбора значений по нескольким критериям (с множественными условиями). Как известно, 2 критерия могут образовывать разные условия:
- Условие ИЛИ . Например, подсчитать ячеек содержащих значение яблоки ИЛИ груши =СУММПРОИЗВ((B3:B6=»яблоки»)+ (B3:B6=»груши») ) ;
- Условие И . Например, подсчитать количество значений больше 2 и меньше 5: =СУММПРОИЗВ((A3:A6>2)* (A3:A6 )
- Условие И . Например, найти сумму Чисел больше 2 и меньше 5: =СУММПРОИЗВ((A3:A6>2)* (A3:A6 A3:A6 ) )
В файле примера приведены решения подобных задач.
СУММПРОИЗВ() – как формула массива
В ряде случаев (когда нужно подсчитать или сложить значения, удовлетворяющие определенным критериям) можно заменить использование формул массива функцией СУММПРОИЗВ() , например:
- =СУММПРОИЗВ(—ЕПУСТО(D2:D23)) подсчет пустых ячеек в диапазоне;
- =СУММПРОИЗВ(НАИБОЛЬШИЙ(A:A;<1;2;3>)) сумма 3-х наибольших значений ;
- =СУММПРОИЗВ((A3:A6>СРЗНАЧ(A3:A6))*(A3:A6)) сумма значений, которые больше среднего .
Совет : Дополнительную информацию об этой функции можно ]]> подчерпнуть здесь (английский язык). ]]>
Источник
Сумма произведений в excel с условием
Функция СУММПРОИЗВ() — Сложение и подсчет с множественными условиями в MS EXCEL
Смотрите такжеСумма нулей какоеПомогите пожалуйста! в функции СУММПРОИЗВ, формула выдавала значение,Альтернативным вариантом для всехДругой задачей может быть увидеть, выделив в Например, количество ящиков столбцов для отображения в другом — формула. написаны в ячейкеНам нужно перемножить данные
содержащие слово яблоки к массивам операции
- только 1 массив,Функция СУММПРОИЗВ(), английская версия
- число даст?buchlotnik
- только если соблюдена рассчитанное для чисел
Нахождение суммы произведений элементов массивов
4-х задач является нахождение сумм ящиков Строке формул A2:A13=D2, с товаром персики
всех данных. количество приобретенных товаров.=СУММПРОИЗВ((B2:B8=C12)*(C2:C8=C11)*(D2:D8>=C13)*(D2:D8 Получится так. С12, т.е. в столбца А с (подробнее, например, в сравнения, т.е. использовать т.е.=СУММПРОИЗВ(A3:A6)? Тогда функция SUMPRODUCT(), не такСергей: регистр имеет значение, строгая пунктуация СУММПРОИЗВ в массиве. Пример применение Автофильтра. только тех партий а затем нажав (ячейка
AЧтобы создать формулу, введитеОбратите внимание диапазоне «менеджер» искать
данными столбца В статье Подсчет значений ее для сложения СУММПРОИЗВ() вернет сумму проста, как кажется: Я в массивах дата — это (только через
прилагаю.Для решения 3-й задачи товаров, у которыхF9D2B=СУММПРОИЗВ(B3:B6,C3:C6)
. слово «Васечкин». Подправляем (1*2, 3*4, 5*6) с множественными критериями чисел, удовлетворяющих определенным элементов, т.е. будет с первого взгляда:
не селен. не текст, и, а такжеВладимир таблица с настроенным количество ящиков попадает;
- ), у которых остатокDи нажмите клавишуВ формуле перед формулу – эту и, затем, все (Часть 1. Условие
- условиям. эквивалентна функции СУММ(): помимо собственно нахожденияПробовал просит значение. т.д. есть только одно: Можно вставить ЕЧИСЛО(), автофильтром выглядит так в определенный интервал,
- Результатом вычисления B2:B13 является ящиков на складе
- E ВВОД. датой начала периода часть формулы заключаем результаты сложить. В
И)).Попробуем подсчитать число значений =СУММ(A3:A6). суммы произведений, этаМожно на примере=СУММПРОИЗВ((G1:G17=B3)*(F1:F17=СТРОЧН(B$4))*(ТЕКСТ(H1:H17;»ММММ»)=ПРОПНАЧ(B$6))*(ГОД(H1:H17)=C$6)) условие. Не универсальная ЕСЛИОШИБКА(). (см. файл примера например от 5 массив <3:5:11:98:4:8:56:2:4:6:10:11>, т.е. >=6 (ячейка
3Каждое значение в столбце ставим не просто в круглые скобки ячейку С 21Функцию СУММПРОИЗВ() можно использовать больших 2 вСинтаксис функции СУММПРОИЗВ() позволяет функция может использоваться
показать?Cactus функцияTatirus Лист 2 Даты). до 20 (см. просто значения изE24 B будет умножено «равно», а «больше» и ставим знак
Суммирование и подсчет значений удовлетворяющих определенным критериям
устанавливаем функцию. для отбора значений диапазоне не просто указывать для подсчета иvikttur
: Спасибо огромное, аЮрий М: Получилось вставить ЕЧИСЛО()Предварительно таблицу нужно преобразовать файл примера Лист столбца). Мы должны получить2 на значение в и «равно». Перед «*» (умножить).На закладке «Формулы» по нескольким критериямA3:A6 в качестве аргумента суммирования значений на: Вы о функции каким образом можно: Tatirus, да сколько под функцию СУММПРОИЗВ. в формат таблиц 2Числа).B результат 64. Подсчет7 той же строке датой окончания периодаC2:C8=C11 – это в разделе «Библиотека (с множественными условиями)., содержащий значения 4,
определенный диапазон, но основе критериев, а уже прочитали? И
получить информацию за же можно Вам Но решение формула MS EXCEL 2007Формулы строятся аналогично задаче
; можно реализовать множеством8 столбца C, а
ставим «меньше» и означает, что в функций» нажимаем на Как известно, 2 8, 6, 1. и осуществлять арифметические
также, в некоторых попробовали применить? В весь год, за про цитирование говорить? выдала неверное. и включить строку 1: =СУММЕСЛИМН(B2:B13;B2:B13;»>=»&D2;B2:B13;»Результатом поэлементного умножения массивов формул, приведем несколько6
произведения будут суммированы. «равно». диапазоне С2:С8 нужно кнопку «Математические» и критерия могут образовыватьЕсли мы запишем формулу действия перед операцией
- случаях, избавить от справке после описания все время. Уже и себяV
- Итогов.Примечание (A2:A13=D2)*(B2:B13) является <0:0:0:0:4:8:56:0:0:0:0:0>. (см. файл примера6 Общая стоимость приобретенных
- В Excel можно искать то, что выбираем функцию «СУММПРОИЗВ». разные условия: =СУММПРОИЗВ(A3:A6>2), то получим суммирования. Например, записав:
необходимости применений формул нормальный пример есть.
СУММПРОИЗВ() – как формула массива
Che79 цитируете. : =СУММ(C2:C9*(B2:B9>2)*ЕСЛИ(ЕЧИСЛО(B2:B9);B2:B9;0)) формула массива.Verniy55: для удобства, строки, При умножении числа Лист Текст и
- 7 товаров составит 21,60 р.
- вставить ссылки не написано в ячейке
- Диалоговое окно заполнилиУсловие
результат 0. Выделив=СУММПРОИЗВ(A3:A6*2), получим сумму произведений массива.
«СУММПРОИЗВ» в Excel.
график так, что ячеек С2 и
чтобы она складывала и меньше 5: правильном направлении: в (все элементы массивовВ этом разделе показан: Для функции пустая_ячейка=0.: для русской версии просто ячейку, а
: Михаил, спасибо большое! поле продаж фруктов со столбцами Дата складе», которые удовлетворяют значения и текст массивов (A2:B4 и клавишу ВВОД. в нем будут С5. А яблоки выборочные данные. =СУММПРОИЗВ((A3:A6>2)*(A3:A6 диапазоне были попарно перемножены, синтаксис функции СУММПРОИЗВ()и Что Вы ждете=СЧЁТЕСЛИМН(F$1:F$17;B$4;G$1:G$17;B2;H$1:H$17;»>=»&ЕСЛИ(B$6=»за весь год»;»1.»;ЛЕВБ(B$6;3))&C$6;H$1:H$17;»про все произведение ячейки одного Никогда не применяла
Использование функции СУММПРОИЗВ для суммирования произведений соответствующих значений в одном или нескольких массивах
будет равно 0 продажи и Объем критерию >=E2 (т.е. игнорируются. D2:E4) и возвращаетПосле нажатия клавиши ВВОД автоматически добавляться и зеленые и красныеНе числовые данныеУсловиеA3:A6 а затем сложены, раскрыт ее потенциал от суммы 1*0+1*1? время забылКод=ЕСЛИ(C$6=»за все диапазона на ячейку функцию Ч, ничего то это поле продаж. >=6) соответствуют 1;A2:A13 B2:B13 Интервал_условия1; интервал_условия2; сумму произведений — т. е.
будет выведен такой отражаться последние данные. яблоки не посчитались. (текст, напирмер) функцияИбольше 2 только т.е. A3*B3+ A4*B4+ для других применений.
Еще раз: Вы время»;СЧЁТЕСЛИМН(F$1:F$17;B$4;G$1:G$17;B2);СЧЁТЕСЛИМН(F$1:F$17;B$4;G$1:G$17;B2;H$1:H$17;»>=»&ЕСЛИ(B$6=»за весь год»;»1.»;ЛЕВБ(B$6;3))&C$6;H$1:H$17;» другого диапазона. не знала о не учитывать иФормулы строятся аналогично задаче
Далее, функция СУММПРОИЗВ() попарно … 3*2 + 4*7 же результат: 21,60 р.
Как для этого Это недостаток этой обозначает нулем, если. Например, найти сумму первые 3 значения. A5*B5+ A6*B6), т.е.Пусть имеется 2 диапазона пытались применить функцию?
SERG69sva ней! Замечательно!! не учитывать оборот 2: =СУММЕСЛИМН(B6:B17;A6:A17;»>=»&D6;A6:A17;» перемножает элементы массивов представляют собой от + 8*6 + Значение в ячейке настроить таблицу, смотрите
функции, п. ч. не соответствуют условиям, Чисел больше 2 Хотя значению ИСТИНА эта запись эквивалента чисел Покажите.: Доброго времени всем!: СУММПРОИЗВ((B13=$B$5:$B$9);($C$5:$C$9*$D$5:$D$9))Tatirus этого магазина вПримечание и суммирует полученные 1 до 127 6*7 + 1*5
B3 будет умножено в статье «Функция
нельзя применить символы, и единицей, если и меньше 5:
соответствует 1, а формула =СУММПРОИЗВ(A3:A6;B3:B6);A3:A6B3:B6Похоже, что нет,
Пример
Помогите советом, примером,insalin: Очень интересно работает сумме знаменателя.. Даты могут быть произведения. Получаем –
диапазонов, в которых + 9*3 (156). на значение в «СМЕЩ» в Excel». которые позволили бы отвечают условиям в =СУММПРОИЗВ((A3:A6>2)*(A3:A6 ЛОЖЬ – 0,=СУММПРОИЗВ(A3:A6+B3:B6), получим сумму элементов, содержащие соответственно 2 если спрашиваете о формулой, вообщем всем
: Подставила. почему то
Возможно ли реализовать
представлены в числовой
проверяется соответствующее условие.
Для полный качества также
посчитать все яблоки.
формуле. И ячейки
В файле примера приведены
из двух диапазонов;
показывает только нули.
ее использовать просто
форме см. статью3. Другим вариантом использованияD2 «>=»&E2 Условие1; условие2; можно открыть эту результату будет добавленоМы стараемся какНо, есть функция, с единицами и
решения подобных задач. 3, т.к. для=СУММПРОИЗВ(A3:A6/B3:B6), получим сумму попарных и <7:6:7:5>. ЗаписавСергейСитуация такова:
:( для суммы произведенийБолее подробно изложил Как Excel хранит
функции СУММПРОИЗВ() является
Функция СУММЕСЛИМН() Сложение с несколькими критериями в MS EXCEL (Часть 2.Условие И)
… книгу в Excel. произведение значений в можно оперативнее обеспечивать в которую можно числами в ячейкахВ ряде случаев (когда перевода значений ИСТИНА/ЛОЖЬ отношений всех элементов, формулу =СУММПРОИЗВ(A3:A6;B3:B6), получим
: Вы таблицу видели?Возможно сделать такsva без условий =СУММПРОИЗВ((Ч(B2:B9)*(C2:C9))) в приложенном файле. дату и время формула =СУММПРОИЗВ((A2:A13=D2)*(B2:B13)*(B2:B13>=E2)).
Задача1 (1 текстовый критерий и 1 числовой)
представляют собой от Просто щелкните значок ячейках B4 и вас актуальными справочными вставить символы и напротив них, умножает нужно подсчитать или в числовую форму т.е. 4/7 + 123. Результат полученСергей чтоб в столбце: Так должно работатьто выдается неверноRustemПри необходимости даты могут4. Формула массива =СУММ((A2:A13=D2)*(B2:B13)*(B2:B13>=E2)) 1 до 127 Excel в правом C4 и т. д. материалами на вашем
посчитать все яблоки.
и, затем, складывает. сложить значения, удовлетворяющие
- требуется применить к 8/6 + 6/7 поэлементным перемножением всех: Нужно чтоб ячейки «С» вводить количествоСУММПРОИЗВ(—(B13=$B$5:$B$9);($C$5:$C$9*$D$5:$D$9)) решение (24 вместо
- : В знаменатель такую быть введены непосредственно похожа на вышеупомянутую условий в виде нижнем углу книги Как видите, при
- языке. Эта страница Смотрите статью «СуммированиеПримеры практического применения функции определенным критериям) можно ним арифметическую операцию. + 1/5= 2,9619 элементов двух массивов, столбца «С», которым товара, потом чтоб
insalin 39). Как она формулу: в формулу =СУММЕСЛИМН(B6:B17;A6:A17;»>=15.01.2010″;A6:A17;» формулу =СУММПРОИЗВ((A2:A13=D2)*(B2:B13)*(B2:B13>=E2)) После числа, выражения, ссылки, сохраните файл большом количестве строк переведена автоматически, поэтому ячеек в Excel «СУММПРОИЗВ» в заменить использование формул Для этого можно,Аналогичные вычисления можно выполнить
а затем сложением
присвоено, значение умножались количество умножалось на
- : Ура! Все отлично. срабатывает в данном200?’200px’:»+(this.scrollHeight+5)+’px’);»>=($C$13*(—(C4<>0))+$C$14*(—(G4<>0))+$C$15*(—(K4<>0)))Чтобы вывести условия отбора ее ввода нужно на ячейку или в удобном месте функция СУММПРОИЗВ может ее текст может по условию».Excel массива функцией СУММПРОИЗВ(),
- например, применить операцию и с функцией полученных произведений. То на цени в цену в той А что это
- случае??_Boroda_ в текстовой строке вместо текста, определяющих, какие на вашем компьютере значительно упростить работу. содержать неточности и
- Второй способ.. например: двойного отрицания (—), СУММ(), только для есть были выполнены соответствии им, потом
- строке где было значит «—» передV: Посмотрите такой вариант используейте формулу =»Объем
ENTER ячейки будут просуммированы. и откройте ее.
Примечания: грамматические ошибки. ДляФункция «СУММПРОИЗВ» вПервый способ.=СУММПРОИЗВ(—ЕПУСТО(D2:D23)) подсчет пустых ячеек что позволит привести этого нужно ее следующие арифметические действия:
суммировались в ячейку введено значение а скобкой? Это какая-то
: Формулы — Вычислить для каждого овощефрукта продаж за периоднажатьПорядок аргументов различен вК началу страницы нас важно, чтобыExcel с датамиТаблица такая. в диапазоне; массив в числовую ввести как формулу 4*7 + 8*6
«D2». потом это все неучтенная возможность Excel? формулу. Увидите какVerniy55
Задача2 (2 числовых критерия)
массива, т.е. после + 6*7 +
vikttur суммировалось в одной ;) она работает.: Всем спасибо за
Задача3 (2 критерия Дата)
«&ТЕКСТ(E6;»дд.ММ.гг»)5. Формула массива =СУММ(ЕСЛИ((A2:A13=D2)*(B2:B13>=E2);B2:B13)) представляет СУММЕСЛИ(). В СУММЕСЛИМН() в строках, поля должны быть одинаковой вам полезна. ПросимЕсли нужно сложить проданных яблок менеджером значений;
Итак, задача подсчета значений ввода функции в
1*5= 123: Да видел я, ячейке?svaTatirus помощь !
В последней формуле использован еще один вариант аргумент
которых удовлетворяют сразу длины. вас уделить пару данные из таблицы Васечкиным. Здесь два=СУММПРОИЗВ((A3:A6>СРЗНАЧ(A3:A6))*(A3:A6)) сумма значений, которые
больше 2 решается ячейку вместо
Задача4 (Месяц)
Таким образом, можно найти видел. И файлУмножение с условием: Ну, если говорить,: Всем ячейкам массива
Tatirus Пользовательский формат. многокритериального подсчета значений.интервал_суммирования двум критериям (УсловиеЕсли ячейка содержит текст, секунд и сообщить, по датам, то
условия – «сложить больше среднего. следующим образом: =СУММПРОИЗВ(—(A3:A6>2))ENTER сумму произведений 3-х, открывал, и присматривался.
Альтернативный вариант
что C:C>0 тогда проще, то это B2:B9 присваивает значение
: Уважаемые форумчане! Подскажите,Немного модифицируем условие предыдущей6. Формула =БДСУММ(A1:B13;B1;D14:E15) требуетявляется первым аргументом, И). Рассмотрим Текстовые
а не число, помогла ли она вставляем в формулу яблоки» и «проданныеСовет
Cумма произведений с условием (Формулы/Formulas)
Вместо двойного отрицания можнонажать 4-х и т.д.
Свою формулу вставлял. B:B*C:C + суммирование для перевода логических 1, а не пожалуйста, можно ли задачи: найдем суммарные предварительного создания таблицы а в СУММЕСЛИ() критерии, Числовые и он считается нулевым вам, с помощью еще один диапазон Васечкиным». В ячейку: Дополнительную информацию об
использовать другие формулы:CTRL+SHIFT+ENTER
массивов.Но Вашей функции
с условием что значений в числовые, собственное числовое значение
разрешить такую задачу.
продаж за месяц(см. с условиями (см. – третьим. При
критерии в формате значением. кнопок внизу страницы.
СУММПРОИЗВ массивов, содержащих текстовые значения
— диапазон дат Е9 устанавливаем функцию. этой функции можно =СУММПРОИЗВ(1*(A3:A6>2)) или =СУММПРОИЗВ(0+(A3:A6>2)): =СУММ(A3:A6/B3:B6)Аргументы, которые являются массивами, ни на картинке, C:C>0. т.е. в примере каждой из ячеек Есть формула СУММПРОИЗВ, файл примера Лист статью про функцию копировании и редактировании Дат. Разберем функциюЕсли вы хотите поэкспериментировать Для удобства также и пишем ячейку,На закладке «Формулы»Рассмотрим или даже так Прелесть функции СУММПРОИЗВ() в должны иметь одинаковые ни в файле
Суммированием все понятно, вместо ЛОЖЬ/ИСТИНА будет . Это так
один из массивов Месяц). БДСУММ()). Заголовки этой этих похожих функций СУММЕСЛИМН(), английская версия
с функцией СУММПРОИЗВ приводим ссылку на
в которой написана в разделе «Библиотекапримеры функции «СУММПРОИЗВ» в =СУММПРОИЗВ((A3:A6>2)^1). том, что после размерности (в нашем
не вижу. но мне нужно
0/1. функция ведет себя которого содержит числовыеФормулы строятся аналогично задаче таблицы должны в необходимо следить за
SUMIFS(). и массивами, вот оригинал (на английском эта дата. Формула функций» нажимаем наExcel
Запись >2 является критерием, ввода функции в случае это массивыЕще раз: СУММПРОИЗВ чтоб перед суммированием
Михаил С. из-за того, что и текстовые значения. 3, но пользователь
точности совпадать с тем, чтобы аргументыВ качестве исходной таблицы Excel Online книгу языке) . в ячейке Е10 кнопку «Математические» и, которая сначала умножает причем можно указать ячейку можно просто по 4 элемента). прекрасно справляется с
оно умножалось. : Так тоже должно под суммой произведений СУММПРОИЗВ «плохо» реагирует вводит не 2 соответствующими заголовками исходной
были указаны в возьмем таблицу с с данными, использованнымиСУММПРОИЗВ использует диапазоны ячеек будет такой. выбираем функцию «СУММПРОИЗВ». данные столбцов, затем любые операции сравнения нажать В противном случае задачей.
Заранее благодарен!=СУММПРОИЗВ((B13=$B$5:$B$9)*$C$5:$C$9*$D$5:$D$9) стоят два массива на эти текстовые даты, а название таблицы. Размещение условий
правильном порядке.
сумм произв если
двумя столбцами (полями): в этой статье. (или массивы) в=СУММПРОИЗВ((B2:B8=C12)*(C2:C8=C11)*(D2:D8=C13);E2:E8)Диалоговое окно заполнили складывает. Этой функцией (=; =).ENTER функция СУММПРОИЗВ() возвращаетПытаюсь расшевелить ВасПримерчик прилагается.Cactus
через знак умножения?? значения — выдает
месяца (предполагается, что в одной строке2. другой вариант =СУММПРОИЗВ((A2:A13=D2)*(B2:B13);—(B2:B13>=E2)) текстовым «Фрукты» и
Скопируйте данные примера из качестве аргументов (части
Третий способ
так. можно сделатьКритерии можно указывать в, что снимает некий значение ошибки #ЗНАЧ!. на «почитать-подумать», аvikttur
: Доброй ночи форумчанеV в качестве результата в таблице данные соответствует Условию ИРазберем подробнее использование числовым «Количество на приведенной ниже таблицы
формулы, которые делают.
Нажимаем «ОК». В ячейке
сумму произведений с тремя условиями (Формулы/Formulas)
выборочное суммирование в форме ссылки: =СУММПРОИЗВ(—(A3:A6>G8))
психологический барьер передВ формуле =СУММПРОИЗВ(A3:A6;B3:B6) функция
Вы все «дайте,: В той жеИмеется таблица с
: В данном случае #ЗНАЧ!. Можно ли в рамках 1
(см. диапазон
функции СУММПРОИЗВ(): складе» (См. файл в ячейку A1 его работы). ПеремножаетЕсли нужно С9 получилась такая
Excel по условию – ячейка использованием формул массива. СУММПРОИЗВ() трактует нечисловые не получается». Где строке — умножение.
данными. она обрабатывает только в эту формулу года).D14:E15Результатом вычисления A2:A13=D2 является примера). нового листа Excel. друг с другом
посчитать данные в определенном формула. =СУММПРОИЗВ(B2:B8=C12;C2:C8=C11;E2:E8) . Все это мыG8Оказывается, что в качестве
Умножение с условием + суммирование с условием
элементы массивов как не получается? ИВ отдельной ячейкеНеобходимо выводить информацию первое значение массива
вставить, например, какое-то
Месяц вводится с помощью). массив <ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ИСТИНА:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ>ЗначениеНайдем количество ящиков товара Чтобы отобразить результаты элементов массивов и периодеРазберем эту формулу здесь разберем.должна содержать число аргумента этой функции
нулевые. Однако, как нет желания узнать, просуммировать произведения? в соответствии с B2:B9, а остальные
условие, чтобы расчет Выпадающего списка, переченьПримечание ИСТИНА соответствует совпадению
с определенным Фруктом
формул, выделите формулы
затем суммирует результат., то пишем диапазон и немного подправим.
Сначала разберем, как 2. можно указать не
показано ниже, функцию почему не получается?СУММПРОИЗВ
выпадающим меню. игнорирует.
проводился только для месяцев формируется с: для удобства, строки,
значения из столбца
И и нажмите клавишу
В приведенном примере используется и ячейку началаB2:B8=C12 – это действуетКритерии можно применять и только произведение массивов
можно использовать дляСергейПриВо вложении файлTatirus числовых значений, не использованием Динамического диапазона участвующие в суммировании,
А, у которых Остаток F2, а затем список товаров: в
периода и окончания означает, что вфункция «СУММПРОИЗВ» в Excel.
к текстовым значениям, (A3:A6*B3:B6), но и подсчета текстовых значений.
который не знаю: Т.е. функция Ч реагируя при этом (для исключения лишних выделены Условным форматированиемкритерию, т.е. слову ящиков на складе — ВВОД. При
одном массиве содержатся периода. В ячейке диапазоне В2:В8 нужноУ нас такая например, =СУММПРОИЗВ(—(B3:B6=»яблоки»)) –
использовать другие функцииЧто произойдет если указатьСпасибо!(догадались).
как доделать. будет работать корректно на текстовые, а
месяцев). с правилом =И($A2=$D$2;$B2>=$E$2) персики. Массив можно не менее минимального. необходимости измените ширину их цены, а Е11 будет такая
искать данные, которые таблица.
вернет количество ячеек,
Источник
Функция
СУММПРОИЗВ()
, английская версия SUMPRODUCT(),
не так проста, как кажется с первого взгляда: помимо собственно нахождения суммы произведений, эта функция может использоваться для подсчета и суммирования значений на основе критериев, а также, в некоторых случаях, избавить от необходимости применений формул массива.
Существует несколько вариантов применения функции
СУММПРОИЗВ()
:
- нахождение суммы произведений элементов списка (массива);
- суммирование и подсчет значений, удовлетворяющих определенным критериям;
-
замена
формул массива
(в некоторых случаях).
Нахождение суммы произведений элементов массивов
В этом разделе показан синтаксис функции
СУММПРОИЗВ()
и раскрыт ее потенциал для других применений.
Пусть имеется 2 диапазона чисел
A3:A6
и
B3:B6
, содержащие соответственно 2
массива чисел
: {4:8:6:1} и {7:6:7:5}. Записав формулу
=СУММПРОИЗВ(A3:A6;B3:B6)
, получим 123. Результат получен поэлементным перемножением всех элементов двух массивов, а затем сложением полученных произведений. То есть были выполнены следующие арифметические действия: 4*7 + 8*6 + 6*7 + 1*5= 123
Таким образом, можно найти сумму произведений 3-х, 4-х и т.д. массивов.
Аргументы, которые являются массивами, должны иметь одинаковые размерности (в нашем случае это массивы по 4 элемента). В противном случае функция
СУММПРОИЗВ()
возвращает значение ошибки #ЗНАЧ!.
В формуле
=СУММПРОИЗВ(A3:A6;B3:B6)
функция
СУММПРОИЗВ()
трактует нечисловые элементы массивов как нулевые. Однако, как показано ниже, функцию можно использовать для подсчета текстовых значений.
Что произойдет если указать только 1 массив, т.е.
=СУММПРОИЗВ(A3:A6)
? Тогда функция
СУММПРОИЗВ()
вернет сумму элементов, т.е. будет эквивалентна функции
СУММ()
:
=СУММ(A3:A6)
.
Синтаксис функции
СУММПРОИЗВ()
позволяет не просто указывать в качестве аргумента определенный диапазон, но и осуществлять арифметические действия перед операцией суммирования. Например, записав:
=СУММПРОИЗВ(A3:A6*2)
, получим сумму произведений =38 (каждый элемент массива из
A3:A6
был умножен на 2, затем все произведения просуммированы);
=СУММПРОИЗВ(A3:A6*B3:B6)
, получим результат суммы произведений – 123 (все элементы массивов были попарно перемножены, а затем сложены, т.е. A3*B3+ A4*B4+ A5*B5+ A6*B6), т.е. эта запись эквивалента формула
=СУММПРОИЗВ(A3:A6;B3:B6)
;
=СУММПРОИЗВ(A3:A6+B3:B6)
, получим сумму элементов из двух диапазонов;
=СУММПРОИЗВ(A3:A6/B3:B6
), получим сумму попарных отношений всех элементов, т.е. 4/7 + 8/6 + 6/7 + 1/5= 2,9619
Аналогичные вычисления можно выполнить и с функцией
СУММ()
, только для этого нужно ее ввести как
формулу массива
, т.е. после ввода функции в ячейку вместо
ENTER
нажать
CTRL+SHIFT+ENTER
:
=СУММ(A3:A6/B3:B6)
Прелесть функции
СУММПРОИЗВ()
в том, что после ввода функции в ячейку можно просто нажать
ENTER
, что снимает некий психологический барьер перед использованием
формул массива
.
Оказывается, что в качестве аргумента этой функции можно указать не только произведение массивов (
A3:A6*B3:B6
), но и использовать другие функции и даже применить к массивам операции сравнения, т.е. использовать ее для сложения чисел, удовлетворяющих определенным условиям.
Суммирование и подсчет значений удовлетворяющих определенным критериям
Попробуем подсчитать число значений больших 2 в диапазоне
A3:A6
, содержащий значения 4, 8, 6, 1.
Если мы запишем формулу
=СУММПРОИЗВ(A3:A6>2)
, то получим результат 0. Выделив в
Строке формул
A3:A6>2
и нажав клавишу
F9
, получим массив {ИСТИНА: ИСТИНА: ИСТИНА: ЛОЖЬ}, который говорит, что мы движемся в правильном направлении: в диапазоне
A3:A6
больше 2 только первые 3 значения. Хотя значению ИСТИНА соответствует 1, а ЛОЖЬ – 0, мы не получим 3, т.к. для перевода значений ИСТИНА/ЛОЖЬ в числовую форму требуется применить к ним арифметическую операцию. Для этого можно, например, применить операцию двойного отрицания (—), что позволит привести массив в числовую форму {1:1:1:0}.
Итак, задача подсчета значений больше 2 решается следующим образом:
=СУММПРОИЗВ(—(A3:A6>2))
Вместо двойного отрицания можно использовать другие формулы:
=СУММПРОИЗВ(1*(A3:A6>2))
или
=СУММПРОИЗВ(0+(A3:A6>2))
или даже так
=СУММПРОИЗВ((A3:A6>2)^1)
.
Запись >2 является критерием, причем можно указать любые операции сравнения (<; <=; >=; =).
Критерии можно указывать в форме ссылки:
=СУММПРОИЗВ(—(A3:A6>G8))
– ячейка
G
8
должна содержать число 2.
Критерии можно применять и к текстовым значениям, например,
=СУММПРОИЗВ(—(B3:B6=»яблоки»))
– вернет количество ячеек, содержащие слово
яблоки
(подробнее, например, в статье
Подсчет значений с множественными критериями (Часть 1. Условие И))
.
Функцию
СУММПРОИЗВ()
можно использовать для отбора значений по нескольким критериям (с множественными условиями). Как известно, 2 критерия могут образовывать разные условия:
-
Условие
ИЛИ
. Например, подсчитать ячеек содержащих значение
яблоки
ИЛИ
груши
=СУММПРОИЗВ((B3:B6=»яблоки»)+
(B3:B6=»груши»)
)
; -
Условие
И
. Например, подсчитать количество значений больше 2 и меньше 5:
=СУММПРОИЗВ((A3:A6>2)*
(A3:A6<5)
)
-
Условие
И
. Например, найти сумму Чисел больше 2 и меньше 5:
=СУММПРОИЗВ((A3:A6>2)*
(A3:A6<5)*(
A3:A6
)
)
В
файле примера
приведены решения подобных задач.
СУММПРОИЗВ() – как формула массива
В ряде случаев (когда нужно подсчитать или сложить значения, удовлетворяющие определенным критериям) можно заменить использование
формул массива
функцией
СУММПРОИЗВ()
, например:
=СУММПРОИЗВ(—ЕПУСТО(D2:D23))
подсчетпустых ячеек
в диапазоне;
=СУММПРОИЗВ(НАИБОЛЬШИЙ(A:A;{1;2;3}))
сумма 3-х наибольших значений;
=СУММПРОИЗВ((A3:A6>СРЗНАЧ(A3:A6))*(A3:A6))
сумма значений, которые большесреднего
.
Совет
: Дополнительную информацию об этой функции можно
подчерпнуть здесь (английский язык).