Hur gör man en komplex rullgardinslista i excel?
Om du fyller i en tabell i Excel och uppgifterna i en kolumn ibland kan upprepas, till exempel namnet på en produkt eller namnet på en anställd, så för att inte ange önskad parameter varje gång är lättare och lättare att skapa en rullgardinslista en gång och välja ett värde från den.
I artikeln kommer vi att titta på hur man gör rullgardinslistor av olika typer i ett Excel-kalkylblad.
Skapa en enkel rullgardinslista
För att göra detta, i celler A1: A7 anger vi data som kommer att visas i listan. Välj nu cellen där vi ska skapa en rullgardinslista - B2.
Gå till fliken "Data" och klicka på knappen "Datavalidering".
På fliken "Alternativ", i fältet "Datatyp", välj "Lista". Du kan ange värden i fältet Källa på olika sätt:
1 - ange värdena för listan manuellt, separerade med semikolon;
2 - ange intervallet av celler där data för rullgardinsmenyn anges;
3 - välj cellerna med namn, högerklicka på dem och välj "Tilldela ett namn" från menyn.
Välj cell B2 och skriv "=" i fältet "Källa" och skriv sedan det skapade namnet.
Därför har vi skapat en enkel rullgardinslista i Excel.
Om du har en rubrik för en kolumn och du behöver fylla i varje rad med värden, välj då inte en cell, utan ett cellintervall - B2:B9. Då kommer det att vara möjligt att välja önskat värde i varje cell från rullgardinsmenyn.
Lägga till värden till rullgardinsmenyn - Dynamisk lista
I det här fallet kommer vi att lägga till värden till det önskade intervallet, och de kommer automatiskt att läggas till i rullgardinsmenyn.
Välj cellintervallet - D1:D8, klicka sedan på "Formatera som tabell" på fliken "Hem" och välj valfri stil.
Vi bekräftar platsen för data och sätter en bock i fältet "Tabell med rubriker".
Överst skriver vi tabellens titel - "Anställda", och fyller den med data.
Välj cellen som ska innehålla rullgardinsmenyn och klicka på knappen "Datavalidering". I nästa fönster, i fältet "Källa", skriv följande: =INDIREKT("Tabell1"). Jag har ett bord på ett ark, så jag skriver "Tabell1", om det finns ett andra - "Tabell2" och så vidare.
Låt oss nu lägga till ett nytt anställdsnamn till vår lista: Ira. Det dök upp i rullgardinsmenyn. Om vi tar bort något namn från tabellen kommer det också att tas bort från listan.
Rullgardinslista med värden från ett annat ark
Om tabellen med rullgardinslistor finns på ett ark, och data för dessa listor finns på ett annat, så kommer den här funktionen att hjälpa oss mycket.
På blad 2, välj en cell eller ett cellområde och klicka sedan på knappen "Datavalidering".
Gå till blad 1, placera markören i fältet "Källa" och välj önskat cellområde.
Nu kan du lägga till namn på blad 1, de kommer att läggas till i rullgardinslistorna på blad 2.
Skapa beroende listor
Låt oss säga att vi har tre intervall: förnamn, efternamn och mellannamn på anställda. För varje måste du tilldela ett namn. Vi väljer cellerna i detta intervall, det kan också vara tomt - med tiden kommer det att vara möjligt att lägga till data till dem som kommer att visas i rullgardinsmenyn. Vi klickar på dem med höger musknapp och väljer "Tilldela ett namn" från listan.
Den första heter "Namn", den andra - "Efternamn", den tredje - "Fader".
Låt oss skapa ett annat område där de tilldelade namnen kommer att skrivas. Låt oss kalla det "Anställda".
Vi gör den första rullgardinsmenyn, som kommer att bestå av namnen på intervallen. Välj cell E1 och välj Datavalidering på fliken Data.
I fältet "Datatyp", välj "Lista", i källfältet - ange antingen "=Anställda", eller välj ett cellområde som har fått ett namn.
Den första rullgardinsmenyn har skapats. Nu i cell F2 kommer vi att skapa en andra lista, som bör bero på den första. Om vi väljer "Förnamn" i det första, kommer listan med efternamn att visas i det andra, om vi väljer "Efternamn" - en lista med efternamn.
Markera cellen och klicka på knappen Datavalidering. I fältet "Datatyp", välj "Lista", i källfältet anger du följande: =INDIREKT($E$1). Här är E1 cellen med den första rullgardinsmenyn.
Enligt denna princip kan du skapa beroende listor.
Om du i framtiden kommer att behöva ange värdena i intervallet som namnet ges, till exempel "Efternamn". Gå till fliken "Formler" och klicka på "Namnhanterare". Välj nu "Efternamn" i intervallets namn och skriv C10 längst ned i stället för den sista cellen C3. Klicka på bockmarkeringen. Därefter kommer intervallet att öka, och det kommer att vara möjligt att lägga till data till det, som automatiskt visas i rullgardinsmenyn.
Nu vet du hur man gör en rullgardinslista i Excel.
Hur man skapar en rullgardinslista som består av flera celler samtidigt (låt oss säga att namnet är med en kostnad)
Tack, allt fungerade bra.
Rullgardinslistan med värden från ett annat ark fungerar inte, eftersom fönstret när datakontrollen är öppen inte tillåter arbete med andra fönster, särskilt med ett annat ark!
Dependent dropdown låter dig göra ett trick som ofta hyllas av användare av Excel-mallar. Ett knep som gör jobbet enklare och snabbare. Ett knep som gör dina former bekväma och behagliga.
Ett exempel på att skapa en beroende rullgardinslista i en Excel-cell
Ett exempel på att använda en beroende rullgardinslista för att skapa ett bekvämt formulär för att fylla i dokument med vilka säljare beställt varor. Från hela sortimentet fick de välja de produkter som de skulle sälja.
Varje säljare identifierade först en produktgrupp och sedan en specifik produkt från denna grupp. Formuläret måste innehålla hela gruppens namn och ett specifikt artikelindex. Eftersom att skriva detta för hand skulle vara för tidskrävande (och irriterande) kom jag på en mycket snabb och enkel lösning - 2 beroende rullgardinsmenyer.
Den första var en lista över alla produktkategorier, den andra var en lista över alla produkter i den valda kategorin. Därför skapade jag en dropdown-lista beroende på valet som gjordes i föregående lista (här hittar du material om hur man skapar två beroende dropdown-listor).
Användaren av hembudgetmallen vill få samma resultat, där kategori och underkategori av utgifter behövs. Ett exempel på data finns i figuren nedan:
Så, till exempel, om vi väljer kategorin Underhållning, bör listan över underkategorier vara: Bio, Teater, Pool. En mycket snabb lösning om du vill analysera mer detaljerad information i din hembudget.
Lista kategorier och underkategorier i Excel-beroende rullgardinsmeny
Jag erkänner att i min föreslagna version av hembudgeten är jag begränsad till endast en kategori, eftersom en sådan uppdelning av utgifter för mig är tillräckligt (namnet på utgifter / inkomster betraktas som en underkategori). Men om du behöver underkategorisera dem, är metoden jag beskriver nedan idealisk. Använd gärna!
Och slutresultatet ser ut så här:
Beroende rullgardinslista med underkategorier
För att uppnå detta måste vi göra en lite annorlunda datatabell än om vi skulle skapa en enda rullgardinslista. Tabellen ska se ut så här (intervall G2:H15):
Arbetande källa Excel-kalkylblad
I den här tabellen måste du ange en kategori och bredvid dess underkategorier. Kategorinamnet måste upprepas lika många gånger som det finns underkategorier. Det är mycket viktigt att uppgifterna sorteras efter kategorikolumnen. Detta kommer att vara oerhört viktigt när vi skriver formeln senare.
Man skulle också kunna använda tabellerna från den första bilden. Naturligtvis skulle formlerna vara annorlunda. En gång till och med hittade jag en sådan lösning på nätet, men jag gillade den inte, eftersom det fanns en fast längd på listan: vilket innebär att ibland innehöll listan tomma fält, och ibland visade den inte alla element. Naturligtvis kan jag undvika denna begränsning, men jag erkänner att jag gillar min lösning bättre, så jag återvände inte till den lösningen.
Okej då. Nu, en efter en, kommer jag att beskriva stegen för att skapa en beroende dropdown-lista.
1. Namn på cellintervall
Detta är ett valfritt steg, utan det borde vi kunna hantera detta utan problem. Däremot gillar jag att använda namn eftersom de gör det mycket lättare att både skriva och läsa formeln.
Låt oss namnge de två områdena. Lista över alla kategorier och arbetslista med kategorier. Dessa kommer att vara intervallen A3:A5 (listan över kategorier i den gröna tabellen i den första bilden) och G3:G15 (listan över dubbletter av kategorier i det lila kalkylbladet).
För att namnge en lista med kategorier:
- Välj område A3:A5.
- I namnfältet (fältet till vänster om formelfältet) anger du namnet "Kategori".
- Bekräfta med Enter-tangenten.
Gör samma sak för kategoriarbetslistomfånget G3:G15, som du kan kalla WorkList. Vi kommer att använda detta intervall i formeln.
2. Skapa en rullgardinslista för en kategori
Det blir enkelt:
- Välj den cell där du vill placera listan. I mitt fall är det A12.
- Från DATA-menyn väljer du verktyget Datavalidering. Fönstret Validera indatavärden visas.
- Välj "Lista" som datatyp.
- För källan anger du: =Kategori (bild nedan).
- Bekräfta med OK.
Resultatet är följande:
Dropdown-lista för kategori.
3. Skapa en beroende dropdown-lista för en underkategori
Nu ska det bli kul. Vi vet hur man skapar listor - vi gjorde det bara för en kategori. Bara en fråga: "Hur säger jag till Excel att bara välja de värden som är för en viss kategori?" Som du säkert kan gissa kommer jag att använda ett kalkylblad här och, naturligtvis, formler.
Låt oss börja med det vi redan vet, vilket är att skapa en rullgardinslista i cell B12. Så välj den cellen och klicka på Data/Datavalidering och ställ in datatypen på Lista.
I listkällan anger du följande formel:
Visa fönstret "Kontrollera inmatningsvärden":
Validering av ingångsvärden för en underkategori i en beroende dropdown-lista
Som du kan se är hela beroendelistans trick att använda OFFSET-funktionen. Tja, nästan alla. Funktionerna MATCH och COUNTIF hjälper henne. OFFSET-funktionen låter dig definiera intervall dynamiskt. Först definierar vi cellen från vilken intervallförskjutningen ska börja, och i efterföljande argument definierar vi dess storlek.
I vårt exempel kommer intervallet att flyttas över underkategorikolumnen i kalkylbladet (G2:H15). Vi kommer att börja flytta från cell H2, som också är det första argumentet för vår funktion. I formeln skrivs cell H2 som en absolut referens, eftersom jag antar att vi kommer att använda rullgardinsmenyn i många celler.
Eftersom kalkylbladet är sorterat efter kategori, kommer intervallet som ska vara källan för rullgardinsmenyn att börja där den valda kategorin först förekommer. För livsmedelskategorin vill vi till exempel visa intervallet H6:H11, för kategorin Transport, intervallet H12:H15 osv. Observera att vi rör oss längs kolumn H hela tiden, och det enda som ändringar är början av intervallet och dess höjd (dvs. antalet element i listan).
Början av intervallet kommer att flyttas i förhållande till cell H2 med lika många celler nedåt (i antal) som positionsnumret för den först förekommande kategorin i kategorikolumnen. Det blir lättare att förstå med ett exempel: området för livsmedelskategorin flyttas 4 celler ner i förhållande till cell H2 (börjar från 4 celler från H2). I den fjärde cellen i underkategorikolumnen (exklusive rubriken, eftersom det är ett intervall som heter WorkList), finns ordet Food (dess första förekomst). Vi använder detta faktum för att faktiskt bestämma början av intervallet. MATCH-funktionen kommer att tjäna oss för detta (infört som det andra argumentet för OFFSET-funktionen):
Höjden på området bestäms av COUNTIF-funktionen. Hon räknar alla förekomster i kategorin, det vill säga ordet Nutrition. Hur många gånger detta ord förekommer, hur många positioner kommer att finnas i vårt sortiment. Antalet positioner i ett intervall är dess höjd. Här är funktionen:
Naturligtvis är båda funktionerna redan inkluderade i OFFSET-funktionen som beskrivs ovan. Observera också att det i både MATCH och COUNTIF finns en referens till ett intervall som kallas WorkList. Som jag nämnde tidigare behöver du inte använda intervallnamn, du kan bara skriva $H3: $H15. Men att använda intervallnamn i en formel gör det enklare och lättare att läsa.
Det är allt:
Nedladdning av beroende rullist exempel i Excel
En formel, ja, inte så enkel, men den underlättar arbetet och skyddar mot inmatningsfel!