Xaashiyaha xaashida ee Excel^(Excel) inta badan waxaa ku jira hoos u dhaca unugyada si loo fududeeyo iyo/ama loo habeeyo gelitaanka xogta. Hoos-u-dhacyadan waxa la abuuraa iyadoo la isticmaalayo astaanta xaqiijinta xogta si loo caddeeyo liiska gelida la oggol yahay.

Si aad u dejiso liiska hoos u dhaca ee fudud, dooro unugga xogta la gelin doono, ka bacdi dhagsii Xaqiijinta Xogta^{(Data Validation)} (ku taal tab Data ), dooro ^(Data)Xaqiijinta Xogta^{(Data Validation)} , dooro Liiska^(List) (hoos Oggolow^(Allow) :) ka dibna geli shayada liiska ) ee Isha^(Source) : beerta (eeg sawirka 1).

Noocan ah hoos u dhigista aasaasiga ah, liiska galitaanka la ogol yahay waxaa lagu qeexay ansaxinta xogta lafteeda; sidaas darteed, si liiska wax looga beddelo, isticmaaluhu waa inuu furo oo tafatiro ansixinta xogta. Tani waxay ku adkaan kartaa, si kastaba ha ahaatee, isticmaalayaasha aan khibradda lahayn, ama xaaladaha ay liiska doorashadu dheer yihiin.

Ikhtiyaar kale ayaa ah in la dhigo liiska magacyo kala duwan oo ku dhex jira xaashida faafinta^{(named range within the spreadsheet)} , ka dibna ku qeex magaca xadka (oo horay loogu soo bandhigay calaamad siman) Isha^(Source) : goobta ansaxinta xogta (sida ku cad sawirka 2^{(Figure 2)} ).

Habkan labaad waxa uu sahlayaa in wax laga beddelo xulashooyinka liiska ku jira, laakiin ku darista ama ka saarista walxaha waxay noqon kartaa dhibaato. Maaddaama tirada la magacaabay ( FruitChoices , tusaale ahaan) waxay tixraacaysaa unugyo go'an ($ H $ 3: $ H $ 10 sida lagu muujiyay), haddii doorashooyin badan lagu daro unugyada H11 ama ka hooseeya, kama soo muuqan doonaan hoos u dhaca. (maadaama unugyadu aanay ka mid ahayn noocyada kala duwan ee FruitChoices ).

Sidoo kale haddii, tusaale ahaan, gelitaanka isteroberi^(Pears) iyo strawberries la tirtiro, mar dambe kama soo muuqan doonaan hoos u dhaca, laakiin taa beddelkeeda hoos u dhigista waxaa ku jiri doona laba doorasho "madhan" maadaama hoos u dhigistu ay weli tixraacayso dhammaan noocyada FruitChoices, oo ay ku jiraan unugyada madhan H9 iyo H10 .

Sababahan dartood, marka la isticmaalayo kala duwanaansho magaceed caadi ah sida isha liiska ee hoos u dhaca, tirada la magacaabay lafteeda waa in la tafatiro si loogu daro unugyo badan ama ka yar haddii liiska lagu daro ama laga tirtiro liiska.

Xalka dhibaatadan waa in la isticmaalo magac kala duwan oo firfircoon^(dynamic) oo ah isha xulashada hoos u dhaca. Magaca kala duwanaanshaha firfircoon waa mid si toos ah u balaadhiya (ama qandaraas) si uu si sax ah ugu dhigmo cabbirka xogta marka la gelinayo ama la saarayo. Si tan loo sameeyo, waxaad isticmaashaa qaacido^(formula) , halkii aad ka isticmaali lahayd tiro go'an oo cinwaanno unug ah, si aad u qeexdo tirada la magacaabay.

Sida loo Dejiyo Range Dynamic^{(Dynamic Range)} in Excel

Magaca kala duwanaanshaha caadiga ah (ma taagan) waxa loola jeedaa tiro cayiman oo unugyo ah ($H$3:$H$10 tusaale ahaan, hoos eeg):

Laakiin kala duwanaansho firfircoon ayaa lagu qeexaa iyadoo la isticmaalayo qaacido (hoos eeg, oo laga soo qaatay xaashi kala duwan oo isticmaaleysa magacyo kala duwan oo firfircoon):

Kahor intaanan bilaabin, hubi inaad soo dejisay faylkayaga tusaalaha Excel  (nooca macros waa naafo).

Aynu si faahfaahsan u baarno qaacidada. Xulashada Miraha waxay ku dhex jiraan unugyo si toos ah uga hooseeya ciwaanka ( FRUIT^(FRUITS) ). Ciwaankaas sidoo kale waxaa loo qoondeeyay magac: FruitsHeading :

Dhammaan caanaha loo isticmaalo in lagu qeexo kala duwanaanshaha firfircoon ee xulashada Miraha waa:^(Fruits)

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

FruitsHeading waxa loola jeedaa ciwaanka hal saf ka sarreeya gelitaanka koowaad ee liiska. Tirada 20 (laba jeer lagu isticmaalo qaacidada) waa cabbirka ugu badan (tirada safafka) ee liiska (tan waxaa loo hagaajin karaa sida la rabo).

Ogsoonow in tusaalahan, ay jiraan 8 gelis oo keliya oo liiska ku jira, laakiin waxa kale oo jira unugyo madhan oo kuwan ka hooseeya kuwaas oo wax-gelin dheeraad ah lagu dari karo. Lambarka 20 waxa uu tilmaamayaa baloogga oo dhan halka wax laga soo gelin karo, ee maaha tirada dhabta ah ee la galiyay.

Hadda aynu u kala qaadno qaacidada qaybo (midab-codeynta qayb kasta), si aan u fahanno sida ay u shaqeyso:

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

Qaybta "gudaha" waa OFFSET(FruitsHeading,1,0,20,1) . Tani waxay tixraacaysaa xayndaabka 20 unug (hoosta unugga FruitsHeading ) halkaasoo xulashooyinka la geli karo. Shaqadan OFFSET waxay asal ahaan tiraahdaa: Ka bilow unugga Headheading^{(FruitsHeading)} , hoos u dhaadhac 1 saf iyo in ka badan 0 tiirar, dabadeed dooro meel dhererkeedu yahay 20 saf iyo 1 tiir oo ballaaran. Markaa taasi waxay ina siinaysaa baloogga 20- saf ee halka la geliyo xulashooyinka Miraha .^(Fruits)

Qaybta xigta ee qaaciddada waa shaqada ISLANK^(ISBLANK) :

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

Halkan, shaqada OFFSET (kor lagu sharaxay) waxaa lagu bedelay "kuwa sare" (si wax loo akhriyo). Laakin shaqada ISBLANK waxay ku shaqaynaysaa 20- saf ee unugyada kuwaas oo shaqada OFFSET ay qeexdo.

ISBLANK waxay markaas abuurtaa 20 qiyam ah oo RUN^(TRUE) ah iyo BEEN AH^(FALSE) , taasoo tusinaysa in mid kasta oo ka mid ah unugyada gaarka ah ee 20- saf ee kala duwan ee lagu tilmaamay shaqada OFFSET ay faaruq yihiin iyo in kale. Tusaalahan, 8-da qiyam ee ugu horreeya ee jaantusku waxay noqon doonaan BEEN^(FALSE) maadaama 8-da unug ee hore aanay faaruqin 12-ka qiyam ee u dambeeyaa waxay noqonayaan RUN^(TRUE) .

Qaybta xigta ee qaaciddada waa shaqada INDEX :

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

Mar labaad, "kuwa sare" waxa ay tilmaamaysaa ISBLANK iyo hawlaha OFFSET ee kor lagu tilmaamay. Hawsha INDEX waxa ay soo celisaa hannaan ka kooban 20 qiyam oo TRUE / FALSE ah oo uu abuuray shaqada ISBLANK .

INDEX waxaa sida caadiga ah loo isticmaalaa in lagu soo xulo qiime gaar ah (ama kala duwanaanta qiyamka) ee xogta block, iyadoo lagu qeexayo saf iyo tiir gaar ah (gudaha blockas). Laakin dejinta safafka iyo tiirka wax-soo-gelinta eber (sida halkan lagu sameeyo) waxay sababtaa INDEX inay soo celiso array ka kooban dhammaan xogta.

Qaybta xigta ee qaaciddada waa shaqada MATCH :

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

Shaqada MATCH waxay soo celisaa booska qiimihii ugu horeeyay ee TRUE , gudaha shaxanka ay soo celisay shaqada INDEX . Maaddaama 8-da gelis ee ugu horreeya ee liisku aanay bannaanayn, 8-da qiyam ee ugu horreeya ee shaxda ayaa noqon doona BEEN^(FALSE) , qiimaha sagaalaadna waxa uu noqon doonaa RUN^(TRUE) (maadaama safka 9aad ee ^cabbirku bannaan yahay).

Markaa shaqada MATCH waxay soo celin doontaa qiimaha 9 . Xaaladdan oo kale, si kastaba ha ahaatee, waxaan runtii rabnaa inaan ogaano inta liiska ku jirta liiska, markaa qaaciddada ayaa ka soo baxda 1 qiimaha MATCH (taas oo siinaysa booska gelitaanka ugu dambeeya). Markaa ugu dambeyntii, MATCH ( RUN^(TRUE) , kor ku xusan, 0) -1 waxay soo celisaa qiimaha 8 .

Qaybta xigta ee qaaciddada waa shaqada IFERROR :

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

Shaqada IFERROR waxay soo celisaa qiime kale, haddii qiimaha ugu horreeya ee la cayimay uu keeno qalad. Shaqadan waxaa lagu soo daray, haddii unugyada oo dhan (dhammaan 20 saf) ay ka buuxaan gelinta, shaqada MATCH waxay soo celin doontaa qalad.

Tani waa sababta oo ah waxaan u sheegaynaa shaqada MATCH si ay u raadiyaan qiimaha ugu horreeya ee RUNTA^(TRUE) ah (oo ah kala soocida qiyamka shaqada ISBLANK ), laakiin haddii aysan midna^(NONE) unugyada ka mid ahi faaruqin, dhammaan shaxanka waxaa ka buuxsami doona qiimeyaal^(FALSE) been abuur ah. Haddii MATCH uu heli waayo qiimihii bartilmaameedka ahaa ( TRUE ) ee qaabaysan ee uu raadinayo, waxay soo celisaa khalad.

Markaa, haddii liiska oo dhan uu buuxo (oo sidaas darteed, MATCH waxay soo celisaa khalad), shaqada IFERROR waxay soo celin doontaa qiimaha 20 (iyadoo la og yahay inay tahay inay jiraan 20 gelis liiska).

Ugu dambeyntii, OFFSET (FruitsHeading,1,0, kan sare,1)^{(OFFSET(FruitsHeading,1,0,the above,1))} waxay soo celinaysaa tirada aan dhab ahaantii raadineyno: Ka bilow unugga FruitsHeading , hoos 1 saf iyo in ka badan 0 tiirar, ka dibna dooro aag si kastaba ha ahaatee safaf badan liiska waxaa ku jira qoraallo (iyo 1 tiir oo ballaaran). Markaa qaacidada oo dhami waxay dib u soo celinaysaa tirada ka kooban gelinta dhabta ah (ilaa unugga ugu horreeya ee maran).

