Excel SUMIFS: Součet hodnot pouze splňuje více kritérií

Funkce SUMIFS rozšiřuje užitečnost funkce SUMIF tím, že umožňuje zadat od 2 do 127 kritérií spíše než jen jedno, jako u SUMIF.

Naučte se, jak můžete použít funkci SUMIFS v aplikacích Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2019 pro Mac, Excel 2016 pro Mac, Excel pro Mac 2011 a Excel Online.

01 z 11

Jak funguje funkce SUMIFS

Normálně SUMIFS pracuje s řadami dat nazvaných záznamy. Ve záznamu jsou všechna data v každé buňce nebo v poli v řádku souvisejí - například název, adresa a telefonní číslo společnosti.

SUMIFS hledá konkrétní kritéria ve dvou nebo více polích záznamu a pouze pokud zjistí shodu pro každé zadané pole, jsou údaje pro tento záznam shrnuty.

V kurzu SUMIF krok za krokem jsme splnili jedno kritérium prodejních agentů, které prodaly více než 250 objednávek za rok.

V tomto tutoriálu nastavíme dvě podmínky pomocí SUMIFS: prodejních agentů v Východní který měl v uplynulém roce méně než 275 prodejů.

Nastavení více než dvou podmínek lze provést zadáním dalších parametrů Criteria_range a Criteria for SUMIFS.

Postupujte podle kroků v níže uvedených tématech a projděte vytvořením a použitím funkce SUMIFS, která je zobrazena na obrázku výše.

02 z 11

Zadání výukového data

Prvním krokem k použití funkce SUMIFS v aplikaci Excel je zadání dat.

Zadejte data do buněk D1 až F11 listu aplikace Excel, jak je vidět na obrázku výše.

Funkce SUMIFS a kritéria vyhledávání (méně než 275 objednávek a obchodních zástupců z prodejní oblasti Východní) budou přidány do řádku 12 pod údaji.

Pokyny pro tutoriály neobsahují kroky formátování pro pracovní list.

To neovlivní dokončení tutoriálu. Váš pracovní list bude vypadat jinak než ukázaný příklad, ale funkce SUMIFS vám poskytne stejné výsledky.

03 z 11

Syntaxe funkce SUMIFS

V aplikaci Excel se syntaxe funkce odkazuje na rozložení funkce a zahrnuje název funkce, závorky a argumenty.

Syntaxe funkce SUMIFS je:

= SUMIFS (Sum_range, Criteria_range1, Criteria1, Criteria_range2, Criteria2, …)

Až 127 Rozsah kritérií / Kritéria páry mohou být specifikovány ve funkci.

04 z 11

Argumenty funkce SUMIFS

Funkce argumenty říkají funkci, jaké podmínky jsou testovány a jaký rozsah dat součet, když tyto podmínky jsou splněny.

Všechny argumenty v této funkci jsou vyžadovány.

Sum_range - data v tomto rozsahu buněk se sčítají, když je nalezena shoda mezi všemi uvedenými Kritéria a jejich odpovídající Rozsah kritérií argumenty.

Rozsah kritérií - skupinou buněk funkce je hledat shodu s odpovídajícími Kritéria argument.

Kritéria - tato hodnota je porovnána s údaji v odpovídajících Rozsah kritérií . Pro tento argument lze zadat skutečná data nebo odkaz na buňky.

05 z 11

Spuštění funkce SUMIFS

Ačkoli je možné jednoduše zadat funkci SUMIFS do buňky v listu, mnoho uživatelů je jednodušší používat dialogové okno funkce pro vstup do funkce.

Tutorial Kroky

1. Klepnutím na buňku F12 ji vytvoříte jako aktivní buňku. Zde vstoupíme do funkce SUMIFS.
2. Klikněte na tlačítko Vzorce záložka.
3. Klikněte na Math & Trig ve skupině Funkční knihovna.
4. Klikněte na SUMIFS v seznamu vyvolá dialogové okno funkce SUMIFS.

Aplikace Excel Online nemá kartu Vzorce. Chcete-li použít SUMIFS v programu Excel Online, použijte tlačítko Vložit funkci.

1. Klikněte na buňku F12 aby byla aktivní buňka. Zde vstoupíme do funkce SUMIFS.
2. Klepněte na tlačítko Vložte funkci tlačítko. Otevře se dialogové okno Vložit funkci.
3. Klikněte na Math & Trig v seznamu Kategorie.
4. Klepnutím na SUMIFS v seznamu spusťte funkci.

Data, která vstupujeme do prázdných řádků v dialogovém okně, tvoří argumenty funkce SUMIFS.

Tyto argumenty vysvětlují funkci, za jakých podmínek testujeme a jaký rozsah dat je součet, když jsou tyto podmínky splněny.

06 z 11

Zadání argumentu Sum_range

The Sum_range argument obsahuje odkazy na buňky, které chceme přidat.

Když funkce zjistí shodu mezi všemi zadanými parametry kritéria a kriteria pro záznam, pole Sum_range pro daný záznam je zahrnuto do součtu.

