V tomto článku se naučíme Výpočet průměru pro čísla splňující zadaná kritéria v aplikaci Excel.
Scénář:
Při práci s daty, kde potřebujeme získat průměr čísel s hodnotami kritérií. Proč hodnoty kritérií vytvářejí rozdíl ve výsledku, funkce Excel by je měla považovat za nulové a vzít výsledek. Nulová hodnota může znamenat rozdíl v průměru čísel. Průměr čísel je součet požadovaných čísel dělený počtem čísel. Kde nula nepřispívá k součtu, ale zvyšuje počet. Pochopte, jak problém vyřešit.
Jak problém vyřešit?
Zde musíme najít průměr čísel s kritérii. Kritéria ignorují hodnoty kritérií. Pokud máte pouze kladná čísla nebo záporná čísla, můžete použít funkci AVERAGEIF
Syntaxe vzorce (kladná čísla):
| = AVERAGEIF (hodnoty, "> = 0") |
Výše uvedený vzorec obsahuje nuly, ale ignoruje hodnoty kritérií. Pokud chcete ignorovat nuly a hodnoty kritérií, použijte kritéria jako "> 0".
Podobně můžete získat průměr záporných hodnot včetně nul pomocí vzorce:
Syntaxe vzorce (záporná čísla):
| = AVERAGEIF (hodnoty, "<= 0") |
Výše uvedený vzorec obsahuje nuly, ale ignoruje hodnoty kritérií. Pokud chcete ignorovat nuly a hodnoty kritérií, použijte kritéria jako "> 0"
Problém zvažování čísel ignorujících kritéria však není vyřešen. Můžete si myslet, že průměrem výše uvedených dvou výsledků (průměr kladných čísel a průměr záporných čísel) získáte požadovanou hodnotu. Funguje to dobře, dokud se počet kladných čísel nerovná počtu záporných čísel. Pro tento problém použijeme funkci AGGREGATE. Funkce AGGREGATE je balíček matematických funkcí. Pomáhá obecně řešit tyto druhy problémů. Rozbalení tohoto balíčku pro průměr hodnot ignorujících kritéria. Použijte níže uvedený vzorec.
Syntaxe vzorce (pro všechna čísla):
| = AGGREGÁT (1, 6, hodnoty) |
1: Argument průměrných čísel
6: ignorování hodnot kritérií
Příklad:
Pochopit to všechno může být matoucí. Vezměme si příklad a pochopíme, jak tyto vzorce použít v příkladu. Zde máme seznam hodnot, pro které je vyžadován průměr.
Nejprve vypočítáme průměr pro kladná čísla. K tomu použijeme níže uvedené údaje.
Použijte vzorec:
| = AVERAGEIF (B3: B15, "> = 0") |
Vysvětlení
- Kritériem jsou čísla, která jsou větší nebo rovna nule.
- Funkce AVERAGEIF zohledňuje pouze čísla, která splňují kritéria, a vrací průměr čísel
Zde vzorec vrátí 58,6, přičemž vezme v úvahu pouze kladné hodnoty, kde je počet kladných hodnot 7. Podobně můžete získat průměr záporných čísel.
Použijte vzorec:
| = AVERAGEIF (B3: B15, "<= 0") |
Vysvětlení
- Kritériem jsou čísla, která jsou menší nebo rovna nule.
- Funkce AVERAGEIF zohledňuje pouze čísla, která splňují kritéria, a vrací průměr čísel.
U záporných čísel je průměr -21,0 včetně nul ignorujících hodnoty kritérií. Počet do uvažovaných čísel je 4.
V tomto případě se počet záporných čísel nerovná počtu kladných čísel. Průměr dvou výše uvedených výsledků tedy nepřinese průměr všech čísel. Pro tyto případy použijte níže uvedený vzorec.
Použijte vzorec:
| = AGREGÁT (1, 6, B3: B15) |
Vysvětlení:
- Argument 1 je uveden pro použití funkce Průměr.
- Argument 6 slouží k ignorování hodnot kritérií.
- Funkce AGGREGATE vrací průměr všech čísel v poli B3: B15 ignorující hodnoty kritérií.
Průměr všech čísel ignorujících všechny hodnoty kritérií vychází na 36,2. Zde je počet čísel 9. Funkce AGGREGATE je nejlepším způsobem, jak tyto problémy vyřešit, protože tyto funkce jsou vynikajícími vestavěnými funkcemi.
Pokud se pokoušíte získat průměr čísel pomocí základní funkce PRŮMĚR, musíte použít níže uvedený vzorec, abyste získali průměr čísel ignorujících kritéria.
Použijte vzorec:
| { = AVERAGE (IF (IScriteria (B3: B15), "", B3: B15)) } |
Poznámka : nepoužívejte kudrnaté závorky ručně. Stačí použít Ctrl + Shift + Enter namísto Vstupte získat výsledek.
Vysvětlení:
- Funkce IF kontroluje stav pomocí funkce IScriteria na poli B3: B15.
- Funkce IScriteria vrací True pro hodnotu, kde hodnota je kritériem, a False, pokud ne.
- Funkce IF vrací prázdnou hodnotu, pokud je hodnota kritériem, a vrací odpovídající hodnotu, pokud hodnota není kritériem.
- PRŮMĚR funkce vrací průměr vrácených hodnot.
- Díky složeným závorkám tyto logické funkce fungují pro pole.
Jak vidíte, vzorec funguje dobře a vrací stejný výsledek. Ale použijte funkci AGGREGATE, je snadné ji použít pro různé matematické funkce s různými vestavěnými kritérii.
Pokud v poli nemáte žádnou nulovou hodnotu. Můžete použít návratovou nulu ze všech hodnot kritérií a použít vzorec, jak zde vzít průměr čísel ignorujících nuly. Tento vzorec ale nefunguje s hodnotami kritérií.
Zde jsou všechny pozorovací poznámky týkající se používání vzorce.
Poznámky:
- Funkce AGGREGATE je pro tyto problémy nejlepší.
- Funkce vrací #HODNOTA! kritéria, pokud zadaným kritériím neodpovídá žádná hodnota.
- Jsou akceptována různá kritéria ve vzorci, například rovná se (=), menší než (<), menší než nebo se rovná (=), větší než (>) a nerovná se ().
Tímto způsobem jsme se naučili Výpočet průměru pro čísla splňující zadaná kritéria v aplikaci Excel. Tyto funkce můžete použít v Excelu 2016, 2013 a 2010. Najděte další články o matematické formulaci s různými kritérii. Pokud se vám naše blogy líbily, sdílejte je se svými přáteli na Facebooku. A také nás můžete sledovat na Twitteru a Facebooku. Rádi bychom od vás slyšeli, dejte nám vědět, jak můžeme zlepšit, doplnit nebo inovovat naši práci a zlepšit ji pro vás. Napište nám na e -mail
Jak zvýraznit buňky nad a pod průměrnou hodnotou: pomocí podmíněného formátování v aplikaci Excel zvýrazněte hodnoty, které jsou nad nebo pod průměrnou hodnotou.
Ignorovat nulu v průměru čísel: vypočítejte průměr čísel v poli ignorujícím nuly pomocí funkce AVERAGEIF v aplikaci Excel.
Vypočítat vážený průměr: pomocí funkce SUMPRODUCT v Excelu najděte průměr hodnot s různou hmotností.
Průměrný rozdíl mezi seznamy: vypočítat průměrný rozdíl dvou různých seznamů. Další informace o tom, jak vypočítat průměr pomocí základního vzorce matematického průměru.
Průměrná čísla, pokud nejsou v Excelu prázdná: extrahujte průměr hodnot, pokud buňka není v Excelu prázdná.
PRŮMĚR 3 nejlepších skóre v seznamu v aplikaci Excel: Najděte průměr čísel s kritérii jako nejvyšší 3 čísla ze seznamu v aplikaci Excel
Oblíbené články:
Jak používat funkci IF v aplikaci Excel : Příkaz IF v aplikaci Excel zkontroluje podmínku a vrátí konkrétní hodnotu, pokud je podmínka PRAVDA, nebo vrátí jinou konkrétní hodnotu, pokud NEPRAVDA.
Jak používat funkci VLOOKUP v Excelu : Toto je jedna z nejpoužívanějších a nejoblíbenějších funkcí Excelu, která se používá k vyhledávání hodnot z různých rozsahů a listů.
Jak používat funkci COUNTIF v aplikaci Excel : Pomocí této úžasné funkce spočítejte hodnoty s podmínkami. K počítání konkrétních hodnot nemusíte data filtrovat. Funkce Countif je nezbytná pro přípravu řídicího panelu.
Jak používat funkci SUMIF v aplikaci Excel : Toto je další základní funkce řídicího panelu. To vám pomůže shrnout hodnoty za konkrétních podmínek.
