Se video - Oprettelse af en afhængig rulleliste i Excel
En Excel -rulleliste er en nyttig funktion, når du opretter dataindtastningsformularer eller Excel Dashboards.
Det viser en liste over emner som en rullemenu i en celle, og brugeren kan foretage et valg fra rullemenuen. Dette kan være nyttigt, når du har en liste med navne, produkter eller regioner, som du ofte skal indtaste i et sæt celler.
Nedenfor er et eksempel på en Excel -rulleliste:
I eksemplet ovenfor har jeg brugt emnerne i A2: A6 til at oprette en rullemenu i C3.
Læs: Her er en detaljeret guide til, hvordan du opretter en Excel -rulleliste.
Nogle gange kan det dog være en god idé at bruge mere end én rulleliste i Excel, så de tilgængelige elementer på en anden rulleliste afhænger af det valg, der foretages i den første rulleliste.
Disse kaldes afhængige rullelister i Excel.
Nedenfor er et eksempel på, hvad jeg mener med en afhængig rulleliste i Excel:
Du kan se, at mulighederne i Drop Down 2 afhænger af det valg, der er foretaget i Drop Down 1. Hvis jeg vælger 'Frugt' i Drop Down 1, får jeg vist frugtnavnene, men hvis jeg vælger Grøntsager i Drop Down 1, så jeg Jeg har vist grøntsagsnavnene i Drop Down 2.
Dette kaldes en betinget eller afhængig rulleliste i Excel.
Oprettelse af en afhængig rulleliste i Excel
Her er trinene til at oprette en afhængig rulleliste i Excel:
- Vælg den celle, hvor du vil have den første (hoved) rulleliste.
- Gå til Data -> Datavalidering. Dette åbner dialogboksen for datavalidering.
- Vælg Liste i fanen Indstillinger i dialogboksen datavalidering.
- I feltet Kilde skal du angive det område, der indeholder de elementer, der skal vises på den første rulleliste.
- Klik på OK. Dette vil skabe Drop Down 1.
- Vælg hele datasættet (A1: B6 i dette eksempel).
- Gå til formler -> Definerede navne -> Opret fra markering (eller du kan bruge tastaturgenvejen Control + Shift + F3).
- I dialogboksen 'Opret navngivet fra markering' skal du markere indstillingen Øverste række og fjerne markeringen fra alle de andre. Hvis du gør dette, opretter du to navneområder ('Frugt' og 'Grøntsager'). Frugt navngivet område refererer til alle frugterne på listen og Grøntsager navngivet område refererer til alle grøntsagerne på listen.
- Klik på OK.
- Vælg den celle, hvor du vil have rullelisten afhængig/betinget (E3 i dette eksempel).
- Gå til Data -> Datavalidering.
- I dialogboksen Datavalidering, under fanen Indstillinger, skal du sørge for, at Liste er valgt.
- Indtast formlen = INDIRECT (D3) i feltet Kilde. Her er D3 den celle, der indeholder den vigtigste rullemenu.
- Klik på OK.
Når du nu foretager valget i Drop Down 1, opdateres indstillingerne i Drop Down List 2 automatisk.
Download eksempelfilen
Hvordan virker det? - Den betingede rulleliste (i celle E3) refererer til = INDIRECT (D3). Det betyder, at når du vælger 'Frugt' i celle D3, henviser rullelisten i E3 til det navngivne område 'Frugt' (via INDIRECT -funktionen) og viser derfor alle emnerne i den kategori.
Vigtig note: Hvis hovedkategorien er mere end ét ord (f.eks. 'Seasonal Fruits' i stedet for 'Fruit'), skal du bruge formlen = INDIRECT (SUBSTITUTE (D3, ”“, ”_”)), i stedet for enkel INDIRECT -funktion vist ovenfor.
- Årsagen til dette er, at Excel ikke tillader mellemrum i navngivne områder. Så når du opretter et navngivet område med mere end ét ord, indsætter Excel automatisk en understregning mellem ordene. Når du f.eks. Opretter et navngivet område med 'Seasonal Fruits', får det navnet Season_Fruits i backend. Brug af SUBSTITUTE -funktionen i INDIRECT -funktionen sørger for, at mellemrum er konverteret til understregninger.
Nulstil/ryd automatisk indholdet af afhængig rulleliste
Når du har foretaget valget, og derefter ændrer rullelisten forælder, ændres den afhængige rulleliste ikke og vil derfor være en forkert indtastning.
For eksempel, hvis du vælger 'Frugt' som kategori og derefter vælger Apple som elementet og derefter går tilbage og ændrer kategorien til 'Grøntsager', vil den afhængige rulleliste fortsat vise Apple som elementet.
Du kan bruge VBA til at sikre, at indholdet på den afhængige rulleliste nulstilles, når hovedrullelisten ændres.
Her er VBA -koden for at rydde indholdet af en afhængig rulleliste:
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
Kreditten for denne kode går til denne tutorial fra Debra om at slette afhængige rullelister i Excel, når valget ændres.
Sådan får du denne kode til at fungere:
- Kopier VBA -koden.
- I Excel -projektmappen, hvor du har den afhængige rulleliste, skal du gå til fanen Udvikler, og inden for gruppen 'Kode' skal du klikke på Visual Basic (du kan også bruge tastaturgenvejen - ALT + F11).
- I VB Editor -vinduet til venstre i projektudforskeren vil du se alle regnearksnavne. Dobbeltklik på den, der har rullelisten.
- Indsæt koden i kodevinduet til højre.
- Luk VB Editor.
Nu, når du ændrer hovedrullelisten, ville VBA -koden blive affyret, og den ville rydde indholdet af den afhængige rulleliste (som vist nedenfor).
Hvis du ikke er fan af VBA, kan du også bruge et simpelt betinget formateringstrick, der fremhæver cellen, når der er et mismatch. Dette kan hjælpe dig med visuelt at se og rette uoverensstemmelsen (som vist nedenfor).
Her er trin t0 fremhæve uoverensstemmelser i de afhængige rullelister:
- Vælg den celle, der har den eller de afhængige rullelister.
- Gå til Hjem -> Betinget formatering -> Ny regel.
- I dialogboksen Ny formateringsregel skal du vælge 'Brug en formel til at bestemme, hvilke celler der skal formateres'.
- I formelfeltet skal du indtaste følgende formel: = FEJL (VLOOKUP (E3, INDEX ($ A $ 2: $ B $ 6,, MATCH (D3, $ A $ 1: $ B $ 1)), 1,0))
- Indstil formatet.
- Klik på OK.
Formlen bruger VLOOKUP -funktionen til at kontrollere, om elementet i den afhængige rulleliste er det fra hovedkategorien eller ej. Hvis det ikke er tilfældet, returnerer formlen en fejl. Dette bruges af funktionen FEJL til at returnere SAND, som fortæller betinget formatering at markere cellen.
Du kan også lide følgende Excel -selvstudier:
- Udtræk data baseret på en rullelistevalg.
- Oprettelse af en rulleliste med søgeforslag.
- Vælg flere elementer fra en rulleliste.
- Opret flere rullelister uden gentagelse.
- Spar tid med dataindtastningsskemaer i Excel.
