Az Excel^(Excel) -táblázatok gyakran tartalmaznak cellák legördülő menüit az adatbevitel egyszerűsítése és/vagy szabványosítása érdekében. Ezek a legördülő menük az adatellenőrzési szolgáltatással jönnek létre, hogy megadják a megengedett bejegyzések listáját.

Egy egyszerű legördülő lista létrehozásához jelölje ki azt a cellát, amelybe az adatokat be kell írni, majd kattintson az Adatellenőrzés^{(Data Validation)} elemre (az Adatok^(Data) lapon), válassza az Adatellenőrzés^{(Data Validation)} lehetőséget , válassza a Lista^(List) lehetőséget (az Engedélyezés^(Allow) alatt :), majd adja meg a listaelemeket (vesszővel elválasztva). ) a Forrás^(Source) : mezőben (lásd az 1. ábrát).

Az ilyen típusú alap legördülő menüben a megengedett bejegyzések listája magán az adatellenőrzésen belül van megadva; ezért a lista módosításához a felhasználónak meg kell nyitnia és szerkesztenie kell az adatellenőrzést. Ez azonban nehéz lehet a tapasztalatlan felhasználók számára, vagy olyan esetekben, amikor a lehetőségek listája hosszú.

Egy másik lehetőség, hogy a listát egy elnevezett tartományba helyezzük a táblázaton belül^{(named range within the spreadsheet)} , majd megadjuk a tartomány nevét (előszóban egyenlőségjellel) az adatellenőrzés Forrás : mezőjében (ahogyan a ^(Source)2. ábrán látható^{(Figure 2)} ).

Ez a második módszer megkönnyíti a listában szereplő lehetőségek szerkesztését, de az elemek hozzáadása vagy eltávolítása problémás lehet. Mivel a megnevezett tartomány ( a példánkban a FruitChoices ) egy rögzített cellatartományra utal ($H$3:$H$10, ahogy az ábrán látható), ha további választási lehetőségeket adnak a H11 vagy az alatti cellákhoz, azok nem jelennek meg a legördülő listában. (mivel ezek a sejtek nem tartoznak a FruitChoices termékcsaládba).

Hasonlóképpen, ha például a Körte^(Pears) és Eper^{(Strawberries)} bejegyzések törlésre kerülnek, akkor azok többé nem jelennek meg a legördülő menüben, hanem két „üres” lehetőséget fog tartalmazni, mivel a legördülő menü továbbra is a teljes FruitChoices tartományra hivatkozik, beleértve a H9 és az üres cellákat is. H10 .

Ezen okok miatt, ha egy normál elnevezett tartományt használ a legördülő lista forrásaként, magát a megnevezett tartományt úgy kell szerkeszteni, hogy több vagy kevesebb cellát tartalmazzon, ha bejegyzéseket ad hozzá vagy töröl a listából.

A probléma megoldása a dinamikus^(dynamic) tartománynév használata a legördülő menük forrásaként. A dinamikatartomány neve olyan név, amely automatikusan kibővül (vagy összehúzódik), hogy pontosan megfeleljen egy adatblokk méretének a bejegyzések hozzáadásakor vagy eltávolításakor. Ehhez egy képletet^(formula) használjon a cellacímek rögzített tartománya helyett a megnevezett tartomány meghatározásához.

Hogyan állítsunk be dinamikus tartományt^{(Dynamic Range)} az Excelben^(Excel)

A normál (statikus) tartománynév egy megadott cellatartományra utal (a példánkban $H$3:$H$10, lásd alább):

A dinamikatartományt azonban egy képlet segítségével határozzák meg (lásd alább, egy dinamikus tartományneveket használó külön táblázatból):

Mielőtt elkezdené, feltétlenül töltse le Excel példafájlunkat  (a rendezési makrók le vannak tiltva).

Vizsgáljuk meg ezt a képletet részletesen. A Fruits opciók egy cellatömbben találhatók, közvetlenül a címsor alatt ( GYÜMÖLCS^(FRUITS) ). Ez a címsor egy nevet is kapott: FruitsHeading :

A Fruits^(Fruits) választások dinamikus tartományának meghatározásához használt teljes képlet a következő:

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)

A FruitsHeading^{(FruitsHeading)} arra a címsorra utal, amely egy sorral a lista első bejegyzése felett van. A 20-as szám (kétszer használva a képletben) a lista maximális mérete (sorok száma) (ez tetszés szerint módosítható).

Vegye figyelembe, hogy ebben a példában csak 8 bejegyzés található a listában, de ezek alatt is vannak üres cellák, ahol további bejegyzéseket lehet hozzáadni. A 20-as szám a teljes blokkra vonatkozik, ahol bejegyzéseket lehet tenni, nem pedig a bejegyzések tényleges számát.

Most bontsuk fel a képletet darabokra (minden egyes darab 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 az OFFSET(FruitsHeading,1,0,20,1) . Ez a 20 cellából álló blokkra hivatkozik (a FruitsHeading cella alatt), ahol a választási lehetőségek megadhatók. Ez az OFFSET funkció lényegében a következőt mondja: Kezdje a FruitsHeading cellával, menjen le 1 sorral és 0 oszlop felett, majd válasszon ki egy 20 sor hosszú és 1 oszlop széles területet. Így megkapjuk azt a 20 soros blokkot, ahol a Gyümölcsök^(Fruits) opciókat kell megadni.

A képlet következő része az ISBLANK függvény:

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(the above),0,0),0)-1,20),1)

Itt az OFFSET funkciót (a fenti magyarázatot) a „fenti”-re cseréltük (a könnyebb olvashatóság érdekében). De az ISBLANK függvény az ^(ISBLANK)OFFSET függvény által meghatározott 20 soros cellatartományon működik.

Az ISBLANK^(ISBLANK) ezután 20 IGAZ^(TRUE) és HAMIS értékből álló halmazt hoz létre, jelezve, hogy az ^(FALSE)OFFSET függvény által hivatkozott 20 soros tartomány egyes cellái üresek (üresek) vagy sem. Ebben a példában a készlet első 8 értéke HAMIS^(FALSE) , mivel az első 8 cella nem üres, az utolsó 12 érték pedig IGAZ^(TRUE) 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)

A „fenti” ismét a fent leírt ISBLANK és OFFSET funkciókra vonatkozik. Az INDEX függvény egy tömböt ad vissza, amely az ISBLANK függvény által létrehozott 20 TRUE / FALSE

Az INDEX^(INDEX) általában arra szolgál, hogy kiválasszon egy bizonyos értéket (vagy értéktartományt) egy adatblokkból egy bizonyos sor és oszlop megadásával (a blokkon belül). De ha a sor és oszlop bemenetét nullára állítja (ahogyan ez itt történik), az INDEX a teljes adatblokkot tartalmazó tömböt ad vissza.

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 MATCH függvény az első TRUE érték pozícióját adja vissza az ^(TRUE)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 , a kilencedik pedig TRUE (mivel a tartomány 9. ^sora üres).

Tehát a MATCH függvény a 9 értéket adja vissza . Ebben az esetben viszont nagyon szeretnénk tudni, hogy hány bejegyzés van a listában, ezért a képlet levon 1-et a MATCH értékből (ami az utolsó bejegyzés pozícióját adja). Tehát végül a MATCH ( TRUE ,a fenti,0)-1 ^(TRUE)8 értéket ad vissza .

A képlet következő része az IFERROR függvény:

=OFFSET(FruitsHeading,1,0,IFERROR(the above,20),1)

Az IFERROR függvény egy alternatív értéket ad vissza, ha az első megadott érték hibát eredményez. Ez a függvény azért szerepel benne, mert ha a teljes cellablokk (mind a 20 sor) meg van töltve bejegyzésekkel, a MATCH függvény hibát ad vissza.

Ez azért van így, mert azt mondjuk a MATCH függvénynek, hogy keresse az első IGAZ^(TRUE) értéket (az ISBLANK függvény értéktömbjében), de ha NINCS üres a cellák közül, akkor a teljes tömb ^(NONE)FALSE értékekkel lesz kitöltve . Ha a MATCH nem találja a célértéket ( TRUE ) a keresett tömbben, akkor hibát ad vissza.

Tehát, ha a teljes lista megtelt (és ezért a MATCH hibát ad vissza), az IFERROR függvény ehelyett 20 értéket ad vissza (tudván, hogy a listában 20 bejegyzésnek kell lennie).

Végül az OFFSET(FruitsHeading,1,0,a fenti,1)^{(OFFSET(FruitsHeading,1,0,the above,1))} visszaadja azt a tartományt, amelyet valójában keresünk: Kezdje a FruitsHeading cellával, menjen le 1 sorral és 0 oszlop felett, majd válasszon ki egy olyan területet, amely akárhány sor hosszúságú. vannak bejegyzések a listában (és 1 oszlop széles). Tehát a teljes képlet együtt azt a tartományt adja vissza, amely csak a tényleges bejegyzéseket tartalmazza (az első üres celláig).

Ha ezzel a képlettel határozza meg a legördülő menü forrását képező tartományt, akkor szabadon szerkesztheti a listát (bejegyzéseket adhat hozzá vagy távolíthat el, mindaddig, amíg a fennmaradó bejegyzések a felső cellában kezdődnek, és egymás mellett vannak), és a legördülő menü mindig tükrözi az aktuális lista (lásd 6. ábra^{(Figure 6)} ).

Az itt használt példafájlt (dinamikus listák) tartalmazza, és letöltheti erről a webhelyről. A makrók azonban nem működnek, mert a WordPress nem szereti a makrókat tartalmazó Excel könyveket.

A listablokkban lévő sorok számának megadása helyett a listablokkhoz saját tartománynevet rendelhetünk, amelyet azután egy módosított képletben használhatunk fel. A példafájlban egy második lista ( Names ) használja ezt a módszert. Itt a teljes listablokk (a „NAMES” címsor alatt, 40 sor a példafájlban) a NameBlock tartománynévvel van rendelve . A Névlista^(NamesList) meghatározásának másik képlete a következő:

=OFFSET(NamesHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(NamesBlock),0,0),0)-1,ROWS(NamesBlock)),1)

ahol a NamesBlock az OFFSET ( FruitsHeading,1,0,20,1 ), a ROWS(NamesBlock) pedig^{(ROWS(NamesBlock))} a 20-at (sorok száma) helyettesíti a korábbi képletben.

Ezért a könnyen szerkeszthető legördülő listákhoz (beleértve más, esetleg tapasztalatlan felhasználók által is) próbáljon dinamikus tartományneveket használni! És vegye figyelembe, hogy bár ez a cikk a legördülő listákra összpontosít, a dinamikus tartománynevek bárhol használhatók, ahol egy változó méretű tartományra vagy listára kell hivatkozni. Élvezd!

Use Dynamic Range Names in Excel for Flexible Dropdowns

Εxcel spreadsheets often include cell dropdowns to simplify and/or standardize data entry.  These dropdowns are created using the data validation feature to speсify a list of allowable entries.

To set up a simple dropdown list, select the cell where data will be entered, then click Data Validation (on the Data tab), select Data Validation, choose List (under Allow:), and then enter the list items (separated by commas) in the Source: field (see Figure 1).

In this type of basic dropdown, the list of allowable entries is specified within the data validation itself; therefore, to make changes to the list, the user must open and edit the data validation.  This may be difficult, however, for inexperienced users, or in cases where the list of choices is lengthy.

Another option is to place the list in a named range within the spreadsheet, and then specify that range name (prefaced with an equal sign) in the Source: field of the data validation (as shown in Figure 2).

This second method makes it easier to edit the choices in the list, but adding or removing items can be problematic.  Since the named range (FruitChoices, in our example) refers to a fixed range of cells ($H$3:$H$10 as shown), if more choices are added to the cells H11 or below, they will not show up in the dropdown (since those cells are not part of the FruitChoices range).

Likewise if, for example, the Pears and Strawberries entries are erased, they will no longer appear in the dropdown, but instead the dropdown will include two “empty” choices since the dropdown still references the entire FruitChoices range, including the empty cells H9 and H10.

For these reasons, when using a normal named range as the list source for a dropdown, the named range itself must be edited to include more or fewer cells if entries are added or deleted from the list.

A solution to this problem is to use a dynamic range name as the source for the dropdown choices.  A dynamic range name is one that automatically expands (or contracts) to exactly match the size of a block of data as entries are added or removed.  To do this, you use a formula, rather than a fixed range of cell addresses, to define the named range.

How to Setup a Dynamic Range in Excel

A normal (static) range name refers to a specified range of cells ($H$3:$H$10 in our example, see below):

But a dynamic range is defined using a formula (see below, taken from a separate spreadsheet which uses dynamic range names):

Before we get started, make sure you download our Excel example file (sort macros have been disabled).

Let’s examine this formula in detail.  The choices for Fruits are in a block of cells directly below a heading (FRUITS).  That heading is also assigned a name: FruitsHeading:

The entire formula used to define the dynamic range for the Fruits choices is:

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)

FruitsHeading refers to the heading that is one row above the first entry in the list.  The number 20 (used two times in the formula) is the maximum size (number of rows) for the list (this can be adjusted as desired).

Note that in this example, there are only 8 entries in the list, but there are also empty cells below these where additional entries could be added.  The number 20 refers to the entire block where entries can be made, not to the actual number of entries.

Now let’s break down the formula into pieces (color-coding each piece), to understand how it works:

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)

The “innermost” piece is OFFSET(FruitsHeading,1,0,20,1).  This references the block of 20 cells (underneath the FruitsHeading cell) where choices may be entered.  This OFFSET function basically says: Start at the FruitsHeading cell, go down 1 row and over 0 columns, then select an area that is 20 rows long and 1 column wide.  So that gives us the 20-row block where the Fruits choices are entered.

The next piece of the formula is the ISBLANK function:

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(the above),0,0),0)-1,20),1)

Here, the OFFSET function (explained above) has been replaced with “the above” (to make things easier to read).  But the ISBLANK function is operating on the 20-row range of cells that the OFFSET function defines.

