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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1. اضغط باستمرار على ALT + F11 مفاتيح ، ويفتح ميكروسوفت فيسوال باسيك للتطبيقات نافذة.

2. انقر إدراج > وحدة، والصق الكود التالي في ملف نافذة الوحدة.

كود 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, ", ") في خلية فارغة محددة حيث تريد وضع النتيجة ، ثم اسحب مقبض التعبئة لأسفل للحصول على جميع القيم المقابلة في خلية واحدة تريدها ، انظر لقطة الشاشة:

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

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

1. اضغط باستمرار على ALT + F11 مفاتيح لفتح ميكروسوفت فيسوال باسيك للتطبيقات نافذة.

2. انقر إدراج > وحدة، والصق الكود التالي في ملف نافذة الوحدة.

رمز 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. بعد إدخال الرمز ، انقر فوق الأدوات > مراجع حسابات في الفتح ميكروسوفت فيسوال باسيك للتطبيقات نافذة ، ثم في المنبثقة المراجع - VBAProject مربع الحوار ، تحقق وقت تشغيل البرمجة لـ Microsoft الخيار في المراجع المتاحة مربع القائمة ، انظر لقطات الشاشة:

4. ثم اضغط OK لإغلاق مربع الحوار ، وحفظ وإغلاق نافذة التعليمات البرمجية ، والعودة إلى ورقة العمل ، وإدخال هذه الصيغة: =MultipleLookupNoRept(E2,$A$2:$C$11,3) into a blank cell where you want to output the result, and then drag the fill hanlde down to get all matching values, see screenshot:

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

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

بعد تثبيت كوتولس ل إكسيليرجى القيام بما يلي:

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

2. انقر كوتولس > دمج وتقسيم > الجمع بين الصفوف المتقدمة، انظر لقطة الشاشة:

3. في خرجت الجمع بين الصفوف المتقدمة صندوق المحادثة:

• انقر فوق اسم عمود المفتاح المراد دمجه بناءً على ، ثم انقر فوق المفتاح الأساسي
• ثم انقر فوق عمود آخر تريد دمج بياناته بناءً على عمود المفتاح ، وانقر فوق دمج لاختيار فاصل واحد لفصل البيانات المجمعة.

4. ثم اضغط OK زر ، وستحصل على النتائج التالية:

قم بتنزيل Kutools for Excel والإصدار التجريبي المجاني الآن!

المزيد من المقالات ذات الصلة: