Hittills i den här datavalideringsserien har vi lärt oss att skapa normal rullgardinsmeny och dynamisk rullgardinsmeny med hjälp av olika tekniker med datavalidering i Excel.
Och idag, i det här kapitlet, kommer vi att visa dig hur du skapar beroende lista i Microsoft Excel med olika metoder.
Beroende rullgardinsmeny kallas också kaskaddatavalidering, och den begränsar valen i en rullgardinslista, beroende på värdet som valts i den andra cellen som innehåller datavalidering. Med andra ord beror det på det värde som valts i den första listrutan som avgör vilka värden som ska visas i den andra listrutan.
Detta är ett mycket vanligt scenario för att arbeta med stora data eller några dynamiska rapporter, där du vill ha 2nd cell visa en lista som är beroende av det listobjekt som valdes i den första listrutan.
Som vi vet att det i excel finns många sätt att utföra en viss uppgift och på samma sätt finns det många sätt att skapa beroende datavalidering i Excel. Och idag kommer vi att demonstrera fem olika tekniker för att skapa beroende datavalideringslista.
Rådata kan ha valfri ordning eller format och varje gång kan du inte ändra data eller format för att få det du letar efter.
Så vi har tagit en datauppsättning men i 3 olika format för att få den beroende listrutan. Och som du kan se är våra data på vänster sida från kolumn A till kolumn E, och vi kommer att ha vår förväntade utmatning på höger sida i kolumn J & K. Kolumn J kommer att ha den primära valideringen lista medan kolumnen K är beroende och visar värdena beroende på värdet som valts i kolumnen J.
1st Exempel:-
2nd Exempel:-
3rd Exempel:-
1st Exempel:-
Vi har produktlista för varje produktkod från kolumn A8 till E13. Och vi vill välja produktkoden i J10, sedan beroende på den valda produktkoden, ett produktnamn i cell K10.
Första metoden:-
Den första metoden är mycket enkel och kort, och det kräver bara 3 steg för att få den beroende rullgardinsmenyn. Det fungerar dock bara framgångsrikt tills du inte gör några ändringar i ditt sortiment. När du ändrar dina data måste du först ändra det namngivna intervallet för att få den uppdaterade validering av kaskaddata.
Följ stegen nedan:-
- Välj hela tabellen från A8 till E13
- Gå sedan till fliken "Formler", klicka sedan på "Skapa från urval" under kategorin "Definierade namn"
- Du kan också använda kortkommandot CTRL + SKIFT + F3
- Dialogrutan Skapa namn från val visas
- Den ber dig bekräfta vilka rader och kolumner som ska användas för att skapa namn för andra rader och kolumner. Vi bekräftar att använda "översta raden" för att skapa namnen och avmarkera 2nd alternativet och sedan klickar vi på OK
Notera: - Mellanslag och andra specialtecken utom understrykning och punkt är inte tillåtna som namn. Som standard kommer det att konverteras till understreck. Så använd understrykningstecken och punkt för att skilja ord. Den första bokstaven kan inte heller vara ett tal; det måste vara en bokstav, en understrykning eller ett snedstreck.
- För att bekräfta att varje intervall har ett namn går vi till "Namnhanteraren" (tryck på CTRL + F3)
- Där kan vi se alla 5 namngivna intervall tillgängliga
- Och vi kan också se att varje intervallnamn har understreck istället för tomt i mitten av strängen
Nu skapar vi rullgardinsmenyn:-
- Välj cell J10 och tryck på ALT ++ D+L för att öppna dialogrutan Datavalidering
- Välj Lista> ange sedan intervallet A8: E8 på fliken Källa
- Klicka på OK
- Nu kommer vi att skapa beroende lista i cell K10
- Öppna dialogrutan Datavalidering genom att trycka på tangenten ALT+D+L
- Välj Lista, i källan anger du den här funktionen:- = INDIRECT (SUBSTITUTE ($ J $ 10, "", "_"))
Vid datavalidering, för att skapa den beroende listan, har vi använt INDIRECT -funktionen för att returnera värdet baserat på den primära datavalideringslistan. Och, för att ersätta understrykning med utrymme, kommer vi att använda SUBSTITUTE -funktionen i INDIRECT -funktionen.
- Klicka på OK
När vi väljer någon produktkod i cell J10 kommer produktlistan med vald produktkod att visas i cell K10. Till exempel: - Vi har valt ETV 501, nu kan du se beroende produktlista visas i cell K10
Notera: - När du lägger till produktnamn och produktkod som inte visas i listan.
Till exempel: - Vi har lagt till produkt 26 under ETV 505 -produktkoden, men när vi väljer ETV 505 -produkten visas inte den tillagda produkten i rullgardinsmenyn.
Så här kan du skapa beroende rullgardinsmeny med hjälp av enkel teknik i bara tre enkla steg.
2nd Exempel:-
I det här exemplet kommer vi att se hur du får en beroende rullgardinslista när du har dina data som visas i den här vertikala tabellen.
Vi kommer att använda två olika metoder för att skapa beroende listruta. Båda är nästan liknande tekniker. Det ena är dock utan det angivna intervallet och det andra kommer att ha det namngivna intervallet.
1st Metod:-
För att göra detsamma använder vi OFFSET, MATCH & COUNTIF -funktioner tillsammans.
Eftersom vi vet att OFFSET -funktionen används för att skapa det dynamiska intervallet, därför använder vi OFFSET -funktionen för att skapa det dynamiska intervallet för att skapa "Dynamisk datavalidering".
MATCH används för att returnera den relativa positionen för ett objekt i en lista i Excel. Och här hjälper det oss att matcha kategorin som valts i den primära listrutan i vårt sortiment på arket, och det kommer att returnera ett nummer.
Och COUNTIF används för att få antalet celler som matchar kriterier. Och här kommer vi att använda detta för att räkna antalet rader som ska visas med hjälp av funktionen RÄNTA.
Följ stegen nedan:-
- Välj cellen J21, där vi skapar vår primära datavalideringslista
- Tryck på tangenten ALT+D+L för att öppna dialogrutan Datavalidering
- Välj lista från tillåt kategori
- Klicka på fliken Källa och välj intervallet från B20: B24
- Och klicka på OK
- Gå till cell K21 och öppna dialogrutan för datavalidering igen
- Sedan väljer vi Lista och, i källa, anger vi nedanstående funktion:
- = OFFSET ($ E $ 19, MATCH ($ J $ 21, $ D $ 20: $ D $ 32,0), 0, COUNTIF ($ D $ 20: $ D $ 32, $ J $ 21))
- Klicka på OK
- I cell K21 kan vi se alla motsvarande värden för vald produktkod:-
Så här kan du få den beroende listan genom att ta cellreferenser i funktionen.
2nd Metod:-
I nästa metod kommer vi att använda namngivet intervall i samma funktion för att få validering av kaskaddata. Först måste vi skapa den dynamiska listan för produktkod. Om någon ny produkt läggs till i data bör rullgardinsmenyn uppdateras för att visa samma.
För att göra detsamma, följ stegen nedan:-
- Välj B19, tryck sedan på CTRL + F3 för att öppna fönstret "Namnhanterare"
- Nu klickar vi på dialogrutan "Nytt" och "Definiera namn" visas
- Vi kan se, namnet visas redan i namnrutan -det beror på att vi har valt B9 innan vi öppnade fönstret "Namnhanterare". Och eftersom B19 har text i den kan vi ändra den till något annat namn om vi vill.
- Ange nedan nämnda formel:-
= OFFSET ('DependentDropDownList'! $ B $ 20,0,0, COUNTA ('DependentDropDownList'! $ B $ 20: $ B $ 32))
- Klicka på OK
Eftersom vi har skapat en dynamisk lista för unika produkter kommer vi nu att skapa ett dynamiskt intervall för produktkodintervall som finns i kolumn D.
Följ samma steg som vi har följt för unik produkt:-
- Välj cell D19, öppna dialogrutan Definiera namn
- Du kommer att hitta namnet finns redan där
- Ange följande formel i referenser:-
= OFFSET ('Dependent Drop Down List'! $ D $ 20,0,0, COUNTA ('Dependent Drop Down List'! $ D $ 20: $ D $ 35))
- Klicka på OK
- Nu är båda dynamiska områdena klara. Så vi går till J22 och trycker på "ALT + D + L" och väljer "List"
- I källan kommer vi att ha det namngivna intervallet som vi definierade det för "Unik produktkod", så vi trycker på F3 för att se alla tillgängliga namngivna intervall
- Vi kan se "Unik produktkod" med namnet sortiment, så vi klickar på det och klickar sedan på OK och vi trycker på enter
- I det ögonblick som vi trycker på enter får vi nedrullningspilen i cell J22, som innehåller listan över unika produktkoder
- Markera cell K22 och öppna dialogrutan "Datavalidering"
- Vi kommer att använda samma funktion som vi har använt i den senaste metoden men med namngivet intervall
- Välj lista och ange sedan formeln i källan:-
= OFFSET ($ E $ 19, MATCH ($ J $ 22, Product_Code, 0), 0, COUNTIF (Product_Code, J22))
- Klicka på OK
- Nu har vi den primära rullgardinsmenyn och barnlistan över produkter
- Välj "ETV-101" -produkt från J22, och i K22 kan vi bara se namnen som faller under denna "ETV-101" -produkt. Och när vi ändrar någon produkt (“ETV-103) i J22, visar K22 motsvarande värden för den koden
Nu får vi se vad som händer när vi lägger till någon ny produktkod i listan? Kommer dessa listrutor att uppdateras?
Låt oss lägga till en ny produkt i listan; Följ stegen nedan:-
- Lägg till produktkod i listan över Unique_Prod_Code
- Lägg också till Product_Code och Product_Name i data:-
- Kontrollera nu rullgardinsmenyn -den tillagda produktkoden och namnet visas
3rd Exempel:-
Vi har de dynamiska rubrikerna direkt från bordet, och vi kommer att lägga till nya produkter i sortimentet. Tabellen är i samma format som vi använde för 1st metod.
4th Metod:-
Följ stegen nedan:-
- Välj rubrik A40: E40
- Skapa först det dynamiska området för rubriker, öppna dialogrutan "Definiera namn"
- Skriv "Rubrik" i namnnamnet och ange sedan nedanstående formel i "refererar till":-
- Ange nedanstående funktion:-
- = OFFSET ('Beroende nedrullningslista'! $ A $ 40 ,,,, COUNTA ('Beroende nedrullningslista'! $ 40: $ 40))
- Klicka på OK
- Dynamiskt "Heading" -område är klart nu
Och nu skapar vi det namngivna intervallet för varje rubrik, följ stegen nedan:-
- Välj tabellen från A40 till E50
- CTRL + SKIFT + F3 kortkommando
- Vi avmarkerar 2nd alternativ
- Och innan vi klickar på OK, se till att 1st alternativet "Övre raden" är valt
- Nu är vi redo med båda serierna
Nu kommer vi att förbereda den överordnade listrutan
- Välj cell J42
- Öppna dialogrutan Datavalidering
- Efter att ha valt “Lista” trycker vi på F3 i källan för att få det namngivna intervallet för rubriker. Vi klickar på "Rubrik" och klickar sedan på OK och trycker på enter. Vi har föräldralistan i J42 nu
- Om du vill skapa listan med objektdetaljer väljer du cellK42
- Öppna dialogrutan Datavalidering genom att trycka på tangenten ALT+D+L
- Välj Lista och ange nedanstående funktion på fliken Källa:-
- = OFFSET (INDIRECT (SUBSTITUTE ($ J $ 42, "", "_")) ,,, COUNTA (INDIRECT (SUBSTITUTE ($ J $ 42, "", "_"))))
- Klicka på OK
Välj nu något objekt i J42, säg att vi väljer "Artikel 01" och titta på K42 -rullgardinsmenyn. Och som tidigare 3 metoder har vi också en beroende lista här.
Så vad är nytt? I det första exemplet kunde du inte lägga till någon produkt i listan, men här kan du lägga till någon ny produkt. Så, säg att vi lägger till ny produkt till den här artikeln. Vi går till A45, och vi skriver ”ETV-501 Prod 05” och kommer sedan tillbaka till K42. Du kan se, den nya produkten har lagts till.
- Lägg nu till några produkter under den nya artikeln
När vi väljer “Artikel 06” går vi till K42 och klickar på rullgardinsmenyn. Överraskande händer ingenting när vi klickar på rullgardinspilen. Det beror på att vi har skapat allt dynamiskt och glömt att skapa ett dynamiskt intervall för bord, varför produkterna inte visas i barnlistan.
För att göra det måste vi använda olika tekniker. Det finns två metoder för att göra det. Du kan antingen skapa tabellen eller bara använda OFFSET -funktionen. Och i nästa metod kommer vi att använda OFFSET -funktionen, och vi kommer att se tricket för att utöka tabellintervallet också.
- Så vi går först till J43 och trycker på “ALT + D + L”
- Vi väljer "List" och sedan i källan trycker vi på F3 och väljer "Rubrik" och klickar på OK och trycker sedan på enter
- Nu går vi till K43, och efter att ha valt "List" går vi till "Source" och anger nedanstå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)))
- Klicka på OK
Nu går vi tillbaka och väljer “Artikel 06 i J43 -cellen och återgår till K43 och klickar på rullgardinspilen. Men den här tidslistan visar produkter som vi har lagt till för nytt objekt. Och vi väljer den första produkten “ETV-506 Prod 01”.
Så här kan du skapa den beroende listrutan med olika metoder för alla typer av dataformat.
Video: Hur man skapar beroende (Kaskad) rullgardinsmeny i Excel med 5 olika tekniker i Microsoft Excel
Klicka på videolänken för snabb referens till användningen av den. Prenumerera på vår nya kanal och fortsätt lära dig med oss!
Om du gillade våra bloggar, dela den med dina vänner på Facebook. Och du kan också följa oss på Twitter och Facebook.
Vi vill gärna höra från dig, låt oss veta hur vi kan förbättra, komplettera eller förnya vårt arbete och göra det bättre för dig. Skriv till oss på e -postwebbplatsen