Skip to main content

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

Author: Xiaoyang Last Modified: 2025-08-06

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


البحث باستخدام vlookup وربط القيم المقابلة المتعددة بوظائف TEXTJOIN و FILTER

إذا كنت تستخدم Excel 365 أو Excel 2021، فإن الجمع بين وظائف TEXTJOIN و FILTER يوفر نهجًا فعالاً ومبنيًا على الصيغ للبحث وربط جميع القيم المتطابقة. هذه الحلول مناسبة بشكل خاص للمجموعات البيانات الديناميكية والمحسنة، حيث سيتم تحديث النتيجة تلقائيًا عند تغيير بيانات المصدر. يُفضل تطبيقها عندما يكون إصدار Excel الخاص بك يدعم وظيفة FILTER، التي تتوفر فقط في الإصدارات الحديثة من Office.

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

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

vlookup and concatenate multiple values with TEXTJOIN and FILTER Functions

شرح لهذه الصيغة:
  1. FILTER($B$2:$B$16, $A$2:$A$16=D2, "")هذه الجزء من الصيغة يتحقق من كل قيمة في النطاق $A$2:$A$16؛ إذا كانت متطابقة مع القيمة في D2، سيتم تضمين القيم المقابلة لها في النطاق $B$2:$B$16 في مجموعة النتائج.
    • $B$2:$B$16: النطاق الذي سيتم استرداد القيم المتطابقة منه.
    • $A$2:$A$16=D2: الشرط الذي يتم اختيار القيم بناءً عليه — فقط تلك الصفوف التي تكون قيمها في النطاق $A$2:$A$16 مساوية لمحتوى D2 سيتم معالجتها.
  2. TEXTJOIN(", ", TRUE, ...)تأخذ هذه الوظيفة مخرجات وظيفة FILTER (مجموعة من النتائج المتطابقة)، وتجمعها في سلسلة نصية واحدة، مفصولة بالفاصل المحدد (فاصلة ومسافة)، مع تجاهل الخلايا الفارغة تلقائيًا.
    • ", ": تحديد الفاصلة والمسافة كفاصل؛ يمكنك تغيير هذا الرمز حسب الحاجة، على سبيل المثال باستخدام فواصل منقوطة أو فواصل أسطر.
    • TRUE: يضمن تجاهل الخلايا الفارغة أثناء عملية الجمع، مما يؤدي إلى إخراج مرتب ومنسق.

ملاحظة خاصة: هذه الطريقة تتطلب Excel 365 أو 2021 ولا تعمل في الإصدارات القديمة (مثل Excel 2019، 2016 أو ما قبلها). تحقق دائمًا من إصدار Excel لديك قبل التطبيق.

نصيحة: إذا تغيرت قيمة البحث الخاصة بك (مثل D2) أو تم إضافة عناصر متطابقة جديدة إلى نطاق البيانات، يتم تحديث النتيجة تلقائيًا دون أي خطوات إضافية.

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


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

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

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

بعد تثبيت Kutools لـ Excel، اتبع الخطوات التالية:

انقر فوق Kutools > Super Lookup > One-to-Many Lookup (إرجاع نتائج متعددة) لفتح مربع الحوار. ضمن هذا الحوار، يمكنك بسرعة تكوين إعدادات البحث وإخراجها باستخدام الخطوات التالية:

  1. اختر خلايا الإخراج المستهدفة لنتائج الربط، والخلايا التي تحتوي على القيم التي ترغب في البحث عنها؛
  2. حدد نطاق الجدول الذي يحتوي على كل من مفاتيح البحث وأعمدة النتائج؛
  3. حدد العمود الذي يحتوي على مفاتيح البحث (Key Column) والعمود الذي تحتوي قيمه على النتائج المطلوبة (Return Column)؛
  4. انقر زر OK لتأكيد الإعدادات ومعالجة البيانات.
     specify the options in the dialog box

النتيجة: الآن سيعرض Kutools جميع القيم المتطابقة والمجمعة في الخلية المحددة لإخراج النتائج. انظر لقطة الشاشة:
concatenated based on the criteria by kutools

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


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

