شروحات برنامج اكسيل - حقوق الصورة البارزة: Lynda.com
2

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

موضوع اليوم سيكون عن قوائم الاختيار (القوائم المنسدلة) أو Drop Down List (الأمثلة التالية تم تطبيقها على Excel 2016، إذا كنت تستخدم إصدارات مختلفة ولا تستطيع أن تطبق هذه الأمثلة بسهولة، أرجو أن تضيف سؤالك ضمن التعليق، وسأحاول أن أساعدك إن أمكن).

قائمة اختيار بسيطة Static Dropdown List

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

يناير – فبراير – مارس – أبريل – مايو – يونيو – يوليو – أغسطس – سبتمبر – أكتوبر – نوفمبر – ديسمبر.

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

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

الآن من قائمة Data اختر Data Validation

قائمة اختيار بسيطة في اكسل

ستظهر نافذة فيها ثلاث صفحات (tabs)

في صفحة Settings اختر  List

قائمة اختيار بسيطة في اكسل

وعندما يظهر حقل الـ source في الأسفل، اضغط على الأيقونة الصغيرة على يمين الحقل

قائمة اختيار بسيطة في اكسل

سيختفي معظم الصفحة ويبقى الحقل الذي يجب أن تدخل فيه عناوين الخلايا التي تحتوي القائمة الكاملة لأسماء الأشهر الاثني عشر. قم بالضغط على الخلية التي تحتوي أول شهر، واستمر بالضغط والنزول للأسفل حتى آخر شهر، ستلاحظ بأنّ أرقام الخلايا التي تحتوي الأشهر قد ظهرت في حقل الـ  source

قائمة اختيار بسيطة في اكسل

اضغط على الأيقونة الصغيرة مرة أخرى لإظهار النافذة الكاملة، ثم اضغط OK.

أصبحت القائمة جاهزة للاستخدام، ستظهر أيقونة صغيرة بمثلث أسود عند تحديد الخلية، وعند الضغط عليها ستظهر قائمة الأشهر.

قائمة اختيار بسيطة في اكسل


التحكم بالمدخلات

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

التحكم بالمدخلات في اكسل

يمكنك أن تغير نص هذه الرسالة كما يلي:

ابقِ التحديد على الخلية الصفراء، ثم اختر data  <– data validation  <– Error Alert.

أدخل عنوان ونص لرسالة الخطأ كما هو موضح في الصورة (يمكنك إدخال أي نص تريده).

التحكم بالمدخلات في اكسل

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

التحكم بالمدخلات في اكسل

في بعض الحالات تريد أن تسمح بإدخال نص غير متضمن في القائمة. في هذه الحالة يمكنك أن تغير الإعدادات باتباع نفس الخطوات السابقة، ولكن هذه المرة قم بتغيير الـ Stop  إلى Warning أو Information كما في الصورة التالية:

التحكم بالمدخلات في اكسل

في هذه الحالة إكسل سيعرض رسالة الخطأ، ولكن سيكون بإمكانك أن تختار إبقاء النص أو رفضه.

التحكم بالمدخلات في اكسل

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


القوائم الاشتراطية

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

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

سنبدأ ببناء القوائم كما هو موضح في الصورة:

القوائم الاشتراطية في اكسل

القائمة الأولى تحتوي على أسماء الأقاليم فقط، أمّا القائمة الثانية فيجب أن تتألف من عمودين: الأول يشير إلى اسم الإقليم والثاني إلى اسم الدولة التي تقع في هذا الإقليم. ينطبق نفس الأمر على القائمة الثالثة التي تظهر العلاقة بين الدولة والمدينة، من الضروري أن يكون اسم الإقليم في العمود اليساري واسم الدولة في العمود اليميني، وينطبق ذلك على قائمة الدول والمدن.

الخطوة الثانية هي تحديد الخلايا التي نريد أن نملأها. سأشير إلى كل خلية بلون كما هو موضح في الصورة:

القوائم الاشتراطية في اكسل

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

القوائم الاشتراطية في اكسل

الخطوة الرابعة هي الخطوة المهمة. القائمة في الخلية Q2 يجب أن تكون ديناميكيةً، أي أن تظهر أسماء الدول الموجودة في الإقليم الذي تم اختياره في الخلية Q1. لتحقيق ذلك، حدد الخلية Q2 ثم اذهب إلى Data   Data Validation   اختر List، ثم ادخل المعادلة التالية في حقل Source

=OFFSET($C$2:$C$11,MATCH($Q$1,$C$2:$C$11,0)-1,1,COUNTIF($C$2:$C$11,$Q$1),1)

القوائم الاشتراطية في اكسل

هذه المعادلة تقوم بالبحث أنّ محتوى الخلية Q1 (أي اسم الإقليم) في العمود C، ثم تقوم بتعداد وعرض محتويات جميع الخلايا التي تقابل اسم الإقليم في العمود المجاور، ربما تبدو هذه المعادلة معقدةً قليلًا، ولكن يمكن تبسيطها على الشكل التالي:

معادلة offset تستخدم كخريطة إحداثيات للوصول إلى خلية أو مجموعة خلايا ضمن صفحة الإكسيل. مكونات المعادلة تبدأ من اليسار، لنفرض أنّ الإقليم الذي اخترناه في الخلية $Q$1 هو أوروبا

$C$2:$C$11: نقطة البداية أي النقطة التي نبدأ رسم الخريطة منها. في هذا المثال، نبدأ من العمود الذي يحتوي أسماء الأقاليم، والهدف هو الوصول إلى أسماء الدول.

MATCH($Q$1,$C$2:$C$11,0) -1: تبحث عن رقم السطر الذي تظهر فيه كلمة أوروبا ابتداءً من أول إقليم.

1: رقم العمود الذي تقع فيه أسماء الدول، وهو العمود الأول باتجاه اليمين بعد عمود الأقاليم

COUNTIF($C$2:$C$11,$Q$1): هذه المعادلة تقوم بتعداد الخلايا التي يتكرر فيها اسم الإقليم. تتكرر كلمة أوروبا أربع مرات في هذا المثال. لذلك، سيكون هناك أربع دول أوروبية في القائمة المنسدلة.

1: هو عرض الجدول الذي تم تكوينه من خلال المعادلة. في هذه المثال، الجدول مؤلف من عمود واحد فقط.

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

=OFFSET($F$1:$F$27,MATCH($Q$2,$F$1:$F$27,0)-1,1,COUNTIF($F$1:$F$27,$Q$2),1)

القوائم الاشتراطية في اكسل

وبالتالي فقط حصلنا على قوائم ديناميكية تسهّل عملية إدخال البيانات وتقلل الأخطار.

2

شاركنا رأيك حول "ومضات إكسيلية، الحلقة الأولى: القوائم المنسدلة"