Tabuľky programu Excel často obsahujú rozbaľovacie bunky na zjednodušenie a / alebo štandardizáciu zadávania údajov. Tieto rozbaľovacie zoznamy sa vytvárajú pomocou funkcie overovania údajov a určujú zoznam povolených položiek.
Ak chcete nastaviť jednoduchý rozbaľovací zoznam, vyberte bunku, do ktorej sa majú zadávať údaje, potom kliknite na položku Validácia údajov (na karte Údaje ), vyberte položku Overenie údajov, vyberte položku Zoznam (v časti Povoliť :) a potom zadajte položky zoznamu (oddelené čiarkami ) v poli Zdroj : (pozri obrázok 1).
V tomto type základného rozbaľovacieho zoznamu je zoznam povolených položiek špecifikovaný v rámci samotnej validácie údajov. preto, aby ste v zozname vykonali zmeny, používateľ musí otvoriť a upraviť overenie údajov. Toto môže byť ťažké pre neskúsených používateľov alebo v prípadoch, keď je zoznam možností dlhý.
Ďalšou možnosťou je umiestniť zoznam v pomenovanom rozsahu v tabuľke a potom zadajte tento názov rozsahu (predbežný s označením rovnosti) v poli Zdroj : validácia údajov (ako je znázornené na obrázku 2).
Táto druhá metóda uľahčuje úpravu možností v zozname, ale pridanie alebo odstránenie položiek môže byť problematické. Keďže pomenovaný rozsah (FruitChoices v našom príklade) odkazuje na pevný rozsah buniek ($ H $ 3: $ H $ 10, ako je zobrazené), ak sa do buniek H11 alebo nižšie pridá viac volieb, nezobrazia sa v rozbaľovacej ponuke (pretože tieto bunky nie sú súčasťou radu FruitChoices).
Rovnako, ak sú napríklad položky Hrušky a Jahody vymazané, nebudú už zobrazené v rozbaľovacej ponuke, rozbaľovacia ponuka však bude obsahovať dve "prázdne" voľby, pretože rozbaľovací zoznam stále odkazuje na celý rozsah FruitChoices vrátane prázdnych buniek H9 a H10.
Z týchto dôvodov sa pri použití normálneho pomenovaného rozsahu ako zdroja zoznamu v rozbaľovacej ponuke musí samotný pomenovaný rozsah upraviť tak, aby zahŕňal viac alebo menej buniek, ak sa pridajú alebo odstránia položky zo zoznamu.
Riešením tohto problému je použiť názov dynamického rozsahu ako zdroj pre rozbaľovacie možnosti. Názov dynamického rozsahu je ten, ktorý sa automaticky rozširuje (alebo kontraktuje) tak, aby presne zodpovedal veľkosti bloku údajov pri pridávaní alebo odstraňovaní záznamov. Ak chcete tak urobiť, použite vzorec, nie pevný rozsah bunkových adries, aby ste definovali pomenovaný rozsah.
Ako nastaviť dynamický rozsah v programe Excel
Normálny (statický) názov rozsahu odkazuje na špecifikovaný rozsah buniek (v našom príklade je $ H $ 3: $ H $ 10, pozri nižšie):
Dynamický rozsah je však definovaný pomocou vzorca (pozri nižšie, prevzatý zo samostatnej tabuľky, ktorá používa názvy dynamického rozsahu):
Skôr ako začneme, uistite sa, že si stiahnete náš príklad súboru programu Excel (triedenie makier bolo zakázané).
Pozrime sa na tento vzorec podrobne. Voľby pre ovocie sú v bloku buniek priamo pod nadpisom ( FRUITS ). Do tejto položky je priradené aj meno: Ovocie :
Celý vzorec použitý na definovanie dynamického rozsahu pre voľby Ovocie je:
= OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH (TRUE, INDEX (ISBLANK (OFFSET (FruitsHeading, 1, 0, 20, 1)), 0, 0), 0) -1, 20), 1)
Ovocie označuje nadpis, ktorý je jeden riadok nad prvou položkou v zozname. Číslo 20 (použité dvakrát vo vzorci) je maximálna veľkosť (počet riadkov) pre zoznam (toto je možné upraviť podľa potreby).
Všimnite si, že v tomto príklade je v zozname iba 8 položiek, ale pod nimi sú aj prázdne bunky, kde je možné pridať ďalšie záznamy. Číslo 20 sa vzťahuje na celý blok, v ktorom je možné zadávať údaje, nie na skutočný počet záznamov.
Teraz si rozdelíme vzorec na kusy (farebne kódovanie každého kusu), aby sme pochopili, ako to funguje:
= OFFSET (ovocie, 1, 0, IFERROR (MATCH (TRUE, INDEX (ISBLANK ( OFFSET (Ovocie, 1, 0, 20, 1 )
"Najvnútornejší" kus je OFFSET (Ovocie, 1, 0, 20, 1) . Toto odkazuje na blok 20 buniek (pod bunkou FruitsHeading), kde je možné zadávať voľby. Táto funkcia OFSET v podstate hovorí: Začnite v bunke FruitsHeading, choďte nadol 1 riadok a viac ako 0 stĺpcov, potom vyberte oblasť, ktorá je 20 riadkov dlhá a 1 stĺpec široký. Takže nám dáva 20-riadkový blok, do ktorého sú zadané voľby Ovocie.
Ďalším vzorcom je funkcia ISBLANK :
= OFFSET (ovocie, 1, 0, IFERROR (MATCH (TRUE, INDEX ( ISBLANK (vyššie), 0, 0), 0) -1, 20)
Tu je funkcia OFFSET (vysvetlená vyššie) nahradená "vyššie uvedeným" (aby sa zjednodušilo čítanie). Funkcia ISBLANK však funguje v rozsahu 20 riadkov, ktorý definuje funkcia OFFSET.
ISBLANK potom vytvorí sadu 20 hodnôt TRUE a FALSE, ktoré označujú, či každá z jednotlivých buniek v rozsahu 20 riadkov, na ktorú odkazuje funkcia OFFSET, je prázdna (prázdna) alebo nie. V tomto príklade budú prvé 8 hodnoty v súbore FALSE, pretože prvé 8 buniek nie sú prázdne a posledných 12 hodnôt bude TRUE.
Ďalším vzorcom je funkcia INDEX:
= OFFSET (ovocie, 1, 0, IFERROR (MATCH (TRUE, INDEX (vyššie, 0, 0), 0) -1, 20)
Opäť platí, že "vyššie uvedené" sa vzťahuje na funkcie ISBLANK a OFFSET popísané vyššie. Funkcia INDEX vracia pole obsahujúce 20 hodnôt TRUE / FALSE vytvorených funkciou ISBLANK.
INDEX sa zvyčajne používa na výber určitej hodnoty (alebo rozsahu hodnôt) z bloku dát určením určitého riadka a stĺpca (v rámci tohto bloku). Ale nastavenie vstupov riadkov a stĺpcov na nulu (ako je to urobené tu) spôsobí, že INDEX vráti pole obsahujúce celý blok dát.
Ďalším vzorcom je funkcia MATCH:
= OFFSET (FruitsHeading, 1, 0, IFERROR ( MATCH (TRUE, vyššie, 0) -1, 20), 1)
Funkcia MATCH vracia polohu prvej hodnoty TRUE v rámci poľa vráteného funkciou INDEX. Keďže prvé 8 položiek v zozname nie sú prázdne, prvých 8 hodnôt v poli bude FALSE a deviata hodnota bude TRUE (pretože 9. riadok v rozsahu je prázdny).
Takže funkcia MATCH vráti hodnotu 9 . V tomto prípade však chceme skutočne vedieť, koľko položiek je v zozname, takže vzorec odčíta hodnotu 1 od hodnoty MATCH (ktorá udáva polohu posledného záznamu). Tak nakoniec MATCH (TRUE, vyššie, 0) -1 vráti hodnotu 8 .
Ďalším vzorcom je funkcia IFERROR:
= OFFSET (ovocie, 1, 0, IFERROR (vyššie, 20), 1)
Funkcia IFERROR vracia alternatívnu hodnotu, ak prvá zadaná hodnota vedie k chybe. Táto funkcia je zahrnutá, pretože ak je celý blok buniek (všetkých 20 riadkov) vyplnený záznamami, funkcia MATCH vráti chybu.
Je to preto, že hovoríme funkcii MATCH, aby hľadala prvú TRUE hodnotu (v poli hodnôt z funkcie ISBLANK), ale ak nie je žiadna z buniek prázdna, celé pole sa naplní hodnotami FALSE. Ak MATCH nedokáže nájsť cieľovú hodnotu (TRUE) v poli, ktoré vyhľadáva, vráti chybu.
Takže ak je celý zoznam plný (a preto MATCH vráti chybu), funkcia IFERROR namiesto toho vráti hodnotu 20 (s vedomím, že v zozname musí byť 20 záznamov).
Napokon, OFFSET (FruitsHeading, 1, 0, vyššie, 1) vracia rozsah, ktorý skutočne hľadáme: Začnite v bunke FruitsHeading, stlačte 1 riadok nad 0 stĺpcov a potom vyberte oblasť, ktorá má však mnoho riadkov dlhú ako sú v zozname (a 1 stĺpec). Takže celý vzorec spolu vráti rozsah, ktorý obsahuje iba skutočné položky (dole do prvej prázdnej bunky).
Použitie tohto vzorca na definovanie rozsahu, ktorý je zdrojom rozbaľovacej ponuky znamená, že môžete voľne upravovať zoznam (pridanie alebo odstránenie položiek, ak zostávajúce položky začínajú v hornej bunke a sú priľahlé) a rozbaľovací zoznam bude vždy odrážať aktuálny (pozri obrázok 6).
Súbor príkladov (Dynamické zoznamy), ktorý bol použitý tu, je zahrnutý a je možné stiahnuť z tejto webovej stránky. Makrá nefungujú, pretože WordPress nemá rád Excel knihy s makrami v nich.
Ako alternatívu k zadaniu počtu riadkov v bloku zoznamov môže byť zoznamu priradený jeho vlastný názov rozsahu, ktorý potom môže byť použitý v modifikovanom vzore. V príklade súboru používa táto metóda druhý zoznam (mená). Tu je celý názov bloku (pod nadpisom "NAMES", 40 riadkov v príklade súboru) priradený názov rozsahu NameBlock . Alternatívny vzorec na definovanie zoznamu mien je potom:
= OFFSET (názvy, 1, 0, IFERROR (MATCH (TRUE, INDEX (ISBLANK, 0, 0), -1), ROWS (NamesBlock )
kde NamesBlock nahrádza OFFSET (FruitsHeading, 1, 0, 20, 1) a ROWS (NamesBlock) nahrádza 20 (počet riadkov) v predchádzajúcom vzorci.
Takže pre rozbaľovacie zoznamy, ktoré sa dajú ľahko upraviť (vrátane iných používateľov, ktorí môžu byť neskúsení), skúste použiť názvy dynamických rozsahov! Upozorňujeme, že hoci bol tento článok zameraný na rozbaľovacie zoznamy, názvy dynamických rozsahov sa môžu použiť kdekoľvek, kde je potrebné odkazovať na rozsah alebo zoznam, ktorý sa môže líšiť vo veľkosti. Užite si to!
