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

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

فيديو

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

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

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

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

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

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

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

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

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

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

   

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

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

نتيجة

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

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

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

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

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

نتيجة

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

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

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

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

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

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

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

   
   ملاحظة:

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

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

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

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

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

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

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

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

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

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

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