Hogyan hozhat létre legördülő listát az Excelben (az egyetlen útmutató, amire szüksége van)

A legördülő lista kiváló módja annak, hogy a felhasználó választhasson az előre meghatározott listából.

Használható, amikor a felhasználót egy űrlap kitöltésére kényszeríti, vagy interaktív Excel -irányítópultokat készít.

A legördülő listák meglehetősen gyakoriak a webhelyeken/alkalmazásokban, és nagyon intuitívak a felhasználó számára.

Videó megtekintése - Legördülő lista létrehozása Excelben

Ebben az oktatóanyagban megtudhatja, hogyan hozhat létre egy legördülő listát az Excelben (ez csak néhány másodpercet vesz igénybe), valamint az összes fantasztikus dolgot.

Hogyan hozzunk létre egy legördülő listát az Excelben

Ebben a szakaszban megtudhatja az Excel legördülő lista létrehozásának lépéseit:

1. A sejtek adatainak felhasználása.
2. Adatok manuális bevitele.
3. Az OFFSET képlet használatával.

#1 A sejtek adatainak használata

Tegyük fel, hogy rendelkezik az alábbi listával:

Íme az Excel legördülő lista létrehozásának lépései:

1. Válassza ki azt a cellát, ahol a legördülő listát létre kívánja hozni.
2. Lépjen az Adatok -> Adateszközök -> Adatellenőrzés menüpontra.
3. Az Adatok érvényesítése párbeszédpanel Beállítások lapján válassza a Lista lehetőséget az érvényesítési feltételek között.
   • Amint kiválasztja a Lista lehetőséget, megjelenik a forrásmező.
4. A forrás mezőbe írja be = $ A $ 2: $ A $ 6, vagy egyszerűen kattintson a Forrás mezőre, és válassza ki a cellákat az egér használatával, majd kattintson az OK gombra. Ez egy legördülő listát illeszt be a C2 cellába.
   • Győződjön meg arról, hogy a Cellaon belüli legördülő menü be van jelölve (ez alapértelmezés szerint be van jelölve). Ha ez az opció nincs bejelölve, a cella nem jelenít meg legördülő menüt, azonban manuálisan is megadhatja az értékeket a listában.

Jegyzet: Ha egyszerre szeretne létrehozni legördülő listákat több cellában, jelölje ki az összes cellát, ahol létre szeretné hozni, majd kövesse a fenti lépéseket. Győződjön meg arról, hogy a cellahivatkozások abszolútak (például $ A $ 2), és nem relatívak (például A2, A $ 2 vagy $ A2).

#2 Az adatok kézi bevitelével

A fenti példában cellahivatkozásokat használunk a Forrás mezőben. Az elemeket közvetlenül is hozzáadhatja, ha kézzel írja be a forrásmezőbe.

Tegyük fel például, hogy két lehetőséget szeretne megjeleníteni: Igen és Nem, a cella legördülő menüjében. Így adhatja meg közvetlenül az adatellenőrzési forrás mezőbe:

• Válassza ki azt a cellát, amelyben létre szeretné hozni a legördülő listát (ebben a példában a C2 cella).
• Lépjen az Adatok -> Adateszközök -> Adatellenőrzés menüpontra.
• Az Adatok érvényesítése párbeszédpanel Beállítások lapján válassza ki a Lista lehetőséget érvényesítési feltételként.
  • Amint kiválasztja a Lista lehetőséget, megjelenik a forrásmező.
• A forrás mezőbe írja be az Igen, Nem lehetőséget
  • Győződjön meg arról, hogy a Cellaon belüli legördülő menü be van jelölve.
• Kattintson az OK gombra.

Ez egy legördülő listát hoz létre a kijelölt cellában. A forrás mezőben felsorolt ​​összes elem, vesszővel elválasztva, különböző sorokban szerepel a legördülő menüben.

A forrás mezőben megadott összes elem vesszővel elválasztva különböző sorokban jelenik meg a legördülő listában.

Jegyzet: Ha egyszerre szeretne létrehozni legördülő listákat több cellában, jelölje ki az összes cellát, ahol létre szeretné hozni, majd kövesse a fenti lépéseket.

#3 Az Excel képletek használata

A cellákból való kiválasztáson és az adatok kézi bevitelén kívül a forrásmezőben található képlet segítségével Excel legördülő listát is létrehozhat.

Bármely képlet, amely értéklistát ad vissza, felhasználható egy legördülő lista létrehozásához az Excelben.

Tegyük fel például, hogy rendelkezik az alábbi adatkészlettel:

Íme az Excel legördülő lista létrehozásának lépései az OFFSET funkció használatával:

• Válassza ki azt a cellát, amelyben létre szeretné hozni a legördülő listát (ebben a példában a C2 cella).
• Lépjen az Adatok -> Adateszközök -> Adatellenőrzés menüpontra.
• Az Adatok érvényesítése párbeszédpanel Beállítások lapján válassza ki a Lista lehetőséget érvényesítési feltételként.
  • Amint kiválasztja a Lista lehetőséget, megjelenik a forrásmező.
• A Forrás mezőbe írja be a következő képletet: = OFFSET ($ A $ 2,0,0,5)
  • Győződjön meg arról, hogy a Cellaon belüli legördülő menü be van jelölve.
• Kattintson az OK gombra.

Ez létrehoz egy legördülő listát, amely felsorolja az összes gyümölcsnevet (az alábbiak szerint).

Jegyzet: Ha egyszerre szeretne létrehozni egy legördülő listát több cellában, jelölje ki az összes cellát, ahol létre szeretné hozni, majd kövesse a fenti lépéseket. Győződjön meg arról, hogy a cellahivatkozások abszolútak (például $ A $ 2), és nem relatívak (például A2, A $ 2 vagy $ A2).

Hogyan működik ez a képlet ??

A fenti esetben az OFFSET függvényt használtuk a legördülő lista létrehozásához. Visszaadja a ra elemek listáját

Visszaadja az A2: A6 tartomány elemeinek listáját.

Íme az OFFSET függvény szintaxisa: = OFFSET (hivatkozás, sorok, oszlopok, [magasság], [szélesség])

Öt érvre van szükség, ahol A2 -ként adtuk meg a hivatkozást (a lista kiindulópontja). A sorok/sorok 0 -nak vannak megadva, mivel nem akarjuk eltolni a referenciacellát. A magasság 5, mivel a listában öt elem található.

Most, amikor ezt a képletet használja, egy tömböt ad vissza, amely tartalmazza az A2: A6 öt gyümölcsének listáját. Ne feledje, hogy ha beírja a képletet egy cellába, jelölje ki és nyomja meg az F9 billentyűt, és látni fogja, hogy a gyümölcsnevek tömbjét adja vissza.

Dinamikus legördülő lista létrehozása Excelben (OFFSET használatával)

A legördülő lista létrehozására szolgáló képlet fenti technikája kiterjeszthető dinamikus legördülő lista létrehozására is. Ha a fentiek szerint az OFFSET funkciót használja, akkor is, ha további elemeket ad hozzá a listához, a legördülő lista nem frissül automatikusan. Minden alkalommal, amikor módosítja a listát, manuálisan frissítenie kell.

Íme egy módja annak, hogy dinamikussá tegyük (és ez nem más, mint a képlet apró csípése):

• Válassza ki azt a cellát, amelyben létre szeretné hozni a legördülő listát (ebben a példában a C2 cella).
• Lépjen az Adatok -> Adateszközök -> Adatellenőrzés menüpontra.
• Az Adatok érvényesítése párbeszédpanel Beállítások lapján válassza a Lista lehetőséget az érvényesítési feltételek között. Amint kiválasztja a Lista lehetőséget, megjelenik a forrásmező.
• A forrás mezőbe írja be a következő képletet: = OFFSET ($ A $ 2,0,0, COUNTIF ($ A $ 2: $ A $ 100, ””))
• Győződjön meg arról, hogy a Cellaon belüli legördülő menü be van jelölve.
• Kattintson az OK gombra.

Ebben a képletben az 5 -ös argumentumot COUNTIF -ra cseréltem ($ A $ 2: $ A $ 100, ””).

A COUNTIF függvény megszámolja a nem üres cellákat az A2: A100 tartományban. Ezért az OFFSET funkció úgy állítja be magát, hogy tartalmazza az összes nem üres cellát.

Jegyzet:

• Ahhoz, hogy ez működjön, NEM lehet üres cella a kitöltött cellák között.
• Ha egyszerre szeretne létrehozni egy legördülő listát több cellában, jelölje ki az összes cellát, ahol létre szeretné hozni, majd kövesse a fenti lépéseket. Győződjön meg arról, hogy a cellahivatkozások abszolútak (például $ A $ 2), és nem relatívak (például A2, A $ 2 vagy $ A2).

Másolja a beillesztési legördülő listákat az Excelben

Másolhatja be az adatok érvényesítésével rendelkező cellákat más cellákba, és másolja az adatok érvényesítését is.

