Splošna formula
=SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"rng"),criteria,INDIRECT("'"&sheets&"'!"&"sumrng")))
Povzetek
Za pogojno seštevanje enakih obsegov, ki obstajajo v ločenih delovnih listih, vse v eni formuli, lahko uporabite funkcijo SUMIF z INDIRECT, ovito v SUMPRODUCT. V prikazanem primeru je formula v C9:
=SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"D4:D5"),B9,INDIRECT("'"&sheets&"'!"&"E4:E5")))
Pojasnilo
Podatki na vsakem od treh listov, ki se obdelujejo, so videti takole:
Najprej upoštevajte, da ne morete uporabljati SUMIF-jev z "običajnimi" referencami 3D, kot je ta:
Sheet1:Sheet3!D4:D5
To je standardna "sintaksa 3D", vendar če jo poskusite uporabiti s SUMIF, boste dobili napako #VALUE. Torej za rešitev te težave lahko uporabite imenovani obseg "listov", ki navaja vsak list (zavihek delovnega lista), ki ga želite vključiti. Če pa želimo zgraditi reference, ki jih bo Excel pravilno razlagal, moramo imena listov združiti v obsege, s katerimi moramo delati, in nato s pomočjo INDIRECT pridobiti, da jih Excel pravilno prepozna.
Ker tudi imenovani obseg "listi" vsebuje več vrednosti (tj. Njegovo matriko), je rezultat SUMIF v tem primeru tudi matrika (včasih imenovana tudi "rezultantna matrika). Torej za obdelavo uporabljamo SUMPRODUCT, saj ima sposobnost izvornega obvladovanja nizov, ne da bi potreboval Ctrl-Shift-Enter, kot mnoge druge formule matrike.
Še en način
Zgornji primer je nekoliko zapleten. Drug način za reševanje te težave je, da na vsakem listu naredite "lokalno" pogojno vsoto, nato pa z navadno 3D vsoto seštejete vsako vrednost na zavihku s povzetkom.
Če želite to narediti, dodajte formulo SUMIF na vsak list lista, ki uporablja celico s kriteriji na povzetku. Potem, ko spremenite merila, se posodobijo vse povezane formule SUMIF.
