غالبًا ما تتضمن جداول بيانات Excel قوائم منسدلة للخلايا لتبسيط و / أو توحيد إدخال البيانات. يتم إنشاء هذه القوائم المنسدلة باستخدام ميزة التحقق من صحة البيانات لتحديد قائمة بالإدخالات المسموح بها.
لإعداد قائمة منسدلة بسيطة ، حدد الخلية التي سيتم إدخال البيانات بها ، ثم انقر فوق التحقق من صحة البيانات(في علامة التبويب البيانات) ، حدد "التحقق من صحة البيانات" ، واختر قائمة(ضمن السماح :) ، ثم أدخل عناصر القائمة (مفصولة بفواصل) في المصدر: الحقل (راجع الشكل 1).
في هذا النوع من القائمة المنسدلة الأساسية ، يتم تحديد قائمة الإدخالات المسموح بها ضمن التحقق من صحة البيانات نفسها ؛ لذلك ، لإجراء تغييرات على القائمة ، يجب على المستخدم فتح وتحرير التحقق من صحة البيانات. ومع ذلك ، قد يكون هذا صعبًا على المستخدمين عديمي الخبرة ، أو في الحالات التي تكون فيها قائمة الخيارات طويلة.
هناك خيار آخر يتمثل في وضع القائمة في النطاق المسمى داخل جدول البيانات ، ثم تحديد اسم النطاق هذا (مقدم مسبقًا بعلامة مساوية) في المصدر: حقل التحقق من صحة البيانات (كما هو موضح في الشكل 2).
هذه الطريقة الثانية تسهّل تعديل الخيارات في القائمة ، لكن إضافة أو إزالة العناصر يمكن أن يكون مشكلة. نظرًا لأن النطاق المحدد (FruitChoices ، في مثالنا) يشير إلى نطاق ثابت من الخلايا ($ H $ 3: $ H $ 10 كما هو موضح) ، إذا تمت إضافة المزيد من الخيارات إلى الخلايا H11 أو أدناه ، فلن تظهر في القائمة المنسدلة (نظرًا لأن هذه الخلايا ليست جزءًا من نطاق FruitChoices).
وبالمثل إذا تم مسح إدخالات Pears and Strawberries ، على سبيل المثال ، فلن تظهر في القائمة المنسدلة ، ولكن بدلاً من ذلك ستتضمن القائمة المنسدلة الخيارات "الفارغة" لأن القائمة المنسدلة لا تزال تشير إلى نطاق FruitChoices بأكمله ، بما في ذلك الخلايا الفارغة H9 و H10.
لهذه الأسباب ، عند استخدام نطاق مسمى عادي كمصدر قائمة للقائمة المنسدلة ، النطاق المحدد يجب تعديل نفسه لتضمين خلايا أكثر أو أقل إذا تمت إضافة إدخالات أو حذفها من القائمة.
حل لهذه المشكلة هو استخدام ديناميكياسم النطاق كمصدر للخيارات المنسدلة. اسم النطاق الديناميكي هو الاسم الذي يتم توسيعه (أو العقود) تلقائيًا لمطابقة حجم كتلة البيانات تمامًا عند إضافة إدخالات أو إزالتها. للقيام بذلك ، يمكنك استخدام الصيغة، بدلاً من نطاق ثابت من عناوين الخلايا ، لتحديد النطاق المحدد.
كيفية إعداد ديناميكي النطاق في Excel
يشير اسم النطاق العادي (الثابت) إلى نطاق محدد من الخلايا ($ H $ 3: $ H $ 10 في مثالنا ، انظر أدناه):
ولكن يتم تحديد النطاق الديناميكي باستخدام صيغة (انظر أدناه ، مأخوذة من جدول بيانات منفصل يستخدم أسماء النطاق الديناميكي):
قبل البدء ، تأكد من تنزيل ملف اكسل سبيل المثال (تم تعطيل فرز وحدات الماكرو).
لنفحص هذه الصيغة بالتفصيل. توجد خيارات الفواكه في كتلة من الخلايا أسفل العنوان مباشرةً (الفواكه). يتم تعيين هذا العنوان أيضًا باسم: FruitsHeading:
الصيغة بأكملها المستخدمة لتحديد النطاق الديناميكي لـ خيارات الفواكه هي:
=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، وانزل لأسفل على صف واحد وأكثر من 0 عمود ، ثم حدد منطقة يبلغ طولها 20 صفًا وعمودًا واحدًا. بحيث يمنحنا هذا المكون من 20 صفًا حيث يتم إدخال خيارات الفواكه.
الجزء التالي من الصيغة هو وظيفة 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 قيمة صحيحة.
الجزء التالي من الصيغة هو دالة 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 قيم في المصفوفة ستكون FALSE ، وستكون القيمة التاسعة هي TRUE (لأن الصف 9 thفي النطاق فارغ).
وبالتالي فإن دالة 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)تُرجع المدى الذي نبحث عنه بالفعل: ابدأ في الخلية FruitsHeading ، وانزل لأسفل على صف واحد وأكثر من 0 أعمدة ، ثم حدد منطقة بها العديد من الصفوف طالما توجد إدخالات في القائمة (وعمود عرض 1 عمود). وبالتالي ، ستعيد الصيغة بأكملها معًا النطاق الذي يحتوي فقط على الإدخالات الفعلية (وصولًا إلى أول خلية فارغة).
استخدام هذه الصيغة لتحديد النطاق الذي يمثل مصدر القائمة المنسدلة يعني أنه يمكنك تحريرها بحرية القائمة (إضافة أو إزالة الإدخالات ، طالما أن الإدخالات المتبقية تبدأ في الخلية العليا وتكون متجاورة) وسوف تعكس القائمة المنسدلة دائمًا القائمة الحالية (انظر الشكل 6).
يتم تضمين ملف المثال (القوائم الديناميكية) الذي تم استخدامه هنا ويمكن تنزيله من هذا الموقع. ومع ذلك ، لا تعمل وحدات الماكرو لأن WordPress لا يحب كتب Excel التي تحتوي على وحدات ماكرو فيها.
كبديل لتحديد عدد الصفوف في كتلة القائمة ، يمكن تعيين كتلة القائمة اسم النطاق الخاص ، والذي يمكن بعد ذلك استخدامه في صيغة معدلة. في ملف المثال ، تستخدم القائمة الثانية (الأسماء) هذه الطريقة. هنا ، يتم تخصيص اسم النطاق لـ NameBlock، كتلة القائمة بأكملها (أسفل عنوان "NAMES" ، 40 صفًا في ملف المثال). الصيغة البديلة لتعريف NamesList هي:
=OFFSET(NamesHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(NamesBlock),0,0),0)-1,ROWS(NamesBlock)),1)
حيث يستبدل NamesBlockOFFSET (FruitsHeading ، 1،0،20،1) و ROWS (NamesBlock)الـ 20 (عدد الصفوف) في الصيغة السابقة.
لذلك ، لقوائم منسدلة يمكن تحريرها بسهولة (بما في ذلك من قِبل مستخدمين آخرين قد يكونوا عديمي الخبرة) ، حاول استخدام أسماء النطاق الديناميكي! ولاحظ أنه على الرغم من تركيز هذه المقالة على القوائم المنسدلة ، يمكن استخدام أسماء النطاق الديناميكي في أي مكان تحتاج إليه للإشارة إلى نطاق أو قائمة يمكن أن تختلف في الحجم. استمتع!