العثور على أطول سلسلة نصية وفق معايير محددة

في هذا البرنامج التعليمي، سنشرح كيفية البحث عن أطول سلسلة نصية في عمود أو صف وفق معايير محددة في Excel بمساعدةدالة INDEX،دالة MATCH،دالة LENودالة MAX.

كيف تجد أطول سلسلة نصية وفق المعايير المحددة في Excel؟

للعثور بسرعة علىالشخص القادم من أمريكا والذي يحمل أطول اسمالمذكور في نطاق الأسماء أعلاه، استخدم صيغة تجمع بين دوال INDEX وMATCH وLEN وMAX بهذه الطريقة: تُحدّد دالة LEN طول كل اسم، ثم تستخرج دالة MAX أطول طول ضمن النطاق، بعد ذلك تحدد دالة MATCH موقع أول اسم يطابق هذا الطول الأقصى، وأخيرًا تسترجع دالة INDEX القيمة الموجودة في ذلك الموقع.

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

=INDEX()return_range,MATCH(MAX(LEN()))return_range)*(criteria_range=criteria_value)),LEN(return_range)*(criteria_range=criteria_value),0))

√ ملاحظة: هذه صيغة مصفوفة تتطلب منك إدخالها باستخدامCtrl+Shift+Enter.

للعثور علىالشخص القادم من أمريكا والذي يحمل أطول اسم، يُرجى نسخ الصيغ التالية أو إدخالها في الخلية F6، ثم الضغط علىCtrl+Shift+Enterللحصول على النتيجة:

=INDEX()B5:B11،MATCH(MAX(LEN()))B5:B11)*(C5:C11=«America»))،LEN(B5:B11)*(C5:C11=«America»),0))

أو استخدم مرجع الخلية لجعل الصيغة ديناميكية:

=INDEX()B5:B11،MATCH(MAX(LEN()))B5:B11)*(C5:C11=F5))،LEN(B5:B11)*(C5:C11=F5),0))

شرح الصيغة

=INDEX()B5:B11,MATCH()MAX()LEN(B5:B11)*(C5:C11=F5)),LEN(B5:B11)*(C5:C11=F5),0))

• LEN(B5:B11)*(C5:C11=F5):تُرجع دالةLEN مصفوفةً تحتوي على أطوال كل اسم في النطاقB5:B11، وهي كالتالي: {5؛5؛6؛3؛4؛8؛5}. أما الجزء(C5:C11=F5)، فيتحقّق من كل قيمة في النطاقC5:C11 لمعرفة ما إذا كانت تساوي القيمة الموجودة في الخليةF5، ويُنتج مصفوفةً بالشكل التالي: {FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE}، والتي تتحوّل تلقائيًا إلى {0؛0؛1؛0؛1؛0؛1} عند الضرب. وبالتالي، تكون نتيجة الضرب كما يلي:{0;0;6;0;4;0;5}.
• MAX()LEN(B5:B11)*(C5:C11=F5))=MAX(){0;0;6;0;4;0;5}):بعد ذلك، تقوم دالة MAX بإرجاع القيمة الأكبر من المصفوفة{0;0;6;0;4;0;5}، وهي6.
• MATCH()MAX()LEN(B5:B11)*(C5:C11=F5)),LEN(B5:B11)*(C5:C11=F5)إن معاملنوع_المطابقة 0يجبر دالة MATCH على إيجاد موقع أول رقم مطابق تمامًا6 في المصفوفة{0;0;6;0;4;0;5}. لذا، فهي تُرجع3 لأن الرقم موجود في الموقع3.
• INDEX()B5:B11,MATCH()MAX()LEN(B5:B11)*(C5:C11=F5)),LEN(B5:B11)*(C5:C11=F5)تقوم دالةINDEX بإرجاع القيمة الثالثة في نطاق الأسماءB5:B11، وهيOliver.

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

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