Dynamické vzorce polí a správanie poľa s presahujúcimi údajmi

Excelové vzorce, ktoré vracajú množinu hodnôt, známu aj ako pole, vracajú tieto hodnoty do susedných buniek. Toto správanie sa nazýva presahovanie.

Vzorce, ktoré môžu vrátiť polia s variabilnou veľkosťou, sa nazývajú dynamické vzorce poľa. Vzorce, ktoré aktuálne vracajú polia, ktoré sa úspešne presahujú, sa môžu označovať ako vzorce poľa s presahom.

Nižšie nájdete niekoľko poznámok, ktoré vám pomôžu pochopiť a používať tieto typy vzorcov.

Čo znamená presahovanie?

Poznámka: Staršie vzorce poľa vždy vrátia výsledok s pevnou veľkosťou – vždy presahujú do rovnakého počtu buniek. Správanie pri presahovaní opísané v tejto téme sa nevzťahuje na staršie vzorce poľa.

Presahovanie znamená, že výsledkom vzorca je viacero hodnôt a tieto hodnoty boli umiestnené v susedných bunkách. Napríklad vzorec =SORT(D2:D11;1;-1), ktorý zoradí pole v zostupnom poradí, by vrátil zodpovedajúce pole s výškou 10 riadkov. Stačí však zadať vzorec do ľavej hornej bunky alebo v tomto prípade F2 a automaticky presiahne po bunku F11.

Zoraďte hodnoty v bunkách D2:D11 pomocou =SORT(D2:D11;1;-1)

Kľúčové body

Keď vzorec potvrdíte stlačením klávesu Enter, Excel dynamicky určí veľkosť výstupného rozsahu a umiestni výsledky do každej bunky v danom rozsahu.
Ak píšete dynamický vzorec poľa na vykonanie úkonov so zoznamom údajov, môže byť užitočné umiestniť ho do excelovej tabuľky a potom použiť štruktúrované odkazy na odkazovanie na údaje. Dôvodom je, že štruktúrované odkazy sa automaticky prispôsobujú počas ich pridávania alebo odstraňovania z tabuľky.
Vzorce poľa s presahovaním nie sú podporované v samotných excelových tabuľkách, preto by ste ich mali umiestniť do mriežky mimo tabuľky. Tabuľky sú najvhodnejšie na uchovávanie riadkov a stĺpcov nezávislých údajov.
Po zadaní vzorca poľa s presahovaním Excel po výbere ľubovoľnej bunky v oblasti presahu umiestni okolo rozsahu zvýraznené orámovanie. Orámovanie zmizne, keď vyberiete bunku mimo oblasti.
Len prvá bunka v oblasti presahovania je upraviteľná. Ak vyberiete inú bunku v oblasti presahovania, vzorec sa zobrazí v riadku vzorcov, ale text je „sivý“ a nedá sa zmeniť. Ak potrebujete aktualizovať vzorec, mali by ste vybrať bunku v ľavom hornom rohu v rozsahu poľa, podľa potreby ju zmeniť a potom Excel automaticky aktualizuje zvyšok oblasti presahovania po stlačení klávesu Enter.
Prekrývanie vzorcov – vzorce poľa nie je možné zadať, ak niečo blokuje výstupný rozsah. Ak sa to stane, Excel vráti chybu #PRESAHOVANIE!, ktorá označuje, že došlo k zablokovaniu. Po odstránení zablokovania sa vzorec presiahne podľa očakávania. V nižšie uvedenom príklade sa výstupný rozsah vzorca prekrýva s iným rozsahom s údajmi a zobrazuje sa s bodkovane orámovanými prekrývajúcimi sa bunkami s hodnotami, ktoré označujú, že sa nedá presahovať. Odstráňte blokujúce údaje alebo ich skopírujte na iné miesto a vzorec bude presahovať podľa očakávania.
Staršie vzorce poľa zadané prostredníctvom kombinácie klávesov CTRL + SHIFT + ENTER (CSE) sú stále podporované z dôvodu spätnej kompatibility, ale už by sa nemali používať. Ak chcete, môžete konvertovať staršie vzorce poľa na dynamické vzorce poľa tak, že nájdete prvú bunku v rozsahu polí, skopírujete text vzorca, odstránite celý rozsah staršieho poľa a potom vzorec znova zadáte do ľavej hornej bunky. Pred inováciou starších vzorcov poľa na dynamické vzorce poľa by ste si mali byť vedomí niektorých rozdielov vo výpočtoch medzi týmito vzorcami.
Excel obmedzil podporu pre dynamické polia medzi zošitmi a tento scenár je podporovaný, len keď sú otvorené oba zošity. Ak zatvoríte zdrojový zošit, všetky vzorce prepojených dynamických polí pri obnovení vrátia chybu #ODKAZ! .