المزيد عن القوائم المنسدلة في Excel

https://facebook.com/msofficemania/photos/a.236273829741140/1971105669591272/

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

في هذا المثال، نفرض أن العناصر التالية هي المنتجات المعروضة للبيع في أحد متاجر الهواتف المحمولة، ولأغراض تيسير العمليات يرغب عمرو (القائم بأعمال تسجيل البيانات) في عمل قائمة منسدلة تتغير عناصرها نتيجة البحث، لدى عمرو منتجات الهواتف المحمولة الموجودة بالصورة التالية:

في نهاية المقال ستعرف كيف سيتمكن عمرو من إعداد القائمة لتظهر بالشكل التالي عند كتابة كلمة Samsung مثلا (أو بعض حروفها) داخل الخلية:

وللقيام بذلك، يتبع عمرو الخطوات التالية:

1- لتبسيط الأمر نفترض أن القائمة المنسدلة المطلوبة وقائمة المنتجات في نفس ورقة العمل، نطاق قائمة المنتجات هو B3:B50 (ويمكن إخفاء العمود B فيما بعد)، والخلية المطلوب عمل القائمة المنسدلة بها (حقل المنتج) هي الخلية N2. لغرض الاختبار قام عمرو بكتابة إحدى القيم المتوقع البحث عنها ولتكن Samsung داخل الخلية N2

2- في نطاق خارج التقرير كذلك وليكن في الخلية I3 (ويمكن إخفاء العمود I فيما بعد)، قام عمرو بكتابة المعادلة التالية باستخدام الدالة Search، يعلم عمرو أن الدالة Search تستخدم للبحث عن قيمة معينة داخل خلية أو نطاق، استخدم عمرو الدالة Search لإيجاد مكان كلمة Samsung الموجودة داخل الخلية N2 في جميع قيم نطاق قائمة المنتجات B3:B50 فكانت المعادلة داخل الخلية I2 كالتالي:

3- قام عمر بالضغط على OK في مربع Function Arguments فظهرت نتيجة المعادلة كالتالي:

(تم إخفاء بعض الصفوف لتيسير عرض النتيجة)

نظرا لاستخدام نطاق في خانة Within Text في المعادلة بالخطوة السابقة، فإن نتيجة المعادلة قد ظهرت في نطاق منزلق Spill Range يحتوي على الخطأ VALUE في الخلايا المناظرة للمنتجات التي لا تتضمن Samsungـ كما تظهر القيمة 1، في الخلايا المناظرة للمنتجات التي تتضمن Samsung، هذه القيمة هي مكان ظهور كلمة Samsung داخل الخلية (أول حرف)، قد تختلف النتيجة اختلافا طفيفا حسب طبيعة البيانات الموجودة لديك، لن يحدث هذا الاختلاف أثرا بالغا فما يهمنا أن تكون القيمة رقما فحسب. لأننا سنقوم بتصفية النطاق للتخلص من الخطأ VALUE في الخطوات التالية.

4- قام عمرو بتعديل المعادلة بالخلية I3 للتخلص من الخطأ VALUE، حيث استخدم الدالة ISNUMBER لتظهر قيم الأرقام بالقيمة TRUE، وتظهر الأخطاء بالقيمة FALSE، لتصبح المعادلة كما يلي:

=ISNUMBER(SEARCH(N2,B3:B51))

5- قام عمرو بالضغط على OK في مربع Function Arguments ، فتم استبدال الخطأ بالقيمة FALSE والأرقام المناظرة لمنتجات Samsung بالقيمة TRUE لتظهر النتيجة كما بالشكل التالي:

(تم إخفاء بعض الصفوف لتيسير عرض النتيجة)

6- لإظهار منتجات Samsung فقط بالنطاق المنزلق، قام عمرو باستخدام الدالة FILTER لتصفية النطاق الأصلي، بمعيار القيم TRUE فقط، وذلك عن طريق تعديل الممعادلة بالخطوة 4 لتصبح كما يلي:

7- قام عمرو بالضغط على OK في مربع Function Arguments فتم عرض منتجات Samsung فقط داخل النطاق المنزلق في العمود I كما توضح الصورة التالية:

وتكون المعادلة المستخدمة بالكامل هي:

=FILTER(B3:B51,ISNUMBER(SEARCH(N2,B3:B51)))

(لاختبار صحة التنفيذ يمكن تغيير القيمة Samsung داخل الخلية N2 ليتغير النطاق المنزلق ويظهر نتائج البحث الجديدة.

8- بعد انتهاء إعداد مصدر القائمة، قام عمرو بتحديد الخلية N2 والمطلوب عمل القائمة المنسدلة بها، ومن علامة تبويب Data ومجموعة أوامر Data Tools قام عمرو بتحديد الأمر Data Validation لتظهر نافذة Data Validation، حيث قام عمرو بضبط الإعدادات في تبويب Settings كما يظهر بالصورة التالية:

حيث قام عمرو بإضافة علامة # داخل مربع Source بعد تحديد الخلية I3 ليتم استخدام النطاق المنزلق بالكامل كمصدر للقائمة المنسدلة مهما اختلف حجمه

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

9- أثناء تحديد الخلية N2، قام عمرو بالضغط من علامة تبويب Data على أمر Data Validation مرة أخرى، ثم قام بإلغاء تفعيل رسالة الخطأ من علامة تبويب Error Alert كما تظهر بالصورة التالية:

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

10- تحديث: إذا كان المطلوب تطبيق القائمة المنسدلة التي تتغير عناصرها بتغير نتيجة البحث على عمود كامل فإننا نحتاج إلى أن تكون نتيجة المعادلة باستخدام الدالة Filter في شكل صف. حتى تكون المعادلة صالحة للتطبيق على نطاق العمود بالكامل. يمكن عمل ذلك باستخدام الدالة Transpose داخل نافذة Data Validation فتصبح المعادلة المشار إليها في الخطوة رقم 7 بعد التحديث كالتالي:

=TRANSPOSE(FILTER(D3:D51,ISNUMBER(SEARCH(B2,D3:D51))))

وفي هذه الحالة ونظرا لأن نتيجة البحث تتغير في كل خلية فإننا نحتاج إلى نطاق منفصل لكل قائمة منسدلة، لذلك نحتاج إلى تغيير النطاق Source في نافذة Data Validation والمشار إليه في الخطوة رقم 8 إلى

=$I3#

بدون علامة التثبيت في منتصف مرجع الخلية، حتى يتحول مرجع الصف إلى مرجع متحرك

المزيد عن القوائم المنسدلة في Excel

https://facebook.com/msofficemania/photos/a.236273829741140/1971105669591272/