Excel -laskentataulukot sisältävät usein avattavia soluvalikkoja tietojen syöttämisen yksinkertaistamiseksi ja/tai standardoimiseksi. Nämä avattavat valikot luodaan tietojen vahvistusominaisuuden avulla sallittujen merkintöjen luettelon määrittämiseksi.

Jos haluat luoda yksinkertaisen avattavan luettelon, valitse solu, johon tiedot syötetään, napsauta sitten Tietojen vahvistus^{(Data Validation)} ( Tiedot^(Data) - välilehdellä), valitse Tietojen vahvistus^{(Data Validation)} , valitse Luettelo^(List) ( Salli^(Allow) -kohdassa :) ja syötä sitten luettelon kohteet (eroteltuna pilkuilla). ) Lähde^(Source) : -kentässä (katso kuva 1).

Tämän tyyppisessä peruspudotusvalikossa sallittujen merkintöjen luettelo on määritetty itse tietojen validoinnissa. siksi, jotta käyttäjä voi tehdä muutoksia luetteloon, hänen on avattava ja muokattava tietojen vahvistus. Tämä voi kuitenkin olla vaikeaa kokemattomille käyttäjille tai tapauksissa, joissa vaihtoehtojen luettelo on pitkä.

Toinen vaihtoehto on sijoittaa luettelo nimetylle alueelle laskentataulukossa^{(named range within the spreadsheet)} ja määrittää sitten alueen nimi (edellä yhtäsuuruusmerkki) tietojen vahvistuksen Lähde : -kentässä (kuten ^(Source)kuvassa 2^{(Figure 2)} ).

Tämä toinen tapa helpottaa luettelon valintojen muokkaamista, mutta kohteiden lisääminen tai poistaminen voi olla ongelmallista. Koska nimetty alue ( esimerkissämme FruitChoices ) viittaa kiinteään solualueeseen ($H$3:$H$10, kuten kuvassa), jos soluihin ^{(FruitChoices)}H11 tai alle lisätään vaihtoehtoja , ne eivät näy avattavassa valikossa. (koska nämä solut eivät kuulu FruitChoices- valikoimaan).

Samoin jos esimerkiksi päärynät^(Pears) ja mansikat^{(Strawberries)} poistetaan, ne eivät enää näy avattavassa valikossa, vaan avattavassa valikossa on kaksi "tyhjää" vaihtoehtoa, koska avattava valikko viittaa edelleen koko FruitChoices-valikoimaan, mukaan lukien tyhjät solut H9 ja H10 .

Näistä syistä, kun käytetään normaalia nimettyä aluetta pudotusvalikon luettelolähteenä, itse nimettyä aluetta on muokattava siten, että se sisältää enemmän tai vähemmän soluja, jos merkintöjä lisätään tai poistetaan luettelosta.

Ratkaisu tähän ongelmaan on käyttää dynaamisen^(dynamic) alueen nimeä pudotusvalikon vaihtoehtojen lähteenä. Dynaamisen alueen nimi laajenee (tai supistuu) automaattisesti vastaamaan täsmälleen tietolohkon kokoa, kun merkintöjä lisätään tai poistetaan. Voit tehdä tämän määrittämään nimetyn alueen käyttämällä kaavaa^(formula) , eikä kiinteää soluosoitealuetta.

Dynaamisen^{(Dynamic Range)} alueen määrittäminen Excelissä^(Excel)

Normaali (staattinen) alueen nimi viittaa tiettyyn solualueeseen ($H$3:$H$10 esimerkissämme, katso alla):

Mutta dynaaminen alue määritellään kaavalla (katso alla, otettu erillisestä laskentataulukosta, jossa käytetään dynaamisten alueiden nimiä):

Ennen kuin aloitamme, varmista, että lataat Excel-esimerkkitiedostomme  (lajittelumakrot on poistettu käytöstä).

Tarkastellaan tätä kaavaa yksityiskohtaisesti. Hedelmien valinnat ovat solulohkossa suoraan otsikon ( FRUITS ) alapuolella. Tälle otsikolle on myös annettu nimi: FruitsHeading :

Koko Fruits^(Fruits) - valintojen dynaamisen alueen määrittämiseen käytetty kaava on:

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

FruitsHeading viittaa otsikkoon, joka on yhden rivin luettelon ensimmäisen kohdan yläpuolella. Numero 20 (käytetään kaavassa kahdesti) on luettelon enimmäiskoko (rivien lukumäärä) (tätä voidaan säätää halutulla tavalla).

