Загрузите образец книги
В этом руководстве показано, как использовать Функция СУММПРОИЗВ в Excel в Excel.
Обзор функции СУММПРОИЗВ
Функция СУММПРОИЗВ умножает массивы чисел и суммирует результирующий массив.
Чтобы использовать функцию SUMPRODUCT Excel Worksheet, выберите ячейку и введите:
(Обратите внимание, как появляются входные данные формулы)
Функция СУММПРОИЗВ Синтаксис и входные данные:
| 1 | = СУММПРОИЗВ (массив1; массив2; массив3) |
array1 - Массивы чисел.
Что такое функция СУММПРОИЗВ?
Функция СУММПРОИЗВ - одна из наиболее мощных функций Excel. Это название может заставить вас поверить, что оно предназначено только для основных математических вычислений, но его можно использовать для гораздо большего.
Массивы
SUMPRODUCT требует ввода массивов.
Итак, во-первых, что мы подразумеваем под «массивом»? Массив - это просто группа элементов (например, чисел), расположенных в определенном порядке, как и диапазон ячеек. Итак, если бы у вас были числа 1, 2, 3 в ячейках A1: A3, Excel прочитал бы это как массив {1,2,3}. Фактически, вы можете ввести {1,2,3} непосредственно в формулы Excel, и он распознает массив.
Подробнее о массивах мы поговорим ниже, но сначала рассмотрим простой пример.
Основы математики
Давайте посмотрим на базовый пример SUMPRODUCT, используя его для расчета общих продаж.
У нас есть таблица продуктов, и мы хотим подсчитать общий объем продаж. У вас может возникнуть соблазн просто добавить новый столбец, взять количество проданного * цена и затем суммировать новый столбец. Однако вместо этого вы можете просто использовать функцию СУММПРОИЗВ. Давайте рассмотрим формулу:
| 1 | = СУММПРОИЗВ (A2: A4; B2: B4) |
Функция загрузит диапазоны чисел в массивы, умножит их друг на друга, а затем просуммирует результаты:
| 1234 | = СУММПРОИЗВ ({100, 50, 10}, {6, 7, 5})= СУММПРОИЗВ ({100 * 6, 50 * 7, 10 * 5})= СУММПРОИЗВ ({600, 350, 50}= 1000 |
Функция SUMPRODUCT смогла перемножить все числа и произвести суммирование.
Средневзвешенное
Другой случай, когда полезно использовать СУММПРОИЗВ, - это когда вам нужно рассчитать средневзвешенное значение. Чаще всего это происходит при выполнении школьных заданий, поэтому давайте рассмотрим следующую таблицу.
Мы можем видеть, сколько викторин, тестов и домашних заданий соответствуют общей оценке, а также каково текущее среднее значение по каждому конкретному пункту. Тогда мы можем рассчитать общую оценку, написав
| 1 | = СУММПРОИЗВ (B2: B4; C2: C4) |
Наша функция снова умножает каждый элемент в массивах перед суммированием итога. Это работает так
| 123 | = СУММПРОИЗВ ({30%, 50%, 20%}, {73%, 90%, 95%})= СУММПРОИЗВ ({22%, 45%, 19%})= 86% |
Несколько столбцов
Еще одно место, где мы могли бы использовать SUMPRODUCT, - это еще больше столбцов, которые нужно умножить друг на друга. Рассмотрим пример, в котором нам нужно рассчитать объем в кусках пиломатериалов.
Вместо того, чтобы создавать вспомогательный столбец для расчета общей продажи для каждой строки, мы можем сделать это с помощью одной формулы. Наша формула будет
| 1 | = СУММПРОИЗВ (B2: B5; C2: C5; D2: D5) |
Первые элементы каждого массива будут умножаться друг на друга (например, 4 * 2 * 1 = 8). Затем 2-й (4 * 2 * 2 = 16) и 3-йrdи т. д. В целом, это произведет набор продуктов, которые выглядят как {8, 16, 16, 32). Тогда общий объем будет суммой этого массива, 72.
Один критерий
Хорошо, давайте добавим еще один уровень сложности. Мы видели, что SUMPRODUCT может обрабатывать массивы чисел, но как насчет того, чтобы проверить критерии? Ну, вы также можете создавать массивы для логических значений (логические значения - это значения ИСТИНА или ЛОЖЬ).
Например, возьмем базовый массив {1, 2, 3}. Давайте создадим соответствующий массив, который указывает, больше ли каждое число 1. Этот массив будет выглядеть как {FALSE, TRUE, TRUE}.
Это очень полезно в формулах, потому что мы можем легко преобразовать ИСТИНА / ЛОЖЬ в 1/0. Давайте рассмотрим пример.
Используя приведенную ниже таблицу, мы хотим рассчитать «Сколько единиц было продано в красном цвете?»
Мы можем сделать это с помощью этой формулы:
| 1 | = СУММПРОИЗВ (A2: A4; - (B2: B4 = "Красный")) |
"Подожди! Что там с двойным минусом? " ты говоришь. Помните, как я сказал, что мы можем преобразовать True / False в 1/0? Мы делаем это, заставляя компьютер выполнять математическую операцию. В этом случае мы говорим «возьмите отрицательное значение, а затем снова возьмите отрицательное». Записав это, наш массив изменится следующим образом:
| 123 | {Верно, Верно, Ложно}{-1, -1, 0}{1, 1, 0} |
Итак, возвращаясь к полной формуле СУММПРОИЗВ, она загружается в наши массивы, а затем умножается, как это
| 123 | = СУММПРОИЗВ ({100, 50, 10}, {1, 1, 0})= СУММПРОИЗВ ({100, 50, 0})= 150 |
Обратите внимание, как 3rd item стал 0, потому что все, что умножено на 0, становится нулем.
Несколько критериев
Мы можем загрузить в нашу функцию до 255 массивов, поэтому мы определенно можем загрузить больше критериев. Давайте посмотрим на эту большую таблицу, в которую мы добавили Месяц проданных товаров.
Если мы хотим знать, сколько товаров было продано красного цвета а также были в феврале месяце, мы могли бы написать нашу формулу как
| 1 | = СУММПРОИЗВ (A2: A4; - (B2: B4 = "Красный"), - (C2: C4 = "Фев")) |
Затем компьютер оценит наши массивы и умножит их. Мы уже рассмотрели, как массивы True / False превращаются в 1/0, поэтому я пока пропущу этот шаг.
| 123 | = СУММПРОИЗВ ({100, 50, 10}, {1, 1, 0}, {0, 1, 1})= СУММПРОИЗВ ({0, 50, 0})= 50 |
В нашем примере у нас была только одна строка, которая соответствовала всем критериям, но с реальными данными у вас могло быть несколько строк, которые вам нужно было сложить вместе.
Комплексные критерии
Хорошо, до этого момента вы могли не быть впечатлены, потому что все наши примеры могли быть выполнены с использованием других функций, таких как СУММЕСЛИ или СЧЁТЕСЛИ. Теперь мы собираемся сделать что-то, что другие функции не могу делать. Раньше в нашем столбце «Месяц» были фактические названия месяцев. Что, если бы вместо этого были даты?
Мы не можем сделать СУММЕСЛИ сейчас, потому что СУММЕСЛИ не может обрабатывать необходимые нам критерии. Однако SUMPRODUCT может справиться с манипулированием массивом и выполнением более глубокого теста. Мы уже работали с массивами, когда переводили True / False в 1/0. Мы собираемся манипулировать этим массивом с помощью функции МЕСЯЦ. Вот полная формула, которую мы собираемся использовать
| 1 | = СУММПРОИЗВ (A2: A4; - (B2: B4 = "Красный"), - (МЕСЯЦ (C2: C4) = 2)) |
Давайте посмотрим на 3rd массив более внимательно. Во-первых, наша формула извлечет номер месяца из каждой даты в C2: C4. Это даст нам {1, 2, 2}. Затем мы проверяем, равно ли это значение 2. Теперь наш массив выглядит как {False, True, True}. Мы снова делаем двойной минус, и у нас есть {0, 1, 1}. Теперь мы снова находимся в том же месте, что и в примере 3, и наша формула сможет сказать нам, что в феврале было продано 50 единиц, которые были красными.
Двойной минус против умножения
Если вы раньше видели использование функции СУММПРОИЗВ, возможно, вы видели немного другое обозначение. Вместо двойного минуса можно написать
| 1 | = СУММПРОИЗВ (A2: A4 * (B2: B4 = «Красный») * (МЕСЯЦ (C2: C4) = 2)) |
Формула по-прежнему будет работать так же, мы просто вручную сообщаем компьютеру, что хотим умножить массивы. SUMPRODUCT все равно собирался это сделать, поэтому в том, как работает математика, нет никаких изменений. Выполнение математической операции преобразует наши True / False в 1/0 того же самого. Итак, в чем разница?
В большинстве случаев это не имеет большого значения, и все сводится к предпочтениям пользователя. Хотя есть по крайней мере один случай, когда умножение необходимо.
Когда вы используете SUMPRODUCT, компьютер ожидает, что все аргументы (array1, array2 и т. Д.) Будут одного размера. Это означает, что у них одинаковое количество строк или столбцов. Однако вы можете выполнять так называемое вычисление двухмерного массива с помощью SUMPRODUCT, что мы увидим в следующем примере. Когда вы это сделаете, массивы будут разных размеров, поэтому нам нужно обойти проверку «все одинакового размера».
Два измерения
Во всех предыдущих примерах наши массивы двигались в одном направлении. SUMPRODUCT может работать в двух направлениях, как мы увидим в следующей таблице.
Вот наша таблица проданных единиц, но данные перегруппированы так, чтобы категории располагались вверху. Если мы хотим узнать, сколько предметов было красным и относилось к категории A, мы можем написать
| 1 | = СУММПРОИЗВ ((A2: A4 = «Красный») * (B1: C1 = «A») * B2: C4) |
Что здесь происходит?? Оказывается, мы собираемся размножаться в двух разных направлениях. Визуализировать это сложнее с помощью простого письменного предложения, поэтому у нас есть несколько изображений, которые нам помогут. Во-первых, наш критерий строки (красный?) Будет умножаться на каждую строку в массиве.
| 1 | = СУММПРОИЗВ ((A2: A4 = «КРАСНЫЙ») * B2: C4) |
Затем критерии столбца (это категория A?) Будут умножаться на каждый столбец.
| 1 | = СУММПРОИЗВ ((A2: A4 = «Красный») * (B1: C1 = «A») * B2: C4) |
После того, как оба этих критерия выполнят свою работу, останутся только 5 и 10. СУММПРОИЗВ даст нам в качестве нашего ответа общую сумму 15.
Помните, как мы говорили о том, что массивы должны быть одинакового размера, если вы не работаете с двумя измерениями? Частично это было правильно. Снова посмотрим на массивы, которые мы использовали в нашей формуле. В рост двух наших массивов одинаковы, а ширина двух наших массивов одинаковы. Итак, вам все равно нужно убедиться, что все будет правильно, но вы можете сделать это в разных измерениях.
Двухмерный и сложный
Часто нам представляются данные, которые не имеют наилучшего формата, подходящего для наших формул. Мы могли бы попытаться переставить его вручную, или мы можем быть умнее с нашими формулами. Рассмотрим следующую таблицу.
Здесь у нас есть смешанные данные по нашим товарам и продажам за каждый месяц. Как нам узнать, сколько товаров Боб продал за весь год?
Для этого мы будем использовать две дополнительные функции: SEARCH и ISNUMBER. Функция ПОИСК позволит нам искать наше ключевое слово «элементы» в ячейках заголовка. На выходе этой функции будет либо число, либо ошибка (если ключевое слово не найдено). Затем мы будем использовать ISNUMBER для преобразования что вывод в наши логические значения. Наша формула будет выглядеть, как показано ниже.
К настоящему времени вы должны быть хорошо знакомы с первым массивом. Будет создан результат вроде {0, 1, 0, 1}. Следующий массив критериев, о котором мы только что говорили. Он создаст номер для всех ячеек с «Items» в них и ошибку для остальных {5, # N / A !, 5, # N / A!}. ISNUMBER затем преобразует это в логическое значение {True, False, True, False}. Затем при умножении сохранятся только значения из первого и третьего столбца. После того, как все массивы умножатся друг на друга, единственные ненулевые числа, которые у нас будут, - это те, которые выделены здесь:
| 1 | = СУММПРОИЗВ ((A2: A5 = "Боб") * (ISNUMBER (ПОИСК ("Элементы"; B1: E1)) * B2: E5)) |
СУММПРОИЗВ затем суммирует их, и мы получим окончательный результат 29.
СУММПРОИЗВ или
Возникает много ситуаций, когда мы хотели бы иметь возможность суммировать значения, если в нашем столбце критериев есть одно значение ИЛИ другое значение. Вы можете сделать это в SUMPRODUCT, добавив два массива критериев друг против друга.
В этом примере мы хотим сложить единицы, проданные как за красный, так и за синий.
Наша формула будет выглядеть так
| 1 | = СУММПРОИЗВ (A2: A7; (B2: B7 = «Красный») + (B2: B7 = «Синий»)) |
Давайте посмотрим на массив критериев Red. В результате будет получен массив, который выглядит следующим образом: {1, 1, 0, 0, 0, 0}. Массив критериев Blue будет иметь вид {0, 0, 1, 0, 1, 0}. Когда вы сложите их вместе, новый массив будет выглядеть как {1, 1, 1, 0, 1, 0}. Мы видим, как два массива объединились в единый массив критериев. Затем функция умножит это на наш первый массив, и мы получим {100, 50, 10, 0, 75, 0}. Обратите внимание, что значения для зеленого были обнулены. Последний шаг СУММПРОИЗА - это сложение всех чисел вместе, чтобы получить решение 235.
Одно предостережение. Будьте осторожны, когда массивы критериев не исключают друг друга. В нашем примере значения в столбце B могут быть либо красным, либо синим, но мы знали, что оба эти значения никогда не могут быть одинаковыми. Подумайте, записали ли мы эту формулу:
| 1 | = СУММПРОИЗВ (A2: A7; (A2: A7> = 50) + (B2: B7 = «Синий»)) |
Наша цель - найти синие предметы, которые были проданы или были в количестве более 50. Однако эти условия не являются исключительными, поскольку в одной строке может быть как больше 50 в столбце A. а также будь синим. Это приведет к тому, что первый массив критериев будет выглядеть как {1, 1, 0, 1, 1, 0}, а второй массив критериев будет иметь вид {0, 0, 1, 0, 1, 0}. Их сложение дает {1, 1, 1, 1, 2, 0}. Вы видите, что у нас там сейчас 2? Если оставить в покое, СУММПРОИЗВ в конечном итоге удвоит значение в этой строке, изменив 75 на 150, и мы получим неправильный результат. Чтобы исправить это, мы помещаем проверку внешних критериев в наш массив, например:
| 1 | = СУММПРОИЗВ (A2: A7; - ((A2: A7> = 50) + (B2: B7 = "Синий")> 0)) |
Теперь, после того, как два внутренних массива критериев были сложены вместе, мы проверим, больше ли результат, чем 0. Это избавляет от 2, которые у нас были раньше, и вместо этого у нас будет такой массив, как {1, 1, 1 , 1, 1, 0}, что даст правильный результат.
СУММПРОИЗВ Точный
Большинство функций в Excel не чувствительны к регистру, но иногда нам нужно иметь возможность выполнять поиск с учетом регистра. Когда желаемый результат числовой, мы можем добиться этого, используя EXACT внутри функции СУММПРОИЗВ. Рассмотрим следующую таблицу:
Мы хотим найти оценку для пункта «ABC123». Обычно функция EXACT сравнивает два элемента и возвращает логический вывод, указывающий, являются ли эти два элемента точно такой же. Однако, поскольку мы находимся внутри СУММПРОИЗВ, наш компьютер будет знать, что мы имеем дело с массивами, и сможет сравнивать один элемент с каждым элементом в массиве. Наша формула будет выглядеть так
| 1 | = СУММПРОИЗВ (- ТОЧНЫЙ ("ABC123"; A2: A5), B2: B5) |
Затем функция EXACT проверит каждый элемент в A2: A5, чтобы убедиться, что он соответствует значению и регистру. В результате будет получен массив, который выглядит как {0, 1, 0, 0}. При умножении на B2: B5 массив становится {0, 2, 0, 0}. После окончательного суммирования получаем решение 2.
СУММПРОИЗВ в Google Таблицах
Функция СУММПРОИЗВ работает в Google Таблицах точно так же, как и в Excel:
Примеры SUMPRODUCT в VBA
Вы также можете использовать функцию СУММПРОИЗВ в VBA. Тип: application.worksheetfunction.sumproduct (массив1, массив2, массив3)
Выполнение следующих операторов VBA
| 1 | Диапазон ("B10") = Application.WorksheetFunction.SumProduct (Range ("A2: A7"), Range ("B2: B7")) |
даст следующие результаты
Для аргументов функции (array1 и т. Д.) Вы можете либо ввести их непосредственно в функцию, либо определить переменные, которые будут использоваться вместо них.
