Hvordan lage en nedtrekksliste i Excel (den eneste guiden du trenger)

En rullegardinliste er en utmerket måte å gi brukeren et alternativ å velge fra en forhåndsdefinert liste.

Det kan brukes mens du får en bruker til å fylle ut et skjema, eller mens du oppretter interaktive Excel -dashbord.

Nedtrekkslister er ganske vanlige på nettsteder/apper og er veldig intuitive for brukeren.

Se video - Opprette en nedtrekksliste i Excel

I denne opplæringen lærer du hvordan du lager en rullegardinliste i Excel (det tar bare noen få sekunder å gjøre dette) sammen med alle de fantastiske tingene du kan gjøre med det.

Hvordan lage en nedtrekksliste i Excel

I denne delen lærer du de nøyaktige trinnene for å lage en Excel-rullegardinliste:

1. Bruke data fra celler.
2. Angi data manuelt.
3. Bruke OFFSET -formelen.

#1 Bruke data fra celler

La oss si at du har en liste over elementer som vist nedenfor:

Her er trinnene for å lage en Excel -rullegardinliste:

1. Velg en celle der du vil opprette rullegardinlisten.
2. Gå til Data -> Dataverktøy -> Datavalidering.
3. I dialogboksen Datavalidering, i kategorien Innstillinger, velger du Liste som valideringskriterier.
   • Så snart du velger Liste, vises kildefeltet.
4. I kildefeltet skriver du inn = $ A $ 2: $ A $ 6, eller bare klikker i kildefeltet og velger cellene med musen og klikker OK. Dette vil sette inn en rullegardinliste i celle C2.
   • Sørg for at rullegardinmenyen i cellen er merket av (som er merket som standard). Hvis dette alternativet ikke er merket av, viser ikke cellen en rullegardinmeny, men du kan manuelt skrive inn verdiene i listen.

Merk: Hvis du vil lage rullegardinlister i flere celler på en gang, velger du alle cellene der du vil opprette den, og følger deretter trinnene ovenfor. Sørg for at cellereferansene er absolutte (for eksempel $ A $ 2) og ikke relative (for eksempel A2 eller A $ 2 eller $ A2).

#2 Ved å legge inn data manuelt

I eksemplet ovenfor brukes cellereferanser i kildefeltet. Du kan også legge til elementer direkte ved å skrive det inn manuelt i kildefeltet.

La oss for eksempel si at du vil vise to alternativer, Ja og Nei, i rullegardinmenyen i en celle. Slik kan du skrive det inn direkte i datavalideringskildefeltet:

• Velg en celle der du vil opprette rullegardinlisten (celle C2 i dette eksemplet).
• Gå til Data -> Dataverktøy -> Datavalidering.
• I dialogboksen Datavalidering, i kategorien Innstillinger, velger du Liste som valideringskriterier.
  • Så snart du velger Liste, vises kildefeltet.
• Skriv inn Ja, Nei i kildefeltet
  • Sørg for at rullegardinmenyen i cellen er merket av.
• Klikk OK.

Dette vil opprette en rullegardinliste i den valgte cellen. Alle elementene som er oppført i kildefeltet, atskilt med et komma, er oppført på forskjellige linjer i rullegardinmenyen.

Alle elementene som er angitt i kildefeltet, atskilt med et komma, vises på forskjellige linjer i rullegardinlisten.

Merk: Hvis du vil lage rullegardinlister i flere celler på en gang, velger du alle cellene der du vil opprette den, og følger deretter trinnene ovenfor.

#3 Bruke Excel -formler

Bortsett fra å velge fra celler og skrive inn data manuelt, kan du også bruke en formel i kildefeltet til å lage en Excel -rullegardinliste.

Enhver formel som returnerer en liste med verdier kan brukes til å lage en rullegardinliste i Excel.

Anta for eksempel at du har datasettet som vist nedenfor:

Her er trinnene for å lage en Excel -rullegardinliste ved hjelp av OFFSET -funksjonen:

• Velg en celle der du vil opprette rullegardinlisten (celle C2 i dette eksemplet).
• Gå til Data -> Dataverktøy -> Datavalidering.
• I dialogboksen Datavalidering, i kategorien Innstillinger, velger du Liste som valideringskriterier.
  • Så snart du velger Liste, vises kildefeltet.
• Skriv inn følgende formel i feltet Kilde: = OFFSET ($ A $ 2,0,0,5)
  • Sørg for at rullegardinmenyen i cellen er merket av.
• Klikk OK.

Dette vil opprette en rullegardinliste som viser alle fruktnavnene (som vist nedenfor).

Merk: Hvis du vil opprette en rullegardinliste i flere celler på en gang, velger du alle cellene der du vil opprette den og følger trinnene ovenfor. Sørg for at cellereferansene er absolutte (for eksempel $ A $ 2) og ikke relative (for eksempel A2 eller A $ 2 eller $ A2).

Hvordan fungerer denne formelen ??

I tilfellet ovenfor brukte vi en OFFSET -funksjon for å lage rullegardinlisten. Den returnerer en liste over varer fra ra

Den returnerer en liste over varer fra området A2: A6.

Her er syntaksen til OFFSET -funksjonen: = OFFSET (referanse, rader, cols, [høyde], [bredde])

Det krever fem argumenter, der vi spesifiserte referansen som A2 (startpunktet for listen). Rader/kolonner er spesifisert som 0, ettersom vi ikke vil kompensere referansecellen. Høyden er angitt som 5 ettersom det er fem elementer i listen.

Når du bruker denne formelen, returnerer den en matrise som har listen over de fem fruktene i A2: A6. Vær oppmerksom på at hvis du skriver inn formelen i en celle, velger du den og trykker på F9, vil du se at den returnerer en rekke fruktnavn.

Opprette en dynamisk nedtrekksliste i Excel (ved hjelp av OFFSET)

Ovenstående teknikk for å bruke en formel for å lage en rullegardinliste kan utvides til å lage en dynamisk rullegardinliste også. Hvis du bruker OFFSET -funksjonen, som vist ovenfor, selv om du legger til flere elementer på listen, vil rullegardinmenyen ikke oppdateres automatisk. Du må oppdatere den manuelt hver gang du endrer listen.

Her er en måte å gjøre den dynamisk på (og det er bare en liten finjustering i formelen):

• Velg en celle der du vil opprette rullegardinlisten (celle C2 i dette eksemplet).
• Gå til Data -> Dataverktøy -> Datavalidering.
• I dialogboksen Datavalidering, i kategorien Innstillinger, velger du Liste som valideringskriterier. Så snart du velger Liste, vises kildefeltet.
• Skriv inn følgende formel i kildefeltet: = OFFSET ($ A $ 2,0,0, COUNTIF ($ A $ 2: $ A $ 100, ””))
• Sørg for at rullegardinmenyen i cellen er merket av.
• Klikk OK.

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

COUNTIF-funksjonen teller de ikke-tomme cellene i området A2: A100. Derfor justerer OFFSET-funksjonen seg til å inkludere alle de ikke-tomme cellene.

Merk:

• For at dette skal fungere, må det IKKE være noen tomme celler mellom cellene som er fylt.
• Hvis du vil opprette en rullegardinliste i flere celler på en gang, velger du alle cellene der du vil opprette den og følger trinnene ovenfor. Sørg for at cellereferansene er absolutte (for eksempel $ A $ 2) og ikke relative (for eksempel A2 eller A $ 2 eller $ A2).

Kopier Lim-nedtrekkslister i Excel

Du kan kopiere lim inn cellene med datavalidering til andre celler, og den vil også kopiere datavalideringen.

For eksempel, hvis du har en rullegardinliste i celle C2, og du vil bruke den på C3: C6 også, bare kopier cellen C2 og lim den inn i C3: C6. Dette vil kopiere rullegardinlisten og gjøre den tilgjengelig i C3: C6 (sammen med rullegardinmenyen vil den også kopiere formateringen).

Hvis du bare vil kopiere rullegardinmenyen og ikke formateringen, er dette trinnene:

• Kopier cellen som har rullegardinmenyen.
• Velg cellene der du vil kopiere rullegardinmenyen.
• Gå til Hjem -> Lim inn -> Lim inn spesial.
• Velg Validering i Lim inn -alternativer i dialogboksen Lim inn spesiell.
• Klikk OK.

Dette vil bare kopiere rullegardinmenyen og ikke formateringen av den kopierte cellen.

Forsiktig når du arbeider med Excel -rullegardinliste

Du må være forsiktig når du jobber med rullegardinlister i Excel.

Når du kopierer en celle (som ikke inneholder en rullegardinliste) over en celle som inneholder en rullegardinliste, går rullegardinlisten tapt.

Den verste delen av dette er at Excel ikke vil vise noen varsel eller melding for å gi brukeren beskjed om at en rullegardinmeny vil bli overskrevet.

Hvordan velge alle celler som har en rullegardinliste i den

Noen ganger er det vanskelig å vite hvilke celler som inneholder rullegardinlisten.

Derfor er det fornuftig å markere disse cellene ved å enten gi den en tydelig ramme eller en bakgrunnsfarge.

I stedet for å kontrollere alle cellene manuelt, er det en rask måte å velge alle cellene som har rullegardinlister (eller en datavalideringsregel).

• Gå til Hjem -> Finn og velg -> Gå til spesialtilbud.
• Velg Datavalidering i dialogboksen Gå til spesiell
  • Datavalidering har to alternativer: Alle og samme. Alle ville velge alle cellene som har en datavalideringsregel på. Det samme ville bare velge cellene som har samme datavalideringsregel som for den aktive cellen.
• Klikk OK.

Dette ville umiddelbart velge alle cellene som har en datavalideringsregel brukt på den (dette inkluderer også rullegardinlister).

Nå kan du ganske enkelt formatere cellene (gi en kant eller en bakgrunnsfarge) slik at de er synlig og du ikke ved et uhell kopierer en annen celle på den.

Her er en annen teknikk av Jon Acampora du kan bruke til å alltid holde nedtrekkspilikonet synlig. Du kan også se noen måter å gjøre dette på i denne videoen av Mr. Excel.

Opprette en avhengig / betinget Excel -rullegardinliste

Her er en video om hvordan du oppretter en avhengig rullegardinliste i Excel.

Hvis du foretrekker å lese fremfor å se på en video, fortsett å lese.

Noen ganger kan det hende du har mer enn én rullegardinliste, og at elementene som vises i den andre rullegardinlisten, skal være avhengige av hva brukeren valgte i den første rullegardinlisten.

Disse kalles avhengige eller betingede rullegardinlister.

Nedenfor er et eksempel på en betinget/avhengig rullegardinliste:

I eksemplet ovenfor, når elementene som er oppført i 'Drop Down 2' er avhengig av valget som er gjort i 'Drop Down 1'.

La oss nå se hvordan du lager dette.

Her er trinnene for å lage en avhengig / betinget 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 merknad mens du arbeider med betingede rullegardinlister i Excel:

• Når du har valgt, og deretter endrer den overordnede rullegardinmenyen, vil den avhengige nedtrekksmenyen ikke endres og vil derfor være en feil oppføring. For eksempel, hvis du velger USA som land og deretter velger Florida som stat, og deretter går tilbake og endrer landet til India, vil staten forbli som Florida. Her er en flott opplæring av Debra om å slette avhengige (betingede) rullegardinlister i Excel når valget endres.
• 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. Så serien "Seasonal Fruits" vil være "Seasonal_Fruits". Ved å bruke SUBSTITUTE -funksjonen i INDIRECT -funksjonen, sørg for at mellomrom er konvertert til understreker.