كيفية مقارنة عمودين في Excel وإزالة التكرارات (تمييز ، تلوين ، نقل)

ستستغرق قراءة هذه المقالة حوالي 10 دقائق. في الدقائق الخمس التالية ، يمكنك بسهولة مقارنة عمودين في Excel ومعرفة ما إذا كان هناك نسخ مكررة فيهما أو حذفهما أو تمييزهما بالألوان. لذا فإن الوقت قد حان!

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

تخيل أن لدينا عمودين بأسماء الأشخاص - 2 أسماء في كل عمود A و 3 أسماء في عمود B. تحتاج إلى مقارنة الأسماء في هذين العمودين والعثور على التكرارات. كما تفهم ، هذه بيانات وهمية ، مأخوذة فقط على سبيل المثال. في الجداول الحقيقية ، نتعامل مع آلاف أو حتى عشرات الآلاف من السجلات.

الخيار A: كلا العمودين على نفس الورقة. على سبيل المثال ، عمود A والعمود B.

الخيار B: الأعمدة على أوراق مختلفة. على سبيل المثال ، عمود A على الورقة Sheet2 والعمود A على الورقة Sheet3.

يحتوي Excel 2013 و 2010 و 2007 على أداة مضمنة إزالة التكرارات (إزالة التكرارات) لكنه عاجز في هذه الحالة لأنه لا يمكنه مقارنة البيانات في عمودين. علاوة على ذلك ، يمكنه فقط إزالة التكرارات. لا توجد خيارات أخرى مثل تمييز الألوان أو تغييرها. و نقطة!

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

• قارن بين عمودين وابحث عن التكرارات باستخدام الصيغ
  
  • الخيار أ: كلا العمودين على نفس الورقة
  • الخيار ب: الأعمدة موجودة في أوراق مختلفة أو في مصنفات مختلفة
• معالجة التكرارات التي تم العثور عليها
  
  • إظهار الصفوف المكررة فقط في العمود A
  • قم بتغيير اللون أو تمييز التكرارات التي تم العثور عليها
  • إزالة التكرارات من العمود الأول

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

الخيار أ: كلا العمودين على نفس الورقة

1. في أول خلية فارغة (في مثالنا ، هذه هي الخلية C1) ، نكتب الصيغة التالية:

   =IF(ISERROR(MATCH(A1,$B$1:$B$10000,0)),"Unique","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;$B$1:$B$10000;0));"Unique";"Duplicate")

   

   في صيغتنا A1 هذه هي الخلية الأولى في العمود الأول التي سنقارنها. 1 دولارات أمريكية и 10000 دولارات أمريكية هذه هي عناوين الخلايا الأولى والأخيرة من العمود الثاني ، والتي سنجري بها المقارنة. لاحظ المراجع المطلقة - أحرف العمود وأرقام الصفوف مسبوقة بعلامة الدولار ($). أستخدم المراجع المطلقة بحيث تظل عناوين الخلايا كما هي عند نسخ الصيغ.

   إذا كنت تريد البحث عن التكرارات في عمود B، قم بتغيير المراجع بحيث تبدو الصيغة كما يلي:

   =IF(ISERROR(MATCH(B1,$A$1:$A$10000,0)),"Unique","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(B1;$A$1:$A$10000;0));"Unique";"Duplicate")

   في حين أن "فقط"و"مكررة»يمكنك كتابة التسميات الخاصة بك ، على سبيل المثال ،«لم يتم العثور على"و"وجدت"، أو اترك فقط"مكررة'وأدخل حرف مسافة بدلاً من القيمة الثانية. في الحالة الأخيرة ، ستبقى الخلايا التي لم يتم العثور على نسخ مكررة لها فارغة ، وأعتقد أن تمثيل البيانات هذا هو الأكثر ملاءمة لمزيد من التحليل.

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

   

تلميح: في الجداول الكبيرة ، سيكون نسخ الصيغة أسرع إذا كنت تستخدم اختصارات لوحة المفاتيح. قم بتمييز خلية C1 والصحافة CTRL + C (لنسخ الصيغة إلى الحافظة) ، ثم انقر فوق السيطرة + التحول + النهاية (لتحديد جميع الخلايا غير الفارغة في العمود C) ثم اضغط أخيرًا CTRL + V (لإدراج الصيغة في جميع الخلايا المحددة).

3. رائع ، الآن يتم وضع علامة على جميع القيم المكررة على أنها "مكررة":

الخيار ب: يوجد عمودين على أوراق مختلفة (في مصنفات مختلفة)

1. في الخلية الأولى من العمود الفارغ الأول بورقة العمل Sheet2 (في حالتنا هو العمود B) أدخل الصيغة التالية:

   =IF(ISERROR(MATCH(A1,Sheet3!$A$1:$A$10000,0)),"","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;Лист3!$A$1:$A$10000;0));"";"Duplicate")

   هنا Sheet3 هو اسم الورقة التي يوجد عليها العمود الثاني ، و 1 دولار أسترالي: 10000 دولارًا أستراليًا هي عناوين الخلايا من الأول إلى الأخير في هذا العمود الثاني.

2. انسخ الصيغة إلى جميع الخلايا في عمود B (نفس الخيار أ).
3. نحصل على هذه النتيجة:

معالجة التكرارات التي تم العثور عليها

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

إظهار الصفوف المكررة فقط في العمود A

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

انقر بزر الماوس الأيمن واختر من قائمة السياق إدخال (إدراج):

أعط أسماء الأعمدة ، على سبيل المثال ، "الاسم"و"ينسخ؟»ثم افتح علامة التبويب البيانات (البيانات) والصحافة الفرز (منقي):

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

هذا كل شيء ، الآن ترى فقط تلك العناصر من العمود А، والتي يتم تكرارها في العمود В. لا يوجد سوى خليتين فقط من هذا القبيل في جدول التدريب الخاص بنا ، ولكن كما تعلمون ، سيكون هناك عدد أكبر بكثير منهم عمليًا.

لعرض كافة صفوف العمود مرة أخرى А، انقر فوق رمز الفلتر في العمود В، الذي يبدو الآن كقمع بسهم صغير ، وحدد تحديد الكل (اختر الكل). أو يمكنك أن تفعل الشيء نفسه من خلال الشريط بالنقر البيانات (بيانات)> حدد & تصفية (فرز وتصفية)> تراجع (مسح) كما هو موضح في الصورة أدناه:

قم بتغيير اللون أو تمييز التكرارات التي تم العثور عليها

إذا كانت الملاحظات "مكررة"لا يكفي لأغراضك وتريد تمييز الخلايا المكررة بلون خط مختلف أو لون تعبئة أو طريقة أخرى ...

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

الآن لن تفوتك بالتأكيد أي خلايا بها نسخ مكررة:

إزالة القيم المكررة من العمود الأول

قم بتصفية الجدول بحيث يتم عرض الخلايا ذات القيم المكررة فقط ، وحدد تلك الخلايا.

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

صحافة OKعندما يطلب منك Excel تأكيد رغبتك حقًا في حذف صف الورقة بالكامل ثم مسح عامل التصفية. كما ترى ، تبقى الصفوف ذات القيم الفريدة فقط:

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

1. قم بتصفية الجدول لإظهار القيم المكررة فقط وتحديد تلك الخلايا. انقر بزر الماوس الأيمن عليها واختر من قائمة السياق محتويات واضحة (محتويات واضحة).
2. تنظيف المرشح.
3. حدد كل الخلايا في عمود А، بدءًا من الخلية A1 وصولا إلى أسفل تحتوي على البيانات.
4. انقر على البيانات (البيانات) والصحافة ترتيب من الألف إلى الياء (فرز من الألف إلى الياء). في مربع الحوار الذي يفتح ، حدد تواصل مع التحديد الحالي (قم بالفرز ضمن التحديد المحدد) وانقر فوق الزر اسود (فرز):
5. احذف العمود الذي يحتوي على الصيغة ، فلن تحتاج إليه بعد الآن ، من الآن فصاعدًا لديك قيم فريدة فقط.
6. هذا كل شيء ، الآن العمود А يحتوي فقط على بيانات فريدة غير موجودة في العمود В:

كما ترى ، فإن إزالة التكرارات من عمودين في Excel باستخدام الصيغ ليس بهذه الصعوبة.