Hur man skapar en nedrullningslista i Excel (den enda guiden du behöver)

En listruta är ett utmärkt sätt att ge användaren ett alternativ att välja från en fördefinierad lista.

Den kan användas när du får en användare att fylla i ett formulär, eller när du skapar interaktiva Excel -instrumentpaneler.

Listrutor är ganska vanliga på webbplatser/appar och är mycket intuitiva för användaren.

Titta på video - Skapa en nedrullningsbar lista i Excel

I den här självstudien lär du dig hur du skapar en rullgardinsmeny i Excel (det tar bara några sekunder att göra detta) tillsammans med alla fantastiska saker du kan göra med det.

Hur man skapar en nedrullningslista i Excel

I det här avsnittet lär du dig exakta steg för att skapa en rullgardinsmeny i Excel:

1. Använda data från celler.
2. Ange data manuellt.
3. Med hjälp av OFFSET -formeln.

#1 Använda data från celler

Låt oss säga att du har en lista med artiklar som visas nedan:

Här är stegen för att skapa en Excel -rullgardinsmeny:

1. Välj en cell där du vill skapa rullgardinsmenyn.
2. Gå till Data -> Dataverktyg -> Datavalidering.
3. I dialogrutan Datavalidering, på fliken Inställningar, välj Lista som valideringskriterier.
   • Så snart du väljer Lista visas källfältet.
4. I källfältet, ange = $ A $ 2: $ A $ 6, eller klicka helt enkelt i källfältet och markera cellerna med musen och klicka på OK. Detta kommer att infoga en rullgardinslista i cell C2.
   • Se till att rullgardinsmenyn i cellen är markerad (som är markerat som standard). Om det här alternativet inte är markerat visar cellen ingen rullgardinsmeny, men du kan manuellt ange värdena i listan.

Notera: Om du vill skapa rullgardinsmenyer i flera celler på en gång väljer du alla celler där du vill skapa den och följer sedan stegen ovan. Se till att cellreferenserna är absoluta (t.ex. $ A $ 2) och inte relativa (till exempel A2 eller A $ 2 eller $ A2).

#2 Genom att ange data manuellt

I exemplet ovan används cellreferenser i källfältet. Du kan också lägga till objekt direkt genom att ange det manuellt i källfältet.

Låt oss till exempel säga att du vill visa två alternativ, Ja och Nej, i rullgardinsmenyn i en cell. Så här kan du ange det direkt i källfältet för datavalidering:

• Välj en cell där du vill skapa rullgardinsmenyn (cell C2 i det här exemplet).
• Gå till Data -> Dataverktyg -> Datavalidering.
• I dialogrutan Datavalidering, på fliken Inställningar, välj Lista som valideringskriterier.
  • Så snart du väljer Lista visas källfältet.
• Ange Ja, Nej i källfältet
  • Se till att rullgardinsmenyn i cellen är markerad.
• Klicka på OK.

Detta skapar en listruta i den markerade cellen. Alla objekt som anges i källfältet, åtskilda med ett komma, listas på olika rader i rullgardinsmenyn.

Alla objekt som anges i källfältet, separerade med ett komma, visas på olika rader i rullgardinsmenyn.

Notera: Om du vill skapa rullgardinsmenyer i flera celler på en gång väljer du alla celler där du vill skapa den och följer sedan stegen ovan.

#3 Använda Excel -formler

Förutom att välja från celler och ange data manuellt kan du också använda en formel i källfältet för att skapa en Excel -rullgardinsmeny.

Varje formel som returnerar en lista med värden kan användas för att skapa en rullgardinslista i Excel.

Anta till exempel att du har datauppsättningen enligt nedan:

Här är stegen för att skapa en Excel -rullgardinsmeny med OFFSET -funktionen:

• Välj en cell där du vill skapa rullgardinsmenyn (cell C2 i det här exemplet).
• Gå till Data -> Dataverktyg -> Datavalidering.
• I dialogrutan Datavalidering, på fliken Inställningar, välj Lista som valideringskriterier.
  • Så snart du väljer Lista visas källfältet.
• I källfältet anger du följande formel: = OFFSET ($ A $ 2,0,0,5)
  • Se till att rullgardinsmenyn i cellen är markerad.
• Klicka på OK.

Detta skapar en rullgardinslista som listar alla fruktnamnen (som visas nedan).

Notera: Om du vill skapa en rullgardinsmeny i flera celler på en gång väljer du alla celler där du vill skapa den och följer sedan stegen ovan. Se till att cellreferenserna är absoluta (t.ex. $ A $ 2) och inte relativa (till exempel A2 eller A $ 2 eller $ A2).

