Tento výukový program ukazuje, jak pomocí funkcí Excel AVERAGEIF a AVERAGEIFS v Excelu a Tabulkách Google průměrovat data, která splňují určitá kritéria.
Přehled funkcí AVERAGEIF
Pomocí funkce AVERAGEIF v Excelu můžete počítat buňky obsahující konkrétní hodnotu, počítat buňky větší nebo rovné hodnotě atd.
Chcete -li použít funkci listu AVERAGEIF Excel, vyberte buňku a zadejte:
(Všimněte si, jak se zobrazují vstupy vzorců)
Syntaxe a argumenty funkce AVERAGEIF:
= AVERAGEIF (rozsah, kritéria, [průměrný_rozsah])
rozsah - Rozsah buněk, které se mají počítat.
kritéria - Kritéria, která určují, které buňky by měly být započítány.
průměrný_rozsah - [nepovinné] Průměr buněk. Pokud je vynechán, použije se rozsah.
Co je to funkce AVERAGEIF?
Funkce AVERAGEIF 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é udávají průměr (aka matematický průměr), pokud jsou hodnoty v rozsahu, které těmto hodnotám odpovídají. Původní funkce AVERAGEIF byla omezena pouze na jedno kritérium. Po roce 2007 byla vytvořena funkce AVERAGEIFS, 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 průměrný příjem.
Protože jsme měli výdaj, zápornou hodnotu, nemůžeme udělat jen základní průměr. Místo toho chceme průměrovat pouze hodnoty, které jsou větší než 0. „Větší než 0“ bude naše kritéria ve funkci AVERAGEIF. Náš vzorec, který to uvádí, je
= AVERAGEIF (A2: A7, "> 0")
Příklad se dvěma sloupci
Přestože byla původní funkce AVERAGEIF 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í použijeme původní funkci AVERAGEIF a zjistíme, kolik banánů v průměru máme. Vložíme naše kritéria do buňky D1 a budeme muset zadat rozsah, který chceme průměrný jako poslední argument, a tak by náš vzorec byl
= AVERAGEIF (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 AVERAGEIFS. Aby se vytvořila jedna struktura, která by fungovala pro libovolný počet kritérií, AVERAGEIFS vyžaduje, aby byl nejprve uveden rozsah součtů. V našem příkladu to znamená, že vzorec musí být
= AVERAGEIFS (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 nalezení průměrného počtu návštěvníků v tomto rozsahu by mohl být:
= AVERAGEIFS (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:
= AVERAGEIFS (C2: C7, B2: B7, "> =" & F2, B2: B7, "<=" & G2, A2: A7, E2)
AVERAGEIFS 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 průměrné tržby pro Adama a Boba. Nejprve krátká diskuse o průměrování. Pokud máte nerovnoměrný počet věcí, například 3 položky pro Adama a 2 pro Boba, nemůžete jednoduše vzít průměr tržeb každé osoby. Toto je známé jako průměr průměrů a nakonec položíte položku, která má málo záznamů, nespravedlivou váhu. Pokud tomu tak je u vašich údajů, budete muset vypočítat průměr „manuálně“: vezměte součet všech svých položek dělený počtem vašich položek. Chcete -li zjistit, jak to udělat, můžete se podívat na články zde:
Pokud je počet záznamů stejný, jako v naší tabulce, máte několik možností, které můžete udělat. Nejjednodušší je sloučit dva AVERAGEIFS dohromady, podobně a poté vydělit 2 (počet položek v našem seznamu)
= (AVERAGEIFS (B2: B7, A2: A7, "Adam")+AVERAGEIFS (B2: B7, A2: A7, "Bob"))/2
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é AVERAGEIFS dohromady. Můžete to však také udělat tak, že do pole napíšete svá kritéria, například takto:
= PRŮMĚR (AVERAGEIFS (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 AVERAGEIFS pro každou položku v našem poli, čímž vytvoříme pole čísel. Vnější funkce PRŮMĚR poté převezme toto pole čísel a změní jej na jedno číslo. Krok za krokem vyhodnocení vzorce by vypadal takto:
= AVERAGE (AVERAGEIFS (B2: B7, A2: A7, {"Adam", "Bob"})) = AVERAGE (13701, 21735) = 17718
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ý průměr skutečně prázdné buňky, použili bychom kritérium „=“ a náš vzorec by vypadal takto:
= AVERAGEIFS (B2: B7, A2: A7, "=")
Na druhou stranu, pokud chceme získat průměr pro všechny buňky, které vizuálně vypadají prázdné, změníme kritéria na „“ a vzorec vypadá takto
= AVERAGEIFS (B2: B7, A2: A7, "")
Pojďme to otočit: co když chcete najít průměr 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.
= AVERAGEIFS (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
AVERAGEIF v Tabulkách Google
Funkce AVERAGEIF funguje v Tabulkách Google úplně stejně jako v Excelu:
