Tento tutoriál ukazuje, jak používat Výbornýel SUMIF a SUMIFS Functions v Excelu a Tabulkách Google sečíst data, která splňují určitá kritéria.
Přehled funkcí SUMIF
Funkci SUMIF v Excelu můžete použít k součtu buněk obsahujících konkrétní hodnotu, součtových buněk, které jsou větší nebo rovné hodnotě atd.
(Všimněte si, jak se zobrazují vstupy vzorců)
Syntaxe a argumenty funkce SUMIF:
| 1 | = SUMIF (rozsah, kritéria, [rozsah_součtu]) |
rozsah - Rozsah buněk, na které chcete použít kritéria.
kritéria - Kritéria použitá k určení, které buňky se mají přidat.
rozsah_sumu - [nepovinné] Buňky k sečtení. Pokud je rozsah_součtu vynechán, buňky v rozsahu se místo toho sečtou.
Co je to funkce SUMIF?
Funkce SUMIF je jednou ze starších funkcí používaných v tabulkách. Používá se ke skenování v rozsahu buněk, které kontrolují konkrétní kritérium, a poté sečtou hodnoty v rozsahu, který těmto hodnotám odpovídá. Původní funkce SUMIF byla omezena pouze na jedno kritérium. Po roce 2007 byla vytvořena funkce SUMIFS, která umožňuje velké množství kritérií. Většina obecného použití mezi nimi zůstává stejná, ale v syntaxi jsou některé zásadní rozdíly, o kterých budeme v tomto článku diskutovat.
Pokud jste to ještě neudělali, můžete si velkou část podobné struktury a příkladů přečíst v článku COUNTIFS.
Základní příklad
Zvažme tento seznam zaznamenaných tržeb a chceme znát celkový příjem.
Protože jsme měli výdaj, zápornou hodnotu, nemůžeme udělat jen základní částku. Místo toho chceme sečíst pouze hodnoty, které jsou větší než 0. „Větší než 0“ bude naše kritéria ve funkci SUMIF. Náš vzorec, který to uvádí, je
| 1 | = SUMIF (A2: A7, "> 0") |
Příklad se dvěma sloupci
Zatímco původní funkce SUMIF byla navržena tak, aby vám umožnila použít kritérium na rozsah čísel, které chcete sčítat, většinu času budete muset použít jedno nebo více kritérií na jiné sloupce. Uvažujme o této tabulce:
Pokud nyní pomocí původní funkce SUMIF zjistíme, kolik banánů máme (uvedených v buňce D1), budeme muset zadat rozsah, který chceme součet jako poslední argument, a tak by náš vzorec byl
| 1 | = SUMIF (A2: A7, D1, B2: B7) |
Když si však programátoři nakonec uvědomili, že uživatelé chtějí zadat více než jedno kritérium, byla vytvořena funkce SUMIFS. Aby bylo možné vytvořit jednu strukturu, která by fungovala pro libovolný počet kritérií, SUMIFS vyžaduje, aby byl nejprve uveden rozsah součtů. V našem příkladu to znamená, že vzorec musí být
| 1 | = SUMIFS (B2: B7, A2: A7, D1) |
POZNÁMKA: Tyto dva vzorce získají stejný výsledek a mohou vypadat podobně, proto věnujte velkou pozornost tomu, která funkce se používá, abyste zajistili uvedení všech argumentů ve správném pořadí.
Práce s daty, více kritérií
Při práci s daty v tabulce, i když je možné zadat datum přímo do vzorce, je nejlepší mít datum v buňce, abyste na buňku ve vzorci mohli pouze odkazovat. Díky tomu může počítač například vědět, že chcete použít datum 27.5.2020, a nikoli číslo 5 děleno 27 děleno rokem 2022.
Podívejme se na naši další tabulku zaznamenávající počet návštěvníků webu každé dva týdny.
V D2 a E2 můžeme určit počáteční a koncový bod rozsahu, na který se chceme podívat. Náš vzorec pro součet počtu návštěvníků v tomto rozsahu by mohl být:
| 1 | = SUMIFS (B2: B7, A2: A7, "> =" & D2, A2: A7, "<=" & E2) |
Všimněte si, jak jsme mohli spojit srovnání „=“ s odkazy na buňky a vytvořit kritéria. I když byla obě kritéria aplikována na stejný rozsah buněk (A2: A7), musíte rozsah vypsat dvakrát, jednou pro každé kritérium.
Více sloupců
Při použití více kritérií je můžete použít na stejný rozsah jako v předchozím příkladu, nebo je můžete použít na různá rozmezí. Zkombinujme naše ukázková data do této tabulky:
Nastavili jsme některé buňky, aby uživatel zadal, co chce hledat v buňkách E2 až G2. Potřebujeme tedy vzorec, který sečte celkový počet jablek sebraných v únoru. Náš vzorec vypadá takto:
| 1 | = SUMIFS (C2: C7, B2: B7, "> =" & F2, B2: B7, "<=" & G2, A2: A7, E2) |
SUMIFS s logikou typu NEBO
Až do tohoto bodu byly všechny příklady, které jsme použili, srovnávány na základě AND, kde hledáme řádky, které splňují všechna naše kritéria. Nyní vezmeme v úvahu případ, kdy chcete vyhledat možnost, aby řada splňovala jedno nebo jiné kritérium.
Podívejme se na tento seznam prodejů:
Chtěli bychom sečíst celkové tržby pro Adama a Boba. K tomu máte několik možností. Nejjednodušší je přidat dva SUMIFY dohromady, například:
| 1 | = SUMIFS (B2: B7, A2: A7, "Adam")+SUMIFS (B2: B7, A2: A7, "Bob") |
Zde jsme nechali počítač vypočítat naše jednotlivá skóre a poté je sečetli.
Naše další možnost je dobrá, když máte více rozsahů kritérií, například že nechcete opakovaně přepisovat celý vzorec. V předchozím vzorci jsme ručně řekli počítači, aby přidal dva různé SUMIFY dohromady. Můžete to však také udělat tak, že do pole napíšete svá kritéria, například takto:
| 1 | = SUM (SUMIFS (B2: B7, A2: A7, {"Adam", "Bob"})) |
Podívejte se, jak je pole konstruováno uvnitř složených závorek. Když počítač vyhodnotí tento vzorec, bude vědět, že chceme vypočítat funkci SUMIFS pro každou položku v našem poli, čímž vytvoříme řadu čísel. Vnější funkce SUMA pak převezme toto pole čísel a změní jej na jedno číslo. Krok za krokem vyhodnocení vzorce by vypadal takto:
| 123 | = SUM (SUMIFS (B2: B7, A2: A7, {"Adam", "Bob"}))= SUM (27401, 43470)= 70871 |
Získáme stejný výsledek, ale mohli jsme vzorec napsat trochu stručněji.
Nakládání s mezerami
Někdy bude mít vaše datová sada prázdné buňky, které musíte buď najít, nebo se jim vyhnout. Nastavení kritérií pro ně může být trochu složité, pojďme se tedy podívat na další příklad.
Buňka A3 je skutečně prázdná, zatímco buňka A5 má vzorec, který vrací řetězec nulové délky „“. Pokud chceme zjistit celkový součet skutečně prázdné buňky, použili bychom kritérium „=“ a náš vzorec by vypadal takto:
| 1 | = SUMIFS (B2: B7, A2: A7, "=") |
Na druhou stranu, pokud chceme získat součet pro všechny buňky, které vizuálně vypadají prázdné, změníme kritéria na „“ a vzorec vypadá takto
| 1 | = SUMIFS (B2: B7, A2: A7, "") |
Pojďme to otočit: co když chcete najít součet neprázdných buněk? Současný design vám bohužel nedovolí vyhnout se řetězci nulové délky. Můžete použít kritérium „“, ale jak vidíte v příkladu, stále obsahuje hodnotu z řádku 5.
| 1 | = SUMIFS (B2: B7, A2: A7, "") |
Pokud potřebujete nepočítat buňky obsahující řetězce nulové délky, měli byste zvážit použití funkce LEN uvnitř SUMPRODUCT
SUMIF v Tabulkách Google
Funkce SUMIF funguje v Tabulkách Google úplně stejně jako v Excelu:
