Екцел^(Excel) табеле често укључују падајуће меније ћелија како би се поједноставио и/или стандардизовао унос података. Ови падајући мении су креирани помоћу функције провере ваљаности података да би се навела листа дозвољених уноса.

Да бисте поставили једноставну падајућу листу, изаберите ћелију у коју ће се уносити подаци, затим кликните на Валидација података^{(Data Validation)} (на картици Подаци^(Data) ), изаберите Валидација података^{(Data Validation)} , изаберите Листа^(List) (под Дозволи^(Allow) :), а затим унесите ставке листе (одвојене зарезима). ) у пољу Извор^(Source) : (погледајте слику 1).

У овој врсти основног падајућег менија, листа дозвољених уноса је наведена унутар саме провере података; стога, да би извршио измене на листи, корисник мора да отвори и уреди валидацију података. Ово може бити тешко, међутим, за неискусне кориснике или у случајевима када је листа избора дуга.

Друга опција је да поставите листу у именовани опсег унутар табеле^{(named range within the spreadsheet)} , а затим наведете име тог опсега (са предзнаком једнакости) у пољу Извор^(Source) : валидације података (као што је приказано на слици 2^{(Figure 2)} ).

Овај други метод олакшава уређивање избора на листи, али додавање или уклањање ставки може бити проблематично. Пошто се именовани опсег ( ФруитЦхоицес^{(FruitChoices)} , у нашем примеру) односи на фиксни опсег ћелија ($Х$3:$Х$10 као што је приказано), ако се у ћелије Х11^(H11) или испод дода више избора, оне се неће појавити у падајућем менију (пошто те ћелије нису део асортимана ФруитЦхоицес^{(FruitChoices)} ).

Слично томе, ако се, на пример, избришу уноси крушке^(Pears) и јагоде , они се више неће појављивати у падајућем менију, већ ће уместо тога падајући мени садржати два „празна“ избора пошто падајући мени и даље упућује на цео опсег ФруитЦхоицес, укључујући празне ћелије Х9 и ^{(Strawberries)}Х10^(H10) .

Из ових разлога, када се користи нормално именовани опсег као извор листе за падајући мени, сам именовани опсег мора да се уреди тако да укључује више или мање ћелија ако се уноси додају или бришу са листе.

Решење овог проблема је коришћење имена динамичког^(dynamic) опсега као извора за изборе у падајућем менију. Назив динамичког опсега је онај који се аутоматски шири (или скупља) како би тачно одговарао величини блока података како се уноси додају или уклањају. Да бисте то урадили, користите формулу^(formula) , а не фиксни опсег адреса ћелија, да дефинишете именовани опсег.

Како подесити динамички опсег^{(Dynamic Range)} у Екцелу^(Excel)

Нормално (статичко) име опсега се односи на одређени опсег ћелија ($Х$3:$Х$10 у нашем примеру, погледајте испод):

Али динамички опсег се дефинише помоћу формуле (погледајте доле, преузету из посебне табеле која користи називе динамичког опсега):

Пре него што почнемо, уверите се да сте преузели нашу Екцел датотеку примера  (макрои за сортирање су онемогућени).

Хајде да детаљно испитамо ову формулу. Избори за воће су у блоку ћелија директно испод наслова ( ВОЋЕ^(FRUITS) ). Том наслову је такође додељено име: ФруитсХеадинг^{(FruitsHeading)} :

Цела формула која се користи за дефинисање динамичког опсега за избор воћа^(Fruits) је:

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

ФруитсХеадинг^{(FruitsHeading)} се односи на наслов који је један ред изнад првог уноса на листи. Број 20 (користи се два пута у формули) је максимална величина (број редова) за листу (ово се може подесити по жељи).

Имајте на уму да у овом примеру постоји само 8 уноса на листи, али постоје и празне ћелије испод њих у које се могу додати додатни уноси. Број 20 се односи на цео блок у који се могу уносити, а не на стварни број уноса.

Сада хајде да разбијемо формулу на делове (кодирање бојом сваког дела) да бисмо разумели како функционише:

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

