Hur man gör en rullgardinslista i Excel

Rullgardinslistan är ett otroligt användbart verktyg som kan hjälpa till att göra arbetet med information bekvämare. Det gör det möjligt att innehålla flera värden i en cell samtidigt, som du kan arbeta med, som med alla andra. För att välja den du behöver klickar du bara på pilikonen, varefter en lista med värden uXNUMXbuXNUMXbis visas. Efter att ha valt en specifik fylls cellen automatiskt med den, och formlerna beräknas om baserat på den.

Excel tillhandahåller många olika metoder för att skapa en rullgardinsmeny, och dessutom låter den dig flexibelt anpassa dem. Låt oss analysera dessa metoder mer i detalj.

Processen att skapa listor

För att skapa en popup-meny, klicka på menyalternativen längs vägen "Data" - "Datavalidering". En dialogruta öppnas där du behöver hitta fliken "Parametrar" och klicka på den om den inte har öppnats tidigare. Den har många inställningar, men objektet "Datatyp" är viktigt för oss. Av alla betydelser är "Lista" den rätta.

Antalet metoder för inmatning av information i popup-listan är ganska stort.

1. Oberoende indikation av listelement separerade med semikolon i fältet "Källa" som finns på samma flik i samma dialogruta.
   

   

2. Preliminär angivelse av värden. Fältet Källa innehåller intervallet där den nödvändiga informationen är tillgänglig.
   

   

3. Ange ett namngivet intervall. En metod som upprepar den föregående, men det är bara nödvändigt att preliminärt namnge intervallet.
   

   

Vilken som helst av dessa metoder ger det önskade resultatet. Låt oss titta på metoder för att skapa rullgardinslistor i verkliga situationer.

Baserat på data från listan

Låt oss säga att vi har en tabell som beskriver typerna av olika frukter.

För att skapa en lista i en rullgardinsmeny baserad på denna uppsättning information, måste du göra följande:

1. Välj den cell som är reserverad för den framtida listan.
2. Hitta fliken Data på menyfliksområdet. Där klickar vi på "Verifiera data".
   

   

3. Hitta objektet "Data Type" och ändra värdet till "List".
   

   

4. I fältet som anger alternativet "Källa", ange önskat intervall. Observera att absoluta referenser måste anges så att informationen inte förskjuts vid kopiering av listan.
   

Dessutom finns det en funktion för att generera listor på en gång i mer än en cell. För att uppnå detta bör du välja dem alla och utföra samma steg som beskrivits tidigare. Återigen måste du se till att absoluta referenser skrivs. Om adressen inte har ett dollartecken bredvid kolumn- och radnamnen måste du lägga till dem genom att trycka på F4-tangenten tills $-tecknet står bredvid kolumn- och radnamnen.

Med manuell dataregistrering

I situationen ovan skrevs listan genom att markera önskat intervall. Detta är en bekväm metod, men ibland är det nödvändigt att manuellt registrera data. Detta kommer att göra det möjligt att undvika dubblering av information i arbetsboken.

Anta att vi står inför uppgiften att skapa en lista som innehåller två möjliga val: ja och nej. För att utföra uppgiften är det nödvändigt:

1. Klicka på cellen för listan.
2. Öppna "Data" och hitta avsnittet "Datakontroll" som är bekant för oss.
   

   

3. Återigen, välj typen "Lista".
   

   

4. Här måste du ange "Ja; Nej” som källa. Vi ser att informationen matas in manuellt med semikolon för uppräkning.

Efter att ha klickat på OK har vi följande resultat.

Därefter kommer programmet automatiskt att skapa en rullgardinsmeny i lämplig cell. All information som användaren har angett som objekt i popup-listan. Reglerna för att skapa en lista i flera celler liknar de tidigare, med det enda undantaget att du måste ange informationen manuellt med semikolon.

Skapa en rullgardinslista med OFFSET-funktionen

Förutom den klassiska metoden är det möjligt att använda funktionen FÖRFOGANDEför att skapa rullgardinsmenyer.

Låt oss öppna arket.

För att använda funktionen för rullgardinsmenyn måste du göra följande:

