مقارنة عمودين للعثور على التكرارات في Excel (الدليل الكامل)
يعد تحديد القيم المكررة عبر عمودين في Excel مهمة شائعة في تحليل البيانات. ويساعد ذلك في تحديد أخطاء إدخال البيانات أو السجلات المكررة أو لأغراض تنظيف البيانات. سترشدك هذه المقالة حول كيفية تحديد التكرارات أو التطابقات بكفاءة ودقة في عمودين.
قارن بين عمودين للعثور على قيم مكررة
- قم بتمييز التكرارات في عمودين باستخدام التنسيق الشرطي
- حدد التكرارات وأبرزها في عمودين باستخدام أداة قوية – Kutools
- ابحث عن التكرارات واستخرجها في عمودين باستخدام الصيغ
- حدد التكرارات في عمودين برمز VBA
قارن بين عمودين للعثور على قيم مكررة
للعثور على قيم مكررة بين عمودين، هناك عدة طرق متاحة وفقًا لمتطلباتك، مثل تمييز التكرارات للحصول على نظرة عامة مرئية أو استخراجها لإجراء تحليل متعمق. سنقدم في هذا القسم بعض الحيل السريعة لإنجاز هذه المهمة في برنامج Excel.
قم بتمييز التكرارات في عمودين باستخدام التنسيق الشرطي
يعد تمييز التكرارات عبر عمودين في Excel طريقة فعالة لتحديد البيانات المتكررة، خاصة ضمن مجموعات البيانات الكبيرة حيث تكون المراجعة اليدوية غير عملية. في هذه الحالة، يعد التنسيق الشرطي ميزة مفيدة لحل هذه المهمة.
الخطوة 1: حدد البيانات الموجودة في العمود الذي تريد تمييز التكرارات منه
في هذا المثال، سأختار A2:A10، انظر لقطة الشاشة:
الخطوة 2: تطبيق ميزة التنسيق الشرطي
- انقر الصفحة الرئيسية > تنسيق مشروط > قانون جديد، انظر لقطة الشاشة:
- في مجلة قاعدة تنسيق جديدة مربع الحوار ، يرجى القيام بالعمليات التالية:
- 2.1 اختر استخدم صيغة لتحديد الخلايا المطلوب تنسيقها من حدد نوع القاعدة مربع القائمة
- 2.2 اكتب الصيغة التالية في تنسيق القيم حيث تكون هذه الصيغة صحيحة مربع الكتابة؛
=COUNTIF($B$2:$B$10, A2)>0
- ملاحظات: في الصيغة أعلاه ، B2: B10 يمثل قائمة البيانات التي تريد مقارنتها، A2 هي الخلية الأولى في العمود الذي تريد تمييز التكرارات منه. تتحقق هذه الصيغة من العثور على القيمة الموجودة في الخلية A2 في أي مكان في العمود B. قم بتعديل مراجع الخلايا لتناسب بياناتك.
- 2.3 ثم انقر فوق شكل .
- في المنبثقة تنسيق الخلايا في مربع الحوار، حدد لونًا واحدًا تريد إبراز العناصر المكررة فيه. وانقر OK.
- عندما يعود قاعدة تنسيق جديدة مربع الحوار، انقر فوق OK .
النتيجة:
الآن، يتم الآن تمييز القيم المكررة في كلا العمودين A وB في العمود A كما هو موضح في لقطة الشاشة التالية:
- القاعدة المكررة في تنسيق مشروط is ليست حساسة لحالة الأحرف. لذلك، سيتم وضع علامة على كل من Apple وApple على أنهما مكررتان.
- إذا كنت تريد تمييز التكرارات من العمود B، فأنت بحاجة فقط إلى تحديد العمود B أولاً، ثم قم بتطبيق الصيغة التالية على تنسيق مشروط:
=COUNTIF($A$2:$A$10, B2)>0
حدد التكرارات وأبرزها في عمودين باستخدام أداة قوية – Kutools
في بعض الأحيان، قد لا تحتاج إلى تمييز التكرارات فحسب، بل تحتاج أيضًا إلى تحديدها لنسخها ولصقها في موقع آخر في المصنف الخاص بك. في مثل هذه الحالات، كوتولس ل إكسيلالصورة حدد نفس الخلايا وخلايا مختلفة الميزة هي الاختيار المثالي. يمكنه تحديد القيم المكررة أو الفريدة من خلال تمييز الخلايا التي تحتاجها وتحديدها.
انقر كوتولس > أختار > حدد نفس الخلايا وخلايا مختلفة، في حدد نفس الخلايا وخلايا مختلفة مربع الحوار ، يرجى القيام بالعمليات التالية:
- حدد البيانات المصدر والبيانات المقارنة في ابحث عن القيم في و وفقًا مربعات بشكل منفصل
- أختار كل صف تحت مرتكز على قسم.
- اختار نفس القيم من قسم.
- حدد لون الخلفية لتمييز القيم المكررة ضمن معالجة النتائج قسم.
- أخيرًا ، انقر فوق OK .
النتيجة:
الآن، يتم تمييز القيم المكررة في كلا العمودين A وB وتحديدها في العمود A، وهي جاهزة للنسخ واللصق في أي خلايا مطلوبة. انظر لقطة الشاشة:
- هذه الميزة يدعم حساس لحالة الأحرف المقارنة في حين موقوتة حساسية الموضوع مربع الاختيار في حدد نفس الخلايا وخلايا مختلفة حوار ؛
- إذا كنت تريد تحديد التكرارات من العمود B، فأنت بحاجة فقط إلى تبديل العمودين المحددين في ابحث عن القيم في و وفقًا مربعات حدد نفس الخلايا وخلايا مختلفة صندوق المحادثة؛
- لتطبيق هذه الميزة ، من فضلك قم بتنزيل وتثبيت Kutools لبرنامج Excel لأول مرة.
البحث عن التكرارات واستخراجها في عمودين باستخدام الصيغة
للعثور على التكرارات واستخراجها بين عمودين، يمكنك استخدام صيغة لتحديد التكرارات وسحبها.
يرجى نسخ الصيغة التالية ولصقها في خلية فارغة حيث تريد وضع النتيجة، ثم اسحب مقبض التعبئة إلى أسفل العمود لتطبيق هذه الصيغة على الخلايا الأخرى.
=IF(ISERROR(MATCH(A2,$B$2:$B$10,0)),"",A2)
ملاحظات: في الصيغة أعلاه ، A2 هي الخلية الأولى من العمود الذي تريد البحث عن التكرارات منه؛ B2: B10 يمثل قائمة البيانات التي تريد المقارنة بها.
النتيجة:
كما ترى، إذا كانت البيانات الموجودة في العمود A موجودة في العمود B، فسيتم عرض القيمة؛ وإلا، سيتم ترك الخلايا فارغة.
حدد التكرارات في عمودين برمز VBA
سيرشدك هذا القسم خلال خطوات إنشاء رمز VBA الذي يحدد ويحدد القيم المكررة بين عمودين.
الخطوة 1: افتح محرر وحدة VBA وانسخ الكود
- صحافة ALT + F11 مفاتيح لفتح ميكروسوفت فيسوال باسيك للتطبيقات نافذة.
- في النافذة المفتوحة، انقر فوق إدراج > وحدة لإنشاء وحدة فارغة جديدة.
- ثم انسخ والصق الكود أدناه في الوحدة الفارغة.
رمز VBA: ابحث عن القيم المكررة بين عمودين وحدِّدهاSub Compare() 'Update by Extendoffice Dim Range1 As Range, Range2 As Range, Rng1 As Range, Rng2 As Range, outRng As Range xTitleId = "KutoolsforExcel" On Error Resume Next Set Range1 = Application.Selection Set Range1 = Application.InputBox("Range1 :", xTitleId, Range1.Address, Type:=8) Set Range2 = Application.InputBox("Range2:", xTitleId, Type:=8) Application.ScreenUpdating = False For Each Rng1 In Range1 xValue = Rng1.Value For Each Rng2 In Range2 If xValue = Rng2.Value Then If outRng Is Nothing Then Set outRng = Rng1 Else Set outRng = Application.Union(outRng, Rng1) End If End If Next Next outRng.Select Application.ScreenUpdating = True End Sub
الخطوة 2: تنفيذ كود VBA هذا
- بعد لصق هذا الرمز ، الرجاء الضغط F5 المفتاح لتشغيل هذا الكود في مربع المطالبة الأول، حدد قائمة البيانات التي تريد تحديد التكرارات منها. ثم انقر فوق OK.
- في مربع المطالبة الثاني، حدد قائمة البيانات التي تريد مقارنتها، ثم انقر فوق OK، انظر لقطة الشاشة:
النتيجة:
الآن، تم تحديد القيم المكررة من العمودين A وB في العمود A، مما يسمح لك بملء الخلايا بالألوان أو نسخها ولصقها حسب الحاجة.
- رمز VBA هذا هو حساسية الموضوع;
- إذا كنت تريد تحديد التكرارات من العمود B، فأنت تحتاج فقط إلى تبديل العمودين المحددين عند تحديد نطاق البيانات.
قارن بين عمودين للتطابقات صفًا تلو الآخر
في Excel، غالبًا ما تكون مقارنة عمودين صفًا تلو الآخر ضرورية للتحقق من التطابقات، مما يساعد في مهام مثل فحص السجلات أو تحليل اتجاهات البيانات. لدى Excel طرق مختلفة للقيام بذلك، بدءًا من الصيغ السهلة وحتى الميزات الخاصة، بحيث يمكنك اختيار أفضل ما يناسب احتياجات بياناتك. دعونا نلقي نظرة على بعض الطرق المباشرة لإنجاز هذه المهمة بفعالية.
قارن بين عمودين في نفس الصف باستخدام الصيغ
توفر صيغ Excel أسلوبًا مباشرًا وفعالاً لمقارنة البيانات عبر الأعمدة. وإليك كيف يمكنك استخدامها. لنفترض أن لديك بيانات في العمود A والعمود B، للتحقق من تطابق البيانات الموجودة في العمودين، قد تساعدك الصيغ التالية:
- استخدام عامل التشغيل يساوي (=): أبسط طريقة لمقارنة خليتين
- وظيفة IF: لجعل المقارنة الخاصة بك أكثر إفادة
- الوظيفة الدقيقة: مقارنة حساسة لحالة الأحرف
استخدام عامل التشغيل يساوي (=):
الرجاء إدخال أو نسخ الصيغة التالية، اضغط أدخل اضغط على المفتاح ثم اسحب مقبض التعبئة لأسفل للحصول على كافة النتائج. سيتم إرجاع TRUE إذا كانت القيم الموجودة في نفس الصف من العمودين A وB متطابقة، وإرجاع FALSE إذا لم تكن متطابقة. انظر لقطة الشاشة:
=A2=B2
إذا كانت الوظيفة:
إذا كنت تريد أن تجعل المقارنة أكثر إفادة، يمكنك استخدام إذا وظيفة لعرض الرسائل المخصصة.
الرجاء إدخال أو نسخ الصيغة أدناه، اضغط أدخل اضغط على المفتاح ثم اسحب مقبض التعبئة لأسفل للحصول على كافة النتائج. سيتم إرجاع "مطابقة" عندما تكون القيم متماثلة و"لا يوجد تطابق" عندما تكون مختلفة. انظر لقطة الشاشة:
=IF(A2=B2, "Match", "No Match")
الوظيفة الدقيقة:
إذا كنت بحاجة إلى مقارنة حساسة لحالة الأحرف، فإن وظيفة دقيقة هي الطريق للذهاب.
الرجاء إدخال أو نسخ الصيغة التالية، اضغط أدخل اضغط على المفتاح ثم اسحب مقبض التعبئة لأسفل للحصول على كافة النتائج. سيُرجع مطابقة عندما تكون القيم متطابقة تمامًا ولا يوجد تطابق عندما تكون مختلفة. انظر لقطة الشاشة:
=IF(EXACT(A2,B2), "Match", "No match")
حدد وأبرز التطابقات في نفس الصف باستخدام أداة مفيدة - Kutools
إذا كنت بحاجة إلى تحديد وتظليل التطابقات بين عمودين صفًا تلو الآخر بدلاً من الحصول على النتيجة في عمود منفصل، فستكون ميزة مقارنة الخلايا في Kutools for Excel خيارًا ممتازًا. فهو يمكّنك من تحديد لون التعبئة وتطبيقه بسرعة على الخلايا التي تتطابق أو تختلف في القيمة داخل كل صف.
انقر كوتولس > قارن الخلايا، في قارن الخلايا مربع الحوار ، يرجى القيام بالعمليات التالية:
- حدد البيانات من العمودين في ابحث عن القيم في و وفقًا مربعات بشكل منفصل
- أختار نفس الخلايا تحت قسم.
- حدد لون الخلفية لتمييز التطابقات الموجودة أسفل معالجة النتائج قسم.
- أخيرًا ، انقر فوق OK .
النتيجة:
الآن، يتم تمييز المطابقات الموجودة في نفس الصف وتحديدها في العمود A، مما يجعلها متاحة لك للنسخ واللصق في أي خلايا مطلوبة. انظر لقطة الشاشة:
- هذه الميزة يدعم حساس لحالة الأحرف المقارنة إذا قمت بفحص حساسية الموضوع الخيار في قارن الخلايا صندوق المحادثة؛
- إذا كنت تريد تحديد التطابقات من العمود B، فأنت تحتاج فقط إلى تبديل العمودين المحددين في ابحث عن القيم في و وفقًا مربعات قارن الخلايا صندوق المحادثة؛
- لتطبيق هذه الميزة ، من فضلك قم بتنزيل وتثبيت Kutools لبرنامج Excel لأول مرة.
قارن بين عمودين وقم بتمييز التطابقات في نفس الصف
يمكن إجراء مقارنة عمودين وتمييز التطابقات في نفس الصف بكفاءة باستخدام التنسيق الشرطي في Excel. فيما يلي دليل لتحديد تطابقات الصفوف وإبرازها:
الخطوة 1: حدد نطاق البيانات
حدد نطاق البيانات الذي تريد تمييز تطابقات الصف فيه.
الخطوة 2: تطبيق ميزة التنسيق الشرطي
- انقر الصفحة الرئيسية > تنسيق مشروط > قانون جديد. في قاعدة تنسيق جديدة مربع الحوار ، يرجى القيام بالعمليات التالية:
- 2.1 اختر استخدم صيغة لتحديد الخلايا المطلوب تنسيقها من حدد نوع القاعدة مربع القائمة
- 2.2 اكتب الصيغة أدناه في تنسيق القيم حيث تكون هذه الصيغة صحيحة مربع الكتابة؛
=$B2=$A2
- 2.3 ثم انقر فوق شكل .
- في المنبثقة تنسيق الخلايا في مربع الحوار، حدد لونًا واحدًا تريد إبراز العناصر المكررة فيه. وانقر OK.
- عندما يعود قاعدة تنسيق جديدة مربع الحوار، انقر فوق OK .
النتيجة:
الآن، يتم تمييز القيم المطابقة في نفس الصف مرة واحدة، انظر لقطة الشاشة:
- الصيغة في التنسيق الشرطي هي ليست حساسة لحالة الأحرف.
- إذا كنت تهدف إلى تمييز الخلايا ذات القيم المختلفة، فقم بتطبيق الصيغة التالية:
=$B2<>$A2
قارن بين عمودين واسحب البيانات المطابقة
عندما تتعامل مع مجموعتين من البيانات في Excel وتحتاج إلى العثور على عناصر مشتركة من قائمة واحدة في القائمة الأخرى، فإن صيغ البحث هي الحل الأمثل لاسترداد هذه المطابقات.
في Excel، إذا كانت لديك قائمة بالفواكه في العمود A وأرقام مبيعاتها في العمود B، فأنت تريد الآن مطابقة هذه مع مجموعة مختارة من الفواكه في العمود D للعثور على مبيعاتها المقابلة. كيف يمكنك إرجاع القيم النسبية من العمود B في Excel؟
يرجى تطبيق أي من الصيغ التالية التي تحتاجها، ثم اسحب مقبض التعبئة لأسفل لتطبيق هذه الصيغة على بقية الخلايا.
- جميع إصدارات الإكسل:
=VLOOKUP(D2, $A$2:$B$6, 2, FALSE)
- إكسل 365 وإكسل 2021:
=XLOOKUP(D2, $A$2:$A$6, $B$2:$B$6)
النتيجة:
سيتم عرض جميع القيم المقابلة إذا تم العثور على تطابق، وإلا فسيتم إرجاع الخطأ #N/A، انظر لقطة الشاشة:
- باستخدام الصيغ المذكورة أعلاه، إذا كانت هناك ثمار في العمود D ليس لها تطابق في العمود A، فسوف تُرجع خطأ. لتسهيل فهم هذه الأخطاء، يمكنك تغليف الصيغة باستخدام الدالة IFERROR:
- جميع إصدارات الإكسل:
=IFERROR(VLOOKUP(D2,$A$2:$B$10,2,FALSE), "No match found")
- إكسل 365 وإكسل 2021:
=IFERROR(XLOOKUP(D2, $A$2:$A$10, $B$2:$B$10),"No match found")
- جميع إصدارات الإكسل:
- لأولئك الذين يحرصون على صيغ البحث المتقدمة، كوتولس ل إكسيل يوفر مجموعة رائعة من صيغ البحث المتقدمة التي تنقل وظيفة VLOOKUP التقليدية إلى آفاق جديدة، مما يمنحك دقة وكفاءة لا مثيل لهما في مهام إدارة البيانات الخاصة بك.
كوتولس ل إكسيل تفتخر بمجموعة تضم أكثر من 300 أداة ملائمة مصممة لتعزيز إنتاجيتك. استمتع بالقوة الكاملة من خلال نسخة تجريبية مجانية مدتها 30 يومًا وارفع مستوى جداول بياناتك اليوم! احصل عليها الآن!
مقالات ذات صلة:
- البحث عن الصفوف المكررة وتمييزها في نطاق ما
- في بعض الأحيان ، قد تكون هناك بعض السجلات المكررة في نطاق بياناتك لورقة العمل ، والآن تريد البحث عن الصفوف المكررة أو إبرازها في النطاق كما هو موضح في لقطات الشاشة التالية. بالطبع يمكنك أن تجدهم واحدًا تلو الآخر عن طريق التحقق من الصفوف. لكن هذا ليس اختيارًا جيدًا إذا كان هناك مئات الصفوف. هنا سأتحدث عن بعض الطرق المفيدة لك للتعامل مع هذه المهمة.
- قم بتمييز القيم المكررة بألوان مختلفة
- في Excel ، يمكننا بسهولة تمييز القيم المكررة في عمود بلون واحد باستخدام التنسيق الشرطي ، ولكن في بعض الأحيان ، نحتاج إلى تمييز القيم المكررة بألوان مختلفة للتعرف على التكرارات بسرعة وسهولة كما هو موضح في لقطة الشاشة التالية. كيف يمكنك حل هذه المهمة في Excel؟
- البحث ، تمييز ، تصفية ، العد ، حذف التكرارات في Excel
- في Excel ، تحدث البيانات المكررة مرة تلو الأخرى عندما نقوم بتسجيل البيانات يدويًا أو نسخ البيانات من مصادر أخرى أو لأسباب أخرى. في بعض الأحيان ، تكون التكرارات ضرورية ومفيدة. ومع ذلك ، في بعض الأحيان تؤدي القيم المكررة إلى أخطاء أو سوء فهم. هنا ، ستقدم هذه المقالة طرقًا لتحديد التكرارات وتمييزها وتصفيتها وعدها وحذفها عن طريق الصيغ وقواعد التنسيق الشرطي والوظائف الإضافية لجهات خارجية وما إلى ذلك بسرعة في Excel.
- إزالة التكرارات واستبدالها بخلايا فارغة
- عادةً عند تطبيق أمر إزالة التكرارات في Excel ، فإنه يزيل الصفوف المكررة بأكملها. لكن في بعض الأحيان ، تريد أن تحل الخلايا الفارغة محل القيم المكررة ، في هذه الحالة ، لن يعمل أمر إزالة التكرار. ستوجهك هذه المقالة إلى إزالة التكرارات واستبدالها بخلايا فارغة في Excel.
أفضل أدوات إنتاجية المكتب
عزز مهاراتك في Excel باستخدام Kutools for Excel، واختبر كفاءة لم يسبق لها مثيل. يقدم Kutools for Excel أكثر من 300 ميزة متقدمة لتعزيز الإنتاجية وتوفير الوقت. انقر هنا للحصول على الميزة التي تحتاجها أكثر...
يجلب Office Tab الواجهة المبوبة إلى Office ، ويجعل عملك أسهل بكثير
- تمكين التحرير والقراءة المبوبة في Word و Excel و PowerPointوالناشر والوصول و Visio والمشروع.
- فتح وإنشاء مستندات متعددة في علامات تبويب جديدة من نفس النافذة ، بدلاً من النوافذ الجديدة.
- يزيد من إنتاجيتك بنسبة 50٪ ، ويقلل مئات النقرات بالماوس كل يوم!
جدول المحتويات
- قارن بين عمودين للعثور على قيم مكررة
- مع التنسيق الشرطي
- باستخدام أداة قوية – Kutools
- مع الصيغ
- مع كود فبا
- قارن بين عمودين للتطابقات صفًا تلو الآخر
- مع الصيغ
- مع أداة مفيدة - Kutools
- مع التنسيق الشرطي
- قارن بين عمودين واسحب البيانات المطابقة
- مقالات ذات صلة
- أفضل أدوات إنتاجية المكتب
- التعليقات