Videó megtekintése - Függő legördülő lista létrehozása Excelben
Az Excel legördülő lista hasznos funkció adatbeviteli űrlapok vagy Excel irányítópultok létrehozásakor.
A cella legördülő listájaként megjeleníti az elemek listáját, és a felhasználó választhat a legördülő menüből. Ez akkor lehet hasznos, ha van egy listája a nevekről, termékekről vagy régiókról, amelyeket gyakran meg kell adnia egy cellakészletbe.
Az alábbiakban egy példa az Excel legördülő listára:
A fenti példában az A2: A6 elemeit használtam egy legördülő menü létrehozásához a C3-ban.
Olvas: Itt található egy részletes útmutató az Excel legördülő lista létrehozásáról.
Néha azonban előfordulhat, hogy egynél több legördülő listát szeretne használni az Excel programban, így a második legördülő listában elérhető elemek az első legördülő listában elvégzett kiválasztástól függenek.
Ezeket függő legördülő listáknak hívják az Excelben.
Az alábbiakban egy példa látható, hogy mit értek az Excel legördülő listája alatt:
Láthatja, hogy a 2. legördülő menü opciói az 1. legördülő menüben elvégzett választástól függenek. Ha az 1. legördülő menüben a „Gyümölcsök” lehetőséget választom, megjelenik a gyümölcs neve, de ha a Zöldségek lehetőséget választom az 1. legördülő menüben, akkor am a zöldségnevek jelennek meg a 2. legördülő menüben.
Ezt feltételes vagy függő legördülő listának hívják az Excelben.
Függő legördülő lista létrehozása Excelben
Az alábbi lépésekkel hozhat létre függő legördülő listát az Excelben:
- Válassza ki azt a cellát, ahol az első (fő) legördülő listát szeretné megjeleníteni.
- Lépjen az Adatok -> Adatellenőrzés menüpontra. Ezzel megnyílik az adatellenőrző párbeszédpanel.
- Az adatok érvényesítése párbeszédpanelen, a beállítások lapon válassza a Lista lehetőséget.
- A Forrás mezőben adja meg azt a tartományt, amely tartalmazza az első legördülő listában megjeleníteni kívánt elemeket.
- Kattintson az OK gombra. Ezzel létrejön a legördülő 1.
- Válassza ki a teljes adathalmazt (ebben a példában A1: B6).
- Lépjen a Képletek -> Meghatározott nevek -> Létrehozás a kijelölésből menüpontra (vagy használhatja a Control + Shift + F3 billentyűkombinációt).
- A „Név létrehozása a kijelölésből” párbeszédpanelen jelölje be a Felső sor opciót, és törölje a többiek jelölését. Ezzel 2 névtartományt hoz létre („Gyümölcsök” és „Zöldségek”). A tartomány nevű gyümölcsök a listában szereplő összes gyümölcsre vonatkoznak, a zöldségfélék nevű tartomány pedig a listában szereplő összes zöldségre.
- Kattintson az OK gombra.
- Válassza ki azt a cellát, ahol a Függő/Feltételes legördülő listát szeretné megjeleníteni (ebben a példában E3).
- Lépjen az Adatok -> Adatellenőrzés menüpontra.
- Az Adatok érvényesítése párbeszédpanelen, a beállítások lapon ellenőrizze, hogy a Lista be van jelölve.
- A Forrás mezőbe írja be a = KÖZVETLEN (D3) képletet. Itt a D3 a fő legördülő menüt tartalmazó cella.
- Kattintson az OK gombra.
Most, amikor az 1. legördülő menüben választ, a 2. legördülő listában felsorolt lehetőségek automatikusan frissülnek.
Töltse le a példa fájlt
Hogy működik ez? - A feltételes legördülő lista (az E3 cellában) = KÖZVETLEN (D3). Ez azt jelenti, hogy amikor a „Gyümölcsök” lehetőséget választja a D3 cellában, az E3 legördülő listája a megnevezett „Gyümölcsök” tartományra vonatkozik (az INDIRECT funkción keresztül), és ezért felsorolja az adott kategória összes elemét.
Fontos jegyzet: Ha a fő kategória több szóból áll (például „Szezonális gyümölcsök” a „Gyümölcsök” helyett), akkor a = KÖZVETLEN (Helyettesítő (D3, ”„, ”_)) képletet kell használnia a fentebb bemutatott egyszerű INDIRECT funkció.
- Ennek az az oka, hogy az Excel nem engedélyezi a szóközöket a megnevezett tartományokban. Tehát ha egy elnevezett tartományt több szó használatával hoz létre, az Excel automatikusan aláhúzást illeszt be a szavak közé. Ha például elnevezett tartományt hoz létre „Szezonális gyümölcsökkel”, akkor a háttérben a Season_Fruits lesz a neve. A PÓTLÁS funkció használata az INDIRECT funkción belül gondoskodik a szóközökről vannak aláhúzásokká alakítva.
A függő legördülő lista tartalmának automatikus visszaállítása/törlése
Ha kiválasztotta, majd módosítja a szülő legördülő menüt, a függő legördülő lista nem változik, és ezért téves bejegyzés.
Ha például a „Gyümölcsök” kategóriát választja, majd az Apple elemet választja, majd visszalép, és a kategóriát „Zöldségek” -re változtatja, akkor a függő legördülő menüben továbbra is az Apple jelenik meg.
A VBA használatával biztosíthatja, hogy a függő legördülő lista tartalma visszaálljon a fő legördülő lista módosításakor.
Itt található a VBA kód a függő legördülő lista tartalmának törléséhez:
Privát almunkamenet_Change (ByVal cél, mint tartomány) On Error Folytassa a következőt, ha a cél. Oszlop = 4 Majd ha a cél.Validation.Type = 3 Majd Application.EnableEvents = False Target.Offset (0, 1). Application.EnableEvents = Igaz kilépés alvég al
Ennek a kódnak a tulajdonosa Debra ezen oktatóanyagában szerepel a függő legördülő listák törléséről az Excelben, amikor a kiválasztást megváltoztatják.
Ezt a kódot a következőképpen lehet működtetni:
- Másolja a VBA kódot.
- Az Excel munkafüzetben, ahol a függő legördülő lista található, lépjen a Fejlesztő fülre, és kattintson a „Kód” csoporton belül a Visual Basic elemre (használhatja az ALT + F11 billentyűparancsot is).
- A VB Editor ablakban, a projektfelfedező bal oldalán láthatja az összes munkalap nevét. Kattintson duplán arra, amelyik a legördülő listát tartalmazza.
- Illessze be a kódot a jobb oldali kódablakba.
- Zárja be a VB szerkesztőt.
Most, amikor megváltoztatja a fő legördülő listát, a VBA kód aktiválódik, és törli a függő legördülő lista tartalmát (az alábbiak szerint).
Ha nem rajong a VBA -hoz, használhat egy egyszerű feltételes formázási trükköt is, amely kiemeli a cellát, ha eltérés van. Ez segíthet vizuálisan látni és kijavítani az eltérést (az alábbiak szerint).
Íme a lépések, amelyek a t0 kiemelik az eltéréseket a függő legördülő listákban:
- Válassza ki azt a cellát, amely a függő legördülő listát tartalmazza.
- Lépjen a Főoldalra -> Feltételes formázás -> Új szabály.
- Az Új formázási szabály párbeszédpanelen válassza a „Képlet használatával határozza meg a formázandó cellákat” lehetőséget.
- A képlet mezőbe írja be a következő képletet: = ISERROR (VLOOKUP (E3, INDEX ($ A $ 2: $ B $ 6,, MATCH (D3, $ A $ 1: $ B $ 1)), 1,0))
- Állítsa be a formátumot.
- Kattintson az OK gombra.
A képlet a VLOOKUP függvényt használja annak ellenőrzésére, hogy a függő legördülő lista eleme a fő kategóriából származik -e vagy sem. Ha nem, a képlet hibát ad vissza. Ezt használja az ISERROR függvény az IGAZ érték visszaadására, amely a feltételes formázást a cella kiemelésére utasítja.
A következő Excel oktatóanyagok is tetszhetnek:
- Az adatok kivonása a legördülő lista alapján.
- Keresési javaslatokat tartalmazó legördülő lista létrehozása.
- Válasszon ki több elemet a legördülő listából.
- Hozzon létre több legördülő listát ismétlés nélkül.
- Takarítson meg időt az Excel adatbeviteli űrlapjaival.
