كيفية العثور على أقرب أو أقرب قيمة في 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 حلاً عملياً من خلال ميزته Select Special Cells، مما يتيح تحديد سريع لجميع القيم ضمن اختلاف محدد من هدفك.

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

1. حدد النطاق الذي ترغب في البحث فيه (على سبيل المثال، B3:B22)، ثم انتقل إلى Kutools > Select > Select Specific Cells.

2. في نافذة Select Specific Cells:

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

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

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

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

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

1. انقر فوق Developer  > Visual Basic. في نافذة Microsoft Visual Basic for Applications، انقر فوق Insert > Module، وقم بنسخ الكود التالي إلى الوحدة:

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. على علامة التبويب Home، انقر فوق Conditional Formatting > New Rule.

3. اختر Use a formula to determine which cells to format في مربع الحوار. ثم، في مربع الصيغة، أدخل الصيغة التالية:

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

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

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

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