كيفية استخدام vlookup لمقارنة قائمتين في أوراق عمل منفصلة؟

استخدام Vlookup لمقارنة قائمتين في أوراق عمل منفصلة باستخدام الصيغ

إحدى الطرق العملية والمباشرة لمقارنة القوائم الموجودة في أوراق عمل Excel مختلفة هي استخدام دالة VLOOKUP. تساعدك هذه الطريقة على استخراج أو تعليم جميع الأسماء الموجودة في كل من Names-1 وNames-2 بفعالية:

1. في ورقة Names-1، اختر خلية مجاورة لبيانات قائمتك (على سبيل المثال، الخلية B2) وأدخل الصيغة التالية:

=VLOOKUP(A2,'Names-2'!$A$2:$A$19,1,FALSE)

ثم اضغط على Enter. إذا كان الاسم في الصف الحالي موجودًا في Names-2، فإن الصيغة تعيد الاسم؛ إذا لم يكن كذلك، سيظهر خطأ #N/A. انظر المثال أدناه:

2. انسخ الصيغة لأسفل عن طريق سحب معالج التعبئة لمقارنة كل اسم في Names-1 مقابل جميع الأسماء في Names-2. ستحتوي الإدخالات المتطابقة على الاسم، بينما ستعرض تلك التي لم يتم العثور عليها قيمة خطأ:

ملاحظات:

1. من أجل المزيد من الوضوح، قد تستخدم هذه الصيغة البديلة لإظهار مؤشرات "نعم" أو "لا" للتطابقات:

=IF(ISNA(VLOOKUP(A2,'Names-2'!$A$2:$A$19,1,FALSE)), "No", "Yes")

تعرض هذه الصيغة "نعم" للأسماء الموجودة في كلا الورقتين و"لا" للأسماء الموجودة فقط في Names-1:

2. عند استخدام هذه الصيغ، استبدل A2 بالخلية الأولى في قائمتك، Names-2 باسم الورقة المرجعية، وقم بتعديل $A$2:$A$19 لتتناسب مع النطاق الفعلي للبيانات في ورقة العمل الخاصة بك. تذكر أن النطاق يجب أن يبدأ وينتهي بالأرقام الصحيحة للصفوف لضمان تضمين جميع بياناتك.

3. نصائح للاستخدام: إذا ظهرت أخطاء #N/A حيث يجب أن تكون هناك تطابقات، تحقق بعناية من وجود مشكلات محتملة بسبب المسافات الإضافية، اختلافات تنسيق البيانات (نص مقابل رقم)، أو الأخطاء الإملائية في قوائمك. استخدم TRIM أو CLEAN في عمود مساعد لتنظيف البيانات إذا لزم الأمر.

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

استخدام Vlookup لمقارنة قائمتين في أوراق عمل منفصلة باستخدام Kutools for Excel

إذا كنت تمتلك Kutools for Excel، فإن ميزة Select Same & Different Cells يمكن أن تساعدك على مقارنة قائمتين من أوراق عمل مختلفة واختيار أو تسليط الضوء على الأسماء المشتركة بين هاتين الورقتين دون الحاجة إلى إدخال صيغ معقدة. هذه الطريقة فعالة بشكل خاص عندما تتعامل مع كميات كبيرة من البيانات أو تريد نتيجة مرئية ومميزة بلون يمكن تفسيرها بسهولة بنظرة سريعة.

بعد تثبيت Kutools for Excel، اتبع الخطوات التالية لمقارنة قوائمك بسهولة:

1. انتقل إلى علامة التبويب Kutools، ثم انقر على Select > Select Same & Different Cells كما هو موضح أدناه:

2. في مربع الحوار Select Same & Different Cells الذي تم فتحه:

(1.) ضمن Find values in، حدد النطاق من Names-1 الذي تحتاج لمقارنته؛

(2.) ضمن According to، حدد النطاق من Names-2 للمقارنة؛

