صفحات گسترده اکسل^(Excel) اغلب شامل کشویی سلولی برای ساده کردن و/یا استاندارد کردن ورود داده می شود. این فهرست‌های کشویی با استفاده از ویژگی اعتبارسنجی داده‌ها برای تعیین فهرستی از ورودی‌های مجاز ایجاد می‌شوند.

برای تنظیم یک لیست کشویی ساده، سلولی را که داده ها در آن وارد می شوند انتخاب کنید، سپس روی Data Validation (در زبانه Data ) کلیک کنید، ^(Data)Data Validation را انتخاب کنید، لیست^(List) را انتخاب کنید (در زیر Allow :)، و سپس موارد لیست را وارد کنید (با کاما از هم جدا شده اند. ) در قسمت Source : (شکل 1 را ببینید).

در این نوع کشویی پایه، فهرست ورودی های مجاز در خود اعتبارسنجی داده ها مشخص می شود. بنابراین، برای ایجاد تغییرات در لیست، کاربر باید اعتبار سنجی داده ها را باز کرده و ویرایش کند. با این حال، این ممکن است برای کاربران بی تجربه یا در مواردی که لیست انتخاب ها طولانی است دشوار باشد.

گزینه دیگر این است که لیست را در یک محدوده نامگذاری شده در صفحه گسترده^{(named range within the spreadsheet)} قرار دهید ، و سپس نام آن محدوده را (با پیشگفتار علامت مساوی) در قسمت Source : اعتبار داده تعیین کنید (همانطور که در شکل 2^{(Figure 2)} نشان داده شده است ).

این روش دوم ویرایش انتخاب‌های فهرست را آسان‌تر می‌کند، اما افزودن یا حذف موارد می‌تواند مشکل‌ساز باشد. از آنجایی که محدوده نام‌گذاری‌شده ( FruitChoices ، در مثال ما) به محدوده ثابتی از سلول‌ها اشاره دارد ($3H$3:$H$10 همانطور که نشان داده شده است)، اگر انتخاب‌های بیشتری به سلول‌های H11 یا پایین‌تر اضافه شوند، در منوی کشویی نشان داده نمی‌شوند. (از آنجایی که آن سلول ها بخشی از محدوده FruitChoices نیستند^{(FruitChoices)} ).

به همین ترتیب، اگر، برای مثال، ورودی‌های Pears و Strawberries پاک شوند، دیگر در فهرست کشویی ظاهر نخواهند شد، بلکه فهرست کشویی شامل دو گزینه خالی خواهد بود، زیرا فهرست بازشو همچنان به کل محدوده FruitChoices، از جمله سلول‌های خالی H9 و H9 اشاره می‌کند. H10 .

به این دلایل، هنگام استفاده از یک محدوده با نام معمولی به عنوان منبع فهرست برای فهرست کشویی، خود محدوده نام‌گذاری شده باید ویرایش شود تا در صورت اضافه یا حذف ورودی‌ها از لیست، تعداد سلول‌های بیشتری یا کمتری را شامل شود.

راه حل این مشکل استفاده از نام محدوده پویا^(dynamic) به عنوان منبع برای انتخاب های کشویی است. نام محدوده پویا نامی است که به طور خودکار گسترش می یابد (یا منقبض می شود) تا دقیقاً با اندازه یک بلوک داده مطابقت داشته باشد که ورودی ها اضافه یا حذف می شوند. برای انجام این کار ، به جای یک محدوده ثابت از آدرس های سلول، از یک فرمول برای تعریف محدوده نامگذاری شده استفاده می کنید.^(formula)

نحوه تنظیم محدوده دینامیک^{(Dynamic Range)} در اکسل^(Excel)

نام محدوده معمولی (ایستا) به محدوده مشخصی از سلول ها اشاره دارد ($H$3:$H$10 در مثال ما، به زیر مراجعه کنید):

اما یک محدوده پویا با استفاده از یک فرمول تعریف می شود (نگاه کنید به زیر، از یک صفحه گسترده جداگانه که از نام دامنه پویا استفاده می کند گرفته شده است):

قبل از شروع، مطمئن شوید که فایل نمونه اکسل ما را دانلود کرده اید  (ماکروهای مرتب سازی غیرفعال شده اند).

بیایید این فرمول را با جزئیات بررسی کنیم. گزینه‌های Fruits در یک بلوک از سلول‌ها مستقیماً زیر عنوان ( 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)

