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