Például, ha van egy legördülő lista a C2 cellában, és szeretné alkalmazni a C3: C6-ra is, egyszerűen másolja ki a C2 cellát, és illessze be a C3: C6 mezőbe. Ez lemásolja a legördülő listát, és elérhetővé teszi a C3: C6-ban (a legördülő menüvel együtt a formázást is).

Ha csak a legördülő menüt szeretné másolni, és nem a formázást, akkor tegye a következőket:

• Másolja a legördülő listát tartalmazó cellát.
• Válassza ki azokat a cellákat, ahová másolni szeretné a legördülő menüt.
• Lépjen a Főoldalra -> Beillesztés -> Különleges beillesztés.
• A Speciális beillesztés párbeszédpanelen válassza a Beillesztés beállításaiban az Érvényesítés lehetőséget.
• Kattintson az OK gombra.

Ez csak a legördülő menüt másolja, a másolt cella formázását nem.

Vigyázat, amikor az Excel legördülő listával dolgozik

Óvatosnak kell lennie, amikor az Excel legördülő listáival dolgozik.

Ha másol egy cellát (amely nem tartalmaz legördülő listát) a legördülő listát tartalmazó cellára, a legördülő lista elveszik.

Ennek legrosszabb része az, hogy az Excel nem jelenít meg semmilyen figyelmeztetést vagy felszólítást a felhasználó tudomására, hogy egy legördülő menü felülírásra kerül.

Hogyan válasszuk ki az összes cellát, amelyben van egy legördülő lista

Néha nehéz tudni, hogy mely cellák tartalmazzák a legördülő listát.

Ezért van értelme ezeket a cellákat úgy megjelölni, hogy külön szegélyt vagy háttérszínt adnak nekik.

Ahelyett, hogy manuálisan ellenőrizné az összes cellát, gyors mód van arra, hogy kiválassza az összes cellát, amelyek legördülő listákat tartalmaznak (vagy bármilyen adatellenőrzési szabályt).

• Lépjen a Kezdőlapra -> Keresés és kiválasztás -> Ugrás a speciális menüponthoz.
• Az Ugrás a speciális párbeszédpanelen válassza az Adatellenőrzés lehetőséget
  • Az adatellenőrzésnek két lehetősége van: Mind és Ugyanaz. Mindenki kijelöli az összes cellát, amelyen adatérvényesítési szabály van érvényben. Ugyanaz csak azokat a cellákat választja ki, amelyek ugyanazokkal az adatellenőrzési szabályokkal rendelkeznek, mint az aktív cella.
• Kattintson az OK gombra.

Ez azonnal kiválasztja az összes cellát, amelyre adatérvényesítési szabály vonatkozik (ideértve a legördülő listákat is).

Most egyszerűen formázhatja a cellákat (adja meg a szegélyt vagy a háttérszínt), hogy vizuálisan látható legyen, és véletlenül ne másoljon rá egy másik cellát.

Itt van egy másik Jon Acampora technika, amellyel mindig láthatóvá teheti a legördülő nyíl ikont. Ennek néhány módját is megtekintheti ebben az Excel úr által készített videóban.

Függő / feltételes Excel legördülő lista létrehozása

Itt egy videó arról, hogyan hozhat létre függő legördülő listát az Excelben.

Ha inkább olvasni szeretne, mint nézni egy videót, olvassa tovább.

Előfordulhat, hogy egynél több legördülő listája van, és azt szeretné, ha a második legördülő menüben megjelenő elemek attól függnének, hogy a felhasználó mit választott ki az első legördülő menüben.

Ezeket függő vagy feltételes legördülő listáknak nevezzük.

Az alábbiakban egy példa a feltételes/függő legördülő listára:

A fenti példában, ha a „Drop 2” menüpontban felsorolt ​​tételek a „Drop Down 1” -ben elvégzett kiválasztástól függenek.

Most nézzük meg, hogyan lehet ezt létrehozni.

Az alábbi lépésekkel hozhat létre függő / feltételes 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 megjegyzés, amikor feltételes legördülő listákkal dolgozik az Excelben:

• 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 az USA -t választja országnak, majd Floridát választja államnak, majd visszamegy és megváltoztatja az országot Indiára, az állam továbbra is Florida marad. Itt van egy nagyszerű Debra bemutató a függő (feltételes) legördülő listák törléséről az Excelben, amikor a kijelölés megváltozik.
• 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é. Tehát a „Szezonális gyümölcsök” elnevezésű tartomány „Szezonális_gyümölcsök” lenne. 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.