الدليل النهائي للقائمة المنسدلة القابلة للبحث في Excel

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

• القائمة المنسدلة القابلة للبحث في Excel 365
• إنشاء قائمة منسدلة قابلة للبحث (لـ Excel 2019 والإصدارات الأحدث)
• إنشاء قائمة منسدلة قابلة للبحث بسهولة (لجميع إصدارات Excel)
• إنشاء قائمة منسدلة قابلة للبحث باستخدام مربع التحرير والسرد وVBA (أكثر تعقيدًا)

فيديو: إنشاء قائمة منسدلة قابلة للبحث

القائمة المنسدلة القابلة للبحث في Excel 365

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

في هذه الحالة، عندما أكتب San في الخلية، تقوم القائمة المنسدلة بتصفية المدن التي تبدأ بالمصطلح San، مثل San Francisco وSan Diego. ثم يمكنك تحديد نتيجة باستخدام الماوس أو استخدام مفاتيح الأسهم والضغط على Enter.

إنشاء قائمة منسدلة قابلة للبحث (لـ Excel 2019 والإصدارات الأحدث)

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

بافتراض أنك قد أنشأت قائمة منسدلة في الخلية A2 من Sheet2 (الصورة على اليمين) باستخدام البيانات في النطاق A2:A8 من Sheet1 (الصورة على اليسار)، اتبع هذه الخطوات لجعل القائمة قابلة للبحث.

الخطوة 1. إنشاء عمود مساعد يسرد عناصر البحث

هنا نحتاج إلى عمود مساعد لسرد العناصر التي تطابق بيانات المصدر. في هذه الحالة، سأقوم بإنشاء العمود المساعد في العمود D من Sheet1.

1. حدد الخلية الأولى D1 في العمود D وأدخل رأس العمود، مثل "نتائج البحث" في هذه الحالة.
2. أدخل الصيغة التالية في الخلية D2 واضغط Enter.

   =FILTER(A2:A8,ISNUMBER(SEARCH(Sheet2!A2,A2:A8)),"Not Found")

   

الخطوة 2: إعادة تكوين القائمة المنسدلة

1. حدد الخلية التي تحتوي على القائمة المنسدلة (في هذه الحالة، أختار الخلية A2 من Sheet2)، ثم انتقل إلى تحديد البيانات > التحقق من صحة البيانات > التحقق من صحة البيانات.
   
2. في مربع الحوار التحقق من صحة البيانات , تحتاج إلى التكوين كما يلي.
   1. تحت علامة التبويب الإعدادات , انقر فوق الزر في مربع المصدر .
      
   2. سيعيد مربع الحوار التحقق من صحة البيانات التوجيه إلى Sheet1، حدد الخلية (مثل D2) التي تحتوي على الصيغة من الخطوة 1، أضف رمز #، وانقر على زر الإغلاق .
      
   3. انتقل إلى علامة التبويب تنبيه الخطأ، وألغِ تحديد خانة اختيار إظهار تنبيه الخطأ بعد إدخال بيانات غير صالحة، وأخيرًا انقر على زر موافق لحفظ التغييرات.
      

النتيجة

القائمة المنسدلة في الخلية A2 من Sheet2 أصبحت الآن قابلة للبحث. اكتب نصًا في الخلية، انقر فوق السهم المنسدل لتوسيع القائمة المنسدلة، وسترى القائمة تُصفى فورًا لتتطابق مع النص المكتوب.

إنشاء قائمة منسدلة قابلة للبحث بسهولة (لجميع إصدارات Excel)

بالنظر إلى القيود المختلفة للطرق المذكورة أعلاه، إليك أداة فعالة للغاية لك - ميزة Make Drop-down List Searchable, Auto-popup من Kutools لـ Excel. هذه الميزة متاحة في جميع إصدارات Excel وتتيح لك بسهولة البحث عن العنصر المطلوب في القائمة المنسدلة بتكوين بسيط.

بعد تنزيل وتثبيت Kutools لـ Excel، حدد Kutools > القائمة المنسدلة > Make Drop-down List Searchable, Auto-popup لتمكين هذه الميزة. في مربع الحوار Make the Drop-down List Searchable، تحتاج إلى:

1. حدد النطاق الذي يحتوي على القوائم المنسدلة التي تحتاج إلى تعيينها كقوائم منسدلة قابلة للبحث.
2. انقر موافق لإكمال الإعدادات.
   

النتيجة

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

إنشاء قائمة منسدلة قابلة للبحث باستخدام مربع التحرير والسرد وVBA (أكثر تعقيدًا)

إذا كنت تريد ببساطة إنشاء قائمة منسدلة قابلة للبحث دون تحديد نوع معين من القوائم المنسدلة. يوفر هذا القسم نهجًا بديلًا: استخدام مربع التحرير والسرد مع كود VBA لتحقيق المهمة.

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

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

1. إذا كانت علامة المطور لا تظهر على الشريط، يمكنك تمكين علامة المطور كما يلي.
   1. في Excel 2010 أو الإصدارات الأحدث، انقر فوق ملف > خيارات. وفي مربع الحوار خيارات Excel، انقر فوق تخصيص الشريط في الجزء الأيسر. انتقل إلى مربع قائمة تخصيص الشريط، وحدد مربع المطور، ثم انقر فوق زر موافق. انظر لقطة الشاشة:
      
   2. في Excel 2007، انقر فوق Office > خيارات Excel. في مربع الحوار خيارات Excel، انقر فوق شائع في الجزء الأيسر، وحدد مربع إظهار علامة المطور في الشريط، وأخيرًا انقر فوق زر موافق.
      
2. بعد إظهار علامة المطور، انقر فوق المطور > إدراج > مربع التحرير والسرد.
   
3. ارسم مربع تحرير وسرد في ورقة العمل، انقر بزر الماوس الأيمن عليه ثم حدد الخصائص من قائمة النقر بزر الماوس الأيمن.
   
4. في مربع الحوار الخصائص , تحتاج إلى:
   1. حدد False في حقل AutoWordSelect؛
   2. حدد خلية في حقل LinkedCell. في هذه الحالة، ندخل A12؛
   3. حدد 2-fmMatchEntryNone في حقل MatchEntry؛
   4. اكتب DropDownList في حقل ListFillRange؛
   5. أغلق مربع الحوار الخصائص. انظر لقطة الشاشة:
      
5. الآن قم بإيقاف وضع التصميم عن طريق النقر فوق المطور > وضع التصميم.
6. حدد خلية فارغة مثل C2، أدخل الصيغة أدناه واضغط Enter. ثم اسحب مقبض التعبئة التلقائية إلى الخلية C9 لتعبئة الخلايا بنفس الصيغة. انظر لقطة الشاشة:

   =--ISNUMBER(IFERROR(SEARCH($A$12,A2,1),""))

   
   ملاحظات:

   1. $A$12 هي الخلية التي حددتها كـ LinkedCell في الخطوة 4؛
   2. بعد الانتهاء من الخطوات أعلاه، يمكنك الآن اختبار: أدخل الحرف C في مربع التحرير والسرد، وسترى أن الخلايا التي تحتوي على الصيغة والتي تشير إلى الخلايا التي تحتوي على الحرف C مملوءة بالرقم 1.
7. حدد الخلية D2، أدخل الصيغة أدناه واضغط Enter. ثم اسحب مقبض التعبئة التلقائية إلى الخلية D9.

   =IF(C2=1,COUNTIF($C$2:C2,1),"")

   
8. حدد الخلية E2، أدخل الصيغة أدناه واضغط Enter. ثم اسحب مقبض التعبئة التلقائية إلى E9 لتطبيق نفس الصيغة.

   =IFERROR(INDEX($A$2:$A$9,MATCH(ROWS($D$2:D2),$D$2:$D$9,0)),"")

   
9. الآن تحتاج إلى إنشاء نطاق اسم. يرجى النقر فوق الصيغة > تعريف الاسم.
   
10. في مربع الحوار الاسم الجديد , اكتب DropDownList في مربع الاسم , أدخل الصيغة أدناه في مربع يشير إلى , ثم انقر فوق الزر موافق .

    =$E$2:INDEX($E$2:$E$9,MAX($D$2:$D$9),1)
    

    
11. الآن، قم بتشغيل وضع التصميم عن طريق النقر فوق المطور > وضع التصميم. ثم انقر نقرًا مزدوجًا فوق مربع التحرير والسرد لفتح نافذة Microsoft Visual Basic for Applications.
12. انسخ والصق كود VBA أدناه في محرر الكود.
    
    كود VBA: جعل القائمة المنسدلة قابلة للبحث

    Private Sub ComboBox1_GotFocus()
    	ComboBox1.ListFillRange = "DropDownList"
    	Me.ComboBox1.DropDown
    End Sub

13. اضغط على مفتاح Alt + Q لإغلاق نافذة Microsoft Visual Basic for Applications.

من الآن فصاعدًا، عند إدخال حرف في مربع التحرير والسرد، سيقوم بإجراء بحث غامض ثم سرد القيم ذات الصلة في القائمة.

مقالات ذات صلة: