كيف يمكن إرجاع عدة نطاقات من نتائج البحث، مفصَّلة بفواصل، في خلية واحدة؟
عند العمل مع البيانات في Excel، قد تجد نفسك بحاجة إلى إرجاع قيم مطابقة متعددة من عملية بحث وعرضها جميعًا في خلية واحدة، مفصَّلة بفواصل أو شرطات أو غيرها. وعلى الرغم من أن دوال البحث المدمجة مثل «VLOOKUP» و«INDEX-MATCH» رائعة للعثور على تطابق واحد، فإن دمج نتائج متعددة في خلية واحدة يتطلب نهجًا أكثر تقدمًا. تقدّم هذه المقالة بعض الطرق الفعّالة لإنجاز هذه المهمة في Excel.
إرجاع عدة نطاق قيمة البحث في خلية واحدة مفصولة بفواصل
إرجاع عدة نطاق قيمة البحث في خلية واحدة مفصولة بفواصل باستخدام دالتي TEXTJOIN وFILTER
إذا كان بإمكانك الوصول إلى Excel 365 أو Excel 2021 أو أي إصدار أحدث، فإن استخدام صيغة المصفوفة الديناميكية يُعد الطريقة الأبسط.
يرجى تطبيق الصيغة أدناه في خلية فارغة، ثم اضغط مفتاح «Enter» للحصول على النتيجة.
=TEXTJOIN(", ", TRUE, FILTER($B$2:$B$15, $A$2:$A$15=D2))بعد ذلك، اسحب مقبض التعبئة لأسفل لملء الصيغة في الخلايا الأخرى، انظر لقطة الشاشة:
- FILTER(B2:B15، A2:A15=D2): تستخرج جميع القيم من العمود B التي يتوافق تواريخها في العمود A مع القيمة الموجودة في الخلية D2.
- TEXTJOIN(«، »، TRUE، ...): تدمج القيم الناتجة في سلسلة نصية واحدة مفصولة بفواصل.
إرجاع عدة نطاق قيمة البحث في خلية واحدة مفصولة بفواصل باستخدام Kutools لـ Excel
«Kutools لـ Excel» هو إضافة مصممة لتبسيط المهام المعقدة في Excel. وتتيح لك ميزته المتقدمة «بحث واحد إلى العديد» استخراج جميع القيم المطابقة في خلية واحدة باستخدام فاصل معيّن—بدون الحاجة إلى صيغ معقدة أو برمجة VBA.
بعد تثبيت Kutools لـ Excel، يرجى اتباع الخطوات التالية:
انقر على «Kutools» > «بحث متقدم» > «بحث واحد إلى العديد (تُرجع نتائج متعددة)» لفتح مربع الحوار. ثم في مربع الحوار، يرجى تحديد العمليات على النحو التالي:
- حدد «منطقة وضع القائمة ونطاق قيمة البحث» في مربعي النص بشكل منفصل؛
- حدد نطاق الجدول الذي تريد استخدامه؛
- حدّد العمود الرئيسي وعمود الإرجاع من القوائم المنسدلة «العمود الرئيسي» و«عمود الإرجاع» بشكل منفصل؛
- أخيرًا، انقر على زر «موافق».
النتيجة:
تم دمج جميع الخلايا المقابلة التي تحمل نفس القيمة في خلية واحدة مفصولة بفواصل، انظر لقطة الشاشة:
إرجاع عدة نطاق قيمة البحث في خلية واحدة مفصولة بفواصل باستخدام دالة مُعرَّفة من قِبل المستخدم
إذا لم تكن تمتلك Excel 365 أو Excel 2021، فسترشدك هذه الفقرة خطوة بخطوة إلى كيفية إنشاء دالة مُعرَّفة من قِبل المستخدم (UDF) واستخدامها لإرجاع جميع نتائج البحث في خلية واحدة، مفصَّلةً بفواصل.
1. اضغط مع الاستمرار على مفتاحَي «ALT» + "F11" لفتح نافذة «مايكروسوفت فيجوال بيسك للتطبيقات».
2. انقر على «Insert» > «Module»، ثم الصق الكود التالي في نافذة الوحدة النمطية.
كود VBA: إرجاع عدة نطاق قيمة البحث في خلية واحدة مفصولة بفواصل
Function JoinLookupValues(lookupValue As String, lookupRange As Range, returnRange As Range, delimiter As String) As String
'Updateby Extendoffice
Dim result As String
Dim i As Long
result = ""
For i = 1 To lookupRange.Rows.Count
If lookupRange.Cells(i, 1).Value = lookupValue Then
result = result & returnRange.Cells(i, 1).Value & delimiter
End If
Next i
If Len(result) > 0 Then
JoinLookupValues = Left(result, Len(result) - Len(delimiter))
Else
JoinLookupValues = ""
End If
End Function
3. بعد ذلك، احفظ هذا الكود وأغلق نافذة الوحدة النمطية، ثم عُد إلى ورقة العمل الخاصة بك وأدخل الصيغة أدناه في خلية فارغة لإظهار النتيجة. بعد ذلك، اسحب مقبض التعبئة لأسفل لتطبيق الصيغة على الخلايا الأخرى، كما هو موضح في لقطة الشاشة:
=JoinLookupValues(D2, $A$2:$A$15, $B$2:$B$15, ", ")
ختامًا، يمكنك تحقيق إرجاع عدة نطاقات من نتائج البحث في خلية واحدة مفصولة بفواصل بكفاءة باستخدام طرق مختلفة، اعتمادًا على إصدار Excel الخاص بك واحتياجاتك. وباختيار الطريقة الأنسب لمتطلباتك، ستتمكن من تبسيط تحليل البيانات وتعزيز إنتاجيتك. إذا كنت مهتمًا باكتشاف المزيد من نصائح وحيل Excel،فإن موقعنا الإلكتروني يقدم آلاف الدروس التعليمية.
مقالات ذات صلة:
- بحث عمودي (Vlookup) وإرجاع قيم متعددة دون تكرار
- عند العمل مع البيانات في Excel، قد تحتاج أحيانًا إلى استرجاع قيم متعددة تطابق معيار بحث معيّن. لكن دالة VLOOKUP الافتراضية تُعيد قيمة واحدة فقط. فإذا وُجدت تطابقات متعددة وترغب في عرضها جميعًا في خلية واحدة دون تكرار، يمكنك اللجوء إلى طرق بديلة لتحقيق ذلك.
- إرجاع قيم مطابقة متعددة بناءً على معيار واحد أو أكثر
- عادةً، يُعد البحث عن قيمة معيّنة وإرجاع العنصر المطابق أمرًا سهلًا لمعظمنا باستخدام دالة VLOOKUP. ولكن، هل سبق أن حاولت إرجاع قيم مطابقة متعددة بناءً على معيار واحد أو أكثر، كما هو موضح في لقطة الشاشة التالية؟ في هذه المقالة، سأقدّم بعض الصيغ الفعّالة لحل هذه المهمة المعقدة في Excel.
- بحث عمودي (Vlookup) وإرجاع آخر قيمة مطابقة
- إذا كانت لديك قائمة تحتوي على عناصر متكررة، وترغب في معرفة آخر قيمة تطابق البيانات التي حددتها، فكيف يمكنك تحقيق ذلك؟
- بحث عمودي (Vlookup) وإرجاع البيانات المطابقة بين قيمتين
- في Excel، يمكننا استخدام دالة VLOOKUP الاعتيادية لاسترجاع القيمة المقابلة بناءً على بيانات معيّنة. لكن في بعض الأحيان، قد نحتاج إلى إجراء بحث عمودي لإرجاع القيمة المطابقة التي تقع بين قيمتين، كما يظهر في لقطة الشاشة التالية. كيف يمكنك تنفيذ هذه المهمة في Excel؟
أفضل أدوات الإنتاجية لمكتبتك
عزِّز مهاراتك في Excel باستخدام Kutools لـ Excel، وعايش الكفاءة كما لم تفعل من قبل.يقدّم Kutools لـ Excel أكثر من 300 ميزة متقدمة لتعزيز الإنتاجية ووقت الحفظ.انقر هنا للحصول على الميزة التي تحتاجها أكثر من غيرها...
يجلب Office Tab واجهة ذات علامات تبويب إلى Office، ويجعل عملك أسهل بكثير
- تمكّن من التحرير والقراءة باستخدام علامات التبويب في Word وExcel وPowerPoint، وPublisher وAccess وVisio وProject.
- افتح وأنشئ مستندات متعددة في علامات تبويب جديدة داخل النافذة نفسها، بدلاً من فتح نوافذ جديدة.
- يزيد إنتاجيتك بنسبة 50% ويوفّر لك مئات نقرات الفأرة كل يوم!
جميع الإضافات من Kutools في برنامج تثبيت واحد!
Kutools for Office حزمةٌ تحتوي على إضافاتٍ مخصصة لتطبيقات Excel وWord وOutlook وPowerPoint، إلى جانب Office Tab Pro، مما يجعلها الخيار المثالي للفِرق التي تعمل عبر تطبيقات Office.
- حزمة شاملة واحدة— إضافات Excel وWord وOutlook وPowerPoint بالإضافة إلى Office Tab Pro
- برنامج تثبيت واحد، ترخيص واحد— الإعداد خلال دقائق (جاهز لـ MSI)
- يعمل بشكل أفضل معًا— إنتاجية ميسَّرة عبر تطبيقات Office
- تجربة مجانية لمدة 30 يومًا بكامل الميزات— بدون تسجيل، بدون بطاقة ائتمان
- أفضل قيمة— وفِّر مقارنةً بشراء الإضافات بشكل منفصل