Перейти к основному содержанию

Excel SUMPRODUCT функция

Автор: Сяоян Последнее изменение: 2019 июля 07 г.

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


 Синтаксис:

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

=SUMPRODUCT (array1, [array2], ...)

 Аргументы:

  • array1: Необходимые. Первый массив или диапазон ячеек, которые вы хотите умножить, а затем добавить.
  • array2: Необязательный. Второй массив или диапазон ячеек, которые вы хотите умножить, а затем добавить.

Ноты:

  • 1. Если в вашем диапазоне есть нечисловые значения, СУММПРОИЗВ рассматривает их как нули.
  • 2. Если массивы или диапазоны имеют разное количество строк и столбцов, функция СУММПРОИЗВ вернет # ЗНАЧ! ошибка.
  • 3. Если в массивах есть логические тесты, они будут создавать значения ИСТИНА и ЛОЖЬ. В большинстве случаев вам нужно преобразовать их в 1 и 0 с помощью двойного унарного оператора (-).
  • 4. Функция СУММПРОИЗВ может обрабатывать до 255 аргументов в Excel 2007 и более поздних версиях и только 30 аргументов в более ранних версиях Excel.
  • 5. SUMPRODUCT не поддерживает подстановочные знаки.

 Вернуть:

Возвращает результат умножения и суммирования массивов.


 Примеры:

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

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

Для решения этой задачи примените следующую формулу:

=SUMPRODUCT(B2:B7,C2:C7)

объяснение: Эта формула SUMPRODUCT(B2:B7,C2:C7) = B2*C2+B3*C3+B4*C4+B5*C5+B6*C6+B7*C7.

А затем нажмите Enter key, ячейки в столбце B умножаются на соответствующие ячейки в той же строке столбца C, и результаты суммируются. Смотрите скриншот:


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

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


Случай 1. Суммируйте значения ячеек, если они содержат определенный текст в другом столбце.

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

1. Введите или скопируйте любую из следующих формул в пустую ячейку:

=SUMPRODUCT(--(A2:A12="KTE"), --(B2:B12="David"), D2:D12)
=SUMPRODUCT((A2:A12="KTE")*(B2:B12="David")*D2:D12)
  • Советы: В приведенной выше формуле:
  • A2: A12 = "KTE": это первый диапазон критериев и критерии, на основе которых вы хотите произвести суммирование.
  • B2: B12 = "Дэвид": это второй диапазон_критерия и критерий, по которому вы хотите произвести суммирование.
  • D2: D12: это диапазон сумм, в котором вы хотите суммировать значения ячеек.

2. Затем нажмите Enter ключ для получения нужного вам результата:


Случай 2: Суммирование ячеек с несколькими критериями с логикой ИЛИ

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

Например, я хочу суммировать общую цену продукта KTE и KTO в столбце A, как показано ниже:

Вставьте следующую формулу в пустую ячейку:

=SUMPRODUCT((A2:A12="KTE")+(A2:A12="KTO"), C2:C12)

А затем нажмите Enter key, рассчитана общая стоимость товара KTE и KTO, см. снимок экрана:


Случай 3: Суммирование ячеек с несколькими критериями с логикой ИЛИ и И

В некоторых ситуациях вам может потребоваться суммировать ячейки с помощью логики ИЛИ и И одновременно. Функция SUMPRODUCE также может легко решить эту задачу.

Звездочка (*) используется как оператор И.

Знак плюс (+) используется как оператор ИЛИ.

Давайте посмотрим на пример, для суммирования общей цены, какой продукт составляет KTE и KTO, когда продажа превышает 200.

Вы должны скопировать или ввести следующую формулу:

=SUMPRODUCT((B2:B12>200)*((A2:A12="KTE")+(A2:A12="KTO"))*C2:C12)

Затем нажмите Enter ключ для получения нужного вам результата:


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

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


Случай 1. Подсчет ячеек с несколькими критериями с логикой И

Здесь я хочу подсчитать ячейки, в которых продукт является KTE, а объем продаж превышает 200. Поэтому, пожалуйста, используйте любую из следующих формул:

=SUMPRODUCT(--(A2:A12="KTE"), --(B2:B12>200))
=SUMPRODUCT((A2:A12="KTE")*(B2:B12>200))
  • Советы: В приведенной выше формуле:
  • A2: A12 = "KTE": - это первый диапазон критериев и критерии, на основе которых вы хотите вести подсчет.
  • B2: B12> 200: это второй диапазон_критерия и критерий, на основе которого вы хотите вести подсчет.

