Az Excel táblázatok gyakran tartalmaznak legördülő elemeket az adatbevitel egyszerűsítése és / vagy szabványosítása érdekében. Ezeket a legördülő menüket az adatkontroll szolgáltatás segítségével hozza létre, hogy meghatározza az engedélyezett bejegyzések listáját.
Egy egyszerű legördülő lista beállításához válassza ki azt a cellát, ahova az adatokat beírja, majd kattintson az Adat ellenőrzés(a Adatoklapon), válassza az Adat ellenőrzése lehetőséget, válassza a Lista(az Engedélyezés :) pontot, majd írja be a lista elemeit (vesszővel elválasztva) az Forrás: mező (lásd az 1. ábrát).
Az ilyen típusú legördülő menüben a megengedett bejegyzések listája kerül meghatározásra. magában az adatok érvényesítésében; ezért a listában történő módosításokhoz a felhasználónak meg kell nyitnia és szerkesztenie kell az adatok érvényesítését. Ez azonban nehéz lehet tapasztalatlan felhasználók számára, vagy olyan esetekben, amikor a választási lista hosszú.
Egy másik lehetőség az, ha a listát megnevezett tartomány a táblázatban -be helyezi, majd megadja az a tartománynév (egyenlőségjel elé helyezve) az adatforrás érvényesítésének Forrásmezőjében (a 2. ábra szerint).
Ez a második módszer megkönnyíti a lista választásainak szerkesztését, ám az elemek hozzáadása vagy eltávolítása problémás lehet. Mivel az elnevezett tartomány (a példánkban a FruitChoices) egy rögzített sejttartományra vonatkozik ($ H $ 3: $ H $ 10, az ábra szerint), ha további lehetőségeket adunk a H11 vagy alatti cellákhoz, akkor azok nem jelennek meg a legördülő menüben (mivel ezek a sejtek nem tartoznak a FruitChoices sorozatba.)
Hasonlóképpen, ha például a Körte és a Szamóca bejegyzéseket törli, akkor azok már nem jelennek meg a legördülő menüben, hanem a legördülő sor két „Üres” választások, mivel a legördülő lista továbbra is a teljes FruitChoices tartományra utal, beleértve az üres H9 és H10 cellákat is.
Ezen okok miatt, amikor egy normál elnevezett tartományt használnak a legördülő lista forrásaként, az elnevezett tartomány magát úgy kell szerkeszteni, hogy több vagy kevesebb cellát tartalmazzon, ha a bejegyzéseket hozzáadják vagy törlik a listáról.
Ennek a problémának a megoldása egy dinamikushasználata. >tartomány neve, mint a legördülő lehetőségek forrása. A dinamikus tartománynév az, amely automatikusan kibővül (vagy összehúzódik), hogy pontosan megfeleljen az adatblokk méretének, amikor a bejegyzéseket hozzáadják vagy eltávolítják. Ehhez a megnevezett tartomány meghatározásához a rögzített cellacímtartomány helyett a képlet-t kell használni.
A dinamikus beállítás beállítása Range in Excel
A normál (statikus) tartománynév meghatározott cellákra vonatkozik ($ H $ 3: $ H $ 10 a példánkban, lásd alább):
De a dinamikus tartományt egy képlet határozza meg (lásd alább, egy külön táblázatból, amely dinamikus tartományneveket használ):
Mielőtt elkezdenénk, győződjön meg arról, hogy letöltötte a Excel példafájl elemet (a makrók rendezése le van tiltva.)
Vizsgáljuk meg részletesen ezt a képletet. A Gyümölcseket közvetlenül a címsor alatt lévő cellák blokkjában lehet megválasztani (Gyümölcsök). Ennek a fejlécnek a nevét is hozzá kell rendelni: FruitsHeading
A teljes képlet a dinamikus tartomány meghatározására szolgál a A gyümölcs választása:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)
FruitsHeadingarra a fejlécre utal, amely egy sorral a lista első bejegyzésének feletti. A 20. szám (a képletben kétszer használt) a lista maximális mérete (sorok száma) (ezt szükség szerint beállíthatjuk).
Vegye figyelembe, hogy ebben a példában csak 8 bejegyzés található , de ezek alatt vannak üres cellák is, ahol további bejegyzéseket lehet hozzáadni. A 20 szám arra a teljes blokkra vonatkozik, amelybe bejegyzéseket lehet tenni, nem pedig a tényleges bejegyzések számára.
Most osztjuk szét a képletet darabokra (az egyes darabok színkódolása), hogy megértsük, hogyan működik :
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)
A „legbelső” darab OFFSET (FruitsHeading, 1,0,20,1). Ez a 20 cellából álló blokkra utal (a FruitsHeading cella alatt), ahol választásokat lehet megadni. Ez az OFFSET funkció alapvetően azt mondja: Indítsa el a FruitsHeadingcellát, menjen le 1 sorral és 0 oszlop felett, majd válassza ki a 20 sor hosszú és 1 oszlop széles területet. Tehát ez adja a 20 soros blokkot, amelybe a Gyümölcs választás kerül beírásra.
A képlet következő darabja az ISBLANKfüggvény:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(the above),0,0),0)-1,20),1)
Itt az OFFSET (fent kifejtett) funkció helyébe a „fentiek” léptek (hogy a dolgok könnyebben olvashatók legyenek). De az ISBLANK függvény az OFFSET függvény által meghatározott 20 soros cellákon működik.
Az ISBLANK ezután 20 TRUE és FALSE értéket készít, jelezve, hogy a 20- Az OFFSET funkció által hivatkozott sortartomány üres (üres) vagy sem. Ebben a példában a készlet első 8 értéke FALSE lesz, mivel az első 8 cella nem üres, és az utolsó 12 érték igaz lesz.
A képlet következő része az INDEX függvény:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(the above,0,0),0)-1,20),1)
Ismét a „fentiek” a fent leírt ISBLANK és OFFSET funkciókra utalnak. Az INDEX függvény egy olyan tömböt ad vissza, amely tartalmazza az ISBLANK függvény által létrehozott 20 TRUE / FALSE értéket.
INDEXáltalában egy bizonyos érték (vagy értéktartomány) kiválasztására szolgál az adatblokk, egy sor és oszlop megadásával (azon a blokon belül). De ha a sor és az oszlop bemeneteit nullára állítja (ahogy itt megtörténik), akkor az INDEX egy olyan tömböt ad vissza, amely tartalmazza a teljes adatblokkot.
A képlet következő része a MATCH függvény:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,the above,0)-1,20),1)
A MEGFELELÉSfüggvény az első IGAZ értékét adja vissza az INDEX függvény által visszaadott tömbön belül. Mivel a lista első 8 bejegyzése nem üres, a tömb első 8 értéke FALSE lesz, és a kilencedik érték TRUE (mivel a tartomány 9 sora üres).
Tehát a MATCH függvény visszaadja a 9értékét. Ebben az esetben azonban nagyon szeretnénk tudni, hogy hány bejegyzés van a listában, így a képlet kivonja az 1-et a MATCH értékből (amely megadja az utolsó bejegyzés helyét). Tehát végül a MATCH (TRUE, a fenti, 0) -1 visszaküldi a 8
értéket. A képlet következő darabja az IFERROR függvény:
=OFFSET(FruitsHeading,1,0,IFERROR(the above,20),1)
Az IFERROR funkció alternatív értéket ad vissza, ha az első megadott érték hibát eredményez. Ez a funkció be van építve, mivel ha a cellák teljes blokkja (mind a 20 sor) kitöltésre kerül, a MATCH függvény hibát ad vissza.
Ennek oka az, hogy a MATCH függvénynek azt mondjuk, hogy keressen az első igaz érték (az ISBLANK függvény értékeinek tömbjében), de ha NEM a cellák üres, akkor a teljes tömb FALSE értékekkel lesz kitöltve. Ha a MATCH nem találja a célértéket (TRUE) a keresett tömbben, akkor egy hibát ad vissza.
Tehát ha a teljes lista megtelt (és ezért a MATCH hibát ad vissza), akkor az IFERROR funkció ehelyett visszaadja a 20 értéket (tudva, hogy 20 tételnek kell lennie a listában).
Végül a OFFSET (FruitsHeading, 1,0, a fenti, 1)ad vissza a tartomány, amelyet valójában keresünk: Indítsa el a FruitsHeading cellát, menjen le 1 sorral és 0 oszlop fölé, majd válassza ki a területet, amely ugyanakkor sok sor, amennyiben a listában vannak bejegyzések (és 1 oszlop széles). Tehát a teljes képlet együttesen visszaadja azt a tartományt, amely csak a tényleges bejegyzéseket tartalmazza (egészen az első üres celláig).
Ezzel a képlettel határozhatja meg a legördülő menü forrásaként használt tartományt, így szabadon szerkeszthető. a lista (bejegyzések hozzáadása vagy eltávolítása, feltéve, hogy a fennmaradó bejegyzések a felső cellában kezdődnek és egymással szomszédosak), és a legördülő menü mindig tükrözi az aktuális listát (lásd 6. ábra).
Az itt használt példafájl (dinamikus listák) mellékelve van, és letölthető erről a webhelyről. A makrók azonban nem működnek, mivel a WordPressnek nem tetszik az Excel könyvek, amelyekben makrók vannak.
A sorblokk megadásának alternatívájaként a listablokkhoz hozzá lehet rendelni a saját tartománynév, amelyet felhasználhatunk egy módosított képletben. A példafájlban egy második lista (Nevek) használja ezt a módszert. Itt a teljes listablokkhoz (a „NÉVEK” címsor alatt, 40 sor a példafájlban) a NameBlocktartománynév lesz hozzárendelve. A NamesList meghatározására szolgáló alternatív képlet ezután:
=OFFSET(NamesHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(NamesBlock),0,0),0)-1,ROWS(NamesBlock)),1)
ahol a NamesBlockhelyettesíti az OFFSET (FruitsHeading, 1,0,20,1) és ROWS (NamesBlock)helyettesíti a korábbi képletben a 20-at (sorok száma).
Tehát a könnyen szerkeszthető legördülő listákhoz (beleértve a tapasztalatlan más felhasználókat is), próbálja meg a dinamikus tartományneveket! És vegye figyelembe, hogy bár ez a cikk a legördülő listákra összpontosított, a dinamikus tartománynevek bárhol felhasználhatók, ahol hivatkozni kell egy tartományra vagy listára, amely méretükben változhat. Enjoy!