إنشاء مربع بحث في Excel - دليل خطوة بخطوة

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

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

إنشاء مربع بحث بسهولة باستخدام دالة FILTER

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

هنا سأوضح لك كيفية استخدام دالة FILTER لإنشاء مربع بحث في Excel.

الخطوة 1: إدراج مربع نص وتكوين الخصائص

1. انتقل إلى علامة التبويب "Developer"، انقر فوق "Insert" > "Text Box (ActiveX Control)".
   نصيحة: إذا لم تظهر علامة التبويب "Developer" على الشريط، يمكنك تمكينها باتباع التعليمات الموجودة في هذا البرنامج التعليمي: How to show/display developer tab in Excel Ribbon?
   
2. سيتحول المؤشر إلى شكل صليب، ثم تحتاج إلى سحب المؤشر لرسم مربع النص في الموقع الذي تريد وضع مربع النص فيه داخل ورقة العمل. بعد رسم مربع النص، حرر الماوس.
   
3. انقر بزر الماوس الأيمن على مربع النص وحدد "Properties" من قائمة السياق.
   
4. في لوحة "Properties"، قم بربط مربع النص بخلية عن طريق إدخال مرجع الخلية في حقل "LinkedCell". على سبيل المثال، كتابة "J2" تضمن أن أي بيانات يتم إدخالها في مربع النص سيتم تحديثها تلقائيًا في الخلية J2، وبالعكس.
   
5. انقر على "Design Mode" تحت علامة التبويب "Developer" للخروج من "Design Mode".
   

يتيح مربع النص الآن إدخال النص.

الخطوة 2: تطبيق دالة FILTER

1. قبل استخدام دالة FILTER، انسخ صف الرأس الأصلي إلى منطقة جديدة. هنا أضع صف الرأس أسفل مربع البحث.
   نصيحة: يتيح هذا النهج للمستخدمين رؤية النتائج بوضوح تحت نفس عناوين الأعمدة كما هو الحال في البيانات الأصلية.
   
2. حدد الخلية أسفل العنوان الأول (مثل I5 في هذا المثال)، وأدخل الصيغة التالية فيها واضغط على زر "Enter" للحصول على النتيجة.

   =FILTER(Sheet2!$A$5:$G$281,Sheet2!$B$5:$B$281=J2,"No data found")

   
   كما هو موضح في لقطة الشاشة أعلاه، وبما أن مربع النص لا يحتوي على أي إدخال الآن، تعرض الصيغة النتيجة "No data found" في I5.

النتيجة: اختبر مربع البحث

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

إنشاء مربع بحث باستخدام التنسيق الشرطي

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

الخطوة 1: إدراج مربع نص وتكوين الخصائص

1. انتقل إلى علامة التبويب "Developer"، انقر فوق "Insert" > "Text Box (ActiveX Control)".
   نصيحة: إذا لم تظهر علامة التبويب "Developer" على الشريط، يمكنك تمكينها باتباع التعليمات الموجودة في هذا البرنامج التعليمي: How to show/display developer tab in Excel Ribbon?
   
2. سيتحول المؤشر إلى شكل صليب، ثم تحتاج إلى سحب المؤشر لرسم مربع النص في الموقع الذي تريد وضع مربع النص فيه داخل ورقة العمل. بعد رسم مربع النص، حرر الماوس.
   
3. انقر بزر الماوس الأيمن على مربع النص وحدد Properties من قائمة السياق.
   
4. في لوحة "Properties"، قم بربط مربع النص بخلية عن طريق إدخال مرجع الخلية في حقل "LinkedCell". على سبيل المثال، كتابة "J3" تضمن أن أي بيانات يتم إدخالها في مربع النص سيتم تحديثها تلقائيًا في الخلية J3، وبالعكس.
   
5. انقر على "Design Mode" تحت علامة التبويب "Developer" للخروج من "Design Mode".
   

يتيح مربع النص الآن إدخال النص.

الخطوة 2: تطبيق التنسيق الشرطي للبحث عن البيانات

1. حدد نطاق البيانات بالكامل الذي سيتم البحث فيه. هنا أختار النطاق A3:G279.
2. تحت علامة التبويب "Home"، انقر على "Conditional Formatting" > "New Rule".
   
3. في مربع الحوار "New Formatting Rule":
   1. حدد "Use a formula to determine which cells to format" في خيارات "Select a Rule Type".
   2. أدخل الصيغة التالية في مربع "Format values where this formula is true".

      =$B3=$J$3

      هنا، "$B3" يمثل الخلية الأولى في العمود الذي تريد مطابقته مع معايير البحث في النطاق المحدد، و "$J$3" هي الخلية المرتبطة بمربع البحث.
   3. انقر على زر "Format" لتحديد لون تعبئة لنتائج البحث.
   4. انقر على زر "OK". انظر لقطة الشاشة:
      

النتيجة

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

إنشاء مربع بحث باستخدام تركيبات الصيغ

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

الخطوة 1: إنشاء قائمة بالقيم الفريدة من عمود البحث

1. في هذه الحالة، أختار وأنسخ النطاق "B4:B281" إلى ورقة عمل جديدة.
2. بعد لصق النطاق في ورقة عمل جديدة، احتفظ بالبيانات الملصقة محددة، انتقل إلى علامة التبويب "Data" وحدد "Remove Duplicates".
   
3. في مربع الحوار "Remove Duplicates" الذي يفتح، انقر على زر "OK".
   