Huomaa, että tässä esimerkissä luettelossa on vain 8 merkintää, mutta niiden alla on myös tyhjiä soluja, joihin voidaan lisätä lisämerkintöjä. Numero 20 viittaa koko lohkoon, johon voidaan syöttää, ei todellista merkintöjen määrää.

Jaetaan nyt kaava osiin (värikoodataan jokainen kappale), jotta ymmärrämme, miten se toimii:

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

"Sisin" pala on OFFSET(FruitsHeading,1,0,20,1) . Tämä viittaa 20 solun lohkoon ( FruitsHeading -solun alapuolella), johon voidaan syöttää valintoja. Tämä OFFSET -toiminto sanoo periaatteessa: Aloita FruitsHeading -solusta, mene alas 1 rivi ja yli 0 saraketta ja valitse sitten alue, joka on 20 riviä pitkä ja 1 sarakkeen leveä. Tämä antaa meille 20-rivisen lohkon, johon hedelmät^(Fruits) - valinnat syötetään.

Kaavan seuraava osa on ISBLANK - funktio:

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

Tässä OFFSET - toiminto (selvitetty yllä) on korvattu "edellä" (jotta on helpompi lukea). Mutta ISTYHJÄ^(ISBLANK) -toiminto toimii 20-rivisellä solualueella, jonka OFFSET - toiminto määrittää.

ISTYHJÄ^(ISBLANK) luo sitten joukon 20 TRUE- ja FALSE -arvoa, jotka osoittavat, onko jokainen ^(FALSE)OFFSET -funktion viittaama yksittäinen solu 20 rivin alueella tyhjä (tyhjä) vai ei. Tässä esimerkissä joukon 8 ensimmäistä arvoa ovat EPÄTOSI^(FALSE) , koska ensimmäiset 8 solua eivät ole tyhjiä ja viimeiset 12 arvoa ovat TOSI^(TRUE) .

Kaavan seuraava osa on INDEX - funktio:

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

Jälleen "edellä" viittaa yllä kuvattuihin ISBLANK- ja OFFSET - toimintoihin. INDEKSI -funktio palauttaa taulukon, joka sisältää 20 TRUE / FALSE arvoa, jotka on luotu ISTYHJÄ - ^(INDEX)funktiolla^(ISBLANK) .

INDEXia^(INDEX) käytetään tavallisesti valitsemaan tietty arvo (tai arvoalue) tietolohkosta määrittämällä tietty rivi ja sarake (tässä lohkossa). Mutta rivin ja sarakkeen syötteiden asettaminen nollaan (kuten tässä tehdään) , INDEX palauttaa taulukon, joka sisältää koko tietolohkon.

Kaavan seuraava osa on MATCH - funktio:

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

MATCH - funktio palauttaa ensimmäisen TRUE - arvon sijainnin INDEX - funktion palauttamassa taulukossa. Koska luettelon 8 ensimmäistä kohtaa eivät ole tyhjiä, taulukon ensimmäiset 8 arvoa ovat FALSE ja yhdeksäs arvo on TRUE (koska alueen 9. ^rivi on tyhjä).

Joten MATCH - funktio palauttaa arvon 9 . Tässä tapauksessa haluamme kuitenkin todella tietää kuinka monta merkintää luettelossa on, joten kaava vähentää 1 MATCH - arvosta (joka antaa viimeisen merkinnän sijainnin). Joten lopulta MATCH ( TOSI^(TRUE) ,edellä oleva,0)-1 palauttaa arvon 8 .

Kaavan seuraava osa on IFERROR - funktio:

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

IFERROR - funktio^(IFERROR) palauttaa vaihtoehtoisen arvon, jos ensimmäinen määritetty arvo aiheuttaa virheen. Tämä toiminto on mukana, koska jos koko solulohko (kaikki 20 riviä) on täytetty merkinnöillä, MATCH - funktio palauttaa virheen.

Tämä johtuu siitä, että käskemme MATCH^(MATCH) - funktiota etsimään ensimmäistä TRUE -arvoa ( ^(TRUE)ISBLANK -funktion arvojen joukosta ), mutta jos MITÄÄN^(NONE) soluista EI ole tyhjä, koko matriisi täytetään EPÄTOSI^(FALSE) arvoilla. Jos MATCH ei löydä kohdearvoa ( TRUE ) etsimästään taulukosta, se palauttaa virheen.

