Gör en dynamiskt beroende listruta i Excel (steg för steg)
Här i den här handledningen kommer vi steg för steg att introducera hur man skapar en beroende rullgardinslista som visar val beroende på de värden som valts i den första rullgardinsmenyn. Med andra ord kommer vi att göra en Excel-datavalideringslista baserad på värdet av en annan lista.
Gör en dynamiskt beroende rullgardinslista
10s för att göra en beroende rullgardinslista med ett praktiskt verktyg
Gör en dynamiskt beroende rullgardinsmeny i Excel 2021, Excel 365 och nyare versioner
Några frågor du kan ställa om denna handledning
Video: Gör en Excel-beroende rullgardinslista
Gör en dynamiskt beroende rullgardinslista
Steg 1: Skriv posterna för listorna
1. Skriv först in de poster du vill ska visas i rullgardinslistorna, varje lista i en separat kolumn.
Lägga märke till att objekten i den första kolumnen (Produkt) kommer att fungera som Excel-namn för de beroende listorna senare. Till exempel kommer Frukt och Grönt här att vara namnen för kolumn B2:B5 och C2:C6 separat.
Se skärmdump:
2. Skapa sedan tabeller för varje datalista.
Välj kolumnintervall A1:A3, klicka Insert > Bord, markera sedan i dialogrutan Skapa tabell Mitt bord har rubriker kryssruta. Klick OK.
Upprepa sedan detta steg för att skapa tabeller för de andra två listorna.
Du kan se alla tabeller och referensen till intervall i Namnhanteraren (tryck ctrl + F3 för att öppna den).
Steg 2: Skapa intervallnamn
I det här steget måste du skapa namn för huvudlistan och varje beroendelista.
1. Välj objekten som visas i huvudlistan (A2: A3).
2. Gå sedan till Namnsruta som bredvid Formula bar.
3. Skriv in namnet i den, här namnger den som Produkt.
4. Tryck ange nyckel för att slutföra.
Upprepa sedan stegen ovan för att skapa namn separat för varje beroende lista.
Här namnges den andra kolumnen (B2:B5) som Frukt och den tredje kolumnen (C2:C6) som Grönsak.
Du kan se alla intervallnamn i Namnhanteraren (tryck på ctrl + F3 för att öppna den).
Steg 3: Lägg till huvudrullgardinslistan
Lägg sedan till huvudrullgardinsmenyn (Produkt), som är en normal rullgardinsmeny för datavalidering, inte en beroende rullgardinslista.
1. Skapa först en tabell.
Välj en cell (E1), och skriv den första kolumnrubriken (Produkt), och flytta till nästa kolumncell (F1), skriv den andra kolumnrubriken (Artikel). Den här tabellen kommer att hålla rullgardinslistorna.
Välj sedan dessa två rubriker (E1 och F1), klick Insert fliken och välj Bord i tabellen Tabeller.
I dialogrutan Skapa tabell markerar du Mitt bord har rubriker rutan och klicka på OK.
2. Välj cell E2 som du vill infoga huvudrullgardinslistan till klickar du på Data fliken och gå till Dataverktyg grupp att klicka på Datagransknings > Datagransknings.
3. I dialogrutan Datavalidering,
- Välja Lista i Tillåt sektion,
- Skriv nedan formel i Källa bar, produkt är namnet på huvudlistan,
- Klicka OK.
=Product
Du kan se att huvudrullgardinslistan har skapats.
Steg 4: Lägg till rullgardinslista för beroende
1. Välj cell F2 som du vill lägga till den beroende rullgardinsmenyn till klickar du på Data fliken och gå till gruppen Dataverktyg för att klicka Datagransknings > Datagransknings.
2. I dialogrutan Datavalidering,
- Välja Lista i Tillåt sektion,
- Skriv in formeln nedan i Källa bar, E2 är cellen som innehåller den huvudsakliga rullgardinsmenyn.
- Klicka OK.
=INDIRECT(SUBSTITUTE(E2," ","_"))
Om E2 är tom (du väljer inte något objekt i huvudrullgardinsmenyn), kommer du att se ett meddelande som visas nedan, klicka på Ja att fortsätta.
Nu har den beroende rullgardinsmenyn skapats.
Steg 5: Testa den beroende rullgardinsmenyn.
1. välj Frukt i huvudrullgardinsmenyn (E2), gå sedan till den beroende rullgardinsmenyn (F2) för att klicka på pilikonen, se om fruktartiklarna finns i listan och välj sedan ett objekt från den beroende rullgardinsmenyn.
2. Tryck Fliken för att starta en ny rad i datainmatningstabellen, välj Grönsaker, och flytta till nästa cell till höger, se om grönsaksartiklarna finns i listan, välj sedan ett objekt från den beroende rullgardinsmenyn.
- Om det inte finns någon artikel vald i huvudrullgardinslistan (Produktkolumnen), kommer den beroende rullgardinsmenyn (Artikelkolumnen) inte att fungera.
- Om du vill återställa eller rensa innehållet i den beroende rullgardinsmenyn efter att ha valt ändrat, gå till den här artikeln Hur rensar jag beroende rullgardinsmeny efter att ha valt ändrad i Excel?, introducerar den en VBA-kod för att hjälpa dig.
- Om du vill skapa en rullgardinslista på tre nivåer, den här artikeln Hur skapar man en flernivåberoende rullgardinslista i Excel? kommer att hjälpa dig.
10s för att göra en beroende rullgardinslista med ett praktiskt verktyg
Kutools för Excel ger ett kraftfullt verktyg för att göra en beroende rullgardinslista enklare och snabbare:
Steg 1: Skriv posterna för rullgardinsmenyn
Först, ordna dina data enligt nedanstående skärmdump:
Steg 2: Använda Kutools verktyg
1. Välj de data du har skapat, klicka Kutools fliken och klicka på Listruta för att visa undermenyn, klicka på Dynamisk rullgardinslista.
2. I listrutan Beroende
- Kontrollera Läge B som matchar ditt dataläge,
- Välj utgångsområde, utdataintervallkolumnen måste vara lika med dataintervallkolumnen,
- Klicka Ok.
Nu har den beroende rullgardinsmenyn skapats.
- Läge B stöder att skapa en tredje nivå eller mer i en rullgardinslista:
- Om dina data är ordnade som skärmbilden nedan visar, måste du använda läge A, som endast stöder att skapa en rullgardinsmeny med två nivåer.
- För mer information om hur man använder Kutools för att skapa en beroende rullgardinslista, vänligen besök denna handledning.
Gör en dynamiskt beroende rullgardinsmeny i Excel 2021, Excel 365 och nyare versioner
Om du använder Excel 365, Excel 2021 eller nyare versioner finns det ett annat sätt att snabbt skapa en dynamiskt beroende rullgardinslista genom att använda nya funktioner UNIK och FILTER.
Anta att dina källdata är ordnade som visas på skärmdumpen, följ stegen nedan för att skapa den dynamiska rullgardinsmenyn.
Steg 1: Använd formeln för att hämta objekt för huvudrullgardinsmenyn
Välj en cell, till exempel cell G3, och använd funktionerna UNIQUE och FILTER för att extrahera de unika värdena från Produkt lista som kommer att vara källan till huvudrullgardinslistan, och tryck ange nyckel.
=UNIQUE(FILTER(A3:A20, A3:A20<>""))
Steg 2: Skapa huvudrullgardinsmenyn
1. Välj en cell som du vill placera i huvudrullgardinsmenyn, till exempel cell D3, klicka på Data fliken och gå till Dataverktyg grupp att klicka på Datagransknings > Datagransknings.
2. I dialogrutan Datavalidering,
- Välja Lista i Tillåt sektion,
- Skriv nedan formel i Källa bar,
- Klicka OK.
=$G$3#
Nu skapas den huvudsakliga rullgardinsmenyn.
Steg 3: Använd formel för att hämta objekt för den beroende rullgardinsmenyn
Välj en cell, till exempel cell H3, med FILTER-funktionen för att filtrera objekten baserat på värdet i cellen D3 (det valda objektet i huvudrullgardinslistan), tryck på ange nyckel.
=FILTER(B3:B20, A3:A20=D3)
Steg 4: Skapa den beroende rullgardinsmenyn
1. Välj en cell som kommer att placera den beroende rullgardinsmenyn, till exempel en cell E3, klicka på Data fliken och gå till Dataverktyg grupp att klicka på Datagransknings > Datagransknings.
2. I dialogrutan Datavalidering,
- Välja Lista i Tillåt sektion,
- Skriv nedan formel i Källa bar,
- Klicka OK.
=$H$3#
Nu skapas den beroende rullgardinsmenyn framgångsrikt.
När du lägger till nya objekt eller gör några ändringar i A3:A20 kommer rullgardinslistorna att uppdateras automatiskt.
Sortera rullgardinslistan i alfabetisk ordning
Om du vill ordna objekten i rullgardinsmenyn alfabetiskt kan du använda nedanstående formel till förberedelsetabellen.För huvudrullgardinsmenyn (formeln i cell G3):
=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>"")))För den beroende rullgardinsmenyn (formeln i cell H3):
=SORT(FILTER(B3:B20, A3:A20=D3))Nu sorteras båda rullgardinslistorna alfabetiskt från A till Ö.
För att sorteras alfabetiskt från Z till A, använd nedanstående formel:
För huvudrullgardinsmenyn (formeln i cell G3):
=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>"")), 1, -1)För den beroende rullgardinsmenyn (formeln i cell H3):
=SORT(FILTER(B3:B20, A3:A20=D3), 1, -1)Några frågor du kan ställa:
1. Varför infoga en tabell för varje datalista?
Att infoga en tabell för datalistan hjälper dig att automatiskt uppdatera rullgardinsmenyn baserat på ändringarna i datalistan. Om du till exempel lägger till "Andra" i den första datalistan, läggs sedan huvudrullgardinsmenyn till automatiskt med "Andra".
2. Varför använda en tabell för att placera rullgardinslistor?
När du trycker på Tab-tangenten för att lägga till en ny rad i tabellen kommer listorna automatiskt att läggas till på den nya raden.
3. Hur fungerar funktionen INDIREKT?
INDIREKT funktion används för att konvertera en textsträng till en giltig referens.
4. Hur fungerar formeln INDIRECT(SUBSTITUTE(E2&F2," ",""))?
För det första, ERSÄTTNING funktion ersätter text med en annan text. Här brukade den ta bort mellanslagen från de kombinerade namnen (E2 och F2). Sedan INDIREKT funktion konverterar textsträngen (det kombinerade innehållet av E2 och F2) till en giltig referens.
Bästa kontorsproduktivitetsverktyg
Uppgradera dina Excel-färdigheter med Kutools för Excel och upplev effektivitet som aldrig förr. Kutools för Excel erbjuder över 300 avancerade funktioner för att öka produktiviteten och spara tid. Klicka här för att få den funktion du behöver mest...
Fliken Office ger ett flikgränssnitt till Office och gör ditt arbete mycket enklare
- Aktivera flikredigering och läsning i Word, Excel, PowerPoint, Publisher, Access, Visio och Project.
- Öppna och skapa flera dokument i nya flikar i samma fönster, snarare än i nya fönster.
- Ökar din produktivitet med 50 % och minskar hundratals musklick för dig varje dag!