Excel formula: kolonnu summa atbilst

Vispārēja formula

=SUMPRODUCT(data*(headers=A1))

Kopsavilkums

Lai summētu vērtības kolonnās, saskaņojot atbilstošās kolonnu galvenes, varat izmantot formulu, kuras pamatā ir funkcija SUMPRODUCT. Parādītajā piemērā formula J5 ir:

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

kur "dati" ir nosauktais diapazons B5: G14 un "galvenes" ir nosauktais diapazons B4: G4.

Formula summē kolonnas, kur galvenes sākas ar "a" un atgriež 201.

Paskaidrojums

Būtībā šī formula balstās uz funkciju SUMPRODUCT, lai summētu vērtības atbilstošās kolonnās nosauktajā diapazonā "dati" C5: G14. Ja visi dati SUMPRODUCT tiktu iesniegti vienā diapazonā, rezultāts būtu visu diapazona vērtību summa:

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

Lai lietotu filtru, saskaņojot kolonnu galvenes - kolonnas ar galvenēm, kas sākas ar “A”, mēs izmantojam LEFT funkciju šādi:

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

Šī izteiksme atgriež vērtību TRUE, ja kolonnas galvene sākas ar "a", un FALSE, ja nē. Rezultāts ir masīvs:

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

Var redzēt, ka vērtības 1,2 un 5 atbilst kolonnām, kas sākas ar “a”.

SUMPRODUCT iekšpusē šis masīvs tiek reizināts ar "datiem". Apraides dēļ rezultāts ir šāds divdimensiju masīvs:

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

Ja mēs vizualizējam šo masīvu tabulā, ir viegli redzēt, ka operācijā ir saglabājušās tikai vērtības kolonnās, kas sākas ar "a", un visas pārējās kolonnas ir nulle. Citiem vārdiem sakot, filtrs saglabā interesējošās vērtības un "atceļ" pārējo:

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

Apstrādājot tikai vienu masīvu, SUMPRODUCT atgriež visu vērtību summu 201.

Summa pēc precīzas atbilstības

Iepriekš minētajā piemērā parādīts, kā summēt kolonnas, kas sākas ar vienu vai vairākām konkrētām rakstzīmēm. Apkopojot kolonnu, pamatojoties uz precīzu atbilstību, varat izmantot šādu vienkāršāku formulu: