Hvordan lage en rullegardinliste i Excel

Nedtrekkslisten er et utrolig nyttig verktøy som kan bidra til å gjøre arbeidet med informasjon mer komfortabelt. Det gjør det mulig å inneholde flere verdier i en celle samtidig, som du kan jobbe med, som med alle andre. For å velge den du trenger, klikker du bare på pilikonet, hvoretter en liste over verdier uXNUMXbuXNUMXbis vises. Etter å ha valgt en bestemt, fylles cellen automatisk med den, og formlene beregnes på nytt basert på den.

Excel gir mange forskjellige metoder for å generere en rullegardinmeny, og i tillegg lar den deg tilpasse dem fleksibelt. La oss analysere disse metodene mer detaljert.

Prosessen for å lage liste

For å generere en popup-meny, klikk på menyelementene langs banen "Data" - "Datavalidering". En dialogboks åpnes der du må finne fanen "Parametere" og klikke på den hvis den ikke har blitt åpnet før. Den har mange innstillinger, men elementet "Datatype" er viktig for oss. Av alle betydningene er "Liste" den rette.

Antall metoder som informasjon legges inn i popup-listen på er ganske stort.

1. Uavhengig indikasjon av listeelementer atskilt med semikolon i "Kilde"-feltet som ligger på samme fane i samme dialogboks.
   

   

2. Foreløpig angivelse av verdier. Kilde-feltet inneholder området der den nødvendige informasjonen er tilgjengelig.
   

   

3. Spesifisere et navngitt område. En metode som gjentar den forrige, men det er bare nødvendig å foreløpig navngi området.
   

   

Enhver av disse metodene vil gi ønsket resultat. La oss se på metoder for å generere rullegardinlister i virkelige situasjoner.

Basert på data fra listen

La oss si at vi har en tabell som beskriver typene av forskjellige frukter.

For å lage en liste i en rullegardinmeny basert på dette settet med informasjon, må du gjøre følgende:

1. Velg cellen som er reservert for den fremtidige listen.
2. Finn Data-fanen på båndet. Der klikker vi på "Bekreft data".
   

   

3. Finn elementet "Data Type" og bytt verdien til "Liste".
   

   

4. I feltet som angir "Kilde"-alternativet, skriv inn ønsket område. Vær oppmerksom på at absolutte referanser må spesifiseres slik at informasjonen ikke forskyves ved kopiering av listen.
   

I tillegg er det en funksjon for å generere lister samtidig i mer enn én celle. For å oppnå dette bør du velge dem alle, og utføre de samme trinnene som beskrevet tidligere. Igjen, du må sørge for at absolutte referanser er skrevet. Hvis adressen ikke har et dollartegn ved siden av kolonne- og radnavnene, må du legge dem til ved å trykke på F4-tasten til $-tegnet er ved siden av kolonne- og radnavnene.

Med manuell dataregistrering

I situasjonen ovenfor ble listen skrevet ved å fremheve det nødvendige området. Dette er en praktisk metode, men noen ganger er det nødvendig å registrere dataene manuelt. Dette vil gjøre det mulig å unngå duplisering av informasjon i arbeidsboken.

Anta at vi står overfor oppgaven med å lage en liste som inneholder to mulige valg: ja og nei. For å utføre oppgaven er det nødvendig:

1. Klikk på cellen for listen.
2. Åpne "Data" og finn delen "Datasjekk" som er kjent for oss.
   

   

3. Igjen, velg "Liste"-typen.
   

   

4. Her må du skrive inn "Ja; Nei» som kilde. Vi ser at informasjon legges inn manuelt ved hjelp av semikolon for oppregning.

Etter å ha klikket OK, har vi følgende resultat.

Deretter vil programmet automatisk lage en rullegardinmeny i den aktuelle cellen. All informasjon som brukeren har spesifisert som elementer i popup-listen. Reglene for å lage en liste i flere celler ligner på de forrige, med det eneste unntaket at du må spesifisere informasjonen manuelt ved hjelp av semikolon.

Opprette en nedtrekksliste ved hjelp av OFFSET-funksjonen

I tillegg til den klassiske metoden er det mulig å bruke funksjonen FJERNINGfor å generere rullegardinmenyer.

La oss åpne arket.

For å bruke funksjonen for rullegardinlisten, må du gjøre følgende:

1. Velg cellen av interesse der du vil plassere den fremtidige listen.
2. Åpne "Data"-fanen og "Datavalidering"-vinduet i rekkefølge.
   

   

3. Sett "Liste". Dette gjøres på samme måte som de foregående eksemplene. Til slutt brukes følgende formel: =OFFSET(A$2$;0;0;5). Vi legger inn det der cellene som skal brukes som argument er spesifisert.

Deretter vil programmet lage en meny med en liste over frukt.

Syntaksen for dette er:

=OFFSET(referanse,linjeforskyvning,kolonneforskyvning,[høyde],[bredde])

Vi ser at denne funksjonen har 5 argumenter. Først gis den første celleadressen som skal forskyves. De neste to argumentene spesifiserer hvor mange rader og kolonner som skal forskyves. Når vi snakker om oss, er Height-argumentet 5 fordi det representerer høyden på listen. 

Nedtrekksliste i Excel med dataerstatning (+ ved bruk av OFFSET-funksjonen)

I det gitte tilfellet FJERNING tillatelse til å lage en popup-meny plassert i et fast område. Ulempen med denne metoden er at etter å ha lagt til elementet, må du redigere formelen selv.

For å lage en dynamisk liste med støtte for å legge inn ny informasjon, må du:

1. Velg cellen av interesse.
2. Utvid fanen "Data" og klikk på "Datavalidering".
3. I vinduet som åpnes, velg «Liste»-elementet igjen og spesifiser følgende formel som datakilde: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. Klikk på OK.

Denne inneholder en funksjon ANTALL.HVIS, for umiddelbart å finne ut hvor mange celler som er fylt (selv om den har et mye større antall bruksområder, skriver vi det bare her for et bestemt formål).

For at formelen skal fungere normalt, er det nødvendig å spore om det er tomme celler på banen til formelen. Det burde de ikke være.

Rullegardinliste med data fra et annet ark eller Excel-fil

Den klassiske metoden fungerer ikke hvis du trenger å hente informasjon fra et annet dokument eller til og med et ark i samme fil. Til dette brukes funksjonen INDIREKTE, som lar deg legge inn i riktig format en lenke til en celle som ligger i et annet ark eller generelt – en fil. Du må gjøre følgende:

1. Aktiver cellen der vi plasserer listen.
2. Vi åpner vinduet vi allerede kjenner. På samme sted som vi tidligere har angitt kilder for andre områder, er en formel angitt i formatet =INDIREKTE(“[List1.xlsx]Ark1!$A$1:$A$9”). Naturligvis, i stedet for Liste1 og Ark1, kan du sette inn henholdsvis bok- og arknavn. 

Merk følgende! Filnavnet er angitt i hakeparenteser. I dette tilfellet vil ikke Excel kunne bruke filen som for øyeblikket er stengt som en informasjonskilde.

Det bør også bemerkes at selve filnavnet gir mening bare hvis det nødvendige dokumentet er plassert i samme mappe som den der listen vil bli satt inn. Hvis ikke, må du spesifisere adressen til dette dokumentet i sin helhet.

Opprette avhengige rullegardiner

En avhengig liste er en hvis innhold påvirkes av brukerens valg i en annen liste. Anta at vi har et bord åpent foran oss som inneholder tre områder, som hver har fått et navn.

Du må følge disse trinnene for å generere lister hvis resultat påvirkes av alternativet som er valgt i en annen liste.

1. Lag første liste med områdenavn.
   

   

2. Ved kildeinngangspunktet er de nødvendige indikatorene uthevet én etter én.
   

   

3. Lag en 2. liste avhengig av hvilken type plante personen har valgt. Alternativt, hvis du spesifiserer trær i den første listen, vil informasjonen i den andre listen være "eik, agnbøk, kastanje" og mer. Det er nødvendig å skrive ned formelen i stedet for input til datakilden =INDIREKTE(E3). E3 – celle som inneholder navnet på området 1.=INDIREKTE(E3). E3 – celle med navnet på listen 1.

Nå er alt klart.

Hvordan velge flere verdier fra en rullegardinliste?

Noen ganger er det ikke mulig å gi preferanse til bare én verdi, så mer enn én må velges. Deretter må du legge til en makro til sidekoden. Ved å bruke tastekombinasjonen Alt + F11 åpnes Visual Basic Editor. Og koden er satt inn der.

Private Sub Worksheet_Change (ByVal Target As Range)

    On Error Resume Next

    If Not Intersect(Target, Range(«Е2:Е9»)) Er Ingenting Og Target.Cells.Count = 1 Da

        Application.EnableEvents = False

        Hvis Len (Target.Offset (0, 1)) = 0 Da

            Target.Offset (0, 1) = Mål

        ellers

            Target.End (xlToRight) .Offset (0, 1) = Mål

        End If

        Target.ClearContents

        Application.EnableEvents = True

    End If

End Sub 

For at innholdet i cellene skal vises nedenfor, setter vi inn følgende kode i editoren.

Private Sub Worksheet_Change (ByVal Target As Range)

    On Error Resume Next

    If Not Intersect(Target, Range(«Н2:К2»)) Is Nothing And Target.Cells.Count = 1 Da

        Application.EnableEvents = False

        Hvis Len (Target.Offset (1, 0)) = 0 Da

            Target.Offset (1, 0) = Mål

        ellers

            Target.End (xlDown) .Offset (1, 0) = Mål

        End If

        Target.ClearContents

        Application.EnableEvents = True

    End If

End Sub

Og til slutt, denne koden brukes til å skrive i én celle.

Private Sub Worksheet_Change (ByVal Target As Range)

    On Error Resume Next

    If Not Intersect(Target, Range(«C2:C5»)) Is Nothing And Target.Cells.Count = 1 Da

        Application.EnableEvents = False

        newVal = Mål

        Application.Angre

        oldval = Mål

        Hvis Len (oldval) <> 0 Og oldval <> newVal Da

            Target = Target & «,» & newVal

        ellers

            Mål = nyVal

        End If

        Hvis Len (newVal) = 0, så Target.ClearContents

        Application.EnableEvents = True

    End If

End Sub

Områder er redigerbare.

Hvordan lage en rullegardinliste med et søk?

I dette tilfellet må du først bruke en annen type liste. "Utvikler"-fanen åpnes, hvoretter du må klikke eller trykke (hvis skjermen er berøring) på "Sett inn" - "ActiveX" -elementet. Den har en kombinasjonsboks. Du vil bli bedt om å tegne denne listen, og deretter legges den til i dokumentet.

Videre er det konfigurert gjennom egenskaper, der et område er spesifisert i ListFillRange-alternativet. Cellen der den brukerdefinerte verdien vises, konfigureres ved hjelp av alternativet LinkedCell. Deretter trenger du bare å skrive ned de første tegnene, siden programmet automatisk foreslår mulige verdier.

Nedtrekksliste med automatisk dataerstatning

Det er også en funksjon som gjør at dataene erstattes automatisk etter at de er lagt til området. Det er enkelt å gjøre dette:

1. Lag et sett med celler for den fremtidige listen. I vårt tilfelle er dette et sett med farger. Vi velger det.
   

   

2. Deretter må den formateres som en tabell. Du må klikke på knappen med samme navn og velge tabellstilen.
   

   

   

Deretter må du bekrefte dette området ved å trykke på "OK"-knappen.

Vi velger den resulterende tabellen og gir den et navn gjennom inndatafeltet øverst i kolonne A.

Det er det, det er en tabell, og den kan brukes som grunnlag for en rullegardinliste, som du trenger:

1. Velg cellen der listen er plassert.
2. Åpne dialogboksen Datavalidering.
   

   

3. Vi setter datatypen til "Liste", og som verdier gir vi navnet på tabellen gjennom =-tegnet.
   

   

   

Alt, cellen er klar, og navnene på fargene vises i den, slik vi opprinnelig trengte. Nå kan du legge til nye posisjoner ganske enkelt ved å skrive dem i en celle som ligger litt lavere rett etter den siste.

Dette er fordelen med tabellen, at rekkevidden automatisk øker når nye data legges til. Følgelig er dette den mest praktiske måten å legge til en liste på.

Hvordan kopiere rullegardinlisten?

For å kopiere er det nok å bruke tastekombinasjonen Ctrl + C og Ctrl + V. Så rullegardinlisten vil bli kopiert sammen med formateringen. For å fjerne formatering, må du bruke en spesiell lim (i kontekstmenyen vises dette alternativet etter kopiering av listen), der alternativet "betingelser for verdier" er satt.

Velg alle cellene som inneholder en rullegardinliste

For å utføre denne oppgaven må du bruke funksjonen "Velg en gruppe med celler" i "Finn og velg"-gruppen.

Deretter åpnes en dialogboks der du bør velge elementene "Alle" og "De samme" i menyen "Datavalidering". Det første elementet velger alle lister, og det andre velger bare de som ligner på enkelte.