يعلمك هذا المقال كيفية استخدام التنسيق الشرطي لمقارنة جميع الخلايا في قائمتين منفصلتين في جدول بيانات Excel ، وتمييز الخلايا التي تظهر في كلتا القائمتين. لا تتوفر هذه الميزة إلا في إصدار سطح المكتب من Excel ، ولا يدعمها تطبيق الأجهزة المحمولة.
خطوات
الطريقة 1 من 2: استخدام صيغة CountIf
الخطوة الأولى: افتح جدول بيانات Excel الذي تريد تحريره
ابحث عن ملف جدول البيانات مع القوائم التي تريد مقارنتها ، وانقر نقرًا مزدوجًا على الملف لفتحه في Microsoft Excel.
الخطوة 2. حدد قائمتك الأولى
انقر فوق الخلية الأولى في قائمتك الأولى ، واسحب الماوس طوال الطريق إلى آخر خلية في القائمة لتحديد نطاق البيانات بالكامل.
الخطوة الثالثة. انقر فوق علامة التبويب الصيغ في شريط الأدوات
يمكنك العثور على علامة التبويب هذه أعلى شريط الأدوات أعلى جدول البيانات. سيفتح أدوات الصيغة الخاصة بك على شريط شريط الأدوات.
الخطوة الرابعة. انقر على "تحديد الاسم" على شريط الأدوات
يمكنك العثور على هذا الخيار في منتصف شريط "الصيغ". سيفتح نافذة منبثقة جديدة ، ويسمح لك بتسمية قائمتك.
الخطوة 5. اكتب List1 في حقل الاسم
انقر فوق حقل النص أعلى النافذة المنبثقة ، وأدخل اسم القائمة هنا.
- يمكنك لاحقًا استخدام هذا الاسم لإدراج قائمتك في صيغة المقارنة.
- بدلاً من ذلك ، يمكنك تسمية قائمتك باسم مختلف هنا. على سبيل المثال ، إذا كانت هذه قائمة بالمواقع ، فيمكنك تسميتها "المواقع 1" أو "قائمة الموقع".
الخطوة 6. انقر فوق "موافق" في النافذة المنبثقة
سيؤكد هذا الإجراء الخاص بك ، ويسمي قائمتك.
الخطوة 7. قم بتسمية قائمتك الثانية باسم List2
اتبع نفس الخطوات المتبعة في القائمة الأولى ، وقم بتسمية القائمة الثانية الخاصة بك. سيسمح لك ذلك باستخدام هذه القائمة الثانية بسرعة في صيغة المقارنة لاحقًا.
يمكنك إعطاء القائمة أي اسم تريده. تأكد من تذكر أو تدوين الاسم الذي تطلقه على كل قائمة من قوائمك هنا
الخطوة 8. حدد قائمتك الأولى
انقر فوق الخلية الأولى في القائمة الأولى ، واسحب لأسفل لتحديد نطاق البيانات بالكامل.
تأكد من تحديد قائمتك الأولى قبل البدء في إعداد التنسيق الشرطي
الخطوة 9. انقر فوق علامة التبويب الصفحة الرئيسية على شريط الأدوات
هذه هي علامة التبويب الأولى في الزاوية العلوية اليسرى من شريط شريط الأدوات. سيفتح أدوات جدول البيانات الأساسية الخاصة بك على شريط الأدوات.
الخطوة 10. انقر فوق "تنسيق شرطي" على شريط الأدوات
يشبه هذا الخيار رمز جدول بيانات صغير مع تمييز بعض الخلايا باللونين الأحمر والأزرق. سيفتح قائمة منسدلة بجميع خيارات التنسيق الخاصة بك.
الخطوة 11. انقر فوق قاعدة جديدة في القائمة المنسدلة
سيفتح نافذة منبثقة جديدة ، ويسمح لك بإعداد قاعدة تنسيق جديدة للنطاق المحدد يدويًا.
الخطوة 12. حدد الخيار "استخدام صيغة لتحديد الخلايا المطلوب تنسيقها"
سيسمح لك هذا الخيار بكتابة صيغة تنسيق يدويًا لمقارنة قائمتين.
- تشغيل شبابيك ، ستجده في أسفل قائمة القواعد في مربع "تحديد نوع القاعدة".
- تشغيل ماك ، تحديد كلاسيك في القائمة المنسدلة "النمط" أعلى النافذة المنبثقة. بعد ذلك ، ابحث عن هذا الخيار في القائمة المنسدلة الثانية أسفل قائمة النمط.
الخطوة 13. انقر فوق حقل الصيغة في النافذة المنبثقة
يمكنك إدخال أي صيغة Excel صالحة هنا لإعداد قاعدة تنسيق شرطي.
الخطوة 14. اكتب = countif (List2، A1) = 1 في شريط الصيغة
ستفحص هذه الصيغة قائمتين ، وتميز جميع الخلايا الموجودة في قائمتك الأولى التي تظهر أيضًا في القائمة الثانية.
- استبدل A1 في الصيغة برقم الخلية الأولى في قائمتك الأولى.
- على سبيل المثال ، إذا كانت الخلية الأولى من قائمتك الأولى هي الخلية D5 ، فستبدو الصيغة الخاصة بك مثل = countif (List2، D5) = 1.
- إذا أعطيت اسمًا مختلفًا لقائمتك الثانية ، فتأكد من استبدال List2 في الصيغة بالاسم الفعلي لقائمتك.
- بدلاً من ذلك ، قم بتغيير الصيغة إلى = countif (List2 ، A1) = 0 إذا كنت تريد وضع علامة على الخلايا التي لاتفعل تظهر في القائمة الثانية.
الخطوة 15. اكتب = countif (List1، B1) = 1 في شريط الصيغة (اختياري)
إذا كنت تريد البحث عن الخلايا الموجودة في ملف ثانيا القائمة التي تظهر أيضًا في القائمة الأولى ، استخدم هذه الصيغة بدلاً من الأولى.
استبدل List1 باسم القائمة الأولى ، واستبدل B1 بالخلية الأولى من القائمة الثانية
الخطوة 16. حدد تنسيقًا مخصصًا لتمييز الخلايا (اختياري)
يمكنك تحديد لون تعبئة خلفية مخصص وأنماط خطوط مختلفة لتمييز الخلايا التي تعثر عليها الصيغة.
- تشغيل شبابيك ، انقر على صيغة الزر الموجود أسفل يمين النافذة المنبثقة. يمكنك تحديد لون الخلفية في علامة التبويب "تعبئة" وأنماط الخطوط في علامة التبويب "الخط".
- تشغيل ماك ، حدد تنسيقًا مُعدًا مسبقًا في القائمة المنسدلة "تنسيق مع" في الجزء السفلي. يمكنك أيضًا الاختيار تنسيق مخصص هنا لتحديد تعبئة الخلفية وأنماط الخط يدويًا.
الخطوة 17. انقر فوق "موافق" في النافذة المنبثقة
هذا سوف يؤكد ويطبق صيغة المقارنة الخاصة بك. سيتم تمييز جميع الخلايا الموجودة في قائمتك الأولى التي تظهر أيضًا في القائمة الثانية باللون والخط الذي اخترته.
- على سبيل المثال ، إذا حددت تعبئة باللون الأحمر الفاتح بنص أحمر داكن ، فستتحول جميع الخلايا المتكررة إلى هذا اللون في قائمتك الأولى.
- إذا كنت تستخدم الصيغة الثانية أعلاه ، فإن التنسيق الشرطي سيميز الخلايا المتكررة في قائمتك الثانية بدلاً من القائمة الأولى.
الطريقة 2 من 2: استخدام صيغة VLookup
الخطوة 1. افتح جدول بيانات Excel الخاص بك
ابحث عن ملف Excel مع القوائم التي تريد مقارنتها ، وانقر نقرًا مزدوجًا على اسم الملف أو الرمز لفتح جدول البيانات في Microsoft Excel.
الخطوة 2. انقر فوق الخلية الفارغة بجوار العنصر الأول في القائمة الثانية
ابحث عن قائمتك الثانية في جدول البيانات ، وانقر على الخلية الفارغة بجوار عنصر القائمة الأول في الأعلى.
- يمكنك إدخال صيغة VLookup الخاصة بك هنا.
- بدلاً من ذلك ، يمكنك تحديد أي خلية فارغة في جدول البيانات الخاص بك. ستجعل هذه الخلية أكثر ملاءمة لك لرؤية المقارنة بجوار قائمتك الثانية.
الخطوة 3. اكتب = vlookup (في الخلية الفارغة
ستسمح لك صيغة VLookup بمقارنة جميع العناصر في قائمتين منفصلتين ، ومعرفة ما إذا كانت القيمة هي قيمة مكررة أم جديدة.
لا تغلق قوس الصيغة حتى تكتمل الصيغة
الخطوة 4. حدد العنصر الأول في قائمتك الثانية
بدون إغلاق قوس الصيغة ، انقر فوق العنصر الأول في القائمة الثانية. سيؤدي هذا إلى إدراج الخلية الأولى في القائمة الثانية في الصيغة.
الخطوة 5. اكتب فاصلة في الصيغة
بعد تحديد الخلية الأولى في قائمتك الثانية ، اكتب فاصلة في الصيغة. ستتمكن من تحديد نطاق المقارنة الخاص بك بعد ذلك.
الخطوة 6. اضغط باستمرار وحدد القائمة الأولى بأكملها
سيؤدي هذا إلى إدراج نطاق خلايا قائمتك الأولى في الجزء الثاني من صيغة VLookup.
سيسمح لك هذا بالبحث في القائمة الأولى عن العنصر المحدد من قائمتك الثانية (العنصر الأول في أعلى القائمة الثانية) ، والعودة إذا كانت قيمة متكررة أو جديدة
الخطوة 7. اكتب فاصلة في الصيغة
سيؤدي هذا إلى قفل نطاق المقارنة في صيغتك.
الخطوة 8. اكتب 1 في الصيغة بعد الفاصلة
يمثل هذا الرقم رقم فهرس العمود الخاص بك. سيطالبك صيغة VLookup بالبحث في عمود القائمة الفعلي بدلاً من عمود مختلف بجواره.
إذا كنت تريد أن تقوم الصيغة بإرجاع القيمة من العمود الموجود بجوار القائمة الأولى مباشرةً ، فاكتب 2 هنا
الخطوة 9. اكتب فاصلة في الصيغة
سيؤدي هذا إلى قفل رقم فهرس العمود (1) في صيغة VLookup.
الخطوة 10. اكتب FALSE في الصيغة
سيبحث هذا في القائمة عن تطابق تام لعنصر البحث المحدد (العنصر الأول في أعلى القائمة الثانية) بدلاً من التطابقات التقريبية.
- بدلاً من FALSE ، يمكنك استخدام 0 ، إنه نفس الشيء تمامًا.
- بدلاً من ذلك ، يمكنك كتابة TRUE أو 1 إذا كنت تريد البحث عن تطابق تقريبي.
الخطوة 11. اكتب) في النهاية لإغلاق الصيغة
يمكنك الآن تشغيل المعادلة الخاصة بك ، ومعرفة ما إذا كان عنصر البحث المحدد في قائمتك الثانية هو قيمة مكررة أم جديدة.
على سبيل المثال ، إذا كانت قائمتك الثانية تبدأ من B1 ، وقائمتك الأولى تنتقل من الخلايا A1 إلى A5 ، ستبدو الصيغة الخاصة بك مثل = vlookup (B1، $ A $ 1: $ A $ 5، 1، false).
الخطوة 12. اضغط على ↵ Enter أو ⏎ العودة على لوحة المفاتيح الخاصة بك.
سيؤدي هذا إلى تشغيل الصيغة ، والبحث في قائمتك الأولى عن العنصر الأول من القائمة الثانية.
- إذا كانت هذه قيمة مكررة ، فسترى نفس القيمة مطبوعة مرة أخرى في خلية الصيغة.
- إذا كانت هذه قيمة جديدة ، فسترى " # لا ينطبق"مطبوعة هنا.
- على سبيل المثال ، إذا كنت تبحث في القائمة الأولى عن "John" ، وترى الآن "John" في خلية الصيغة ، فهي قيمة مكررة تظهر في كلتا القائمتين. إذا رأيت "# N / A" ، فهي قيمة جديدة في القائمة الثانية.
الخطوة 13. حدد خلية الصيغة الخاصة بك
بعد تشغيل الصيغة ورؤية النتائج الخاصة بك لعنصر القائمة الأول ، انقر فوق خلية الصيغة لتحديدها.
الخطوة 14. انقر واسحب النقطة الخضراء أسفل يمين الخلية
سيؤدي هذا إلى توسيع خلية الصيغة الخاصة بك على طول القائمة ، وتطبيق الصيغة على كل عنصر قائمة في القائمة الثانية.
- بهذه الطريقة يمكنك مقارنة كل عنصر في قائمتك الثانية بالقائمة الأولى بأكملها.
- سيؤدي هذا إلى البحث في قائمتك الأولى عن كل عنصر في قائمتك الثانية بشكل فردي ، وإظهار النتيجة بجانب كل خلية على حدة.
- إذا كنت تريد رؤية علامة مختلفة للقيم الجديدة بدلاً من "# N / A" ، فاستخدم هذه الصيغة: = iferror (vlookup (B1، $ A $ 1: $ A $ 5، 1، false)، "New Value"). سيؤدي هذا إلى طباعة "قيمة جديدة" للقيم الجديدة بدلاً من "# N / A".
