Excel: Správně vypočítat podmíněné částky
V Excelu můžete vypočítat částky, které nezohledňují všechny prvky vybrané oblasti - tzv. Podmíněné součty. Ukážeme vám, jak provést nejrozumnější výpočet.
Tři různé funkce pro podmíněné součty v Excelu
Excel nabízí různé možnosti a funkce pro podmíněné součty. Správná volba závisí na typu podmínek:
- Pokud se na každou dotyčnou hodnotu vztahuje pevná podmínka, použijte funkci SUMIF.
- Je-li třeba vzít v úvahu několik podmínek, existuje také funkce SUMIFS od Excelu 2007.
- U proměnných podmínek však potřebujete maticový vzorec nebo funkci SUM PRODUKT.
- V následujících odstavcích vysvětlujeme tři funkce pomocí příkladů.
SUMIF: Podmíněné částky s pevnými podmínkami
- Předpokládejme, že data jsou v buňkách A1 až A10 a různé hodnoty v buňkách B1 až C10, které se používají pro podmínky.
- První úkol s pevnou podmínkou pak znamená například součet všech hodnot ve sloupci A, pro které je příslušná hodnota ve sloupci B přibližně větší než 10.
- K tomu použijte vzorec »= SUMIF (B1: B10;"> 10 "; A1: A10)".
- První argument »B1: B10« definuje oblast, která má být hodnocena, následovaná podmínkou »"> 10 "«. Upozorňujeme, že podmínky se symboly nebo operátory musí být v uvozovkách.
- Nakonec »A1: A10« definuje oblast, která se má sečíst.
- Rozsahy pro podmínky a data mohou být také totožné, takže vzorec »= SUMIF (A1: A10;"> 10 ")" je dostačující.
SUM IF IF: Podmíněné částky s více podmínkami
Od Excelu 2007 rozšiřuje funkce SUMIFS tento princip na několik až maximálně 127 podmínek:
- Všimněte si jiného pořadí argumentů. Oblast, která se má sčítat, je zde první. Následuje oblast kritérií a související stav, přičemž oblasti musí být vždy specifikovány samostatně.
- Například vzorec "= SUMIFS (A1: A10; B1: B10;"> 10 "; C1: C10;" <100 ")" shrnuje všechny hodnoty ve sloupci A, pro které jsou odpovídající hodnoty ve sloupci B větší než 10 a odpovídající hodnoty ve sloupci C jsou menší než 100.
SUM SUM PRODUCT: Podmíněné součet s proměnnými podmínkami
Proměnná kritéria, jako například „pokud je hodnota ve sloupci B větší než ve sloupci C“, vyžadují maticový vzorec pro vyhodnocení:
- Hodnoty, které je třeba vzít v úvahu, se vyberou vynásobením logickou hodnotou.
- Chcete-li to provést, zadejte asi "= SUM (A1: A10 * (B1: B10> C1: C10))" "a vždy uzavřete zadání, a to i po změně kombinací kláves [Ctrl] + [Shift] + [Enter], takže vzorec je ve složených závorkách.
- V takovém případě můžete dosáhnout stejného výsledku pomocí funkce "= SOUHRNNÝ VÝROBEK (A1: A10; 1 * (B1: B10> C1: C10))".
- Musíte však poznamenat, že musíte nejprve znásobit druhý argument hodnotou 1, abyste převedli logickou hodnotu porovnání na číslo.
Poznámka: Prakticky ve všech případech můžete jasně vytvořit součet pomocným sloupcem. To je také vhodné při vývoji výpočtu pro kontrolu výsledků složitých vzorců. Tip: Zde vysvětlíme, jak přidat mnoho buněk v Excelu.