Jak používat funkci REDUCE v Microsoft Excelu

VBA bývalo jediným způsobem, jak v Microsoft Excel řešit iterativní a vícekrokovou logiku. Už to tak není. Funkce REDUCE přináší sílu funkcionálního programování přímo do vašich buněk, což vám umožní zhušťovat složitá a nepřehledná data do jediných, čistých výsledků.

Ať už odstraňujete nežádoucí znaky, nebo vytváříte dynamický vyhledávací engine, REDUCE vám umožňuje vytvářet sofistikované datové enginy, které se přizpůsobují růstu vašich tabulek.

Syntax REDUCE: Analogie sněhové koule

Funkce REDUCE může vypadat zastrašujícím dojmem, protože vyžaduje LAMBDA, ale logika je ve skutečnosti docela intuitivní, jakmile si ji vizualizujete. Zde je syntaxe:

=REDUCE(initial_value,array,LAMBDA(a,v,calculation))

Abychom pochopili, jak to funguje, představte si sněhovou kouli kutálející se z hory:

• počáteční_hodnota (volitelné): Toto je vaše výchozí hodnota. Je to původní velikost sněhové koule, než začne kutálet. Pokud toto pole necháte prázdné, Excel použije jako výchozí hodnotu první položku ve vašem poli.
• pole: Toto je sklon. Je to rozsah buněk, který chcete, aby funkcí procházely.
• LAMBDA(a,v,...): Toto je motor, který určuje, co má sněhová koule dělat v každém kroku. Akumulátor (a) je samotná sněhová koule, která přenáší výsledek z předchozího řádku do dalšího. Hodnota (v) je čerstvý sníh - konkrétní položka v aktuálním řádku, kterou funkce zpracovává.
• výpočet: Toto je logika. Říkáte Excelu, aby vzal sněhovou kouli (a), přidal nový sníh (v) a předal výsledek do dalšího kroku.

Důležité je si pamatovat, že REDUCE má paměť. Na rozdíl od standardních funkcí, které se dívají na každou buňku nezávisle, přesně ví, co se stalo v každém kroku.

Jednoduché rozcvičení: Použití REDUCE pro iterativní součet

Než vysvětlím, jak použít REDUCE k řešení „nemožných“ úloh, ukážu vám sněhovou kouli v akci na několika základních matematických příkladech. Zatímco byste normálně v následujícím příkladu použili funkci SUM, použití REDUCE pro sčítání seznamu čísel ukazuje, jak akumulátor (a) a hodnota (v) předávají štafetu.

Představte si, že máte formátovanou tabulku Excel nazvanou T_Numbers, která obsahuje číslice od 1 do 5.

Tady je vzorec pro jejich sečtení:

=REDUCE(0,T_Numbers,LAMBDA(a,v,a+v))

Toto se děje:

Funkce dokončí seznam a vrátí konečný výsledek 15.

V tomto jednoduchém příkladu byla sněhová koule číslem, které rostlo prostřednictvím sčítání. Pravá síla funkce REDUCE se však projeví, když si uvědomíte, že akumulátor (a) může uchovávat libovolný typ dat. V následujících příkladech z reálného světa vám ukážu, jak může akumulátor čistit textové řetězce nebo sledovat logické stavy.

Příklad z reálného světa 1: Dynamická vícepostavová striptérka

Představte si, že máte seznam kódů v tabulce Excel a chcete je vyčistit odstraněním všech pomlček, závorek, teček, znamének plus a mezer.

Můžete zvážit vložení funkcí SUBSTITUTE do sebe navzájem, abyste to udělali takto:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([@Code],"-",""),"(",""),")",""),".",""),"+","")," ","")

Tento vzorec je však těžko čitelný, náchylný k překlepům a neuvěřitelně křehký. Navíc, pokud se později rozhodnete také odstranit dvojtečky a hashtagy z kódů, budete muset přidat ještě více vnořených funkcí SUBSTITUTE a závorek.

S funkcí REDUCE můžete použít argument array k určení znaků, které chcete odstranit. To Excelu říká, aby prošel seznam symbolů a postupně vyčistil text po jednotlivých znacích.

Nejprve vytvořte tabulku nazvanou T_Chars a vyjmenujte všechny symboly, které chcete odstranit. Šestý řádek obsahuje mezeru.

Pak v hlavní tabulce zadejte následující vzorec a stiskněte Enter:

=REDUCE([@Code],T_Chars[Characters],LAMBDA(a,v,SUBSTITUTE(a,v,"")))

Takhle to funguje:

1. Počáteční hodnota (a): Začínáte s neuspořádaným kódem v aktuálním řádku ([@Code]). To je sněhová koule v její původní podobě.
2. Pole (v): To je váš sloupec T_Chars[Characters]. Excel prochází každý jednotlivý znak v seznamu, jeden po druhém.
3. Smyčka: V prvním kole REDUCE vezme kód a nahradí první znak ve sloupci T_Chars[Characters] (pomlčku) ničím (" "). Poté v druhém kole vezme tento již částečně vyčištěný kód a odstraní další znak (otevírací závorku). Funkce pokračuje tímto cyklem, dokud nevyčerpá všechny řádky ve vašem sloupci T_Chars[Characters].

