V tomto článku se naučíme Jak používat funkci IF místo funkce SUMPRODUCT a SUMIFS v Excelu.
Scénář:
Jednoduše řečeno, při práci s dlouhou rozptýlenou datovou sadou někdy potřebujeme najít součet čísel s určitými kritérii. Například nalezení součtu platů v konkrétním oddělení nebo více kritérií pro datum, jména, oddělení nebo dokonce čísla, jako jsou platy pod hodnotou nebo množství nad hodnotou. K tomu obvykle používáte funkci SUMPRODUCT nebo SUMIFS. Ale nevěřili byste, stejnou funkci provádíte s funkcí IF základní funkce IF.
Jak problém vyřešit?
Určitě přemýšlíte, jak je to možné, provádět logické operace přes pole tabulky pomocí funkce IF. Pokud je funkce v Excelu velmi užitečná, provede vás některými obtížnými úkoly v aplikaci Excel nebo v jiných kódovacích jazycích. Funkce IF testuje podmínky na poli odpovídajícím požadovaným hodnotám a vrací výsledek jako pole odpovídající podmínkám True jako 1 a False jako 0.
Pro tento problém budeme používat následující funkce:
- Funkce SUM
- IF funkce
Budeme vyžadovat tyto výše uvedené funkce a nějaký základní smysl pro provoz dat. logické podmínky na polích lze aplikovat pomocí logických operátorů. Tyto logické operátory pracují na textu i číslech. Níže je obecný vzorec. { } curly braces je kouzelný nástroj k provádění maticových vzorců s funkcí IF.
Obecný vzorec:
| { = SUM (IF ((logický_1) * (logický_2) *… * (logický_n), součet_pole)) } |
Poznámka: Pro složená rovnátka ( { } ) Použijte Ctrl + Shift + Enter při práci s poli nebo rozsahy v Excelu. To ve výchozím nastavení ve vzorci vygeneruje složené závorky. NEPOKOUŠEJTE se tvrdě kódovat složené závorky.
Logická 1: testuje podmínku 1 na poli 1
Logická 2: testuje podmínku 2 na poli 2 a tak dále
sum_array: pole, provede se součet operací
Příklad:
Pochopit to všechno může být matoucí. Pojďme tedy tento vzorec otestovat spuštěním na níže uvedeném příkladu. Zde máme data o dodaných produktech do různých měst spolu s odpovídajícími poli kategorií a množstvím. Zde máme data a potřebujeme najít množství cookies odeslaných do Bostonu, kde je množství větší než 40.
Tabulka dat a tabulka kritérií jsou uvedeny na výše uvedeném obrázku. Pro účely porozumění jsme pro pojmenovaná pole použili pojmenované rozsahy. Pojmenované rozsahy jsou uvedeny níže.
Tady :
Město definované pro pole A2: A17.
Kategorie definovaná pro pole B2: A17.
Množství definované pro pole C2: C17.
Nyní jste připraveni získat požadovaný výsledek pomocí níže uvedeného vzorce.
Použijte vzorec:
| { = SUM (IF ((město = "Boston") * (kategorie = "cookies") * (množství> 40), množství)) } |
Vysvětlení :
- City = "Boston": zkontroluje hodnoty v rozsahu města, aby odpovídaly "Boston".
- Kategorie = "Cookies": kontroluje, zda se hodnoty v kategorii Kategorie shodují s "Cookies".
- Množství> 40: zkontroluje hodnoty v rozsahu množství na ma
- Množství je pole, kde je požadován součet.
- Funkce IF kontroluje všechna kritéria a hvězdička char (*) znásobuje všechny výsledky pole.
= SUM (IF ({0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0}, {33; 87; 58; 38; 54; 51; 28; 36; 28; 44; 23; 27; 43; 42; 33; 30}))
- Nyní funkce IF vrací pouze množství odpovídající 1 s a zbytek jsou ignorovány.
- Funkce SUM vrací SUM.
Nyní se množství odpovídající 1 sčítá, aby se získal výsledek.
Jak vidíte, množství 43 je vráceno, ale do „Bostonu“ byly doručeny tři objednávky cookies s množstvím 38, 36 a 43. Potřebovali jsme součet množství, kde je množství vyšší než 40. Vzorec tedy vrací pouze 43. Nyní použijte jiná kritéria a získejte SUM Quantity for City: „Los Angeles“ & Kategorie: „Bars“ & Quantity be less than 50.
Použijte vzorec
| { = SUM (IF ((Město = "Los Angeles") * (Kategorie = "Bary") * (Množství <50), Množství)) } |
Jak vidíte, vzorec vrací jako výsledek hodnoty 86. Což je součet 2 objednávek splňujících podmínky s množstvím 44 a 42. Tento článek ukazuje, jak nahradit vnořený vzorec IF jediným IF ve vzorci pole. Toho lze využít ke snížení složitosti složitých vzorců. Tento konkrétní problém však lze snadno vyřešit pomocí funkce SUMIFS nebo SUMPRODUCT.
Použití funkce SUMPRODUCT:
Funkce SUMPRODUCT vrací součet odpovídajících hodnot v poli. Získáme tedy pole, která vrátí hodnoty 1s a True a hodnoty 0s False. Poslední částka tedy bude odpovídat tomu, kde všechna tvrzení platí.
Použijte vzorec:
| = SUMPRODUCT ( - (Město = "Boston"), - (Kategorie = "Cookies"), - (Množství> 40), Množství) |
-: operace používaná k převodu všech PRAVD na 1 s a FALSE na 0.
Funkce SUMPRODUCT znovu kontroluje SUM množství vráceného funkcí SUM a IF vysvětlenou výše.
Podobně pro druhý příklad je výsledek stejný.
Jak vidíte, funkce SUMPRODUCT může provádět stejný úkol.
Zde jsou všechny pozorovací poznámky týkající se používání vzorce.
Poznámky:
- Pole sum_array ve vzorci funguje pouze s čísly.
- Pokud vzorec vrací chybu #HODNOTA, musí být ve vzorci uvedena kontrola složených závorek, jak je uvedeno v příkladech v článku.
- Negace (-) znak změní hodnoty, PRAVDA nebo 1 s na NEPRAVDA nebo 0 s a NEPRAVDA nebo 0 s na PRAVDA nebo 1 s.
- Operace jako rovná se ( = ), menší než rovno ( <= ), větší než ( > ) nebo se nerovná () lze provést v rámci použitého vzorce, pouze s čísly.
Doufám, že tento článek o tom, jak používat funkci IF místo funkce SUMPRODUCT a SUMIFS v aplikaci Excel, je vysvětlující. Další články o součtových vzorcích najdete zde. 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 používat funkci SUMPRODUCT v Excelu: Vrací SUMU po vynásobení hodnot ve více polích v Excelu.
SUM, pokud je datum mezi : Vrátí SUMU hodnot mezi danými daty nebo obdobím v Excelu.
Součet, pokud je datum větší než dané datum: Vrátí SUMU hodnot po daném datu nebo období v aplikaci Excel.
2 způsoby součtu podle měsíce v Excelu: Vrátí SUMU hodnot v rámci daného konkrétního měsíce v aplikaci Excel.
Jak sečíst více sloupců s podmínkou: Vrátí SUMU hodnot napříč více sloupci s podmínkou v Excelu.
Oblíbené články:
50 Zkratka aplikace Excel pro zvýšení produktivity : Získejte rychleji svůj úkol. Těchto 50 klávesových zkratek vám umožní pracovat v Excelu ještě rychleji.
Jak používat tFunkce 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.