Bendroji formulė
=SUMPRODUCT(data*(headers=A1))
Santrauka
Norėdami susumuoti vertes stulpeliuose suderindami atitinkamas stulpelių antraštes, galite naudoti formulę, pagrįstą funkcija SUMPRODUCT. Parodytame pavyzdyje formulė J5 yra:
=SUMPRODUCT(data*(LEFT(headers)=J4))
kur „duomenys“ yra pavadintas diapazonas B5: G14, o „antraštės“ - pavadintas diapazonas B4: G4.
Formulė sumuoja stulpelius, kuriuose antraštės prasideda „a“ ir grąžina 201.
Paaiškinimas
Iš esmės ši formulė remiasi funkcija SUMPRODUCT, kad sumos reikšmės atitiktų stulpelių pavadintame diapazone „duomenys“ C5: G14. Jei visi duomenys būtų pateikti „SUMPRODUCT“ viename diapazone, rezultatas būtų visų diapazono verčių suma:
=SUMPRODUCT(data) // all data, returns 387
Norėdami pritaikyti filtrą suderindami stulpelių antraštes - stulpelius su antraštėmis, kurie prasideda „A“, mes naudojame kairę funkciją taip:
LEFT(headers)=J4) // must begin with "a"
Ši išraiška grąžina TRUE, jei stulpelio antraštė prasideda „a“, o FALSE - jei ne. Rezultatas yra masyvas:
(TRUE,TRUE,FALSE,FALSE,TRUE,FALSE)
Galite pamatyti, kad reikšmės 1,2 ir 5 atitinka stulpelius, prasidedančius „a“.
SUMPRODUCT viduje šis masyvas padauginamas iš „duomenų“. Dėl transliacijos rezultatas yra toks dviejų matmenų masyvas:
(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)
Jei vizualizuojame šią masyvą lentelėje, lengva pastebėti, kad operacijoje išliko tik reikšmės stulpeliuose, prasidedančiuose „a“, visi kiti stulpeliai yra lygūs nuliui. Kitaip tariant, filtras išlaiko dominančias vertes, o likusias „panaikina“:
| 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 |
Apdorojant tik vieną masyvą, SUMPRODUCT pateikia visų reikšmių sumą 201.
Suma pagal tikslią atitikmenį
Aukščiau pateiktame pavyzdyje parodyta, kaip susumuoti stulpelius, prasidedančius vienu ar daugiau konkrečių simbolių. Apibendrinant stulpelį pagal tikslią atitiktį, galite naudoti tokią paprastesnę formulę:
=SUMPRODUCT(data*(headers=J4))
