Stáhněte si ukázkový sešit
Tento tutoriál předvede, jak pomocí funkcí SUMPRODUCT a SUMIFS sčítat data, která splňují určitá kritéria ve více listech v Excelu a Google Sheets.
Pravidelný součet ve více listech
Někdy mohou vaše data zahrnovat několik listů v souboru aplikace Excel. To je běžné u údajů, které jsou shromažďovány pravidelně. Každý list v sešitu může obsahovat data za stanovené časové období. Chceme vzorec, který sečte data obsažená ve dvou nebo více listech.
Funkce SUM vám umožňuje snadno sčítat data na více listů pomocí 3D reference:
| 1 | = SUM (List1: List2! A1) |
To však není možné s funkcí SUMIFS. Místo toho musíme použít složitější vzorec.
Součet, pokud jde o více listů
Tento příklad shrne Počet plánovaných dodávek pro každého Zákazník na více pracovních listech, z nichž každý uchovává data týkající se jiného měsíce, pomocí funkcí SUMIFS, SUMPRODUCT a INDIRECT:
| 1 | = Souhrn |
Pojďme si projít tento vzorec.
Krok 1: Vytvořte vzorec SUMIFS pouze pro 1 vstupní list:
K součtu používáme funkci SUMIFS Počet plánovaných dodávek podle Zákazník pro jeden vstupní datový list:
| 1 | = SUMIFY (D3: D7, C3: C7, H3) |
Krok 2: Přidejte do vzorce odkaz na list
Výsledek vzorce ponecháme stejný, ale určíme, že vstupní data jsou v listu s názvem 'Krok 2'
| 1 | = SUMIFS ('Krok 2'! D3: D7, 'Krok 2'! C3: C7, H3) |
Krok 3: Vnořte se do funkce SUMPRODUCT
Chcete -li připravit vzorec k provádění výpočtů SUMIFS na více listech a poté sečíst výsledky dohromady, přidáme kolem vzorce funkci SUMPRODUCT
| 1 | = SUMPRODUCT (SUMIFS ('Krok 3'! D3: D7, 'Krok 3' C3: C7, H3)) |
Použitím funkce SUMIFS na jednom listu získáte jedinou hodnotu. Na více listech funkce SUMIFS vydává pole hodnot (jedna pro každý list). K součtu hodnot v tomto poli používáme funkci SUMPRODUCT.
Krok 4: Nahraďte odkaz na list seznamem názvů listů
Chceme vyměnit Název listu část vzorce se seznamem dat obsahujícím hodnoty: Jan, Února, Mar, a Duben. Tento seznam je uložen v buňkách F3: F6.
Nepřímá funkce zajišťuje, že se zobrazí seznam textů Názvy listů je považováno za součást platné reference na buňku ve funkci SUMIFS.
| 1 | = Souhrn |
V tomto vzorci odkaz na dříve zapsaný rozsah:
| 1 | 'Krok 3'! D3: D7 |
Nahrazuje se:
| 1 | NEPŘÍMÉ ("'" & F3: F6 & "'!" & "D3: D7") |
Díky uvozovkám je vzorec obtížně čitelný, proto se zde zobrazuje s přidanými mezerami:
| 1 | NEPŘÍMÉ ("'" & F3: F6 & "'!" & "D3: D7") |
Použití tohoto způsobu odkazování na seznam buněk nám také umožňuje shrnout data z více listů, které nedodržují styl číselného seznamu. Standardní 3D reference by vyžadovala, aby názvy listů byly ve stylu: Input1, Input2, Input3 atd., Ale výše uvedený příklad vám umožňuje použít seznam libovolných Názvy listů a nechat je odkazovat do samostatné buňky.
Zamykání odkazů na buňky
Aby byly naše vzorce čitelnější, ukázali jsme vzorce bez zamčených odkazů na buňky:
| 1 | = Souhrn |
Tyto vzorce ale nebudou správně fungovat, když je zkopírujete a vložíte jinam do souboru. Místo toho byste měli použít uzamčené odkazy na buňky takto:
| 1 | = SOUČET "C3: C7"), H3)) |
Další informace najdete v našem článku o zamykání odkazů na buňky.
Součet, pokud jde o více listů v Tabulkách Google
Použití funkce NEPŘÍMO k odkazu na seznam listů ve funkci SUMPRODUCT a SUMIFS není v Tabulkách Google aktuálně možné.
Místo toho lze pro každý vstupní list provést samostatné výpočty SUMIFS a výsledky sečíst:
| 1234 | = SUMIFY (led! D3: D7, led! C3: C7, H3)+SUMIFY (únor! D3: D7, únor! C3: C7, H3)+SUMIFY (březen! D3: D7, březen! C3: C7, H3)+SUMIFY (duben! D3: D7, duben! C3: C7, H3) |
