Hogyan készítsünk legördülő listát Excelben

A legördülő lista egy hihetetlenül hasznos eszköz, amely kényelmesebbé teheti az információkkal való munkát. Lehetővé teszi, hogy egy cellában egyszerre több érték is szerepeljen, amelyekkel ugyanúgy dolgozhat, mint a többivel. A megfelelő kiválasztásához kattintson a nyíl ikonra, amely után megjelenik az uXNUMXbuXNUMXbis értékek listája. Egy adott kiválasztása után a cella automatikusan kitöltésre kerül, és ez alapján történik a képletek újraszámítása.

Az Excel számos különféle módszert biztosít a legördülő menü létrehozásához, és emellett lehetővé teszi azok rugalmas testreszabását. Elemezzük ezeket a módszereket részletesebben.

Listakészítés folyamata

Egy előugró menü létrehozásához kattintson az „Adatok” – „Adatellenőrzés” útvonalon lévő menüpontokra. Megnyílik egy párbeszédpanel, ahol meg kell találnia a „Paraméterek” lapot, és kattintson rá, ha korábban nem nyitotta meg. Rengeteg beállítási lehetőség van, de az „Adattípus” elem fontos számunkra. Az összes jelentés közül a „lista” a megfelelő.

A felugró listába történő információbeviteli módok száma meglehetősen nagy.

1. Pontosvesszővel elválasztott listaelemek független jelzése a „Forrás” mezőben, amely ugyanazon a párbeszédpanelen található.
   

   

2. Értékek előzetes feltüntetése. A Forrás mező tartalmazza azt a tartományt, ahol a szükséges információ elérhető.
   

   

3. Megnevezett tartomány megadása. Olyan módszer, amely megismétli az előzőt, de csak előzetesen kell megnevezni a tartományt.
   

   

Ezen módszerek bármelyike ​​meghozza a kívánt eredményt. Nézzük meg a legördülő listák létrehozásának módszereit valós helyzetekben.

A lista adatai alapján

Tegyük fel, hogy van egy táblázatunk, amely leírja a különböző gyümölcsfajtákat.

Ha egy listát szeretne létrehozni egy legördülő menüben ezen információk alapján, a következőket kell tennie:

1. Válassza ki a jövőbeli listához fenntartott cellát.
2. Keresse meg az Adatok lapot a szalagon. Itt rákattintunk az „Adatok ellenőrzése” gombra.
   

   

3. Keresse meg az „Adattípus” elemet, és állítsa az értéket „Lista”-ra.
   

   

4. A „Forrás” opciót jelölő mezőbe írja be a kívánt tartományt. Felhívjuk figyelmét, hogy abszolút hivatkozásokat kell megadni, hogy a lista másolásakor az információ ne mozduljon el.
   

Ezenkívül van egy funkció, amely egyszerre több cellában is létrehozhat listákat. Ennek eléréséhez válassza ki mindegyiket, és hajtsa végre a korábban leírt lépéseket. Ismét meg kell győződnie arról, hogy abszolút hivatkozások vannak írva. Ha a címben nincs dollárjel az oszlop- és sornevek mellett, akkor ezeket az F4 billentyű lenyomásával kell hozzáadni, amíg a $ jel nem kerül az oszlop- és sornevek mellé.

Kézi adatrögzítéssel

A fenti helyzetben a lista a szükséges tartomány kiemelésével készült. Ez egy kényelmes módszer, de néha szükséges az adatok manuális rögzítése. Ez lehetővé teszi a munkafüzetben található információk megkettőzésének elkerülését.

Tegyük fel, hogy azzal a feladattal állunk szemben, hogy készítsünk egy listát, amely két lehetséges választási lehetőséget tartalmaz: igen és nem. A feladat elvégzéséhez szükséges:

1. Kattintson a cellára a listához.
2. Nyissa meg az „Adatok” menüpontot, és ott találja meg a számunkra ismerős „Adatellenőrzés” részt.
   

   

3. Ismét válassza ki a „Lista” típust.
   

   

4. Itt be kell írnia: „Igen; Nem” forrásként. Azt látjuk, hogy az információkat manuálisan adják meg pontosvesszővel a felsoroláshoz.

Az OK gombra kattintva a következő eredményt kapjuk.

Ezután a program automatikusan létrehoz egy legördülő menüt a megfelelő cellában. Minden olyan információ, amelyet a felhasználó elemként adott meg a felugró listában. A lista több cellában történő létrehozásának szabályai hasonlóak az előzőekhez, azzal az egyetlen kivétellel, hogy az információkat kézzel kell megadni pontosvesszővel.

Legördülő lista készítése az OFFSET funkció segítségével

A klasszikus módszer mellett lehetőség van a függvény használatára is ÁRTALMATLANÍTÁSlegördülő menük létrehozásához.

Nyissuk ki a lapot.

A funkció használatához a legördülő listában a következőket kell tennie:

1. Jelölje ki azt az érdekes cellát, ahová el szeretné helyezni a jövőbeli listát.
2. Nyissa meg egymás után az „Adatok” lapot és az „Adatérvényesítés” ablakot.
   

   