1. Välj den cell av intresse där du vill placera framtidslistan.
2. Öppna fliken "Data" och fönstret "Datavalidering" i följd.
   

   

3. Låtlista". Detta görs på samma sätt som de tidigare exemplen. Slutligen används följande formel: =OFFSET(A$2$;0;0;5). Vi anger det där cellerna som ska användas som argument anges.

Sedan kommer programmet att skapa en meny med en lista över frukter.

Syntaxen för detta är:

=OFFSET(referens,linjeförskjutning,kolumnförskjutning,[höjd],[bredd])

Vi ser att denna funktion har 5 argument. Först ges den första celladressen som ska förskjutas. De följande två argumenten anger hur många rader och kolumner som ska förskjutas. På tal om oss är Height-argumentet 5 eftersom det representerar höjden på listan. 

Dropdown-lista i Excel med datasubstitution (+ med OFFSET-funktionen)

I det givna fallet FÖRFOGANDE tillåts skapa en popup-meny som ligger inom ett fast intervall. Nackdelen med denna metod är att efter att ha lagt till objektet måste du själv redigera formeln.

För att skapa en dynamisk lista med stöd för att ange ny information måste du:

1. Välj cellen av intresse.
2. Expandera fliken "Data" och klicka på "Datavalidering".
3. I fönstret som öppnas väljer du objektet "Lista" igen och anger följande formel som datakälla: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. Klicka på OK.

Denna innehåller en funktion COUNTIF, för att omedelbart avgöra hur många celler som är fyllda (även om det har ett mycket större antal användningsområden, skriver vi det bara här för ett specifikt ändamål).

För att formeln ska fungera normalt är det nödvändigt att spåra om det finns tomma celler på formelns väg. Det borde de inte vara.

Rullgardinslista med data från ett annat ark eller Excel-fil

Den klassiska metoden fungerar inte om du behöver få information från ett annat dokument eller till och med ett ark som finns i samma fil. För detta används funktionen INDIREKT, som låter dig ange i rätt format en länk till en cell som finns i ett annat ark eller i allmänhet – en fil. Du måste göra följande:

1. Aktivera cellen där vi placerar listan.
2. Vi öppnar fönstret vi redan känner. På samma ställe där vi tidigare angett källor för andra intervall anges en formel i formatet =INDIREKT(“[List1.xlsx]Ark1!$A$1:$A$9”). Naturligtvis, istället för List1 och Sheet1, kan du infoga dina bok- respektive arknamn. 

Uppmärksamhet! Filnamnet anges inom hakparenteser. I det här fallet kommer Excel inte att kunna använda filen som för närvarande är stängd som informationskälla.

Det bör också noteras att själva filnamnet är vettigt bara om det nödvändiga dokumentet finns i samma mapp som den där listan kommer att infogas. Om inte, måste du ange adressen till detta dokument i sin helhet.

Skapa beroende dropdowns

En beroendelista är en vars innehåll påverkas av användarens val i en annan lista. Anta att vi har ett öppet bord framför oss som innehåller tre intervall, som var och en har fått ett namn.

Du måste följa dessa steg för att generera listor vars resultat påverkas av alternativet som valts i en annan lista.

1. Skapa 1:a lista med intervallnamn.
   

   

2. Vid källans ingångspunkt markeras de nödvändiga indikatorerna en efter en.
   

   

3. Skapa en 2:a lista beroende på vilken typ av växt personen har valt. Alternativt, om du anger träd i den första listan, kommer informationen i den andra listan att vara "ek, avenbok, kastanj" och mer. Det är nödvändigt att skriva ner formeln i stället för inmatning av datakällan =INDIREKT(E3). E3 – cell som innehåller namnet på området 1.=INDIREKT(E3). E3 – cell med namnet på listan 1.

Nu är allt klart.

Hur väljer man flera värden från en rullgardinslista?

Ibland är det inte möjligt att ge företräde till endast ett värde, så mer än ett måste väljas. Sedan måste du lägga till ett makro i sidkoden. Genom att använda tangentkombinationen Alt + F11 öppnas Visual Basic Editor. Och koden är insatt där.

