Så langt i denne datavalideringsserien har vi lært å lage normal nedtrekksliste og dynamisk rullegardinliste ved hjelp av forskjellige teknikker med datavalidering i Excel.
Og i dag, i dette kapitlet, vil vi vise deg hvordan du lager avhengige rullegardinliste i Microsoft Excel ved hjelp av forskjellige metoder.
Avhengig av rullegardinlisten er også kjent som datavalidering i kaskader, og den begrenser valgene i en nedtrekksliste, avhengig av verdien som er valgt i den andre cellen som inneholder datavalidering. Med andre ord, det avhenger av verdien som er valgt i den første nedtrekkslisten som bestemmer verdiene som skal vises i den andre nedtrekkslisten.
Dette er et veldig vanlig scenario for å jobbe med store data eller noen dynamiske rapporter, der du vil ha 2nd celle vise en liste som er avhengig av listeelementet som ble valgt i den første rullegardinlisten.
Som vi vet at det i excel er mange måter å utføre en bestemt oppgave på, og på samme måte er det mange måter å lage avhengig datavalidering i Excel. Og i dag vil vi demonstrere 5 forskjellige teknikker for å lage avhengig datavalideringsliste.
Rådata kan være i hvilken som helst rekkefølge eller format, og hver gang kan du ikke endre dataene eller formatet for å få det du leter etter.
Så vi har tatt ett datasett, men i 3 forskjellige formater for å få den avhengige rullegardinlisten. Og, som du kan se, er dataene våre på venstre side fra kolonne A til kolonne E, og vi vil ha vår forventede utgang på høyre side i kolonne J og K. Kolonne J kommer til å ha den primære valideringen liste mens kolonnen K vil være avhengig og vil vise verdiene avhengig av verdien som er valgt i kolonnen J.
1st Eksempel:-
2nd Eksempel:-
3rd Eksempel:-
1st Eksempel:-
Vi har produktliste for hver produktkode fra kolonne A8 til E13. Og vi vil velge produktkoden i J10, og deretter, avhengig av den valgte produktkoden, et produktnavn i celle K10.
Første metode:-
Den første metoden er veldig enkel og kort, og det krever bare 3 trinn for å få den avhengige rullegardinlisten. Det fungerer imidlertid bare vellykket til du ikke gjør noen endringer i området ditt. Når du endrer dataene dine, må du først endre det navngitte området for å få den oppdaterte validering av kaskadedata.
Følg trinnene nedenfor:-
- Velg hele tabellen fra A8 til E13
- Gå deretter til kategorien "Formler", og klikk deretter på "Opprett fra utvalg" under kategorien "Definerte navn"
- Du kan også bruke hurtigtasten CTRL + SKIFT + F3
- Dialogboksen Opprett navn fra valg vises
- Den ber om å bekrefte hvilke rader og kolonner som skal brukes til å lage navnene på andre rader og kolonner. Vi bekrefter å bruke "øverste rad" for å lage navnene og fjerne merket for 2nd alternativet, og deretter klikker vi på OK
Merk: - Mellomrom og andre spesialtegn unntatt understreking og punktum er ikke tillatt som navn. Som standard blir den konvertert til understreking. Så bruk understreketegn og punktum for å skille ord. Den første bokstaven kan heller ikke være et tall; det må være en bokstav, en understreking eller en skråstrek.
- For å bekrefte at hvert område har et navn, går vi til "Name Manager" (trykk CTRL + F3)
- Der kan vi se alle 5 navngitte områder tilgjengelig
- Og vi kan også se at hvert områdenavn har understreking i stedet for tomt i midten av strengen
Nå lager vi rullegardinlisten:-
- Velg celle J10 og trykk ALT ++ D+L for å åpne dialogboksen Datavalidering
- Velg Liste> og angi deretter området A8: E8 i kategorien Kilde
- Klikk på OK
- Nå vil vi lage en avhengig liste i celle K10
- Åpne dialogboksen Datavalidering ved å trykke ALT+D+L
- Velg Liste, i kilden, skriv inn denne funksjonen:- = INDIRECT (SUBSTITUTE ($ J $ 10, "", "_"))
I datavalidering, for å lage den avhengige listen, har vi brukt INDIRECT -funksjonen for å returnere verdien basert på den primære datavalideringslisten. Og, for å erstatte understreking med plass, vil vi bruke SUBSTITUTE -funksjonen inne i INDIRECT -funksjonen.
- Klikk på OK
Når vi velger en produktkode i celle J10, vil produktlisten over den valgte produktkoden vises i celle K10. For eksempel: - Vi har valgt ETV 501, nå kan du se avhengig produktliste som vises i celle K10
Merk: - Når du legger til produktnavn og produktkode som ikke vises i listen.
For eksempel: - Vi har lagt til produkt 26 under ETV 505 -produktkoden, men når vi velger ETV 505 -produkt, vises ikke det ekstra produktet i rullegardinlisten.
Så, slik kan du lage avhengig rullegardinliste ved hjelp av enkel teknikk i bare 3 enkle trinn.
2nd Eksempel:-
I dette eksemplet vil vi se hvordan du får en avhengig rullegardinliste når du har dataene dine som vist i denne vertikale tabellen.
Vi vil bruke to forskjellige metoder for å lage avhengig rullegardinliste. Begge er nesten like teknikker. Den ene er imidlertid uten det navngitte området, og den andre vil ha det navngitte området.
1st Metode:-
For å gjøre det samme, bruker vi OFFSET, MATCH & COUNTIF -funksjoner sammen.
Siden vi vet at OFFSET -funksjonen brukes til å lage det dynamiske området, og derfor bruker vi OFFSET -funksjonen til å returnere det dynamiske området for å lage en liste over "dynamisk datavalidering".
MATCH brukes til å returnere den relative posisjonen til et element i en liste i Excel. Og her vil det hjelpe oss å matche kategorien som er valgt i den primære nedtrekkslisten i vårt område på arket, og det vil returnere et tall.
Og COUNTIF brukes til å få antallet celler som samsvarer med kriterier. Og her vil vi bruke dette til å telle antall rader som skal vises ved å bruke COUNTIF -funksjonen.
Følg trinnene nedenfor:-
- Velg cellen J21, der vi vil lage vår primære datavalideringsliste
- Trykk på tasten ALT+D+L for å åpne dialogboksen Datavalidering
- Velg liste fra kategorien Tillat
- Klikk på Kilde -fanen og velg området fra B20: B24
- Og klikk på OK
- Gå til celle K21 og åpne dialogboksen for datavalidering igjen
- Deretter velger vi Liste og, i kilde, skriver du inn funksjonen nedenfor:
- = OFFSET ($ E $ 19, MATCH ($ J $ 21, $ D $ 20: $ D $ 32,0), 0, COUNTIF ($ D $ 20: $ D $ 32, $ J $ 21))
- Klikk på OK
- I celle K21 kan vi se alle de tilsvarende verdiene for valgt produktkode:-
Så slik kan du få den avhengige listen ved å ta cellereferanser i funksjonen.
2nd Metode:-
I den neste metoden vil vi bruke navngitt område i samme funksjon for å få validering av kaskadedata. Først må vi lage den dynamiske listen for produktkode. Hvis et nytt produkt er lagt til dataene, bør rullegardinlisten oppdateres for å vise det samme.
For å gjøre det samme, følg trinnene nedenfor:-
- Velg B19, og trykk deretter CTRL + F3 for å åpne "Name Manager" -vinduet
- Nå klikker vi på "Ny" og "Definer navn" dialogboksen vises
- Vi kan se, navnet vises allerede i navnefeltet -det er fordi vi har valgt B9 før vi åpnet "Name Manager" -vinduet. Og ettersom B19 har tekst i den, kan vi endre den til et annet navn hvis vi vil.
- Skriv inn formelen nedenfor:-
= OFFSET ('DependentDropDownList'! $ B $ 20,0,0, COUNTA ('DependentDropDownList'! $ B $ 20: $ B $ 32))
- Klikk på OK
Siden vi har opprettet en dynamisk liste for unike produkter, vil vi nå opprette et dynamisk område for produktkodeområdet som er i kolonne D.
Følg de samme trinnene som vi har fulgt for unikt produkt:-
- Velg cellen D19, åpne dialogboksen Definer navn
- Du vil finne navnet er allerede der
- Skriv inn formelen nedenfor i referanser:-
= OFFSET ('Dependent Drop Down List'! $ D $ 20,0,0, COUNTA ('Dependent Drop Down List'! $ D $ 20: $ D $ 35))
- Klikk på OK
- Nå er begge dynamiske områdene klare. Så vi går til J22 og trykker “ALT + D + L” og velger “Liste”
- I kilden vil vi ha det navngitte området som vi definerte det for "Unik produktkode", så vi trykker på F3 for å se alle tilgjengelige navngitte områder
- Vi kan se "Unique Product Code" navngitt utvalg, så vi klikker på den og klikker deretter OK og vi trykker enter
- I det øyeblikket vi trykker enter, får vi nedtrekkspilen i celle J22, som inneholder listen over unike produktkoder
- Velg celle K22 og åpne dialogboksen "Datavalidering"
- Vi vil bruke den samme funksjonen som vi har brukt i den siste metoden, men med navngitt område
- Velg liste, og angi formelen nedenfor i kilden:-
= OFFSET ($ E $ 19, MATCH ($ J $ 22, Product_Code, 0), 0, COUNTIF (Product_Code, J22))
- Klikk på OK
- Nå har vi den primære rullegardinmenyen og barnelisten over produkter
- Velg "ETV-101" -produkt fra J22, og i K22 kan vi bare se navnene som faller under dette "ETV-101" -produktet. Og når vi endrer et produkt (“ETV-103) i J22, viser K22 de tilsvarende verdiene for den koden
Nå får vi se hva som skjer når vi legger til en ny produktkode på listen? Vil disse rullegardinlistene bli oppdatert?
La oss legge til nytt produkt på listen; Følg trinnene nedenfor:-
- Legg til produktkode i listen over Unique_Prod_Code
- Legg også til Product_Code og Product_Name i dataene:-
- Sjekk nå rullegardinlisten -lagt til produktkode og navnet vises
3rd Eksempel:-
Vi har de dynamiske overskriftene direkte fra bordet, og vi vil legge til nye produkter i serien. Tabellen er i samme format som vi brukte for 1st metode.
4th Metode:-
Følg trinnene nedenfor:-
- Velg overskriften A40: E40
- Lag først det dynamiske området for overskrifter, åpne dialogboksen "Definer navn"
- Skriv "Overskrift" i stedet for navnet, og deretter i "refererer til", skriv inn formelen nedenfor:-
- Skriv inn funksjonen nedenfor:-
- = OFFSET ('Dependent Drop Down List'! $ A $ 40 ,,,, COUNTA ('Dependent Drop Down List'! $ 40: $ 40))
- Klikk på OK
- Dynamisk "Heading" -område er klart nå
Og nå skal vi lage det navngitte området for hver overskrift, følg trinnene nedenfor:-
- Velg tabellen fra A40 til E50
- CTRL + SKIFT + F3 hurtigtast
- Vi fjerner merket for 2nd alternativ
- Og før vi klikker på OK, må du sikre at 1st alternativet "Øverste rad" er valgt
- Nå er vi klare med begge områdene
Nå vil vi utarbeide nedtrekkslisten for foreldre
- Velg cellen J42
- Åpne dialogboksen Datavalidering
- Etter å ha valgt “Liste”, trykker vi på F3 i kilden for å få det navngitte området for overskrifter. Vi klikker på "Overskrift", klikker deretter på OK og trykker enter. Vi har foreldrelisten i J42 nå
- For å lage listen over elementdetaljer, velg cellK42
- Åpne dialogboksen Datavalidering ved å trykke ALT+D+L
- Velg Liste, og skriv deretter inn funksjonen under i kategorien Kilde:-
- = OFFSET (INDIRECT (SUBSTITUTE ($ J $ 42, "", "_")) ,,, COUNTA (INDIRECT (SUBSTITUTE ($ J $ 42, "", "_"))))
- Klikk på OK
Velg nå et element i J42, si at vi velger “Item 01” og se på rullegardinlisten K42. Og, som tidligere 3 metoder, har vi også en avhengig liste her.
Så hva er nytt? I det første eksemplet kan du ikke legge til noe produkt på listen, men her kan du legge til et nytt produkt. Så si at vi legger til nytt produkt i denne varen. Vi går til A45, og vi skriver “ETV-501 Prod 05” og kommer deretter tilbake til K42 og her går du. Du kan se, det nye produktet er lagt til.
- Legg til noen få produkter under den nye varen
Når vi velger “Item 06”, går vi til K42 og klikker på rullegardinlisten. Overraskende nok skjer det ingenting når vi klikker på nedtrekkspilen. Det er fordi vi har skapt alt dynamisk og glemte å lage et dynamisk område for bord, og derfor vises ikke produktene i barnelisten.
For å gjøre det må vi bruke forskjellige teknikker. Det er to metoder for å gjøre det. Du kan enten lage tabellen eller bare bruke OFFSET -funksjonen. Og i den neste metoden vil vi bruke OFFSET -funksjonen, og vi vil se trikset for å utvide tabellområdet også.
- Så vi går først til J43 og trykker “ALT + D + L”
- Vi velger "Liste", og deretter i kilden trykker vi på F3 og velger "Overskrift", klikker på OK og trykker deretter enter
- Nå går vi til K43, og etter å ha valgt “Liste”, går vi til “Kilde” og angir funksjonen nedenfor
= OFFSET ($ A $ 40,1, MATCH ($ J $ 43, $ 40: $ 40,0) -1, COUNTA (OFFSET ($ A $ 40,1, MATCH ($ J $ 43, $ 40: $ 40,0) -1,1000) ,1)))
- Klikk på OK
Nå går vi tilbake og velger “Element 06 i J43 -cellen og går tilbake til K43 og klikker på rullegardinpilen. Men denne tidslisten viser produkter som vi har lagt til for nytt element. Og vi velger det første produktet “ETV-506 Prod 01”.
Slik kan du lage den avhengige rullegardinlisten ved hjelp av forskjellige metoder for alle typer dataformater.
Video: Hvordan lage avhengige (brusende) rullegardinliste i Excel ved hjelp av 5 forskjellige teknikker i Microsoft Excel
Klikk på videolinken for rask referanse til bruken av den. Abonner på vår nye kanal og fortsett å lære med oss!
Hvis du likte bloggene våre, del den med vennene dine på Facebook. Og du kan også følge oss på Twitter og Facebook.
Vi vil gjerne høre fra deg, gi oss beskjed om hvordan vi kan forbedre, utfylle eller innovere arbeidet vårt og gjøre det bedre for deg. Skriv til oss på e -post