3. Állítsa be a „Lista” lehetőséget. Ez ugyanúgy történik, mint az előző példákban. Végül a következő képletet használjuk: =ELTOLÁS(A$2$;0;0;5). Adjuk meg, ahol megadjuk azokat a cellákat, amelyeket argumentumként használunk.

Ezután a program létrehoz egy menüt a gyümölcsök listájával.

Ennek szintaxisa a következő:

=OFFSET(hivatkozás,vonal_eltolás,oszlop_eltolás,[magasság],[szélesség])

Látjuk, hogy ennek a függvénynek 5 argumentuma van. Először az első eltolandó cellacím kerül megadásra. A következő két argumentum azt határozza meg, hogy hány sort és oszlopot kell eltolni. Ha rólunk beszélünk, a Magasság argumentum 5, mert a lista magasságát jelenti. 

Legördülő lista Excelben adathelyettesítéssel (+ az OFFSET funkcióval)

Az adott esetben ÁRTALMATLANÍTÁS engedélyezett egy előugró menü létrehozása egy meghatározott tartományban. Ennek a módszernek az a hátránya, hogy az elem hozzáadása után magának kell szerkesztenie a képletet.

Új információk bevitelét támogató dinamikus lista létrehozásához a következőket kell tennie:

1. Válassza ki a kívánt cellát.
2. Bontsa ki az „Adatok” lapot, és kattintson az „Adatellenőrzés” elemre.
3. A megnyíló ablakban válassza ki ismét a „Lista” elemet, és adja meg a következő képletet adatforrásként: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. Kattintson az OK gombra.

Ez egy funkciót tartalmaz COUNTIF, hogy azonnal megállapíthassuk, hány cella van kitöltve (bár sokkal nagyobb a felhasználási száma, csak konkrét célból írjuk ide).

Ahhoz, hogy a képlet normálisan működjön, nyomon kell követni, hogy vannak-e üres cellák a képlet útján. Nem szabadna.

Legördülő lista egy másik munkalapból vagy Excel-fájlból származó adatokkal

A klasszikus módszer nem működik, ha egy másik dokumentumból vagy akár egy ugyanabban a fájlban lévő lapról kell információt szerezni. Ehhez a funkciót használják KÖZVETETT, amely lehetővé teszi, hogy megfelelő formátumban írjon be egy hivatkozást egy másik lapon található cellára vagy általában – egy fájlra. A következőket kell tennie:

1. Aktiválja azt a cellát, ahová a listát helyezzük.
2. Kinyitjuk a már ismert ablakot. Ugyanazon a helyen, ahol korábban más tartományok forrásait jeleztük, egy képlet szerepel a formátumban =INDIRECT("[List1.xlsx]Sheet1!$A$1:$A$9"). Természetesen a Lista1 és a Lap1 helyett beillesztheti a könyvek és a lapok nevét. 

Figyelem! A fájl neve szögletes zárójelben van megadva. Ebben az esetben az Excel nem tudja információforrásként használni az éppen bezárt fájlt.

Azt is meg kell jegyezni, hogy magának a fájlnévnek csak akkor van értelme, ha a kívánt dokumentum ugyanabban a mappában található, ahol a lista be lesz illesztve. Ha nem, akkor teljes egészében meg kell adnia ennek a dokumentumnak a címét.

Függő legördülő menük létrehozása

Függő lista az, amelynek tartalmát befolyásolja a felhasználó egy másik listában történő választása. Tegyük fel, hogy van előttünk egy táblázat, amely három tartományt tartalmaz, amelyek mindegyike nevet kapott.

Kövesse ezeket a lépéseket olyan listák létrehozásához, amelyek eredményét egy másik listában kiválasztott opció befolyásolja.

1. Hozzon létre 1. listát tartománynevekkel.
   

   

2. A forrás belépési pontján egyenként kiemelik a szükséges mutatókat.
   

   

3. Hozzon létre egy 2. listát attól függően, hogy a személy milyen növényt választott. Alternatív megoldásként, ha az első listában fákat ad meg, akkor a második listában a „tölgy, gyertyán, gesztenye” és azon túlmenő információk szerepelnek. A képletet az adatforrás beviteli helyére kell felírni =KÖZVETETT(E3). E3 – a tartomány nevét tartalmazó cella 1.=INDIRECT(E3). E3 – cella a lista nevével 1.

Most minden készen áll.

Hogyan válasszunk ki több értéket egy legördülő listából?

Néha nem lehet csak egy értéket előnyben részesíteni, ezért egynél többet kell kiválasztani. Ezután hozzá kell adnia egy makrót az oldal kódjához. Az Alt + F11 billentyűkombináció használatával megnyitja a Visual Basic Editort. És a kód be van írva.

