Загальна формула
=SUMPRODUCT(data*(headers=A1))
Резюме
Для підсумовування значень у стовпцях шляхом збігу відповідних заголовків стовпців можна використовувати формулу, засновану на функції SUMPRODUCT. У наведеному прикладі формула в J5 має вигляд:
=SUMPRODUCT(data*(LEFT(headers)=J4))
де "дані" - іменований діапазон B5: G14, а "заголовки" - іменований діапазон B4: G4.
Формула підсумовує стовпці, де заголовки починаються на "a" і повертають 201.
Пояснення
В основі цієї формули лежить функція SUMPRODUCT для підсумовування значень у відповідних стовпцях у названому діапазоні "дані" C5: G14. Якби всі дані були надані SUMPRODUCT в одному діапазоні, результатом була б сума всіх значень у діапазоні:
=SUMPRODUCT(data) // all data, returns 387
Щоб застосувати фільтр, зіставляючи заголовки стовпців - стовпці з заголовками, що починаються на "A", ми використовуємо функцію LEFT наступним чином:
LEFT(headers)=J4) // must begin with "a"
Цей вираз повертає TRUE, якщо заголовок стовпця починається з "a", і FALSE, якщо ні. Результат - масив:
(TRUE,TRUE,FALSE,FALSE,TRUE,FALSE)
Ви можете бачити, що значення 1,2 та 5 відповідають стовпцям, які починаються на "a".
Усередині SUMPRODUCT цей масив множиться на "дані". Завдяки трансляції виходить двовимірний масив, такий:
(8,10,0,0,7,0;9,10,0,0,10,0;8,6,0,0,6,0;7,6,0,0,6,0;8,6,0,0,6,0;10,11,0,0,7,0;7,8,0,0,8,0;2,3,0,0,3,0;3,4,0,0,4,0;7,7,0,0,4,0)
Якщо ми візуалізуємо цей масив у таблиці, неважко помітити, що лише значення в стовпцях, що починаються з "а", пережили операцію, а всі інші стовпці дорівнюють нулю. Іншими словами, фільтр зберігає цікаві значення і "скасовує" решту:
| A001 | A002 | B001 | B002 | A003 | B003 |
|---|---|---|---|---|---|
| 8 | 10 | 0 | 0 | 7 | 0 |
| 9 | 10 | 0 | 0 | 10 | 0 |
| 8 | 6 | 0 | 0 | 6 | 0 |
| 7 | 6 | 0 | 0 | 6 | 0 |
| 8 | 6 | 0 | 0 | 6 | 0 |
| 10 | 11 | 0 | 0 | 7 | 0 |
| 7 | 8 | 0 | 0 | 8 | 0 |
| 2 | 3 | 0 | 0 | 3 | 0 |
| 3 | 4 | 0 | 0 | 4 | 0 |
| 7 | 7 | 0 | 0 | 4 | 0 |
Маючи лише один масив для обробки, SUMPRODUCT повертає суму всіх значень, 201.
Сума за точним збігом
У наведеному вище прикладі показано, як підсумовувати стовпці, які починаються з одного або декількох конкретних символів. Щоб підсумувати стовпець на основі точної відповідності, ви можете використовувати простішу формулу, як це:
=SUMPRODUCT(data*(headers=J4))
