Sådan oprettes afhængig (kaskader) rulleliste i Excel ved hjælp af 5 forskellige teknikker

Hidtil i denne datavalideringsserie har vi lært at oprette normal rulleliste og dynamisk rulleliste ved hjælp af forskellige teknikker med datavalidering i Excel.

Og i dag i dette kapitel viser vi dig, hvordan du opretter afhængig rulleliste i Microsoft Excel ved hjælp af forskellige metoder.

Afhængig rulleliste er også kendt som validering af kaskadedata, og det begrænser valgmulighederne i en rulleliste afhængigt af den værdi, der er valgt i den anden celle, der indeholder datavalidering. Med andre ord afhænger det af den værdi, der er valgt i den første rulleliste, som bestemmer de værdier, der skal vises i den anden rulleliste.

Dette er et meget almindeligt scenario for at arbejde med store data eller nogle dynamiske rapporter, hvor du vil have 2^nd celle viser en liste, der er afhængig af det listeelement, der blev valgt i første rullemenu.

Som vi ved, at der i excel er masser af måder at udføre en bestemt opgave på, og på samme måde er der mange måder at oprette afhængig datavalidering i Excel. Og i dag vil vi demonstrere 5 forskellige teknikker til at oprette afhængig datavalideringsliste.

De rådata kan være i enhver rækkefølge eller format, og hver gang kan du ikke ændre data eller format for at få det, du leder efter.

Så vi har taget et datasæt, men i 3 forskellige formater for at få den afhængige rulleliste. Og som du kan se, er vores data i venstre side, der er fra kolonne A til kolonne E, og vi vil have vores forventede output på højre side i kolonne J & K. Kolonne J får den primære validering liste, hvorimod kolonnen K vil være afhængig og vil vise værdierne afhængigt af den værdi, der er valgt i kolonnen J.

1^st Eksempel:-

2^nd Eksempel:-

3^rd Eksempel:-

1^st Eksempel:-

Vi har produktliste for hver produktkode fra kolonne A8 til E13. Og vi vil vælge produktkoden i J10, derefter afhængigt af den valgte produktkode, et produktnavn i celle K10.

Første metode:-

Den første metode er meget enkel og kort, og det kræver kun 3 trin for at få den afhængige rulleliste. Det fungerer dog kun med succes, indtil du ikke foretager ændringer i dit område. Når du ændrer dine data, skal du først ændre det navngivne område for at få den opdaterede validering af kaskadedata.

Følg trinene nedenfor:-

• Vælg hele tabellen fra A8 til E13

• Gå derefter til fanen "Formler", klik derefter på "Opret fra udvalg" under kategorien "Definerede navne"
• Du kan også bruge tastaturgenvejen CTRL + SKIFT + F3
• Dialogboksen Opret navne fra markeringer vises

• Det beder om at bekræfte, hvilke rækker og kolonner der skal bruges til at oprette navne til andre rækker og kolonner. Vi bekræfter at bruge "øverste række" til at oprette navne og fjerne markeringen i 2^nd indstilling, og derefter klikker vi på OK

Bemærk: - Mellemrum og andre specialtegn undtagen understregning og punktum er ikke tilladt som navne. Som standard vil det blive konverteret til understregning. Brug derfor understregningstegn og punktum til at adskille ord. Også det første bogstav kan ikke være et tal; det skal være et bogstav, en understregning eller en skråstreg.

• For at bekræfte, at hvert område har et navn, går vi til "Name Manager" (tryk på CTRL + F3)
• Der kan vi se alle 5 navngivne intervaller tilgængelige
• Og vi kan også se, at hvert område navn har understregning i stedet for blank i midten af ​​strengen

Nu opretter vi rullelisten:-

• Vælg celle J10, og tryk på ALT ++ D+L for at åbne dialogboksen Datavalidering
• Vælg Liste> indtast derefter området A8: E8 i fanen Kilde

• Klik på OK
• Nu vil vi oprette afhængig liste i celle K10
• Åbn dialogboksen Datavalidering ved at trykke på tasten ALT+D+L
• Vælg Liste, indtast denne funktion i kilden:- = INDIRECT (SUBSTITUTE ($ J $ 10, "", "_"))

I datavalidering, til at oprette den afhængige liste, har vi brugt INDIRECT -funktion til at returnere værdien baseret på den primære datavalideringsliste. Og for at erstatte understregning med plads vil vi bruge SUBSTITUTE -funktionen inde i INDIRECT -funktionen.

• Klik på OK

Når vi vælger en produktkode i celle J10, vises produktlisten med den valgte produktkode i celle K10. For eksempel: - Vi har valgt ETV 501, nu kan du se, at afhængig produktliste vises i celle K10

Bemærk: - Når du tilføjer produktnavn og produktkode, der ikke vises på listen.

For eksempel: - Vi har tilføjet produkt 26 under ETV 505 -produktkoden, men når vi vælger ETV 505 -produkt, vises tilføjet produkt ikke på rullelisten.

Så sådan kan du oprette afhængig rulleliste ved hjælp af simpel teknik i kun 3 enkle trin.

2^nd Eksempel:-

I dette eksempel vil vi se, hvordan du får en afhængig rulleliste, når du har dine data som vist i denne lodrette tabel.

Vi vil bruge to forskellige metoder til at oprette afhængig rulleliste. Begge er næsten ens teknikker. Den ene er dog uden det navngivne område, og den anden vil have det navngivne område.

1^st Metode:-

For at gøre det samme vil vi bruge OFFSET, MATCH & COUNTIF -funktioner sammen.

Da vi ved, at OFFSET -funktionen bruges til at oprette det dynamiske område, derfor til at oprette en liste over "Dynamisk datavalidering", bruger vi OFFSET -funktionen til at returnere det dynamiske område.

MATCH bruges til at returnere den relative position af et element på en liste i Excel. Og her vil det hjælpe os med at matche den kategori, der er valgt i den primære rulleliste i vores område på arket, og det vil returnere et nummer.

Og COUNTIF bruges til at få antallet af celler, der matcher kriterier. Og her vil vi bruge dette til at tælle antallet af rækker, der skal vises ved hjælp af funktionen TÆLLE.

Følg trinene nedenfor:-

• Vælg cellen J21, hvor vi opretter vores primære datavalideringsliste
• Tryk på tasten ALT+D+L for at åbne dialogboksen Datavalidering
• Vælg liste fra tillad kategori
• Klik på fanen Kilde, og vælg området fra B20: B24

• Og klik på OK

• Gå til celle K21, og åbn dialogboksen for datavalidering igen
• Derefter vælger vi Liste og indtaster nedenstående funktion i kilden:
• = OFFSET ($ E $ 19, MATCH ($ J $ 21, $ D $ 20: $ D $ 32,0), 0, COUNTIF ($ D $ 20: $ D $ 32, $ J $ 21))

• Klik på OK
• I celle K21 kan vi se alle de tilsvarende værdier for valgt produktkode:-

Så sådan kan du få den afhængige liste ved at tage cellereferencer i funktionen.

2^nd Metode:-

I den næste metode vil vi bruge navngivet område i den samme funktion til at få validering af kaskadedata. Først skal vi oprette den dynamiske liste for produktkode. Hvis der tilføjes et nyt produkt til dataene, skal rullemenuen opdateres for at vise det samme.

Følg trinene nedenfor for at gøre det samme:-

• Vælg B19, og tryk derefter på CTRL + F3 for at åbne vinduet "Name Manager"
• Nu 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 åbner vinduet "Name Manager". Og da B19 har tekst i den, kan vi ændre den til et andet navn, hvis vi vil.

• Indtast nedenstående formel:-

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

• Klik på OK

Da vi har oprettet en dynamisk liste for unikke produkter, vil vi nu oprette et dynamisk område for produktkodeområde, der er i kolonne D.

Følg de samme trin, som vi har fulgt for unikt produkt:-

• Vælg celle D19, åbn Definer navn dialogboks
• Du vil finde navnet er der allerede
• Indtast nedenstående formel i referencer:-

= OFFSET ('Dependent Drop Down List'! $ D $ 20,0,0, COUNTA ('Dependent Drop Down List'! $ D $ 20: $ D $ 35))

• Klik på OK
• Nu er begge dynamiske områder klar. Så vi går til J22 og trykker på "ALT + D + L" og vælger "Liste"
• I kilden vil vi have det navngivne område, som vi definerede det for "Unik produktkode", så vi trykker på F3 for at se alle de tilgængelige navngivne intervaller
• Vi kan se "Unik produktkode" navngivet sortiment, så vi klikker på det og klikker derefter på OK, og vi trykker på enter

• I det øjeblik vi trykker på enter, får vi rullemenuen i celle J22, som indeholder listen over unikke produktkoder

• Vælg celle K22, og åbn dialogboksen "Datavalidering"
• Vi vil bruge den samme funktion, som vi har brugt i den sidste metode, men med navngivet område
• Vælg liste, og indtast derefter formlen nedenfor i kilden:-

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

• Klik på OK
• Nu har vi den primære rulleliste samt børneliste over produkter
• Vælg "ETV-101" produkt fra J22, og i K22 kan vi kun se de navne, der falder ind under dette "ETV-101" produkt. Og når vi ændrer ethvert produkt (“ETV-103) i J22, viser K22 de tilsvarende værdier for den kode

Nu vil vi se, hvad der sker, når vi tilføjer en ny produktkode til listen? Vil disse rullelister blive opdateret?

Lad os tilføje nyt produkt på listen; Følg nedenstående trin:-

• Tilføj produktkode på listen over Unique_Prod_Code

• Tilføj også Product_Code og Product_Name i dataene:-

• Kontroller nu rullelisten -tilføjet produktkode, og navnet vises

3^rd Eksempel:-

Vi har de dynamiske overskrifter direkte fra bordet, og vi tilføjer nye produkter til sortimentet. Tabellen er i det samme format, som vi brugte til 1^st metode.

4^th Metode:-

Følg trinene nedenfor:-

• Vælg overskriften A40: E40
• Opret først det dynamiske område for overskrifter, åbn dialogboksen "Definer navn"
• Skriv "Overskrift" i navnet, og derefter i "henviser til", indtast nedenstående formel:-
• Indtast nedenstående funktion:-
• = OFFSET ('Dependent Drop Down List'! $ A $ 40 ,,,, COUNTA ('Dependent Drop Down List'! $ 40: $ 40))
• Klik på OK

• Dynamisk "Heading" -område er klar nu

Og nu vil vi oprette det navngivne område for hver overskrift, følg trinene nedenfor:-

• Vælg tabellen fra A40 til E50
• CTRL + SKIFT + F3 tastaturgenvej
• Vi fjerner markeringen af ​​2^nd mulighed
• Og før vi klikker på OK, skal du sikre 1^st "øverste række" er valgt

• Nu er vi klar med begge områder

Nu udarbejder vi forældrenes rulleliste

• Vælg cellen J42
• Åbn dialogboksen Datavalidering

• Efter at have valgt "Liste", trykker vi på F3 i kilden for at få det navngivne område til overskrifter. Vi klikker på "Overskrift", klikker derefter på OK og trykker på enter. Vi har forældrelisten i J42 nu

• For at oprette listen over varedetaljer skal du vælge cellK42
• Åbn dialogboksen Datavalidering ved at trykke på tasten ALT+D+L
• Vælg Liste, og indtast derefter funktionen under fanen Kilde:-
• = OFFSET (INDIRECT (SUBSTITUTE ($ J $ 42, "", "_")) ,,, COUNTA (INDIRECT (SUBSTITUTE ($ J $ 42, "", "_"))))

• Klik på OK

Vælg nu et element i J42, sig, at vi vælger "Item 01", og kig på rullelisten K42. Og ligesom tidligere 3 metoder fik vi også en afhængig liste her.

Nå, noget nyt? I det første eksempel kunne du ikke tilføje noget produkt til listen, men her kan du tilføje ethvert nyt produkt. Så sig, at vi tilføjer nyt produkt til denne vare. Vi går til A45, og vi skriver “ETV-501 Prod 05” og kommer derefter tilbage til K42, og her er du. Du kan se, det nye produkt er tilføjet.

• Tilføj nu få produkter under den nye vare

Når vi vælger “Item 06”, går vi til K42 og klikker på rullelisten. Overraskende nok sker der ikke noget, når vi klikker på rullemenuen. Det er fordi vi har skabt alt dynamisk og glemte at oprette et dynamisk område til bord, hvorfor produkterne ikke vises på barnelisten.

For at gøre det skal vi bruge forskellige teknikker. Der er to metoder til at gøre det. Du kan enten oprette tabellen eller blot bruge OFFSET -funktionen. Og i den næste metode vil vi bruge OFFSET -funktionen, og vi vil også se tricket til at udvide tabellen.

• Så vi går først til J43 og trykker på “ALT + D + L”
• Vi vælger "Liste", og derefter i kilden trykker vi på F3 og vælger "Overskrift", klikker på OK og derefter trykker på enter

• Nu går vi til K43, og efter at have valgt "Liste", går vi til "Kilde" og indtaster nedenstående funktion

= 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)))

• Klik på OK

Nu går vi tilbage og vælger “Item 06 i J43 -cellen og vender tilbage til K43 og klikker på pil ned. Men denne tidsliste viser produkter, som vi har tilføjet til ny vare. Og vi vælger det første produkt “ETV-506 Prod 01”.

Sådan kan du oprette den afhængige rulleliste ved hjælp af forskellige metoder til enhver form for dataformat.

Video: Sådan opretter du afhængig (kaskade) rulleliste i Excel ved hjælp af 5 forskellige teknikker i Microsoft Excel

Klik på videolinket for hurtig reference til brugen af ​​det. Abonner på vores nye kanal, og bliv ved med at lære med os!

Hvis du kunne lide vores blogs, kan du dele den med dine venner på Facebook. Og også du kan følge os på Twitter og Facebook.
Vi vil meget gerne høre fra dig, lad os vide, hvordan vi kan forbedre, supplere eller innovere vores arbejde og gøre det bedre for dig. Skriv til os på e -mail -stedet