Завантажте зразок робочої книги
Цей підручник демонструє, як користуватися Функція Excel SUMPRODUCT в Excel.
ОГЛЯД ОЗНАКУ ФУНКЦІЙ ПРОДУКТУ
Функція SUMPRODUCT Множить масиви чисел і підсумовує отриманий масив.
Щоб використовувати функцію SUMPRODUCT Excel Worksheet, виберіть клітинку та введіть:
(Зверніть увагу, як виглядають вхідні дані формул)
Функція SUMPRODUCT Синтаксис та входи:
| 1 | = SUMPRODUCT (масив1, масив2, масив3) |
масив1 - масиви чисел.
Що таке функція SUMPRODUCT?
Функція SUMPRODUCT є однією з найпотужніших функцій Excel. Ця назва може привести вас до думки, що вона призначена лише для базових математичних обчислень, але її можна використовувати для набагато більшого.
Масиви
SUMPRODUCT вимагає введення масивів.
Отже, по -перше, що ми маємо на увазі під "масивом"? Масив - це проста група елементів (наприклад, номери), розташованих у певному порядку, як і діапазон клітинок. Отже, якби у клітинках A1: A3 були цифри 1, 2, 3, Excel прочитав би це як масив {1,2,3}. Фактично, ви можете ввести {1,2,3} безпосередньо у формули Excel, і він розпізнає масив.
Нижче ми поговоримо докладніше про масиви, але спочатку розглянемо простий приклад.
Основи математики
Давайте розглянемо базовий приклад SUMPRODUCT, використовуючи його для розрахунку загальних продажів.
У нас є таблиця продуктів, і ми хочемо підрахувати загальний обсяг продажів. У вас виникає спокуса просто додати новий стовпець, взяти продану кількість * ціну, а потім підсумувати новий стовпець. Натомість, однак, ви можете просто скористатися функцією SUMPRODUCT. Пройдемося по формулі:
| 1 | = ПОВНІННЯ (A2: A4, B2: B4) |
Функція завантажить діапазони чисел у масиви, помноживши їх один на одного, а потім підсумує результати:
| 1234 | = ПІДПРИЄМСТВО ({100, 50, 10}, {6, 7, 5})= ПІДПРИЄМСТВО ({100 * 6, 50 * 7, 10 * 5})= ПІДПРИЄМСТВО ({600, 350, 50}= 1000 |
Функція SUMPRODUCT Funciton змогла перемножити всі числа і зробити підсумовування.
Середнє зважене
Інший випадок, коли корисно використовувати SUMPRODUCT, це коли вам потрібно обчислити середньозважене значення. Найчастіше це відбувається під час виконання шкільних завдань, тому розглянемо таку таблицю.
Ми можемо побачити, скільки варті вікторини, тести та домашнє завдання для загальної оцінки, а також який середній показник для кожного конкретного предмета. Тоді ми можемо підрахувати загальну оцінку, написавши
| 1 | = ПОВНІННЯ (В2: В4, С2: С4) |
Наша функція знову множить кожен елемент у масивах перед підсумовуванням загальної суми. Це працює так
| 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) і 3rdі т. д. В цілому це призведе до масиву продуктів, які мають вигляд {8, 16, 16, 32). Тоді загальний обсяг буде сумою цього масиву, 72.
Один критерій
Гаразд, додамо ще один рівень складності. Ми бачили, що SUMPRODUCT може обробляти масиви чисел, але як бути, якщо ми хочемо перевірити наявність критеріїв? Ну, ви також можете створити масиви для булевих значень (Булеві значення - це значення TRUE або FALSE).
Наприклад, візьмемо базовий масив {1, 2, 3}. Створимо відповідний масив, який вказує, чи кожне число більше 1. Цей масив буде виглядати так: {FALSE, TRUE, TRUE}.
Це надзвичайно корисно у формулах, тому що ми можемо легко перетворити TRUE / FALSE на 1 / 0. Давайте розглянемо приклад.
Використовуючи таблицю нижче, ми хочемо обчислити "Скільки проданих одиниць було червоним?"
Ми можемо зробити це за такою формулою:
| 1 | = ПІДПРИЄМСТВО (A2: A4, -(B2: B4 = "Червоний")) |
"Зачекай! Що там із символом подвійного мінуса? " ти кажеш. Пам’ятаєте, як я сказав, що ми можемо перетворити з True/False на 1/0? Ми робимо це, змушуючи комп'ютер виконувати математичну операцію. У цьому випадку ми говоримо «візьміть від’ємне значення, а потім знову прийміть негативне». Виписавши це, наш масив зміниться так:
| 123 | {True, True, False}{-1, -1, 0}{1, 1, 0} |
Отже, повертаючись до повної формули SUMPRODUCT, вона завантажиться в наші масиви, а потім множиться, як це
| 123 | = ПІДПРИЄМСТВО ({100, 50, 10}, {1, 1, 0})= ПІДПРИЄМСТВО ({100, 50, 0})= 150 |
Зверніть увагу, як 3rd елемент став 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 |
У нашому прикладі у нас був лише один рядок, який відповідав усім критеріям, але з реальними даними, можливо, у вас було кілька рядків, які вам потрібно додати разом.
Складні критерії
Гаразд, до цього моменту ви, можливо, не будете вражені, оскільки всі наші приклади могли бути зроблені за допомогою інших функцій, таких як SUMIF або COUNTIF. Тепер ми збираємося виконувати ці інші функції не може робити. Раніше у нашій колонці Місяць були фактичні назви місяців. Що, якби замість нього були дати?
Зараз ми не можемо зробити SUMIF, оскільки SUMIF не може відповідати нашим критеріям. Хоча SUMPRODUCT може впоратися з тим, що ми маніпулюємо масивом і проводимо більш глибоке тестування. Ми вже маніпулювали масивами, коли перевели True/False на 1/0. Ми збираємося маніпулювати цим масивом за допомогою функції MONTH. Ось повна формула, яку ми будемо використовувати
| 1 | = ПІДПРИЄМСТВО (A2: A4, -(B2: B4 = "Червоний"), -(МІСЯЦЬ (C2: C4) = 2)) |
Давайте подивимось на 3rd розташувати ближче. По -перше, наша формула збирається витягти номер місяця з кожної дати в C2: C4. Це дасть нам {1, 2, 2}. Далі ми перевіряємо, чи дорівнює це значення 2. Тепер наш масив виглядає так: {False, True, True}. Ми знову робимо подвійний мінус і маємо {0, 1, 1}. Тепер ми повернулися на схоже місце, яке було у прикладі 3, і наша формула зможе повідомити нам, що у лютому було продано 50 одиниць червоного кольору.
Подвійний мінус проти множення
Якщо ви бачили функцію SUMPRODUCT у використанні раніше, можливо, ви побачили дещо інші позначення. Замість того, щоб використовувати подвійний мінус, ви можете писати
| 1 | = ПІДВИСОК (A2: A4*(B2: B4 = "Червоний")*(МІСЯЦЬ (C2: C4) = 2)) |
Формула все одно працюватиме так само, ми просто вручну повідомляємо комп’ютеру, що хочемо помножити масиви. SUMPRODUCT все одно збирався це зробити, тому ніяких змін у тому, як працює математика, немає. Виконання математичної операції перетворює наше Істинне/Неправдиве на 1/0 те саме. Отже, чому різниця?
У більшості випадків це не має особливого значення, і це залежить від уподобань користувача. Хоча є принаймні один випадок, коли потрібно множення.
Коли ви використовуєте SUMPRODUCT, комп’ютер очікує, що всі аргументи (масив1, масив2 тощо) матимуть однаковий розмір. Це означає, що вони мають однакову кількість рядків або стовпців. Однак ви можете зробити те, що відомо як обчислення двовимірного масиву за допомогою SUMPRODUCT, що ми побачимо в наступному прикладі. Коли ви це робите, масиви мають різний розмір, тому нам потрібно обійти цю перевірку "все однакового розміру".
Два виміри
У всіх попередніх прикладах наші масиви йшли в одному напрямку. SUMPRODUCT може впоратися з двома напрямками, як ми побачимо в наступній таблиці.
Ось наша таблиця проданих одиниць, але дані впорядковано там, де категорії проходять у верхній частині. Якщо ми хочемо дізнатися, скільки предметів було червоного кольору та належало до категорії А, ми можемо написати
| 1 | = ПІДВИСОК ((A2: A4 = "Червоний")*(B1: C1 = "A")*B2: C4) |
Що тут відбувається ?? Виявляється, ми будемо множитися у двох різних напрямках. Візуалізувати це важче лише за допомогою написаного речення, тому у нас є кілька зображень, які нам допоможуть. По -перше, наші критерії рядків (чи це червоний?) Збираються множитися на кожен рядок масиву.
| 1 | = ПІДПРИЄМСТВО ((A2: A4 = "ЧЕРВОНИЙ")*B2: C4) |
Далі критерії стовпця (це категорія А?) Збираються множити кожен стовпець
| 1 | = ПІДВИСОК ((A2: A4 = "Червоний")*(B1: C1 = "A")*B2: C4) |
Після того, як обидва ці критерії виконали свою роботу, єдиними ненулями залишаються 5 і 10. SUMPRODUCT дасть нам у відповідь загальну загальну суму 15.
Пригадайте, як ми говорили про те, що масиви повинні мати однаковий розмір, якщо ви не робите двох вимірів? Це було частково правильно. Знову дивиться на масиви, які ми використовували у нашій формулі. The висота двох наших масивів однакові, а ширина з двох наших масивів однакові. Отже, вам все одно потрібно переконатися, що все буде правильно, але ви можете зробити це в різних вимірах.
Два виміри і комплекс
Багато разів нам представляють дані, які не найкращим чином підходять для наших формул. Ми можемо спробувати змінити його вручну, або ми можемо бути розумнішими з нашими формулами. Розглянемо таку таблицю.
Тут ми маємо дані про наші товари та продажі, змішані за кожен місяць. Як би ми дізналися, скільки предметів Боб продав за весь рік?
Для цього ми будемо використовувати дві додаткові функції: SEARCH та ISNUMBER. Функція SEARCH дозволить нам шукати наше ключове слово "items" у клітинках заголовка. Вихід цієї функції буде або числом, або помилкою (якщо ключове слово не знайдено). Потім ми будемо використовувати ISNUMBER для конвертації що вивести в наші булеві значення. Наша формула буде виглядати так, як показано нижче.
Ви вже повинні бути добре знайомі з першим масивом. Він створить такий результат, як {0, 1, 0, 1}. Наступний масив критеріїв, про який ми щойно говорили. Буде створено номер для всіх клітинок із "Елементами", а для інших - помилка {5, #Н/Д !, 5, #Н/Д!}. Потім ISNUMBER перетворює це на булеве значення {True, False, True, False}. Тоді, коли ми множимо, ми збережемо значення лише з першого та третього стовпців. Після того, як усі масиви множаться один проти одного, єдиними ненульовими числами, які ми матимемо, є ті, що виділені тут:
| 1 | = SUMPRODUCT ((A2: A5 = "Bob")*(ISNUMBER (SEARCH ("Items", B1: E1))*B2: E5)) |
Потім SUMPRODUCT додасть це все, і ми отримаємо кінцевий результат 29.
ПІДВИСОК Or
Виникає багато ситуацій, коли ми хотіли б підсумувати значення, якщо наш стовпець критеріїв має одне значення АБО інше значення. Ви можете досягти цього в SUMPRODUCT, додавши два масиви критеріїв один проти одного.
У цьому прикладі ми хочемо додати одиниці, продані як для червоного, так і для синього.
Наша формула буде виглядати так
| 1 | = ПІДПРИЄМСТВО (A2: A7, (B2: B7 = "Червоний")+(B2: B7 = "Синій")) |
Давайте розглянемо масив червоних критеріїв. Він створить масив, який виглядає так: {1, 1, 0, 0, 0, 0}. Масив синіх критеріїв матиме вигляд {0, 0, 1, 0, 1, 0}. Коли ви їх додасте, новий масив буде мати вигляд {1, 1, 1, 0, 1, 0}. Ми можемо побачити, як два масиви об'єдналися в єдиний масив критеріїв. Потім функція помножить це на наш перший масив, і ми отримаємо {100, 50, 10, 0, 75, 0}. Зверніть увагу, що значення зеленого були обнулені. Останній крок ПОВІДОМЛЕННЯ - це додавання всіх чисел разом, щоб досягти нашого рішення 235.
Тут одне застереження. Будьте обережні, коли масиви критеріїв не виключають один одного. У нашому прикладі значення у стовпці В можуть бути або червоними, або синіми, але ми знали, що вони ніколи не можуть бути обома. Подумайте, якби ми написали цю формулу:
| 1 | = ПІДВИСОК (A2: A7, (A2: A7> = 50)+(B2: B7 = "Синій")) |
Ми маємо намір знайти блакитні речі, які були продані або їх було більше 50. Однак ці умови не є винятковими, оскільки один стовпець у стовпці А може становити більше 50 та бути Синім. Це призведе до того, що перший масив критеріїв матиме вигляд {1, 1, 0, 1, 1, 0}, другий - {0, 0, 1, 0, 1, 0}. Додавши їх разом, ви отримаєте {1, 1, 1, 1, 2, 0}. Ви бачите, як у нас зараз там 2? Якщо залишити його в спокої, SUMPRODUCT в кінцевому підсумку подвоїть значення в цьому рядку, змінивши 75 на 150, і ми отримаємо неправильний результат. Щоб виправити це, ми розміщуємо зовнішню перевірку критеріїв у нашому масиві так:
| 1 | = ПІДПРИЄМСТВО (A2: A7, -((A2: A7> = 50)+(B2: B7 = "Синє")> 0)) |
Тепер, після того як два внутрішні масиви критеріїв були додані разом, ми перевіримо, чи результат більше 0. Це позбавить від 2 -х, які ми мали раніше, і замість цього у нас буде такий масив, як {1, 1, 1 , 1, 1, 0}, що дасть правильний результат.
ПОДГОТОВКА Точно
Більшість функцій в Excel не враховують регістр, але іноді нам потрібно мати можливість шукати з урахуванням чутливості до регістру. Коли бажаний результат є числовим, ми можемо досягти цього, використовуючи EXACT всередині функції SUMPRODUCT. Розглянемо таку таблицю:
Ми хочемо знайти оцінку для позиції "ABC123". Як правило, функція EXACT порівнює два елементи і повертає булевий результат, в якому вказується, чи є ці два елементи точно так само. Однак, оскільки ми всередині ВИХОДУ, наш комп’ютер буде знати, що ми маємо справу з масивами, і зможе порівнювати один елемент з кожним елементом у масиві. Наша формула буде виглядати так
| 1 | = ПІДПРИЄМСТВО (-ТОЧНО ("ABC123", A2: A5), B2: B5) |
Тоді функція EXACT перевірить кожен елемент у A2: A5, щоб перевірити, чи відповідає він значенню та регістру. Це створить масив, який виглядає як {0, 1, 0, 0}. Якщо помножити на B2: B5, масив стає {0, 2, 0, 0}. Після остаточного підсумовування ми отримуємо наше рішення 2.
SUMPRODUCT у Google Таблицях
Функція SUMPRODUCT працює точно так само в Google Таблицях, як і в Excel:
ВИВОДИ Приклади у VBA
Ви також можете використовувати функцію SUMPRODUCT у VBA. Тип: application.worksheetfunction.sumproduct (масив1, масив2, масив3)
Виконання наступних операторів VBA
| 1 | Діапазон ("B10") = Application.WorksheetFunction.SumProduct (Діапазон ("A2: A7"), Діапазон ("B2: B7")) |
дасть такі результати
Для аргументів функції (масив1 тощо) ви можете або ввести їх безпосередньо у функцію, або визначити змінні для використання замість них.
