Excel proračunske tablice često uključuju padajuće izbornike ćelija kako bi se pojednostavio i/ili standardizirao unos podataka. Ovi padajući izborniki kreirani su pomoću značajke provjere valjanosti podataka za određivanje popisa dopuštenih unosa.

Da biste postavili jednostavan padajući popis, odaberite ćeliju u koju će se unositi podaci, zatim kliknite Provjera valjanosti podataka^{(Data Validation)} (na kartici Podaci^(Data) ), odaberite Provjera valjanosti podataka^{(Data Validation)} , odaberite Popis^(List) (pod Dopusti^(Allow) :), a zatim unesite stavke popisa (odvojene zarezima ) u polju Izvor^(Source) : (vidi sliku 1).

U ovoj vrsti osnovnog padajućeg izbornika, popis dopuštenih unosa naveden je unutar same provjere valjanosti podataka; stoga, da bi izvršio izmjene na popisu, korisnik mora otvoriti i urediti provjeru valjanosti podataka. To može biti teško, međutim, za neiskusne korisnike ili u slučajevima kada je popis izbora podugačak.

Druga je mogućnost smjestiti popis u imenovani raspon unutar proračunske tablice^{(named range within the spreadsheet)} , a zatim navesti taj naziv raspona (s predznakom jednakosti) u polju Izvor^(Source) : provjere valjanosti podataka (kao što je prikazano na slici 2^{(Figure 2)} ).

Ova druga metoda olakšava uređivanje izbora na popisu, ali dodavanje ili uklanjanje stavki može biti problematično. Budući da se imenovani raspon ( FruitChoices , u našem primjeru) odnosi na fiksni raspon ćelija ($H$3:$H$10 kao što je prikazano), ako se u ćelije H11 ili ispod doda više izbora, one se neće pojaviti u padajućem izborniku (budući da te ćelije nisu dio asortimana FruitChoices ).

Isto tako, ako se, na primjer, izbrišu unosi Kruške^(Pears) i jagode , oni se više neće pojavljivati ​​u padajućem izborniku, već će umjesto toga padajući izbornik uključivati ​​dva "prazna" izbora jer padajući izbornik i dalje upućuje na cijeli raspon FruitChoices, uključujući prazne ćelije H9 i ^{(Strawberries)}H10 .

Iz tih razloga, kada koristite uobičajeni imenovani raspon kao izvor popisa za padajući izbornik, sam imenovani raspon mora se urediti tako da uključuje više ili manje ćelija ako se unosi dodaju ili brišu s popisa.

Rješenje ovog problema je korištenje imena dinamičkog^(dynamic) raspona kao izvora za padajuće izbore. Naziv dinamičkog raspona je onaj koji se automatski širi (ili skuplja) kako bi točno odgovarao veličini bloka podataka kako se unosi dodaju ili uklanjaju. Da biste to učinili, koristite formulu^(formula) , umjesto fiksnog raspona adresa ćelija, za definiranje imenovanog raspona.

Kako postaviti dinamički raspon^{(Dynamic Range)} u Excelu^(Excel)

Normalni (statički) naziv raspona odnosi se na određeni raspon ćelija ($H$3:$H$10 u našem primjeru, pogledajte dolje):

Ali dinamički raspon definira se pomoću formule (pogledajte dolje, preuzetu iz zasebne proračunske tablice koja koristi nazive dinamičkog raspona):

Prije nego što počnemo, provjerite jeste li preuzeli našu Excel datoteku primjera  (makronaredbe za sortiranje su onemogućene).

Proučimo ovu formulu detaljno. Izbori za Voće su u bloku ćelija neposredno ispod naslova ( VOĆE^(FRUITS) ). Tom naslovu je također dodijeljen naziv: FruitsHeading :

Cijela formula koja se koristi za definiranje dinamičkog raspona za izbor voća^(Fruits) je:

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

FruitsHeading se odnosi na naslov koji je jedan red iznad prvog unosa na popisu. Broj 20 (koristi se dva puta u formuli) je maksimalna veličina (broj redaka) za popis (ovo se može prilagoditi po želji).

Imajte na umu da u ovom primjeru postoji samo 8 unosa na popisu, ali postoje i prazne ćelije ispod njih u koje se mogu dodati dodatni unosi. Broj 20 odnosi se na cijeli blok u koji se mogu unositi, a ne na stvarni broj unosa.

Sada raščlanimo formulu na dijelove (kodiranje bojom svakog dijela) da bismo razumjeli kako funkcionira:

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

"Najnutarnji" komad je OFFSET (FruitsHeading,1,0,20,1) . Ovo upućuje na blok od 20 ćelija (ispod ćelije FruitsHeading ) gdje se mogu unijeti izbori. Ova funkcija OFFSET u osnovi kaže: Počnite od ćelije FruitsHeading , idite dolje za 1 red i preko 0 stupaca, zatim odaberite područje koje je dugačko 20 redaka i široko 1 stupac. To nam daje blok od 20 redaka u koji se unose izbori voća .^(Fruits)

Sljedeći dio formule je funkcija ISBLANK :

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

Ovdje je funkcija OFFSET (objašnjena gore) zamijenjena s "gore" (kako bi se stvari lakše čitale). Ali funkcija ISBLANK djeluje na rasponu ćelija od 20 redaka koje definira funkcija OFFSET .

ISBLANK zatim stvara skup od 20 vrijednosti TRUE i FALSE , pokazujući je li svaka od pojedinačnih ćelija u rasponu od 20 redaka na koje upućuje funkcija OFFSET prazna (prazna) ili nije. ^(OFFSET)U ovom primjeru, prvih 8 vrijednosti u skupu bit će FALSE jer prvih 8 ćelija nije praznih, a posljednjih 12 vrijednosti bit će TRUE .

Sljedeći dio formule je funkcija INDEX :

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

Opet, "gore" se odnosi na gore opisane funkcije ISBLANK i OFFSET . Funkcija INDEX vraća niz koji sadrži 20 vrijednosti TRUE / FALSE koje je kreirala funkcija ISBLANK .

INDEX se obično koristi za odabir određene vrijednosti (ili raspona vrijednosti) iz bloka podataka, navođenjem određenog retka i stupca (unutar tog bloka). Ali postavljanje unosa retka i stupca na nulu (kao što je ovdje učinjeno) uzrokuje da INDEX vrati niz koji sadrži cijeli blok podataka.

Sljedeći dio formule je MATCH funkcija:

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

Funkcija MATCH vraća poziciju prve vrijednosti TRUE , unutar niza koji vraća funkcija INDEX . Budući da prvih 8 unosa na popisu nije prazno, prvih 8 vrijednosti u nizu bit će FALSE , a deveta vrijednost će biti TRUE (budući da je 9. ^redak u rasponu prazan).

Dakle, funkcija MATCH će vratiti vrijednost 9 . U ovom slučaju, međutim, stvarno želimo znati koliko je unosa na popisu, pa formula oduzima 1 od vrijednosti MATCH (što daje poziciju posljednjeg unosa). Dakle, u konačnici, MATCH ( TRUE ,gore,0)-1 vraća vrijednost 8 .

Sljedeći dio formule je funkcija IFERROR :

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

Funkcija IFERROR vraća zamjensku vrijednost, ako prva navedena vrijednost rezultira pogreškom. Ova funkcija je uključena jer, ako je cijeli blok ćelija (svih 20 redaka) popunjen unosima, funkcija MATCH će vratiti pogrešku.

To je zato što mi govorimo funkciji MATCH da traži prvu vrijednost TRUE (u nizu vrijednosti iz funkcije ISBLANK ), ali ako NIJEDNA^(NONE) ćelija nije prazna, tada će cijeli niz biti popunjen s FALSE vrijednostima. Ako MATCH ne može pronaći ciljnu vrijednost ( TRUE ) u nizu koji pretražuje, vraća pogrešku.

