Skip to main content

Kutools for Office — مجموعة واحدة. خمسة أدوات. أنجز المزيد.

كيفية العثور على القيم المشتركة في 3 أعمدة في Excel؟

Author Xiaoyang Last modified

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

find common values in 3 columns

ابحث عن القيم المشتركة في 3 أعمدة باستخدام صيغ المصفوفات

ماكرو VBA لاستخراج القيم الموجودة في جميع الأعمدة الثلاثة

arrow blue right bubble ابحث عن القيم المشتركة في 3 أعمدة باستخدام صيغ المصفوفات

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

أدخل هذه الصيغة المصفوفة في خلية فارغة حيث تريد عرض أول قيمة مشتركة:

=LOOKUP("zzz",CHOOSE({1,2},"",INDEX(A$2:A$10,MATCH(0,COUNTIF(E$1:E1,A$2:A$10)+IF(IF(COUNTIF(B$2:B$8,A$2:A$10)>0,1,0)+IF(COUNTIF(C$2:C$9,A$2:A$10)>0,1,0)=2,0,1),0))))

كيفية استخدام هذه الصيغة المصفوفة:

  • بعد إدخال الصيغة في الخلية المحددة، اضغط Shift + Ctrl + Enter (وليس فقط Enter). سيحيط Excel الصيغة بأقواس معقوفة للإشارة إلى أنها صيغة مصفوفة.
  • اسحب الصيغة لأسفل العمود حتى تظهر خلايا فارغة. ستحصل بذلك على قائمة بجميع القيم المشتركة بين الأعمدة الثلاثة، وستشير الخلايا الفارغة إلى عدم وجود المزيد من التطابقات.

Find common values in3 columns with array formula

ملاحظات وشروحات حول المعاملات:

  1. إذا كنت تفضل استخدام صيغة مصفوفة مختلفة، فإن هذه الصيغة أيضًا تعيد جميع القيم الفريدة الموجودة في الأعمدة الثلاثة:
    =INDEX($A$2:$A$10, MATCH(0, COUNTIF($E$1:E1, $A$2:$A$10)+IF(IF(COUNTIF($B$2:$B$8, $A$2:$A$10)>0,1,0)+IF(COUNTIF($C$2:$C$9, $A$2:$A$10)>0,1,0)=2,0,1),0))
    مرة أخرى، تذكر الضغط Shift + Ctrl + Enter بعد كتابة أو لصق الصيغة.
  2. في هذه الصيغ:
    • A2:A10، B2:B8، C2:C9 هي نطاقات البيانات في كل من الأعمدة الثلاثة التي ترغب في مقارنتها.
    • E1 يشير إلى الخلية الموجودة مباشرة فوق مكان بدء الصيغة (لمنطق الاستبعاد). قم بتعديل مراجع الخلايا لتتناسب مع نطاقات البيانات الفعلية وموقع ظهور النتائج.
  3. تعمل هذه الطرق بشكل جيد لمجموعات البيانات المتوسطة ولكن قد تصبح بطيئة مع أحجام كبيرة جدًا بسبب متطلبات الحساب الخاصة بصيغ المصفوفات.
  4. كن حذرًا لتجنب تعديل نطاقات المصدر أثناء التنفيذ، حيث يمكن أن يؤدي ذلك إلى نتائج غير دقيقة أو أخطاء في الصيغ.
  5. إذا كانت النتيجة تحتوي على صفوف فارغة، فهذا يعني أنه تم استخراج جميع القيم المشتركة ولم يتبقَ أي نقاط تقاطع أخرى في الخلايا المتبقية.
a screenshot of kutools for excel ai

اكتشف سحر Excel مع Kutools AI

  • التنفيذ الذكي: قم بعمليات الخلية، وتحليل البيانات، وإنشاء المخططات - كل ذلك يتم من خلال أوامر بسيطة.
  • الصيغ المخصصة: قم بإنشاء صيغ مصممة خصيصًا لتسهيل سير عملك.
  • برمجة VBA: كتابة وتطبيق أكواد VBA بسهولة.
  • تفسير الصيغ: فهم الصيغ المعقدة بسهولة.
  • ترجمة النصوص: تجاوز الحواجز اللغوية داخل جداول البيانات الخاصة بك.
