Kako stvoriti ovisni (kaskadni) padajući popis u Excelu pomoću 5 različitih tehnika

Sadržaj:

Anonim

Do sada smo u ovoj seriji provjere valjanosti podataka naučili stvarati normalan padajući popis i dinamički padajući popis pomoću različitih tehnika s provjerom valjanosti podataka u Excelu.

Danas ćemo vam u ovom poglavlju pokazati kako stvoriti ovisni padajući popis u programu Microsoft Excel različitim metodama.

Ovisni padajući popis poznat je i kao kaskadno provjeravanje podataka i ograničava izbore na padajućem popisu, ovisno o vrijednosti odabranoj u drugoj ćeliji koja sadrži provjeru valjanosti podataka. Drugim riječima, ovisi o vrijednosti odabranoj na prvom padajućem popisu koja određuje vrijednosti koje će se prikazati na drugom padajućem popisu.

Ovo je vrlo čest scenarij rada s velikim podacima ili nekim dinamičkim izvješćima, gdje želite imati 2nd ćelija prikazuje popis koji ovisi o stavci popisa odabranoj u prvom padajućem izborniku.

Kao što znamo da u Excelu postoji mnogo načina za obavljanje određenog zadatka, a slično tome, postoji mnogo načina za stvaranje ovisne provjere valjanosti podataka u Excelu. Danas ćemo pokazati 5 različitih tehnika za stvaranje ovisnog popisa provjere valjanosti podataka.

Sirovi podaci mogu biti bilo kojim redoslijedom ili formatom i svaki put ne možete promijeniti podatke ili format kako biste dobili ono što tražite.

Dakle, uzeli smo jedan skup podataka, ali u 3 različita formata kako bismo dobili ovisni padajući popis. Kao što vidite, naši su podaci s lijeve strane, od stupca A do stupca E, a očekivani izlaz ćemo imati s desne strane, koja je u stupcu J & K. Stupac J će imati primarnu provjeru valjanosti popisa dok će stupac K biti ovisan i prikazat će vrijednosti ovisno o vrijednosti odabranoj u stupcu J.

1sv Primjer:-

2nd Primjer:-

3rd Primjer:-

1sv Primjer:-

Za svaki kod proizvoda imamo popis proizvoda od stupca A8 do E13. I želimo odabrati šifru proizvoda u J10, a zatim, ovisno o odabranoj šifri proizvoda, naziv proizvoda u ćeliji K10.

Prva metoda:-

Prva metoda je vrlo jednostavna i kratka i zahtijeva samo 3 koraka da biste dobili ovisni padajući popis. Međutim, uspješno radi samo dok ne promijenite raspon. Nakon što izmijenite svoje podatke, morat ćete prvo izmijeniti imenovani raspon kako biste dobili ažuriranu kaskadnu provjeru podataka.

Slijedite dolje navedene korake:-

  • Odaberite cijelu tablicu od A8 do E13

  • Zatim idite na karticu "Formule", a zatim u kategoriji "Definirani nazivi" kliknite "Stvori iz odabira"
  • Također možete koristiti tipkovnički prečac CTRL + SHIFT + F3
  • Pojavit će se dijaloški okvir Stvaranje imena iz odabira

  • Traži potvrdu koji će se retci i stupac koristiti za stvaranje naziva za druge retke i stupce. Potvrđujemo da koristimo "gornji red" za stvaranje imena i poništimo oznaku 2nd opciju, a zatim kliknite U redu

Bilješka: - Razmaci i drugi posebni znakovi osim podcrtavanja i točke nisu dopušteni kao imena. Prema zadanim postavkama, pretvara se u podcrtavanje. Dakle, koristite znak podcrtavanja i točku za odvajanje riječi. Također, prvo slovo ne može biti broj; to mora biti slovo, donja crta ili kosa crta.

  • Sada, kako bismo potvrdili da svaki raspon ima naziv, idemo u "Upravitelj imena" (pritisnite CTRL + F3)
  • Tamo možemo vidjeti svih 5 imenovanih raspona koji su dostupni
  • Također možemo vidjeti da svaki naziv raspona ima donju crtu umjesto praznog u sredini niza

Sada ćemo stvoriti padajući popis:-

  • Odaberite ćeliju J10 i pritisnite ALT ++ D+L da biste otvorili dijaloški okvir Validacija podataka
  • Odaberite Popis> zatim unesite raspon A8: E8 na kartici Izvor

  • Pritisnite OK
  • Sada ćemo stvoriti ovisni popis u ćeliji K10
  • Otvorite dijaloški okvir Validacija podataka pritiskom na tipke ALT+D+L
  • Odaberite popis, u izvoru unesite ovu funkciju:- = INDIREKTNA (ZAMJENA ($ J $ 10, "", "_"))

Za provjeru valjanosti podataka za izradu ovisnog popisa koristili smo funkciju INDIRECT za vraćanje vrijednosti na temelju popisa provjere primarnih podataka. Za zamjenu podcrtavanja razmakom koristit ćemo funkciju ZAMJENA unutar funkcije INDIRECT.

  • Pritisnite OK

Kad odaberemo bilo koji kôd proizvoda u ćeliji J10, tada će se popis proizvoda odabranog koda proizvoda pojaviti u ćeliji K10. Na primjer: - Odabrali smo ETV 501, sada možete vidjeti da se popis ovisnih proizvoda pojavljuje u ćeliji K10

Bilješka: - Kad god dodate naziv proizvoda i kôd proizvoda koji se neće pojaviti na popisu.

Na primjer: - Dodali smo Proizvod 26 pod šifrom proizvoda ETV 505, ali kada odaberemo proizvod ETV 505 tada se dodani proizvod ne pojavljuje na padajućem popisu.

Dakle, ovako možete stvoriti ovisni padajući popis pomoću jednostavne tehnike u samo 3 jednostavna koraka.

2nd Primjer:-

U ovom primjeru vidjet ćemo kako dobiti ovisni padajući popis ako imate svoje podatke kao što je prikazano u ovoj okomitoj tablici.

Koristit ćemo dvije različite metode za stvaranje ovisnog padajućeg popisa. Obje su gotovo slične tehnike. Međutim, jedan je bez imenovanog raspona, a drugi će imati imenovani raspon.

1sv Metoda:-

Da bismo učinili isto, zajedno ćemo koristiti funkcije OFFSET, MATCH & COUNTIF.

Budući da znamo da se funkcija OFFSET koristi za stvaranje dinamičkog raspona, stoga za izradu popisa “Validacija dinamičkih podataka” koristimo funkciju OFFSET za vraćanje dinamičkog raspona.

MATCH se koristi za vraćanje relativnog položaja stavke na popisu u Excelu. I ovdje će nam pomoći da u listu raspona uparimo kategoriju odabranu na primarnom padajućem popisu u našem rasponu i vratit će broj.

COUNTIF se koristi za dobivanje broja ćelija koje odgovaraju kriterijima. Ovdje ćemo upotrijebiti ovo za brojanje redova koji će se prikazati pomoću funkcije COUNTIF.

Slijedite dolje navedene korake:-

  • Odaberite ćeliju J21 u kojoj ćemo stvoriti naš popis provjere primarnih podataka
  • Pritisnite tipku ALT+D+L da biste otvorili dijaloški okvir Validacija podataka
  • Odaberite popis iz dopuštene kategorije
  • Pritisnite karticu Izvor i odaberite raspon od B20: B24

  • I kliknite OK

  • Idite na ćeliju K21 i još jednom otvorite dijaloški okvir za provjeru valjanosti podataka
  • Zatim odabiremo popis i u izvoru unosimo donju funkciju:
  • = OFFSET ($ E $ 19, MATCH ($ J $ 21, $ D $ 20: $ D $ 32,0), 0, COUNTIF ($ D $ 20: $ D $ 32, $ J $ 21))

  • Pritisnite OK
  • U ćeliji K21 možemo vidjeti sve odgovarajuće vrijednosti odabranog koda proizvoda:-

Dakle, ovako možete dobiti ovisni popis uzimanjem referenci ćelija u funkciji.

2nd Metoda:-

U sljedećoj metodi koristit ćemo imenovani raspon u istoj funkciji za dobivanje kaskadne provjere valjanosti podataka. Prvo moramo stvoriti dinamički popis za šifru proizvoda. U slučaju da se u podatke doda novi proizvod, padajući izbornik treba ažurirati kako bi se prikazao isti.

Da biste učinili isto, slijedite dolje navedene korake:-

  • Odaberite B19, zatim pritisnite CTRL + F3 da biste otvorili prozor "Upravitelj imena"
  • Sada kliknite na "Novo" i pojavit će se dijaloški okvir "Definiraj naziv"
  • Možemo vidjeti, naziv se već pojavljuje u okviru s imenom -to je zato što smo odabrali B9 prije otvaranja prozora "Upravitelj imena". A kako B19 ima tekst u sebi, ako ga želimo, možemo ga promijeniti u neki drugi naziv.

  • Unesite formulu za spominjanje ispod:-

= OFFSET ('DependentDropDownList'! $ B $ 20,0,0, COUNTA ('DependentDropDownList'! $ B $ 20: $ B $ 32))

  • Pritisnite OK

Kako smo stvorili dinamički popis za jedinstvene proizvode, sada ćemo stvoriti dinamički raspon za šifru proizvoda koji se nalazi u stupcu D.

Slijedite iste korake koje smo slijedili za jedinstveni proizvod:-

  • Odaberite ćeliju D19, otvorite dijaloški okvir Definiraj naziv
  • Vidjet ćete da je ime već tamo
  • U uputama unesite donju formulu:-

= OFFSET ('Ovisni padajući popis'! $ D $ 20,0,0, COUNTA ('Ovisni padajući popis'! $ D $ 20: $ D $ 35))

  • Pritisnite OK
  • Sada su oba dinamička raspona spremna. Dakle, idemo na J22 i pritisnemo "ALT + D + L" i odaberemo "Popis"
  • U izvoru ćemo imati imenovani raspon koji smo definirali za “Jedinstveni kôd proizvoda” pa pritisnemo F3 da vidimo sve dostupne imenovane raspone
  • Možemo vidjeti naziv "Jedinstveni kôd proizvoda", pa ga kliknemo, a zatim pritisnemo U redu i pritisnemo enter

  • U trenutku kada pritisnemo enter, u ćeliji J22 dobivamo padajuću strelicu koja sadrži popis jedinstvenih kodova proizvoda

  • Odaberite ćeliju K22 i otvorite dijaloški okvir "Validacija podataka"
  • Koristit ćemo istu funkciju koju smo koristili u prošloj metodi, ali s imenovanim rasponom
  • Odaberite popis, a zatim u izvoru unesite formulu ispod:-

= OFFSET ($ E $ 19, MATCH ($ J $ 22, Product_Code, 0), 0, COUNTIF (Product_Code, J22))

  • Pritisnite OK
  • Sada imamo primarni padajući izbornik kao i podređeni popis proizvoda
  • Odaberite proizvod “ETV-101” iz J22, a u K22 možemo vidjeti samo nazive koji potpadaju pod ovaj proizvod “ETV-101”. A kad promijenimo bilo koji proizvod (“ETV-103) u J22, K22 prikazuje odgovarajuće vrijednosti za taj kôd

Sada ćemo vidjeti što će se dogoditi kada na popis dodamo bilo koji novi kod proizvoda? Hoće li se ovi padajući popisi ažurirati?

Dodajmo novi proizvod na popis; Slijedite dolje navedene korake:-

  • Dodajte kôd proizvoda na popis Unique_Prod_Code

  • U podatke dodajte i Product_Code i Product_Name:-

  • Sada provjerite pojavljuje li se kôd proizvoda i naziv s padajućeg popisa

3rd Primjer:-

Dinamičke naslove imamo izravno iz tablice, a asortimanu ćemo dodati nove proizvode. Tablica je u istom formatu koji smo koristili za 1sv metoda.

4th Metoda:-

Slijedite dolje navedene korake:-

  • Odaberite naslov A40: E40
  • Prvo stvorite dinamički raspon za naslove, otvorite dijaloški okvir "Definiraj naziv"
  • Napišite "Naslov" na mjestu imena, a zatim u "odnosi se na" unesite dolje navedenu formulu:-
  • Unesite donju funkciju:-
  • = OFFSET ('Ovisni padajući popis'! $ 40 USD ,,,, COUNTA ('Ovisni padajući popis'! 40 USD: 40 USD))
  • Pritisnite OK

  • Dinamički raspon naslova sada je spreman

