انتقل إلى المحتوى الرئيسي

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

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


فيديو


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

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

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

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

إنشاء قائمة منسدلة قابلة للبحث (لبرنامج 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")
ملاحظة:
  • في هذه الصيغة، A2: A8 هو نطاق البيانات المصدر. الورقة 2! A2 هو موقع القائمة المنسدلة، مما يعني أن القائمة المنسدلة موجودة في A2 من الورقة2. يرجى تغييرها وفقا للبيانات الخاصة بك.
  • إذا لم يتم تحديد أي عنصر من القائمة المنسدلة في A2 من الورقة2، فستعرض الصيغة جميع العناصر من البيانات المصدر، كما هو موضح في الصورة أعلاه. وعلى العكس من ذلك، إذا تم تحديد عنصر ما، فسوف يعرض D2 هذا العنصر كنتيجة للصيغة.
الخطوة 2: إعادة تكوين القائمة المنسدلة
  1. حدد خلية القائمة المنسدلة (في هذه الحالة، أحدد الخلية A2 من الورقة2)، ثم انتقل للتحديد البيانات > التحقق من صحة البيانات > التحقق من صحة البيانات.
  2. في مجلة التحقق من صحة البيانات مربع الحوار، تحتاج إلى تكوين على النحو التالي.
    1. تحت المبادرة من الإعدادات ، انقر فوق زر في مصدر مربع.
    2. التحقق من صحة البيانات سيقوم مربع الحوار بإعادة التوجيه إلى الورقة 1، وحدد الخلية (على سبيل المثال، D2) التي تحتوي على الصيغة من الخطوة 1، وأضف # ، وانقر فوق اغلاق .
    3. انتقل إلى تنبيه خطأ علامة التبويب، قم بإلغاء تحديد إظهار تنبيه خطأ بعد إدخال بيانات غير صالحة خانة الاختيار، وأخيرا انقر فوق OK زر لحفظ التغييرات.
نتيجة

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

ملاحظة:
  • هذه الطريقة متاحة فقط لـ Excel 2019 والإصدارات الأحدث.
  • تعمل هذه الطريقة فقط على خلية قائمة منسدلة واحدة في كل مرة. لجعل القوائم المنسدلة قابلة للبحث في الخلايا من A3 إلى A8 في الورقة2، يجب تكرار الخطوات المذكورة أعلاه لكل خلية.
  • عند كتابة نص في خلية القائمة المنسدلة، لا يتم توسيع القائمة المنسدلة تلقائيًا، ستحتاج إلى النقر فوق سهم القائمة المنسدلة لتوسيعها يدويًا.

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

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

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

  1. حدد الخيار الذي تحتاجه في حدد النطاق قائمة منسدلة.
    في هذه الحالة، أحتاج إلى إنشاء قائمة منسدلة في نطاق محدد قابلة للبحث، لذلك قمت بتحديد حدد النطاق الخيار وحدد النطاق.
  2. انقر OK.
نتيجة

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

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

إنشاء قائمة منسدلة قابلة للبحث باستخدام 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 مفاتيح لإغلاق ميكروسوفت فيسوال باسيك للتطبيقات نافذة.

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

ملاحظات: تحتاج إلى حفظ هذا المصنف كملف Excel Macro-Enabled Workbook من أجل الاحتفاظ برمز VBA للاستخدام في المستقبل.

أفضل أدوات إنتاجية المكتب

كوتولس ل إكسيل - يساعدك على التميز من بين الحشود

🤖 مساعد Kutools AI: إحداث ثورة في تحليل البيانات على أساس: التنفيذ الذكي   |  إنشاء التعليمات البرمجية  |  إنشاء صيغ مخصصة  |  تحليل البيانات وإنشاء الرسوم البيانية  |  استدعاء وظائف Kutools...
الميزات الشعبية: البحث عن التكرارات أو تمييزها أو تحديدها  |  حذف الصفوف الفارغة  |  دمج الأعمدة أو الخلايا دون فقدان البيانات  |  جولة بدون صيغة 
سوبر فيلوكوب: معايير متعددة  |  قيمة متعددة  |  عبر الأوراق المتعددة  |  بحث غامض
حال. قائمة منسدلة: قائمة منسدلة سهلة  |  القائمة المنسدلة التابعة  |  قائمة منسدلة متعددة التحديد
مدير العمود: إضافة عدد محدد من الأعمدة  |  نقل الأعمدة  |  تبديل حالة رؤية الأعمدة المخفية  قارن الأعمدة ب حدد نفس الخلايا وخلايا مختلفة 
الميزات المميزة: التركيز على الشبكة  |  عرض تصميم  |  شريط الفورمولا الكبير  |  مدير المصنفات والأوراق | مكتبة الموارد (النص السيارات)  |  منتقي التاريخ  |  اجمع أوراق العمل  |  تشفير/فك تشفير الخلايا  |  إرسال رسائل البريد الإلكتروني عن طريق القائمة  |  سوبر تصفية  |  مرشح خاص (تصفية غامق / مائل / يتوسطه خط ...) ...
أفضل 15 مجموعة أدوات12 نص الأدوات (إضافة نص, إزالة الأحرف ...)  |  +50 رسم الأنواع (مخطط جانت ...)  |  40+ عملي الصيغ (احسب العمر على أساس تاريخ الميلاد ...)  |  19 إدخال الأدوات (أدخل رمز الاستجابة السريعة, إدراج صورة من المسار ...)  |  12 تحويل الأدوات (أرقام إلى كلمات, نتيجة تحويل عملة ...)  |  7 دمج وتقسيم الأدوات (الجمع بين الصفوف المتقدمة, تقسيم خلايا إكسل ...)  |  ... و اكثر

Kutools for Excel يضم أكثر من 300 ميزة، التأكد من أن ما تحتاجه هو مجرد نقرة واحدة...

الوصف


علامة تبويب Office - تمكين القراءة والتحرير المبوب في Microsoft Office (بما في ذلك Excel)

  • ثانية واحدة للتبديل بين عشرات المستندات المفتوحة!
  • قلل مئات النقرات بالماوس كل يوم ، وداعًا ليد الماوس.
  • يزيد من إنتاجيتك بنسبة 50٪ عند عرض مستندات متعددة وتحريرها.
  • يجلب علامات التبويب الفعالة إلى Office (بما في ذلك Excel)، تمامًا مثل Chrome وEdge وFirefox.