Excel-kaava: Summa vastaavat sarakkeet -

Yleinen kaava

=SUMPRODUCT(data*(headers=A1))

Yhteenveto

Voit laskea yhteen sarakkeiden arvot vastaavia sarakeotsikoita käyttämällä SUMPRODUCT-funktioon perustuvaa kaavaa. Esitetyssä esimerkissä kaava kohdassa J5 on:

=SUMPRODUCT(data*(LEFT(headers)=J4))

missä "data" on nimetty alue B5: G14 ja "otsikot" on nimetty alue B4: G4.

Kaava summaa sarakkeet, joissa otsikot alkavat "a": lla ja palauttavat 201.

Selitys

Ytimessä tämä kaava perustuu SUMPRODUCT-funktioon summaamaan arvot vastaavissa sarakkeissa nimettyyn alueeseen "data" C5: G14. Jos kaikki tiedot toimitettaisiin SUMPRODUCTille yhdellä alueella, tulos olisi alueen kaikkien arvojen summa:

=SUMPRODUCT(data) // all data, returns 387

Jos haluat käyttää suodatinta vastaavilla sarakeotsikoilla - sarakkeet, joiden otsikot alkavat "A" -, käytämme VASEN-funktiota seuraavasti:

LEFT(headers)=J4) // must begin with "a"

Tämä lauseke palauttaa TOSI, jos sarakeotsikon alussa on "a", ja EPÄTOSI, jos ei. Tuloksena on taulukko:

(TRUE,TRUE,FALSE,FALSE,TRUE,FALSE)

Voit nähdä, että arvot 1,2 ja 5 vastaavat sarakkeita, jotka alkavat "a": lla.

SUMPRODUCTin sisällä tämä taulukko kerrotaan "tiedoilla". Lähetyksen takia tulos on kaksiulotteinen taulukko:

(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)

Jos visualisoimme tämän taulukon taulukossa, on helppo nähdä, että vain arvot sarakkeissa, jotka alkavat "a": lla, ovat selvinneet toiminnasta, kaikki muut sarakkeet ovat nollia. Toisin sanoen suodatin pitää kiinnostavat arvot ja "poistaa" loput:

A001	A002	A003
8	10	7
9	10	10
8	6	6
7	6	6
8	6	6
10	11	7
7	8	8
2	3	3
3	4	4
7	7	4

Vain yksi matriisi käsiteltäväksi SUMPRODUCT palauttaa kaikkien arvojen summan, 201.

Summa tarkka ottelu

Yllä oleva esimerkki osoittaa, kuinka yhteen lasketaan sarakkeet, jotka alkavat yhdellä tai useammalla erityisellä merkillä. Tarkkaan vastaavuuteen perustuvan sarakkeen yhteenvetona voit käyttää yksinkertaisempaa kaavaa: