Sum Cells, які падають між двома значеннями
Огляд SUMPRODUCT
Функція SUMPRODUCT в Excel є дуже універсальною функцією, яка дасть різні результати залежно від способу введення аргументів функції.
Як правило, як випливає з назви, SUMPRODUCT помножує елементи одного або декількох масивів, щоб одержати свій продукт, а потім доповнює або об'єднує разом сукупні продукти.
Змінюючи синтаксис функції, він може бути використаний для підрахунку суми лише даних у клітинах, що відповідають певним критеріям.
Починаючи з Excel 2007, програма містила дві функції - SUMIF та SUMIFS, які сума даних в комірках, які відповідають одному або декільком встановленим критеріям.
Проте іноді SUMPRODUCT простіше працювати, коли йдеться про пошук кількох умов, що стосуються того самого діапазону, як показано на зображенні вище.
SUMPRODUCT Функція синтаксису для Sum Cells
Синтаксис, який використовується для отримання SUMPRODUCT для обчислення даних у клітинах, які відповідають певним умовам, є:
= SUMPRODUCT (умова 1 * умова 2 * масив)
condition1, condition2 - умови, які необхідно виконати, перш ніж функція знайде продукт масиву.
масив - сусідній діапазон клітин
Приклад: підсумовування даних у клітинах, які відповідають декількома умовами
Приклад на зображенні вище додає дані в клітинках діапазону від D1 до E6, які містять від 25 до 75.
Введення функції SUMPRODUCT
Оскільки цей приклад використовує неправильну форму функції SUMPRODUCT, діалогове вікно функції не може використовуватися для введення функції та її аргументів. Замість цього функція повинна бути введена вручну в клітинку робочого листа.
- Клацніть на комірці B7 у робочому аркуші, щоб зробити його активною коміркою;
- Введіть наступну формулу в комірку B7: = SUMPRODUCT (($ A $ 2: $ B $ 6> 25) * ($ A $ 2: $ B $ 6 <75) * (A2: B6))
- Відповідь 250 повинен з'явитися в клітині B7
- Відповідь була отримана шляхом додавання п'яти чисел у діапазоні (40, 45, 50, 55 і 60), які становлять від 25 до 75. Загалом 250
Розбиваючи форму SUMPRODUCT
Коли умови для аргументів використовуються, SUMPRODUCT оцінює кожен елемент масиву за умови і повертає логічне значення (TRUE або FALSE).
Для цілей розрахунків Excel присвоює значення 1 для тих елементів масиву, які ІСТИНА (відповідають умовам) і значенням 0 для елементів масиву, які НЕБЕЗПЕКА (не відповідають умовам).
Наприклад, номер 40:
- ІСТИНА для першої умови, тому значення 1 призначається в першому масиві;
- ІСТИНА для другої умови так, що значення 1 призначається у другому масиві.
номер 15:
- FALSE для першої умови, тобто значення 0 призначається в першому масиві;
- ІСТИНА для другої умови так, що значення 1 призначається у другому масиві.
Відповідні і нулі в кожному масиві множиться разом:
- Для числа 40 - у нас 1 x 1 повертаємо значення 1;
- Для числа 15 - ми маємо 0 x 1, повертаємо значення 0.
Продовжити читання Нижче
02 з 02Помноження одиниць і нулів на діапазон
Потім ці і нулі помножуються на числа в діапазоні A2: B6.
Це зроблено, щоб дати нам числа, які будуть підсумовані функцією.
Це працює, оскільки:
- 1 раз будь-яке число дорівнює початковому номеру
- 0 разів будь-яке число дорівнює 0
Отже, ми закінчуємо:
- 1 * 40 = 400 * 15 = 00 * 22 = 01 * 45 = 451 * 50 = 501 * 55 = 550 * 25 = 00 * 75 = 01 * 60 = 600 * 100 = 0
Підведення підсумків
SUMPRODUCT потім підсумовує вищезазначені результати, щоб знайти відповідь.
40 + 0 + 0 + 45 + 50 + 55 + 0 + 0 + 60 + 0 = 250