Hur fungerar denna formel ??

I ovanstående fall använde vi en OFFSET -funktion för att skapa rullgardinsmenyn. Det returnerar en lista med objekt från ra

Den returnerar en lista med artiklar från intervallet A2: A6.

Här är syntaxen för OFFSET -funktionen: = OFFSET (referens, rader, kolumner, [höjd], [bredd])

Det krävs fem argument, där vi angav referensen som A2 (listans utgångspunkt). Rader/Cols anges som 0 eftersom vi inte vill kompensera referenscellen. Höjd anges som 5 eftersom det finns fem element i listan.

Nu, när du använder den här formeln, returnerar den en matris som har listan över de fem frukterna i A2: A6. Observera att om du anger formeln i en cell, markerar du den och trycker på F9, ser du att den returnerar en rad med fruktnamnen.

Skapa en dynamisk nedrullningslista i Excel (med hjälp av OFFSET)

Ovanstående teknik för att använda en formel för att skapa en rullgardinslista kan utökas till att skapa en dynamisk rullgardinslista också. Om du använder OFFSET -funktionen, som visas ovan, även om du lägger till fler objekt i listan, skulle rullgardinsmenyn inte uppdateras automatiskt. Du måste uppdatera den manuellt varje gång du ändrar listan.

Här är ett sätt att göra det dynamiskt (och det är inget annat än en liten tweak i formeln):

• Välj en cell där du vill skapa rullgardinsmenyn (cell C2 i det här exemplet).
• Gå till Data -> Dataverktyg -> Datavalidering.
• I dialogrutan Datavalidering, på fliken Inställningar, välj Lista som valideringskriterier. Så snart du väljer Lista visas källfältet.
• Ange följande formel i källfältet: = OFFSET ($ A $ 2,0,0, COUNTIF ($ A $ 2: $ A $ 100, ””))
• Se till att rullgardinsmenyn i cellen är markerad.
• Klicka på OK.

I denna formel har jag ersatt argumentet 5 med COUNTIF ($ A $ 2: $ A $ 100, ””).

COUNTIF-funktionen räknar de icke-tomma cellerna i intervallet A2: A100. Därför justerar OFFSET-funktionen sig för att inkludera alla icke-tomma celler.

Notera:

• För att detta ska fungera får det INTE finnas några tomma celler mellan cellerna som är fyllda.
• Om du vill skapa en rullgardinsmeny i flera celler på en gång väljer du alla celler där du vill skapa den och följer sedan stegen ovan. Se till att cellreferenserna är absoluta (t.ex. $ A $ 2) och inte relativa (t.ex. A2 eller A $ 2 eller $ A2).

Kopiera klistra ned rullgardinslistor i Excel

Du kan kopiera klistra in cellerna med datavalidering till andra celler, och det kommer också att kopiera datavalideringen.

Om du till exempel har en rullgardinsmeny i cell C2 och du vill tillämpa den på C3: C6, kopierar du bara cellen C2 och klistrar in den i C3: C6. Detta kommer att kopiera listrutan och göra den tillgänglig i C3: C6 (tillsammans med rullgardinsmenyn kommer den också att kopiera formateringen).

Om du bara vill kopiera rullgardinsmenyn och inte formateringen, här är stegen:

• Kopiera cellen som har rullgardinsmenyn.
• Markera cellerna där du vill kopiera rullgardinsmenyn.
• Gå till Hem -> Klistra in -> Klistra in special.
• I dialogrutan Klistra in special väljer du Validering i alternativ för klistra in.
• Klicka på OK.

Detta kommer bara att kopiera rullgardinsmenyn och inte formateringen av den kopierade cellen.

Var försiktig när du arbetar med Excel -rullgardinsmenyn

Du måste vara försiktig när du arbetar med rullgardinslistor i Excel.

När du kopierar en cell (som inte innehåller en rullgardinsmeny) över en cell som innehåller en rullgardinsmeny, förloras rullgardinsmenyn.

Det värsta med detta är att Excel inte kommer att visa någon varning eller uppmaning för att låta användaren veta att en rullgardinsmeny kommer att skrivas över.

Så här väljer du alla celler som har en nedrullningsbar lista

Ibland är det svårt att veta vilka celler som innehåller rullgardinsmenyn.

Därför är det vettigt att markera dessa celler genom att antingen ge den en distinkt kant eller en bakgrundsfärg.

Istället för att manuellt kontrollera alla celler finns det ett snabbt sätt att välja alla celler som har listrutor (eller någon datavalideringsregel) i den.

• Gå till Hem -> Hitta & Välj -> Gå till Special.
• I dialogrutan Gå till special väljer du Datavalidering
  • Datavalidering har två alternativ: Alla och samma. Alla skulle välja alla celler som har en datavalideringsregel tillämpad på den. Samma skulle bara välja de celler som har samma datavalideringsregel som för den aktiva cellen.
• Klicka på OK.

Detta skulle omedelbart välja alla celler som har en datavalideringsregel tillämpad på den (detta inkluderar även rullgardinslistor).

Nu kan du helt enkelt formatera cellerna (ge en kant eller en bakgrundsfärg) så att de syns visuellt och du inte råkar kopiera en annan cell på den av misstag.

Här är en annan teknik av Jon Acampora som du kan använda för att alltid hålla rullgardinsmenyn synlig. Du kan också se några sätt att göra detta i den här videon av Mr. Excel.

Skapa en beroende / villkorlig Excel -rullgardinsmeny

Här är en video om hur du skapar en beroende listruta i Excel.

Om du föredrar att läsa framför att titta på en video, fortsätt läsa.

Ibland kan du ha mer än en listruta och du vill att objekten som visas i den andra rullgardinsmenyn beror på vad användaren valde i den första listrutan.

Dessa kallas beroende eller villkorade listrutor.

Nedan är ett exempel på en villkorlig/beroende rullgardinsmeny:

I exemplet ovan, när objekten som anges i "Drop Down 2" är beroende av valet i "Drop Down 1".

Låt oss nu se hur man skapar detta.

Här är stegen för att skapa en beroende / villkorad listruta i Excel:

• Välj den cell där du vill ha den första (huvud) listrutan.
• Gå till Data -> Datavalidering. Detta öppnar dialogrutan för datavalidering.
• I listan för datavalidering, på fliken Inställningar, välj Lista.
• I fältet Källa anger du intervallet som innehåller de objekt som ska visas i den första listrutan.
• Klicka på OK. Detta skapar Drop Down 1.
• Välj hela datamängden (A1: B6 i detta exempel).
• Gå till Formler -> Definierade namn -> Skapa från urval (eller så kan du använda tangentbordsgenvägen Ctrl + Skift + F3).
• I dialogrutan "Skapa namngivna ur markering" markerar du alternativet Översta raden och avmarkerar alla andra. Genom att göra detta skapas två namnintervall ("Frukt" och "Grönsaker"). Frukt med namnet intervall hänvisar till alla frukter i listan och grönsaker som heter range refererar till alla grönsaker i listan.
• Klicka på OK.
• Välj den cell där du vill ha listrutan Beroende/villkorad (E3 i detta exempel).
• Gå till Data -> Datavalidering.
• I dialogrutan Datavalidering, på fliken inställningar, se till att listan är vald.
• I källfältet anger du formeln = INDIRECT (D3). Här är D3 den cell som innehåller den viktigaste rullgardinsmenyn.
• Klicka på OK.

När du gör valet i rullgardinsmenyn 1 uppdateras alternativen som listas i nedrullningslista 2 automatiskt.

Ladda ner exempelfilen

Hur fungerar detta? - Den villkorade listrutan (i cell E3) hänvisar till = INDIRECT (D3). Det betyder att när du väljer "Frukt" i cell D3 hänvisar rullgardinsmenyn i E3 till det namngivna intervallet "Frukt" (via INDIRECT -funktionen) och listar därför alla objekt i den kategorin.

Viktig anmärkning När du arbetar med villkorade rullgardinslistor i Excel:

• När du har gjort valet och sedan ändrar den överordnade rullgardinsmenyn ändras inte den beroende rullgardinsmenyn och skulle därför vara en felaktig post. Till exempel, om du väljer USA som land och sedan väljer Florida som staten och sedan går tillbaka och ändrar landet till Indien, skulle staten förbli som Florida. Här är en bra handledning av Debra om att rensa beroende (villkorade) listrutor i Excel när valet ändras.
• Om huvudkategorin är mer än ett ord (till exempel 'Seasonal Fruits' istället för 'Fruits'), måste du använda formeln = INDIRECT (SUBSTITUTE (D3, ”“, ”_”)), istället för enkel INDIRECT -funktion som visas ovan. Anledningen till detta är att Excel inte tillåter mellanslag i namngivna intervall. Så när du skapar ett namngivet intervall med mer än ett ord infogar Excel automatiskt en understrykning mellan orden. Så "Seasonal Fruits" med namnet "Seasonal_Fruits". Om du använder funktionen SUBSTITUTE inom INDIRECT -funktionen ser du till att mellanslag är omvandlas till understrykningar.