Generisk formel
=SUMPRODUCT(data*(headers=A1))
Resumé
For at opsummere værdier i kolonner ved at matche matchende kolonneoverskrifter kan du bruge en formel baseret på SUMPRODUCT-funktionen. I det viste eksempel er formlen i J5:
=SUMPRODUCT(data*(LEFT(headers)=J4))
hvor "data" er det navngivne område B5: G14, og "overskrifter" er det navngivne område B4: G4.
Formlen summerer kolonner, hvor overskrifter begynder med "a" og returnerer 201.
Forklaring
I kernen er denne formel afhængig af SUMPRODUCT-funktionen til at summere værdier i matchende kolonner i det navngivne interval "data" C5: G14. Hvis alle data blev leveret til SUMPRODUCT i et enkelt område, ville resultatet være summen af alle værdier i området:
=SUMPRODUCT(data) // all data, returns 387
For at anvende et filter ved at matche kolonneoverskrifter - kolonner med overskrifter, der begynder med "A" - bruger vi VENSTRE-funktionen som denne:
LEFT(headers)=J4) // must begin with "a"
Dette udtryk returnerer SAND, hvis en kolonneoverskrift begynder med "a", og FALSK hvis ikke. Resultatet er en matrix:
(TRUE,TRUE,FALSE,FALSE,TRUE,FALSE)
Du kan se, at værdierne 1,2 og 5 svarer til kolonner, der begynder med "a".
Inde i SUMPRODUCT ganges denne matrix med "data". På grund af udsendelse er resultatet et todimensionalt array som dette:
(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)
Hvis vi visualiserer denne matrix i en tabel, er det let at se, at kun værdier i kolonner, der begynder med "a", har overlevet operationen, alle andre kolonner er nul. Med andre ord holder filteret værdier af interesse og "annullerer" 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 kun et enkelt array at behandle returnerer SUMPRODUCT summen af alle værdier, 201.
Sum efter eksakt match
Eksemplet ovenfor viser, hvordan man summerer kolonner, der begynder med et eller flere specifikke tegn. For at opsummere kolonne baseret på et nøjagtigt match kan du bruge en enklere formel som denne:
=SUMPRODUCT(data*(headers=J4))
