Як користуватися функцією Excel SUMPRODUCT -

Зміст

Резюме

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

Призначення

Помножте, а потім підсумуйте масиви

Повернене значення

Результат множених та підсумованих масивів

Синтаксис

= SUMPRODUCT (масив1, (масив2),…)

Аргументи

  • array1 - перший масив або діапазон, який потрібно помножити, а потім додати.
  • array2 - (необов’язково) Другий масив або діапазон для множення, а потім додавання.

Версія

Excel 2003

Примітки щодо використання

Функція SUMPRODUCT працює з масивами, але для введення не потрібен звичайний синтаксис масиву (Ctrl + Shift + Enter). Призначення функції SUMPRODUCT полягає у множенні, а потім підсумовуванні масивів. Якщо надано лише один масив, SUMPRODUCT просто підсумує елементи в масиві. Можна поставити до 30 масивів.

Коли ви вперше стикаєтесь з SUMPRODUCT, це може здатися нудним, складним і навіть безглуздим. Але SUMPRODUCT - це надзвичайно універсальна функція, яка багато в чому застосовується. Оскільки він буде обробляти масиви витончено і без нарікань, ви можете використовувати його для обробки діапазонів комірок розумними, елегантними способами (див. Посилання на приклади формул на цій сторінці).

Щоб проілюструвати, як працює SUMPRODUCT, ось кілька типових прикладів.

ПІДПРИЄМСТВО для умовних сум і відліків

Припустимо, у вас є дані про замовлення в A2: B6, а у стовпці A - стан, у стовпці B - продажі:

A B
1 Держава Продажі
2 UT 75
3 CO 100
4 TX 125
5 CO 125
6 TX 150

Використовуючи SUMPRODUCT, ви можете підрахувати загальний обсяг продажів у Техасі ("TX") за такою формулою:

=SUMPRODUCT(--(A2:A6="TX"))

І ви можете підсумувати загальний обсяг продажів для Техасу ("TX") за цією формулою:

=SUMPRODUCT(--(A2:A6="TX"),B2:B6)

Примітка: Нехай вас не бентежить подвійний негатив. Це загальний трюк, який використовується в більш вдосконалених формулах Excel для примушення значень TRUE та FALSE до 1 і 0. Дивіться далі нижче …

Для наведеного вище прикладу суми, ось віртуальне представлення двох масивів, які вперше були оброблені SUMPRODUCT:

масив1 масив2
ПОМИЛКОВИЙ 75
ПОМИЛКОВИЙ 100
ІСТИНА 125
ПОМИЛКОВИЙ 125
ІСТИНА 150

Кожен масив має 5 елементів. Перший масив містить значення TRUE / FALSE, які є результатом виразу A2: A6 = "TX", а другий масив - вміст B2: B6. Кожен елемент у першому масиві буде помножений на відповідний елемент у другому масиві. Однак у поточному стані результат SUMPRODUCT буде нульовим, оскільки значення TRUE та FALSE будуть розглядатися як нуль. Нам потрібні елементи в масиві1, щоб вони були числовими - їх потрібно "примусити" на одиниці та нулі. Тут виникає подвійний негатив.

Використовуючи подвійний негатив - (подвійний одинарний, для ваших технічних типів) ми можемо примусити TRUE / FALSE до числових значень одиниці та нуля, як показано у віртуальному поданні нижче. Останній стовпець "Товар" представляє результат множення двох масивів разом. Підсумований результат, 275, є значенням, яке повертає SUMPRODUCT.

масив1 масив2 Продукт
0 * 75 = 0
0 * 100 = 0
1 * 125 = 125
0 * 125 = 0
1 * 150 = 150
Сума 275

Використовуючи синтаксис фігурних дужок для масивів, приклад виглядає так після примусу:

=SUMPRODUCT((0,0,1,0,1),(75,100,125,125,150))

і ось так після множення:

=SUMPRODUCT((0,0,125,0,150))

Цей приклад розширює наведені вище ідеї з більшою деталізацією.

SUMPRODUCT з іншими функціями

SUMPRODUCT може використовувати інші функції безпосередньо. Ви можете побачити SUMPRODUCT, який використовується з функцією LEN для підрахунку загальної кількості символів у діапазоні, або з такими функціями, як ISBLANK, ISTEXT тощо. Це, як правило, не є функціями масиву, але коли їм надається діапазон, вони створюють "масив результатів". Оскільки SUMPRODUCT побудований для роботи з масивами, він може здійснювати обчислення масивів безпосередньо. Це може бути хорошим способом заощадити місце на робочому аркуші, усунувши необхідність у стовпці "помічник".

Наприклад, припустимо, що у вас 10 різних текстових значень в А1: А10, і ви хочете порахувати загальну кількість символів для всіх 10 значень. Ви можете додати допоміжний стовпець у стовпець B, який використовує цю формулу: LEN (A1) для обчислення символів у кожній комірці. Тоді за допомогою SUM можна скласти всі 10 чисел. Однак, використовуючи SUMPRODUCT, ви можете написати таку формулу:

=SUMPRODUCT(LEN(A1:A10))

При використанні з діапазоном, як A1: A10, LEN повертає масив із 10 значень. Тоді SUMPRODUCT просто підсумовує всі значення і повертає результат, без допоміжного стовпця.

Нижче наведено приклади багатьох інших способів використання SUMPRODUCT.

Примітки:

  1. SUMPRODUCT розглядає нечислові елементи в масивах як нулі.
  2. Аргументи масиву повинні бути однакового розміру. В іншому випадку SUMPRODUCT генерує #VALUE! значення помилки.
  3. Логічні тести всередині масивів створять значення TRUE і FALSE. У більшості випадків вам потрібно буде примусити їх до 1 і 0.
  4. SUMPRODUCT часто може використовувати безпосередньо результат інших функцій (див. Приклади формул нижче)

Цікаві статті...