البحث عن أقرب تطابق

للبحث عن أقرب تطابق لقيمة بحث في مجموعة بيانات رقمية في Excel، يمكنك استخدام دوال INDEX وMATCH وABS و MIN معًا.

كيفية العثور على أقرب تطابق في Excel؟

لمعرفة البائع الذي حقق مبيعات أقرب إلى الهدف البالغ 20,000 دولار كما هو موضح أعلاه، فإن صيغة تجمع بين دوال INDEX وMATCH وABS وMIN ستساعدك بهذه الطريقة: ستقوم دالة ABS بتحويل جميع قيم الفروق بين المبيعات التي حققها كل بائع والهدف الإيجابي، ثم ستجد MIN أصغر فرق، مما يعني التطابق الأقرب. الآن يمكننا استخدام دالة MATCH لتحديد موقع التطابق الأقرب واستخدام INDEX لاسترداد القيمة في الموقع المقابل.

صيغة عامة

=INDEX(نطاق_الإرجاع,MATCH(MIN(ABS(مصفوفة_البحث-قيمة_البحث)),ABS(مصفوفة_البحث-قيمة_البحث),0))

√ ملاحظة: هذه صيغة صفيف تتطلب منك إدخالها باستخدام Ctrl + Shift + Enter.

لمعرفة البائع الذي حقق مبيعات أقرب إلى الهدف البالغ 20,000 دولار، يرجى نسخ أو إدخال الصيغة أدناه في الخلية F5، والضغط على Ctrl + Shift + Enter للحصول على النتيجة:

=INDEX(B5:B10,MATCH(MIN(ABS(C5:C10-20000)),ABS(C5:C10-20000),0))

أو، استخدم مرجع خلية لجعل الصيغة ديناميكية:

=INDEX(B5:B10,MATCH(MIN(ABS(C5:C10-F4)),ABS(C5:C10-F4),0))

شرح الصيغة

=INDEX(B5:B10,MATCH(MIN(ABS(C5:C10-F4)),ABS(C5:C10-F4),0))

• ABS(C5:C10-F4): الجزء C5:C10-F4 سيحصل على جميع قيم الفروق بين كل مبيعات في النطاق C5:C10 وهدف المبيعات البالغ 20,000 دولار في الخلية F4 في مصفوفة مثل هذه: {-4322;2451;6931;-1113;6591;-4782}. ستقوم دالة ABS بتحويل جميع الأرقام السالبة إلى موجبة على النحو التالي: {4322;2451;6931;1113;6591;4782}.
• MIN(ABS(C5:C10-F4)) = MIN({4322;2451;6931;1113;6591;4782}): ستقوم دالة MIN بالعثور على أصغر عدد من المصفوفة {4322;2451;6931;1113;6591;4782}، مما يعني أصغر فرق، أو ما نسميه أقرب تطابق. لذلك ستعيد الدالة 1113.
• MATCH(MATCH(MIN(ABS(C5:C10-F4)ABS(C5:C10-F4)),,ABS(C5:C10-F4)ABS(C5:C10-F4),0),0) = MATCH(MATCH(1113,,{4322;2451;6931;1113;6591;4782},0),0): نوع_التطابق 0 يجبر دالة MATCH على العثور على موقع الرقم الدقيق 1113 في المصفوفة {4322;2451;6931;1113;6591;4782}. ستعيد الدالة 4 لأن الرقم موجود في المركز 4.
• INDEX(B5:B10B5:B10,,,MATCH(MIN(ABS(C5:C10-F4)ABS(C5:C10-F4))),,,ABS(C5:C10-F4)ABS(C5:C10-F4),0))) = INDEX(B5:B10B5:B10,,,4): ستقوم دالة INDEX بإرجاع القيمة الرابعة في نطاق الأسماء B5:B10، وهي Bale.

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

الصيغ ذات الصلة