Obsah
Čo sú dynamické polia
V septembri 2018 spoločnosť Microsoft vydala aktualizáciu, ktorá do programu Microsoft Excel pridáva úplne nový nástroj: Dynamické polia a 7 nových funkcií na prácu s nimi. Tieto veci bez preháňania radikálne menia všetku zaužívanú techniku práce so vzorcami a funkciami a znepokojujú doslova každého používateľa.
Zvážte jednoduchý príklad na vysvetlenie podstaty.
Predpokladajme, že máme jednoduchú tabuľku s údajmi o mestách a mesiacoch. Čo sa stane, ak vyberieme ľubovoľnú prázdnu bunku napravo na hárku a zadáme do nej vzorec, ktorý sa neviaže na jednu bunku, ale hneď na rozsah?
Vo všetkých predchádzajúcich verziách Excelu po kliknutí na vstúpiť dostali by sme obsah len jednej prvej bunky B2. Ako inak?
Alebo by bolo možné zabaliť tento rozsah do nejakej agregačnej funkcie ako =SUM(B2:C4) a získať zaň celkový súčet.
Ak by sme potrebovali zložitejšie operácie ako primitívny súčet, ako je extrahovanie jedinečných hodnôt alebo Top 3, potom by sme museli zadať náš vzorec ako vzorec poľa pomocou klávesovej skratky ctrl+smena+vstúpiť.
Teraz je všetko inak.
Teraz po zadaní takéhoto vzorca môžeme jednoducho kliknúť ďalej vstúpiť - a ako výsledok okamžite získate všetky hodnoty uXNUMXbuXNUMXb, na ktoré sme odkazovali:
Toto nie je mágia, ale nové dynamické polia, ktoré teraz má Microsoft Excel. Vitajte v novom svete 🙂
Vlastnosti práce s dynamickými poľami
Technicky je celé naše dynamické pole uložené v prvej bunke G4 a svojimi údajmi zapĺňa požadovaný počet buniek vpravo a dole. Ak vyberiete akúkoľvek inú bunku v poli, odkaz na riadku vzorcov bude neaktívny, čo ukazuje, že sa nachádzame v jednej z „podradených“ buniek:
Pokus o vymazanie jednej alebo viacerých „detských“ buniek k ničomu nepovedie – Excel ich okamžite prepočíta a vyplní.
Zároveň môžeme bezpečne odkazovať na tieto „detské“ bunky v iných vzorcoch:
Ak skopírujete prvú bunku poľa (napríklad z G4 na F8), celé pole (jeho referencie) sa bude pohybovať rovnakým smerom ako v bežných vzorcoch:
Ak potrebujeme presunúť pole, bude stačiť presunúť (myšou alebo kombináciou ctrl+X, ctrl+V), opäť len prvá hlavná bunka G4 – po nej sa presunie na nové miesto a celé naše pole sa opäť rozšíri.
Ak potrebujete odkazovať niekde inde na hárku na vytvorené dynamické pole, môžete použiť špeciálny znak # („libra“) za adresou jeho vedúcej bunky:
Teraz môžete napríklad jednoducho vytvoriť rozbaľovací zoznam v bunke, ktorá odkazuje na vytvorené dynamické pole:
Chyby dynamického poľa
Čo sa však stane, ak nie je dostatok miesta na rozšírenie poľa alebo ak sú v jeho ceste bunky už obsadené inými údajmi? Zoznámte sa so zásadne novým typom chýb v Exceli – #PREJSŤ! (#SPILL!):
Ako vždy, ak klikneme na ikonu so žltým kosoštvorcom a výkričníkom, dostaneme podrobnejšie vysvetlenie zdroja problému a rýchlo nájdeme rušivé bunky:
Podobné chyby sa vyskytnú, ak pole zmizne z hárka alebo narazí na zlúčenú bunku. Ak prekážku odstránite, všetko sa okamžite opraví.
Dynamické polia a inteligentné tabuľky
Ak dynamické pole ukazuje na „inteligentnú“ tabuľku vytvorenú klávesovou skratkou ctrl+T alebo Domov – Formátovať ako tabuľku (Domov – Formátovať ako tabuľku), potom zdedí aj svoju hlavnú kvalitu – automatickú veľkosť.
Pri pridávaní nových údajov dole alebo vpravo sa automaticky rozšíri aj inteligentná tabuľka a dynamický rozsah:
Existuje však jedno obmedzenie: nemôžeme použiť odkaz na dynamický rozsah vo fórach v inteligentnej tabuľke:
Dynamické polia a ďalšie funkcie programu Excel
Dobre, hovoríš. To všetko je zaujímavé a vtipné. Nie je potrebné, ako predtým, manuálne rozťahovať vzorec s odkazom na prvú bunku pôvodného rozsahu nadol a doprava a tak ďalej. A to je všetko?
Nie tak celkom.
Dynamické polia nie sú len ďalším nástrojom v Exceli. Teraz sú vložené do samotného srdca (alebo mozgu) programu Microsoft Excel – jeho výpočtového stroja. To znamená, že prácu s dynamickými poľami teraz podporujú aj ďalšie vzorce a funkcie Excelu, ktoré poznáme. Pozrime sa na niekoľko príkladov, aby ste mali predstavu o hĺbke zmien, ku ktorým došlo.
transponovať
Na transponovanie rozsahu (zámena riadkov a stĺpcov) mal Microsoft Excel vždy zabudovanú funkciu TRANSP (TRANSPOSE). Aby ste ju však mohli použiť, musíte najprv správne vybrať rozsah pre výsledky (ak bol napríklad vstup rozsah 5×3, potom musíte vybrať 3×5), potom zadať funkciu a stlačiť tlačidlo kombinácia ctrl+smena+vstúpiť, pretože by mohol fungovať iba v režime vzorca poľa.
Teraz stačí vybrať jednu bunku, zadať do nej rovnaký vzorec a kliknúť na normálne vstúpiť – dynamické pole urobí všetko samo:
Násobiteľská tabuľka
Toto je príklad, ktorý som uviedol, keď som bol požiadaný, aby som vizualizoval výhody vzorcov poľa v Exceli. Teraz na výpočet celej Pytagorovej tabuľky stačí postaviť sa do prvej bunky B2, zadať tam vzorec, ktorý vynásobí dve polia (vertikálna a horizontálna množina čísel 1..10) a jednoducho kliknúť na vstúpiť:
Lepenie a konverzia puzdra
Polia môžu byť nielen násobené, ale aj lepené pomocou štandardného operátora & (ampersand). Predpokladajme, že potrebujeme extrahovať meno a priezvisko z dvoch stĺpcov a opraviť veľkosť písmen v pôvodných údajoch. Robíme to pomocou jedného krátkeho vzorca, ktorý tvorí celé pole, a potom naň aplikujeme funkciu PROPNACH (SPRÁVNE)urobiť poriadok v registri:
Záver Top 3
Predpokladajme, že máme množstvo čísel, z ktorých chceme odvodiť tri najlepšie výsledky a usporiadať ich v zostupnom poradí. Teraz sa to robí jedným vzorcom a opäť bez akéhokoľvek ctrl+smena+vstúpiť ako predtým:
Ak chcete, aby sa výsledky neumiestňovali do stĺpca, ale do riadku, potom stačí v tomto vzorci nahradiť dvojbodky (oddeľovač riadkov) bodkočiarkou (oddeľovač prvkov v rámci jedného riadku). V anglickej verzii Excelu sú tieto oddeľovače bodkočiarky a čiarky.
VLOOKUP extrahovanie viacerých stĺpcov naraz
funkcie VPR (VLOOKUP) teraz môžete vytiahnuť hodnoty nie z jedného, ale z niekoľkých stĺpcov naraz – stačí zadať ich čísla (v ľubovoľnom poradí) ako pole v treťom argumente funkcie:
Funkcia OFFSET, ktorá vracia dynamické pole
Jednou z najzaujímavejších a najužitočnejších funkcií (po VLOOKUP) na analýzu údajov je funkcia LIKVIDÁCIA (POSUN), ktorej som svojho času venoval celú kapitolu vo svojej knihe a článok tu. Ťažkosti s pochopením a zvládnutím tejto funkcie boli vždy v tom, že vo výsledku vrátila pole (rozsah) údajov, ale my sme to nevideli, pretože Excel stále nevedel, ako pracovať s poľami.
Teraz je tento problém minulosťou. Pozrite sa, ako teraz pomocou jedného vzorca a dynamického poľa vráteného pomocou OFFSET môžete extrahovať všetky riadky pre daný produkt z ľubovoľnej zoradenej tabuľky:
Pozrime sa na jej argumenty:
- A1 – počiatočná bunka (referenčný bod)
- ПОИСКПОЗ(F2;A2:A30;0) – výpočet posunu od východiskovej bunky nadol – k prvej nájdenej kapuste.
- 0 – posun „okna“ doprava vzhľadom k počiatočnej bunke
- СЧЁТЕСЛИ(A2:A30;F2) – výpočet výšky vráteného „okna“ – počet riadkov, kde je kapusta.
- 4 — veľkosť „okna“ horizontálne, tj výstup 4 stĺpce
Nové funkcie pre dynamické polia
Okrem podpory mechanizmu dynamického poľa v starých funkciách pribudlo do programu Microsoft Excel niekoľko úplne nových funkcií, ktoré sú špeciálne prispôsobené na prácu s dynamickými poľami. Ide najmä o:
- GRADE (TRIEDENIE) – triedi vstupný rozsah a vytvára dynamické pole na výstupe
- SORTPO (TRIEDIŤ PODĽA) - môže zoradiť jeden rozsah podľa hodnôt od druhého
- FILTER (FILTER) – načíta riadky zo zdrojového rozsahu, ktoré spĺňajú zadané podmienky
- UNIK (JEDINEČNÉ) – extrahuje jedinečné hodnoty z rozsahu alebo odstráni duplikáty
- SLMASSIVE (RANDARRAY) – generuje pole náhodných čísel danej veľkosti
- AFTERBIRTH (SEKVENCIA) — vytvorí pole zo sekvencie čísel s daným krokom
Viac o nich – o niečo neskôr. Na premyslené štúdium stoja za samostatný článok (a nie jeden) 🙂
Závery
Ak ste si prečítali všetko napísané vyššie, potom si myslím, že si už uvedomujete rozsah zmien, ktoré sa udiali. Toľko vecí v Exceli sa teraz dá robiť jednoduchšie, jednoduchšie a logickejšie. Musím priznať, že som trochu šokovaný, koľko článkov tu, na tejto stránke a v mojich knihách teraz bude musieť byť opravených, ale som pripravený to urobiť s ľahkým srdcom.
Zhrnutím výsledkov, klady dynamické polia, môžete napísať nasledovné:
- Na kombináciu môžete zabudnúť ctrl+smena+vstúpiť. Excel teraz nevidí žiadny rozdiel medzi „bežnými vzorcami“ a „vzorcami poľa“ a zaobchádza s nimi rovnakým spôsobom.
- O funkcii SUMPRODUCT (SUMA PRODUKT), ktorý sa predtým používal na zadávanie vzorcov poľa bez ctrl+smena+vstúpiť môžete tiež zabudnúť – teraz je to dosť jednoduché SUM и vstúpiť.
- Inteligentné tabuľky a známe funkcie (SUM, IF, VLOOKUP, SUMIFS atď.) teraz plne alebo čiastočne podporujú aj dynamické polia.
- Existuje spätná kompatibilita: ak otvoríte zošit s dynamickými poľami v starej verzii Excelu, zmenia sa na vzorce poľa (v zložených zátvorkách) a budú naďalej fungovať v „starom štýle“.
Našiel nejaké číslo mínusy:
- Z dynamického poľa nemôžete vymazať jednotlivé riadky, stĺpce alebo bunky, tj žije ako jedna entita.
- Dynamické pole nemôžete triediť obvyklým spôsobom Údaje – triedenie (Údaje – Zoradiť). Teraz na to existuje špeciálna funkcia. GRADE (TRIEDENIE).
- Dynamický rozsah nemožno zmeniť na inteligentnú tabuľku (ale môžete vytvoriť dynamický rozsah založený na inteligentnej tabuľke).
Samozrejme, toto nie je koniec a som si istý, že Microsoft bude tento mechanizmus vylepšovať aj v budúcnosti.
Kde môžem stiahnuť?
A na záver hlavná otázka 🙂
Microsoft prvýkrát oznámil a ukázal ukážku dynamických polí v Exceli v septembri 2018 na konferencii Zapáliť. V nasledujúcich mesiacoch prebehlo dôkladné testovanie a spustenie nových funkcií, najskôr mačky zamestnancov samotného Microsoftu a potom na dobrovoľných testeroch z okruhu Office Insiders. Tento rok sa aktualizácia, ktorá pridáva dynamické polia, začala postupne sprístupňovať bežným predplatiteľom Office 365. Napríklad som ho dostal len v auguste s predplatným Office 365 Pro Plus (Mesačné cielené).
Ak váš Excel ešte nemá dynamické polia, ale naozaj s nimi chcete pracovať, existujú nasledujúce možnosti:
- Ak máte predplatné Office 365, môžete jednoducho počkať, kým sa k vám táto aktualizácia dostane. Ako rýchlo sa to stane, závisí od toho, ako často sa aktualizácie doručujú do vášho balíka Office (raz za rok, raz za šesť mesiacov, raz za mesiac). Ak máte firemný počítač, môžete požiadať správcu, aby nastavil častejšie sťahovanie aktualizácií.
- Môžete sa pripojiť k radom dobrovoľníkov testujúcich Office Insiders – potom budete prvý, kto získa všetky nové funkcie a funkcie (ale samozrejme existuje šanca na väčšie chyby v Exceli).
- Ak nemáte predplatné, ale krabicovú samostatnú verziu Excelu, budete musieť počkať minimálne do vydania ďalšej verzie Office a Excelu v roku 2022. Používatelia takýchto verzií dostávajú iba bezpečnostné aktualizácie a opravy chýb a všetky nové „dobroty“ teraz poputujú iba predplatiteľom Office 365. Smutné, ale pravdivé 🙂
V každom prípade, keď sa vo vašom Exceli objavia dynamické polia – po tomto článku budete na to pripravení 🙂
- Čo sú vzorce poľa a ako ich používať v Exceli
- Sumácia okna (rozsahu) pomocou funkcie OFFSET
- 3 spôsoby, ako transponovať tabuľku v Exceli