كيفية ربط النصوص بسهولة بناءً على معايير في Excel؟

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

• دمج النصوص بناءً على المعايير باستخدام دالة TEXTJOIN (Excel 365، 2019 وما بعدها)
• دمج النصوص بناءً على المعايير باستخدام Kutools لـ Excel (جميع الإصدارات)
• دمج النصوص بناءً على المعايير باستخدام FILTER و TEXTJOIN (بديل لـ 365/2021+)
• دمج النصوص بناءً على المعايير باستخدام دالة معرفة من قبل المستخدم (جميع الإصدارات)

دمج النصوص بناءً على المعايير باستخدام دالة TEXTJOIN

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

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

1. أدخل الصيغة التالية في الخلية المستهدفة (على سبيل المثال، E2):

=TEXTJOIN(", ", TRUE, IF($A$2:$A$15=D2, $B$2:$B$15, ""))

2. في Excel 2019، بعد إدخال الصيغة، استخدم مجموعة المفاتيح Ctrl + Shift + Enter لتطبيقها كصيغة صفائف. في Excel 365/2021 وما بعدها، اضغط ببساطة على Enter. ثم اسحب مقبض التعبئة لأسفل لملء الصيغة للخلايا الأخرى التي تمثل معايير مختلفة. انظر لقطة الشاشة أدناه:

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

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

دمج النصوص بناءً على المعايير باستخدام Kutools لـ Excel

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

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

1. افتح ورقة العمل الخاصة بك، ثم انتقل إلى علامة التبويب "Kutools"، واختر "Super Lookup"، يتبعها "بحث واحد إلى العديد (يعود بنتائج متعددة)".
2. في الحوار الذي يظهر، حدد "منطقة الإخراج" حيث ستظهر النتائج، و"قيم البحث" والتي تعتبر مفاتيحك أو معاييرك الفريدة.
3. اختر مجموعة البيانات الكاملة كجدول المصدر الخاص بك.
4. اضبط "العمود الرئيسي" (العمود الذي يحتوي على معيارك، على سبيل المثال، البلد) و"عمود الإرجاع" (العمود الذي يحتوي على القيم التي سيتم دمجها، على سبيل المثال، الأسماء) باستخدام القوائم المنسدلة المتاحة.
5. انقر على "موافق" لتنفيذ العملية.
   

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

مزايا استخدام Kutools:

• تنطبق على أي إصدار من Excel
• لا حاجة لإدخال الصيغ أو العمليات الصفيفية
• تقلل من خطر الأخطاء الناتجة عن المراجع المعقدة للخلايا
• فعالة للمستخدمين غير المألوفين بصيغ متقدمة

دمج النصوص بناءً على المعايير باستخدام دوال FILTER و TEXTJOIN (بديل لـ Excel 365/2021 أو أعلى)

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

1. في الخلية الناتجة (على سبيل المثال، E2)، أدخل الصيغة أدناه:

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

2. اضغط على Enter لتأكيد الصيغة، ثم استخدم مقبض التعبئة لسحب الصيغة لأسفل لعدد الصفوف الذي تحتاج إليه.

هذه الطريقة تتجنب الحاجة إلى Ctrl + Shift + Enter وتتكيف تلقائيًا إذا تغيرت البيانات، مما يجعلها قوية للغاية للجداول الديناميكية. في الصيغة أعلاه، B$2:B$15 هي العمود الذي سيتم دمجه، A$2:A$15 هو المرجع للمعيار، و D2 هو قيمة المعيار المحددة. استبدل النطاقات حسب الحاجة لمجموعة البيانات الخاصة بك. لاحظ أن FILTER يتطلب إصدارًا متوافقًا من Excel.

دمج النصوص بناءً على المعايير باستخدام دالة معرفة من قبل المستخدم

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

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

2. في نافذة VBA، انقر على "إدراج" > "وحدة". بعد ذلك، انسخ والصق الكود التالي في لوحة الوحدة التي تظهر:

كود VBA: دمج النصوص بناءً على المعايير

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. بعد لصق الكود، احفظ وأغلق نافذة الكود. عد إلى ورقة عمل Excel الخاصة بك. لاستخدام هذه الدالة UDF، أدخل صيغة مثل ما يلي في الخلية E2، ثم استخدم مقبض التعبئة لسحب الصيغة لأسفل للقيم الأخرى:

=CONCATENATEIF($A$2:$A$15, D2, $B$2:$B$15, ",")

ملاحظة: في الصيغة أعلاه، A2:A15 يشير إلى النطاق مع المعايير (مثل البلد)، D2 هو المعيار المطلوب المطابقة، و B2:B15 هو نطاق النصوص التي سيتم دمجها. الفاصل الافتراضي هو الفاصلة (",") ولكنه يمكن تغييره إلى أي حرف أو سلسلة تفضلها. تأكد من أن نطاقات المعايير والنطاقات المدمجة هي بنفس الحجم، وإلا ستعيد الدالة خطأ.

نصائح للوظائف المعرفة من قبل المستخدم (UDFs):

• يجب تمكين الماكرو حتى تعمل وظائف UDFs.
• لا تقوم وظائف UDFs بإعادة الحساب تلقائيًا عند تغيير بيانات المصدر - اضغط على F9 لتحديث إذا لزم الأمر.

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

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