Обща формула
=SUMPRODUCT(data*(headers=A1))
Обобщение
За да сумирате стойности в колони чрез съвпадение на съответстващи заглавки на колони, можете да използвате формула, базирана на функцията SUMPRODUCT. В показания пример формулата в J5 е:
=SUMPRODUCT(data*(LEFT(headers)=J4))
където "data" е наименованият диапазон B5: G14, а "headers" е наименуваният диапазон B4: G4.
Формулата сумира колони, където заглавията започват с "а" и връщат 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 съответстват на колони, които започват с "а".
Вътре в 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))
