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

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

ملاحظة

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

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

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