كيفية استخدام دالة VLOOKUP لإرجاع قيم متعددة في خلية واحدة في Excel؟

دالة VLOOKUP هي وظيفة قوية في Excel، ولكن بشكل افتراضي، تقوم بإرجاع أول قيمة مطابقة فقط. فماذا لو كنت بحاجة إلى استرداد جميع القيم المتطابقة ودمجها في خلية واحدة؟ هذا هو طلب شائع عند تحليل مجموعات البيانات أو تلخيص المعلومات. في هذا الدليل، سنرشدك خطوة بخطوة لاستخدام كل من الصيغ والميزات المفيدة لإرجاع قيم متعددة في خلية واحدة.

إرجاع قيم متعددة في خلية واحدة باستخدام دالة TEXTJOIN (Excel 2019 وOffice 365)

• إرجاع جميع القيم المتطابقة في خلية واحدة
• إرجاع جميع القيم المتطابقة بدون تكرار في خلية واحدة

إرجاع قيم متعددة في خلية واحدة باستخدام Kutools

إرجاع قيم متعددة في خلية واحدة باستخدام دالة معرفة من قبل المستخدم

• إرجاع جميع القيم المتطابقة في خلية واحدة
• إرجاع جميع القيم المتطابقة بدون تكرار في خلية واحدة

إرجاع قيم متعددة في خلية واحدة باستخدام دالة TEXTJOIN (Excel 2019 وOffice 365)

إذا كنت تستخدم إصدارًا أحدث من Excel مثل Excel 2019 وOffice 365، هناك دالة جديدة - TEXTJOIN، مع هذه الوظيفة القوية، يمكنك بسرعة البحث عن القيم باستخدام VLOOKUP وإرجاع جميع القيم المتطابقة في خلية واحدة.

  إرجاع جميع القيم المتطابقة في خلية واحدة

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

  إرجاع جميع القيم المتطابقة بدون تكرار في خلية واحدة

إذا كنت تريد إرجاع جميع القيم المتطابقة بناءً على بيانات البحث بدون تكرار، قد تساعدك الصيغة أدناه.

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

إرجاع قيم متعددة في خلية واحدة باستخدام Kutools

مع ميزة "Advanced Combine Rows" من Kutools لـ Excel، يمكنك بسهولة استرداد قيم متعددة متطابقة في خلية واحدة - لا حاجة لصيغ معقدة! وداعًا للحلول اليدوية واستكشف طريقة أكثر كفاءة للتعامل مع مهام البحث في Excel. دعنا نستعرض كيف يجعل Kutools لـ Excel كل هذا ممكنًا!

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

1. حدد نطاق البيانات الذي تريد دمج بيانات عمود واحد بناءً على عمود آخر.

2. انقر فوق "Kutools" > "Merge & Split" > "Advanced Combine Rows"، انظر لقطة الشاشة:

3. في مربع الحوار المنبثق "Advanced Combine Rows":

• انقر فوق اسم العمود الرئيسي ليتم دمجه بناءً عليه، ثم انقر فوق "Primary Key".
• ثم انقر فوق عمود آخر تريد دمج بياناته بناءً على العمود الرئيسي، وانقر فوق قائمة منسدلة من حقل "Operation"، واختر فاصلًا لفصل البيانات المدمجة من قسم "Combine".
• ثم، انقر فوق زر OK.

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

نصائح: إذا كنت تريد إزالة المحتوى المكرر أثناء دمج الخلايا، ما عليك سوى تحديد خيار "Delete Duplicate Values" في مربع الحوار. هذا يضمن أن الإدخالات الفريدة فقط سيتم دمجها في خلية واحدة، مما يجعل بياناتك أنظف وأكثر تنظيمًا دون أي جهد إضافي. انظر لقطات الشاشة:

قم بتنزيل Kutools لـ Excel الآن وجرّبه مجانًا!

إرجاع قيم متعددة في خلية واحدة باستخدام دالة معرفة من قبل المستخدم

دالة TEXTJOIN المذكورة أعلاه متاحة فقط لـ Excel 2019 وOffice 365، إذا كنت تستخدم إصدارات أخرى أقدم من Excel، يجب عليك استخدام بعض الأكواد لإكمال هذه المهمة.

  إرجاع جميع القيم المتطابقة في خلية واحدة

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

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

كود VBA: Vlookup لإرجاع قيم متعددة في خلية واحدة

Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant
'Updateby Extendoffice
Dim xResult As String
On Error Resume Next
If CriteriaRange.Count <> ConcatenateRange.Count Then
    ConcatenateIf = CVErr(xlErrRef)
    Exit Function
End If
For i = 1 To CriteriaRange.Count
    If CriteriaRange.Cells(i).Value = Condition Then
        xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
    End If
Next i
If xResult <> "" Then
    xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
End If
ConcatenateIf = xResult
Exit Function
End Function

3. ثم احفظ وأغلق هذا الكود، عد إلى ورقة العمل، وأدخل هذه الصيغة: =CONCATENATEIF($A$2:$A$11, E2, $C$2:$C$11, ", ") في خلية فارغة محددة حيث تريد وضع النتيجة، ثم اسحب مقبض التعبئة لأسفل للحصول على جميع القيم المقابلة في خلية واحدة كما تريد، انظر لقطة الشاشة:

  إرجاع جميع القيم المتطابقة بدون تكرار في خلية واحدة

لتخطي التكرارات في القيم المتطابقة المرجعة، يرجى القيام بما يلي باستخدام الكود أدناه.

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

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

كود VBA: Vlookup وإرجاع قيم متعددة فريدة متطابقة في خلية واحدة

Function MultipleLookupNoRept(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer)
'Updateby Extendoffice
    Dim xDic As New Dictionary
    Dim xRows As Long
    Dim xStr As String
    Dim i As Long
    On Error Resume Next
    xRows = LookupRange.Rows.Count
    For i = 1 To xRows
        If LookupRange.Columns(1).Cells(i).Value = Lookupvalue Then
            xDic.Add LookupRange.Columns(ColumnNumber).Cells(i).Value, ""
        End If
    Next
    xStr = ""
    MultipleLookupNoRept = xStr
    If xDic.Count > 0 Then
        For i = 0 To xDic.Count - 1
            xStr = xStr & xDic.Keys(i) & ","
        Next
        MultipleLookupNoRept = Left(xStr, Len(xStr) - 1)
    End If
End Function

3. بعد إدراج الكود، ثم انقر فوق "Tools" > "References" في نافذة "Microsoft Visual Basic for Applications" المفتوحة، وبعد ذلك، في مربع الحوار المنبثق "References – VBAProject"، حدد خيار "Microsoft Scripting Runtime" في مربع القائمة "Available References"، انظر لقطات الشاشة:

4. ثم انقر فوق OK لإغلاق مربع الحوار، احفظ وأغلق نافذة الكود، عد إلى ورقة العمل، وأدخل هذه الصيغة: =MultipleLookupNoRept(E2,$A$2:$C$11,3) في خلية فارغة حيث تريد إخراج النتيجة، ثم اسحب مقبض التعبئة لأسفل للحصول على جميع القيم المتطابقة، انظر لقطة الشاشة:

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

مقالات ذات صلة: