Yleinen kaava
=SUMPRODUCT(--(range1=criteria),range2)
Yhteenveto
Sarakkeiden yhteenlaskeminen tai välisumma vierekkäisten sarakkeiden ehtojen perusteella voit käyttää kaavaa, joka perustuu SUMPRODUCT-funktioon. Esitetyssä esimerkissä kaava kohdassa J5 on:
=SUMPRODUCT(--($B5:$H5=J$4),$C5:$I5)
Selitys
Ytimessä tämä kaava käyttää SUMPRODUCT-arvoa kertomaan sitten kahden matriisin: matriisi1 ja matriisi2 tulot . Ensimmäinen taulukko, taulukko1, on asetettu toimimaan "suodattimena" sallimaan vain kriteerit täyttävät arvot.
Matriisi1 käyttää aluetta, joka alkaa ensimmäisestä sarakkeesta ja sisältää arvot, joiden on läpäistävä ehdot. Nämä "kriteeriarvot" sijaitsevat sarakkeessa "data-arvojen" vasemmalla puolella ja välittömästi niiden vieressä.
Kriteerejä käytetään yksinkertaisena testinä, joka luo TOSI- ja EPÄTOSI-arvot:
--($B5:$H5=J$4)
Tämä kaavan bitti "testaa" ensimmäisen taulukon jokaisen arvon käyttämällä annettuja ehtoja ja käyttää sitten kaksoisnegatiivista (-) pakottaakseen saadut TOSI- ja EPÄTOSI-arvot 1- ja 0-arvoiksi. Tulos näyttää tältä:
(1,0,0,0,1,0,1)
Huomaa, että 1: t vastaavat sarakkeita 1,5 ja 7, jotka täyttävät "A": n kriteerit.
Varten matriisi2 sisällä SUMPRODUCT, käytämme alueella, joka on "siirtynyt" yhden sarakkeen oikealle. Tämä alue alkaa ensimmäisestä sarakkeesta, joka sisältää yhteenlasketut arvot, ja päättyy viimeiseen sarakkeeseen, joka sisältää yhteenlasketut arvot.
Joten J5: n esimerkkikaavassa, kun taulukot on täytetty, meillä on:
=SUMPRODUCT((1,0,0,0,1,0,1),(1,"B",1,"A",1,"A",1))
Koska SUMPRODUCT on ohjelmoitu nimenomaan jättämään huomiotta virheet, jotka johtuvat tekstiarvojen kertomisesta, lopullinen taulukko näyttää tältä:
(1,0,0,0,1,0,1)
Ainoat arvot, jotka "selviävät" kertolaskuista, ovat ne, jotka vastaavat matriisin1 sisällä olevia 1-arvoja . Voit ajatella matriisin1 logiikkaa "suodattamalla" matriisin2 arvot .
