Bendroji formulė
=SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"rng"),criteria,INDIRECT("'"&sheets&"'!"&"sumrng")))
Santrauka
Norėdami sąlygiškai susumuoti identiškus diapazonus, esančius atskiruose darbalapiuose, vienoje formulėje, galite naudoti funkciją SUMIF su INDIRECT, suvyniotu į SUMPRODUCT. Parodytame pavyzdyje formulė C9 yra:
=SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"D4:D5"),B9,INDIRECT("'"&sheets&"'!"&"E4:E5")))
Paaiškinimas
Kiekvieno iš trijų apdorojamų lapų duomenys atrodo taip:
Pirmiausia atkreipkite dėmesį, kad negalima naudoti SUMIF su „įprasta“ 3D nuoroda:
Sheet1:Sheet3!D4:D5
Tai yra standartinė „3D sintaksė“, bet jei bandysite ją naudoti su SUMIF, gausite #VALUE klaidą. Taigi, norėdami išspręsti šią problemą, galite naudoti pavadintą diapazoną „lapai“, kuriuose pateikiami visi norimi įtraukti lapai (darbalapio skirtukas). Tačiau norėdami sukurti nuorodas, kurias „Excel“ interpretuos teisingai, turime susieti lapų pavadinimus su diapazonais, su kuriais turime dirbti, ir tada naudoti NETIESIOGINĮ, kad „Excel“ juos teisingai atpažintų.
Be to, kadangi pavadintame diapazone „lakštai“ yra kelios vertės (ty jo masyvas), SUMIF rezultatas šiuo atveju taip pat yra masyvas (kartais vadinamas „gautuoju masyvu“). Taigi, mes naudojame SUMPRODUCT, kad jį tvarkytume, nes SUMPRODUCT turi galimybę natūraliai tvarkyti masyvus nereikalaudamas „Ctrl-Shift-Enter“, kaip ir daugelis kitų masyvo formulių.
Kitas būdas
Aukščiau pateiktas pavyzdys yra šiek tiek sudėtingas. Kitas būdas išspręsti šią problemą yra padaryti „vietinę“ sąlyginę sumą kiekviename lape, tada naudoti įprastą 3D sumą, kad kiekviena suvestinės skirtuko suma būtų pridėta.
Norėdami tai padaryti, prie kiekvieno lapo lapo, kuriame naudojamas kriterijų langelis suvestinės lape, pridėkite SUMIF formulę. Tada, kai pakeisite kriterijus, visos susietos SUMIF formulės bus atnaujintos.
