Doslej smo se v tej seriji Validacija podatkov naučili ustvariti običajen spustni seznam in dinamični spustni seznam z različnimi tehnikami s preverjanjem podatkov v Excelu.
Danes vam bomo v tem poglavju pokazali, kako z različnimi metodami ustvariti odvisen spustni seznam v programu Microsoft Excel.
Odvisni spustni seznam je znan tudi kot kaskadno preverjanje podatkov in omejuje izbire na spustnem seznamu, odvisno od vrednosti, izbrane v drugi celici, ki vsebuje potrditev podatkov. Z drugimi besedami, od vrednosti, izbrane na prvem spustnem seznamu, je odvisno, katere vrednosti bodo prikazane na drugem spustnem seznamu.
To je zelo pogost scenarij dela z velikimi podatki ali nekaterimi dinamičnimi poročili, kjer želite imeti 2nd celica prikaže seznam, ki je odvisen od elementa seznama, izbranega v prvem spustnem meniju.
Ker vemo, da v Excelu obstaja veliko načinov za opravljanje določene naloge, podobno pa obstaja veliko načinov za ustvarjanje odvisnih potrditev podatkov v Excelu. Danes bomo pokazali 5 različnih tehnik za ustvarjanje odvisnega seznama preverjanja podatkov.
Neobdelani podatki so lahko v poljubnem vrstnem redu ali obliki in vsakič, ko ne morete spremeniti podatkov ali oblike, da dobite tisto, kar iščete.
Torej smo vzeli en niz podatkov, vendar v 3 različnih oblikah, da dobimo odvisen spustni seznam. In, kot vidite, so naši podatki na levi strani, ki je od stolpca A do stolpca E, in pričakovani izid bomo imeli na desni strani, ki je v stolpcu J & K. Stolpec J bo imel primarno potrditev seznam, ker bo stolpec K odvisen in bo prikazal vrednosti, odvisno od vrednosti, izbrane v stolpcu J.
1st Primer:-
2nd Primer:-
3rd Primer:-
1st Primer:-
Za vsako kodo izdelka imamo seznam izdelkov od stolpcev A8 do E13. Kodo izdelka želimo izbrati v J10, nato pa glede na izbrano kodo izdelka ime izdelka v celici K10.
Prva metoda:-
Prva metoda je zelo preprosta in kratka ter zahteva le 3 korake, da dobite odvisen spustni seznam. Vendar pa deluje le, dokler ne spremenite svojega obsega. Ko enkrat spremenite svoje podatke, boste morali najprej spremeniti imenovano območje, da dobite posodobljeno kaskadno preverjanje podatkov.
Sledite spodnjim korakom:-
- Izberite celotno tabelo od A8 do E13
- Nato pojdite na zavihek »Formule«, nato v kategoriji »Določena imena« kliknite »Ustvari iz izbora«
- Uporabite lahko tudi bližnjico na tipkovnici CTRL + SHIFT + F3
- Prikaže se pogovorno okno Ustvari imena iz izborov
- Zahteva, da potrdite, katere vrstice in stolpce uporabite za ustvarjanje imen drugih vrstic in stolpcev. Potrjujemo, da za ustvarjanje imen uporabimo »zgornjo vrstico« in počistimo polje 2nd možnost in nato kliknemo V redu
Opomba: - Presledki in drugi posebni znaki, razen podčrtaja in pike, niso dovoljeni kot imena. Privzeto se pretvori v podčrtaj. Zato za ločevanje besed uporabite podčrtaj in piko. Tudi prva črka ne more biti številka; mora biti črka, podčrtaj ali poševnica.
- Zdaj, da potrdimo, da ima vsak obseg ime, gremo v »Upravitelj imen« (pritisnite CTRL + F3)
- Tam lahko vidimo vseh 5 imenovanih razponov, ki so na voljo
- Prav tako lahko vidimo, da ima vsako ime obsega podčrtaj namesto praznega sredi niza
Zdaj bomo ustvarili spustni seznam:-
- Izberite celico J10 in pritisnite ALT ++ D+L, da odprete pogovorno okno Preverjanje podatkov
- Izberite Seznam> in na zavihku Vir vnesite obseg A8: E8
- Kliknite V redu
- Zdaj bomo v celici K10 ustvarili odvisen seznam
- Odprite pogovorno okno Preverjanje podatkov s pritiskom na tipko ALT+D+L
- Izberite Seznam, v viru vnesite to funkcijo:- = INDIRECT (SUBSTITUTE ($ J $ 10, "", "_"))
Pri preverjanju podatkov smo za ustvarjanje odvisnega seznama uporabili funkcijo INDIRECT za vrnitev vrednosti na podlagi seznama preverjanja primarnih podatkov. Za zamenjavo podčrtaja s presledkom bomo uporabili funkcijo SUBSTITUTE znotraj funkcije INDIRECT.
- Kliknite V redu
Ko izberemo katero koli kodo izdelka v celici J10, se bo v celici K10 prikazal seznam izdelkov izbrane kode izdelka. Na primer: - Izbrali smo ETV 501, zdaj lahko vidite, da se v celici K10 prikaže seznam odvisnih izdelkov
Opomba: - Kadar koli dodate ime izdelka in kodo izdelka, ki ne bosta prikazana na seznamu.
Na primer: - Pod oznako izdelka ETV 505 smo dodali izdelek 26, ko pa izberemo izdelek ETV 505, se dodani izdelek ne prikaže na spustnem seznamu.
Tako lahko s preprosto tehniko v samo treh preprostih korakih ustvarite odvisen spustni seznam.
2nd Primer:-
V tem primeru bomo videli, kako pridobiti odvisen spustni seznam, če imate svoje podatke, kot je prikazano v tej navpični tabeli.
Za izdelavo odvisnega spustnega seznama bomo uporabili dve različni metodi. Obe tehniki sta si skoraj enaki. Vendar pa je eden brez imenovanega obsega, drugi pa bo imenoval obseg.
1st Metoda:-
Enako bomo uporabili funkcije OFFSET, MATCH & COUNTIF skupaj.
Ker vemo, da se za ustvarjanje dinamičnega obsega uporablja funkcija OFFSET, zato za vrnitev dinamičnega območja uporabljamo funkcijo OFFSET.
MATCH se uporablja za vrnitev relativnega položaja elementa na seznamu v Excelu. In tukaj nam bo v pomoč, da uvrstimo kategorijo, izbrano na primarnem spustnem seznamu v našem obsegu na listu, in vrnil bo številko.
COUNTIF se uporablja za pridobivanje števila celic, ki ustrezajo kriterijem. Tukaj bomo to uporabili za štetje števila vrstic, ki se prikažejo s funkcijo COUNTIF.
Sledite spodnjim korakom:-
- Izberite celico J21, v kateri bomo ustvarili naš seznam preverjanja primarnih podatkov
- Pritisnite tipko ALT+D+L, da odprete pogovorno okno Preverjanje podatkov
- Izberite seznam iz dovoljene kategorije
- Kliknite na zavihek Vir in izberite obseg od B20: B24
- In kliknite V redu
- Pojdite v celico K21 in še enkrat odprite pogovorno okno za preverjanje podatkov
- Nato izberemo Seznam in v viru vnesemo spodnjo funkcijo:
- = OFFSET (19 EUR $
- Kliknite V redu
- V celici K21 lahko vidimo vse ustrezne vrednosti izbrane kode izdelka:-
Tako lahko odvisni seznam dobite tako, da v funkcijo vnesete sklice na celice.
2nd Metoda:-
V naslednji metodi bomo v isti funkciji uporabili imenovano območje za pridobitev kaskadne validacije podatkov. Najprej moramo ustvariti dinamični seznam za kodo izdelka. Če je v podatke dodan nov izdelek, je treba spustni meni posodobiti, da se prikaže isti.
Če želite storiti enako, sledite spodnjim korakom:-
- Izberite B19, nato pritisnite CTRL + F3, da odprete okno »Upravitelj imen«
- Zdaj kliknemo »Novo« in prikaže se pogovorno okno »Določi ime«
- Vidimo, da je ime že prikazano v polju z imenom -to je zato, ker smo izbrali B9, preden smo odprli okno »Upravitelj imen«. Ker ima B19 besedilo, ga lahko spremenimo v drugo ime.
- Spodaj vnesite formulo:-
= OFFSET ('DependentDropDownList'! $ B $ 20,0,0, COUNTA ('DependentDropDownList'! $ B $ 20: $ B $ 32))
- Kliknite V redu
Ker smo ustvarili dinamični seznam za edinstvene izdelke, bomo zdaj ustvarili dinamični razpon za obseg kod izdelkov, ki je v stolpcu D.
Sledite istim korakom, ki smo jih upoštevali pri edinstvenem izdelku:-
- Izberite celico D19, odprite pogovorno okno Določi ime
- Ugotovili boste, da je ime že tam
- V sklicevanju vnesite naslednjo formulo:-
= OFFSET ('Odvisni spustni seznam'! $ D $ 20,0,0, COUNTA ('Odvisni spustni seznam'! $ D $ 20: $ D $ 35))
- Kliknite V redu
- Zdaj sta pripravljena oba dinamična območja. Torej gremo na J22 in pritisnemo »ALT + D + L« in izberemo »Seznam«
- V viru bomo imeli imenovani obseg, ki smo ga opredelili za »Enotno kodo izdelka«, zato pritisnemo tipko F3, da vidimo vse razpoložljive imenovane obsege
- Vidimo lahko poimenovani obseg "Unique Product Code", zato ga kliknemo, nato pa kliknemo V redu in pritisnemo enter
- V trenutku, ko pritisnemo enter, dobimo spustno puščico v celici J22, ki vsebuje seznam edinstvenih kod izdelkov
- Izberite celico K22 in odprite pogovorno okno »Preverjanje podatkov«
- Uporabili bomo isto funkcijo, ki smo jo uporabili pri zadnji metodi, vendar z imenovanim obsegom
- Izberite seznam in nato v vir vnesite spodnjo formulo:-
= OFFSET ($ E $ 19, MATCH ($ J $ 22, Product_Code, 0), 0, COUNTIF (Product_Code, J22))
- Kliknite V redu
- Zdaj imamo primarni spustni meni in podrejen seznam izdelkov
- Izberite izdelek "ETV-101" iz J22, v K22 pa lahko vidimo samo imena, ki spadajo pod ta izdelek "ETV-101". Ko spremenimo kateri koli izdelek ("ETV-103) v J22, K22 prikaže ustrezne vrednosti za to kodo
Zdaj bomo videli, kaj se zgodi, ko na seznam dodamo novo kodo izdelka? Ali bodo ti spustni seznami posodobljeni?
Dodajmo nov izdelek na seznam; Sledite spodnjim korakom:-
- Na seznam Unique_Prod_Code dodajte kodo izdelka
- V podatke dodajte tudi kodo izdelka in ime izdelka:-
- Zdaj preverite, ali se na spustnem seznamu prikaže koda in ime izdelka
3rd Primer:-
Dinamične naslove imamo neposredno iz tabele, v ponudbo pa bomo dodali nove izdelke. Tabela je v istem formatu, ki smo ga uporabili za 1st metoda.
4th Metoda:-
Sledite spodnjim korakom:-
- Izberite naslov A40: E40
- Najprej ustvarite dinamični obseg za naslove, odprite pogovorno okno »Določi ime«
- Na mesto imena napišite »naslov« in nato v »nanaša se« vnesite spodnjo formulo:-
- Vnesite spodnjo funkcijo:-
- = OFFSET ('Odvisni spustni seznam'! $ A $ 40 ,,,, COUNTA ('Odvisni spustni seznam'! $ 40: $ 40))
- Kliknite V redu
- Dinamični obseg »Naslov« je zdaj pripravljen
Zdaj bomo ustvarili imenovani obseg za vsak naslov, sledite spodnjim korakom:-
- Izberite tabelo od A40 do E50
- CTRL + SHIFT + F3 bližnjica na tipkovnici
- Odznačimo 2nd možnost
- Preden kliknemo V redu, se prepričajte, da je 1st je izbrana možnost »Zgornja vrstica«
- Zdaj smo pripravljeni na oba razpona
Zdaj bomo pripravili spustni seznam staršev
- Izberite celico J42
- Odprite pogovorno okno Preverjanje podatkov
- Potem, ko izberemo »Seznam«, pritisnemo F3 v viru, da dobimo imenovano območje za naslove. Kliknemo »Naslov«, nato kliknemo V redu in pritisnemo enter. Zdaj imamo seznam staršev v J42
- Če želite ustvariti seznam podrobnosti postavke, izberite celicoK42
- Odprite pogovorno okno Preverjanje podatkov s pritiskom na tipko ALT+D+L
- Izberite Seznam in vnesite spodnjo funkcijo na zavihku Vir:-
- = OFFSET (NEPOSREDNO (SUBSTITUTE ($ J $ 42, "", "_")) ,,, COUNTA (INDIRECT (SUBSTITUTE ($ J $ 42, "", "_"))))
- Kliknite V redu
Zdaj izberite nekaj v J42, recimo, da izberemo »postavko 01« in si ogledamo spustni seznam K42. Tako kot prejšnje 3 metode imamo tudi tukaj odvisen seznam.
Torej kaj je novega? V prvem primeru na seznam ni bilo mogoče dodati nobenega izdelka, tukaj pa lahko dodate kateri koli nov izdelek. Torej, recimo temu izdelku dodamo nov izdelek. Gremo na A45 in vtipkamo “ETV-501 Prod 05”, nato pa se vrnemo na K42 in tukaj ste. Vidite, nov izdelek je bil dodan.
- Zdaj dodajte nekaj izdelkov pod novo postavko
Ko izberemo “Item 06”, gremo na K42 in kliknemo spustni seznam. Presenetljivo je, da se s klikom na spustno puščico ne zgodi nič. To je zato, ker smo ustvarili vse dinamično in pozabili ustvariti dinamični razpon za mizo, zato izdelki niso prikazani na podrejenem seznamu.
Za to moramo uporabiti različne tehnike. Obstajata dva načina za to. Tabelo lahko ustvarite ali preprosto uporabite samo funkcijo OFFSET. V naslednji metodi bomo uporabili funkcijo OFFSET in videli bomo trik za razširitev obsega tabel.
- Torej, najprej gremo na J43 in pritisnemo »ALT + D + L«
- Izberemo »Seznam« in nato v viru pritisnemo F3 in izberemo »Naslov«, kliknemo V redu in nato pritisnemo enter
- Zdaj gremo na K43 in po izbiri »Seznam« gremo na »Vir« in vnesemo spodnjo funkcijo
= OFFSET ($ 40,1 $, MATCH ($ J $ 43, $ 40: $ 40,0) -1, COUNT , 1)))
- Kliknite V redu
Zdaj se vrnemo in v celici J43 izberemo »postavko 06 in se vrnemo na K43 ter kliknemo puščico navzdol. Toda ta časovni seznam prikazuje izdelke, ki smo jih dodali za nov izdelek. Izbrali smo prvi izdelek “ETV-506 Prod 01”.
Tako lahko ustvarite odvisen spustni seznam z različnimi metodami za vse vrste podatkovnih oblik.
Video: Kako ustvariti odvisen (kaskaden) spustni seznam v Excelu z uporabo 5 različnih tehnik v programu Microsoft Excel
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