غالبًا ما تتضمن جداول بيانات Excel^(Excel) قوائم منسدلة للخلايا لتبسيط و / أو توحيد إدخال البيانات. يتم إنشاء هذه القوائم المنسدلة باستخدام ميزة التحقق من صحة البيانات لتحديد قائمة الإدخالات المسموح بها.

لإعداد قائمة منسدلة بسيطة ، حدد الخلية حيث سيتم إدخال البيانات ، ثم انقر فوق التحقق من صحة البيانات^{(Data Validation)} (في علامة التبويب البيانات ) ، وحدد التحقق من ^(Data)صحة البيانات^{(Data Validation)} ، واختر قائمة^(List) (ضمن السماح^(Allow) :) ، ثم أدخل عناصر القائمة (مفصولة بفواصل ) في المصدر^(Source) : الحقل (انظر الشكل 1).

في هذا النوع من القائمة المنسدلة الأساسية ، يتم تحديد قائمة الإدخالات المسموح بها في التحقق من صحة البيانات نفسه ؛ لذلك ، لإجراء تغييرات على القائمة ، يجب على المستخدم فتح التحقق من صحة البيانات وتحريره. قد يكون هذا صعبًا ، مع ذلك ، للمستخدمين عديمي الخبرة ، أو في الحالات التي تكون فيها قائمة الخيارات طويلة.

خيار آخر هو وضع القائمة في نطاق مسمى داخل جدول البيانات^{(named range within the spreadsheet)} ، ثم تحديد اسم النطاق هذا (مُسبقًا بعلامة يساوي) في المصدر^(Source) : حقل التحقق من صحة البيانات (كما هو موضح في الشكل 2^{(Figure 2)} ).

تعمل هذه الطريقة الثانية على تسهيل تحرير الاختيارات في القائمة ، ولكن إضافة العناصر أو إزالتها قد تكون مشكلة. نظرًا لأن النطاق المسمى ( FruitChoices ، في مثالنا) يشير إلى نطاق ثابت من الخلايا ($ H $ 3: $ H $ 10 كما هو موضح) ، إذا تمت إضافة المزيد من الخيارات إلى الخلايا H11 أو أقل ، فلن تظهر في القائمة المنسدلة (نظرًا لأن هذه الخلايا ليست جزءًا من نطاق خيارات FruitChoices^{(FruitChoices)} ).

وبالمثل ، على سبيل المثال ، إذا تم مسح إدخالات Pears و Strawberries ، فلن تظهر بعد ذلك في القائمة المنسدلة ، ولكن بدلاً من ذلك ستتضمن القائمة المنسدلة خيارين "فارغين" نظرًا لأن القائمة المنسدلة لا تزال تشير إلى نطاق FruitChoices بأكمله ، بما في ذلك الخلايا الفارغة H9 و H10 .

لهذه الأسباب ، عند استخدام نطاق مسمى عادي كمصدر قائمة لقائمة منسدلة ، يجب تحرير النطاق المسمى نفسه ليشمل عددًا أكبر أو أقل من الخلايا إذا تمت إضافة الإدخالات أو حذفها من القائمة.

حل هذه المشكلة هو استخدام اسم نطاق ديناميكي^(dynamic) كمصدر لخيارات القائمة المنسدلة. اسم النطاق الديناميكي هو الاسم الذي يتمدد (أو يتعاقد) تلقائيًا ليطابق حجم كتلة البيانات تمامًا عند إضافة الإدخالات أو إزالتها. للقيام بذلك ، يمكنك استخدام صيغة^(formula) ، بدلاً من نطاق ثابت لعناوين الخلايا ، لتحديد النطاق المسمى.

كيفية إعداد النطاق الديناميكي^{(Dynamic Range)} في Excel

يشير اسم النطاق العادي (الثابت) إلى نطاق محدد من الخلايا ($ H $ 3: $ H $ 10 في مثالنا ، انظر أدناه):

ولكن يتم تحديد النطاق الديناميكي باستخدام صيغة (انظر أدناه ، مأخوذة من جدول بيانات منفصل يستخدم أسماء نطاقات ديناميكية):

قبل أن نبدأ ، تأكد من تنزيل ملف مثال Excel الخاص بنا  (تم تعطيل وحدات الماكرو).