4. ثم يظهر مربع رسالة "Microsoft Excel" لإظهار عدد التكرارات التي تم إزالتها. انقر على "OK".
   
5. بعد إزالة التكرارات، حدد جميع القيم الفريدة في القائمة، باستثناء العنوان، وخصص اسمًا لهذا النطاق عن طريق إدخاله في مربع "Name". هنا سميت النطاق باسم "Customer".
   

الخطوة 2: إدراج مربع تحرير وسرد وتكوين الخصائص

1. عد إلى ورقة العمل التي تحتوي على مجموعة البيانات التي تريد البحث عنها. انتقل إلى علامة التبويب "Developer"، انقر فوق "Insert" > "Combo Box (ActiveX Control)".
   نصيحة: إذا لم تظهر علامة التبويب "Developer" على الشريط، يمكنك تمكينها باتباع التعليمات الموجودة في هذا البرنامج التعليمي: How to show/display developer tab in Excel Ribbon?
   
2. سيتحول المؤشر إلى شكل صليب، ثم تحتاج إلى سحب المؤشر لرسم مربع التحرير والسرد في الموقع الذي تريد وضع مربع البحث فيه داخل ورقة العمل. بعد رسم مربع التحرير والسرد، حرر الماوس.
   
3. انقر بزر الماوس الأيمن على مربع التحرير والسرد وحدد "Properties" من قائمة السياق.
   
4. في لوحة "Properties":
   1. قم بربط مربع التحرير والسرد بخلية عن طريق إدخال مرجع الخلية في حقل "LinkedCell". هنا أكتب "M2".
      نصيحة: تحديد هذا الحقل يضمن أن أي بيانات يتم إدخالها في مربع التحرير والسرد سيتم تحديثها تلقائيًا في الخلية M2، وبالعكس.
   2. في حقل "ListFillRange"، أدخل "اسم النطاق" الذي حددته للقائمة الفريدة في الخطوة 1.
   3. غير حقل "MatchEntry" إلى "2 – fmMatchEntryNone".
   4. أغلق لوحة "Properties".
      
5. انقر على "Design Mode" تحت علامة التبويب "Developer" للخروج من وضع التصميم.
   

يمكنك الآن تحديد أي عنصر من مربع التحرير والسرد أو الكتابة في النص للبحث عنه.

الخطوة 3: تطبيق الصيغ

1. إنشاء ثلاثة أعمدة مساعدة بجانب نطاق البيانات الأصلي. انظر لقطة الشاشة:
   
2. في الخلية (H5) أسفل عنوان العمود المساعد الأول، أدخل الصيغة التالية واضغط على "Enter".

   =ROWS($B$5:B5)

   هنا "B5" هي الخلية التي تحتوي على اسم العميل الأول في العمود الذي سيتم البحث فيه.
   
3. انقر نقرًا مزدوجًا على الزاوية اليمنى السفلية للخلية التي تحتوي على الصيغة، وسيتم تعبئة الخلايا التالية تلقائيًا بنفس الصيغة.
   
4. في الخلية (I5) أسفل عنوان العمود المساعد الثاني، أدخل الصيغة التالية واضغط على "Enter". ثم انقر نقرًا مزدوجًا على الزاوية اليمنى السفلية للخلية التي تحتوي على الصيغة لتعبئة الخلايا أدناه تلقائيًا بنفس الصيغة.

   =IF(ISNUMBER(SEARCH($M$2,B5)),H5,"")

   هنا "M2" هي الخلية المرتبطة بمربع التحرير والسرد.
   
5. في الخلية (J5) أسفل عنوان العمود المساعد الثالث، أدخل الصيغة التالية واضغط على "Enter". ثم انقر نقرًا مزدوجًا على الزاوية اليمنى السفلية للخلية التي تحتوي على الصيغة لتعبئة الخلايا أدناه تلقائيًا بنفس الصيغة.

   =IFERROR(SMALL($I$5:$I$281,H5),"") 

   
6. انسخ صف الرأس الأصلي إلى منطقة جديدة. هنا أضع صف الرأس أسفل مربع البحث.
   
7. حدد الخلية أسفل العنوان الأول (مثل L5 في هذا المثال)، وأدخل الصيغة التالية فيها واضغط على زر "Enter".

   =IFERROR(INDEX($A$5:$G$281,$J5,COLUMNS($L$4:L4)),"")

   هنا "A5:G281" هو نطاق البيانات بالكامل الذي تريد عرضه في خلية النتيجة.
   
8. حدد هذه الخلية التي تحتوي على الصيغة، واسحب "Fill Handle" إلى اليمين ثم إلى الأسفل لتطبيق الصيغة على الأعمدة والصفوف المقابلة.
   
   ملاحظات:

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

النتيجة

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

إنشاء مربع بحث في Excel يمكن أن يحسن بشكل كبير كيفية تفاعلك مع بياناتك، مما يجعل جداول البيانات الخاصة بك أكثر ديناميكية وسهولة في الاستخدام. سواء اخترت البساطة في دالة FILTER، المساعدة البصرية للتنسيق الشرطي، أو تنوع تركيبات الصيغ، توفر كل طريقة أدوات قيمة لتعزيز قدراتك في التعامل مع البيانات. جرب هذه التقنيات لمعرفة أي منها يعمل بشكل أفضل لاحتياجاتك وسيناريوهات البيانات الخاصة بك. بالنسبة لأولئك الذين يتوقون إلى الغوص بعمق في قدرات Excel، يحتوي موقعنا على ثروة من البرامج التعليمية. Discover more Excel tips and tricks here.

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