Hogyan készítsünk összetett legördülő listát Excelben?
Ha Excelben tölt ki egy táblázatot, és az oszlopban lévő adatok időnként ismétlődnek, például egy termék vagy egy alkalmazott neve, akkor annak érdekében, hogy ne minden alkalommal adja meg a szükséges paramétert, egyszerűbb és egyszerűbb egyszer létrehozni egy legördülő listát, és kiválasztani belőle egy értéket.
A cikkben megvizsgáljuk, hogyan lehet különféle típusú legördülő listákat készíteni egy Excel-táblázatban.
Hozzon létre egy egyszerű legördülő listát
Ehhez az A1: A7 cellákba beírjuk a listában megjelenő adatokat. Most válassza ki a cellát, amelyben létrehozunk egy legördülő listát - B2.
Lépjen az "Adatok" fülre, és kattintson az "Adatok ellenőrzése" gombra.
Az "Opciók" lapon az "Adattípus" mezőben válassza a "Lista" lehetőséget. A Forrás mezőbe többféleképpen adhat meg értékeket:
1 - adja meg kézzel a lista értékeit pontosvesszővel elválasztva;
2 - adja meg a cellák tartományát, amelybe a legördülő lista adatait beírják;
3 - válassza ki a nevekkel ellátott cellákat, kattintson rájuk a jobb gombbal, és válassza a menü "Név hozzárendelése" parancsát.
Jelölje ki a B2 cellát, és írja be a "="" jelet a "Forrás" mezőbe, majd írja be a létrehozott nevet.
Így létrehoztunk egy egyszerű legördülő listát az Excelben.
Ha van fejléce egy oszlophoz, és minden sort ki kell töltenie értékekkel, akkor ne egy cellát jelöljön ki, hanem egy cellatartományt - B2:B9. Ezután minden cellában kiválasztható a kívánt érték a legördülő listából.
Értékek hozzáadása a legördülő listához - Dinamikus lista
Ebben az esetben hozzáadunk értékeket a kívánt tartományhoz, és automatikusan hozzáadódnak a legördülő listához.
Válassza ki a cellák tartományát - D1:D8, majd a "Kezdőlap" lapon kattintson a "Táblázat formázása" gombra, és válasszon bármilyen stílust.
Megerősítjük az adatok helyét, és bejelöljük a "Táblázat fejlécekkel" mezőt.
A tetejére írjuk a táblázat címét - "Alkalmazottak", és töltsük ki adatokkal.
Válassza ki a legördülő listát tartalmazó cellát, majd kattintson az „Adatellenőrzés” gombra. A következő ablakban a "Forrás" mezőbe írja be a következőt: =INDIRECT("Table1"). Egy táblázatom van egy lapon, ezért azt írom, hogy „Table1”, ha van egy második - „Table2”, és így tovább.
Most adjunk hozzá egy új alkalmazott nevet a listánkhoz: Ira. Megjelent a legördülő listában. Ha bármilyen nevet eltávolítunk a táblázatból, az is lekerül a listáról.
Legördülő lista egy másik lap értékeivel
Ha az egyik lapon a legördülő listákat tartalmazó táblázat, a másikon pedig ezen listák adatai vannak, akkor ez a funkció sokat segít nekünk.
A 2. lapon jelöljön ki egy cellát vagy cellatartományt, majd kattintson az "Adatellenőrzés" gombra.
Lépjen az 1. lapra, helyezze a kurzort a "Forrás" mezőbe, és válassza ki a kívánt cellatartományt.
Most már felvehet neveket az 1. munkalapon, ezek felkerülnek a 2. munkalap legördülő listáira.
Függő legördülő listák létrehozása
Tegyük fel, hogy három tartományunk van: az alkalmazottak keresztneve, vezetékneve és középső neve. Mindegyikhez nevet kell rendelnie. Ennek a tartománynak a celláit jelöljük ki, ez lehet üres is - idővel lehetőség nyílik olyan adatok hozzáadására, amelyek a legördülő listában jelennek meg. Jobb egérgombbal kattintunk rájuk, és a listából kiválasztjuk a "Név hozzárendelése" lehetőséget.
Az első neve "Név", a második - "Vezetéknév", a harmadik - "Apa".
Készítsünk egy másik tartományt, amelybe a hozzárendelt neveket írjuk. Nevezzük „alkalmazottaknak”.
Elkészítjük az első legördülő listát, amely a tartományok neveiből fog állni. Válassza ki az E1 cellát, és az Adatok lapon válassza az Adatok érvényesítése lehetőséget.
Az "Adattípus" mezőben válassza a "Lista" lehetőséget, a forrás mezőbe írja be a "=Alkalmazottak" kifejezést, vagy válasszon ki egy nevet kapott cellatartományt.
Az első legördülő lista elkészült. Most az F2 cellában létrehozunk egy második listát, amely az elsőtől függ. Ha az elsőben a „Keresztnév” lehetőséget választjuk, akkor a másodikban a vezetéknevek listája jelenik meg, ha a „Vezetéknév” lehetőséget választjuk - a vezetéknevek listája.
Jelölje ki a cellát, és kattintson az Adatok érvényesítése gombra. Az "Adattípus" mezőben válassza a "Lista" lehetőséget, a forrás mezőbe írja be a következőket: =INDIRECT($E$1). Itt az E1 az első legördülő listát tartalmazó cella.
Ezen elv alapján függő legördülő listákat készíthet.
Ha a jövőben meg kell adnia az értékeket abban a tartományban, amelyhez a név tartozik, például: "Vezetéknév". Lépjen a "Képletek" fülre, és kattintson a "Névkezelő" gombra. Most a tartomány nevében válassza ki a "Vezetéknév" lehetőséget, és alul az utolsó C3 cella helyett írja be a C10-et. Kattintson a pipára. Ezt követően a tartomány növekszik, és lehetőség nyílik rá adatok hozzáadására, amelyek automatikusan megjelennek a legördülő listában.
Most már tudja, hogyan készíthet legördülő listát az Excelben.
Egyszerre több cellából álló legördülő lista létrehozása (tegyük fel, hogy a név költséggel jár)
Köszönöm, minden jól működött.
A másik lapról származó értékeket tartalmazó legördülő lista nem működik, mivel az ablak az adatellenőrzés megnyitásakor nem teszi lehetővé a többi ablakkal való munkát, különösen egy másik lappal!
A függő legördülő menü segítségével olyan trükköt hajthat végre, amelyet gyakran dicsérnek az Excel-sablonok felhasználói. Egy trükk, ami megkönnyíti és gyorsabbá teszi a munkát. Egy trükk, amely kényelmessé és kellemessé teszi formáit.
Példa egy függő legördülő lista létrehozására egy Excel cellában
Példa egy függő legördülő lista használatára, amellyel kényelmes űrlapot hozhat létre az eladók által megrendelt dokumentumok kitöltéséhez. A teljes kínálatból ki kellett választani azokat a termékeket, amelyeket értékesíteni fognak.
Minden eladó először azonosított egy termékcsoportot, majd egy adott terméket ebből a csoportból. Az űrlapnak tartalmaznia kell a csoport teljes nevét és egy adott cikkindexet. Mivel ezt kézzel beírni túl időigényes (és bosszantó) lenne, egy nagyon gyors és egyszerű megoldást találtam ki – 2 függő legördülő menüt.
Az első az összes termékkategória listája, a második a kiválasztott kategória összes termékének listája volt. Ezért készítettem egy legördülő listát az előző listában végzett kiválasztás függvényében (itt két függő legördülő lista létrehozásához találsz anyagot).
Az otthoni költségvetési sablon használója ugyanazt az eredményt szeretné elérni, ahol a kiadások kategóriájára és alkategóriájára van szükség. Példa az adatokra az alábbi ábrán:
Tehát ha például a Szórakozás kategóriát választjuk, akkor az alkategóriák listája a következő legyen: Mozi, Színház, Medence. Nagyon gyors megoldás, ha részletesebb információkat szeretne elemezni otthoni költségvetésében.
Kategóriák és alkategóriák listázása az Excel-függő legördülő menüben
Bevallom, hogy az otthoni költségvetés javasolt változatában csak egy kategóriára korlátozódtam, mivel számomra egy ilyen kiadásmegosztás bőven elég (a kiadások/bevételek megnevezése alkategóriának számít). Ha azonban alkategorizálnia kell őket, akkor az alábbiakban leírt módszer az ideális. Használd bátran!
A végeredmény pedig így néz ki:
Az alkategóriák függő legördülő listája
Ennek eléréséhez egy kicsit más adattáblázatot kell készítenünk, mintha egyetlen legördülő listát hoznánk létre. A táblázatnak így kell kinéznie (G2:H15 tartomány):
Munkaforrás Excel táblázat
Ebben a táblázatban meg kell adnia egy kategóriát és mellette annak alkategóriáit. A kategória nevét annyiszor kell megismételni, ahány alkategória van. Nagyon fontos, hogy az adatok a Kategória oszlop szerint legyenek rendezve. Ez rendkívül fontos lesz, amikor később megírjuk a képletet.
Használhatjuk az első kép táblázatait is. Természetesen a képletek mások lennének. Egyszer még én is találtam ilyen megoldást a neten, de nem tetszett, mert fix hosszúságú volt a lista: ami azt jelenti, hogy a lista néha üres mezőket tartalmazott, néha pedig nem minden elemet. Természetesen ezt a korlátot elkerülhetem, de bevallom, hogy az én megoldásomat jobban szeretem, ezért nem tértem vissza ehhez a megoldáshoz.
Rendben, akkor. Most egyenként leírom a függő legördülő lista létrehozásának lépéseit.
1. Cellatartományok nevei
Ez egy opcionális lépés, enélkül gond nélkül meg tudjuk oldani. Viszont szeretek neveket használni, mert így sokkal könnyebben írható és olvasható a képlet.
Nevezzük el a két tartományt. Az összes kategória listája és a kategóriák munkalistája. Ezek az A3:A5 (a kategóriák listája az első képen a zöld táblázatban) és a G3:G15 (a lila munkalap ismétlődő kategóriáinak listája) tartományok lesznek.
A kategóriák listájának elnevezéséhez:
- Válassza ki az A3:A5 tartományt.
- A név mezőbe (a képletsor bal oldalán lévő mezőbe) írja be a „Kategória” nevet.
- Erősítse meg az Enter billentyűvel.
Tegye ugyanezt a G3:G15 kategória munkalista-tartományával, amelyet WorkList-nek nevezhet. Ezt a tartományt fogjuk használni a képletben.
2. Hozzon létre egy legördülő listát egy kategóriához
Egyszerű lesz:
- Válassza ki azt a cellát, ahová a listát el szeretné helyezni. Az én esetemben A12.
- Az ADAT menüből válassza ki az Adatellenőrzés eszközt. Megjelenik a Bemeneti értékek érvényesítése ablak.
- Adattípusként válassza a "Lista" lehetőséget.
- A forráshoz írja be: =Kategória (az alábbi kép).
- Erősítse meg az OK gombbal.
Az eredmény a következő:
A kategória legördülő listája.
3. Hozzon létre egy függő legördülő listát egy alkategóriához
Most jó móka lesz. Tudjuk, hogyan kell listákat létrehozni – csak egy kategória esetében csináltuk. Csak egy kérdés: "Hogyan mondhatom meg az Excelnek, hogy csak azokat az értékeket válassza ki, amelyek egy adott kategóriához tartoznak?" Amint azt valószínűleg sejtheti, itt egy munkalapot fogok használni, és természetesen képleteket.
Kezdjük azzal, amit már tudunk, vagyis egy legördülő lista létrehozásával a B12-es cellában. Tehát jelölje ki azt a cellát, kattintson az Adatok/Adatok érvényesítése elemre, és állítsa az adattípust Lista értékre.
A lista forrásába írja be a következő képletet:
A "Bemeneti értékek ellenőrzése" ablak nézete:
Egy alkategória bemeneti értékeinek ellenőrzése egy függő legördülő listában
Amint látja, az egész függő lista trükk az OFFSET funkció használata. Nos, majdnem minden. A MATCH és COUNTIF függvények segítenek neki. Az OFFSET funkció lehetővé teszi a tartományok dinamikus meghatározását. Először definiáljuk azt a cellát, ahonnan a tartományváltásnak indulnia kell, majd a következő argumentumokban a méretét.
Példánkban a tartomány a munkalap Alkategória oszlopában mozog (G2:H15). A H2 cellából indulunk el, ami egyben függvényünk első argumentuma is. A képletben a H2 cella abszolút hivatkozásként van írva, mert feltételezem, hogy sok cellában a legördülő listát fogjuk használni.
Mivel a munkalap kategória szerint van rendezve, a legördülő lista forrásaként szolgáló tartomány ott kezdődik, ahol a kiválasztott kategória először előfordul. Például az Élelmiszer kategóriában szeretnénk megjeleníteni a H6:H11 tartományt, a Szállítás kategóriánál a H12:H15 tartományt stb. Vegye figyelembe, hogy folyamatosan a H oszlop mentén haladunk, és az egyetlen dolog, A változások a tartomány eleje és magassága (azaz a lista elemeinek száma).
A tartomány eleje a H2 cellához képest annyi cellával lejjebb kerül (szám szerint), amennyi a Kategória oszlopban az elsőként előforduló kategória pozíciószáma. Egy példával könnyebb lesz megérteni: az Élelmiszer kategória tartománya 4 cellával lejjebb kerül a H2 cellához képest (a H2 cellához képest 4 cellával kezdődik). Az Alkategória oszlop 4. cellájában (a címsor nélkül, mivel ez egy WorkList nevű tartomány) az Élelmiszer szó található (első előfordulása). Ezt a tényt használjuk a tartomány kezdetének tényleges meghatározására. Ehhez a MATCH függvény szolgál majd (az OFFSET függvény második argumentumaként):
A tartomány magasságát a COUNTIF függvény határozza meg. Megszámolja a kategória összes előfordulását, vagyis a Táplálkozás szót. Hányszor fordul elő ez a szó, hány pozíció lesz a tartományunkban. A pozíciók száma egy tartományban a magassága. Itt van a funkció:
Természetesen mindkét funkció már benne van a fent leírt OFFSET funkcióban. Azt is vegye figyelembe, hogy mind a MATCH, mind a COUNTIF mezőben van hivatkozás a WorkList nevű tartományra. Mint korábban említettem, nem kell tartományneveket használnod, csak beírhatod a $H3: $H15 parancsot. A tartománynevek képletben való használata azonban egyszerűbbé és könnyebben olvashatóvá teszi.
Ez minden:
Töltse le a függő legördülő lista példáját az Excelben
Egy képlet, nos, nem olyan egyszerű, de megkönnyíti a munkát és megvéd az adatbeviteli hibáktól!