А затем нажмите Enter ключ для получения номера результата:


Случай 2: подсчет ячеек с несколькими критериями с логикой ИЛИ

Для подсчета количества продуктов KTE и KTO в столбце A используйте следующую формулу:

=SUMPRODUCT((A2:A12="KTE")+(A2:A12="KTO"))

А затем нажмите Enter ключ для получения номера товара КТЕ и КТО. Смотрите скриншот:


Случай 3: подсчет ячеек с несколькими критериями с помощью логики ИЛИ и И

Чтобы подсчитать количество товаров КТЕ и КТО, продажи которых превышают 200, необходимо применить следующую формулу:

=SUMPRODUCT((B2:B12>200)*((A2:A12="KTE")+(A2:A12="KTO")))

Затем нажмите на Enter ключ, чтобы получить нужный вам результат:


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

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

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

Для расчета средневзвешенного значения используйте следующую формулу:

=SUMPRODUCT(B2:B6, C2:C6) / SUM(C2:C6)

объяснение: Эта формула: SUMPRODUCT(B2:B6, C2:C6) / SUM(C2:C6) = (B2*C2+B3*C3+B4*C4+B5*C5+B6*C6)/(C2+C3+C4+C5+C6)

А затем нажмите Enter key, средневзвешенное значение было рассчитано сразу, см. снимок экрана:


 Другие статьи с функцией СУММПРОИЗВ:

  • Подсчитайте конкретное значение на нескольких листах
  • Предположим, у меня есть несколько листов, содержащих следующие данные, и теперь я хочу получить количество вхождений определенного значения «Excel» из этих листов. Как я могу подсчитать конкретные значения на нескольких листах?
  • Рассчитать средневзвешенное значение в Excel
  • Например, у вас есть список покупок с ценами, весом и суммой. Вы можете легко рассчитать среднюю цену с помощью функции СРЕДНИЙ в Excel. А что если средневзвешенная цена? В этой статье я представлю метод расчета средневзвешенного значения, а также метод расчета средневзвешенного значения при соблюдении определенных критериев в Excel.

Лучшие инструменты для работы в офисе

Kutools for Excel - поможет вам выделиться из толпы

🤖 Kutools AI Помощник: Революционный анализ данных на основе: Интеллектуальное исполнение   |  Генерировать код  |  Создание пользовательских формул  |  Анализ данных и создание диаграмм  |  Вызов функций Kutools...
Популярные опции: Найдите, выделите или определите дубликаты  |  Удалить пустые строки  |  Объедините столбцы или ячейки без потери данных  |  Раунд без формулы ...
Супер ВПросмотр: Несколько критериев  |  Множественное значение  |  На нескольких листах  |  Нечеткий поиск...
Адв. Выпадающий список: Простой раскрывающийся список  |  Зависимый раскрывающийся список  |  Выпадающий список с множественным выбором...
Менеджер столбцов: Добавить определенное количество столбцов  |  Переместить столбцы  |  Переключить статус видимости скрытых столбцов  Сравнить столбцы с Выберите одинаковые и разные ячейки ...
Рекомендуемые функции: Сетка Фокус  |  Просмотр дизайна  |  Большой Формулный Бар  |  Менеджер книг и листов | Библиотека ресурсов (Авто текст)  |  Выбор даты  |  Комбинировать листы  |  Шифровать/дешифровать ячейки  |  Отправлять электронные письма по списку  |  Суперфильтр  |  Специальный фильтр (фильтровать жирным шрифтом/курсивом/зачеркиванием...) ...
15 лучших наборов инструментов12 Текст Инструменты (Добавить текст, Удалить символы ...)  |  50+ График Тип (Диаграмма Ганта ...)  |  40+ Практических Формулы (Рассчитать возраст по дню рождения ...)  |  19 Вносимые Инструменты (Вставить QR-код, Вставить изображение из пути ...)  |  12 Конверсия Инструменты (Числа в слова, Конверсия валюты ...)  |  7 Слияние и разделение Инструменты (Расширенные ряды комбинирования, Разделить ячейки Excel ...)  |  ... и более

Kutools для Excel может похвастаться более чем 300 функциями, Гарантия того, что то, что вам нужно, находится на расстоянии одного клика...

Описание


Вкладка Office - включение чтения и редактирования с вкладками в Microsoft Office (включая Excel)

  • Одна секунда для переключения между десятками открытых документов!
  • Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
  • Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
  • Добавляет эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations