Opprette en avhengig rullegardinliste i Excel (trinn-for-trinn-opplæring)

Se video - Opprette en avhengig rullegardinliste i Excel

En rullegardinliste i Excel er en nyttig funksjon når du oppretter dataregistreringsskjemaer eller Excel Dashboards.

Den viser en liste over elementer som en rullegardinmeny i en celle, og brukeren kan velge mellom rullegardinmenyen. Dette kan være nyttig når du har en liste over navn, produkter eller regioner som du ofte trenger å skrive inn i et sett med celler.

Nedenfor er et eksempel på en rullegardinliste i Excel:

I eksemplet ovenfor har jeg brukt elementene i A2: A6 til å lage en rullegardinmeny i C3.

Lese: Her er en detaljert guide for hvordan du oppretter en Excel -rullegardinliste.

Noen ganger kan det imidlertid være lurt å bruke mer enn én rullegardinliste i Excel, slik at elementene som er tilgjengelige i den andre rullegardinlisten, er avhengig av valget i den første rullegardinlisten.

Disse kalles avhengige rullegardinlister i Excel.

Nedenfor er et eksempel på hva jeg mener med en avhengig rullegardinliste i Excel:

Du kan se at alternativene i rullegardinmenyen 2 avhenger av valget som er gjort i nedtrekkslisten 1. Hvis jeg velger 'Frukt' i nedtrekkslisten 1, får jeg vist fruktnavnene, men hvis jeg velger Grønnsaker i nedtrekksmenyen 1, så jeg Jeg får vist grønnsaksnavnene i Drop Down 2.

Dette kalles en betinget eller avhengig rullegardinliste i Excel.

Opprette en avhengig rullegardinliste i Excel

Her er trinnene for å lage en avhengig rullegardinliste i Excel:

• Velg cellen der du vil ha den første (hoved) rullegardinlisten.
• Gå til Data -> Datavalidering. Dette åpner dialogboksen for datavalidering.
• Velg Liste i dialogboksen for datavalidering.
• I feltet Kilde angir du området som inneholder elementene som skal vises i den første rullegardinlisten.
• Klikk OK. Dette vil opprette Drop Down 1.
• Velg hele datasettet (A1: B6 i dette eksemplet).
• Gå til Formler -> Definerte navn -> Opprett fra utvalg (eller du kan bruke hurtigtasten Control + Shift + F3).
• I dialogboksen "Opprett navngitt fra utvalg", merker du av alternativet Øverste rad og fjerner merket for alle de andre. Hvis du gjør dette, opprettes det to navneområder ("frukt" og "grønnsaker"). Frukt navngitt område refererer til alle fruktene i listen og Grønnsaker med navn refererer til alle grønnsakene i listen.
• Klikk OK.
• Velg cellen der du vil ha rullegardinlisten Avhengig/betinget (E3 i dette eksemplet).
• Gå til Data -> Datavalidering.
• I dialogboksen Datavalidering, i kategorien Innstillinger, må du kontrollere at Liste er valgt.
• Skriv inn formelen = INDIRECT (D3) i feltet Kilde. Her er D3 cellen som inneholder hovedvinduet.
• Klikk OK.

Når du gjør valget i rullegardinmenyen 1, oppdateres alternativene i rullegardinlisten 2 automatisk.

Last ned eksempelfilen

Hvordan virker dette? - Den betingede rullegardinlisten (i celle E3) refererer til = INDIRECT (D3). Dette betyr at når du velger ‘Frukt’ i celle D3, refererer rullegardinlisten i E3 til det navngitte området ‘Frukt’ (gjennom INDIRECT -funksjonen) og viser derfor alle elementene i den kategorien.

Viktig notat: Hvis hovedkategorien er mer enn ett ord (for eksempel 'Seasonal Fruits' i stedet for 'Fruit'), må du bruke formelen = INDIRECT (SUBSTITUTE (D3, ”“, ”_”)), i stedet for enkel INDIRECT -funksjon vist ovenfor.

• Grunnen til dette er at Excel ikke tillater mellomrom i navngitte områder. Så når du oppretter et navngitt område med mer enn ett ord, legger Excel automatisk inn en understreking mellom ordene. For eksempel, når du oppretter et navngitt område med ‘Seasonal Fruits’, vil det få navnet Season_Fruits i backend. Ved å bruke SUBSTITUTE -funksjonen i INDIRECT -funksjonen, sørg for at mellomrom er konvertert til understreker.

Tilbakestill/fjern innholdet i den avhengige rullegardinlisten automatisk

Når du har valgt og deretter endrer den overordnede nedtrekkslisten, vil den avhengige nedtrekkslisten ikke endres og vil derfor være en feil oppføring.

For eksempel, hvis du velger "Frukt" som kategori og deretter velger Apple som element, og deretter går tilbake og endrer kategorien til "Grønnsaker", vil den avhengige nedtrekksmenyen fortsette å vise Apple som elementet.

Du kan bruke VBA til å sørge for at innholdet i den avhengige rullegardinlisten nullstilles når hovedmenyen blir endret.

Her er VBA -koden for å slette innholdet i en avhengig rullegardinliste:

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 koden går til denne opplæringen av Debra om å slette avhengige rullegardinlister i Excel når valget endres.

Slik får du denne koden til å fungere:

• Kopier VBA -koden.
• I Excel -arbeidsboken der du har den avhengige rullegardinlisten, går du til Utvikler -fanen, og i gruppen "Kode" klikker du på Visual Basic (du kan også bruke hurtigtasten - ALT + F11).
• I VB Editor -vinduet, til venstre i prosjektutforskeren, vil du se alle regnearknavnene. Dobbeltklikk på den som har rullegardinlisten.
• Lim inn koden i kodevinduet til høyre.
• Lukk VB Editor.

Når du endrer hovedrullegardinlisten, vil VBA -koden bli avfyrt og den vil slette innholdet i den avhengige rullegardinlisten (som vist nedenfor).

Hvis du ikke er fan av VBA, kan du også bruke et enkelt betinget formateringstrick som vil markere cellen når det er feil samsvar. Dette kan hjelpe deg med å visuelt se og korrigere feil samsvar (som vist nedenfor).

Her er trinnene t0 som markerer feil samsvar i de avhengige rullegardinlistene:

• Velg cellen som har den eller de avhengige rullegardinlistene.
• Gå til Hjem -> Betinget formatering -> Ny regel.
• I dialogboksen Ny formateringsregel velger du 'Bruk en formel for å bestemme hvilke celler som skal formateres'.
• I formelfeltet skriver du inn følgende formel: = FEIL (VLOOKUP (E3, INDEX ($ A $ 2: $ B $ 6,, MATCH (D3, $ A $ 1: $ B $ 1)), 1,0))
• Angi formatet.
• Klikk OK.

Formelen bruker VLOOKUP -funksjonen til å kontrollere om elementet i den avhengige rullegardinlisten er det fra hovedkategorien eller ikke. Hvis det ikke er det, returnerer formelen en feil. Dette brukes av FEIL -funksjonen for å returnere TRUE som forteller betinget formatering for å markere cellen.

Du kan også like følgende Excel -opplæringsprogrammer:

• Trekk ut data basert på et rullegardinlistevalg.
• Opprette en rullegardinliste med søkeforslag.
• Velg flere elementer fra en rullegardinliste.
• Lag flere rullegardinlister uten gjentagelse.
• Spar tid med dataregistreringsskjemaer i Excel.