V tomto článku sa naučíme, ako vytvoriť dynamický rozbaľovací zoznam v programe Microsoft Excel.
Ako vieme, funkcia overovania údajov zlepšuje efektivitu zadávania údajov v programe Excel a znižuje chyby a chyby pri písaní. Slúži na obmedzenie používateľa na typ údajov, ktoré je možné zadať do rozsahu. V prípade neplatného záznamu zobrazí správu a umožní užívateľovi zadať údaje na základe zadanej podmienky.
Dynamický rozbaľovací zoznam v programe Excel je však pohodlnejším spôsobom výberu údajov bez toho, aby ste museli meniť zdroj. Inými slovami, povedzme, že budete často aktualizovať zoznam, ktorý ste urobili v rozbaľovacom zozname. A myslíte si, že ak urobíte v zozname nejaké zmeny, musíte aktualizáciu údajov vždy zmeniť, aby ste získali aktualizovaný rozbaľovací zoznam.
Tu však prichádza na scénu dynamický rozbaľovací zoznam a je to najlepšia voľba na výber údajov bez vykonávania akýchkoľvek zmien v overovaní údajov. Je veľmi podobný normálnej validácii údajov. Keď však aktualizujete zoznam, dynamický rozbaľovací zoznam sa zmení tak, aby vyhovoval tejto akcii, zatiaľ čo normálny rozbaľovací zoznam nie.
Vezmime si teda príklad a pochopme, ako vytvárame dynamický rozbaľovací zoznam:-
V stĺpci A máme zoznam produktov a v bunke D9 budeme mať dynamický rozbaľovací zoznam produktov.
Názov tabuľky s nepriamou funkciou
Najprv vytvoríme tabuľku; postupujte podľa nižšie uvedených krokov:-
- Vyberte rozsah A8: A16
- Prejdite na kartu Vložiť a potom kliknite na tabuľku
- Po kliknutí na možnosť „Tabuľka“ sa otvorí okno Tabuľka
- Potom vyberte rozsah, pre ktorý chceme vložiť tabuľku A8: A17
- Kliknite na OK
- Teraz klikneme na OK
- Môžete vidieť, že tento rozsah bol prevedený do tabuľky a hlavička tejto tabuľky má tiež možnosť rozbaľovacej ponuky filtra
Poznámka: - Ak do spodnej časti zoznamu pridáme akýkoľvek produkt alebo položku, tabuľka sa automaticky rozbalí, aby zahrnula nové produkty alebo položky.
Teraz vytvoríme dynamický rozbaľovací zoznam v bunke D9 a postupujte podľa nižšie uvedených krokov:-
- Vyberte bunku D9
- Otvorte dialógové okno Overenie údajov stlačením klávesov ALT+D+L
- V rozbaľovacom zozname Povoliť vyberte položku Zoznam
- A potom zadajte túto funkciu = NEPRIAMY („Tabuľka1“) na karte zdroj
- Kliknite na OK
Poznámka: - Keď klikneme na OK, v Exceli sa otvorí okno, ktoré hovorí, že so vstupom nie je niečo v poriadku. Dôvodom je, že Excel neakceptuje žiadnu samorozbaľovaciu tabuľku priamo vo validácii údajov.
Teraz pridajte nové produkty do zoznamu produktov.
Na obrázku vyššie vidíme, že v rozbaľovacom zozname sa zobrazuje nový pridaný produkt.
2nd Príklad:-
V tomto prípade sa naučíme, ako priradiť názov tabuľky ako názov na diaľku
Názov tabuľky už máme, ale tu musíme definovať názov tejto tabuľky, aby sme získali dynamický rozbaľovací zoznam; postupujte podľa nižšie uvedených krokov:-
- Vyberte bunku D10
- Prejdite na rozsah tabuľky a okrem hlavičky vyberáme rozsah od prvého produktu po posledný produkt
- Prejdite do poľa s názvom a zadajte krátky názov „tablerange“, stlačte kláves Enter
- Po stlačení klávesu Enter vidíme, že sa v poli s názvom nič nezmenilo
- Kliknutím na možnosť rozbaľovacieho zoznamu zobrazíte všetky uvedené rozsahy, ktoré sú k dispozícii
- V rozbaľovacom zozname môžeme vidieť aj názov, ktorý sme práve definovali pre túto tabuľku
- Teraz prejdeme na overenie údajov a v časti „Zdroj“ zadáme „tabuľkový rozsah“
Poznámka:- Ak si nepamätáte, aký názov ste danému rozsahu dali, môžete stlačiť kláves F3 a vyskočí okno, ktoré vám navrhne všetky pomenované rozsahy, ktoré sú k dispozícii.
- Teraz prejdite na kartu „Zadať správu“ a do názvu napíšte „Vybrať produkt“ a potom do tela správy napíšeme „Vyberte svoj produkt zo zoznamu“
- Teraz prejdite na kartu „Upozornenie na chybu“ a tam v názve napíšte „Neplatný produkt“ a v chybovom hlásení napíšeme „Zadali ste nesprávny produkt“
- Kliknite na OK
- Bunka D10 obsahujúca vstupnú správu spolu s rozbaľovacím zoznamom
- Teraz, keď do zoznamu pridáme akýkoľvek produkt, automaticky sa zobrazí v rozbaľovacom zozname
Čo sa však stane, keď preskočíme jednu bunku za poslednou bunkou a potom pridáme nový produkt alebo položku? Môžete vidieť, že tentoraz sa rozsah tabuliek nerozšíril, a v skutočnosti je novo pridaný produkt vo všeobecnom formáte. Bude sa teda zobrazovať v rozbaľovacom zozname alebo nie? Aby sme to skontrolovali, keď prejdeme na bunku D10 a skontrolujeme rozbaľovací zoznam, môžeme vidieť rovnaký starý rozbaľovací zoznam bez nového produktu. Je to preto, že rozsah tabuľky po poslednej bunke nič nenašiel, a preto rozsah nevydal.
3rd Príklad:-
V nasledujúcich dvoch metódach sa naučíme, ako pomocou funkcií OFFSET a COUNTA môžeme náš rozbaľovací zoznam dynamickejšie.
Postupujte podľa nižšie uvedených krokov:-
- Vyberte bunku D11 a stlačte kombináciu klávesov ALT + D + L
- Otvorí sa dialógové okno Overenie údajov
- Teraz vyberte zoznam v možnosti „Povoliť“
- Potom v možnosti Zdroj zadajte nasledujúci vzorec:-
= OFFSET ($ A $ 9,0,0, COUNTA ($ A: $ A), 1)
Vysvetlenie vzorca:- Vybrali sme A9, čo je prvý produkt v rade, a potom napíšeme 0 na 2nd argument, pretože nechceme presunúť riadok z počiatočného bodu; potom znova 0 v 3rd argument, pretože tu nechceme žiadne zmeny v počte stĺpcov ani v počiatočnom bode. Potom sme zadali funkciu COUNTA a vybrali celý stĺpec A. Tento argument skontroluje výšku počtu riadkov a vráti počet prázdnych políčok. Po vykonaní akýchkoľvek zmien v rozsahu rozšíri rozsah.
A posledný argument „Šírka“ je voliteľný argument. Je to šírka v počte stĺpcov. Buď to môžeme preskočiť, alebo tu zatiaľ môžeme napísať 1. Ak preskočíme, bude v predvolenom nastavení brať do úvahy šírku vráteného rozsahu, ktorý sme zadali v argumente, a potom zatvoríme zátvorky.
- Po kliknutí na tlačidlo OK sa nám v bunke D11 zobrazí rozbaľovací zoznam
- Zobrazuje zoznam vrátane prázdnych položiek a potom výrobky, ktoré sme pridali
4th Príklad:-
V tomto prípade použijeme funkciu na definovanie názvu.
Ak chcete definovať názov rozsahu, postupujte podľa nižšie uvedených krokov:-
- Stlačte CTRL + F3, zobrazí sa dialógové okno Správca mien
- Kliknite na Nový
- Definujte názov rozsahu „ProdName“ a zadajte nasledujúci vzorec:-
= OFFSET („Dynamický rozbaľovací zoznam s DV“! $ A $ 9,0,0, COUNTA („Dynamický rozbaľovací zoznam s DV“! $ A: $ A))
- Kliknite na OK
- Dialógové okno Overenie údajov otvoríte stlačením klávesov Alt + D + L
- V rozbaľovacom zozname Povoliť vyberte položku Zoznam
- Na karte Zdroj zadajte = ProdName
- Kliknite na OK
- Ak teraz do zoznamu niečo pridáme, to isté sa zobrazí v zozname
Takto teda môžete získať dynamický zoznam pre akýkoľvek produkt alebo položku rôznymi spôsobmi pomocou validácie údajov. To je zatiaľ všetko. V nasledujúcom videu z tejto série vysvetlíme, ako vytvoriť závislý rozbaľovací zoznam rôznymi metódami v programe Excel.
Kliknutím na odkaz na video získate rýchly prehľad o jeho použití. Prihláste sa na odber nášho nového kanála a učte sa s nami!
Ak sa vám páčili naše blogy, zdieľajte ich so svojimi priateľmi na Facebooku. A tiež nás môžete sledovať na Twitteri a Facebooku.
Radi by sme vás počuli, dajte nám vedieť, ako môžeme zlepšiť, doplniť alebo inovovať našu prácu a zlepšiť ju pre vás. Napíšte nám na emailovú stránku