كيفية العثور على أقرب أو أقرب قيمة في 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. انقر فوق 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 تسليط الضوء على الخلايا الأقرب إلى القيمة المستهدفة الخاصة بك، مما يجعل من السهل رؤيتها بنظرة سريعة. على الرغم من أن هذه الطريقة لا تقوم بإرجاع القيمة الدقيقة نفسها، إلا أنها فعالة لتحليل البيانات السريع والتوكيد البصري.

الميزة الرئيسية لهذه الطريقة هي تسليط الضوء الديناميكي وغير المدمر الذي يمكن أن يتكيف مع تغيير البيانات أو القيم المستهدفة. إنه مناسب بشكل خاص لل_dashboards، العروض التقديمية، وسيناريوهات المراجعة حيث تكون الرؤية هي المفتاح. قد تكون أقل دقة إذا كانت أكثر من قيمة تشترك في نفس 'القرب'، ولا تقوم بإخراج القيمة نفسها لأغراض معالجة إضافية.

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

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

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

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

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

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

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