Private Sub Worksheet_Change (ByVal Target As Range)

    On Error Resume Next

    Ha nem metszik(Cél, Tartomány(«Е2:Е9»)) Semmi és Cél.Cellák.Szám = 1 Akkor

        Application.EnableEvents = Hamis

        Ha Len (Cél.Eltolás (0, 1)) = 0 Akkor

            Target.Offset (0, 1) = Cél

        más

            Target.End (xlToRight) .Offset (0, 1) = Cél

        Ha véget

        Target.ClearContents

        Application.EnableEvents = Igaz

    Ha véget

End Sub 

Annak érdekében, hogy a cellák tartalma az alábbiakban megjelenjen, a következő kódot szúrjuk be a szerkesztőbe.

Private Sub Worksheet_Change (ByVal Target As Range)

    On Error Resume Next

    Ha nem metszik(Cél, Tartomány(«Н2:К2»)) Semmi és Cél.Cellák.Szám = 1 Akkor

        Application.EnableEvents = Hamis

        Ha Len (Cél.Eltolás (1, 0)) = 0 Akkor

            Target.Offset (1, 0) = Cél

        más

            Target.End (xlDown) .Offset (1, 0) = Cél

        Ha véget

        Target.ClearContents

        Application.EnableEvents = Igaz

    Ha véget

End Sub

És végül ez a kód egy cellába való írásra szolgál.

Private Sub Worksheet_Change (ByVal Target As Range)

    On Error Resume Next

    Ha nem metszik(Cél, Tartomány(«C2:C5»)) Semmi és Cél.Cellák.Szám = 1 Akkor

        Application.EnableEvents = Hamis

        newVal = Cél

        Alkalmazás.Visszavonás

        oldval = Cél

        Ha Len (oldval) <> 0 És oldval <> newVal Akkor

            Cél = Target & «,» & newVal

        más

            Cél = newVal

        Ha véget

        Ha Len (newVal) = 0, akkor Target.ClearContents

        Application.EnableEvents = Igaz

    Ha véget

End Sub

A tartományok szerkeszthetők.

Hogyan készítsünk legördülő listát kereséssel?

Ebben az esetben először más típusú listát kell használnia. Megnyílik a „Fejlesztő” fül, amely után kattintson vagy érintse meg (ha a képernyő érintés) az „Insert” – „ActiveX” elemre. Kombinált doboza van. A rendszer felkéri, hogy rajzolja meg ezt a listát, majd hozzáadja a dokumentumhoz.

Továbbá a tulajdonságokon keresztül konfigurálható, ahol egy tartomány a ListFillRange opcióban van megadva. A cella, ahol a felhasználó által megadott érték megjelenik, a LinkedCell beállítással van konfigurálva. Ezután már csak az első karaktereket kell felírni, mivel a program automatikusan javasolja a lehetséges értékeket.

Legördülő lista automatikus adatpótlással

Van egy olyan funkció is, amely automatikusan helyettesíti az adatokat, miután hozzáadták őket a tartományhoz. Ezt könnyű megtenni:

1. Hozzon létre egy cellakészletet a jövőbeli listához. Esetünkben ez egy színkészlet. Kiválasztjuk.
   

   

2. Ezután táblázatként kell formázni. Kattintson az azonos nevű gombra, és válassza ki a táblázat stílusát.
   

   

   

Ezután meg kell erősítenie ezt a tartományt az „OK” gomb megnyomásával.

Kiválasztjuk az eredményül kapott táblázatot, és az A oszlop tetején található beviteli mezőben nevet adunk neki.

Ez az, van egy táblázat, és ez egy legördülő lista alapjául szolgálhat, amihez szüksége van:

1. Válassza ki a cellát, ahol a lista található.
2. Nyissa meg az Adatellenőrzés párbeszédpanelt.
   

   

3. Az adattípust „Lista”-ra állítjuk, és értékként a táblázat nevét adjuk meg az = jellel.
   

   

   

Minden, a cella készen áll, és a színek nevei is megjelennek benne, ahogy eredetileg kellett. Mostantól egyszerűen hozzáadhat új pozíciókat egy, közvetlenül az utolsó után kissé lejjebb található cellába.

Ez a táblázat előnye, hogy a tartomány automatikusan növekszik új adatok hozzáadásával. Ennek megfelelően ez a legkényelmesebb módja a lista hozzáadásának.

Hogyan lehet legördülő listát másolni?

A másoláshoz elegendő a Ctrl + C és a Ctrl + V billentyűkombinációt használni. Így a legördülő lista a formázással együtt másolásra kerül. A formázás eltávolításához speciális beillesztést kell használni (a helyi menüben ez a lehetőség a lista másolása után jelenik meg), ahol az „értékekre vonatkozó feltételek” lehetőség van beállítva.

Jelölje ki az összes legördülő listát tartalmazó cellát

A feladat végrehajtásához a „Cellacsoport kiválasztása” funkciót kell használnia a „Keresés és kijelölés” csoportban.

Ezt követően megnyílik egy párbeszédpanel, ahol az „Adatellenőrzés” menüben ki kell választani az „Összes” és „Ezek ugyanazok” elemet. Az első elem az összes listát kijelöli, a második pedig csak azokat, amelyek hasonlóak bizonyos listákhoz.