كيف تجد القيمة الأقرب أو الأدنى في Excel؟

افترض أن لديك قائمة بأرقام في العمود B وتحتاج إلى تحديد القيمة الأقرب إلى رقم معين—مثلًا، 18. تتيح لك صيغة المصفوفة في Excel تحديد هذه القيمة بكفاءة، دون الحاجة إلى تصفح القائمة يدويًّا.

لبدء العمل، حدد خلية فارغة ثم أدخل الصيغة التالية. بعد كتابتها، تأكد من الضغط علىCtrl + Shift + Enter بدلاً من مفتاح Enter فقط—فهذا سيضمن تشغيل الصيغة كصيغة مصفوفة، وهو أمرٌ ضروري لتعمل بشكل صحيح:

=INDEX(B3:B22,MATCH(MIN(ABS(B3:B22-E2)),ABS(B3:B22-E2),0))

هذا الأسلوب أنسب عندما تحتاج إلى استرجاع القيمة الأقرب الوحيدة من نطاق متصل. وهو يعمل بكفاءة في معظم الحالات التي تكون فيها الدقة الرقمية والمطابقة الدقيقة أمرًا بالغ الأهمية. ومع ذلك، ضع في اعتبارك أن صيغ المصفوفة قد تستهلك موارد كبيرة عند التعامل مع مجموعات بيانات ضخمة جدًّا. إذا واجهت مشكلات في الأداء أو ظهرت رسائل خطأ مثل#VALUE!، فتحقق مرة أخرى من مراجع الخلايا وتأكد من الضغط علىCtrl + Shift + Enter بشكل صحيح.

قد لا تحتاج في بعض الأحيان إلى القيمة الأقرب وحدها، بل تريد تحديد جميع الأرقام التي تقع ضمن نطاق معين من قيمتك المستهدفة—وهو ما يُعرف غالبًا باسم «نطاق الانحراف». ويقدّم لك Kutools لـ Excel حلاً عمليًّا عبر ميزةتحديد خلايا خاصة، التي تتيح لك تحديد جميع القيم الواقعة ضمن فرق معيّن من هدفك بسرعةٍ فائقة.

على سبيل المثال، لنفترض أن قيمتك المستهدفة هي 18، وقد حددت انحرافًا مقداره 2. هذا يعني أنك ترغب في تحديد جميع القيم ضمن نطاق يمتد من 16 (18 – 2) إلى 20 (18 + 2). فيما يلي خطوات تحقيق ذلك:

1. حدد النطاق الذي تريد البحث فيه (مثلًا: B3:B22)، ثم انتقل إلىKutools > تحديد > تحديد خلايا محددة.

2. في مربع حوارتحديد خلايا محددة:

• ضمننوع التحديد، اخترخلية.
• فيتحديد النوع:
  - اضبط القائمة المنسدلة الأولى علىقائمة منسدلة، وأدخل16 في المربع.
  - اضبط القائمة المنسدلة الثانية علىأقل من أو يساوي، وأدخل20.

3. انقرموافقلتنفيذه. سيُبلغك Kutools بعدد الخلايا التي استوفت معاييرك، وسيُبرز جميع القيم الأقرب ضمن الانحراف المحدد، كما هو موضح أدناه:

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

للمستخدمين الباحثين عن الأتمتة أو المحتاجين إلى تنفيذ بحث مخصص عن القيمة الأقرب—سواء كانت رقمية أو نصية—عبر عدة أوراق عمل أو مجموعات بيانات ضخمة، يُعد ماكرو VBA حلاً فعّالًا ومرنًا. فمن خلال برمجة Excel للتحقق منهجيًا من الفروق بين هدفك وجميع القيم المرشحة، يمكنك استرجاع ليس فقط العدد الأقرب، بل أيضًا السلسلة النصية الأقرب استنادًا إلى المسافة النصية.

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

1. انقرالمطور > Visual Basic. في نافذةMicrosoft Visual Basic for Applications، انقرإدراج > وحدة نمطية، ثم انسخ الشفرة التالية إلى الوحدة النمطية:

Function FindClosest(rng As Range, target As Double) As Double
    Dim cell As Range
    Dim minDiff As Double
    Dim closestValue As Double
    
    minDiff = 1E+99
    For Each cell In rng
        If Abs(cell.Value - target) < minDiff Then
            minDiff = Abs(cell.Value - target)
            closestValue = cell.Value
        End If
    Next cell  
    FindClosest = closestValue
End Function 

2. بعد ذلك، انتقل إلى ورقة العمل الخاصة بك، وأدخل الصيغة التالية في خلية فارغة:=FindClosest(B3:B22, E2)، ثم اضغط على مفتاح Enter للحصول على القيمة الأقرب.

عند مراجعة البيانات أو عرضها، غالبًا ما يكون من المفيد تحديد القيم الأقرب إلى هدفك بصريًّا دون الحاجة إلى تصفية البيانات أو إعادة ترتيبها. تتيح لك ميزةاستخدم تنسيق الشروطالمضمنة في Excel تمييز الخلايا الأقرب إلى قيمتك المستهدفة، مما يسهل عليك رصدها لمحةً بصريةً سريعة. وعلى الرغم من أن هذه الطريقة لا تُظهر القيمة الدقيقة نفسها، إلا أنها فعّالة جدًّا في تحليل البيانات والتحقق البصري الفوري.

الميزة الأساسية لهذا الأسلوب تكمن في قدرته على التمييز الديناميكي غير المدمّر، الذي يتكيف بسلاسة مع تغيّر البيانات أو القيم المستهدفة. وهو مثالي تمامًا لاستخدامه في لوحات المعلومات والعروض التقديمية وسيناريوهات المراجعة التي تعتمد على وضوح الرؤية كأولوية قصوى. ومع ذلك، قد تنخفض دقّته في الحالات التي تشترك فيها أكثر من قيمة واحدة في نفس درجة «القرب»، كما أنه لا يُخرِج القيمة نفسها لاستخدامها في المعالجة اللاحقة.

1. حدد نطاق الخلايا الذي تريد تحليله (على سبيل المثال، B3:B22).

2. في علامة التبويبالرئيسية، انقراستخدم تنسيق الشروط > قاعدة جديدة.

3. اختراستخدام صيغة لتحديد الخلايا التي سيتم تنسيقهافي مربع الحوار. بعد ذلك، أدخل الصيغة التالية في مربع الصيغة:

=ABS(B3-$E$2)=MIN(ABS($B$3:$B$22-$E$2))

4. انقرتنسيق، واختر لون التمييز، ثم انقرموافق، وانقرموافقمرة أخرى لتطبيق القاعدة.

سيؤدي ذلك إلى تمييز جميع الخلايا ضمن نطاق التحديد التي تكون قيمها الأقرب بشكل متساوٍ إلى القيمة المستهدفة في الخلية E2.

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