Загальна формула
=SUMPRODUCT(data*(range1=criteria1)*(range2=criteria2))
Резюме
Для підсумовування значень у відповідних стовпцях і рядках ви можете використовувати функцію SUMPRODUCT. У наведеному прикладі формула в J6 має вигляд:
=SUMPRODUCT(data*(codes=J4)*(days=J5))
де дані (C5: G14), дні (B5: B14) та коди (C4: G4) називаються діапазонами.
Пояснення
Функція SUMPRODUCT може обробляти масиви безпосередньо, не вимагаючи введення зсуву управління.
У цьому випадку ми множимо всі значення в названих даних діапазону на два вирази, які фільтрують значення, що не цікавлять. Перший вираз застосовує фільтр на основі кодів:
(codes=J4)
Оскільки J4 містить "A002", вираз створює масив TRUE FALSE значень, як це:
(FALSE,TRUE,FALSE,FALSE,FALSE)
Другий вираз фільтрує за день:
(days=J5)
Оскільки J4 містить "Wed", вираз створює масив TRUE FALSE значень, як це:
(FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
У програмі Excel значення TRUE FALSE автоматично примушуються до значень 1 і 0 за допомогою будь-якої математичної операції, тому операція множення примушує наведені вище масиви до одиниць і нулів і створює 2D-масив з тими ж розмірами, що й вихідні дані. Процес можна візуалізувати, як показано нижче:
Нарешті, SUMPRODUCT повертає суму всіх елементів у кінцевому масиві, 9.
Підрахувати замість суми
Якщо ви хочете підрахувати відповідні значення замість підсумовування, ви можете скоротити формулу до:
=SUMPRODUCT((codes=J4)*(days=J5)) // count only
Зверніть увагу, що ця кількість включатиме порожні клітинки.
Примітки
- Хоча в прикладі показано лише один відповідний стовпець, ця формула буде правильно підсумовувати кілька відповідних стовпців.
- Якщо вам потрібно лише зіставити стовпці (а не рядки), ви можете скористатися такою формулою.
- Щоб зіставити лише рядки, можна скористатися функцією COUNTIFS.
