Obecný vzorec
=SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"rng"),criteria,INDIRECT("'"&sheets&"'!"&"sumrng")))
souhrn
Chcete-li podmíněně sečíst shodné rozsahy, které existují v samostatných pracovních listech, vše v jednom vzorci, můžete použít funkci SUMIF s funkcí INDIRECT zabalenou do SUMPRODUCT. V zobrazeném příkladu je vzorec v C9:
=SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"D4:D5"),B9,INDIRECT("'"&sheets&"'!"&"E4:E5")))
Vysvětlení
Data na každém ze tří zpracovávaných listů vypadají takto:
Nejprve si povšimněte, že SUMIF nelze použít s „normálním“ 3D odkazem, jako je tento:
Sheet1:Sheet3!D4:D5
Jedná se o standardní „3D syntaxi“, ale pokud ji zkusíte použít se SUMIF, zobrazí se chyba #HODNOTA. Chcete-li tento problém vyřešit, můžete použít pojmenovaný rozsah "listů", který obsahuje seznam všech listů (karta listu), které chcete zahrnout. Abychom však vytvořili odkazy, které Excel bude správně interpretovat, musíme zřetězit názvy listů na rozsahy, se kterými musíme pracovat, a poté pomocí NEPŘÍMÉHO získat Excel, aby je správně rozpoznal.
Také proto, že pojmenovaný rozsah „listů“ obsahuje více hodnot (tj. Jeho pole), je výsledkem SUMIF v tomto případě také pole (někdy nazývané „výsledné pole“). K jeho zpracování tedy používáme SUMPRODUCT, protože SUMPRODUCT má schopnost nativně zpracovávat pole, aniž by vyžadoval Ctrl-Shift-Enter, stejně jako mnoho jiných vzorců pole.
Jiná cesta
Výše uvedený příklad je poněkud komplikovaný. Dalším způsobem, jak tento problém vyřešit, je provést „místní“ podmíněný součet na každém listu a poté použít běžný 3D součet k přidání každé hodnoty na kartě souhrnu.
Chcete-li to provést, přidejte vzorec SUMIF do každého listu listu, který používá buňku kritérií na souhrnném listu. Když pak změníte kritéria, aktualizují se všechny propojené vzorce SUMIF.
