✅ Skapa en beroende listruta i Excel (steg-för-steg-handledning)

Innehållsförteckning

Skapa en beroende listruta i Excel

Titta på video - Skapa en beroende listruta i Excel

En rullgardinsmeny i Excel är en användbar funktion när du skapar datainmatningsformulär eller Excel -instrumentpaneler.

Den visar en lista med objekt som en rullgardinsmeny i en cell, och användaren kan göra ett val från rullgardinsmenyn. Detta kan vara användbart när du har en lista med namn, produkter eller regioner som du ofta behöver ange i en uppsättning celler.

Nedan är ett exempel på en rullgardinsmeny i Excel:

I exemplet ovan har jag använt objekten i A2: A6 för att skapa en rullgardinsmeny i C3.

Läsa: Här är en detaljerad guide om hur du skapar en Excel -rullgardinsmeny.

Ibland kan du dock använda mer än en listruta i Excel så att de tillgängliga objekten i en andra listrutan är beroende av valet i den första listrutan.

Dessa kallas beroende rullgardinslistor i Excel.

Nedan följer ett exempel på vad jag menar med en beroende listruta i Excel:

Du kan se att alternativen i Drop Down 2 beror på valet i Drop Down 1. Om jag väljer 'Frukt' i Drop Down 1 visas fruktnamnen, men om jag väljer Grönsaker i Drop Down 1, då jag visas grönsaksnamnen i Drop Down 2.

Detta kallas en villkorlig eller beroende rullgardinsmeny i Excel.

Skapa en beroende listruta i Excel

Här är stegen för att skapa en beroende listruta i Excel:

Välj den cell där du vill ha den första (huvud) listrutan.
Gå till Data -> Datavalidering. Detta öppnar dialogrutan för datavalidering.
I listan för datavalidering, på fliken Inställningar, välj Lista.
I fältet Källa anger du intervallet som innehåller de objekt som ska visas i den första listrutan.
Klicka på OK. Detta skapar Drop Down 1.
Välj hela datamängden (A1: B6 i detta exempel).
Gå till Formler -> Definierade namn -> Skapa från urval (eller så kan du använda tangentbordsgenvägen Ctrl + Skift + F3).
I dialogrutan "Skapa namngivna ur markering" markerar du alternativet Översta raden och avmarkerar alla andra. Genom att göra detta skapas två namnintervall ("Frukt" och "Grönsaker"). Frukt med namnet intervall hänvisar till alla frukter i listan och grönsaker som heter range refererar till alla grönsaker i listan.
Klicka på OK.
Välj den cell där du vill ha listrutan Beroende/villkorad (E3 i detta exempel).
Gå till Data -> Datavalidering.
I dialogrutan Datavalidering, på fliken inställningar, se till att listan är vald.
I källfältet anger du formeln = INDIRECT (D3). Här är D3 den cell som innehåller den viktigaste rullgardinsmenyn.
Klicka på OK.

När du gör valet i rullgardinsmenyn 1 uppdateras alternativen som listas i nedrullningslista 2 automatiskt.

Ladda ner exempelfilen

Hur fungerar detta? - Den villkorade listrutan (i cell E3) hänvisar till = INDIRECT (D3). Det betyder att när du väljer "Frukt" i cell D3 hänvisar rullgardinsmenyn i E3 till det namngivna intervallet "Frukt" (via INDIRECT -funktionen) och listar därför alla objekt i den kategorin.

Viktig notering: Om huvudkategorin är mer än ett ord (till exempel 'Seasonal Fruits' istället för 'Fruits'), måste du använda formeln = INDIRECT (SUBSTITUTE (D3, ”“, ”_”)), istället för enkel INDIRECT -funktion som visas ovan.

Anledningen till detta är att Excel inte tillåter mellanslag i namngivna intervall. Så när du skapar ett namngivet intervall med mer än ett ord infogar Excel automatiskt en understrykning mellan orden. Till exempel, när du skapar ett namngivet intervall med "Seasonal Fruits" kommer det att heta Season_Fruits i backend. Om du använder funktionen SUBSTITUTE inom INDIRECT -funktionen ser du till att mellanslag är omvandlas till understrykningar.

Återställ/rensa innehållet i den beroende listrutan automatiskt

När du har gjort valet och sedan ändrar överordnad rullgardinsmeny, kommer den beroende rullgardinsmenyn inte att ändras och skulle därför vara en felaktig post.

Till exempel, om du väljer "Frukt" som kategori och sedan väljer Apple som objekt och sedan går tillbaka och ändrar kategorin till "Grönsaker", kommer den beroende rullgardinsmenyn att fortsätta att visa Apple som objektet.

Du kan använda VBA för att se till att innehållet i den beroende listrutan återställs när huvudmenyn ändras.

Här är VBA -koden för att rensa innehållet i en beroende listruta:

Private Sub Worksheet_Change (ByVal Target As Range) On Error Resume Next If Target.Column = 4 Then If Target.Validation.Type = 3 Then Application.EnableEvents = False Target.Offset (0, 1) .ClearContents End If End If exitHandler: Application.EnableEvents = True Exit Sub End Sub

Krediten för denna kod går till den här självstudien av Debra om att rensa beroende listrutor i Excel när valet ändras.

Så här får du den här koden att fungera:

Kopiera VBA -koden.
I Excel -arbetsboken där du har den beroende rullgardinsmenyn, gå till fliken Utvecklare och inom gruppen 'Kod' klickar du på Visual Basic (du kan också använda kortkommandot - ALT + F11).
I VB Editor -fönstret, till vänster i projektutforskaren, ser du alla kalkylbladens namn. Dubbelklicka på den som har rullgardinsmenyn.
Klistra in koden i kodfönstret till höger.
Stäng VB -redigeraren.

Nu när du ändrar huvudmenyn kommer VBA -koden att avfyras och den rensar innehållet i den beroende rullgardinsmenyn (som visas nedan).

Om du inte är ett fan av VBA kan du också använda ett enkelt villkorligt formateringstrick som markerar cellen när det finns en felaktig matchning. Detta kan hjälpa dig att visuellt se och korrigera felmatchningen (som visas nedan).

Här är stegen t0 som markerar felaktigheter i de beroende listrutorna:

Välj den cell som har den eller de beroende listrutorna.
Gå till Hem -> Villkorlig formatering -> Ny regel.
I dialogrutan Ny formateringsregel väljer du "Använd en formel för att avgöra vilka celler som ska formateras".
I formelfältet anger du följande formel: = FEL (VLOOKUP (E3, INDEX ($ A $ 2: $ B $ 6,, MATCH (D3, $ A $ 1: $ B $ 1)), 1,0))
Ställ in formatet.
Klicka på OK.

Formeln använder VLOOKUP -funktionen för att kontrollera om objektet i den beroende listrutan är det från huvudkategorin eller inte. Om det inte är det returnerar formeln ett fel. Detta används av funktionen FEL för att returnera SANT som säger villkorlig formatering att markera cellen.

Du kanske också gillar följande Excel -självstudier: