كيفية البحث باستخدام VLOOKUP وربط القيم المقابلة المتعددة في Excel؟

عند استخدام VLOOKUP في Excel، فإنه عادةً ما يعيد أول قيمة مطابقة من مجموعة البيانات. ومع ذلك، هناك سيناريوهات قد تحتاج فيها إلى إرجاع وربط القيم المقابلة المتعددة (مثل جميع الأسماء المرتبطة بفئة معينة). نظرًا لأن VLOOKUP بمفرده لا يمكن أن يحقق هذا، سنستعرض تقنيات متقدمة لتحقيق النتيجة المرجوة.

البحث باستخدام VLOOKUP وربط القيم المقابلة المتعددة في Excel

• استخدام دوال TEXTJOIN و FILTER (Excel 365/2021)
• استخدام Kutools لـ Excel
• استخدام الدالة المعرفة من قبل المستخدم

البحث باستخدام VLOOKUP وربط القيم المقابلة المتعددة باستخدام دوال TEXTJOIN و FILTER

إذا كنت تستخدم Excel 365 أو Excel 2021، فإن الجمع بين دوال TEXTJOIN و FILTER يوفر طريقة قوية وفعالة لربط القيم المقابلة المتعددة.

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

=TEXTJOIN(", ", TRUE, FILTER($B$2:$B$16, $A$2:$A$16=D2, ""))

البحث باستخدام VLOOKUP وربط القيم المقابلة المتعددة باستخدام Kutools لـ Excel

هل تعبت من قيود VLOOKUP في Excel عند التعامل مع القيم المقابلة المتعددة؟ ولكن باستخدام ميزة البحث واحد إلى العديد في Kutools لـ Excel، يمكنك بسهولة البحث عن القيم وربط القيم المقابلة المتعددة بنقرات قليلة فقط!

بعد تثبيت Kutools لـ Excel، يرجى القيام بما يلي:

انقر فوق "Kutools" > "Super LOOKUP" > "One-to-many Lookup(returns multiple results)" لفتح مربع الحوار. في مربع الحوار، يرجى تحديد العمليات كما يلي:

1. حدد "Output Range" و "Lookup values" في مربعات النص بشكل منفصل؛
2. حدد نطاق الجدول الذي تريد استخدامه؛
3. حدد العمود الرئيسي وعمود الإرجاع من القوائم المنسدلة "Key Column" و "Return Column" على حدة؛
4. أخيرًا، انقر فوق زر "موافق".
   

النتيجة: الآن، تم ربط جميع القيم المطابقة في خلية واحدة. انظر لقطة الشاشة:

البحث باستخدام VLOOKUP وربط القيم المقابلة المتعددة باستخدام الدالة المعرفة من قبل المستخدم

إذا كنت مرتاحًا لاستخدام VBA (Visual Basic for Applications)، يمكنك إنشاء دالة معرفة من قبل المستخدم (UDF) لربط القيم المقابلة المتعددة. تعمل هذه الطريقة في جميع إصدارات Excel.

1. اضغط باستمرار على مفتاحي "ALT + F11" لفتح نافذة "Microsoft Visual Basic for Applications".

2. انقر فوق "Insert" > "Module"، والصق الكود التالي في نافذة الوحدة.

كود VBA: البحث باستخدام VLOOKUP وربط القيم المتطابقة المتعددة في خلية

Function ConcatenateMatches(LookupValue As String, LookupRange As Range, ReturnRange As Range, Optional Delimiter As String = ", ") As String
'Updateby Extendoffice
    Dim Cell As Range
    Dim Result As String
    Result = ""
    For Each Cell In LookupRange
        If Cell.Value = LookupValue Then
            Result = Result & Cell.Offset(0, ReturnRange.Column - LookupRange.Column).Value & Delimiter
        End If
    Next Cell
    If Result <> "" Then
        Result = Left(Result, Len(Result) - Len(Delimiter))
    End If
    ConcatenateMatches = Result
End Function

3. ثم احفظ وأغلق هذا الكود، عد إلى ورقة العمل، وأدخل هذه الصيغة: =ConcatenateMatches(D2, $A$2:$A$16, $B$2:$B$16) في خلية فارغة حيث تريد وضع النتيجة، ثم اسحب مقبض التعبئة لأسفل لملء الصيغة في الخلايا الأخرى. تم إرجاع جميع القيم المقابلة بناءً على بيانات محددة في خلية واحدة مع فواصل فاصلة ومسافة، انظر لقطة الشاشة:

يمكن تحقيق ربط القيم المقابلة المتعددة في Excel من خلال طرق مختلفة، لكل منها مزاياها الخاصة. سواء كنت تفضل استخدام الدوال المدمجة مثل TEXTJOIN و FILTER، أو أدوات الجهات الخارجية مثل Kutools، أو حلول VBA المخصصة، فإن المفتاح هو اختيار الطريقة التي تناسب إصدار Excel الخاص بك، مستوى المهارة، والمتطلبات المحددة. إذا كنت مهتمًا باستكشاف المزيد من نصائح وحيل Excel، يقدم موقعنا آلاف الدروس التعليمية.