V tem članku se bomo naučili ustvariti dinamični spustni seznam v programu Microsoft Excel.
Kot vemo, funkcija preverjanja podatkov izboljšuje učinkovitost vnosa podatkov v excelu in zmanjšuje napake in tipkarske napake. Uporablja se za omejitev uporabnika glede vrste podatkov, ki jih je mogoče vnesti v obseg. V primeru neveljavnega vnosa prikaže sporočilo in uporabniku omogoča vnos podatkov glede na določeno stanje.
Toda dinamični spustni seznam v Excelu je bolj priročen način za izbiro podatkov, ne da bi pri tem spremenili vir. Z drugimi besedami, recite, da boste pogosto posodabljali seznam, ki ste ga vzeli na spustnem seznamu. In če razmišljate o spremembah na seznamu, morate vsakič spremeniti potrditev podatkov, da dobite posodobljen spustni seznam.
Toda tu nastopi dinamični spustni meni, zato je najboljša možnost, da izberete podatke, ne da bi pri tem potrdili podatke. Je zelo podoben običajni validaciji podatkov. Ko pa posodobite seznam, se dinamični spustni seznam spremeni tako, da ustreza temu dejanju, medtem ko običajni spustni seznam ne.
Torej, vzemimo primer in razumejmo, kako ustvarimo dinamični spustni seznam:-
V stolpcu A imamo seznam izdelkov in imeli bomo dinamični spustni seznam izdelkov v celici D9.
Ime tabele s posredno funkcijo
Najprej bomo ustvarili tabelo; sledite spodnjim korakom:-
- Izberite obseg A8: A16
- Pojdite na zavihek Vstavi in kliknite Tabela
- Ko kliknete možnost »Tabela«, se prikaže okno Tabela
- Nato izberite obseg, za katerega želimo vstaviti tabelo A8: A17
- Kliknite V redu
- Sedaj kliknemo V redu
- Vidite lahko, da je to območje pretvorjeno v tabelo, v glavi te tabele pa je tudi možnost spustnega filtra
Opomba: - Če na dno seznama dodamo kateri koli izdelek ali izdelek, se bo tabela samodejno razširila in vključila nove izdelke ali postavke.
Zdaj ustvarimo dinamični spustni seznam v celici D9 in sledimo spodnjim korakom:-
- Izberite celico D9
- Odprite pogovorno okno Preverjanje podatkov s pritiskom na tipko ALT+D+L
- Na spustnem seznamu Dovoli izberite Seznam
- Nato vnesite to funkcijo = INDIRECT ("Tabela1") na zavihku vir
- Kliknite V redu
Opomba: - Ko kliknemo V redu, se v Excelu prikaže okno, ki pravi, da je z vnosom nekaj narobe. To je zato, ker Excel ne sprejema samorazširljive tabele neposredno v preverjanju podatkov.
Zdaj dodajte nove izdelke na seznam izdelkov.
Na zgornji sliki lahko vidimo, da se na spustnem seznamu pojavlja nov dodani izdelek.
2nd Primer:-
V tem primeru se bomo naučili, kako ime tabele podati kot ime z razponom
Ime tabele že imamo, vendar moramo tukaj določiti ime te tabele, da dobimo dinamični spustni seznam; sledite spodnjim korakom:-
- Izberite celico D10
- Pojdite na obseg tabel in razen glave izberemo obseg od prvega do zadnjega izdelka
- Pojdite v polje z imenom in vnesite kratko ime "tabela", pritisnite Enter
- Ko pritisnemo enter, v polju z imenom ne vidimo ničesar
- Kliknite možnost spustnega seznama, če si želite ogledati vse imenovane obsege, ki so na voljo
- Na spustnem seznamu je prikazano tudi ime, ki smo ga pravkar določili za to tabelo
- Zdaj gremo na potrditev podatkov in v »Vir« vnesemo »obseg tabel«
Opomba:- Če se ne spomnite, kakšno ime ste dali temu obsegu, lahko pritisnete tipko F3 in pojavilo se bo okno, ki vam bo predlagalo vse razpoložljive imenovane obsege.
- Zdaj pojdite na zavihek »Vnosno sporočilo« in v naslov vtipkamo »Izberi izdelek«, nato pa v telo sporočila napišemo »Prosimo, izberite svoj izdelek s seznama«
- Zdaj pojdite na zavihek »Opozorilo o napaki« in tam v naslovu napišemo »Neveljaven izdelek«, v sporočilu o napaki pa vnesemo »Vnesli ste napačen izdelek
- Kliknite V redu
- Celica D10, ki vsebuje vhodno sporočilo skupaj s spustnim seznamom
- Ko dodamo kateri koli izdelek na seznam, se bo samodejno pojavil na spustnem seznamu
Kaj pa se zgodi, če preskočimo eno celico za zadnjo celico in nato dodamo nov izdelek ali izdelek? Vidite, tokrat se obseg tabel ni razširil in pravzaprav je na novo dodani izdelek v splošni obliki. Bo torej prikazan na spustnem seznamu ali ne? Če želimo to preveriti, lahko na celici D10 in na spustnem seznamu vidimo isti stari spustni seznam brez novega izdelka. To je zato, ker obseg tabele po zadnji celici ni našel ničesar, zato se obseg ni porabil.
3rd Primer:-
V naslednjih dveh metodah se bomo naučili, kako lahko naredimo naš spustni seznam bolj dinamičen z uporabo funkcij OFFSET in COUNTA.
Sledite spodnjim korakom:-
- Izberite celico D11 in pritisnite ALT + D + L
- Odpre se pogovorno okno Preverjanje podatkov
- Zdaj izberite seznam v možnosti »Dovoli«
- Nato v možnosti Vir vnesite spodnjo formulo:-
= OFFSET ($ A $ 9,0,0, COUNTA ($ A: $ A), 1)
Pojasnilo formule:- Izbrali smo A9, ki je prvi izdelek v ponudbi, nato pa na 2 vnesemo 0nd argument, ker ne želimo premakniti vrstice iz izhodišča; nato spet 0 v 3rd argument, saj tukaj ne želimo sprememb v številu stolpcev, pa tudi od izhodišča. Nato smo vnesli funkcijo COUNTA in izbrali celoten stolpec A. Ta argument bo preveril višino v številu vrstic in vrnil število, ki ni prazno. Obseg bo razširil, ko bodo v razponu spremenjene.
In zadnji argument "Širina" je neobvezen argument. To je širina v številu stolpcev. Lahko ga preskočimo ali pa za zdaj vnesemo 1 tukaj. Če preskočimo, bo privzeto upošteval širino vrnjenega obsega, ki smo ga podali v argumentu, in nato zaprli oklepaje.
- Po kliku na V redu lahko vidimo spustni seznam v celici D11
- Prikaže seznam, vključno s praznimi in nato izdelke, ki smo jih dodali
4th Primer:-
V tem primeru bomo funkcijo uporabili za opredelitev imena.
Če želite določiti ime obsega, sledite spodnjim korakom:-
- Pritisnite CTRL + F3, odpre se pogovorno okno Upravitelj imen
- Kliknite na Novo
- Določite ime obsega »Ime imena« in vnesite spodnjo formulo:-
= OFFSET ('Dinamični spustni seznam z DV'! $ 9,0,0 $, COUNTA ('Dinamični spustni seznam z DV'! $ A: $ A))
- Kliknite V redu
- Odprite pogovorno okno Preverjanje podatkov s pritiskom na tipki Alt + D + L
- Na spustnem seznamu Dovoli izberite Seznam
- Vnesite = ime podjetja na zavihku Vir
- Kliknite V redu
- Če na seznam dodamo karkoli, se bo na seznamu pojavilo isto
Tako lahko z validacijo podatkov dobite dinamični seznam za kateri koli izdelek ali izdelek z različnimi metodami. To je vse za zdaj. V naslednjem videoposnetku te serije bomo razložili, kako z različnimi metodami v Excelu ustvariti odvisen spustni seznam.
Kliknite na video povezavo za hiter pregled uporabe. Naročite se na naš novi kanal in se učite skupaj z nami!
Če so vam bili naši blogi všeč, jih delite s prijatelji na Facebooku. Prav tako nas lahko spremljate na Twitterju in Facebooku.
Radi bi slišali od vas, nam sporočite, kako lahko izboljšamo, dopolnimo ali inoviramo svoje delo in ga izboljšamo. Pišite nam na spletni strani elektronske pošte