البحث عن أقرب قيمة مطابقة باستخدام معايير متعددة

في بعض الحالات، قد تحتاج إلى البحث عن أقرب قيمة مطابقة أو قيمة تقريبية بناءً على أكثر من معيار. وباستخدام مجموعة من دالاتINDEX وMATCH وIF، يمكنك إنجاز ذلك بسرعة في Excel.

كيف تبحث عن أقرب قيمة مطابقة باستخدام أكثر من معيار؟

كما يظهر في لقطة الشاشة أدناه، عليك العثور على الشخص المناسب للوظيفة بناءً على معيارين: “التخصص هوالحاسوب” و“خبرة العمل هي15 سنة”.

ملاحظة: لكي يعمل هذا بشكل صحيح، إذا احتوى النطاق على تخصصات مكررة، يجب أن تكون خبرات العمل الخاصة بهذه التخصصات المكررة مرتبة ترتيبًا تصاعديًّا.

1. حدد خلية فارغة لإظهار النتيجة، ثم انسخ الصيغة أدناه إليها واضغط على مفاتيحCtrl+Shift+Enter للحصول على النتيجة.

=INDEX(D3:D8,MATCH(G5,IF(B3:B8=G4,C3:C8),1))

ملاحظات:في هذه الصيغة:

• D3:D8هو نطاق العمود الذي يحتوي على النتيجة التي تبحث عنها؛
• G5يحتوي على المعيار الثاني (عدد سنوات الخبرة 15) الذي ستعتمد عليه في البحث عن القيمة؛
• G4يحتوي على المعيار الأول (الحاسوب) الذي ستعتمد عليه في البحث عن القيمة؛
• B3:B8هو نطاق الخلايا المطابق للمعيار الأول؛
• C3:C8هو نطاق الخلايا المطابق للمعيار الثاني؛
• الرقم1هو بحث تقريبي، ما يعني أنه إذا لم يتم العثور على القيمة الدقيقة، فسيبحث عن أكبر قيمة أقل من قيمة البحث؛
• يجب إدخال هذه الصيغة كصيغة صفيفية باستخدام مفاتيحCtrl+Shift+Enter.

كيف تعمل هذه الصيغة

يمكن تقسيم هذه الصيغة إلى عدة مكونات:

• IF(B3:B8=G4,C3:C8): تقوم دالة IF هنا بإرجاع النتيجة كـ{9;13;FALSE;FALSE;FALSE;FALSE}، وذلك بعد اختبار القيم في B3:B8 لمعرفة ما إذا كانت تطابق القيمة الموجودة في G4. فإذا وُجد تطابق، تُرجع القيمة المقابلة من C3:C8، وإلا تُرجع FALSE. وقد وجدت في هذا المثال تطابقين وأربع حالات عدم تطابق.
• صيغة صفيفية=MATCH(G5,{9;13;FALSE;FALSE;FALSE;FALSE},1): تقوم دالة MATCH بالبحث عن موضع الرقم 15 (القيمة الموجودة في الخلية G5) ضمن الصفيف {9؛13؛FALSE؛FALSE؛FALSE؛FALSE}. وبما أن الرقم 15 غير موجود، فإن الدالة تُطابقه مع أقرب قيمة أصغر منه، وهي 13. وبالتالي، تكون النتيجة هنا 2.
• و=INDEX(D3:D8,2): تُرجع دالة INDEX قيمة الخلية الثانية ضمن النطاق D3:D8، وبالتالي تكون النتيجة النهائية هي آمي.

الدوال ذات الصلة

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