"درونی ترین" قطعه 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^(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 و مقدار نهم ^(FALSE)TRUE خواهد بود (زیرا ردیف نهم ^در محدوده خالی است).

بنابراین تابع MATCH مقدار 9 را برمی گرداند . با این حال، در این مورد، ما واقعاً می‌خواهیم بدانیم چند ورودی در لیست وجود دارد، بنابراین فرمول 1 را از مقدار MATCH (که موقعیت آخرین ورودی را نشان می‌دهد) کم می‌کند. بنابراین در نهایت، MATCH ( TRUE ، بالا، 0)-1 مقدار 8 را برمی گرداند .

قطعه بعدی فرمول تابع IFERROR^(IFERROR) است:

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

اگر اولین مقدار مشخص شده منجر به خطا شود، تابع IFERROR یک مقدار جایگزین برمی گرداند . ^(IFERROR)این تابع از آنجایی گنجانده شده است که اگر کل بلوک سلول ها (همه 20 ردیف) با ورودی ها پر شود، تابع MATCH یک خطا برمی گرداند.

این به این دلیل است که به تابع MATCH می گوییم که به دنبال اولین مقدار TRUE (در آرایه مقادیر تابع ISBLANK ) باشد، اما اگر هیچ یک^(NONE) از سلول ها خالی نباشد، کل آرایه با مقادیر FALSE پر می شود. ^(FALSE)اگر MATCH نتواند مقدار هدف ( TRUE ) را در آرایه ای که جستجو می کند پیدا کند، یک خطا برمی گرداند.

بنابراین، اگر کل لیست پر باشد (و بنابراین، MATCH یک خطا را برمی گرداند)، تابع IFERROR^(IFERROR) در عوض مقدار 20 را برمی گرداند (با دانستن اینکه باید 20 ورودی در لیست وجود داشته باشد).

در نهایت، OFFSET (FruitsHeading، 1،0، بالا، 1)^{(OFFSET(FruitsHeading,1,0,the above,1))} محدوده مورد نظر ما را برمی‌گرداند: از سلول FruitsHeading شروع کنید ، 1 سطر و بیش از 0 ستون را پایین بیاورید، سپس ناحیه‌ای را انتخاب کنید که هر چند ردیف طول داشته باشد. ورودی هایی در لیست وجود دارد (و 1 ستون عرض دارد). بنابراین کل فرمول با هم محدوده ای را که فقط شامل ورودی های واقعی است (تا اولین سلول خالی) برمی گرداند.

استفاده از این فرمول برای تعریف محدوده ای که منبع کشویی است به این معنی است که می توانید آزادانه لیست را ویرایش کنید (اضافه یا حذف ورودی ها، تا زمانی که ورودی های باقیمانده از سلول بالایی شروع می شوند و به هم پیوسته هستند) و کشویی همیشه منعکس کننده جریان فعلی است. فهرست ( شکل 6^{(Figure 6)} را ببینید ).

فایل نمونه (فهرست های پویا) که در اینجا استفاده شده است گنجانده شده است و از این وب سایت قابل دانلود است. با این حال، ماکروها کار نمی کنند، زیرا وردپرس از کتاب های ^(WordPress)اکسل^(Excel) با ماکرو در آنها خوشش نمی آید.

به‌عنوان جایگزینی برای تعیین تعداد ردیف‌ها در بلوک فهرست، می‌توان به بلوک فهرست نام محدوده خود را اختصاص داد، که سپس می‌تواند در فرمول اصلاح‌شده استفاده شود. در فایل مثال، لیست دوم ( نام^(Names) ها ) از این روش استفاده می کند. در اینجا، کل بلوک لیست (زیر عنوان "NAMES"، 40 ردیف در فایل مثال) به نام محدوده NameBlock اختصاص داده می شود . فرمول جایگزین برای تعریف NamesList به این صورت است:

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

که در آن NamesBlock جایگزین OFFSET ( FruitsHeading,1,0,20,1 ) و ROWS(NamesBlock) جایگزین 20 (تعداد ردیف) در فرمول قبلی می شود.

بنابراین، برای لیست‌های کشویی که به راحتی قابل ویرایش هستند (از جمله توسط سایر کاربرانی که ممکن است بی‌تجربه باشند)، از نام‌های محدوده پویا استفاده کنید! و توجه داشته باشید که اگرچه این مقاله بر روی لیست‌های کشویی متمرکز شده است، نام‌های محدوده پویا را می‌توان در هر جایی که برای ارجاع به محدوده یا فهرستی که می‌تواند از نظر اندازه متفاوت باشد، استفاده کرد. لذت بردن!

Use Dynamic Range Names in Excel for Flexible Dropdowns

Excel spreadѕheets often include cell dropdowns to simplify аnd/or standardize data entry.  These dropdownѕ arе created usіng the data validation feature to specify 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

• نحوه حذف خطوط خالی در اکسل

• نحوه استفاده از ویژگی Speak Cells اکسل

• نحوه درج کاربرگ اکسل در Word Doc

• نحوه استفاده از تجزیه و تحلیل What-If اکسل

• نحوه تعمیر یک ردیف در اکسل

• نحوه استفاده از دستورات If و Nested If در اکسل

• نحوه ادغام داده ها در چندین فایل اکسل

• زمان استفاده از Index-Match به جای VLOOKUP در اکسل

• املای سریع اعداد در Word و Excel

• چگونه یک فایل اکسل را برای همکاری آسان به اشتراک بگذاریم

• با استفاده از ابزار جستجوی هدف تجزیه و تحلیل What-If اکسل

• چرا باید از محدوده های نامگذاری شده در اکسل استفاده کنید؟

• نحوه مرتب سازی بر اساس تاریخ در اکسل

• نحوه درج CSV یا TSV در کاربرگ اکسل

• نحوه حذف ردیف های تکراری در اکسل

• نحوه استفاده از مراجع مطلق در اکسل

• از Spike برای برش و چسباندن چندین مورد متنی در Word استفاده کنید

• نحوه محاسبه امتیاز Z در اکسل

• اتصال اکسل به MySQL

• 5 روش برای تبدیل متن به اعداد در اکسل