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.

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.

01 z 10

Jak funguje funkce SUMIFS

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

V tomto tutoriálu nastavíme dvě podmínky pomocí SUMIFS - prodejních agentů v prodejním regionu východ, kteří v minulém roce měli 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.

Tutorial Témata

• Zadání výukového data
• Syntaxe funkce SUMIFS
• Spuštění funkce SUMIFS
• Argument Sum_range
• Argument Criteria_range1
• Argument kritéria1
• Argument Criteria_range2
• Argument kritéria2

02 z 10

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 10

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, ...)

Poznámka: Do funkce lze zadat až 127 párů kriterií / kritérií .

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 zadanými kritérii a jejich odpovídajícími argumenty Criteria_range .

Criteria_range - skupina buněk funkce je hledat shodu s příslušným parametrem Criteria .

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

04 z 10

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 kartu Vzorce .
3. Klepnutím na ikonu Math & Trig na pásu karet otevřete rozbalovací seznam funkcí.
4. Klepnutím na SUMIFS v seznamu vyvoláte dialog SUMIFS.

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.

05 z 10

Zadání argumentu Sum_range

Argument Sum_range 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 výukovém programu jsou data pro argument Sum_range umístěna ve sloupci Celkové prodeje .

Tutorial Kroky

1. Klepněte na řádek Sum_range v dialogovém okně .
2. Zvýrazněte buňky F3 až F9 v listu a přidejte tyto odkazy na buňku do řádku Sum_range .

06 z 10

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ů.

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

Tutorial Kroky

1. V dialogovém okně klikněte na řádek Criteria_range1 .
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í .

07 z 10

Zadání argumentu Criteria1

V tomto tutoriálu je prvním kritériem, které hledáme, aby odpovídalo, pokud jsou data v rozsahu D3: D9 rovna Východě .

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

Tutorial Kroky

1. V dialogovém okně klikněte na řádek Criteria1 .
2. Klepnutím na buňku D12 zadejte odkaz buňky. Funkce vyhledá v rozsahu vybraném v předchozím kroku údaje, které odpovídají těmto kritériím.
3. 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

Pokud je jako argument kritéria zadán odkaz na buňku, jako je D12, funkce SUMIFS bude hledat shody s údaji, které byly zadány do této buňky v listu .

Takže po zjištění částky prodeje pro východní region bude snadné najít stejné údaje pro jiný prodejní region jednoduše tím, že změníte východ na sever nebo západ v buňce D12. Funkce automaticky aktualizuje a zobrazí nový výsledek.

08 z 10

Zadání argumentu Criteria_range2

Jak již bylo zmíněno výše, v tomto tutoriálu se snažíme v každém záznamu dat shodit 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ů.

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. V dialogovém okně klikněte na řádek Criteria_range2 .
2. Velkokřídlé buňky E3 až E9 v listu zadejte tyto odkazy na buňky jako druhý rozsah, který má být prohledán funkcí .

09 z 10

Zadání argumentu Criteria2

V tomto výukovém kurzu je druhým kritériem, které hledáme, aby odpovídalo, je-li data v rozsahu E3: E9 méně než 275 prodejních zakázek.

Stejně jako u argumentu Criteria1 , budeme zadávat odkaz na buňku na místo Criteria2 do dialogového okna namísto samotných dat.

Tutorial Kroky

1. Klikněte na řádek Criteria2 v dialogovém okně .
2. Klepnutím na buňku E12 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.
3. Klepnutím na tlačítko OK dokončete funkci SUMIFS a zavřete dialogové okno.
4. Odpověď nula (0) se objeví v buňce F12 - buňce, 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á.
5. Kritéria vyhledávání budou přidána v dalším kroku tutoriálu.

10 z 10

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

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

Tutorial Kroky

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

1. V buňce D12 typu East a stiskněte klávesu Enter na klávesnici.
2. V buňce typu E12 <275 a stiskněte klávesu Enter na klávesnici ("<" je symbol pro méně než v aplikaci Excel).
3. Odpověď $ 119,719.00 by se měla zobrazit v buňce F12.
4. 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.
5. Částka 49 017 a 70 702 dolarů činí 119 719 dolarů.
6. Když klepnete na buňku F12, úplnou funkci
   = SUMIFS (F3: F9, D3: D9, D12, E3: E9, E12) se objeví ve vzorci nad tabulkou .