Isticmaalka qaaciidadan si aad u qeexdo inta u dhaxaysa isha hoos u dhaca macnaheedu waa in aad si xor ah u tafatir karto liiska (ku-dar ama ka saarida gelinta, ilaa inta inta soo hadhay ay ka bilaabmayso unugga sare oo ay isku xidhan yihiin) hoos u dhigiddu waxa ay had iyo jeer ka tarjumaysaa tan hadda jirta. liiska (fiiri sawirka 6^{(Figure 6)} ).

Tusaalaha faylka (Liiska firfircoon) ee halkan lagu adeegsaday ayaa ku jira oo laga soo dejisan karaa degelkan. Macrosku ma shaqeeyaan, si kastaba ha ahaatee, sababtoo ah WordPress ma jecla buugaagta Excel ee ay ku jiraan macros.

Beddelka lagu qeexayo tirada safafka ee liiska liiska, block-ga liisku waxa loo qoondayn karaa magac u gaar ah, kaas oo markaa loo isticmaali karo qaacido la beddelay. Tusaalaha faylka, liiska labaad ( Magacyada^(Names) ) ayaa isticmaala habkan. Halkan, liiska liiska oo dhan (hoosta cinwaanka "NAMES", 40 saf ee faylka tusaalaha) waxaa loo qoondeeyey magaca kala duwan ee NameBlock . Qaabka kale ee lagu qeexay Liiska Magacyada^(NamesList) waa:

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

halka NamesBlock ay ku badasho OFFSET ( FruitsHeading,1,0,20,1 ) iyo ROWS(NamesBlock) ku bedesho 20 (tirada safka) ee qaacidada hore.

Markaa, liisaska hoos u dhaca kuwaas oo si fudud loo tafatiran karo (ay ku jiraan isticmaalayaasha kale ee laga yaabo inay yihiin kuwa aan khibrad lahayn), isku day inaad isticmaasho magacyo kala duwan oo firfircoon! Oo ogow in, inkasta oo maqaalkani diiradda lagu saaray liisaska hoos u dhaca, magacyada kala duwanaanta firfircoon ayaa loo isticmaali karaa meel kasta oo aad u baahan tahay inaad tixraacdo tiro ama liis ku kala duwanaan kara cabbirka. Ku raaxayso!

Use Dynamic Range Names in Excel for Flexible Dropdowns

Excel spreadsheets often inclυde cell drоpdowns to sіmplify and/оr standardize data entry.  These dropdowns are created usіng the data validation feature tо specify a list of allowablе entrіes.

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

• Sida loo tirtiro khadadka banaan ee Excel

• Sida loo Isticmaalo Tilmaamaha Unugyada Hadalka ee Excel

• Sida loo Geliyo Xaashida Shaqada ee Excel Word Doc

• Sida loo isticmaalo Excel's What-If Analysis

• Sida Loo Hagaajiyo Safka gudaha Excel

• Sida loo qoro IF Formula/Bayaanka gudaha Excel

• Sida loo Hagaajiyo #N/A Khaladaadka gudaha Foomamka Excel sida VLOOKUP

• Sida Loo Abuuro VBA Macro Ama Script gudaha Excel

• Waa maxay sababta ay tahay inaad u isticmaasho safafka la magacaabay ee Excel

• Hal-Column iyo Xog-urur badan oo Aasaaska ah oo ku kala soocida xaashida faafinta ee Excel

• Safka Kooxda iyo Tiirarka ku jira xaashida shaqada ee Excel

• Sida loo xisaabiyo Z-Score gudaha Excel

• Ku xidhida Excel iyo MySQL

• Sida loo xarfo xarfaha Excel

• Sida loo raad raaco Ku-tiirsanaanta gudaha Excel

• Sida Loo Sameeyo Liisaska Hoos-u-dhaca ee Isku Xirka Badan ee Excel

• 5 siyaabood oo loogu badalo qoraalka nambarada gudaha Excel

• Sida Loo Dhaqaajiyo Columns gudaha Excel

• Sida Loo Mideeyo Unugyada, Tiirarka & Safafka gudaha Excel

• Hagaha VBA Sare ee MS Excel