دعونا نفحص هذه الصيغة بالتفصيل. توجد اختيارات الفواكه في كتلة من الخلايا أسفل العنوان مباشرةً ( الفاكهة^(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^(OFFSET) هذه بشكل أساسي: ابدأ من خلية FruitsHeading ، وانزل صفًا واحدًا وأكثر من 0 عمودًا ، ثم حدد منطقة يبلغ طولها 20 صفًا وعرضها عمود واحد. هذا يعطينا الكتلة المكونة من 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 فارغة (فارغة) أم لا. في هذا المثال ، ستكون القيم الثمانية الأولى في المجموعة 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) عادة لاختيار قيمة معينة (أو نطاق من القيم) من كتلة من البيانات ، من خلال تحديد صف وعمود معين (داخل تلك الكتلة). لكن تعيين مدخلات الصف والعمود على صفر (كما هو الحال هنا) يتسبب في إرجاع INDEX مصفوفة تحتوي على كتلة البيانات بأكملها.

الجزء التالي من الصيغة هو دالة MATCH :

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

تُرجع الدالة MATCH موضع أول قيمة ^(MATCH)TRUE ، ضمن الصفيف الذي تُرجعه الدالة INDEX . نظرًا لأن الإدخالات الثمانية الأولى في القائمة ليست فارغة ، فإن القيم الثمانية الأولى في المصفوفة ستكون FALSE ، وستكون القيمة التاسعة TRUE (نظرًا لأن الصف ^التاسع في النطاق فارغ).

لذا ستُرجع الدالة MATCH القيمة ^(MATCH)9 . ومع ذلك ، في هذه الحالة ، نريد حقًا معرفة عدد الإدخالات الموجودة في القائمة ، لذلك تطرح الصيغة 1 من قيمة MATCH (التي تعطي موضع الإدخال الأخير). في النهاية ، تُرجع MATCH ( TRUE ، أعلاه ، 0) -1 قيمة 8 .

الجزء التالي من الصيغة هو دالة IFERROR :

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

ترجع الدالة IFERROR قيمة بديلة ، إذا نتج عن القيمة الأولى المحددة خطأ. يتم تضمين هذه الوظيفة لأنه إذا كانت كتلة الخلايا بأكملها (جميع الصفوف العشرين) مملوءة بإدخالات ، فستقوم وظيفة MATCH بإرجاع خطأ.

هذا لأننا نطلب من دالة MATCH البحث عن أول قيمة TRUE (في مصفوفة القيم من دالة ISBLANK ) ، ولكن إذا كانت NONE من الخلايا فارغة ، فسيتم ملء الصفيف بأكمله بقيم FALSE . إذا لم تتمكن MATCH من العثور على القيمة الهدف ( TRUE ) في المصفوفة التي تبحث عنها ، فإنها تُرجع خطأً.

لذلك ، إذا كانت القائمة بأكملها ممتلئة (وبالتالي ، فإن MATCH ترجع خطأ) ، فستقوم الدالة IFERROR بدلاً من ذلك بإرجاع القيمة 20 (مع العلم أنه يجب أن يكون هناك 20 إدخالاً في القائمة).

أخيرًا ، يعرض OFFSET (FruitsHeading ، 1،0 ، ما سبق ، 1)^{(OFFSET(FruitsHeading,1,0,the above,1))} النطاق الذي نبحث عنه بالفعل: ابدأ من خلية FruitsHeading ، ثم انتقل لأسفل صفًا واحدًا وأكثر من 0 عمودًا ، ثم حدد منطقة بها العديد من الصفوف بطول توجد إدخالات في القائمة (وعرض عمود واحد). لذلك ستعيد الصيغة بأكملها معًا النطاق الذي يحتوي على الإدخالات الفعلية فقط (وصولاً إلى الخلية الفارغة الأولى).

إن استخدام هذه الصيغة لتحديد النطاق الذي يمثل مصدر القائمة المنسدلة يعني أنه يمكنك تحرير القائمة بحرية (إضافة أو إزالة الإدخالات ، طالما أن الإدخالات المتبقية تبدأ من الخلية العلوية وتكون متجاورة) وستعكس القائمة المنسدلة دائمًا القيمة الحالية قائمة (انظر الشكل 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 spreadsheets оften include cеll droрdowns tо simplify and/or ѕtandardize data entry.  These dropdowns arе created using 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

• كيفية حذف الأسطر الفارغة في Excel

• كيفية استخدام ميزة Speak Cells في Excel

• كيفية إدراج ورقة عمل Excel في مستند Word

• كيفية استخدام تحليل ماذا لو في Excel

• كيفية إصلاح صف في Excel

• كيفية الترتيب الأبجدي في Excel

• ما هي مصفوفة VBA في Excel وكيفية البرمجة الأولى

• لماذا يجب أن تستخدم النطاقات المسماة في Excel

• كيفية تحريك الأعمدة في Excel

• كيفية دمج البيانات في ملفات Excel متعددة

• كيفية استخدام المراجع المطلقة في Excel

• كيفية إنشاء قوائم منسدلة متعددة مرتبطة في Excel

• جداول بيانات Google مقابل Microsoft Excel - ما هي الاختلافات؟

• تهجئة الأرقام بسرعة في Word و Excel

• كيفية عمل مخطط دائري في Excel

• كيفية مشاركة ملف Excel لسهولة التعاون

• قم بتجميع الصفوف والأعمدة في ورقة عمل Excel

• كيفية إدراج صفوف متعددة بسرعة في Excel

• كيفية فتح Word و Excel في الوضع الآمن

• كيفية استخدام عبارات If ومتداخلة If في Excel