перевершувати SUMPRODUCT функція

У Excel функцію SUMPRODUCT можна використовувати для множення двох або більше стовпців або масивів разом, а потім отримати суму добутків. Насправді SUMPRODUCT є корисною функцією, яка може допомогти підрахувати або підсумувати значення комірок за допомогою кількох критеріїв, таких як функція COUNTIFS або SUMIFS. У цій статті буде представлено синтаксис функції та деякі приклади для цієї функції SUMPRODUCT.

• Приклад 1: Основне використання функції SUMPRODUCT
• Приклад 2: Суми комірок з кількома критеріями з функцією SUMPRODUCT
• Приклад 3: Підрахуйте клітинки з кількома критеріями за допомогою функції SUMPRODUCT
• Приклад 4: Обчисліть середньозважене за допомогою функції SUMPRODUCT

 Синтаксис:

Синтаксис функції SUMPRODUCT в Excel:

 аргументи:

• array1: Вимагається. Перший масив або діапазон комірок, які потрібно помножити, а потім додати.
• array2: Необов’язково. Другий масив або діапазон комірок, які потрібно помножити, а потім додати.

Примітки:

• 1. Якщо у вашому діапазоні є нечислові значення, SUMPRODUCT розглядає їх як нулі.
• 2. Якщо масиви або діапазони мають різну кількість рядків і стовпців, функція SUMPRODUCT поверне значення #VALUE! помилка.
• 3. Якщо в масивах є логічні тести, вони створять значення TRUE і FALSE. У більшості випадків вам потрібно буде перетворити їх на 1 і 0, використовуючи подвійний одинарний оператор (-).
• 4. Функція SUMPRODUCT може обробляти до 255 аргументів у програмі Excel 2007 та пізніших версіях та лише 30 аргументів у попередніх версіях Excel.
• 5. SUMPRODUCT не підтримує символи підстановки.

 повернення:

Повертає результат помножених та підсумованих масивів.

 Приклади:

Приклад 1: Основне використання функції SUMPRODUCT

Основне використання SUMPRODUCT - це множення двох стовпців, а потім їх додавання, наприклад, у вас є такі дані знімка екрана, тепер ви хочете помножити замовлення та ціну одиниці, а потім підсумувати всі результати множення разом, щоб отримати Загальна сума.

Щоб вирішити це завдання, застосуйте формулу нижче:

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

А потім натисніть Що натомість? Створіть віртуальну версію себе у ключ, клітинки у стовпці B множаться на відповідні комірки в тому ж рядку стовпця C, і результати підсумовуються. Дивіться знімок екрана:

Приклад 2: Суми комірок з кількома критеріями з функцією SUMPRODUCT

За винятком функції SUMIFS для підсумовування значень комірок на основі кількох умов, функція SUMPRODUCT також може швидко та легко вирішити цю роботу.

Випадок 1: Сума значень комірок, якщо містить конкретний текст в іншому стовпці

Припустимо, у вас є таблиця даних, як показано на знімку екрана, тепер ви хочете розрахувати загальну ціну, яку має продукт KTE, а ім’я - Девід, як ви можете це зробити, використовуючи формулу SUMPRODUCT в Excel?

1. Будь ласка, введіть або скопіюйте будь-яку з наведених нижче формул у порожню комірку:

• Поради: У наведеній вище формулі:
• A2: A12 = "KTE": це перший діапазон_ критеріїв та критерії, за якими потрібно підсумувати
• B2: B12 = "Девід": це другий діапазон_ критеріїв та критерії, за якими ви хочете підсумувати.
• D2: D12: - діапазон суми, який потрібно підсумувати значення комірок.

2. Потім натисніть Що натомість? Створіть віртуальну версію себе у ключ, щоб отримати результат, як вам потрібно:

Випадок 2: Сума комірок з кількома критеріями з логікою АБО

Для підсумовування комірок на основі кількох критеріїв з логікою АБО слід використовувати символ плюса (+), щоб об’єднати кілька умов разом у функції SUMPRODUCT.

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

Будь ласка, застосуйте таку формулу до порожньої комірки:

А потім натисніть Що натомість? Створіть віртуальну версію себе у ключ, була розрахована загальна ціна продукту KTE та KTO, див. скріншот:

Випадок 3: Підсумовуйте клітинки з кількома критеріями з логікою OR та AND

У деяких ситуаціях може знадобитися одночасно підсумовувати клітинки як з OR, так і з AND. Функція SUMPRODUCE також може легко вирішити цю роботу.

Зірочка (*) використовується як оператор І.

Символ плюс (+) використовується як оператор АБО.

Подивимось приклад для підсумовування загальної ціни, яка продукція становить KTE та KTO, коли продаж перевищує 200.

Вам слід скопіювати або ввести формулу нижче:

Потім натисніть Що натомість? Створіть віртуальну версію себе у ключ, щоб отримати результат, як вам потрібно:

Приклад 3: Підрахуйте клітинки з кількома критеріями за допомогою функції SUMPRODUCT

Як правило, ця функція SUMPRODUCT також може допомогти нам підрахувати клітинки з кількома критеріями в Excel. Як і значення сум, вам просто потрібно видалити аргумент sum_range з формули.

Випадок 1: Підрахувати клітинки з кількома критеріями за допомогою логіки І

Тут я хочу порахувати клітинки, що товар - це КТЕ, а продаж перевищує 200. Тож, будь ласка, скористайтеся будь-якою з наведених нижче формул:

• Поради: У наведеній вище формулі:
• A2: A12 = "KTE": це перший діапазон_ критеріїв та критерії, за якими ви хочете розрахувати.
• B2: B12> 200.

А потім натисніть Що натомість? Створіть віртуальну версію себе у ключ, щоб отримати номер результату:

Випадок 2: підрахувати клітинки з кількома критеріями з логікою АБО

Для підрахунку кількості продуктів KTE та KTO у стовпці A, застосуйте цю формулу:

А потім натисніть Що натомість? Створіть віртуальну версію себе у ключ, щоб отримати номер товару KTE і KTO. Дивіться знімок екрана:

Випадок 3: Підрахувати клітинки з кількома критеріями за допомогою логіки OR та AND

Для підрахунку кількості продуктів KTE та KTO, продаж яких перевищує 200, слід застосувати таку формулу:

Потім натисніть Що натомість? Створіть віртуальну версію себе у ключ, щоб отримати потрібний номер результату:

Приклад 4: Обчисліть середньозважене за допомогою функції SUMPRODUCT

Зазвичай ми можемо швидко і легко обчислити середнє значення діапазону клітин. Але якщо ви хочете обчислити середнє значення списку значень з різними рівнями релевантності, це означає обчислити середньозважене. Немає прямої функції для його отримання в Excel. Але функція SUMPRODUCT може допомогти вам впоратися з нею.

Наприклад, у мене є таблиця статистичних оцінок студента, кожне завдання має різну вагу, як показано на знімку екрана нижче.

Для обчислення середньозваженого значення, будь ласка, використовуйте таку формулу:

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

А потім натисніть Що натомість? Створіть віртуальну версію себе у клавіша, середньозважене середнє було обчислено відразу, див. скріншот:

 Інші статті з функцією SUMPRODUCT: