Sådan laver du en rulleliste i Excel

Rullelisten er et utroligt nyttigt værktøj, der kan hjælpe med at gøre arbejdet med information mere behageligt. Det gør det muligt at indeholde flere værdier i en celle på én gang, som du kan arbejde med, ligesom med alle andre. For at vælge den, du har brug for, skal du blot klikke på pileikonet, hvorefter en liste over værdier uXNUMXbuXNUMXbis vises. Efter at have valgt en bestemt, udfyldes cellen automatisk med den, og formlerne genberegnes baseret på den.

Excel giver mange forskellige metoder til at generere en rullemenu, og derudover giver det dig mulighed for fleksibelt at tilpasse dem. Lad os analysere disse metoder mere detaljeret.

Liste oprettelsesproces

For at generere en pop op-menu skal du klikke på menupunkterne langs stien "Data" - "Datavalidering". En dialogboks åbnes, hvor du skal finde fanen "Parameters" og klikke på den, hvis den ikke er blevet åbnet før. Det har mange indstillinger, men "Data Type"-elementet er vigtigt for os. Af alle betydningerne er "Liste" den rigtige.

Antallet af metoder, hvorpå oplysninger indtastes i pop-up-listen, er ret stort.

1. Uafhængig indikation af listeelementer adskilt af et semikolon i feltet "Kilde" placeret på samme fane i den samme dialogboks.
   

   

2. Foreløbig angivelse af værdier. Feltet Kilde indeholder det område, hvor de nødvendige oplysninger er tilgængelige.
   

   

3. Angivelse af et navngivet område. En metode, der gentager den forrige, men det er kun nødvendigt at foreløbigt navngive området.
   

   

Enhver af disse metoder vil give det ønskede resultat. Lad os se på metoder til at generere drop-down lister i virkelige situationer.

Baseret på data fra listen

Lad os sige, at vi har en tabel, der beskriver typerne af forskellige frugter.

For at oprette en liste i en rullemenu baseret på dette sæt oplysninger, skal du gøre følgende:

1. Vælg den celle, der er reserveret til den fremtidige liste.
2. Find fanen Data på båndet. Der klikker vi på "Bekræft data".
   

   

3. Find punktet "Data Type", og skift værdien til "Liste".
   

   

4. Indtast det ønskede område i feltet, der angiver "Kilde". Vær opmærksom på, at absolutte referencer skal angives, så oplysningerne ikke forskydes ved kopiering af listen.
   

Derudover er der en funktion til at generere lister på én gang i mere end én celle. For at opnå dette skal du vælge dem alle og udføre de samme trin som beskrevet tidligere. Igen skal du sørge for, at der er skrevet absolutte referencer. Hvis adressen ikke har et dollartegn ved siden af ​​kolonne- og rækkenavnene, skal du tilføje dem ved at trykke på F4-tasten, indtil $-tegnet er ved siden af ​​kolonne- og rækkenavnene.

Med manuel dataregistrering

I situationen ovenfor blev listen skrevet ved at fremhæve det påkrævede område. Dette er en praktisk metode, men nogle gange er det nødvendigt at registrere data manuelt. Dette vil gøre det muligt at undgå duplikering af oplysninger i projektmappen.

Antag, at vi står over for opgaven med at oprette en liste med to mulige valg: ja og nej. For at udføre opgaven er det nødvendigt:

1. Klik på cellen for listen.
2. Åbn "Data", og find sektionen "Datacheck", som vi kender.
   

   

3. Igen skal du vælge typen "Liste".
   

   

4. Her skal du indtaste "Ja; Nej” som kilde. Vi ser, at oplysningerne indtastes manuelt ved hjælp af semikolon til opregning.

Efter at have klikket på OK, har vi følgende resultat.

Dernæst vil programmet automatisk oprette en rullemenu i den relevante celle. Alle oplysninger, som brugeren har angivet som elementer i pop-up-listen. Reglerne for oprettelse af en liste i flere celler ligner de foregående, med den eneste undtagelse, at du skal angive oplysningerne manuelt ved hjælp af semikolon.

Oprettelse af en rulleliste ved hjælp af OFFSET-funktionen

Ud over den klassiske metode er det muligt at bruge funktionen BORTSKAFFELSEfor at generere dropdown-menuer.

Lad os åbne arket.

For at bruge funktionen til rullelisten skal du gøre følgende:

1. Vælg den celle af interesse, hvor du vil placere den fremtidige liste.
2. Åbn fanen "Data" og vinduet "Datavalidering" i rækkefølge.
   

   

3. Sætliste". Dette gøres på samme måde som de foregående eksempler. Til sidst bruges følgende formel: =OFFSET(A$2$;0;0;5). Vi indtaster det, hvor de celler, der skal bruges som argument, er angivet.

Derefter vil programmet oprette en menu med en liste over frugter.

Syntaksen for dette er:

=OFFSET(reference,linjeforskydning,søjleforskydning,[højde],[bredde])

Vi ser, at denne funktion har 5 argumenter. Først gives den første celleadresse, der skal forskydes. De næste to argumenter angiver, hvor mange rækker og kolonner der skal forskydes. Når vi taler om os, så er højdeargumentet 5, fordi det repræsenterer højden på listen. 

Rulleliste i Excel med datasubstitution (+ ved hjælp af OFFSET-funktionen)

I det givne tilfælde BORTSKAFFELSE lov til at oprette en pop-up menu placeret i et fast område. Ulempen ved denne metode er, at efter at have tilføjet elementet, skal du selv redigere formlen.

For at oprette en dynamisk liste med understøttelse af indtastning af nye oplysninger skal du:

1. Vælg cellen af ​​interesse.
2. Udvid fanen "Data" og klik på "Datavalidering".
3. I vinduet, der åbnes, skal du vælge "Liste"-elementet igen og angive følgende formel som datakilde: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. Klik på OK.

Denne indeholder en funktion TÆL.HVIS, for straks at bestemme, hvor mange celler der er fyldt (selvom det har et meget større antal anvendelser, skriver vi det bare her til et bestemt formål).

For at formlen skal fungere normalt, er det nødvendigt at spore, om der er tomme celler på formlens sti. Det burde de ikke være.

Rulleliste med data fra et andet ark eller Excel-fil

Den klassiske metode virker ikke, hvis du har brug for at få oplysninger fra et andet dokument eller endda et ark indeholdt i den samme fil. Til dette bruges funktionen INDIREKTE, som giver dig mulighed for i det korrekte format at indtaste et link til en celle placeret i et andet ark eller generelt – en fil. Du skal gøre følgende:

1. Aktiver cellen, hvor vi placerer listen.
2. Vi åbner det vindue, vi allerede kender. Samme sted, hvor vi tidligere har angivet kilder for andre områder, er en formel angivet i formatet =INDIREKTE(“[List1.xlsx]Ark1!$A$1:$A$9”). I stedet for Liste1 og Ark1 kan du naturligvis indsætte henholdsvis dine bog- og arknavne. 

Opmærksomhed! Filnavnet er angivet i firkantede parenteser. I dette tilfælde vil Excel ikke være i stand til at bruge den fil, der i øjeblikket er lukket, som en informationskilde.

Det skal også bemærkes, at selve filnavnet kun giver mening, hvis det påkrævede dokument er placeret i samme mappe som den, hvor listen vil blive indsat. Hvis ikke, skal du angive hele adressen på dette dokument.

Oprettelse af afhængige dropdowns

En afhængig liste er en, hvis indhold påvirkes af brugerens valg på en anden liste. Antag, at vi har et bord åbent foran os, der indeholder tre områder, som hver har fået et navn.

Du skal følge disse trin for at generere lister, hvis resultat er påvirket af den indstilling, der er valgt på en anden liste.

1. Opret 1. liste med områdenavne.
   

   

2. Ved kildeindgangspunktet fremhæves de påkrævede indikatorer én efter én.
   

   

3. Opret en 2. liste alt efter hvilken plantetype personen har valgt. Alternativt, hvis du angiver træer i den første liste, vil oplysningerne i den anden liste være "eg, avnbøg, kastanje" og mere. Det er nødvendigt at nedskrive formlen i stedet for input af datakilden =INDIREKTE(E3). E3 – celle, der indeholder navnet på området 1.=INDIREKTE(E3). E3 – celle med navnet på listen 1.

Nu er alt klar.

Hvordan vælger man flere værdier fra en rulleliste?

Nogle gange er det ikke muligt kun at give fortrinsret til én værdi, så der skal vælges mere end én. Så skal du tilføje en makro til sidekoden. Ved at bruge tastekombinationen Alt + F11 åbnes Visual Basic Editor. Og koden er indsat der.

Private Sub Worksheet_Change (ByVal Target As Range)

    On Error Resume Next

    Hvis ikke skærer (Mål, Range(«Е2:Е9»)) Er Intet Og Target.Cells.Count = 1 Så

        Application.EnableEvents = False

        Hvis Len (Target.Offset (0, 1)) = 0 Så

            Target.Offset (0, 1) = Target

        andet

            Target.End (xlToRight) .Offset (0, 1) = Target

        End If

        Target.ClearContents

        Application.EnableEvents = True

    End If

End Sub 

For at indholdet af cellerne skal vises nedenfor, indsætter vi følgende kode i editoren.

Private Sub Worksheet_Change (ByVal Target As Range)

    On Error Resume Next

    Hvis ikke skærer(mål, rækkevidde(«Í2:К2»)) er intet og mål.celleantal = 1, så

        Application.EnableEvents = False

        Hvis Len (Target.Offset (1, 0)) = 0 Så

            Target.Offset (1, 0) = Target

        andet

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

        End If

        Target.ClearContents

        Application.EnableEvents = True

    End If

End Sub

Og endelig bruges denne kode til at skrive i én celle.

Private Sub Worksheet_Change (ByVal Target As Range)

    On Error Resume Next

    Hvis ikke skærer(Mål, Range(«C2:C5»)) Er Intet Og Target.Cells.Count = 1 Så

        Application.EnableEvents = False

        newVal = Mål

        Ansøgning. Fortryd

        oldval = Mål

        Hvis Len (oldval) <> 0 Og oldval <> newVal Så

            Target = Target & «,» & newVal

        andet

            Mål = nyVal

        End If

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

        Application.EnableEvents = True

    End If

End Sub

Områder kan redigeres.

Hvordan laver man en rulleliste med en søgning?

I dette tilfælde skal du i første omgang bruge en anden type liste. Fanen "Udvikler" åbnes, hvorefter du skal klikke eller trykke (hvis skærmen er touch) på elementet "Indsæt" - "ActiveX". Den har en kombinationsboks. Du vil blive bedt om at tegne denne liste, hvorefter den vil blive tilføjet til dokumentet.

Yderligere konfigureres det gennem egenskaber, hvor et område er angivet i ListFillRange-indstillingen. Cellen, hvor den brugerdefinerede værdi vises, konfigureres ved hjælp af LinkedCell-indstillingen. Dernæst skal du blot skrive de første tegn ned, da programmet automatisk vil foreslå mulige værdier.

Rulleliste med automatisk datasubstitution

Der er også en funktion, som betyder, at data erstattes automatisk, efter at de er føjet til området. Det er nemt at gøre dette:

1. Opret et sæt celler til den fremtidige liste. I vores tilfælde er dette et sæt farver. Vi vælger det.
   

   

2. Dernæst skal den formateres som en tabel. Du skal klikke på knappen af ​​samme navn og vælge bordstilen.
   

   

   

Dernæst skal du bekræfte dette område ved at trykke på knappen "OK".

Vi vælger den resulterende tabel og giver den et navn gennem inputfeltet, der er placeret øverst i kolonne A.

Det er det, der er en tabel, og den kan bruges som grundlag for en drop-down liste, som du skal bruge:

1. Vælg den celle, hvor listen er placeret.
2. Åbn dialogboksen Datavalidering.
   

   

3. Vi sætter datatypen til "List", og som værdier giver vi navnet på tabellen gennem =-tegnet.
   

   

   

Alt, cellen er klar, og navnene på farverne er vist i den, som vi oprindeligt havde brug for. Nu kan du tilføje nye positioner blot ved at skrive dem i en celle placeret lidt lavere umiddelbart efter den sidste.

Dette er fordelen ved tabellen, at rækkevidden automatisk øges, når nye data tilføjes. Derfor er dette den mest bekvemme måde at tilføje en liste på.

Hvordan kopierer man rullelisten?

For at kopiere er det nok at bruge tastekombinationen Ctrl + C og Ctrl + V. Så rullelisten vil blive kopieret sammen med formateringen. For at fjerne formatering skal du bruge en speciel pasta (i kontekstmenuen vises denne mulighed efter kopiering af listen), hvor indstillingen "betingelser for værdier" er indstillet.

Vælg alle celler, der indeholder en rulleliste

For at udføre denne opgave skal du bruge funktionen "Vælg en gruppe af celler" i gruppen "Find og vælg".

Derefter åbnes en dialogboks, hvor du skal vælge punkterne "Alle" og "Disse samme" i menuen "Datavalidering". Det første punkt vælger alle lister, og det andet vælger kun dem, der ligner bestemte.