إنشاء تقاطع (جميع التركيبات) من عمودين في Excel — دليل كامل
عند العمل مع قائمتين في Excel—مثل أسماء المنتجات والأحجام، أو المناطق ومندوبي المبيعات، أو الطلاب والمواد الدراسية—قد تحتاج إلى إنشاء كل تركيبة ممكنة بين العناصر في القائمتين. يُعرف هذا الإجراء باسم «Crossjoin» (ويُشار إليه أيضًا بـ«الجداء الديكارتي»). يغطي هذا الدليل تعليمات خطوة بخطوة، ومزايا وعيوب كل طريقة، بالإضافة إلى أمثلة واقعية، لضمان اختيارك الأسلوب الأنسب لسير عملك.
ما هو التقاطع (Crossjoin)؟
التقاطع (المعروف أيضًا باسم الجداء الديكارتي) هو عمليةٌ تُنشئ كل تركيبة ممكنة بين قائمتين. في Excel، يعني ذلك ربط كل عنصر من القائمة A بكل عنصر من القائمة B، لتوليد مصفوفة شاملة من جميع التركيبات الممكنة.
يُعدّ التقاطع مفيدًا جدًّا في العديد من سيناريوهات البيانات الواقعية، مثل:
اختلافات المنتجات
اجمع بين الألوان × المقاسات × الأنماط لإنشاء كتالوج منتجاتك الكامل!
تحليل المبيعات
أنشئ تركيبات من المناطق × مندوبي المبيعات × الأرباع.
الجدولة والتخطيط
ولّد جميع التركيبات الممكنة بين الموظفين والورديات أو بين الطلاب والمواد الدراسية.
الاختبارات والمحاكاة
أنشئ تركيبات سيناريوهات للنمذجة أو التنبؤ أو التحقق.
مثال:
إذا كان لديك:
ستكون نتيجة Crossjoin كالتالي:
تنفيذ تقاطع في Excel
يوفّر Excel عدة طرق لإنشاء التقاطع، وتختلف أفضل طريقة باختلاف إصدار Excel الذي تستخدمه، ومدى راحتك في التعامل مع الصيغ أو الأدوات، وحجم مجموعة البيانات الخاصة بك. فيما يلي أربع طرق عملية وفعّالة يمكنك الاعتماد عليها—تتراوح بين صيغ بسيطة وأدوات متقدمة مثل Power Query وVBA. ولكل طريقة مزاياها الفريدة، مما يتيح لك اختيار الأنسب منها لسير عملك، وحجم بياناتك، واحتياجاتك من الأتمتة.
الطريقة 1: التقاطع باستخدام صيغة (Excel 365)
1.جهّز بياناتك: ضع قائمتك الأولى في عمود (مثل A2:A5 للمنتجات)، وقائمتك الثانية في عمود آخر (مثل C2:C5 للألوان).
2.أدخل الصيغة التالية في خلية فارغة حيث تريد عرض النتيجة، ثم اضغط مفتاح Enter. ستتمدّد الصيغة تلقائيًا وتعيد جميع التركيبات المُنشأة دفعة واحدة. انظر لقطة الشاشة:
=TEXTSPLIT(TEXTJOIN(",", TRUE, A2:A5 & "|" & TRANSPOSE(C2:C5)), "|", ",")
شرح هذه الصيغة:
- A2:A5 & «|» & TRANSPOSE(C2:C5): يزاوج كل قيمة في العمود A مع كل قيمة في العمودC.
- TEXTJOIN(",", TRUE, …): تدمج جميع العناصر في سلسلة نصية واحدة طويلة، مفصَّلة بفواصل.
- TEXTSPLIT(…, «|», ","): تقسم النص مرة أخرى إلى جدول مكوَّن من عمودين.
باختصار، تقوم الصيغة بإنشاء أزواجA|C، ثم تدمجها في سلسلة نصية واحدة، وبعد ذلك تقسمها مجددًا إلى جدول منظم مكوَّن من عمودين — لتوليد جميع التركيبات الممكنة فعليًّا.
نصائح:
بالإضافة إلى الصيغة الموضَّحة سابقًا، يمكنك استخدام الصيغة التالية للحصول على النتيجة نفسها.
=LET(a,A2:A5,b,C2:C5,
MAKEARRAY(ROWS(a)*ROWS(b),2,
LAMBDA(r,c,
IF(c=1, INDEX(a, 1+INT((r-1)/ROWS(b))), INDEX(b, 1+MOD(r-1, ROWS(b))))
)
))
المزايا
- ديناميكي بالكامل
- لا حاجة لأعمدة مساعدة
- يتم التحديث تلقائيًا عند تغيير قوائم المصدر
العيوب
- يتطلب Excel 365
- تبدو الصيغة معقدة للمبتدئين
✨ قائمة جميع التركيبات — ولِّد كل الاحتمالات بنقرة واحدة!
هل مللت من كتابة صيغ معقدة للحصول على كل التركيبات الممكنة؟ باستخدامKutools لـ Excel، يمكنك عرض كل تركيبة لأعمدة أو قيم متعددة فورًا — بدون صيغ، بدون Power Query، فقط بضع نقرات!
الطريقة 2: التقاطع باستخدام Power Query
يقدّم Power Query نهجًا نظيفًا وخاليًا من التعليمات البرمجية، ويعمل بسلاسة عبر جميع إصدارات Excel الحديثة. فهو يمكّنك من إنشاء تقاطع بسهولة من خلال خطوات سريعة تعتمد على النقر، دون الحاجة إلى كتابة أي صيغ. اتبع التعليمات خطوة بخطوة أدناه.
الخطوة 1: إنشاء جداول لكل عمود بيانات
1. حدد قائمة البيانات الأولى، ثم انقر فوقإدراج > جدول. في مربع الحوارإنشاء جدول، انقر فوقموافق، وستحصل على الجدول الأول.
2.من تبويبتصميم الجدول، غيِّر اسم الجدول إلى اسمٍ ذي معنى ليكون أسهل في الرجوع إليه لاحقًا.
3.كرِّر نفس الخطوات لتحويل عمود بيانات آخر إلى جدول وتعيين اسمٍ له.
الخطوة 2: استيراد الجداول وتحميلها كاتصالات
1.حدد الجدول الأول وانقر فوقبيانات>من جدول/نطاق، انظر لقطة الشاشة:
2.في نافذةمحرر Power Queryالمفتوحة، انقر فوقإغلاق وتحميل > إغلاق وتحميلمن تبويبالصفحة الرئيسية.
3.سيظهر مربع حواراستيراد البيانات. اختر الخيارإنشاء اتصال فقط، ثم انقر فوقموافق.
4.ستظهر لوحةالاستعلامات والاتصالاتعلى اليمين، لتُظهِر أن استعلامًا واحدًا قد أُضيف كاتصال فقط.
5.كرِّر نفس الخطوات لتحميل الجدول الثاني كاستعلام اتصال فقط، وستجده مدرجًا بجانب الأول في لوحةالاستعلامات والاتصالات.
الخطوة 3: إنشاء استعلام مرجعي وعمود مخصص
1.انقر بزر الماوس الأيمن على الاستعلام الذي ترغب في استخدامه كجدول أول في عملية الربط العرضي، ثم اخترمرجع.
2.في نافذةمحرر Power Query، انتقل إلى تبويبإضافة عمود، ثم انقر فوقعمود مخصص، انظر لقطة الشاشة:
3.في مربع حوارعمود مخصص، اكتب في مربعصيغة العمود المخصصاسم الجدول الآخر الذي تريد استخدامه في عملية الربط العرضي، ثم انقر فوق زرموافق.
ملاحظة:
إذا احتوى اسم الاستعلام على مسافات (مثل: لون المنتج)، فيجب وضعه بين علامتي اقتباس باستخدام الصيغة #«اسم الاستعلام» عند إدخاله في مربع صيغة العمود المخصص. فعلى سبيل المثال، بالنسبة إلى «لون المنتج»، اكتب #«لون المنتج».
4.سيظهر عمود مخصص جديد. انقر فوق زرتوسيعلعرض محتوياته.
5.في اللوحة الموسَّعة، تأكد من تحديد اسم العمود الذي تريد ربطه، ثم انقر فوقموافق.
6.الآن، يمكنك رؤية جميع التركيبات المولَّدة من الجدولين.
الخطوة 4: تحميل البيانات إلى ورقة العمل
انتقل إلى علامة التبويبالصفحة الرئيسية، ثم انقر فوقإغلاق وتحميل > إغلاق وتحميل. وسيتم تحميل الجدول الذي يحتوي على جميع التركيبات في ورقة عمل جديدة.
→
المزايا
- يدعم البيانات الضخمة: أداء متميز مع آلاف الصفوف.
- قابل لإعادة الاستخدام والتحديث: ما عليك سوى تحديث الاستعلام عند إضافة المزيد من البيانات إلى نطاق المصدر، وستُحدَّث النتائج تلقائيًا.
العيوب
- يتطلب خطوات إضافية بسيطة
- يتطلب معرفة أساسية بـ Power Query
الطريقة 3: التقاطع باستخدام جدول بيانات محوري
هذه الطريقة غير مباشرة، لكنها قد تُدهشك بفعاليتها حين تحتاج إلى إنشاء جميع التركيبات دون كتابة أي صيغ. وهي مثالية خصوصًا للمستخدمين الذين يفضلون النهج البصري القائم على النقر، أو لأولئك الذين يستخدمون إصدارات Excel التي لا تدعم صيغ المصفوفات الديناميكية.
1.أنشئ جدولين منفصلين لقوائم البيانات، وعطِّلهما أسماءً باستخدام نفس الخطوات الموضَّحة فيالخطوة 1 من الطريقة 2.
2. حدد الجدول الذي تريد استخدامه كعمود أول. بعد ذلك، انتقل إلى تبويبإدراج، ثم انقر فوقجدول محوري. انظر لقطة الشاشة:
3.في مربع حوارجدول محوري من جدول أو نطاق، حددورقة عمل موجودة، واختر موقع الجدول المحوري، وفعِّل الخيارإضافة هذه البيانات إلى نموذج البيانات، ثم انقر فوقموافق.
4.عندما تظهر لوحةحقول الجدول المحوريعلى اليمين، اختر اسم العمود من الجدول وسيُضاف تلقائيًا إلى منطقةالصفوف.
5.بعد ذلك، انتقل إلى تبويبالكلفي لوحةحقول الجدول المحوري، وحدد الجدول الذي تريد استخدامه كعمود ثانٍ في عملية الربط العرضي، ثم فعِّل اسم عموده لإضافته إلى منطقةالصفوف. وستحصل على جدول بيانات محوري كما في لقطة الشاشة أدناه:
6. انقر على خلية واحدة في جدولك المحوري، ثم انتقل إلى تبويبتصميم، وحددتخطيط التقرير > عرض بشكل جدولي، لتحصل فورًا على جدول محوري بتنسيق جدولي أنيق! انظر لقطة الشاشة:
7.استمر في النقر فوقتخطيط التقرير > تكرار جميع تسميات العناصرلعرض جميع العناصر في كل صف.
8.أخيرًا، انقر فوقالمجاميع الكلية > إيقاف للصفوف والأعمدة.
الآن، يعرض جدول PivotTable قائمةً نظيفةً بجميع التركيبات دون أي صفوف أو أعمدة ملخّصة.
المزايا
- لا حاجة لصيغ أو Power Query
- سهل جدًّا وبصري
- مناسب للتحليل السريع
العيوب
- ليس ديناميكيًّا
- يتطلب إجراءات يدوية
- الناتج غير مرتبط بالبيانات الأصلية
الطريقة 4: التقاطع باستخدام دالة معرّفة من قبل المستخدم (Excel 365 / Excel 2021 وما بعده)
إذا كنت بحاجةٍ متكررةٍ إلى إنشاء جميع التركيبات الممكنة بين قائمتين، فإن الدالة المعرّفة من قبل المستخدم (UDF) تُقدّم حلاً نظيفًا، قابلاً لإعادة الاستخدام، ومرنًا للغاية. ومع Excel 365 أو Excel 2021، يُولّد مخرج المصفوفة المتمدّدة نتائجَ ديناميكيةً بالكامل، مما يوفّر لك طريقةً قويةً ومريحةً لأتمتة عمليات التقاطع.
1. اضغطAlt + F11 لفتح محرر VBA.
2.بعد ذلك، انقر فوقإدراج > وحدة نمطية، ثم انسخ والصق الكود التالي في الوحدة النمطية الفارغة.
Function CrossJoin(list1 As Range, list2 As Range)
'Updateby Extendoffice
Dim arr1, arr2, result()
Dim i As Long, j As Long, r As Long
arr1 = list1.Value
arr2 = list2.Value
ReDim result(1 To UBound(arr1, 1) * UBound(arr2, 1), 1 To 2)
r = 1
For i = 1 To UBound(arr1, 1)
For j = 1 To UBound(arr2, 1)
result(r, 1) = arr1(i, 1)
result(r, 2) = arr2(j, 1)
r = r + 1
Next j
Next i
CrossJoin = result
End Function
3.عُد إلى ورقة عمل Excel، وأدخل الصيغة أدناه، ثم اضغطEnter، وسيقوم Excel تلقائيًا بملء جميع التركيبات.
=CrossJoin(A2:A5, C2:C5)
الخاتمة
باختصار، يوفّر تنفيذ تقاطع في Excel حلولًا مرنة وفعّالة متعددة، تتيح لك اختيار الطريقة الأنسب بناءً على احتياجاتك الخاصة وبيئة عملك:
- الصيغةتستفيد من صيغ المصفوفات الديناميكية في Excel 365 لتوليد النتائج بسرعة دون الحاجة إلى البرمجة، مما يجعلها مثالية للمستخدمين البسيطين الذين يفضلون الصيغ الأصلية.
- Power Query يوفّر عمليةً واضحةً وقابلةً لإعادة الاستخدام، قادرةً على التعامل مع مجموعات البيانات الكبيرة، مما يجعله الخيار الأمثل لتنظيف البيانات والأتمتة في التقارير.
- طريقةجدول البيانات المحوريقد تكون أقل مباشرة، لكنها تُثبت فعاليتها العالية وبديهيتها للجمهور المألوف بها.
- دالةVBA المعرّفة من قبل المستخدمتوفّر أعلى مستوى من التخصيص، وهي مثالية للسيناريوهات التي تتطلب التكامل ضمن كود ماكرو معقّد.
أفضل أدوات الإنتاجية لمكتبتك
عزِّز مهاراتك في 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 يومًا بكامل الميزات— بدون تسجيل، بدون بطاقة ائتمان
- أفضل قيمة— وفِّر مقارنةً بشراء الإضافات بشكل منفصل