„Најунутрашњији“ комад је ОФФСЕТ (ФруитсХеадинг,1,0,20,1)^{(OFFSET(FruitsHeading,1,0,20,1))} . Ово се односи на блок од 20 ћелија (испод ћелије ФруитсХеадинг^{(FruitsHeading)} ) где се могу унети избори. Ова функција ОФФСЕТ^(OFFSET) у основи каже: Почните од ћелије ФруитсХеадинг^{(FruitsHeading)} , идите доле за 1 ред и преко 0 колона, а затим изаберите област која је дугачка 20 редова и широка 1 колона. То нам даје блок од 20 редова у који се уносе избори воћа .^(Fruits)

Следећи део формуле је ИСБЛАНК^(ISBLANK) функција:

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

Овде је функција ОФФСЕТ^(OFFSET) (објашњена горе) замењена са „горе“ (да би ствари биле лакше за читање). Али функција ИСБЛАНК^(ISBLANK) ради на опсегу ћелија од 20 редова које дефинише функција ОФФСЕТ .^(OFFSET)

ИСБЛАНК^(ISBLANK) затим креира скуп од 20 вредности ТРУЕ^(TRUE) и ФАЛСЕ^(FALSE) , показујући да ли је свака од појединачних ћелија у опсегу од 20 редова на које упућује функција ОФФСЕТ^(OFFSET) празна (празна) или није. У овом примеру, првих 8 вредности у скупу ће бити ФАЛСЕ^(FALSE) јер првих 8 ћелија није празних, а последњих 12 вредности ће бити ТРУЕ^(TRUE) .

Следећи део формуле је функција ИНДЕКС^(INDEX) :

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

Опет, „горе“ се односи на горе описане функције ИСБЛАНК^(ISBLANK) и ОФФСЕТ^(OFFSET) . Функција ИНДЕКС^(INDEX) враћа низ који садржи 20 вредности TRUE / FALSE креираних помоћу функције ИСБЛАНК^(ISBLANK) .

ИНДЕКС^(INDEX) се обично користи за одабир одређене вредности (или опсега вредности) из блока података, навођењем одређеног реда и колоне (унутар тог блока). Али постављање уноса реда и колоне на нулу (као што је овде урађено) узрокује да ИНДЕКС^(INDEX) врати низ који садржи цео блок података.

Следећи део формуле је функција МАТЦХ^(MATCH) :

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

Функција МАТЦХ^(MATCH) враћа позицију прве вредности ТРУЕ^(TRUE) , унутар низа који је вратила функција ИНДЕКС^(INDEX) . Пошто првих 8 уноса на листи нису празни, првих 8 вредности у низу ће бити ФАЛСЕ^(FALSE) , а девета вредност ће бити ТРУЕ^(TRUE) (пошто је 9. ^ред у опсегу празан).

Дакле, функција МАТЦХ^(MATCH) ће вратити вредност 9 . У овом случају, међутим, заиста желимо да знамо колико уноса има на листи, тако да формула одузима 1 од вредности МАТЦХ^(MATCH) (што даје позицију последњег уноса). Дакле, на крају, МАТЦХ^(MATCH) ( ТРУЕ^(TRUE) ,горе,0)-1 враћа вредност 8 .

Следећи део формуле је функција ИФЕРРОР^(IFERROR) :

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

Функција ИФЕРРОР^(IFERROR) враћа алтернативну вредност, ако прва наведена вредност резултира грешком. Ова функција је укључена јер, ако је цео блок ћелија (свих 20 редова) попуњен уносима, функција МАТЦХ^(MATCH) ће вратити грешку.

То је зато што ми говоримо функцији МАТЦХ^(MATCH) да тражи прву вредност ТРУЕ^(TRUE) (у низу вредности из функције ИСБЛАНК^(ISBLANK) ), али ако ниједна^(NONE) ћелија није празна, онда ће цео низ бити попуњен са ФАЛСЕ^(FALSE) вредностима. Ако МАТЦХ^(MATCH) не може да пронађе циљну вредност ( ТРУЕ^(TRUE) ) у низу који претражује, враћа грешку.