ISBLANK then creates a set of 20 TRUE and FALSE values, indicating whether each of the individual cells in the 20-row range referenced by the OFFSET function is blank (empty) or not.  In this example, the first 8 values in the set will be FALSE since the first 8 cells are not empty and the last 12 values will be TRUE.

The next piece of the formula is the INDEX function:

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(the above,0,0),0)-1,20),1)

Again, “the above” refers to the ISBLANK and OFFSET functions described above.  The INDEX function returns an array containing the 20 TRUE / FALSE values created by the ISBLANK function.

INDEX is normally used to pick a certain value (or range of values) out of a block of data, by specifying a certain row and column (within that block).  But setting the row and column inputs to zero (as is done here) causes INDEX to return an array containing the entire block of data.

The next piece of the formula is the MATCH function:

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,the above,0)-1,20),1)

The MATCH function returns the position of the first TRUE value, within the array that’s returned by the INDEX function.  Since the first 8 entries in the list are not blank, the first 8 values in the array will be FALSE, and the ninth value will be TRUE (since the 9^th row in the range is empty).

So the MATCH function will return the value of 9.  In this case, however, we really want to know how many entries are in the list, so the formula subtracts 1 from the MATCH value (which gives the position of the last entry).  So ultimately, MATCH(TRUE,the above,0)-1 returns the value of 8.

The next piece of the formula is the IFERROR function:

=OFFSET(FruitsHeading,1,0,IFERROR(the above,20),1)

The IFERROR function returns an alternate value, if the first value specified results in an error.  This function is included since, if the entire block of cells (all 20 rows) are filled with entries, the MATCH function will return an error.

This is because we’re telling the MATCH function to look for the first TRUE value (in the array of values from the ISBLANK function), but if NONE of the cells are empty, then the entire array will be filled with FALSE values.  If MATCH cannot find the target value (TRUE) in array it is searching, it returns an error.

So, if the entire list is full (and therefore, MATCH returns an error), the IFERROR function will instead return the value of 20 (knowing that there must be 20 entries in the list).

Finally, OFFSET(FruitsHeading,1,0,the above,1) returns the range we are actually looking for:  Start at the FruitsHeading cell, go down 1 row and over 0 columns, then select an area that is however many rows long as there are entries in the list (and 1 column wide).  So the entire formula together will return the range that contains only the actual entries (down to the first empty cell).

Using this formula to define the range that is the source for the dropdown means you can freely edit the list (adding or removing entries, as long as the remaining entries start at the top cell and are contiguous) and the dropdown will always reflect the current list (see Figure 6).

The example file (Dynamic Lists) that’s been used here is included and is downloadable from this website. The macros don’t work, however, because WordPress doesn’t like Excel books with macros in them.

As an alternative to specifying the number of rows in the list block, the list block can be assigned its own range name, which can then be used in a modified formula.  In the example file, a second list (Names) uses this method.  Here, the entire list block (underneath the “NAMES” heading, 40 rows in the example file) is assigned the range name of NameBlock.  The alternate formula for defining the NamesList is then:

=OFFSET(NamesHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(NamesBlock),0,0),0)-1,ROWS(NamesBlock)),1)

where NamesBlock replaces OFFSET(FruitsHeading,1,0,20,1) and ROWS(NamesBlock) replaces the 20 (number of rows) in the earlier formula.

So, for dropdown lists which can be easily edited (including by other users who may be inexperienced), try using dynamic range names!  And note that, although this article has been focused on dropdown lists, dynamic range names can be used anywhere you need to reference a range or list that can vary in size. Enjoy!

Related posts

• Az üres sorok törlése az Excelben

• Az Excel Speak Cells funkciójának használata

• Excel-munkalap beszúrása Word-dokumentumba

• Az Excel „Mi lenne, ha” elemzésének használata

• Hogyan javítsunk ki egy sort az Excelben

• Sorok és oszlopok csoportosítása egy Excel munkalapon

• Hogyan készítsünk kördiagramot Excelben

• Használja a billentyűzetet a sormagasság és az oszlopszélesség módosításához az Excelben

• Hogyan lehet nyomon követni a függőket Excelben

• Google Táblázatok vs Microsoft Excel – Mi a különbség?

• Hogyan távolítsuk el a rácsvonalakat az Excelben

• A cellahivatkozások megőrzése képlet Excelben másolásakor

• 4 módszer a pipa használatára az Excelben

• Cellák, oszlopok és sorok egyesítése az Excelben

• Dátum szerinti rendezés az Excelben

• A Microsoft Excel Online és az Excel For Desktop közötti különbségek

• 3 módszer a cellák felosztására az Excelben

• Az Excel csatlakoztatása a MySQL-hez

• Gyorsan írd ki a számokat Wordben és Excelben

• 2 módszer az Excel transzponálási funkciójának használatára