بالنسبة للمستخدمين الذين يتقنون VBA (Visual Basic for Applications)، أو أولئك الذين يستخدمون إصدارات قديمة من Excel التي تفتقر إلى دعم المصفوفات الديناميكية أو وظيفة FILTER، يمكنك إنشاء وظيفة معرفة من قبل المستخدم (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. احفظ وأغلق محرر VBA. عد إلى ورقة العمل الخاصة بك، واستخدم هذه الوظيفة المعرفة من قبل المستخدم عن طريق إدخال الصيغة: =ConcatenateMatches(D2, $A$2:$A$16, $B$2:$B$16) في خلية فارغة حيث تريد نتيجة الإخراج. اسحب مقبض التعبئة لأسفل لنسخ الصيغة إلى الخلايا الأخرى حسب الحاجة. سيتم إرجاع جميع القيم المقابلة بناءً على قيمة بحث معينة وربطها في خلية واحدة، مفصولة بفاصلة ومسافة. انظر لقطة الشاشة:

concatenated based on the criteria by vba

شرح لهذه الصيغة:
  • D2: قيمة البحث التي سيتم مطابقتها داخل مجموعة البيانات (LookupValue).
  • A2:A16: النطاق الذي تبحث فيه الدالة عن قيمة البحث (LookupRange).
  • B2:B16: النطاق الذي يحتوي على القيم التي سيتم ربطها عند مطابقة قيمة البحث (ReturnRange).

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

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

1. انقر فوق Developer Tools > Visual Basic لفتح محرر VBA.

2. في نافذة VBA، انقر فوق Insert > Module، ثم الصق هذا الكود في الوحدة:

Sub VLookupAndConcatenate()
    Dim ws As Worksheet
    Dim dataRange As Range, lookupRange As Range, resultRange As Range
    Dim dict As Object
    Dim i As Long, lastRow As Long
    Dim lookupValue As Variant, result As String
    Dim delimiter As String
    delimiter = ", "
    Set dict = CreateObject("Scripting.Dictionary")
    Set ws = ActiveSheet
    On Error Resume Next
    Set dataRange = Application.InputBox( _
        Prompt:="Please select the data range (contains lookup column and result column)", _
        Title:="Select Data Range", _
        Type:=8)
    On Error GoTo 0
    If dataRange Is Nothing Then Exit Sub
    On Error Resume Next
    Set lookupRange = Application.InputBox( _
        Prompt:="Please select the lookup range (single column)", _
        Title:="Select Lookup Range", _
        Type:=8)
    On Error GoTo 0
    If lookupRange Is Nothing Then Exit Sub
    On Error Resume Next
    Set resultRange = Application.InputBox( _
        Prompt:="Please select the starting cell for results output", _
        Title:="Select Output Location", _
        Type:=8)
    On Error GoTo 0
    If resultRange Is Nothing Then Exit Sub
    resultRange.Resize(lookupRange.Rows.Count, 1).ClearContents
    For i = 1 To dataRange.Rows.Count
        lookupValue = dataRange.Cells(i, 1).Value
        If Not dict.Exists(lookupValue) Then
            dict.Add lookupValue, dataRange.Cells(i, 2).Value
        Else
            dict(lookupValue) = dict(lookupValue) & delimiter & dataRange.Cells(i, 2).Value
        End If
    Next i
    For i = 1 To lookupRange.Rows.Count
        lookupValue = lookupRange.Cells(i, 1).Value
        If dict.Exists(lookupValue) Then
            resultRange.Cells(i, 1).Value = dict(lookupValue)
        Else
            resultRange.Cells(i, 1).Value = "Not Found"
        End If
    Next i
    MsgBox "Operation completed! Processed " & lookupRange.Rows.Count & " lookup values.", vbInformation
End Sub

3. انقر الزر Run button لتشغيل الماكرو. ستظهر مربعات الإدخال لتحديد نطاق البيانات، نطاق البحث، ونطاق النتائج. ثم يتم عرض النتيجة المجمعة مباشرة في الخلايا المحددة للإخراج.

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

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

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

لمزيد من تقنيات Excel المتقدمة ومجموعة واسعة من الأدلة العملية، قم بزيارة مكتبة دروسنا الشاملة.

أفضل أدوات زيادة الإنتاجية لمجموعة Office

🤖 Kutools AI Aide: أحدث ثورة في تحليل البيانات بفضل: التنفيذ الذكي|توليد الشيفرة|إنشاء الصيغ المخصصة|تحليل البيانات وتوليد الرسوم البيانية|استدعاء الوظائف المحسنة
الميزات الشائعة: إيجاد أو تمييز أو وضع علامة على المكررات|حذف الصفوف الفارغة|دمج الأعمدة أو الخلايا دون فقدان البيانات|تقريب...
بحث متقدم: بحث V بمعايير متعددة|بحث V بقيم متعددة|بحث في عدة ورقات|مطابقة غامضة ....
قائمة منسدلة متقدمة: أنشئ قائمة منسدلة بسرعة|قائمة منسدلة معتمدة|قائمة منسدلة متعددة الاختيار ....
إدارة الأعمدة: إضافة عدد معين من الأعمدة |نقل الأعمدة |تغيير حالة إظهار/إخفاء الأعمدة المخفية| مقارنة النطاقات و الأعمدة ...
ميزات مميزة: التركيز على الشبكة|عرض التصميم|شريط الصيغ المحسن|إدارة أدوات المصنف وورقة العمل|مكتبة النص التلقائي (Auto Text)|منتقي التاريخ|دمج البيانات |تشفير/فك تشفير الخلايا|إرسال البريد الإلكتروني حسب القائمة|مرشح متقدم|تصفية خاصة (تصفية الخلايا التي تحتوي على خط عريض أو مائل أو يتوسطه خط...) ...
أفضل15 مجموعة أدوات:12 أداة نصية (إضافة نص، حذف الأحرف المحددة، ... )|أكثر من50 نوعاً من الرسوم البيانية (مخطط جانت، ...)|أكثر من40 صيغة عملية (حساب العمر بناءً على تاريخ الميلاد، ...)|19 أداة إدراج (إدراج رمز الاستجابة السريعة، إدراج صورة من المسار، ... )|12 أداة تحويل (تحويل إلى كلمات، تحويل العملة، ...)|7 أدوات دمج و تقسيم (دمج متقدم للصفوف، تقسيم الخلايا، ...)|... والمزيد
استخدم Kutools بلغتك المفضلة – يدعم الإنجليزية، الإسبانية، الألمانية، الفرنسية، الصينية، وأكثر من40 لغة أخرى!

عزز مهاراتك في Excel مع Kutools لـ Excel، وتمتع بكفاءة غير مسبوقة. يقدم Kutools لـ Excel أكثر من300 ميزة متقدمة لتعزيز الإنتاجية وتوفير وقت الحفظ. انقر هنا للحصول على الميزة التي تحتاجها أكثر...


Office Tab يجلب تجربة التبويبات إلى Office، ويجعل عملك أسهل بكثير

  • فعّل تحرير وقراءة المستندات عبر التبويبات في Word وExcel وPowerPoint
  • افتح وأنشئ مستندات متعددة في تبويبات جديدة ضمن نفس النافذة، بدلاً من فتحها في نوافذ جديدة.
  • يمنحك زيادة إنتاجية تصل إلى50% ويوفر عليك مئات النقرات يومياً!