Дакле, ако је цела листа пуна (и стога, МАТЦХ^(MATCH) враћа грешку), функција ИФЕРРОР^(IFERROR) ће уместо тога вратити вредност од 20 (знајући да на листи мора бити 20 уноса).

Коначно, ОФФСЕТ(ФруитсХеадинг,1,0,горе,1)^{(OFFSET(FruitsHeading,1,0,the above,1))} враћа опсег који заправо тражимо: Почните од ћелије ФруитсХеадинг^{(FruitsHeading)} , идите доле за 1 ред и преко 0 колона, а затим изаберите област која има колико год редова дугачка постоје уноси у листи (и ширине 1 колоне). Дакле, цела формула заједно ће вратити опсег који садржи само стварне уносе (до прве празне ћелије).

Коришћење ове формуле за дефинисање опсега који је извор за падајући мени значи да можете слободно уређивати листу (додавање или уклањање уноса, све док преостали уноси почињу у горњој ћелији и су узастопни) и падајући мени ће увек одражавати тренутни листа (видети слику 6^{(Figure 6)} ).

Пример фајла (динамичке листе) који је овде коришћен је укључен и може се преузети са ове веб локације. Макрои не функционишу, међутим, зато што ВордПресс^(WordPress) не воли Екцел^(Excel) књиге са макроима у њима.

Као алтернатива одређивању броја редова у блоку листе, блоку листе се може доделити сопствено име опсега, које се затим може користити у модификованој формули. У датотеци примера, друга листа ( Имена^(Names) ) користи овај метод. Овде је целом блоку листе (испод наслова „НАМЕС“, 40 редова у датотеци примера) додељено име опсега НамеБлоцк^(NameBlock) . Алтернативна формула за дефинисање листе имена^(NamesList) је тада:

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

где НамесБлоцк^(NamesBlock) замењује ОФФСЕТ^(OFFSET) ( ФруитсХеадинг,1,0,20,1^{(FruitsHeading,1,0,20,1)} ) и РОВС(НамесБлоцк)^{(ROWS(NamesBlock))} замењује 20 (број редова) у ранијој формули.

Дакле, за падајуће листе које се могу лако уређивати (укључујући и друге кориснике који можда нису искусни), покушајте да користите имена динамичког опсега! И имајте на уму да, иако је овај чланак фокусиран на падајуће листе, имена динамичког опсега могу да се користе свуда где треба да референцирате опсег или листу који могу да варирају по величини. Уживати!

Use Dynamic Range Names in Excel for Flexible Dropdowns

Excel sрreadsheets often inсlude cell dropdowns to ѕimplify and/or standardize data entry.  These dropdowns are created using the data validation feature to specify a list of аllowable 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

• Како избрисати празне линије у Екцелу

• Како користити Екцел-ову функцију говорних ћелија

• Како уметнути Екцел радни лист у Ворд документ

• Како користити Екцел-ову анализу шта ако

• Како поправити ред у Екцелу

• Како написати ИФ формулу/изјаву у Екцелу

• Како да поправите #Н/А грешке у Екцел формулама као што је ВЛООКУП

• Како делити Екцел датотеку за лаку сарадњу

• Групирајте редове и колоне у Екцел радном листу

• Како распоредити по абецеди у Екцелу

• Користите тастатуру да промените висину реда и ширину колоне у Екцел-у

• Како уклонити линије мреже у Екцелу

• Како направити ВБА макро или скрипту у Екцелу

• Коришћење Екцел-овог алата за тражење циља за анализу шта ако

• Користите Спике за исецање и лепљење више текстуалних ставки у Ворд

• 2 начина за коришћење Екцел-ове функције транспоновања

• Шта је ВБА низ у Екцел-у и како га програмирати

• Како сортирати по датуму у Екцелу

• Како направити тортни графикон у Екцелу

• 4 начина да претворите Екцел у Гоогле табеле