عزز قدرات Excel لديك باستخدام أدوات تعمل بالذكاء الاصطناعي. قم بالتنزيل الآن وعش تجربة الكفاءة كما لم يحدث من قبل!

ماكرو VBA لاستخراج القيم الموجودة في جميع الأعمدة الثلاثة

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

1. انقر فوق Developer > Visual Basic لفتح محرر VBA (إذا لم يكن شريط Developer مرئيًا، يمكنك تفعيله عبر File > Options > Customize Ribbon).

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

Sub FindCommonValuesThreeColumns()
    Dim dict1 As Object
    Dim dict2 As Object
    Dim dict3 As Object
    Dim resultDict As Object
    Dim rngA As Range
    Dim rngB As Range
    Dim rngC As Range
    Dim cell As Range
    Dim outputRow As Long
    Dim key As Variant
    
    On Error Resume Next
    
    Set dict1 = CreateObject("Scripting.Dictionary")
    Set dict2 = CreateObject("Scripting.Dictionary")
    Set dict3 = CreateObject("Scripting.Dictionary")
    Set resultDict = CreateObject("Scripting.Dictionary")

    ' Prompt the user to select the three column ranges
    Set rngA = Application.InputBox("Select the first column range", "KutoolsforExcel", Selection.Address, Type:=8)
    Set rngB = Application.InputBox("Select the second column range", "KutoolsforExcel", Selection.Address, Type:=8)
    Set rngC = Application.InputBox("Select the third column range", "KutoolsforExcel", Selection.Address, Type:=8)

    ' Store all unique values from each column into corresponding dictionaries
    For Each cell In rngA
        If Not dict1.exists(cell.Value) And cell.Value <> "" Then
            dict1.Add cell.Value, 1
        End If
    Next

    For Each cell In rngB
        If Not dict2.exists(cell.Value) And cell.Value <> "" Then
            dict2.Add cell.Value, 1
        End If
    Next

    For Each cell In rngC
        If Not dict3.exists(cell.Value) And cell.Value <> "" Then
            dict3.Add cell.Value, 1
        End If
    Next

    ' Check which values exist in all three dictionaries
    For Each key In dict1.keys
        If dict2.exists(key) And dict3.exists(key) Then
            resultDict.Add key, 1
        End If
    Next

    ' Output result to next empty column on the active sheet
    outputRow = 1
    For Each key In resultDict.keys
        Cells(outputRow, Columns.Count).End(xlToLeft).Offset(0, 1).Value = key
        outputRow = outputRow + 1
    Next

    MsgBox "Common values extracted next to your data.", vbInformation, "KutoolsforExcel"
End Sub

3. في نافذة VBA، ومع الوحدة المحددة، اضغط F5 أو انقر فوق زر التشغيل (▶) لتنفيذ الكود. سيتم مطالبتك بالتتابع لاختيار كل واحد من نطاقات الأعمدة الثلاثة التي ترغب في مقارنتها. استخدم فأرتك لتحديد الخلايا المناسبة أثناء كل موجه.

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

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

أفضل أدوات الإنتاجية لمكتب العمل

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

عزز مهاراتك في 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 Tabs PowerPoint
  • حزمة الكل في واحد — إضافات Excel وWord وOutlook وPowerPoint + Office Tab Pro
  • مثبّت واحد، ترخيص واحد — إعداد في دقائق (جاهز لـ MSI)
  • الأداء الأفضل معًا — إنتاجية مُبسطة عبر تطبيقات Office
  • تجربة كاملة لمدة30 يومًا — بدون تسجيل، بدون بطاقة ائتمان
  • قيمة رائعة — وفر مقارنة بشراء الإضافات بشكل منفرد