تعرضنا في مقال سابق إلى استخدام أداة Data Validation في Excel لعمل قائمتين منسدلتين مرتبطتين في Excel بطريقة سهلة لا تحتاج إلى معادلات معقدة، لكن يعيب هذه الطريقة التقيد باستخدام عناصر بالقائمة الرئيسية تصلح للاستخدام كأسماء نطاقات (لا تحتوي على مسافات – لا تبدأ برقم). يميل البعض لاستخدام هذه الطريقة لعدم احتوائها على معادلات معقدة، إلا أن الواقع العملي قد يفرض وجود بعض الخيارات التي لا تتوافق مع هذه القيود خاصة وجود المسافات، وخاصة لدى مستخدمي اللغة العربية حيث أنه من غير المألوف لدينا استخدام علامات بديلة للمسافات مثل اللغة الإنجليزية. في هذا المقال سنقدم طريقة عملية مبسطة قدر الإمكان لتنفيذ قائمتين منسدلتين مرتبطتين معا، مع قابلية التحديث بسهولة واستخدام الأسماء التي تحتوي على مسافات وأرقام. وذلك للوصول لنتيجة تشبه ما توضحه الصورة التالية:

وللوصول إلى هذا النموذج نتبع الخطوات التالية:

إذا كنت قد قمت من قبل بعمل قوائم منسدلة بسيطة باستخدام Data Validation فيمكنك متابعة القراءة من الخطوة 6

1- في البداية، وخارج نطاق الجدول، نقوم بإعداد البيانات المطلوب إدراجها بحيث يكون اسم الدولة (أو كل عناصر القائمة الأساسية) في صف واحد، وتحت كل عنصر منها، نكتب الاختيارات المطلوبة المقابلة له في القائمة الثانية أو الفرعية، كما توضح الصورة التالية:

2- نحدد الخلية الأولى تحت عمود “الدولة” ثم نقوم بالضغط على علامة تبويب Data ومنها ومن داخل مجموعة أوامر Data Tools نضغط على Data Validation فتظهر لنا نافذة Data Validation كما بالشكل التالي:

3- في نافذة Data Validation ومن قائمة الخيارات تحت Allow نحدد الاختيار List فيظهر مربع Source داخل النافذة كما يوضح الشكل التالي:

4- نتأكد من تفعيل المربع تحت Source بالضغط بداخله، ثم نقوم بتحديد نطاق عناصر القائمة الرئيسية، فيظهر النطاق داخل المربع كما بالشكل التالي:

5- في نافذة Data Validation نضغط OK لإغلاق النافذة، وبالعودة إلى الخلية التي تم تحديدها في الخطوة 2 تظهر لنا القائمة المنسدلة كما بالشكل التالي

وهكذا تم الانتهاء من إعداد القائمة الأولى

6- لتيسير عملية تحديث القوائم الفرعية فيما بعد، نحدد الخلية أعلى العنصر الأول من القائمة الرئيسية وهي هنا الخلية A1ثم نكتب المعادلة التالية لإيجاد عدد عناصر القائمة الفرعية الأولى:

=COUNTA(A3:A1048576)

بعد الضغط على Enter نلاحظ ظهور الرقم 8 وهو عدد عناصر القائمة الأولى في المثال الموضح

7- بعد نسخ المعادلة المكتوبة في الخطوة السابقة إلى الخلايا المجاورة تظهر لدينا عدد عناصر كل قائمة من القوائم الفرعية كما بالصورة التالية:

8- نحدد الخلية المطلوب إدراج القائمة الفرعية بها (الخلية تحت عمود المدينة كما في المثال الموضح)، ومن علامة تبويب Data ومن داخل مجموعة أوامر Data Tools نحدد الأمر Data Validation فتظهر نافذة Data Validation، ومنها تحت Allow  نحدد الاختيار List فتظهر لنا النافذة كما سبق توضيحه في الخطوات 3 و 4، نتأكد من تحديد مربع Source الذي يظهر في نافذة Data Validation:

9- داخل مربع Source نكتب المعادلة التالية:

=OFFSET(القوائم!$A$2,1,MATCH($B$2,القوائم!$A$2:$C$2,0)-1,CHOOSE(MATCH($B$2,القوائم!$A$2:$C$2,0),القوائم!$A$1,القوائم!$B$1,القوائم!$C$1),1)

حيث أن:

Offset دالة تستخدم لتحديد نطاق معين مطلوب استخدامه كمرجع لوظيفة أخرى، مثل عمل قائمة منسدلة معتمدة عليه

القوائم!$A$2 : الخلية التي يبدأ من عندها وصف أو تحديد بداية النطاق المطلوب، وهي هنا أقرب الخلايا للنطاقات المستخدمة، يمكن اختيار أي خلية يسهل من خلالها الإشارة بالأرقام إلى باقي العناصر الموضحة أدناه، مع مراعاة اختلاف المكان

1 الرقم 1 معناه أن النطاق المطلوب تحديده يبدأ أسفل الخلية المحددة القوائم!$A$2  بصف واحد فقط

MATCH($B$2,القوائم!$A$2:$C$2,0)-1

وظيفة المعادلة الإجابة على سؤال، من أين نبدأ تحديد الأعمدة، والنتيجة إما 1 أو 2 أو 3 لكل اختيار من القائمة الرئيسية على حدة، وحتى يتوافق الترتيب المطلوب مع ترتيب الخلايا في Excel نطرح رقم 1، حتى لا تحدث مشكلة في حالة اختيار العنصر الأول في القائمة الرئيسية

CHOOSE(MATCH($B$2,القوائم!$A$2:$C$2,0),القوائم!$A$1,القوائم!$B$1,القوائم!$C$1)

هذا الجزء من المعادلة مسئول عن عدد الصفوف في النطاق المحدد، وهو يوافق عدد عناصر كل قائمة فرعية، ويختلف باختلاف الاختيارات، ويتم حسابه بالرجوع إلى معادلات CountA التي تمت إضافتها في الخطوة رقم 6

الرقم 1 في نهائة المعادلة يعني أننا نريد أن يكون النطاق المطلوب مكونا من عمود واحد فقط.

10- بعد كتابة المعادلة وتعديلها طبقا لتطبيقك، قم بالضغط على OK لإغلاق نافذة Data Validation، إذا كان تطبيقك سليما فستحصل داخل الخلية الأولى تحت عمود (المدينة) على قائمة منسدلة تتغير تبعا للعنصر الذي تقوم باختياره تحت عمود (الدولة)، كما يمكن تحديث عناصر القوائم المنسدلة الفرعية ببساطة بمجرد إضافتها تحت العمود الخاص بها في ورقة عمل (القوائم)