Dakle, ako je cijeli popis pun (i stoga MATCH vraća pogrešku), funkcija IFERROR će umjesto toga vratiti vrijednost 20 (znajući da na popisu mora biti 20 unosa).

Konačno, OFFSET(FruitsHeading,1,0,gore,1)^{(OFFSET(FruitsHeading,1,0,the above,1))} vraća raspon koji zapravo tražimo: Počnite od ćelije FruitsHeading , idite dolje za 1 red i preko 0 stupaca, zatim odaberite područje koje ima koliko god redaka dugačko postoje unosi na popisu (i širine 1 stupca). Dakle, cijela formula zajedno će vratiti raspon koji sadrži samo stvarne unose (do prve prazne ćelije).

Korištenje ove formule za definiranje raspona koji je izvor za padajući izbornik znači da možete slobodno uređivati ​​popis (dodavanje ili uklanjanje unosa, sve dok preostali unosi počinju u gornjoj ćeliji i su susjedni), a padajući izbornik uvijek će odražavati trenutni popis (vidi sliku 6^{(Figure 6)} ).

Primjer datoteke (Dinamički popisi) koja je ovdje korištena je uključena i može se preuzeti s ove web stranice. Međutim, makronaredbe ne rade jer WordPress ne voli Excel knjige s makronaredbama u njima.

Kao alternativa određivanju broja redaka u bloku popisa, bloku popisa može se dodijeliti vlastiti naziv raspona, koji se zatim može koristiti u modificiranoj formuli. U datoteci primjera, drugi popis ( Imena^(Names) ) koristi ovu metodu. Ovdje je cijelom bloku popisa (ispod naslova “NAMES”, 40 redaka u datoteci primjera) dodijeljen naziv raspona NameBlock . Alternativna formula za definiranje popisa imena^(NamesList) je tada:

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

gdje NamesBlock zamjenjuje OFFSET ( FruitsHeading,1,0,20,1 ) i ROWS(NamesBlock) zamjenjuje 20 (broj redaka) u ranijoj formuli.

Dakle, za padajuće popise koji se mogu lako uređivati ​​(uključujući druge korisnike koji možda nemaju iskustva), pokušajte koristiti nazive dinamičkog raspona! I imajte na umu da, iako je ovaj članak bio usredotočen na padajuće popise, nazivi dinamičkog raspona mogu se koristiti bilo gdje gdje trebate referencirati raspon ili popis koji može varirati u veličini. Uživati!

Use Dynamic Range Names in Excel for Flexible Dropdowns

Εxcel spreadsheеtѕ often include cell dropdowns to simplify and/or standardize data entry.  These dropdowns are created using the data validation feature to specify а list of allowable еntries.

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

• Kako izbrisati prazne retke u Excelu

• Kako koristiti Excelovu značajku govornih ćelija

• Kako umetnuti Excel radni list u Word dokument

• Kako koristiti Excelovu analizu "što ako".

• Kako popraviti redak u Excelu

• Kako stvoriti VBA makro ili skriptu u Excelu

• Kako pretraživati ​​u Excelu

• Kako premjestiti stupce u Excelu

• Korištenje Excelovog alata za traženje cilja analize Što ako

• Kako brzo umetnuti više redaka u Excel

• Kako napraviti tortni grafikon u Excelu

• Kako umetnuti CSV ili TSV u Excel radni list

• Sačuvajte reference ćelija prilikom kopiranja formule u Excelu

• Centrirajte svoje podatke radnog lista u Excelu za ispis

• Kako spojiti podatke u više Excel datoteka

• Povezivanje Excela s MySQL

• 4 načina za pretvaranje Excela u Google tablice

• Kada koristiti Index-Match umjesto VLOOKUP-a u Excelu

• Koristite tipkovnicu za promjenu visine retka i širine stupca u Excelu

• Zašto biste trebali koristiti imenovane raspone u Excelu