Общая формула
=SUMPRODUCT(data*(headers=A1))
Резюме
Для суммирования значений в столбцах путем сопоставления заголовков столбцов можно использовать формулу, основанную на функции СУММПРОИЗВ. В показанном примере формула J5 имеет следующий вид:
=SUMPRODUCT(data*(LEFT(headers)=J4))
где «данные» - это именованный диапазон B5: G14, а «заголовки» - именованный диапазон B4: G4.
Формула суммирует столбцы, заголовки которых начинаются с «a», и возвращает 201.
Объяснение
По сути, эта формула использует функцию СУММПРОИЗВ для суммирования значений в совпадающих столбцах в названном диапазоне «данные» C5: G14. Если бы все данные были предоставлены СУММПРОИЗВ в одном диапазоне, результатом была бы сумма всех значений в диапазоне:
=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)
Если мы визуализируем этот массив в таблице, легко увидеть, что только значения в столбцах, начинающихся с «a», пережили операцию, все остальные столбцы равны нулю. Другими словами, фильтр сохраняет интересующие значения и «отменяет» остальные:
| 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 |
Имея только один массив для обработки, СУММПРОИЗВ возвращает сумму всех значений 201.
Сумма по точному совпадению
В приведенном выше примере показано, как суммировать столбцы, начинающиеся с одного или нескольких определенных символов. Чтобы суммировать столбец на основе точного совпадения, вы можете использовать более простую формулу, подобную этой:
=SUMPRODUCT(data*(headers=J4))