V tomto tutoriálu jsou data pro argument Sum_range umístěna v adresáři Celkový prodej sloupec.

Tutorial Kroky

1. Klikněte na tlačítko Sum_range v dialogovém okně.
2. Zvýrazněte buňky F3 až F9 v listu přidat tyto odkazy na buňku Sum_range řádek.

07 z 11

Zadání argumentu Criteria_range1

V tomto tutoriálu se snažíme v každém záznamu dat shodovat dvě kritéria:

1. Obchodní zástupci z východní oblasti prodeje.
2. Obchodní zástupci, kteří v tomto roce uskutečnili méně než 275 prodejů.

The Criteria_range1 argument označuje rozsah buněk, které má SUMIFS vyhledávat, když se snaží splnit první kritéria - východní oblast prodeje.

Tutorial Kroky

1. Klikněte na tlačítko Criteria_range1 v dialogovém okně.
2. Zvýrazněte buňky D3 až D9 v listu a zadejte tyto odkazy na buňky jako rozsah, který má být prohledán funkcí.

08 z 11

Zadání argumentu Criteria1

V tomto tutoriálu je prvním kritériem, které hledáme, aby se shodovalo, pokud se data v rozsahu D3: D9 rovnají Východní .

Přestože skutečná data, například slovo Východní , lze zadat do dialogového okna tohoto argumentu, že je obvykle nejlepší přidat data do buňky v listu a potom zadejte odkaz buňky do dialogového okna.

Tutorial Kroky

1. Klikněte na tlačítko Kritéria1 v dialogovém okně.
2. Klikněte na buňku D12 zadejte odkaz na buňku. Funkce vyhledá v rozsahu vybraném v předchozím kroku údaje, které odpovídají těmto kritériím.

Hledaný výraz (východ) bude přidán do buňky D12 v posledním kroku tutoriálu.

Jak odkazy na buňky zvyšují všestrannost SUMIFS

Je-li odkaz na buňku, například D12, zadán jako Kritéria Argument, funkce SUMIFS bude hledat shody s údaji, které byly zadány do této buňky v listu.

Takže poté, co zjistil částku prodeje za Východní v regionu bude snadné najít stejné údaje pro jiný prodejní region prostě změnou Východní na Severní nebo Západ v buňce D12. Funkce automaticky aktualizuje a zobrazí nový výsledek.

09 z 11

Zadání argumentu Criteria_range2

Argument Criteria_range2 označuje rozsah buněk, které má SUMIFS vyhledávat, když se snaží vyhovět druhému kritériu - prodejním agentům, kteří tento rok prodali méně než 275 objednávek.

Tutorial Kroky

1. Klikněte na tlačítko Kritérium2 v dialogovém okně.
2. Zvýrazněte buňky E3 až E9 v listu zadejte tyto odkazy na buňky jako druhý rozsah, který má funkce vyhledávat.

10 z 11

Zadání argumentu Criteria2

Druhým standardem, který hledáme, je, pokud jsou data v rozsahu E3: E9 nižší než 275 prodejních zakázek.

Stejně jako u Kritéria1 argument, zadáme odkaz buňky do polohy Criteria2 do dialogového okna namísto samotných dat.

Tutorial Kroky

1. Klikněte na tlačítko Kritéria2 v dialogovém okně.
2. Klikněte na buňku E12 zadejte odkaz na buňku. Funkce vyhledá v rozsahu vybraném v předchozím kroku údaje, které odpovídají kritériím.
3. Klikněte na tlačítko OK dokončete funkci SUMIFS a zavřete dialogové okno.

Odpověď nula (0) se objeví v buňce F12 (buňka, do které jsme vstoupili do funkce), protože jsme ještě nezadali data do polí Criteria1 a Criteria2 (C12 a D12). Dokud tak neučiníme, není nic pro to, aby se funkce přidala, takže celková doba zůstává nulová.

Kritéria vyhledávání budou přidána v dalším kroku tutoriálu.

11 z 11

Přidání kritérií vyhledávání a dokončení výuky

Posledním krokem v tutoriálu je přidat data do buněk v listu označeném jako obsahující Kritéria argumenty.

Tutorial Kroky

Nápovědu k tomuto příkladu naleznete na obrázku výše.

1. V buňce D12 typ Východní a stiskněte tlačítko Zadejte na klávesnici.
2. V buňce E12 typ <275 a stiskněte tlačítko Zadejte ("<" je symbol pro méně než v aplikaci Excel).

Odpověď $ 119,719.00 by se měla zobrazit v buňce F12.

Pouze dvě záznamy v řádcích 3 a 4 odpovídají oběma kritériím, a proto jsou součtem pouze součtu prodejů pro tyto dvě záznamy.

Částka 49 017 a 70 702 dolarů činí 119 719 dolarů.

Když klepnete na buňku F12, úplnou funkci= SUMIFS (F3: F9, D3: D9, D12, E3: E9, E12) se objeví ve vzorci nad tabulkou.