يمكن أن تكون وظائف Excel قوية جدًا إذا تعلمت الجمع بين الصيغ المختلفة.ما بدا مستحيلًا يمكن أن يصبح فجأة مثل لعب الأطفال.
أحد الأمثلة على ذلك هو العثور على أقرب قيمة في مجموعة بيانات Excel.
هناك العديد من وظائف البحث المفيدة في Excel (مثل VLOOKUP و INDEX MATCH) التي يمكنها العثور على أقرب值(كما هو موضح في المثال أدناه).
لكن أفضل جزء هو أنه يمكنك دمج وظائف البحث هذه مع وظائف Excel الأخرى للقيام بأكثر من ذلك بكثير (بما في ذلك العثور على أقرب تطابق لقيمة بحث في قائمة غير مرتبة).
في هذا البرنامج التعليمي ، سأوضح لك كيفية استخدام صيغة بحث للعثور على أقرب تطابق لقيمة بحث في Excel.
هناك العديد من السيناريوهات المختلفة حيث تحتاج إلى العثور على أقرب تطابق (أو أقرب قيمة مطابقة).
فيما يلي مثال سأقوم بتغطيته في هذه المقالة:
- ابحث عن معدلات العمولة على أساس المبيعات
- ابحث عن أفضل مرشح (بناءً على أقرب تجربة)
- ابحث عن تاريخ الحدث التالي
لنبدأ!
المحتويات
ابحث عن معدلات العمولة (ابحث عن أقرب قيمة مبيعات)
لنفترض أن لديك مجموعة بيانات مثل المجموعة أدناه حيث تريد العثور على معدلات العمولة لجميع مندوبي المبيعات.
يتم توزيع العمولات على أساس قيمة البيع.يتم حساب هذا باستخدام الجدول الموجود على اليمين.
على سبيل المثال ، إذا كان إجمالي مبيعات مندوب المبيعات 5000 ، فإن العمولة تساوي 0٪ ، وإذا كان إجمالي مبيعاته هو 15000 ، فإن العمولة تكون 5٪.
للحصول على معدل عمولة ، تحتاج إلى العثور على أقرب نطاق مبيعات أقل بقليل من قيمة المبيعات.على سبيل المثال ، بالنسبة لـ 15000 عملية بيع ، تكون العمولة 10,000 (أو 5٪) ، وبالنسبة لـ 25000 عملية بيع يكون معدل العمولة 20,000 (أو 7٪).
للعثور على أقرب قيمة بيع والحصول على معدل عمولة ، يمكنك استخدام المطابقة التقريبية في VLOOKUP.
يمكن للصيغة التالية القيام بذلك:
= VLOOKUP (B2، $ E $ 2: $ F 6,2,1،XNUMX،XNUMX)
لاحظ أنه في هذه الصيغة ، المعلمة الأخيرة هي 1 ، والتي تخبر الصيغة باستخدام البحث التقريبي.هذا يعني أن الصيغة سوف تمر عبر قيمة المبيعات في العمود E وتجد القيمة الموجودة أسفل قيمة البحث مباشرةً.
ستعطي صيغة VLOOKUP بعد ذلك معدل العمولة لتلك القيمة.
注意: للقيام بذلك ، تحتاج إلى فرز البيانات بترتيب تصاعدي.
ابحث عن أفضل مرشح (بناءً على أقرب تجربة)
في المثال أعلاه ، يجب فرز البيانات بترتيب تصاعدي.ولكن قد تكون هناك حالات لا يتم فيها فرز البيانات.
لنأخذ مثالاً ونرى كيف يمكننا استخدام تركيبات الصيغ للعثور على أقرب تطابق في Excel.
يوجد أدناه مثال لمجموعة بيانات حيث أحتاج إلى العثور على أسماء الموظفين الذين تكون خبرتهم العملية أقرب إلى القيمة المطلوبة.القيمة المتوقعة في هذه الحالة هي 2.5 سنة.
لاحظ أنه لا يتم فرز البيانات.بالإضافة إلى ذلك ، قد تكون التجارب الأقرب أقل أو أكثر من تجربة معينة.على سبيل المثال ، سنتان و 2 سنوات متقاربتان بشكل متساوٍ (فرق 3 سنة).
هذه هي الصيغة التي تعطينا النتيجة:
=INDEX($A$2:$A$15,MATCH(MIN(ABS(D2-B2:B15)),ABS(D2-$B$2:$B$15),0))
الحيلة في هذه الصيغة هي تغيير قيم صفيف البحث والبحث للعثور على أصغر فرق تجريبي بين القيم المرغوبة والقيم الفعلية.
دعنا نفهم أولاً كيف ستفعل ذلك يدويًا (وبعد ذلك سأشرح كيف تعمل هذه الصيغة).
عند القيام بذلك يدويًا ، تمر عبر كل خلية في العمود B وتجد الفرق بين التجربة التي تريدها والتجربة التي يتمتع بها المرء.بمجرد أن تحصل على جميع الاختلافات ، تجد أصغرها وتحصل على اسم ذلك الشخص.
هذا بالضبط ما نفعله بهذه الصيغة.
دعني أشرح.
قيمة البحث في صيغة MATCH هي MIN (ABS (D2-B2: B15)).
يمنحك هذا القسم الحد الأدنى من الاختلاف بين التجربة المعينة (أي 2.5 سنة) وجميع التجارب الأخرى.في هذه الحالة يتم إرجاع 0.3
لاحظ أنني أستخدم ABS للتأكد من أنني أبحث عن الأقرب (ربما أكثر أو أقل من التجربة المحددة).
الآن ، تصبح هذه القيمة الدنيا هي قيمة البحث الخاصة بنا.
صفيف البحث في دالة MATCH هو ABS (D2- $ B $ 2: $ B $ 15).
هذا يعطينا مجموعة من الأرقام التي يتم طرح 2.5 (الخبرة المطلوبة) منها.
所以現在我們有一個查找值 (0.3) 和一個查找數組 ({6.8;0.8;19.5;21.8;14.5;11.2;0.3;9.2;2;9.8;14.8;0.4;23.8;2.9})
تعثر الدالة MATCH على موضع 0.3 في هذه المصفوفة ، وهو موضع اسم الشخص صاحب الخبرة الأقرب.
تستخدم الدالة INDEX بعد ذلك رقم الوظيفة لإرجاع اسم الشخص.
ملاحظة: إذا كان هناك عدة مرشحين لديهم نفس الحد الأدنى من الخبرة ، فستعطي الصيغة أعلاه اسم الموظف المطابق الأول.
ابحث عن تاريخ الحدث التالي
فيما يلي مثال آخر حيث يمكنك استخدام صيغة بحث للعثور على التاريخ التالي لحدث بناءً على التاريخ الحالي.
يوجد أدناه مجموعة البيانات حيث لدي أسماء الأحداث وتواريخ الأحداث.
ما أريده هو اسم الحدث القادم وتاريخ الحدث لهذا الحدث القادم.
هذه هي الصيغة التي تعطي اسم الحدث القادم:
=INDEX($A$2:$A$11,MATCH(E1,$B$2:$B$11,1)+1)
ستوضح الصيغة التالية تواريخ الأحداث القادمة:
=INDEX($B$2:$B$11,MATCH(E1,$B$2:$B$11,1)+1)
اسمحوا لي أن أشرح كيف تعمل هذه الصيغة.
للحصول على تاريخ الحدث ، تبحث الدالة MATCH في العمود B عن التاريخ الحالي.في هذه الحالة ، لا نبحث عن تطابق تام ، ولكن عن تطابق تقريبي.لذلك ، فإن المعلمة الأخيرة لوظيفة MATCH هي 1 (تجد أكبر قيمة أقل من أو تساوي قيمة البحث).
لذلك ستعيد وظيفة MATCH موضع الخلية التي يكون تاريخها أقل من التاريخ الحالي أو مساويًا له.لذلك في هذه الحالة سيكون الحدث التالي في الخلية التالية (حيث يتم فرز القائمة بترتيب تصاعدي).
لذلك ، للحصول على تاريخ الحدث القادم ، ما عليك سوى إضافة واحد إلى موضع الخلية الذي يتم إرجاعه بواسطة وظيفة MATCH وسوف يمنحك موضع الخلية في تاريخ الحدث التالي.
ثم يتم إعطاء هذه القيمة بواسطة دالة INDEX.
للحصول على اسم الحدث ، استخدم نفس الصيغة وقم بتغيير النطاق في دالة INDEX من العمود B إلى العمود A.
جاء هذا المثال في ذهني عندما قدم أحد الأصدقاء طلبًا.يسرد أعياد ميلاد جميع أصدقائه / أقاربه في عمود ويريد أن يعرف أن عيد الميلاد القادم قادم (واسم الشخص).
توضح الأمثلة الثلاثة أعلاه كيفية استخدام صيغة البحث للعثور على أقرب قيمة في Excel.
