استرجاع أول قيمة مطابقة في الخلية مقابل قائمة

افترض أن لديك قائمة بالكلمات المفتاحية، وللاسترجاع أول كلمة مفتاحية تظهر في خلية محددة — بينما تحتوي الخلية على عدة قيم أخرى — ستحتاج إلى استخدام صيغة تجمع بين دالتيINDEX وMATCH، بمساعدة دالتيAGGREGATE وSEARCH.

كيف يمكن استرجاع أول قيمة مطابقة في الخلية من قائمة؟

للحصول علىأول كلمة مفتاحية مطابقة في خلية مقابل قائمة الكلمات المفتاحيةكما هو موضح في الجدول أعلاه، نحتاج إلى إجراء مطابقة احتواء وليس مطابقة دقيقة. ولتحقيق ذلك، يمكنك استخدام دالة SEARCH لإدخال مواضع ظهور الكلمات المفتاحية في الخلية كأرقام رقمية إلى دالة AGGREGATE. بعد ذلك، تقوم دالة AGGREGATE بإرجاع أصغر رقم بتعيينfunction_num إلى 15 ومعيار ref2 إلى 1. ثم استخدم دالة MATCH لتحديد موقع أول أصغر قيمة، وأدخل رقم الموضع الناتج إلى دالة INDEX لاسترجاع القيمة الموجودة في ذلك الموضع.

التركيب العام

=INDEX()keyword_rng,MATCH(AGGREGATE(15,6,SEARCH()))keyword_rng,lookup_cell),1),SEARCH(keyword_rng,lookup_cell),0))

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

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

=INDEX()$E$5:$E$7,MATCH(AGGREGATE(15,6,SEARCH()))$E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5),0))

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

شرح الصيغة

=INDEX($E$5:$E$7,)MATCH()AGGREGATE(15,6,)SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5),0))

• SEARCH($E$5:$E$7,B5):تُرجع دالة SEARCH موضع كل كلمة مفتاحية من النطاق$E$5:$E$7 كأرقام عند العثور عليها، أو خطأ#VALUE! إذا لم يتم العثور عليها. وتكون النتيجة على هيئة مصفوفة كالتالي: {15;11;#VALUE!}.
• AGGREGATE(15,6,)SEARCH($E$5:$E$7,B5),1)=AGGREGATE(15,6,){15;11;#VALUE!},1):تقوم دالةAGGREGATE عند استخدامfunction_num المساوي لـ15 وoption المساوي لـ6 بإرجاع أصغر قيمة في المصفوفة وفقًا للمعيارref2 = 1، مع تجاهل قيم الأخطاء. لذلك، تُرجع هذه الصيغة القيمة11.
• MATCH()AGGREGATE(15,6,)SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5)=MATCH()11,{15;11;#VALUE!},0):إنmatch_type الذي يساوي 0يُلزم دالة MATCH بإجراء مطابقة دقيقة وإرجاع موضع القيمة11 في المصفوفة{15;11;#VALUE!}. وبالتالي، سترجع الدالة2.
• INDEX($E$5:$E$7,)MATCH()AGGREGATE(15,6,)SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5)) = INDEX($E$5:$E$7,2):ثم تقوم دالةINDEX بإرجاع القيمة الثانية في النطاق$E$5:$E$7، وهيbbb.

ملاحظة

• إذا لم توجد كلمات مفتاحية في الخلية، فسيتم إرجاع خطأ#NUM!.
• الصيغة لا تُراعي حالة الأحرف. ولإجراء مطابقة تأخذ حالة الأحرف بعين الاعتبار، ما عليك سوى استبدال دالةSEARCH بدالةFIND.

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

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