Pokud se později rozhodnete, že chcete odstranit také dvojtečky a mřížky, jednoduše přidejte tyto znaky na konec sloupce T_Chars[Characters]. Stejně tak, pokud si uvědomíte, že chcete zachovat všechny závorky, odstraňte tyto řádky z tabulky.

Příklad z reálného světa 2: Vícekategoriální vyhledávač

Představte si, že spravujete inventární seznam a potřebujete označit jakoukoliv položku, jejíž popis obsahuje slova „Nebezpečné“, „Hořlavé“, „Křehké“, „Žíravé“ nebo „Toxické“.

Chcete-li to udělat pomocí vnořeného výrazu OR, museli byste sestavit složitý vzorec takto:

=OR(ISNUMBER(SEARCH("Hazardous",[@Description])),ISNUMBER(SEARCH("Flammable",[@Description])),ISNUMBER(SEARCH("Fragile",[@Description])))

I když to takto funguje, vzorec je téměř nesnesitelně dlouhý, a pokaždé, když potřebujete přidat nové klíčové slovo, musíte ručně otevřít vzorec, přidat nový řetězec ISNUMBER(SEARCH()) a vyvážit závorky.

Místo toho můžete s REDUCE vytvořit seznam klíčových slov pro argument array. Nastavte tedy tabulku s názvem T_Watchlist s jediným sloupcem Keyword a ve své tabulce produktů zadejte tento vzorec:

=REDUCE(FALSE,T_Watchlist[Keyword],LAMBDA(a,v,a+ISNUMBER(SEARCH(v,[@Description]))>0))

Takhle se to má:

1. Počáteční hodnota (a): Začínáte s FALSE — produkt je považován za bezpečný, dokud smyčka neprokáže opak.
2. Pole (v): Toto je váš sloupec T_Watchlist[Keyword]. Excel prochází každý jednotlivý výraz v seznamu, jeden po druhém.
3. Logika: ISNUMBER(SEARCH(v,[@Description])) vrací 1 (PRAVDA), pokud je nalezena shoda, nebo 0 (NEPRAVDA), pokud nalezena není. Vzorec pak tuto 1 nebo 0 přičítá k akumulátoru (a). Protože končí >0, v okamžiku, kdy smyčka najde shodu a akumulátor dosáhne 1, výsledek zůstává PRAVDA po zbytek procesu.

Pokud přidáte 10 nových klíčových slov do sloupce T_Watchlist[Keyword], funkce REDUCE automaticky zaregistruje nové řádky, spustí dodatečné smyčky a okamžitě aktualizuje vaše příznaky. Díky tomu přecházíte z vnořené OR formule, která vyžaduje dohled, na REDUCE formuli, která se vyvíjí podle změny vašich kritérií.

Proč používat REDUCE místo VBA?

Možná vás napadne, proč byste pro tyto úkoly neměli jednoduše použít makro VBA nebo Power Query. I když jsou tyto nástroje silné, REDUCE nabízí tři výhody:

• Nativní podpora cloudu: Makra VBA nefungují v Excelu pro web ani v mobilních a tabletových aplikacích Excelu. Pokud sdílíte sešit s makry s někým, kdo jej otevře v prohlížeči, vaše řešení přestane fungovat. REDUCE je nativní funkce, která funguje ve všech nejnovějších verzích Excelu bez ohledu na zařízení nebo platformu.
• Žádné bezpečnostní překážky: Mnoho IT oddělení blokuje soubory XLSM (s makry) kvůli bezpečnostním rizikům, která mohou představovat. Protože REDUCE je uložen uvnitř standardního souboru XLSX, můžete si být jisti, že nebude blokován firewally ani nezpůsobí žádná bezpečnostní varování.
• Okamžité přepočítání: Power Query je skvělé pro obrovské sady dat, ale k zobrazení změn vyžaduje manuální aktualizaci. REDUCE je však reaktivní. Jakmile přidáte nové klíčové slovo do svého seznamu sledovaných položek nebo opravíte překlep v kódu produktu, výsledky se okamžitě aktualizují.

SNIŽOVÁNÍ nemusí být vždy řešení

I když je REDUCE neuvěřitelně výkonný, není to univerzální řešení pro každou situaci. Existují dvě situace, kdy byste měli zvážit jiné nástroje:

• Obrovské datové sady: Protože funkce REDUCE provádí výpočetní smyčku pro každou buňku, může výrazně zpomalit váš list, pokud spouštíte zvlášť složité smyčky nebo pracujete s velmi velkými datovými sadami. V těchto případech je obvykle nejlepší použít Power Query.
• Komplexní ladění: Na rozdíl od VBA, kde můžete procházet kód řádek po řádku, REDUCE postrádá specializované nástroje pro krokové ladění. I když můžete testovat malé části vzorce, je mnohem obtížnější přesně určit, který řádek způsobil chybu v dlouhé smyčce.

Přechodem od vnořených řetězců k čisté logice REDUCE své vzorce zkrátíte a zpřehledníte. Ve skutečnosti je REDUCE součástí tria výkonných funkcí LAMBDA navržených pro práci s poli v rámci tabulky. MAP je jako výrobní linka - použijte ji, když chcete aplikovat stejnou logiku na každou buňku v rozsahu a vrátit nový seznam stejné velikosti jako původní. SCAN je spíše jako průběžná zpráva - je ideální funkcí pro vytváření běžícího součtu nebo postupné historie výpočtu.