Sådan opretter du en rulleliste i Excel (den eneste vejledning, du har brug for)

En rulleliste er en glimrende måde at give brugeren mulighed for at vælge fra en foruddefineret liste.

Det kan bruges, mens du får en bruger til at udfylde en formular, eller mens du opretter interaktive Excel -dashboards.

Drop-down lister er ret almindelige på websteder/apps og er meget intuitive for brugeren.

Se video - Oprettelse af en rulleliste i Excel

I denne vejledning lærer du, hvordan du opretter en rulleliste i Excel (det tager kun et par sekunder at gøre dette) sammen med alle de fantastiske ting, du kan gøre med det.

Sådan opretter du en rulleliste i Excel

I dette afsnit lærer du de nøjagtige trin for at oprette en Excel-rulleliste:

1. Brug af data fra celler.
2. Indtastning af data manuelt.
3. Brug af OFFSET -formlen.

#1 Brug af data fra celler

Lad os sige, at du har en liste over varer som vist herunder:

Her er trinene til at oprette en Excel -rulleliste:

1. Vælg en celle, hvor du vil oprette rullelisten.
2. Gå til Data -> Dataværktøjer -> Datavalidering.
3. I dialogboksen Datavalidering, under fanen Indstillinger, skal du vælge Liste som valideringskriterier.
   • Så snart du vælger Liste, vises kildefeltet.
4. I kildefeltet skal du indtaste = $ A $ 2: $ A $ 6, eller blot klikke i feltet Kilde og markere cellerne ved hjælp af musen og klikke på OK. Dette vil indsætte en rulleliste i celle C2.
   • Sørg for, at rullemenuen In-cell er markeret (som er markeret som standard). Hvis denne indstilling ikke er markeret, viser cellen ikke en rullemenu, men du kan manuelt indtaste værdierne på listen.

Bemærk: Hvis du vil oprette rullelister i flere celler på én gang, skal du markere alle de celler, hvor du vil oprette det, og derefter følge ovenstående trin. Sørg for, at cellereferencerne er absolutte (f.eks. $ A $ 2) og ikke relative (f.eks. A2 eller A $ 2 eller $ A2).

#2 Ved at indtaste data manuelt

I ovenstående eksempel bruges cellereferencer i feltet Kilde. Du kan også tilføje elementer direkte ved at indtaste det manuelt i kildefeltet.

Lad os f.eks. Sige, at du vil vise to muligheder, Ja og Nej, i rullemenuen i en celle. Sådan kan du direkte indtaste det i datavalideringskildefeltet:

• Vælg en celle, hvor du vil oprette rullelisten (celle C2 i dette eksempel).
• Gå til Data -> Dataværktøjer -> Datavalidering.
• I dialogboksen Datavalidering, under fanen Indstillinger, skal du vælge Liste som valideringskriterier.
  • Så snart du vælger Liste, vises kildefeltet.
• Indtast Ja, Nej i kildefeltet
  • Sørg for, at rullemenuen In-cell er markeret.
• Klik på OK.

Dette vil oprette en rulleliste i den valgte celle. Alle de elementer, der er angivet i kildefeltet, adskilt med et komma, er angivet på forskellige linjer i rullemenuen.

Alle de elementer, der er angivet i kildefeltet, adskilt med et komma, vises på forskellige linjer i rullelisten.

Bemærk: Hvis du vil oprette rullelister i flere celler på én gang, skal du markere alle de celler, hvor du vil oprette det, og derefter følge ovenstående trin.

#3 Brug af Excel -formler

Bortset fra at vælge fra celler og indtaste data manuelt, kan du også bruge en formel i kildefeltet til at oprette en Excel -rulleliste.

Enhver formel, der returnerer en liste med værdier, kan bruges til at oprette en rulleliste i Excel.

Antag f.eks., At du har datasættet som vist herunder:

Her er trinene til at oprette en Excel -rulleliste ved hjælp af OFFSET -funktionen:

• Vælg en celle, hvor du vil oprette rullelisten (celle C2 i dette eksempel).
• Gå til Data -> Dataværktøjer -> Datavalidering.
• I dialogboksen Datavalidering, under fanen Indstillinger, skal du vælge Liste som valideringskriterier.
  • Så snart du vælger Liste, vises kildefeltet.
• Indtast følgende formel i feltet Kilde: = OFFSET ($ A $ 2,0,0,5)
  • Sørg for, at rullemenuen In-cell er markeret.
• Klik på OK.

Dette vil oprette en rulleliste, der viser alle frugtnavne (som vist nedenfor).

Bemærk: Hvis du vil oprette en rulleliste i flere celler på én gang, skal du markere alle de celler, hvor du vil oprette den, og derefter følge ovenstående trin. Sørg for, at cellereferencerne er absolutte (f.eks. $ A $ 2) og ikke relative (f.eks. A2 eller A $ 2 eller $ A2).

Hvordan fungerer denne formel ??

I ovenstående tilfælde brugte vi en OFFSET -funktion til at oprette rullelisten. Det returnerer en liste over varer fra ra

Det returnerer en liste over varer fra intervallet A2: A6.

Her er syntaksen for OFFSET -funktionen: = OFFSET (reference, rækker, kolonner, [højde], [bredde])

Det kræver fem argumenter, hvor vi angav referencen som A2 (udgangspunktet for listen). Rækker/Cols er angivet som 0, da vi ikke ønsker at opveje referencecellen. Højde er angivet som 5, da der er fem elementer på listen.

Når du nu bruger denne formel, returnerer den en matrix, der har listen over de fem frugter i A2: A6. Bemærk, at hvis du indtaster formlen i en celle, vælger du den og trykker på F9, vil du se, at den returnerer en matrix med frugtnavnene.

Oprettelse af en dynamisk rulleliste i Excel (ved hjælp af OFFSET)

Ovenstående teknik til at bruge en formel til at oprette en rulleliste kan udvides til også at oprette en dynamisk rulleliste. Hvis du bruger OFFSET -funktionen, som vist ovenfor, selvom du tilføjer flere elementer til listen, opdateres rullemenuen ikke automatisk. Du bliver nødt til at opdatere det manuelt hver gang du ændrer listen.

Her er en måde at gøre det dynamisk på (og det er ikke andet end en mindre finjustering i formlen):

• Vælg en celle, hvor du vil oprette rullelisten (celle C2 i dette eksempel).
• Gå til Data -> Dataværktøjer -> Datavalidering.
• I dialogboksen Datavalidering, under fanen Indstillinger, skal du vælge Liste som valideringskriterier. Så snart du vælger Liste, vises kildefeltet.
• Indtast følgende formel i kildefeltet: = OFFSET ($ A $ 2,0,0, COUNTIF ($ A $ 2: $ A $ 100, ””))
• Sørg for, at rullemenuen In-cell er markeret.
• Klik på OK.

I denne formel har jeg erstattet argumentet 5 med COUNTIF ($ A $ 2: $ A $ 100, ””).

COUNTIF-funktionen tæller de ikke-tomme celler i området A2: A100. OFFSET-funktionen justerer sig derfor til at omfatte alle de ikke-tomme celler.

Bemærk:

• For at dette skal fungere, må der IKKE være tomme celler mellem cellerne, der er fyldt.
• Hvis du vil oprette en rulleliste i flere celler på én gang, skal du markere alle de celler, hvor du vil oprette den, og derefter følge ovenstående trin. Sørg for, at cellehenvisningerne er absolutte (f.eks. $ A $ 2) og ikke relative (f.eks. A2 eller A $ 2 eller $ A2).

Kopier lim-rullelister i Excel

Du kan kopiere og indsætte cellerne med datavalidering til andre celler, og det vil også kopiere datavalideringen.

For eksempel, hvis du har en rulleliste i celle C2, og du også vil anvende den på C3: C6, skal du blot kopiere cellen C2 og indsætte den i C3: C6. Dette vil kopiere rullelisten og gøre den tilgængelig i C3: C6 (sammen med rullemenuen vil den også kopiere formateringen).

Hvis du kun vil kopiere rullemenuen og ikke formateringen, er her trinene:

• Kopier cellen, der har rullemenuen.
• Vælg de celler, hvor du vil kopiere rullemenuen.
• Gå til Hjem -> Indsæt -> Indsæt special.
• Vælg Validering i indsætningsindstillinger i dialogboksen Indsæt speciel.
• Klik på OK.

Dette kopierer kun rullemenuen og ikke formateringen af ​​den kopierede celle.

Forsigtighed, mens du arbejder med Excel -rulleliste

Du skal være forsigtig, når du arbejder med rullelister i Excel.

Når du kopierer en celle (der ikke indeholder en rulleliste) over en celle, der indeholder en rulleliste, går rullelisten tabt.

Den værste del af dette er, at Excel ikke viser nogen advarsel eller prompt for at lade brugeren vide, at en rullemenu vil blive overskrevet.

Sådan vælges alle celler, der har en rulleliste i den

Nogle gange er det svært at vide, hvilke celler der indeholder rullelisten.

Derfor er det fornuftigt at markere disse celler ved enten at give det en tydelig kant eller en baggrundsfarve.

I stedet for manuelt at kontrollere alle cellerne, er der en hurtig måde at vælge alle de celler, der har rullelister (eller en hvilken som helst datavalideringsregel) i den.

• Gå til Hjem -> Find og vælg -> Gå til Special.
• Vælg Datavalidering i dialogboksen Gå til speciel
  • Datavalidering har to muligheder: Alle og samme. Alle ville vælge alle de celler, der har en datavalideringsregel anvendt på den. Det samme ville kun vælge de celler, der har den samme datavalideringsregel som den for den aktive celle.
• Klik på OK.

Dette ville øjeblikkeligt vælge alle de celler, der har en datavalideringsregel anvendt på den (dette inkluderer også rullelister).

Nu kan du simpelthen formatere cellerne (give en kant eller en baggrundsfarve), så de er visuelt synlige, og du ikke ved et uheld kopierer en anden celle på den.

Her er en anden teknik af Jon Acampora, du kan bruge til altid at holde pil ned -ikonet synligt. Du kan også se nogle måder at gøre dette på i denne video af Mr. Excel.

Oprettelse af en afhængig / betinget Excel -rulleliste

Her er en video om, hvordan du opretter en afhængig rulleliste i Excel.

Hvis du foretrækker at læse frem for at se en video, skal du fortsætte med at læse.

Nogle gange kan du have mere end én rulleliste, og du vil have, at elementerne, der vises i den anden rulleliste, skal afhænge af, hvad brugeren valgte i den første rulleliste.

Disse kaldes afhængige eller betingede rullelister.

Nedenfor er et eksempel på en betinget/afhængig rulleliste:

I ovenstående eksempel, når elementerne i 'Drop Down 2' er afhængige af det valg, der er foretaget i 'Drop Down 1'.

Lad os nu se, hvordan du opretter dette.

Her er trinene til at oprette en afhængig / betinget 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, mens du arbejder med betingede rullelister i Excel:

• Når du har foretaget valget, og derefter ændrer den overordnede rullemenu, ændres den afhængige rullemenu ikke og ville derfor være en forkert indtastning. For eksempel, hvis du vælger USA som land og derefter vælger Florida som staten og derefter går tilbage og ændrer landet til Indien, forbliver staten som Florida. Her er en god tutorial fra Debra om at slette afhængige (betingede) rullelister i Excel, når valget ændres.
• 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. Så 'Seasonal Fruits' navngivet sortiment ville være 'Seasonal_Fruits'. Brug af SUBSTITUTE -funktionen i INDIRECT -funktionen sørger for, at mellemrum er konverteret til understregninger.