اكسل

استخراج تطابقات متعددة في أعمدة منفصلة

Extract Multiple Matches Into Separate Columns

صيغة Excel: استخراج تطابقات متعددة في أعمدة منفصلةملخص

لاستخراج تطابقات متعددة لفصل الخلايا ، في أعمدة منفصلة ، يمكنك استخدام صيغة صفيف تستند إلى INDEX و SMALL. في المثال الموضح ، الصيغة في F5 هي:



 
{= IFERROR ( INDEX (names, SMALL ( IF (groups=$E5, ROW (names)- MIN ( ROW (names))+1), COLUMNS ($E:E5))),'')}

هذه صيغة صفيف ويجب إدخالها باستخدام Control + Shift + Enter.

ماذا يفعل vlookup في Excel

بعد إدخال الصيغة في الخلية الأولى ، اسحبها لأسفل وعبر لملء الخلايا الأخرى.





تفسير

ملاحظة: تستخدم هذه الصيغة اثنين النطاقات المسماة : تشير 'الأسماء' إلى C5: C11 ، وتشير 'المجموعات' إلى B5: B11. يتم تعريف هذه الأسماء في لقطة الشاشة أعلاه أيضًا.

جوهر هذه الصيغة هو: نحن نستخدم الدالة SMALL لإنشاء رقم صف مطابق لـ 'تطابق رقم'. بمجرد أن نحصل على رقم الصف ، نقوم بتمريره ببساطة إلى وظيفة دالة INDEX ، والتي تُرجع القيمة في هذا الصف.



الحيلة هي أن SMALL يعمل مع مصفوفة تم إنشاؤها ديناميكيًا بواسطة IF في هذا البت:

 
 IF (groups=$E5, ROW (names)- MIN ( ROW (names))+1)

يختبر هذا المقتطف النطاق المحدد 'المجموعات' للقيمة الموجودة في E5. إذا تم العثور عليه ، فإنه يُرجع رقم صف من مصفوفة من أرقام الصفوف النسبية التي تم إنشاؤها باستخدام:

 
 ROW (names)- MIN ( ROW (names))+1

النتيجة النهائية هي مصفوفة تحتوي على أرقام حيث يوجد تطابق ، و FALSE حيث لا:

أين تجد وظيفة vlookup قيم البحث الخاصة بها؟

{1FALSEFALSEFALSEFALSE6FALSE}

هذه المجموعة تذهب إلى SMALL. تأتي قيمة k لـ SMALL (nth) من توسيع النطاق :

 
 COLUMNS ($E:E5)

عند نسخه عبر الجدول الناتج ، يتسع النطاق ، مما يؤدي إلى زيادة k (n). ترجع الدالة SMALL كل رقم صف مطابق ، والذي يتم توفيره للدالة INDEX كـ row_num ، مع النطاق المسمى 'أسماء' كمصفوفة.

معالجة الأخطاء

عندما تُرجع الدالة COLUMNS قيمة لـ k غير موجودة ، فإن SMALL يرمي الخطأ #NUM. يحدث هذا بعد حدوث كل المباريات. لمنع الخطأ ، نقوم بلف الصيغة في الدالة IFERROR للقبض على الأخطاء وإرجاع ملف سلسلة فارغة (').

المؤلف ديف برونز


^