Dynamické polia v Exceli: jeden vzorec namiesto stovky

Dynamické polia sú najväčšou zmenou, ktorou Excel za posledné roky prešiel – a zároveň zmenou, o ktorej mnohí používatelia stále nevedia. Princíp je jednoduchý: vzorec už nemusí vrátiť jednu hodnotu do jednej bunky. Môže vrátiť celý zoznam, ktorý sa sám rozlieva do toľkých buniek, koľko potrebuje. Anglicky sa tomu hovorí spill.

Rozdiel oproti starému spôsobu práce je zásadný. Predtým ste napísali vzorec do prvej bunky a potiahli ho o päťsto riadkov nižšie – a keď pribudli nové dáta, museli ste ho ťahať znova. Dynamický vzorec napíšete raz, do jednej bunky, a on sám prispôsobí veľkosť výsledku množstvu dát.

1. UNIQUE – zoznam bez duplicít, ktorý sa aktualizuje sám

Klasická úloha: z dvetisíc riadkov faktúr potrebujete zoznam všetkých zákazníkov, každého raz. Doteraz to znamenalo skopírovať stĺpec bokom a použiť funkciu Odstrániť duplicity – teda ručný úkon, ktorý treba po každej zmene dát zopakovať.

=UNIQUE(B2:B2000)

To je celé. Vzorec vypíše každého zákazníka práve raz a pri zmene zdrojových dát sa zoznam prepočíta automaticky.

Menej známy je druhý a tretí argument. Zápis =UNIQUE(B2:B2000;;PRAVDA) vráti iba hodnoty, ktoré sa v zozname vyskytujú práve raz – užitočné, keď hľadáte jednorazových zákazníkov alebo naopak overujete, či sa nejaký kód neopakuje.

2. FILTER – filtrovanie, ktoré nechá pôvodné dáta na pokoji

Bežný filter mení pohľad na tabuľku: skryje riadky a zobrazí len tie vyhovujúce. FILTER robí niečo iné – vypíše vyhovujúce riadky na nové miesto, pričom zdrojová tabuľka zostane nedotknutá.

=FILTER(A2:E2000;D2:D2000>1000)

Vzorec vypíše všetky riadky, kde je hodnota v stĺpci D vyššia ako 1000, aj so všetkými stĺpcami A až E. Podmienky viete kombinovať: hviezdička znamená „a zároveň", plus znamená „alebo".

=FILTER(A2:E2000;(D2:D2000>1000)*(C2:C2000="SK"))

Tretí argument ošetruje situáciu, keď podmienke nevyhovuje nič – bez neho vzorec vráti chybu:

=FILTER(A2:E2000;D2:D2000>1000;"Žiadne záznamy")

Práve tu je FILTER najsilnejší: umožňuje postaviť živý prehľad. Na samostatný hárok napíšete jeden vzorec, ktorý vyberá napríklad všetky faktúry po splatnosti – a ten prehľad je vždy aktuálny, bez toho, aby ho niekto musel obnovovať.

3. SORT a SORTBY – zoradenie bez zásahu do dát

SORT zoradí výsledok priamo vo vzorci. To je rozdiel oproti tlačidlu Zoradiť, ktoré natrvalo prehádže riadky v zdrojovej tabuľke.

=SORT(UNIQUE(B2:B2000))

Tento zápis ukazuje najväčšiu prednosť dynamických polí: funkcie sa dajú vkladať do seba. Výsledok jednej sa stáva vstupom druhej. Vnútorná UNIQUE vytvorí zoznam bez duplicít, vonkajšia SORT ho zoradí podľa abecedy.

Trojkombinácia potom vyrieši typickú úlohu „zoznam aktívnych zákazníkov, bez duplicít, zoradený":

=SORT(UNIQUE(FILTER(B2:B2000;E2:E2000="aktívny")))

Príbuzná funkcia SORTBY zoradí jednu oblasť podľa hodnôt v inej – napríklad zoznam produktov podľa objemu predaja, hoci samotný objem vo výsledku nezobrazujete.

4. VSTACK – konsolidácia hárkov jedným vzorcom

Toto je funkcia, ktorá najviac šetrí čas pri mesačných reportoch. VSTACK poskladá viac oblastí pod seba do jednej súvislej tabuľky.

=VSTACK(Január!A2:E500;Február!A2:E500;Marec!A2:E500)

Namiesto ručného kopírovania dát z dvanástich mesačných hárkov na jeden zberný hárok máte jeden vzorec, ktorý sa prepočíta sám. A keďže ide o dynamické pole, výsledok viete rovno vložiť do ďalšej funkcie:

=SORT(VSTACK(Január!A2:E500;Február!A2:E500);1)

Sesterská funkcia HSTACK robí to isté vedľa seba, teda spája oblasti do stĺpcov.

Jedna nepríjemnosť: ak v jednotlivých hárkoch necháte rezervu na budúce riadky, VSTACK poctivo prenesie aj prázdne bunky a výsledok bude preložený dierami. Riešenie prináša nasledujúca časť.

5. Bodky v odkazoch – ako odrezať prázdne riadky

Novinka, ktorá vyzerá ako preklep, no je to plnohodnotný nástroj. Do odkazu na oblasť môžete pridať bodku k dvojbodke a tým Excelu povedať, aby prázdne riadky na okraji oblasti ignoroval.

Pravidlo je jednoduché: bodka stojí na tej strane dvojbodky, ktorý koniec oblasti chcete orezať. Odtiaľ vyplývajú tri varianty:

• A1.:A100 – bodka vľavo, teda pri začiatku oblasti. Odreže prázdne bunky na začiatku (hore).
• A1:.A100 – bodka vpravo, teda pri konci oblasti. Odreže prázdne bunky na konci (dole).
• A1.:.A100 – bodky na oboch stranách. Odreže prázdne bunky na oboch koncoch.

V praxi sa najčastejšie používa druhý variant – dáta začínajú hneď na prvom riadku a prázdno je až za nimi.

Vďaka tomu si môžete dovoliť odkazovať aj na celý stĺpec, čo sa predtým neodporúčalo kvôli spomaleniu súboru. Zápis =SUM(A:.A) spočíta stĺpec A len po poslednú vyplnenú bunku, takže Excel nemusí prechádzať milión prázdnych riadkov.

Príklad z predchádzajúcej časti sa tak zbaví dier:

=VSTACK(Január!A2:.E500;Február!A2:.E500)

Bodkový zápis je iba skrátená podoba funkcie TRIMRANGE – ide o tú istú vec, takže obe obmedzenia nižšie platia rovnako pre bodky aj pre TRIMRANGE.

Po prvé, odrezávajú sa iba okraje oblasti. Prázdny riadok uprostred dát zostane a treba naň FILTER alebo Power Query. Po druhé, pozor na bunky, ktoré obsahujú prázdny textový reťazec z nejakého vzorca – tie sa za prázdne nepovažujú a orezanie sa na nich zastaví.

Keď to nefunguje: chyba #SPILL!

Najčastejšia chyba pri dynamických poliach má jednoduchú príčinu: vzorcu niečo stojí v ceste. Ak sa výsledok potrebuje rozliať do dvadsiatich buniek, ale v niektorej z nich už čokoľvek je, Excel ohlási #SPILL!. Riešením je uvoľniť oblasť pod vzorcom – niekedy stačí zmazať aj zdanlivo prázdnu bunku s medzerou.

Užitočná pomôcka je aj mriežka za odkazom. Ak vzorec v bunke H2 vytvára rozliaty zoznam, zápis H2# odkazuje na celý ten zoznam bez ohľadu na to, koľko riadkov práve zaberá. Skvele sa to hodí na rozbaľovacie zoznamy v overovaní údajov: zoznam sa sám rozšíri, keď pribudne nová položka.

Dostupnosť a čo si vyskúšať ako prvé

UNIQUE, FILTER a SORT nájdete v Microsoft 365 aj v Exceli 2021. VSTACK, HSTACK a bodkové odkazy sú novšie a vyžadujú Microsoft 365. V starších verziách sa dá časť týchto úloh vyriešiť cez Power Query alebo kontingenčné tabuľky.

Ak chcete začať, skúste ako prvý vzorec =SORT(UNIQUE(...)) nad stĺpcom, z ktorého pravidelne robíte zoznam ručne. Je to jeden riadok, ktorý nahradí opakovaný úkon – a najlepšie ukáže, v čom je celý princíp dynamických polí iný.