Vispārēja formula
=SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"rng"),criteria,INDIRECT("'"&sheets&"'!"&"sumrng")))
Kopsavilkums
Lai nosacīti summētu identiskos diapazonus, kas pastāv atsevišķās darblapās, vienā formulā, varat izmantot funkciju SUMIF ar INDIRECT, kas iesaiņots SUMPRODUCT. Parādītajā piemērā formula C9 ir:
=SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"D4:D5"),B9,INDIRECT("'"&sheets&"'!"&"E4:E5")))
Paskaidrojums
Dati par katru no trim apstrādājamām lapām izskatās šādi:
Pirmkārt, ņemiet vērā, ka jūs nevarat izmantot SUMIF ar "normālu" 3D atsauci, piemēram:
Sheet1:Sheet3!D4:D5
Šī ir standarta "3D sintakse", taču, mēģinot to izmantot ar SUMIF, tiks parādīta kļūda #VALUE. Tātad, lai novērstu šo problēmu, varat izmantot nosaukto diapazonu "lapas", kas uzskaita katru lapu (darblapas cilni), kuru vēlaties iekļaut. Tomēr, lai izveidotu atsauces, kuras Excel pareizi interpretēs, mums ir jāsavieno lapu nosaukumi diapazoniem, ar kuriem mums jāstrādā, un pēc tam jāizmanto INDIRECT, lai Excel tos pareizi atpazītu.
Tā kā nosauktajā diapazonā "lapas" ir vairākas vērtības (ti, tā masīvs), SUMIF rezultāts šajā gadījumā ir arī masīvs (dažreiz to sauc arī par "rezultātu masīvu"). Tātad, lai to apstrādātu, mēs izmantojam SUMPRODUCT, jo SUMPRODUCT spēj apstrādāt masīvus dabiski, neprasot taustiņu kombināciju Ctrl-Shift-Enter, tāpat kā daudzas citas masīvu formulas.
Vēl viens veids
Iepriekš minētais piemērs ir nedaudz sarežģīts. Vēl viens veids, kā tikt galā ar šo problēmu, ir veikt "vietējo" nosacīto summu katrā lapā, pēc tam izmantot parasto 3D summu, lai summētu katru vērtību cilnē Kopsavilkums.
Lai to izdarītu, pievienojiet SUMIF formulu katrai lapas lapai, kurā tiek izmantota kritēriju šūna kopsavilkuma lapā. Tad, kad mainīsit kritērijus, tiks atjauninātas visas saistītās SUMIF formulas.