Private Sub Worksheet_Change (ByVal Target As Range)

    On Error Resume Next

    If Not Intersect(Target, Range(«Е2:Е9»)) är ingenting och Target.Cells.Count = 1 Då

        Application.EnableEvents = False

        Om Len (Target.Offset (0, 1)) = 0 Då

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

        annars

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

        End If

        Target.ClearContents

        Application.EnableEvents = True

    End If

End Sub 

För att innehållet i cellerna ska visas nedan, infogar vi följande kod i editorn.

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 Då

        Application.EnableEvents = False

        Om Len (Target.Offset (1, 0)) = 0 Då

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

        annars

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

        End If

        Target.ClearContents

        Application.EnableEvents = True

    End If

End Sub

Och slutligen används den här koden för att skriva i en cell.

Private Sub Worksheet_Change (ByVal Target As Range)

    On Error Resume Next

    Om inte skära(Target, Range(«C2:C5»)) är ingenting och Target.Cells.Count = 1 Då

        Application.EnableEvents = False

        newVal = Mål

        Application.Ångra

        oldval = Mål

        Om Len (oldval) <> 0 Och oldval <> newVal Då

            Target = Target & «,» & newVal

        annars

            Mål = newVal

        End If

        Om Len (newVal) = 0 Då Target.ClearContents

        Application.EnableEvents = True

    End If

End Sub

Områden är redigerbara.

Hur gör man en rullgardinslista med en sökning?

I det här fallet måste du först använda en annan typ av lista. Fliken "Utvecklare" öppnas, varefter du måste klicka eller knacka (om skärmen är touch) på elementet "Infoga" - "ActiveX". Den har en kombinationslåda. Du kommer att bli ombedd att rita denna lista, varefter den läggs till i dokumentet.

Vidare konfigureras det genom egenskaper, där ett intervall anges i alternativet ListFillRange. Cellen där det användardefinierade värdet visas konfigureras med alternativet LinkedCell. Därefter behöver du bara skriva ner de första tecknen, eftersom programmet automatiskt kommer att föreslå möjliga värden.

Dropdown-lista med automatisk dataersättning

Det finns också en funktion som gör att data ersätts automatiskt efter att de lagts till i intervallet. Det är lätt att göra så här:

1. Skapa en uppsättning celler för framtidslistan. I vårt fall är detta en uppsättning färger. Vi väljer det.
   

   

2. Därefter måste den formateras som en tabell. Du måste klicka på knappen med samma namn och välja tabellstil.
   

   

   

Därefter måste du bekräfta detta område genom att trycka på "OK"-knappen.

Vi väljer den resulterande tabellen och ger den ett namn genom inmatningsfältet som ligger överst i kolumn A.

Det är det, det finns en tabell, och den kan användas som grund för en rullgardinslista, för vilken du behöver:

1. Välj cellen där listan finns.
2. Öppna dialogrutan Datavalidering.
   

   

3. Vi ställer in datatypen till "List", och som värden ger vi tabellens namn genom tecknet =.
   

   

   

Allt, cellen är klar, och namnen på färgerna visas i den, som vi ursprungligen behövde. Nu kan du lägga till nya positioner helt enkelt genom att skriva dem i en cell som ligger lite lägre direkt efter den sista.

Detta är fördelen med tabellen, att intervallet automatiskt ökar när ny data läggs till. Följaktligen är detta det bekvämaste sättet att lägga till en lista.

Hur kopierar man rullgardinsmenyn?

För att kopiera räcker det med att använda tangentkombinationen Ctrl + C och Ctrl + V. Så rullgardinsmenyn kommer att kopieras tillsammans med formateringen. För att ta bort formatering måste du använda en speciell inklistring (i snabbmenyn visas det här alternativet efter kopiering av listan), där alternativet "villkor för värden" är inställt.

Markera alla celler som innehåller en rullgardinslista

För att utföra denna uppgift måste du använda funktionen "Välj en grupp av celler" i gruppen "Sök och välj".

Efter det öppnas en dialogruta där du ska välja objekten "Alla" och "Dessa samma" i menyn "Datavalidering". Det första alternativet väljer alla listor och det andra väljer bara de som liknar vissa.