كيفية استخدام vlookup وربط القيم المقابلة المتعددة في Excel؟
عند استخدام VLOOKUP في Excel، فإن الدالة ستقوم عادةً بإرجاع فقط أول قيمة مطابقة تجدها بناءً على معيار البحث المحدد. ومع ذلك، هناك العديد من السيناريوهات الشائعة حيث قد تحتاج إلى استرداد ودمج جميع القيم المتطابقة المرتبطة بمفتاح معين، مثل سرد جميع الطلاب في صف أو جميع المنتجات المرتبطة بفئة معينة. وبما أن دالة 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, ""))
- 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 سيتم معالجتها.
- TEXTJOIN(", ", TRUE, ...)تأخذ هذه الوظيفة مخرجات وظيفة FILTER (مجموعة من النتائج المتطابقة)، وتجمعها في سلسلة نصية واحدة، مفصولة بالفاصل المحدد (فاصلة ومسافة)، مع تجاهل الخلايا الفارغة تلقائيًا.
- ", ": تحديد الفاصلة والمسافة كفاصل؛ يمكنك تغيير هذا الرمز حسب الحاجة، على سبيل المثال باستخدام فواصل منقوطة أو فواصل أسطر.
- TRUE: يضمن تجاهل الخلايا الفارغة أثناء عملية الجمع، مما يؤدي إلى إخراج مرتب ومنسق.
ملاحظة خاصة: هذه الطريقة تتطلب Excel 365 أو 2021 ولا تعمل في الإصدارات القديمة (مثل Excel 2019، 2016 أو ما قبلها). تحقق دائمًا من إصدار Excel لديك قبل التطبيق.
نصيحة: إذا تغيرت قيمة البحث الخاصة بك (مثل D2) أو تم إضافة عناصر متطابقة جديدة إلى نطاق البيانات، يتم تحديث النتيجة تلقائيًا دون أي خطوات إضافية.
القيود المحتملة: في مجموعات البيانات الكبيرة جدًا، قد يزداد وقت حساب الصيغة. كذلك، يجب على المستخدمين التأكد من عدم وجود خلايا مدمجة في نطاقات البحث أو النتائج، لأن ذلك يمكن أن يتسبب في أخطاء في الصيغ.
البحث باستخدام vlookup وربط القيم المقابلة المتعددة باستخدام Kutools لـ Excel
إذا وجدت أن طرق الصيغ المدمجة صعبة أو أن إصدار Excel الخاص بك لا يدعم وظائف متقدمة مثل TEXTJOIN و FILTER، يوفر Kutools لـ Excel حلًا رسوميًا سهل الاستخدام. ميزة البحث الواحد إلى العديد في Kutools تمكنك من البحث وربط النتائج المتطابقة المتعددة بعدة خطوات بسيطة، مما يجعلها مناسبة لكل من المبتدئين والمستخدمين المتقدمين. مع Kutools، لا حاجة لكتابة صيغ أو أكواد معقدة، وهو أمر مفيد بشكل خاص عند التعامل مع مجموعات بيانات كبيرة أو متغيرة تتطلب عمليات بحث وتجميع متكررة.
بعد تثبيت Kutools لـ Excel، اتبع الخطوات التالية:
انقر فوق Kutools > Super Lookup > One-to-Many Lookup (إرجاع نتائج متعددة) لفتح مربع الحوار. ضمن هذا الحوار، يمكنك بسرعة تكوين إعدادات البحث وإخراجها باستخدام الخطوات التالية:
- اختر خلايا الإخراج المستهدفة لنتائج الربط، والخلايا التي تحتوي على القيم التي ترغب في البحث عنها؛
- حدد نطاق الجدول الذي يحتوي على كل من مفاتيح البحث وأعمدة النتائج؛
- حدد العمود الذي يحتوي على مفاتيح البحث (Key Column) والعمود الذي تحتوي قيمه على النتائج المطلوبة (Return Column)؛
- انقر زر OK لتأكيد الإعدادات ومعالجة البيانات.
النتيجة: الآن سيعرض 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) في خلية فارغة حيث تريد نتيجة الإخراج. اسحب مقبض التعبئة لأسفل لنسخ الصيغة إلى الخلايا الأخرى حسب الحاجة. سيتم إرجاع جميع القيم المقابلة بناءً على قيمة بحث معينة وربطها في خلية واحدة، مفصولة بفاصلة ومسافة. انظر لقطة الشاشة:
- 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. انقر الزر لتشغيل الماكرو. ستظهر مربعات الإدخال لتحديد نطاق البيانات، نطاق البحث، ونطاق النتائج. ثم يتم عرض النتيجة المجمعة مباشرة في الخلايا المحددة للإخراج.
هذا النهج باستخدام الماكرو مفيد بشكل خاص إذا كنت تقوم كثيرًا بعمليات بحث متعددة الربط بقيم مختلفة، حيث يتجنب ازدحام ورقة العمل باستدعاءات الوظائف المعرفة من قبل المستخدم.
يمكنك بسهولة تعديل الفاصل في الكود إذا لزم الأمر، وتوسيع الماكرو لإخراج النتائج إلى خلية أو ملف حسب تدفق عملك.
ربط القيم المقابلة المتعددة في Excel ممكن باستخدام طرق مختلفة، ولكل منها فوائد محددة تعتمد على حالتك. سواء اخترت صيغ المصفوفات الديناميكية، أو الإضافات مثل Kutools لـ Excel أو الطرق القائمة على VBA، ستتمكن من تحسين قدرتك على تحليل وعرض البيانات المجمعة بكفاءة. بناءً على حجم وتعقيد مجموعة بياناتك، ضع في اعتبارك الطريقة التي توفر الأداء الأمثل وسهولة الصيانة لك أو لفريقك. في العمليات اليومية، تحقق من اتساق البيانات، وتجنب الخلايا المدمجة، وتحقق من نطاقات المرجع للحصول على أفضل النتائج. إذا واجهت أخطاء في حسابات الصيغ، قم بمراجعة أن نطاقاتك متطابقة مع البيانات وأنك تستخدم طريقة إدخال الصيغة الصحيحة لإصدار Excel الخاص بك.
لمزيد من تقنيات Excel المتقدمة ومجموعة واسعة من الأدلة العملية، قم بزيارة مكتبة دروسنا الشاملة.
أفضل أدوات زيادة الإنتاجية لمجموعة Office
عزز مهاراتك في Excel مع Kutools لـ Excel، وتمتع بكفاءة غير مسبوقة. يقدم Kutools لـ Excel أكثر من300 ميزة متقدمة لتعزيز الإنتاجية وتوفير وقت الحفظ. انقر هنا للحصول على الميزة التي تحتاجها أكثر...
Office Tab يجلب تجربة التبويبات إلى Office، ويجعل عملك أسهل بكثير
- فعّل تحرير وقراءة المستندات عبر التبويبات في Word وExcel وPowerPoint
- افتح وأنشئ مستندات متعددة في تبويبات جديدة ضمن نفس النافذة، بدلاً من فتحها في نوافذ جديدة.
- يمنحك زيادة إنتاجية تصل إلى50% ويوفر عليك مئات النقرات يومياً!