Minulý týden jsme si ve videu ukázali, že složené závorky umí ve funkci SUMIFS nebo COUNTIFS nahradit podmínku NEBO. Dnes vám ale ukážu trik, který zná jen minimum uživatelů Excelu. Stačí totiž změnit jeden znak ve složených závorkách a Excel najednou přestane počítat dvě podmínky a začne vytvářet všechny jejich kombinace.
Excelový soubor ke stažení:
Náš úkol je jednoduchý. Musíme spočítat celkové tržby pro produkty A nebo B, které se prodaly v Praze nebo Brně. Co tím máme na mysli? Chceme spočítat celkové tržby pro kombinaci produkty A, které se prodaly v Praze nebo v Brně a produkty B, které se prodaly v Praze nebo v Brně. Tedy čtyři možné kombinace.
Většina uživatelů Excelu by v této situaci sáhla po funkci SUMIFS. Jak bychom tento výpočet pomocí funkce SUMIFS dosáhli? Museli bychom pro čtyři kombinace použít čtyři funkce SUMIFS, které bychom sečetli.
V první funkci SUMIFS sečteme tržby, které náleží produktům A, které se prodaly v Praze. Ve druhé funkci SUMIFS sečteme tržby, které náleží produktům A, které se prodaly v Brně. Ve třetí funkci SUMIFS sečteme tržby, které náleží produktům B, které se prodaly v Praze a ve čtvrté funkci SUMIFS sečteme tržby, které náleží produktům B, které se prodaly v Brně. Tím získáme správný výsledek. A je to nejspíše postup, který by uvolila většina uživatelů Excelu.
Někdo by ovšem mohl chtít použít postup z minulého videa se složenými závorkami a zde by narazil na problém. Nejprve si ukážeme, jak by vůbec vypadal zápis se složenými závorkami. Ve funkci SUMIFS bychom nejprve sečetli tržby, a to pro produkty A nebo B a pro zápis vyjádření nebo bychom použili složené závorky. Tento zápis v podstatě spočítá celkové tržby pro produkty A i pro produkty B. První je součet tržeb pro produkt A druhé číslo je součet pro produkt B.
A pak by nás mohlo napadnout pokračovat a sečíst tržby pro pobočky a opět použít stejný zápis. Tedy, že ve složených závorkách uvedeme jak pobočku Praha, tak Brno. Jelikož víme, že tento zápis vrací více hodnot, tak tyto hodnoty musíme sečíst pomocí funkce SUMA. Starší verze Excelu rovněž tento zápis musí potvrdit stisknutím kláves CTRL+SHIFT a ENTER. Novějším Excelům stačí pro potvrzení klávesa ENTER.
Nicméně tento zápis vrátí po potvrzení jiný výsledek, který je chybný. Víte, co tento zápis ve funkci SUMIFS sečetl?
Tento zápis vrátil celkové tržby pro produkty A, které se prodaly v Praze a pro produkty B, které se prodaly v Brně. Jinými slovy, tento zápis vzal kombinace prvního produktu a první pobočky a druhého produktu a druhé pobočky. A důvodem je to, že jsme v obou složených závorkách použili pro oddělení hodnot středník. Středník v české verzi Excelu odděluje řádky. Když odběhneme od tohoto příkladu a podíváme se na následující zápis, tak pochopíme proč. Když do libovolné buňky ve složených závorkách napíšu {1;2;3} tak se vrátí tři řádky s těmito hodnotami. A je to proto, že v matici středník odděluje hodnoty do řádků. Tím pádem v zápisu {„A“;“B“} a {„Praha“;“Brno“} vznikla dvě svislá pole vedle sebe, a proto se spároval produkt A a Praha a produkt B a Brno. Excel v tomto případě pracuje s hodnotami na stejných pozicích.
První prvek prvního pole se spojí s prvním prvkem druhého pole:
A a Praha
Druhý prvek prvního pole se spojí s druhým prvkem druhého pole:
B a Brno
Tento výpočet je v této podobě pro naše účely tedy chybný. Ale neznamená to, že tento zápis nemůžeme vůbec použít. A právě zde spočívá trik.
Použijeme jen jednu drobnou změnu a tento výpočet začne fungovat. Začneme znovu. Napíšeme funkci SUMIFS, kde nejprve jako oblast součtu označíme sloupec s tržbami. Jako první oblast kritérií označíme sloupec s produkty a v parametru kritérium použijeme složené závorky. Ve složených závorkách stanovíme kritéria a oddělíme je středníkem. V tomto případě tedy {„A“;“B“}. A budeme pokračovat. Jako druhou oblast kritérií označíme sloupec s pobočkami a do parametru kritérium uvedeme opět složené závorky. A v těchto složených závorkách opět uvedeme kritéria, ale tentokrát je oddělíme obráceným lomítkem. Tedy {„Praha“\“Brno“}.
Potvrdíme a tento zápis s touto drobnou změnou vrací správný výsledek. Proč? Obrácené lomítko v české verzi Excelu odděluje sloupce. Tato drobná změna v zápisu způsobila, že nevznikla dvě svislá pole ale dvě různá pole. Produkty jsou oddělené středníkem, a tudíž pod sebou v řádcích a pobočky jsou oddělené obráceným lomítkem, a tudíž ve sloupcích. Právě tento rozdíl v orientaci je základem celého triku. Excel rozlišuje, zda jsme hodnoty uspořádali pod sebe, nebo vedle sebe. Microsoft popisuje konstanty polí jako jednorozměrná či dvourozměrná pole, přičemž použitý oddělovač určuje, zda vzniknou řádky, nebo sloupce.
Excel v tomto případě už nemůže jednoduše spárovat první položku s první a druhou s druhou, protože pole nemají stejný tvar.
Jedno pole představuje dva řádky:
A
B
Druhé představuje dva sloupce:
Praha Brno
Excel proto vytvoří výslednou matici, která má dva řádky a dva sloupce.
Řádky převezme z prvního pole a sloupce z druhého pole:
Každá buňka této výsledné matice představuje jednu kombinaci produktu a regionu.
Tento zápis se tedy ve zkrácené formě rovná přesně prvnímu zápisu, kde jsme sčítali čtyři funkce SUMIFS. A to jen díky tomu, že jsme změnily směr matice u druhé složené závorky.