Sada ćemo stvoriti imenovani raspon za svaki naslov, slijedite dolje navedene korake:-

  • Odaberite tablicu od A40 do E50
  • CTRL + SHIFT + F3 tipkovni prečac
  • Poništimo oznaku 2nd opcija
  • I prije nego što kliknemo na U redu, provjerite 1sv odabrana je opcija "Gornji red"

  • Sada smo spremni za oba raspona

Sada ćemo pripremiti padajući popis roditelja

  • Odaberite ćeliju J42
  • Otvorite dijaloški okvir Validacija podataka

  • Zatim nakon odabira “Popis”, pritisnemo F3 u izvoru da bismo dobili imenovani raspon za naslove. Kliknemo na “Naslov”, zatim pritisnemo U redu i pritisnemo enter. Sada imamo popis roditelja u J42

  • Da biste stvorili popis pojedinosti stavke, odaberite ćelijuK42
  • Otvorite dijaloški okvir Validacija podataka pritiskom na tipke ALT+D+L
  • Odaberite Popis, a zatim unesite donju funkciju na kartici Izvor:-
  • = OFFSET (INDIREKTNO (ZAMJENA ($ J $ 42, "", "_")) ,,, COUNTA (INDIREKTNO (ZAMJENA ($ J $ 42, "", "_")))

  • Pritisnite OK

Sada odaberite neku stavku u J42, recimo da odaberemo "Stavka 01" i pogledajte padajući popis K42. Kao i prethodne 3 metode, i ovdje imamo ovisni popis.

Dakle, što je novo? U prvom primjeru niste mogli dodati nijedan proizvod na popis, ali ovdje možete dodati bilo koji novi proizvod. Dakle, recimo da ovoj stavci dodamo novi proizvod. Idemo na A45 i upisujemo “ETV-501 Prod 05”, a zatim se vraćamo na K42 i evo vas. Vidite, novi proizvod je dodan.

  • Sada dodajte nekoliko proizvoda pod novu stavku

Kad odaberemo “Stavka 06”, idemo na K42 i kliknemo na padajući popis. Iznenađujuće, ništa se ne dogodi kada kliknemo strelicu padajućeg izbornika. To je zato što smo stvorili sve dinamično i zaboravili stvoriti dinamički raspon za tablicu, zbog čega se proizvodi ne prikazuju na podređenom popisu.

Da bismo to učinili, moramo koristiti različite tehnike. Postoje dvije metode za to. Možete stvoriti tablicu ili jednostavno upotrijebiti samo funkciju OFFSET. U sljedećoj ćemo metodi koristiti funkciju OFFSET, a vidjet ćemo i trik za proširenje raspona tablica.

  • Dakle, prvo idemo na J43 i pritisnemo “ALT + D + L”
  • Odabiremo "Popis", a zatim u izvoru pritisnemo F3 i odaberemo "Zaglavlje", pritisnemo U redu, a zatim pritisnemo enter

  • Sada idemo na K43, a nakon odabira “Popis”, idemo na “Izvor” i ulazimo u dolje navedenu funkciju

= OFFSET ($ 40,1 $, MATCH ($ J $ 43, $ 40: $ 40,0) -1, COUNTA (OFFSET ($ A $ 40,1, MATCH ($ J $ 43, $ 40: $ 40,0) -1,1000 , 1)))

  • Pritisnite OK

Sada se vraćamo i odabiremo „Stavka 06 u ćeliji J43 te se vraćamo na K43 i kliknemo strelicu padajućeg izbornika. No, ovaj popis prikazuje proizvode koje smo dodali za novu stavku. Odabrali smo prvi proizvod “ETV-506 Prod 01”.

Ovako možete stvoriti ovisni padajući popis pomoću različitih metoda za bilo koju vrstu formata podataka.

Video: Kako stvoriti ovisni (kaskadni) padajući popis u Excelu pomoću 5 različitih tehnika u Microsoft Excelu

Kliknite na video vezu za brzu referencu o upotrebi. Pretplatite se na naš novi kanal i nastavite učiti s nama!

Ako vam se sviđaju naši blogovi, podijelite ih sa svojim prijateljima na Facebooku. Također nas možete pratiti na Twitteru i Facebooku.
Voljeli bismo čuti vaše mišljenje, javite nam kako možemo poboljšati, nadopuniti ili inovirati svoj rad i učiniti ga boljim za vas. Pišite nam na web stranici e -pošte