(3.) في قسم Based on، اختر Each row للمقارنة بين الصفوف على التوالي؛

(4.) من قسم Find، حدد Same Values لتحديد وتسليط الضوء على الأسماء المتطابقة؛

(5.) اختياريًا، يمكنك تعيين لون خلفية أو لون خط لتسليط الضوء على النتائج وجعل التطابقات تبرز بصريًا.

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

اضغط لتحميل وتجربة Kutools for Excel مجانًا الآن!

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

في حالات نادرة، إذا لم ترجع الوظيفة النتائج المتوقعة، تحقق مما إذا كانت كلتا القائمتين مُنسقتين بنفس الطريقة (على سبيل المثال، كلاهما كنص، بدون مسافات رائدة/خلفية مخفية)، حيث قد تؤدي التباينات في التنسيق إلى فقدان التطابقات.

تنسيق شرطي باستخدام صيغة عبر الأوراق

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

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

الخطوات:

1. في Names-1، اختر النطاق الذي ترغب في تطبيق التمييز عليه (على سبيل المثال، A2:A19).

2. اذهب إلى Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.

3. في مربع الصيغة، أدخل الصيغة التالية:

=COUNTIF('Names-2'!$A$2:$A$19,A2)>0

تتحقق هذه الصيغة ما إذا كانت القيمة في A2 من Names-1 موجودة في أي مكان في Names-2!A2:A19.

4. انقر Format لاختيار لون تمييز، ثم انقر OK لتطبيق القاعدة. سيتم تمييز أي تطابقات تلقائيًا في النطاق المحدد.

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

تذكيرات الأخطاء: إذا لم تظهر التحديدات كما هو متوقع، تحقق من اختيارات نطاق الخلايا والإشارات عبر الأوراق للأخطاء. تأكد من عدم وجود مسافات رائدة/خلفية أو عدم توافق في التنسيق يؤدي إلى فقدان التطابقات. إذا لزم الأمر، استخدم TRIM في عمود مساعد لتنظيف القوائم لإجراء مقارنة دقيقة.

كود VBA - مقارنة قوائم تلقائيًا وتمييز أو استخراج التطابقات

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

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

كيفية تشغيل الماكرو لتسليط الضوء على التطابقات في Names-1 إذا كانت موجودة في Names-2:

1. انقر Developer Tools > Visual Basic لتشغيل نافذة Microsoft Visual Basic for Applications. في النافذة، انقر Insert > Module والصق الكود التالي في الوحدة الجديدة:

Sub HighlightMatchingNames()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim rng1 As Range
    Dim cell As Range
    Dim matchFound As Range
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set ws1 = Worksheets("Names-1")
    Set ws2 = Worksheets("Names-2")
    
    Set rng1 = ws1.Range("A2", ws1.Cells(ws1.Rows.Count, "A").End(xlUp))
    
    ws1.Range("A2:A" & ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row).Interior.ColorIndex = xlNone
    
    For Each cell In rng1
        Set matchFound = ws2.Range("A2:A" & ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row).Find( _
            What:=cell.Value, LookIn:=xlValues, LookAt:=xlWhole)
        
        If Not matchFound Is Nothing And cell.Value <> "" Then
            cell.Interior.Color = vbYellow
        End If
    Next cell
End Sub

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

حل المشكلات: إذا لم يتم تسليط الضوء على أي خلايا، تحقق من أن كلا ورقتَي العمل يحملان الاسم الدقيق "Names-1" و"Names-2" وأن نطاقات بياناتك تبدأ من A2. تأكد من تفعيل البرامج النصية وأنه لا يوجد ورقة عمل محمية أو مُرشحة. يمكن تخصيص هذا النهج بسهولة؛ على سبيل المثال، يمكنك تغيير لون التمييز، أو تعديل الكود لنسخ النتائج المتطابقة إلى ورقة أو عمود آخر.

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