كيفية مقارنة ورقتي Excel للتكرارات: 5 طرق سريعة

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

الطرق الأكثر شيوعًا للعثور على التكرارات في ورقتي Excel هي استخدام:

1. وظائف VLOOKUP أو COUNTIF أو EXACT

2. تنسيق مشروط

3. استعلام القوة

4. الأدوات الخارجية والوظائف الإضافية

5. التحقق البصري من التكرارات

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

هيا بنا نبدأ!

جدول المحتويات

• نموذج لأوراق العمل والبيانات
• 1. استخدام وظائف VLOOKUP أو COUNTIF أو بالضبط للبحث عن التكرارات
  • أ. كيف تستخدم وظيفة VLOOKUP للبحث عن التكرارات في ورقتين؟
    • ماذا لو كنت تتعامل مع مصنفات مختلفة؟
  • ب. كيف تستخدم وظيفة COUNTIF للبحث عن التكرارات عبر أوراق العمل؟
  • ج. كيف تستخدم الوظيفة الدقيقة للبحث عن التكرارات عبر أوراق العمل؟
• 2. كيفية استخدام التنسيق الشرطي للصفوف المكررة
  • كيفية استخدام إدارة قواعد التنسيق الشرطي
• 3. كيفية استخدام Power Query للبحث عن التكرارات عبر أوراق العمل
• 4. أدوات ووظائف إضافية لتحديد التكرارات عبر أوراق العمل
• 5. كيفية التحقق بصريًا من التكرارات في صفحتين
• 3 نصائح لإعداد أوراق عمل Excel الخاصة بك
• كيفية التعامل مع الأخطاء والتناقضات
• افكار اخيرة

نموذج لأوراق العمل والبيانات

لاتباع الأمثلة الواردة في هذه المقالة ، قم بإنشاء مصنف Excel جديد بهذه البيانات في العمود A من الورقة الأولى:

• تفاحة

• البرتقالي

• كُمَّثرَى

• الفراولة

ضع هذه البيانات في العمود A من الورقة الثانية:

• كُمَّثرَى

• الفراولة

• تفاحة

• أناناس

تبدو أوراق العمل الخاصة بك كما يلي:

بمجرد إعداد أوراق العمل الخاصة بك ، يمكننا الآن الانتقال إلى الطرق المختلفة للعثور على التكرارات في ورقتين ، بدءًا من وظائف VLOOKUP و COUNTIF و EXACT.

دعنا نذهب!

1. استخدام وظائف VLOOKUP أو COUNTIF أو بالضبط للبحث عن التكرارات

يحتوي Excel على ثلاث وظائف مضمنة يمكن أن تجعل العثور على التكرارات أمرًا سهلاً: VLOOKUP و COUNTIF و EXACT .

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

تخدم كل وظيفة غرضًا فريدًا ويمكن أن يؤدي فهم كيفية استخدامها إلى تبسيط عملية تحليل البيانات بشكل كبير.

في الأقسام التالية ، سوف نستكشف كيفية استخدام وظائف VLOOKUP و COUNTIF و EXACT لتحديد التكرارات بشكل فعال في أوراق عمل Excel الخاصة بك.

أ. كيف تستخدم وظيفة VLOOKUP للبحث عن التكرارات في ورقتين؟

VLOOKUP تعني البحث العمودي. يمكنك استخدام هذه الوظيفة للعثور على قيم مكررة بين عمودين. هذا هو بناء الجملة:

= VLOOKUP (lookup_value، table_array، col_index_num، [range_lookup])

• lookup_value : القيمة التي تريد البحث عنها في العمود الأول من table_array.

• table_array : نطاق الخلايا الذي يحتوي على البيانات التي تريد البحث فيها.

• col_index_num : رقم العمود في table_array الذي تريد إرجاع القيمة منه.

• range_lookup : اختياري. إنها إما TRUE (مطابقة تقريبية) أو خطأ (مطابقة تامة). الافتراضي هو TRUE.

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

على سبيل المثال ، يشير هذا إلى الخلايا A2 إلى A5 في الورقة 2 من نفس المصنف: Sheet 2! $ A $ 2: $ A $ 5.

لاستخدام وظيفة VLOOKUP في نموذج جدول البيانات ، اتبع الخطوات التالية:

1. حدد الخلية B2 لعرض نتيجة المقارنة الأولى.

2. اكتب هذه الصيغة: = VLOOKUP (A2، Sheet2! $ A $ 2: $ A $ 5، 1، FALSE).

3. اضغط على Enter لعرض نتيجة المقارنة.

4. املأ الصيغة لمقارنة القيم لبقية الصفوف في الورقة الأولى.

ستبدو النتائج كما في الصورة أدناه:

يمكنك ترتيب العرض من خلال إظهار رسالة سهلة الاستخدام بدلاً من الخطأ عند عدم العثور على نسخة مكررة.

على سبيل المثال ، ستعرض هذه الصيغة "نعم" أو "لا" للقيم التي تم العثور عليها وغير الموجودة على التوالي:

= IF (ISNA (VLOOKUP (A2، Sheet 2! $ A $ 2: $ A $ 5، 1، FALSE))، "لا"، "نعم")

يوجد المزيد من الأمثلة على هذه الوظيفة في مقالتنا حول استخدام VLOOKUP لمقارنة عمودين .

ماذا لو كنت تتعامل مع مصنفات مختلفة؟

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

أنت بحاجه إلى:

• قم بتضمين اسم مصنف Excel بين قوسين

• اتبع باسم ورقة العمل

• قم بإحاطة المصنف وورقة العمل بعلامات اقتباس.

على سبيل المثال ، إذا كانت الخلايا موجودة في ورقة تسمى Sheet2 في مصنف باسم "WB 2.xlsx" ، فسيبدو التنسيق كما يلي:

'[WB 2.xlsx] ورقة 2'! $ A $ 2: $ 5 $

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

ب. كيف تستخدم وظيفة COUNTIF للبحث عن التكرارات عبر أوراق العمل؟

تُستخدم الدالة COUNTIF في Excel لحساب عدد الخلايا ضمن نطاق محدد والتي تفي بمعايير معينة.

لمقارنة أوراق متعددة ، يمكنك حساب عدد الخلايا في ورقة العمل الثانية التي تتطابق مع خلية في ورقة العمل الأولى.

هذا هو تركيب الوظيفة:

= COUNTIF (النطاق ، المعايير)

• النطاق : نطاق الخلايا الذي تريد حسابه بناءً على المعايير المحددة.

• المعايير : الشرط الذي يجب تحقيقه لخلية ليتم حسابها.

لاستخدام الدالة مع نموذج البيانات ، اتبع الخطوات التالية:

1. حدد الخلية B2 لعرض نتيجة المقارنة الأولى.

2. اكتب هذه الصيغة: = COUNTIF (Sheet2! $ A $ 2: $ A $ 5، A2)

3. اضغط على Enter لعرض نتيجة المقارنة.

4. املأ الصيغة لمقارنة القيم لبقية الصفوف في الورقة الأولى.

ستعثر الوظيفة على تطابق واحد لبعض الخلايا ولا يوجد تطابق مع البعض الآخر. تعرض خلية المقارنة العدد. هذه هي النتيجة من عينة البيانات:

يمكن استخدام وظيفة COUNTIF لمهام أخرى مفيدة مثل حساب الخلايا غير الفارغة في Excel .

ج. كيف تستخدم الوظيفة الدقيقة للبحث عن التكرارات عبر أوراق العمل؟

يمكن أيضًا استخدام وظيفة EXACT في Excel للبحث عن التكرارات داخل نفس الخلايا في ورقتي عمل Excel مختلفتين. الصيغة هي:

= دقيق (نص 1 ، نص 2)

• النص 1 هو أول سلسلة نصية تريد مقارنتها.

• text2 هي السلسلة النصية الثانية التي تريد مقارنتها.

اتبع هذه الخطوات:

1. حدد الخلية B2.

2. اكتب الصيغة = بالضبط (A2، Sheet2! A2)

3. اضغط على Enter لعرض نتيجة المقارنة. سترجع الصيغة TRUE إذا كانت كلتا القيمتين متطابقتين أو FALSE خلاف ذلك.

4. املأ الصيغة لمقارنة القيم لبقية الصفوف في الورقة الأولى.

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

يمكن أن يكون هذا مفيدًا مع البيانات المطلوبة حيث لا تتوقع سوى استثناءات قليلة.

تعد وظائف VLOOKUP و COUNTIF و EXACT مفيدة في العثور على التكرارات ، لكن Excel برنامج متعدد الاستخدامات ، وهناك خيارات أخرى. في القسم التالي ، سنلقي نظرة على كيفية استخدام التنسيق الشرطي لتحديد التكرارات في ورقتين.

2. كيفية استخدام التنسيق الشرطي للصفوف المكررة

في هذا القسم ، ستتعلم كيفية استخدام التنسيق الشرطي للبحث عن الصفوف المكررة وتمييزها في ورقتي عمل Excel.

لإنشاء قاعدة تنسيق شرطي ، اتبع الخطوات التالية:

1. حدد نطاق الخلايا التي تحتوي على البيانات (A2: A5 في هذه الحالة).

