كيفية إنشاء صيغة بحث Excel بمعايير متعددة

2024 مؤلف: Abigail Brown | [email protected]. آخر تعديل: 2024-01-07 19:00

ما يجب معرفته

• أولاً ، قم بإنشاء دالة INDEX ، ثم ابدأ وظيفة MATCH المتداخلة عن طريق إدخال وسيطة Lookup_value.
• بعد ذلك ، أضف وسيطة Lookup_array متبوعة بالوسيطة Match_type ، ثم حدد نطاق العمود.
• ثم قم بتحويل الدالة المتداخلة إلى صيغة صفيف بالضغط على Ctrl+ Shift+ أدخل. أخيرًا ، أضف مصطلحات البحث إلى ورقة العمل.

تشرح هذه المقالة كيفية إنشاء صيغة بحث تستخدم معايير متعددة في Excel للعثور على معلومات في قاعدة بيانات أو جدول بيانات باستخدام صيغة صفيف.تتضمن صيغة الصفيف تداخل دالة MATCH داخل دالة INDEX. تغطي المعلومات Excel لـ Microsoft 365 و Excel 2019 و Excel 2016 و Excel 2013 و Excel 2010 و Excel لنظام التشغيل Mac.

اتبع جنبا إلى جنب مع البرنامج التعليمي

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

• أدخل النطاق الأعلى للبيانات في الخلايا من D1 إلى F2.
• أدخل النطاق الثاني في الخلايا من D5 إلى F11.

إنشاء دالة INDEX في Excel

وظيفة INDEX هي واحدة من الوظائف القليلة في Excel التي لها أشكال متعددة. تحتوي الوظيفة على نموذج مصفوفة ونموذج مرجعي. يقوم Array Form بإرجاع البيانات من قاعدة بيانات أو جدول بيانات.يعطي النموذج المرجعي مرجع الخلية أو موقع البيانات في الجدول.

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

اتبع هذه الخطوات لإنشاء وظيفة الفهرس:

1. حدد الخلية F3لجعلها الخلية النشطة. هذه الخلية هي المكان الذي سيتم فيه إدخال الوظيفة المتداخلة.

2. انتقل إلى الصيغ.

   

3. اختر بحث ومرجعلفتح قائمة الوظائف المنسدلة.
4. حدد INDEX لفتح مربع الحوار حدد الوسيطات.
5. اختر مجموعة ، row_num ، column_num.
6. حدد OK لفتح مربع الحوار وسيطات الوظيفة. في Excel for Mac ، يفتح Formula Builder.
7. ضع المؤشر في مربع النص Array.

8. قم بتمييز الخلايا D6 إلى F11في ورقة العمل لإدخال النطاق في مربع الحوار.

   اترك مربع حوار وسيطات الوظيفة مفتوحًا. الصيغة ليست منتهية. ستكمل الصيغة في التعليمات أدناه.

   

بدء وظيفة MATCH المتداخلة

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

عند إدخال الوظائف يدويًا ، يتم فصل وسيطات الوظيفة عن بعضها البعض بفاصلة.

الخطوة الأولى لإدخال دالة MATCH المتداخلة هي إدخال وسيطة Lookup_value. Lookup_value هو الموقع أو مرجع الخلية لمصطلح البحث المراد مطابقته في قاعدة البيانات.

يقبل Lookup_value معايير أو مصطلح بحث واحد فقط. للبحث عن معايير متعددة ، قم بتوسيع Lookup_value عن طريق ربط اثنين أو أكثر من مراجع الخلايا أو الانضمام إليها باستخدام رمز علامة العطف (&).

1. في مربع الحوار Function Arguments ، ضع المؤشر في مربع النص Row_num.
2. أدخل MATCH (.
3. حدد الخلية D3لإدخال مرجع الخلية في مربع الحوار.
4. أدخل &(علامة العطف) بعد مرجع الخلية D3لإضافة مرجع خلية ثانية.
5. حدد الخلية E3لإدخال مرجع الخلية الثانية.

6. أدخل ،(فاصلة) بعد مرجع الخلية E3 لإكمال إدخال وسيطة Lookup_value لوظيفة MATCH.

   

   في الخطوة الأخيرة من البرنامج التعليمي ، سيتم إدخال قيم Lookup_values في الخلايا D3 و E3 من ورقة العمل.

أكمل وظيفة المطابقة المتداخلة

تغطي هذه الخطوة إضافة وسيطة Lookup_array لوظيفة MATCH المتداخلة. إن Lookup_array هو نطاق الخلايا الذي تبحث عنه وظيفة MATCH للعثور على وسيطة Lookup_value المضافة في الخطوة السابقة من البرنامج التعليمي.

نظرًا لأنه تم تحديد حقلي بحث في وسيطة Lookup_array ، يجب إجراء نفس الشيء لـ Lookup_array. تبحث الدالة MATCH في مصفوفة واحدة فقط لكل مصطلح محدد. لإدخال مصفوفات متعددة ، استخدم علامة العطف لربط المصفوفات معًا.

1. ضع المؤشر في نهاية البيانات في مربع النص Row_num. يظهر المؤشر بعد الفاصلة في نهاية الإدخال الحالي.
2. قم بتمييز الخلايا D6 إلى D11في ورقة العمل لإدخال النطاق. هذا النطاق هو المصفوفة الأولى التي تبحث عنها الوظيفة.
3. أدخل &(علامة العطف) بعد مراجع الخلية D6: D11. يتسبب هذا الرمز في قيام الوظيفة بالبحث عن صفيفتين.

4. قم بتمييز الخلايا E6 إلى E11في ورقة العمل لإدخال النطاق. هذا النطاق هو المصفوفة الثانية التي تبحث عنها الوظيفة.

5. أدخل ،(فاصلة) بعد مرجع الخلية E3لإكمال إدخال وسيطة Lookup_array الخاصة بوظيفة MATCH.

   

6. اترك مربع الحوار مفتوحًا للخطوة التالية في البرنامج التعليمي.

أضف وسيطة نوع المباراة

الوسيطة الثالثة والأخيرة لدالة MATCH هي وسيطة Match_type. تخبر هذه الوسيطة Excel بكيفية مطابقة Lookup_value مع القيم في Lookup_array. الخيارات المتاحة هي 1 أو 0 أو -1.

هذه الحجة اختيارية. إذا تم حذفها ، فإن الدالة تستخدم القيمة الافتراضية 1.

• إذا كانت Match_type=1 أو تم حذفها ، فستجد MATCH أكبر قيمة أقل من أو تساوي Lookup_value. يجب فرز بيانات Lookup_array بترتيب تصاعدي.
• إذا كانت Match_type=0 ، فستجد MATCH القيمة الأولى التي تساوي Lookup_value. يمكن فرز بيانات Lookup_array بأي ترتيب.
• إذا كان Match_type=-1 ، فستجد MATCH أصغر قيمة أكبر من أو تساوي Lookup_value. يجب فرز بيانات Lookup_array بترتيب تنازلي.

أدخل هذه الخطوات بعد إدخال الفاصلة في الخطوة السابقة على سطر Row_num في دالة INDEX:

1. أدخل 0(صفر) بعد الفاصلة في مربع النص Row_num. يتسبب هذا الرقم في قيام الدالة المتداخلة بإرجاع المطابقات التامة للمصطلحات التي تم إدخالها في الخلايا D3 و E3.

2. أدخل )(قوس دائري إغلاق) لإكمال وظيفة المطابقة.

   

3. اترك مربع الحوار مفتوحًا للخطوة التالية في البرنامج التعليمي.

إنهاء وظيفة الفهرس

تتم وظيفة MATCH. حان الوقت للانتقال إلى مربع النص Column_num في مربع الحوار وإدخال الوسيطة الأخيرة للدالة INDEX. تخبر هذه الوسيطة Excel أن رقم العمود يقع في النطاق من D6 إلى F11. هذا النطاق هو المكان الذي يعثر فيه على المعلومات التي تم إرجاعها بواسطة الوظيفة.في هذه الحالة مورد الحاجيات التيتانيوم

1. ضع المؤشر في مربع النص Column_num.

2. أدخل 3(الرقم ثلاثة). يخبر هذا الرقم المعادلة بالبحث عن البيانات في العمود الثالث من النطاق D6 إلى F11.

   

3. اترك مربع الحوار مفتوحًا للخطوة التالية في البرنامج التعليمي.

إنشاء صيغة المصفوفة

قبل إغلاق مربع الحوار ، قم بتحويل الوظيفة المتداخلة إلى صيغة صفيف. تسمح هذه المصفوفة للوظيفة بالبحث عن مصطلحات متعددة في جدول البيانات. في هذا البرنامج التعليمي ، يتم مطابقة مصطلحين: أدوات من العمود 1 وتيتانيوم من العمود 2.

لإنشاء صيغة صفيف في Excel ، اضغط على CTRL ، SHIFT ، و ENTERمفتاحًا في وقت واحد. بمجرد الضغط على الوظيفة ، يتم إحاطتها بأقواس متعرجة ، مما يشير إلى أن الوظيفة أصبحت الآن مصفوفة.

1. حدد OK لإغلاق مربع الحوار. في Excel for Mac ، حدد Done.
2. حدد الخلية F3لعرض الصيغة ، ثم ضع المؤشر في نهاية الصيغة في شريط الصيغة.
3. لتحويل الصيغة إلى مصفوفة ، اضغط على CTRL+ SHIFT+ ENTER.
4. يظهر خطأAN / A في الخلية F3. هذه هي الخلية حيث تم إدخال الوظيفة.

5. يظهر الخطأN / A في الخلية F3 لأن الخلايا D3 و E3 فارغة. D3 و E3 هما الخلايا التي تبحث فيها الوظيفة عن Lookup_value. بعد إضافة البيانات إلى هاتين الخليتين ، يتم استبدال الخطأ بمعلومات من قاعدة البيانات.

   

أضف معايير البحث

الخطوة الأخيرة هي إضافة مصطلحات البحث إلى ورقة العمل. تتطابق هذه الخطوة مع المصطلحات Widgets من العمود 1 و Titanium من العمود 2.

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

1. حدد الخلية D3.
2. أدخل الحاجيات.
3. حدد الخلية E3.
4. اكتب التيتانيوم ، واضغط على أدخل.
5. يظهر اسم المورد ، Widgets Inc. ، في الخلية F3. هذا هو المورد الوحيد المدرج الذي يبيع أدوات التيتانيوم.

6. حدد الخلية F3. تظهر الوظيفة في شريط الصيغة أعلى ورقة العمل.

   {=INDEX (D6: F11، MATCH (D3 & E3، D6: D11 & E6: E11، 0)، 3)}
   في هذا المثال ، يوجد مورد واحد فقط لأدوات التيتانيوم. إذا كان هناك أكثر من مورد واحد ، يتم إرجاع المورد المدرج أولاً في قاعدة البيانات بواسطة الوظيفة.