Jak používat COUNTIFS, SUMIFS, AVERAGEIFS v Excelu

Tři nejčastěji používané vzorce v aplikaci Excel, které provádějí jednoduché matematické výpočty jsou SPOČÍTAT, SOUČET a PRŮMĚRNÝ. Ať už spravujete finanční rozpočet v aplikaci Excel nebo jednoduše sledujete svou příští dovolenou, pravděpodobně jste dříve použili jednu z těchto funkcí.

V tomto článku budeme procházet základy těchto tří funkcí a jejich relevantními a užitečnými protějšky: COUNTIFS, SUMIFS a AVERAGEIFS.

Řekněme, že začínáme nový online obchod prodávající mobilní telefony a máme list, který uvádí prodej, který jsme provedli v prvních dvou měsících. Stáhněte si například tabulku aplikace Excel zde.

Excel COUNT, SUM a AVERAGE

Chcete-li vědět, kolik mobilních telefonů, které jsme prodali, můžeme rychle použít SPOČÍTAT vzorec:

= COUNT (E2: E16)

Na druhou stranu, abychom získali celkové množství prodeje, které jsme provedli, můžeme použít SOUČET vzorec:

= SUM (E2: E16)

Konečně, abychom zjistili průměrný prodej, který jsme vyrobili pro všechny telefony, můžeme použít PRŮMĚRNÝ vzorec:

= PRŮMĚRNÝ (E2: E16)

Výsledek by měl být následující:

Vzorce COUNT, SUM a AVERAGE budou fungovat pouze pro záznamy, ve kterých je hodnota buňky v číselném formátu. Jakýkoliv záznam v rozsahu vzorce (tj. E2: E16 v tomto příkladu) bude ignorován.

Zajistěte prosím, aby všechny buňky v rámci vzorce COUNT, SUM a AVERAGE byly formátovány jako Číslo, ne Text. Zkuste použít stejný vzorec, ale s E: E jako rozsah namísto E2: E16. Vrátí stejný výsledek jako dříve, protože ignoruje záhlaví (t.j.. Prodejní cena), který je v textovém formátu.

Co kdybychom chtěli znát počet prodejů, celkový objem prodeje a průměrnou výši prodeje na telefon, jen pro ty, kteří se prodávají v USA? Zde hrají důležitou roli COUNTIFS, SUMIFS a AVERAGEIFS. Dodržujte níže uvedený vzorec:

COUNTIFS

Členění vzorce:

1. = COUNTIFS ( - “=” označuje začátek vzorce v buňce a COUNTIFS je první část aplikace Excel, kterou používáme.
2. D2: D16 - Vztahuje se na rozsah údajů, aby se zjistilo, zda splňuje kritéria, která mají být zahrnuta do vzorce pro počítání.
3. „USA“ - Kritéria, která se mají vyhledat v zadaném rozsahu dat (D2: D16)
4. ) - Zavírací závorka označující konec vzorce.

Vzorec vrací 6, což je počet prodejů výrobků expedovaných ze skladu v USA.

SUMIFS

Členění vzorce:

1. = SUMIFS ( - “=” označuje začátek vzorce znovu.
2. E2: E16 - Vztahuje se na rozsah údajů, které bychom chtěli celkem, tj. Prodejní cenu v našem příkladu.
3. D2: D16 - Vztahuje se na rozsah údajů, abyste zjistili, zda splňuje kritéria, která mají být zahrnuta do celkové částky.
4. „USA“ - Kritéria, která se mají vyhledat v zadaném rozsahu dat (D2: D16)
5. ) - Zavírací závorka označující konec vzorce.

Vzorec ukazuje $ 6,050 celkové prodeje, které byly uskutečněny u výrobků dodaných ze skladu ve Spojených státech.

PRŮMĚRNÉ

Členění vzorce:

1. = AVERAGEIFS ( - “=” označují začátek vzorce.
2. E2: E16 - Vztahuje se na rozsah údajů, které bychom rádi průměrovali. V tomto příkladu chceme dosáhnout průměrného objemu prodeje všech telefonů prodávaných v USA.
3. D2: D16 - Vztahuje se na rozsah údajů, aby se zjistilo, zda splňuje kritéria pro zahrnutí do průměrného vzorce.
4. „USA“ - Kritéria pro vyhledání v zadaném rozsahu dat
5. ) - Zavírací závorka označující konce vzorce.

Vzorec ukazuje, že jsme výrobek prodali pro okolí $ 1,008 na telefon v USA.

Všechny tři vzorce mohou mít více než jedno kritérium. Například pokud chceme znát stejné údaje (tzn. SPOČÍTAT, SOUČET a PRŮMĚRNÝ) u výrobků prodávaných v roce 2006. \ t USA, ale konkrétně pouze pro Samsung značky, musíme jen doplnit rozsah dat, který má být zkontrolován, následovaný jeho kritérii.

Viz příklad níže, kde je k počátečním kontrolám kritérií přidáno druhé kritérium. (Modrý text označuje první kritéria a červený označuje druhá kritéria)

= COUNTIFS (D2: D16, "USA", B2: B16, "Samsung") = SUMIFS (E2: E16, D2: D16, "USA", B2: B16, "Samsung") = AVERAGEIFS (E2: E16, D2: D16, "USA", B2: B16, "Samsung")

Všimněte si, že Excel má také COUNTIF, SUMIF a AVERAGEIF vzorce bez přípony „S“. Ty se používají podobně COUNTIFS, SUMIFS a PRŮMĚRNÉ. Ty však bez přípony „S“ ve vzorci mají omezení pouze umožnění jednoho kritéria pro vzorec.

Vzhledem k tomu, že syntaxe je poněkud odlišná, doporučuji použít COUNTIFS, SUMIFS a PRŮMĚRNÉ pokud je to nutné, může být použito pro jedno nebo více kritérií. Užívat si!