الدليل الشامل لـ جعل قائمة الانسحاب قابلة للبحث في Excel

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

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

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

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

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

في هذه الحالة، اكتبSan في الخلية، فتظهر لك قائمة منسدلة تُرشّح المدن التي تبدأ بمصطلح البحثSan، مثلسان فرانسسكووسان دييغو. بعد ذلك، يمكنك اختيار النتيجة التي تريدها إما بالنقر عليها بالماوس أو باستخدام مفاتيح الأسهم ثم الضغط على 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)

نظرًا للقيود المختلفة في الطرق السابقة، إليك أداة فعّالة جدًّا لك — Kutools لـ Excelمع خاصيةجعل القائمة المنسدلة قابلة للبحث والظهور التلقائي. هذه الخاصية متاحة في جميع إصدارات Excel وتتيح لك العثور بسهولة على العنصر المطلوب في القائمة المنسدلة بمجرد إعداد بسيط!

بعدتنزيل وتثبيت Kutools لـ Excel، اخترKutools > قائمة منسدلة > جعل قائمة منسدلة قابلة للبحث، والظهور التلقائيلتفعيل هذه الميزة. في مربع الحواراجعل قائمة منسدلة قابلة للبحث، قم بما يلي:

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

النتيجة

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

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

إذا كنت ترغب فقط في جعل قائمة الانسحاب قابلة للبحث دون تحديد نوع معين من القوائم المنسدلة، يقدّم هذا القسم نهجًا بديلاً: استخدام مربع التحرير والسرد (Combo Box) مع كود VBA لتحقيق ما تريد.

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

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

1. إذا لم تظهر علامة التبويبالمطوّرفي الشريط، فيمكنك تمكين علامة التبويبالمطوّرعلى النحو التالي.
   1. في Excel 2010 أو الإصدارات الأحدث، انقر علىملف > خيارات. في مربع حوارخيارات Excel، انقر علىتخصيص الشريطفي الجزء الأيسر. من قائمة «تخصيص الشريط»، فعِّل خانةالمطور، ثم انقر على زرموافق. انظر لقطة الشاشة:
      
   2. في Excel 2007، انقر علىزر Office > خيارات Excel. في مربع حوارخيارات Excel، انقر علىشائعفي الجزء الأيسر، وفعّل خانةإظهار علامة تبويب المطور في الشريط، ثم انقر على زرموافق.
      
2. بعد عرض علامة التبويبالمطوّر، انقرالمطوّر>إدراج>مربع التحديد (Combo box).
   
3. ارسم مربع تحديد (Combo box) في ورقة العمل، ثم انقر عليه بزر الماوس الأيمن واخترخصائصمن قائمة النقر بزر الماوس الأيمن.
   
4. في مربع الحوارالخصائص، عليك بما يلي:
   1. اخترFalseفي حقلAutoWordSelect؛
   2. حدّد خلية في حقلLinkedCell. في هذه الحالة، سنُدخل A12؛
   3. اختر2-fmMatchEntryNoneفي حقلMatchEntry؛
   4. اكتبDropDownListفي حقلListFillRange؛
   5. أغلق مربع حوارالخصائص. انظر لقطة الشاشة:
      
5. الآن، أوقف وضع التصميم بالنقر علىالمطوّر > وضع التصميم.
6. حدد خلية فارغة مثل C2، وأدخل الصيغة أدناه واضغطEnter. ثم اسحب مقبض التعبئة التلقائية (AutoFill Handle) لأسفل حتى الخلية 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. الآن، شغّل وضع التصميم بالنقر علىالمطوّر > وضع التصميم. ثم انقر نقرًا مزدوجًا على مربع التحديد (Combo Box) لفتح نافذةمايكروسوفت فيجوال بيسك للتطبيقات.
12. انسخ ولصق كود VBA أدناه في محرر الكود.
    
    كود VBA: جعل القائمة المنسدلة قابلة للبحث

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

13. اضغط على مفاتيحAlt + Q لإغلاق نافذةمايكروسوفت فيجوال بيسك للتطبيقات.

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

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