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

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

كيف تجد أقرب تطابق في Excel؟

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

الصيغة العامة

=INDEX()return_range,MATCH(MIN(ABS()))lookup_array-lookup_value)),ABS(lookup_array-lookup_value),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()MIN()ABS(C5:C10-F4)),ABS(C5:C10-F4)إننوع_المطابقة 0يجبر دالة MATCH على إيجاد موقع الرقم الدقيق1113 في المصفوفة{4322;2451;6931;1113;6591;4782}. وستُرجع الدالة4 لأن الرقم موجود في الموقع4 بالترتيب.
• INDEX()B5:B10,MATCH()MIN()ABS(C5:C10-F4)),ABS(C5:C10-F4)تُرجع دالةINDEX القيمة الرابعة في نطاق الأسماءB5:B10، وهيBale.

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

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