Generisk formel
=SUMPRODUCT(data*(headers=A1))
Sammanfattning
För att summera värden i kolumner genom att matcha matchande kolumnrubriker kan du använda en formel baserad på SUMPRODUCT-funktionen. I exemplet som visas är formeln i J5:
=SUMPRODUCT(data*(LEFT(headers)=J4))
där "data" är det namngivna området B5: G14, och "rubriker" är det namngivna området B4: G4.
Formeln summerar kolumner där rubriker börjar med "a" och returnerar 201.
Förklaring
Kärnan bygger denna formel på SUMPRODUCT-funktionen för att summera värden i matchande kolumner i det angivna intervallet "data" C5: G14. Om all data lämnades till SUMPRODUCT i ett enda område, skulle resultatet bli summan av alla värden i intervallet:
=SUMPRODUCT(data) // all data, returns 387
För att tillämpa ett filter genom att matcha kolumnrubriker - kolumner med rubriker som börjar med "A" - använder vi VÄNSTER-funktionen så här:
LEFT(headers)=J4) // must begin with "a"
Detta uttryck returnerar SANT om en kolumnrubrik börjar med "a" och FALSE om inte. Resultatet är en matris:
(TRUE,TRUE,FALSE,FALSE,TRUE,FALSE)
Du kan se att värdena 1,2 och 5 motsvarar kolumner som börjar med "a".
Inuti SUMPRODUCT multipliceras denna matris med "data". På grund av sändningen är resultatet en tvådimensionell matris så här:
(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)
Om vi visualiserar denna matris i en tabell är det lätt att se att endast värden i kolumner som börjar med "a" har överlevt operationen, alla andra kolumner är noll. Med andra ord behåller filtret intressanta värden och "avbryter" resten:
| 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 |
Med bara en enda matris att bearbeta returnerar SUMPRODUCT summan av alla värden, 201.
Summa efter exakt matchning
Exemplet ovan visar hur man summerar kolumner som börjar med en eller flera specifika tecken. För att summera kolumnen baserat på en exakt matchning kan du använda en enklare formel så här:
=SUMPRODUCT(data*(headers=J4))