2. انقر فوق علامة التبويب "الصفحة الرئيسية" في شريط Excel.

3. انقر فوق "تنسيق شرطي" في مجموعة "الأنماط".

4. اختر "قاعدة جديدة" من القائمة المنسدلة.

المهمة التالية هي توفير صيغة لاستخدامها القاعدة. اتبع هذه الخطوات:

1. اختر "استخدام صيغة لتحديد الخلايا المراد تنسيقها" في مربع الحوار

2. أدخل الصيغة التالية: = COUNTIF (Sheet2! $ A $ 2: $ A $ 5، A2)> 0

أخيرًا ، يمكنك تطبيق التنسيق الذي تفضله للخلايا المكررة.

1. انقر فوق الزر "تنسيق" لفتح مربع الحوار "تنسيق الخلايا".

2. اختر تنسيقًا ، على سبيل المثال ، املأ التكرارات بلون خلفية أصفر.

3. انقر فوق موافق.

يتم الآن تمييز بياناتك المكررة باللون الأصفر.

كيفية استخدام إدارة قواعد التنسيق الشرطي

بمجرد إنشاء قاعدة التنسيق الشرطي ، يمكنك إدارتها باستخدام مدير قواعد التنسيق الشرطي.

للوصول إلى المدير:

1. انتقل إلى علامة التبويب الصفحة الرئيسية.

2. انقر فوق تنسيق شرطي.

3. اختر "إدارة القواعد".

سترى قائمة بجميع قواعد التنسيق الشرطي المطبقة على الورقة المحددة. يمكنك تعديل أو حذف أو تغيير ترتيب القواعد عن طريق تحديد القاعدة والنقر على الأزرار المناسبة.

لتطبيق نفس القاعدة على الورقة الأخرى ، اتبع الخطوات التالية:

1. حدد النطاق الذي تريد مقارنته في الورقة الثانية.

2. انتقل إلى مدير قواعد التنسيق الشرطي.

3. حدد القاعدة ، انقر فوق قاعدة مكررة ثم اضغط على تحرير القاعدة.

4. استبدل "Sheet2" باسم الورقة الأولى للمقارنة.

الآن بعد أن قمت بتطبيق قاعدة التنسيق الشرطي على كلتا الورقتين ، سيتم تمييز التكرارات وفقًا للتنسيق الذي اخترته.

تأكد من ضبط النطاق ومراجع الخلايا في الصيغ حسب الحاجة لتغطية جميع البيانات التي تريد مقارنتها.

قد يبدو التنسيق الشرطي بدائيًا بعض الشيء. إذا كنت تريد تحكمًا أفضل ، فقد يكون Power Query هو الحل! في القسم التالي ، سنتناول كيف يمكنك استخدام Power Query للعثور على التكرارات.

3. كيفية استخدام Power Query للبحث عن التكرارات عبر أوراق العمل

Power Query هي أداة لتحويل البيانات وإعدادها في Microsoft Excel. يعد تحديد القيم نفسها أحد مهام التحليل العديدة التي يمكنك إجراؤها باستخدام الأداة.

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

1. انقر بزر الماوس الأيمن فوق نطاق الخلايا.

2. اختر "الحصول على البيانات من الجدول / النطاق".

3. قم بتعديل اسم الجدول إلى شيء مناسب.

بعد استيراد كلتا الورقتين ، تكون المهمة الأولى هي دمج البيانات:

1. انتقل إلى علامة التبويب البيانات.

2. انقر فوق "الحصول على البيانات".

3. حدد "دمج الاستعلامات".

4. اختر "دمج" وحدد الجدولين.

5. انقر فوق العمودين الأساسيين.

6. اختر "Inner" باعتباره "Join Kind" وانقر على "موافق".

سيفتح محرر Power Query بالبيانات المدمجة من كلا الجدولين في ورقة Excel. سترى عمودين ، واحد من كل جدول. نظرًا لأنك مهتم فقط بالقيم المكررة ، يمكنك إزالة العمود الثاني.

يمكنك النقر فوق "إغلاق وتحميل" في محرر Power Query لتحميل التكرارات إلى ورقة عمل جديدة.

لاستكشاف المزيد من جوانب هذه الميزة القوية ، اتبع الأمثلة الواردة في مقالتنا حول كيفية استخدام Power Query في Excel .

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

4. أدوات ووظائف إضافية لتحديد التكرارات عبر أوراق العمل

يمكن للأدوات الخارجية والوظائف الإضافية أن تقدم وظائف متقدمة قد لا تكون متوفرة في ميزات Excel الأصلية. يمكن لهذه الأدوات تبسيط عملية مقارنة الأوراق بالتكرارات.

مقارنة جداول البيانات هي إحدى أدوات Microsoft التي تتيح لك مقارنة مصنفين جنبًا إلى جنب ، وإبراز الاختلافات وتحديد التكرارات بسهولة. يمكنك تنزيله من موقع Microsoft على الويب.

هناك العديد من الوظائف الإضافية التي يمكنك تثبيتها لأتمتة عملية البحث عن التكرارات. أحد الأمثلة هو " مزيل مكرر ". لتثبيت وظيفة إضافية:

1. انتقل إلى علامة التبويب إدراج.

2. انقر فوق "الحصول على الوظيفة الإضافية".

3. ابحث عن "مكرر".

4. انقر فوق "إضافة" على الأداة التي تختارها.

5. كيفية التحقق بصريًا من التكرارات في صفحتين

إذا فشل كل شيء آخر ، استخدم عينيك! يتيح لك مربع الحوار ترتيب Windows في Excel عرض أوراق عمل أو مصنفات متعددة جنبًا إلى جنب.

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

1. انقر فوق علامة التبويب "عرض" في شريط Excel.

2. انقر فوق "ترتيب الكل" في مجموعة "النافذة".

3. اختر خيار ترتيب مثل "عمودي" أو "أفقي".

سيؤدي هذا إلى عرض كلتا الورقتين جنبًا إلى جنب أو واحدة فوق الأخرى. يمكنك الآن مقارنة البيانات يدويًا في كل ورقة لتحديد التكرارات.

تحتاج إلى التمرير عبر البيانات وفحص كل قيمة بصريًا للعثور على التطابقات.

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

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

3 نصائح لإعداد أوراق عمل Excel الخاصة بك

قبل البدء في مقارنة أوراق متعددة ، تأكد من ترتيب أعمدة وصفوف مجموعات البيانات بشكل صحيح.

تأكد من أن كلا ورقتي Excel لهما نفس البنية ونفس أسماء الرؤوس. إذا لزم الأمر ، يمكنك إعادة ترتيب الأعمدة في كلتا الصفحتين لتتناسب مع بعضها البعض.

فيما يلي ثلاثة اقتراحات لضمان إجراء مقارنات دقيقة:

1. رتب بياناتك بنفس الترتيب في كلا الورقتين. هذا يجعل من السهل على وظائف Excel العمل بفعالية.

2. تطبيع البيانات الخاصة بك باستخدام التنسيق المتسق ، والأحرف الكبيرة ، وأنواع البيانات. سيمنع هذا الإدخالات غير المتطابقة بسبب الاختلافات الطفيفة.

3. قم بإزالة الصفوف أو الأعمدة الفارغة غير الضرورية ، لأنها قد تتداخل مع عملية المقارنة.

يمكنك تقييم مقدار الازدواجية في مجموعة البيانات عن طريق حساب القيم الفريدة. يستعرض هذا الفيديو عدة طرق:

كيفية التعامل مع الأخطاء والتناقضات

يمكن أن تؤثر التناقضات في بياناتك على عملية المقارنة. فيما يلي أربع نصائح لحل التناقضات:

1. تحقق من وجود اختلافات في أنواع البيانات ، مثل خلط النص والقيم الرقمية في نفس العمود.

2. تأكد من استخدام التنسيق المتسق للتواريخ والأرقام وأنواع البيانات الأخرى.

3. افحص بياناتك بحثًا عن إدخالات مفقودة أو غير صحيحة ، وقم بالتحديث إذا لزم الأمر.

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

افكار اخيرة

يعد العثور على التكرارات عبر ورقتي عمل Excel مهمة أساسية لإدارة البيانات وتحليلها ، مما يضمن تكامل البيانات ودقتها. يقدم Excel تقنيات متعددة لتحديد التكرارات ، ولكل منها مزاياها وقيودها.

يعتمد اختيار الطريقة على احتياجات المستخدم وحجم وتعقيد مجموعة البيانات والنتيجة المرجوة. بالنسبة لمجموعات البيانات الأصغر والمقارنات المباشرة ، قد يكون استخدام VLOOKUP أو COUNTIF أو التنسيق الشرطي كافيًا.

بالنسبة لمجموعات البيانات الأكبر أو عمليات تحويل البيانات الأكثر تعقيدًا ، تعد Power Query أداة قوية ومرنة يمكنها التعامل مع مجموعة كبيرة من مهام إعداد البيانات ، بما في ذلك البحث عن التكرارات.

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

ثق بنا ، كلما تحسنت في ذلك ، ستنقل مهام البيانات الخاصة بك وتثير إعجاب كل من حولك!