Obsah
Co jsou dynamická pole
V září 2018 Microsoft vydal aktualizaci, která do Microsoft Excelu přidává zcela nový nástroj: Dynamic Arrays a 7 nových funkcí pro práci s nimi. Tyto věci bez nadsázky radikálně mění veškerou obvyklou techniku práce se vzorci a funkcemi a týkají se doslova každého uživatele.
Zvažte jednoduchý příklad pro vysvětlení podstaty.
Předpokládejme, že máme jednoduchou tabulku s údaji o městech a měsících. Co se stane, když vybereme libovolnou prázdnou buňku napravo na listu a zadáme do ní vzorec, který se neváže na jednu buňku, ale rovnou na rozsah?
Ve všech předchozích verzích Excelu po kliknutí na vstoupit dostali bychom obsah pouze jedné první buňky B2. Jak jinak?
No, nebo by bylo možné zabalit tento rozsah do nějaké agregační funkce jako =SUM(B2:C4) a získat za to celkový součet.
Pokud bychom potřebovali složitější operace než primitivní součet, jako je extrahování jedinečných hodnot nebo Top 3, museli bychom náš vzorec zadat jako maticový vzorec pomocí klávesové zkratky Ctrl+směna+vstoupit.
Nyní je vše jinak.
Nyní po zadání takového vzorce můžeme jednoduše kliknout na vstoupit – a jako výsledek okamžitě získáte všechny hodnoty uXNUMXbuXNUMXb, na které jsme odkazovali:
To není kouzlo, ale nová dynamická pole, která nyní má Microsoft Excel. Vítejte v novém světě 🙂
Vlastnosti práce s dynamickými poli
Technicky je celé naše dynamické pole uloženo v první buňce G4 a svými daty zaplňuje požadovaný počet buněk doprava a dolů. Pokud vyberete jakoukoli jinou buňku v poli, odkaz na řádku vzorců bude neaktivní, což ukazuje, že jsme v jedné z „podřízených“ buněk:
Pokus o smazání jedné nebo více „dětských“ buněk k ničemu nepovede – Excel je okamžitě přepočítá a vyplní.
Zároveň můžeme bezpečně odkazovat na tyto „dětské“ buňky v jiných vzorcích:
Pokud zkopírujete první buňku pole (například z G4 do F8), bude se celé pole (jeho reference) pohybovat stejným směrem jako v běžných vzorcích:
Pokud potřebujeme pole přesunout, bude stačit přesun (myší nebo kombinací Ctrl+X, Ctrl+V), opět pouze první hlavní buňka G4 – po ní se přesune na nové místo a celé naše pole se opět rozšíří.
Pokud potřebujete na vytvořené dynamické pole odkazovat někde jinde na listu, můžete za adresou jeho úvodní buňky použít speciální znak # („libra“):
Nyní můžete například snadno vytvořit rozevírací seznam v buňce, která odkazuje na vytvořené dynamické pole:
Chyby dynamického pole
Co se ale stane, pokud není dostatek místa pro rozšíření pole nebo pokud jsou v jeho cestě buňky již obsazené jinými daty? Seznamte se se zásadně novým typem chyb v Excelu – #PŘEVOD! (#ROZLÍT!):
Jako vždy, pokud klikneme na ikonu se žlutým kosočtvercem a vykřičníkem, dostaneme podrobnější vysvětlení zdroje problému a můžeme rychle najít rušící buňky:
K podobným chybám dojde, pokud pole zmizí z listu nebo narazí na sloučenou buňku. Pokud překážku odstraníte, vše bude okamžitě opraveno.
Dynamická pole a chytré tabulky
Pokud dynamické pole ukazuje na „chytrou“ tabulku vytvořenou klávesovou zkratkou Ctrl+T nebo Domů – Formát jako tabulka (Domů — Formátovat jako tabulku), pak také zdědí svou hlavní kvalitu – automatickou velikost.
Při přidávání nových dat dolů nebo doprava se automaticky rozšíří také chytrá tabulka a dynamický rozsah:
Existuje však jedno omezení: nemůžeme použít odkaz na dynamický rozsah ve fóru uvnitř inteligentní tabulky:
Dynamická pole a další funkce Excelu
Dobře, říkáš. To vše je zajímavé a vtipné. Není třeba, jako dříve, ručně roztahovat vzorec odkazem na první buňku původního rozsahu dolů a doprava a tak dále. A to je vše?
Ne tak docela.
Dynamická pole nejsou jen dalším nástrojem v Excelu. Nyní jsou zabudovány do samotného srdce (neboli mozku) Microsoft Excel – jeho výpočetního stroje. To znamená, že práci s dynamickými poli nyní podporují i další vzorce a funkce Excelu, které známe. Pojďme se podívat na několik příkladů, abyste si udělali představu o hloubce změn, ke kterým došlo.
Přemístit
Pro transpozici rozsahu (prohození řádků a sloupců) měl Microsoft Excel vždy vestavěnou funkci TRANSP (PŘEMÍSTIT). Abyste ji však mohli použít, musíte nejprve správně vybrat rozsah pro výsledky (např. pokud byl zadáním rozsah 5×3, pak musíte mít vybráno 3×5), poté funkci zadat a stisknout tlačítko kombinace Ctrl+směna+vstoupit, protože to mohlo fungovat pouze v režimu vzorců pole.
Nyní stačí vybrat jednu buňku, zadat do ní stejný vzorec a kliknout na normální vstoupit – dynamické pole udělá vše samo:
Násobilka
Toto je příklad, který jsem uvedl, když jsem byl požádán, abych vizualizoval výhody maticových vzorců v aplikaci Excel. Nyní pro výpočet celé Pythagorejské tabulky stačí postavit se do první buňky B2, zadat tam vzorec, který vynásobí dvě pole (svislá a vodorovná sada čísel 1..10) a jednoduše kliknout na vstoupit:
Lepení a přestavba pouzdra
Pole lze nejen násobit, ale také lepit dohromady pomocí standardního operátoru & (ampersand). Předpokládejme, že potřebujeme extrahovat jméno a příjmení ze dvou sloupců a opravit přeskakování v původních datech. Uděláme to pomocí jednoho krátkého vzorce, který tvoří celé pole, a poté na něj aplikujeme funkci PROPNACH (SPRÁVNÉ)udělat pořádek v registru:
Závěr Top 3
Předpokládejme, že máme spoustu čísel, ze kterých chceme odvodit tři nejlepší výsledky a uspořádat je v sestupném pořadí. Nyní se to děje jedním vzorcem a opět bez jakéhokoli Ctrl+směna+vstoupit jako předtím:
Pokud chcete, aby výsledky nebyly umístěny do sloupce, ale do řádku, pak stačí v tomto vzorci nahradit dvojtečky (oddělovač řádku) středníkem (oddělovač prvků v rámci jednoho řádku). V anglické verzi Excelu jsou tyto oddělovače středníky a čárky.
VLOOKUP extrahování více sloupců najednou
Funkce VPR (VYHLEDAT) nyní můžete vytáhnout hodnoty ne z jednoho, ale z několika sloupců najednou – stačí zadat jejich čísla (v libovolném pořadí) jako pole ve třetím argumentu funkce:
Funkce OFFSET vrací dynamické pole
Jednou z nejzajímavějších a nejužitečnějších funkcí (po VLOOKUP) pro analýzu dat je funkce LIKVIDACE (POSUN), kterému jsem svého času věnoval celou kapitolu ve své knize a článek zde. Potíž s pochopením a zvládnutím této funkce byla vždy v tom, že ve výsledku vracela pole (rozsah) dat, ale my jsme to neviděli, protože Excel stále nevěděl, jak s poli po vybalení pracovat.
Nyní je tento problém minulostí. Podívejte se, jak nyní můžete pomocí jediného vzorce a dynamického pole vráceného OFFSET extrahovat všechny řádky pro daný produkt z libovolné seřazené tabulky:
Pojďme se podívat na její argumenty:
- A1 – počáteční buňka (referenční bod)
- ПОИСКПОЗ(F2;A2:A30;0) – výpočet posunu od výchozí buňky dolů – k prvnímu nalezenému zelí.
- 0 – posun „okna“ doprava vzhledem k počáteční buňce
- СЧЁТЕСЛИ(A2:A30;F2) – výpočet výšky vráceného „okna“ – počet řádků, kde je zelí.
- 4 — velikost „okna“ horizontálně, tj. výstup 4 sloupce
Nové funkce pro dynamická pole
Kromě podpory mechanismu dynamických polí ve starých funkcích bylo do aplikace Microsoft Excel přidáno několik zcela nových funkcí, které byly speciálně upraveny pro práci s dynamickými poli. Konkrétně se jedná o:
- GRADE (ŘADIT) – seřadí vstupní rozsah a na výstupu vytvoří dynamické pole
- SORTPO (SEŘAZENO PODLE) – může seřadit jeden rozsah podle hodnot od jiného
- FILTER (FILTR) – načte řádky ze zdrojového rozsahu, které splňují zadané podmínky
- UNIK (UNIKÁTNÍ) – extrahuje jedinečné hodnoty z rozsahu nebo odstraní duplikáty
- SLMASSIVE (RANDARRAY) – generuje pole náhodných čísel dané velikosti
- PO NAROZENÍ (SEKVENCE) — tvoří pole z posloupnosti čísel s daným krokem
Více o nich – o něco později. K promyšlenému studiu stojí za samostatný článek (a ne jeden) 🙂
Závěry
Pokud jste si přečetli vše napsané výše, pak si myslím, že si již uvědomujete rozsah změn, ke kterým došlo. Tolik věcí v Excelu lze nyní dělat snadněji, snadněji a logičtěji. Musím přiznat, že jsem trochu šokován, kolik článků bude nyní muset být opraveno zde, na tomto webu a v mých knihách, ale jsem připraven to udělat s lehkým srdcem.
Shrneme-li výsledky, плюсы dynamická pole, můžete napsat následující:
- Na kombinaci můžete zapomenout Ctrl+směna+vstoupit. Excel nyní nevidí žádný rozdíl mezi „běžnými vzorci“ a „polovými vzorci“ a zachází s nimi stejným způsobem.
- O funkci SUMPRODUCT (SUMPRODUKT), který se dříve používal k zadávání maticových vzorců bez Ctrl+směna+vstoupit můžete také zapomenout – nyní je to snadné SOUČET и vstoupit.
- Inteligentní tabulky a známé funkce (SUM, IF, VLOOKUP, SUMIFS atd.) nyní plně nebo částečně podporují dynamická pole.
- Existuje zpětná kompatibilita: pokud otevřete sešit s dynamickými poli ve staré verzi Excelu, změní se na maticové vzorce (ve složených závorkách) a budou nadále fungovat ve „starém stylu“.
Našel nějaké číslo minusy:
- Z dynamického pole nelze odstranit jednotlivé řádky, sloupce nebo buňky, tj. žije jako jedna entita.
- Dynamické pole nemůžete seřadit obvyklým způsobem Data – řazení (Data – Seřadit). K tomu nyní existuje speciální funkce. GRADE (ŘADIT).
- Dynamický rozsah nelze změnit na inteligentní tabulku (ale můžete vytvořit dynamický rozsah založený na chytré tabulce).
Samozřejmě to není konec a jsem si jistý, že Microsoft bude tento mechanismus vylepšovat i v budoucnu.
Kde mohu stáhnout?
A nakonec hlavní otázka 🙂
Microsoft poprvé oznámil a ukázal náhled dynamických polí v Excelu v září 2018 na konferenci Zapálit. V následujících několika měsících došlo k důkladnému testování a zavádění nových funkcí, nejprve na kočky zaměstnanci samotného Microsoftu a dále pak na dobrovolné testery z okruhu Office Insiders. V letošním roce začala být aktualizace, která přidává dynamická pole, postupně zaváděna pro běžné předplatitele Office 365. Dostal jsem ho například až v srpnu s předplatným Office 365 Pro Plus (Monthly Targeted).
Pokud váš Excel ještě nemá dynamická pole, ale opravdu s nimi chcete pracovat, existují následující možnosti:
- Pokud máte předplatné Office 365, můžete jednoduše počkat, až se k vám tato aktualizace dostane. Jak rychle k tomu dojde, závisí na tom, jak často jsou do vašeho Office doručovány aktualizace (jednou ročně, jednou za šest měsíců, jednou měsíčně). Pokud máte podnikový počítač, můžete požádat správce, aby nastavil aktualizace, které se budou stahovat častěji.
- Můžete se připojit k řadám těchto testovacích dobrovolníků Office Insiders – pak budete první, kdo obdrží všechny nové funkce a funkce (ale samozřejmě existuje šance na větší chyby v Excelu).
- Pokud nemáte předplatné, ale krabicovou samostatnou verzi Excelu, tak si budete muset počkat minimálně do vydání další verze Office a Excelu v roce 2022. Uživatelé těchto verzí dostávají pouze aktualizace zabezpečení a opravy chyb a všechny nové „dobroty“ nyní dostanou pouze předplatitelé Office 365. Smutné, ale pravdivé 🙂
V každém případě, až se ve vašem Excelu objeví dynamická pole – po tomto článku na to budete připraveni 🙂
- Co jsou maticové vzorce a jak je používat v Excelu
- Suma oken (rozsahu) pomocí funkce OFFSET
- 3 způsoby, jak transponovat tabulku v Excelu