Jednou z běžných otázek na fóru je, jak používat funkci SumIf se dvěma různými podmínkami. Odpověď bohužel zní, že SumIf nedokáže zpracovat dvě různé podmínky.
Chcete-li udělat dvě podmínky, musíte použít poměrně komplikovaný maticový vzorec. Doplněk Průvodce podmíněným součtem umožňuje snadné zadávání těchto složitých vzorců.
Zde je list aplikace Excel se sloupci pro produkt, obchodní zástupce a prodej. Data jsou v buňkách A2: C29.
Pokud chcete součet prodejů, bude fungovat jednoduchá funkce SUM (). =SUM(C2:C29).
Mnoho Excelentů objevuje funkci SumIf. Pomocí této funkce je poměrně snadné zjistit celkový prodej produktu ABC.=SUMIF(A2:A29,E2,C2:C29)
Je také snadné zjistit celkový prodej uskutečněný prodejním zástupcem Joe =SUMIF(B2:B29,E2,C2:C29).
Pak byste předpokládali, že je možné zjistit celkový prodej produktu ABC provedeného Joeem. Neexistuje však žádný způsob, jak to provést pomocí funkce SumIf. Ukázalo se, že musíte použít poměrně složité pole nebo vzorec CSE.
Přiznejme si to - vzorec Sum je Excel 101. Vzorec SumIf není ve složitosti pozadu. Vzorec CSE pro výpočet celkového prodeje ABC uskutečněného Joeem však stačí k tomu, aby se mi dokonce roztočila hlava.
Dobrá zpráva - Microsoft nabízí Průvodce podmíněným součtem, který umožňuje i nováčkovi zadávat složité podmíněné vzorce založené na 1, 2 nebo více podmínkách. Průvodce podmíněným součtem je doplněk. Chcete-li tuto funkci přidat do aplikace Excel, přejděte do nabídky Nástroje a vyberte doplňky. V dialogovém okně Doplňky zaškrtněte políčko vedle Průvodce podmíněným součtem a klikněte na OK. Je možné, že v tomto okamžiku budete potřebovat instalační CD, protože Microsoft nezahrnuje průvodce do výchozí instalace.
Jakmile je doplněk úspěšně zapnut, v dolní části nabídky Nástroje bude volba Podmíněná suma….
Vyberte jednu buňku v datové sadě a zvolte Nástroje - Podmíněný součet. Za předpokladu, že jsou vaše data pěkně naformátována pomocí jediného řádku záhlaví, Excel správně odhadne rozsah vašich dat. Zvolte Další.
V kroku 2 vyberte sloupec, který chcete sčítat. V tomto případě průvodce už hádal, že chcete sečíst první (a jediný) číselný sloupec - Prodej. Uprostřed dialogového okna jsou tři ovládací prvky rozevíracího seznamu. Stává se, že jsou správné pro první podmínku - Produkt se rovná ABC, takže zvolte tlačítko Přidat podmínku.
Pak můžete přidat druhou podmínku. V tomto případě chcete určit, že prodejním zástupcem je Joe. Vyberte šipku pro první rozevírací nabídku. Excel nabízí abecední seznam dostupných názvů sloupců. Zvolte obchodní zástupce
Rozevírací seznam ve středu je správný, ale pro úplnost zde vidíte, že jste si mohli vybrat rovné, menší než, větší než, menší než nebo stejné, větší než nebo stejné nebo ne stejné.
Ve třetím rozevíracím seznamu vyberte Joe.
Zvolte tlačítko Přidat podmínku.
Nyní jste připraveni přejít na krok 3. Stiskněte tlačítko Další.
V kroku 3 máte dvě možnosti. Při první volbě průvodce zadá jeden vzorec s hodnotami „ABC“ a „Joe“ pevně zakódovanými do vzorce. Dá vám odpověď, ale nebude možné snadno změnit vzorec. Při druhé volbě Excel nastaví novou buňku s hodnotou „ABC“ a novou buňku s hodnotou „Joe“. Třetí buňka bude obsahovat vzorec, který provede podmíněný součet založený na těchto dvou hodnotách. S touto možností můžete do buněk zadat nové hodnoty, abyste viděli celkový počet XYZ prodaných Adamem.
Průvodce se poté zeptá, kde chcete hodnotu pro ABC. Vyberte buňku a zvolte Další. Opakujte, když vás Průvodce požádá o výběr buňky pro Joea a vzorec.
Když v posledním kroku zvolíte Dokončit, Excel vytvoří mírně odlišnou (ale platnou) verzi vzorce CSE.
Tento vzorec počítá, že Joe prodal 33 338 $ ABC.
Pokud změníte vstupní buňku produktu z ABC na DEF, vzorec se přepočítá a ukáže, že Joe prodal 24 478 USD z DEF.
Průvodce podmíněným součtem poskytuje komplexní vzorce dobře na dosah všech vlastníků aplikace Excel.
Dodatečné informace:Chcete-li vytvořit tabulku, která bude ukazovat prodej každého produktu každým prodejním zástupcem, je třeba o těchto vzorcích vědět nějaké speciální „ošetřování a krmení“. Zadejte každého obchodního zástupce do horní části rozsahu. Zadejte každý produkt do levého sloupce rozsahu. Upravte vzorec poskytnutý průvodcem. Na obrázku níže vzorec ukazuje na produkt v buňce E6. Tato reference opravdu musí být $ E6. Pokud necháte odkaz jako E6 a zkopírujete vzorec do sloupce G, vzorec by se díval na F6 místo E6 a to by bylo špatné. Přidáním znaku dolaru před E v E6 zajistíte, aby vzorec vždy sledoval produkt ve sloupci E. Vzorec také ukazuje na obchodního zástupce v buňce F5. Tato reference opravdu musí být 5 $. Pokud jste nechali odkaz jako F5 a zkopírovali dolů do řádku 7,reference F5 se změní na F6 a to není správné. Přidáním znaku dolaru před číslo řádku uzamknete číslo řádku a odkaz bude vždy ukazovat na řádek 5.
V režimu úprav (vyberte buňku a upravte ji stisknutím klávesy F2) zadejte $ před E. Zadejte znak dolaru před 5 ve F5. Ještě nestiskněte Enter!
Tento vzorec je speciální typ vzorce. Pokud stisknete Enter, dostanete 0, což není správné.
Místo psaní Enter podržte při stisknutí klávesy Enter klávesy Ctrl a Shift. Tato magická kombinace C trl + S hift + E nter je důvod, proč nazývám tyto vzorce CSE.
Před zkopírováním vzorce do zbytku tabulky je ještě jedna poslední úvaha. Váš sklon může být kopírování F6 a vložení do F6: G8. Pokud to zkusíte, Excel vám dá záhadnou zprávu „Nelze změnit část pole“. Excel si stěžuje, že nemůžete vložit vzorec CSE do rozsahu, který obsahuje původní vzorec CSE.
Je snadné to obejít. Zkopírujte F6. Vložit do F7: F8.
Kopie F6: F8. Vložte do G6: G8. Budete mít tabulku vzorců CSE zobrazujících součty na základě dvou podmínek.