Joten jos koko luettelo on täynnä (ja siksi MATCH palauttaa virheen), IFERROR - funktio palauttaa sen sijaan arvon 20 (tietäen, että luettelossa on oltava 20 merkintää).

Lopuksi OFFSET(FruitsHeading,1,0,yllä oleva,1)^{(OFFSET(FruitsHeading,1,0,the above,1))} palauttaa etsimämme alueen: Aloita FruitsHeading -solusta, mene alas 1 rivi ja yli 0 saraketta ja valitse sitten alue, joka on kuinka monta riviä pitkä tahansa. luettelossa on merkintöjä (ja 1 sarakkeen levyinen). Joten koko kaava yhdessä palauttaa alueen, joka sisältää vain todelliset merkinnät (ensimmäiseen tyhjään soluun asti).

Kun käytät tätä kaavaa määrittääksesi alueen, joka on avattavan valikon lähde, voit vapaasti muokata luetteloa (lisäät tai poistat merkintöjä, kunhan loput merkinnät alkavat yläsolusta ja ovat vierekkäisiä) ja pudotusvalikko heijastaa aina nykyistä luettelo (katso kuva 6^{(Figure 6)} ).

Tässä käytetty esimerkkitiedosto (dynaamiset luettelot) on mukana ja on ladattavissa tältä verkkosivustolta. Makrot eivät kuitenkaan toimi, koska WordPress ei pidä Excel -kirjoista, joissa on makroja.

Vaihtoehtona listalohkon rivien määrän määrittämiselle listalohkolle voidaan antaa oma alueen nimi, jota voidaan sitten käyttää muokatussa kaavassa. Esimerkkitiedostossa toinen luettelo ( Nimet^(Names) ) käyttää tätä menetelmää. Tässä koko luettelolohkolle ("NAMES" -otsikon alla, 40 riviä esimerkkitiedostossa) on määritetty alueen nimi NameBlock . Vaihtoehtoinen kaava nimiluettelon^(NamesList) määrittämiseksi on sitten:

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

jossa NamesBlock korvaa OFFSET ( FruitsHeading,1,0,20,1 ) ja ROWS (NamesBlock) korvaa 20 (rivien lukumäärä) aikaisemmassa kaavassa.

Joten kokeile dynaamisten alueiden nimiä avattavassa luettelossa, jota voidaan muokata helposti (mukaan lukien muut kokemattomat käyttäjät). Ja huomaa, että vaikka tämä artikkeli on keskittynyt avattaviin luetteloihin, dynaamisten alueiden nimiä voidaan käyttää missä tahansa, jossa haluat viitata alueeseen tai luetteloon, jonka koko voi vaihdella. Nauttia!

Use Dynamic Range Names in Excel for Flexible Dropdowns

Excel spreadsheets often include cell dropdowns to simplify and/or standardize data entry.  These droрdowns are created υsing the data validаtion feature to ѕpecifу a list of allowable entrieѕ.

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

• Kuinka poistaa tyhjiä rivejä Excelissä

• Excelin Speak Cells -ominaisuuden käyttäminen

• Kuinka lisätä Excel-laskentataulukko Word-dokumenttiin

• Excelin mitä jos -analyysin käyttäminen

• Kuinka korjata rivi Excelissä

• Mikä on VBA-taulukko Excelissä ja kuinka ohjelmoida yksi

• Yksisarakkeen ja monisarakkeen tietojen peruslajittelu Excel-laskentataulukoissa

• Google Sheets vs Microsoft Excel – mitkä ovat erot?

• Milloin käyttää Index-Matchia VLOOKUPin sijaan Excelissä

• Kuinka käyttää PMT-funktiota Excelissä

• Excelin mitä jos -analyysitavoitteenhakutyökalun käyttäminen

• Käytä näppäimistöä rivin korkeuden ja sarakkeen leveyden muuttamiseen Excelissä

• Keskitä laskentataulukon tiedot Exceliin tulostusta varten

• Excel-tiedoston jakaminen helppoa yhteistyötä varten

• Kuinka siirtää sarakkeita Excelissä

• Kuinka yhdistää tietoja useisiin Excel-tiedostoihin

• Kuinka korjata #N/A-virheet Excel-kaavoissa, kuten VLOOKUP

• Ryhmittele rivit ja sarakkeet Excel-laskentataulukossa

• Kuinka käyttää Flash-täyttöä Excelissä

• Kuinka lisätä nopeasti useita rivejä Exceliin