صيغة بحث Excel مع معايير متعددة

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

تتضمن صيغة الصفيف تداخل الدالة MATCH داخل دالة INDEX .

يشتمل هذا البرنامج التعليمي على مثال خطوة بخطوة لإنشاء صيغة بحث تستخدم معايير متعددة للعثور على مورد لـ Tinyium Widgets في نموذج قاعدة بيانات.

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

01 من 09

دخول بيانات البرنامج التعليمي

الخطوة الأولى في البرنامج التعليمي هي إدخال البيانات في ورقة عمل Excel.

من أجل اتباع الخطوات الواردة في البرنامج التعليمي ، أدخل البيانات الموضحة في الصورة أعلاه في الخلايا التالية.

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

يتم ترك الصفوف 3 و 4 فارغة من أجل استيعاب صيغة الصفيف التي تم إنشاؤها خلال هذا البرنامج التعليمي.

لا يتضمن البرنامج التعليمي التنسيق المرئي في الصورة ، ولكن هذا لن يؤثر في كيفية عمل صيغة البحث.

تتوفر معلومات حول خيارات التنسيق المشابهة لتلك المذكورة أعلاه في هذا البرنامج التعليمي الأساسي لـ Excel Excel.

02 من 09

بدء وظيفة INDEX

دالة INDEX هي واحدة من القلائل في Excel التي تحتوي على نماذج متعددة. تحتوي الدالة على نموذج صفيف ونموذج مرجع .

يقوم نموذج الصفيف بإرجاع البيانات الفعلية من قاعدة بيانات أو جدول بيانات ، بينما يعطيك النموذج المرجعي مرجع الخلية أو موقع البيانات في الجدول.

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

يحتوي كل نموذج على قائمة مختلفة من الوسيطات التي يجب تحديدها قبل بدء الوظيفة.

خطوات البرنامج التعليمي

1. انقر فوق الخلية F3 لتجعلها الخلية النشطة . هذا هو المكان الذي سنقوم بإدخال الدالة المتداخلة.
2. انقر فوق علامة التبويب صيغ من قائمة الشريط .
3. اختر بحث ومرجع من الشريط لفتح القائمة المنسدلة الدالة.
4. انقر على INDEX في القائمة لإظهار مربع الحوار Select Arguments .
5. اختر الصفيف ، row_num ، خيار col_num في مربع الحوار.
6. انقر فوق موافق لفتح مربع حوار الدالة INDEX.

03 من 09

دخول INDEX الدالة Array Argument

الوسيطة الأولى المطلوبة هي حجة Array. تحدد هذه الوسيطة نطاق الخلايا المطلوب البحث عنها للبيانات المطلوبة.

بالنسبة لهذا البرنامج التعليمي ، ستكون هذه الحجة هي نموذج قاعدة بياناتنا .

خطوات البرنامج التعليمي

1. في مربع الحوار INDEX function ، انقر فوق سطر Array .
2. قم بتمييز الخلايا من D6 إلى F11 في ورقة العمل لإدخال النطاق في مربع الحوار.

04 من 09

بدء تشغيل الدالة MATCH المتداخلة

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

يجب كتابة الدالة المتداخلة كأحد وسيطات الدالة الأولى.

في هذا البرنامج التعليمي ، سيتم إدخال الدالة MATCH المتداخلة ووسائطها في السطر الثاني من مربع حوار الدالة INDEX - سطر Row_num .

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

إدخال وسيطة Lookup_value الخاصة بدالة MATCH

الخطوة الأولى في إدخال الدالة MATCH المتداخلة هي إدخال وسيطة Lookup_value .

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

عادةً ما يقبل Lookup_value معيار بحث واحد أو مصطلح واحد فقط. من أجل البحث عن معايير متعددة ، يجب علينا توسيع Lookup_value .

يتم ذلك عن طريق وصل أو ضم مرجعين للخلية أو أكثر معًا باستخدام رمز العطف " & ".

خطوات البرنامج التعليمي

1. في مربع الحوار دالة INDEX ، انقر فوق سطر Row_num .
2. اكتب تطابق اسم الوظيفة متبوعًا بقوس مستدير مفتوح " ( "
3. انقر فوق الخلية D3 لإدخال مرجع الخلية هذا في مربع الحوار.
4. اكتب علامة العطف " & " بعد مرجع الخلية D3 لإضافة مرجع خلية ثانية.
5. انقر فوق الخلية E3 لإدخال مرجع الخلية الثانية في مربع الحوار.
6. اكتب فاصلة "،" بعد إشارة الخلية E3 لإكمال إدخال وسيطة Lookup_value الخاصة بوظيفة MATCH.
7. اترك مربع حوار الدالة INDEX مفتوحًا للخطوة التالية في البرنامج التعليمي.

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

05 من 09

إضافة Lookup_array للدالة MATCH

تغطي هذه الخطوة إضافة وسيطة Lookup_array للدالة MATCH المتداخلة.

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

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

لإدخال صفائف متعددة ، نستخدم كلمة العطف " & " مرة أخرى لسَلسَط المصفوفات معًا.

خطوات البرنامج التعليمي

يجب إدخال هذه الخطوات بعد إدخال الفاصلة في الخطوة السابقة على سطر Row_num في مربع الحوار INDEX.

1. انقر فوق سطر Row_num بعد الفاصلة لوضع نقطة الإدراج في نهاية الإدخال الحالي.
2. قم بتمييز الخلايا من D6 إلى D11 في ورقة العمل لإدخال النطاق. هذه هي المصفوفة الأولى التي تبحث عنها.
3. اكتب علامة العطف " & " بعد أن تشير الخلية D6: D11 لأننا نريد أن تقوم الدالة بالبحث عن صفيفين.
4. قم بتمييز الخلايا من E6 إلى E11 في ورقة العمل لإدخال النطاق. هذا هو المصفوفة الثانية التي تعمل على البحث عنها.
5. اكتب فاصلة "،" بعد مرجع الخلية E3 لإكمال إدخال الدالة Lookup_array للدالة MATCH.
6. اترك مربع حوار الدالة INDEX مفتوحًا للخطوة التالية في البرنامج التعليمي.

06 من 09

إضافة نوع المطابقة وإكمال وظيفة MATCH

الوسيطة الثالثة والأخيرة للدالة 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. بعد الفاصلة على سطر Row_num ، اكتب صفر " 0 " لأننا نريد أن تقوم الدالة المتداخلة بإرجاع التطابقات التامة إلى المصطلحات التي ندخلها في الخلايا D3 و E3.
2. اكتب قوس مستدير إغلاق " ) " لإكمال وظيفة MATCH.
3. اترك مربع حوار الدالة INDEX مفتوحًا للخطوة التالية في البرنامج التعليمي.

07 من 09

العودة إلى الدالة INDEX

الآن بعد أن تم تنفيذ وظيفة MATCH ، سننتقل إلى السطر الثالث من مربع الحوار المفتوح ، ثم ندخل الوسيطة الأخيرة للدالة INDEX.

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

خطوات البرنامج التعليمي

1. انقر فوق سطر Column_num في مربع الحوار.
2. أدخل الرقم ثلاثة " 3 " (بدون علامتي الاقتباس) على هذا السطر لأننا نبحث عن بيانات في العمود الثالث من النطاق من D6 إلى F11.
3. لا تنقر فوق موافق أو أغلق مربع حوار الدالة INDEX. يجب أن تظل مفتوحة للخطوة التالية في البرنامج التعليمي - إنشاء صيغة الصفيف .

08 من 09

خلق صيغة صفيف

قبل إغلاق مربع الحوار ، نحتاج إلى تحويل الدالة المتداخلة إلى صيغة صفيف .

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

يتم إنشاء صيغة صفيف في Excel بالضغط على المفاتيح CTRL و SHIFT و ENTER على لوحة المفاتيح في نفس الوقت.

إن تأثير الضغط على هذه المفاتيح معًا هو تطويق الوظيفة باستخدام أقواس معقوفة: {} مشيرة إلى أنها الآن عبارة عن صيغة صفيف.

خطوات البرنامج التعليمي

1. مع استمرار فتح مربع الحوار المكتمل من الخطوة السابقة في هذا البرنامج التعليمي ، اضغط باستمرار على المفتاحين CTRL و SHIFT على لوحة المفاتيح ثم اضغط على المفتاح ENTER وحرره.
2. إذا تم ذلك بشكل صحيح ، سيتم إغلاق مربع الحوار وسيظهر خطأ # N / A في الخلية F3 - الخلية حيث دخلنا الدالة.
3. يظهر الخطأ # N / A في الخلية F3 لأن الخلايا D3 و E3 فارغة. D3 و E3 هما الخلايا التي أخبرنا الدالة فيها عن العثور على Lookup_values ​​في الخطوة 5 من البرنامج التعليمي. بمجرد إضافة البيانات إلى هاتين الخاليتين ، سيتم استبدال الخطأ بمعلومات من قاعدة البيانات .

09 من 09

إضافة معايير البحث

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

كما ذكرنا في الخطوة السابقة ، نتطلع إلى مطابقة مصطلحات الأدوات من العمود 1 و التيتانيوم من العمود 2.

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

خطوات البرنامج التعليمي

1. انقر فوق الخلية D3.
2. اكتب Widgets واضغط على مفتاح Enter في لوحة المفاتيح.
3. انقر فوق الخلية E3.
4. اكتب Titanium واضغط على مفتاح Enter في لوحة المفاتيح.
5. يجب أن يظهر اسم المورد Widgets Inc. في الخلية F3 - موقع الوظيفة نظرًا لأنها المورد الوحيد المدرج الذي يبيع أدوات Titanium.
6. عندما تضغط على الخلية F3 الوظيفة الكاملة
   {= INDEX (D6: F11، MATCH (D3 & E3، D6: D11 & E6: E11، 0)، 3)}
   يظهر في شريط الصيغة أعلى ورقة العمل .

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