Stiahnite si ukážkový zošit
Tento tutoriál predvedie, ako používať funkcie SUMPRODUCT a SUMIFS na súčet údajov, ktoré spĺňajú určité kritériá vo viacerých hárkoch v Exceli a Tabuľkách Google..
Pravidelný súčet vo viacerých listoch
Niekedy môžu vaše údaje zahŕňať niekoľko pracovných hárkov v súbore Excel. To je bežné pre údaje, ktoré sa zbierajú pravidelne. Každý list v zošite môže obsahovať údaje za stanovené časové obdobie. Chceme vzorec, ktorý sumarizuje údaje obsiahnuté v dvoch alebo viacerých listoch.
Funkcia SUMA vám umožňuje jednoducho sumarizovať údaje na viacerých listoch pomocou a 3D referencia:
| 1 | = SUM (List1: List2! A1) |
S funkciou SUMIFS to však nie je možné. Namiesto toho musíme použiť zložitejší vzorec.
Súčet vo viacerých listoch
Tento príklad zhrnie Počet plánovaných dodávok pre každý Zákazník na viacerých pracovných listoch, z ktorých každý obsahuje údaje týkajúce sa iného mesiaca, pomocou funkcií SUMIFS, SUMPRODUCT a INDIRECT:
| 1 | = SUMPRODUCT (SUMIFY (NEPRIAMY ("'" & F3: F6 & "'!" & "D3: D7"), NEPRIAMY ("'" & F3: F6 & "'!" & "C3: C7"), H3)) |
Prejdeme si tento vzorec.
Krok 1: Vytvorte vzorec SUMIFS iba pre 1 vstupný list:
Na súčet používame funkciu SUMIFS Počet plánovaných dodávok od Zákazník pre jeden vstupný list:
| 1 | = SUMIFS (D3: D7, C3: C7, H3) |
Krok 2: Pridajte k vzorcu odkaz na hárok
Výsledok vzorca ponecháme rovnaký, ale špecifikujeme, že vstupné údaje sú v hárku tzv 'Krok 2'
| 1 | = SUMIFS („Krok 2“! D3: D7, „Krok 2“! C3: C7, H3) |
Krok 3: Vnorte sa do funkcie SUMPRODUCT
Aby sme pripravili vzorec na vykonávanie výpočtov SUMIFS na viacerých listoch a potom aby sme zhrnuli výsledky dohromady, pridáme k vzorcu funkciu SUMPRODUCT
| 1 | = SUMPRODUCT (SUMIFS („krok 3“! D3: D7, „krok 3“! C3: C7, H3)) |
Použitím funkcie SUMIFS na jednom hárku získate jednu hodnotu. Na viacerých listoch funkcia SUMIFS vydáva pole hodnôt (jedna pre každý pracovný list). Na sčítanie hodnôt v tomto poli používame funkciu SUMPRODUCT.
Krok 4: Nahraďte odkaz na hárok zoznamom názvov hárkov
Chceme nahradiť Názov listu časť vzorca so zoznamom údajov obsahujúcim hodnoty: Jan, Február, Mara Apríl. Tento zoznam je uložený v bunkách F3: F6.
NEPRIAMA funkcia zaisťuje, že sa zobrazí zoznam textov Názvy listov je považovaný za súčasť platného odkazu na bunku vo funkcii SUMIFS.
| 1 | = SUMPRODUKT (SUMIFY (NEPRIAMY ("'" & F3: F6 & "'!" & "D3: D7"), NEPRIAMY ("'" & F3: F6 & "'!" & "C3: C7"), H3)) |
V tomto vzorci odkaz na predtým zapísaný rozsah:
| 1 | „Krok 3“! D3: D7 |
Nahrádza sa:
| 1 | NEPRIAMY ("'" & F3: F6 & "'!" & "D3: D7") |
Uvodzovky robia vzorec ťažko čitateľným, a preto je tu zobrazený s pridanými medzerami:
| 1 | NEPRIAMY ("'" & F3: F6 & "'!" & "D3: D7") |
Použitie tohto spôsobu odkazovania na zoznam buniek nám tiež umožňuje sumarizovať údaje z viacerých hárkov, ktoré nedodržiavajú štýl číselného zoznamu. Štandardná 3D referencia by vyžadovala, aby názvy hárkov boli v štýle: Input1, Input2, Input3 atď., Ale vyššie uvedený príklad vám umožňuje použiť zoznam akýchkoľvek Názvy listov a nechať ich odkazovať do samostatnej bunky.
Zamknutie odkazov na bunky
Aby boli naše vzorce ľahšie čitateľné, ukázali sme vzorce bez uzamknutých odkazov na bunky:
| 1 | = SUMPRODUCT (SUMIFY (NEPRIAMY ("'" & F3: F6 & "'!" & "D3: D7"), NEPRIAMY ("'" & F3: F6 & "'!" & "C3: C7"), H3)) |
Tieto vzorce však nebudú fungovať správne, keď ich skopírujete a vložíte na iné miesto v súbore. Namiesto toho by ste mali použiť uzamknuté odkazy na bunky takto:
| 1 | = SUMPRODUCT (SUMIFS (NEPRIAMY ("'" & $ F $ 3: $ F $ 6 & "'!" & "D3: D7"), NEPRIAMY ("'" & $ F $ 3: $ F $ 6 & "'!" " „C3: C7“), H3)) |
Ak sa chcete dozvedieť viac, prečítajte si náš článok o uzamknutí odkazov na bunky.
Suma vo viacerých listoch v Tabuľkách Google
Použitie funkcie INDIRECT na odkazovanie na zoznam hárkov vo funkcii SUMPRODUCT a SUMIFS nie je v súčasnosti v Tabuľkách Google k dispozícii.
Namiesto toho je možné pre každý vstupný list vykonať samostatné výpočty SUMIFS a výsledky sčítať:
| 1234 | = SUMIFS (Jan! D3: D7, Jan! C3: C7, H3)+SUMIFY (február! D3: D7, február! C3: C7, H3)+SUMIFY (marec! D3: D7, marec! C3: C7, H3)+SUMIFY (apríl D3: D7, apríl C3: C7, H3) |
