كيفية مقارنة قائمتين في Excel

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

مقارنة قائمتين في Excel

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

صياغة المشكلة

لذلك نحن بحاجة إلى مقارنة هذه الأعمدة. تكون إجراءات المقارنة بين وثيقتين كالتالي:

1. إذا كانت الخلايا الفريدة لكل من هذه القوائم هي نفسها ، وكان العدد الإجمالي للخلايا الفريدة هو نفسه ، والخلايا متماثلة ، فيمكن اعتبار هذه القوائم متماثلة. الترتيب الذي يتم به تكديس القيم في هذه القائمة لا يهم كثيرًا.
2. يمكننا التحدث عن المصادفة الجزئية للقوائم إذا كانت القيم الفريدة نفسها هي نفسها ، لكن عدد التكرارات مختلف. لذلك ، قد تحتوي هذه القوائم على عدد مختلف من العناصر.
3. يشار إلى حقيقة عدم تطابق القائمتين بمجموعة مختلفة من القيم الفريدة.

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

حل المشكلة

دعنا ننشئ نطاقين ديناميكيين لتسهيل مقارنة القوائم. كل واحد منهم سوف يتوافق مع كل قائمة.

لمقارنة قائمتين ، قم بما يلي:

1. في عمود منفصل ، نقوم بإنشاء قائمة بالقيم الفريدة الخاصة بكلتا القائمتين. لهذا نستخدم الصيغة: ЕСЛИОШИБКА(ЕСЛИОШИБКА( ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список1);0)); ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список2);0))); «»). يجب كتابة الصيغة نفسها كصيغة صفيف.
2. دعنا نحدد عدد المرات التي تحدث فيها كل قيمة فريدة في مصفوفة البيانات. فيما يلي الصيغ للقيام بذلك: = COUNTIF (List1، D5) و = COUNTI (List2، D5).
3. إذا كان كل من عدد التكرارات وعدد القيم الفريدة متماثلًا في جميع القوائم المضمنة في هذه النطاقات ، فإن الدالة ترجع القيمة 0. ويشير هذا إلى أن التطابق هو XNUMX٪. في هذه الحالة ، ستكتسب عناوين هذه القوائم خلفية خضراء.
4. إذا كان كل المحتوى الفريد موجودًا في كلتا القائمتين ، فسيتم إرجاعه بواسطة الصيغ =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;E5:E34;0) и =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;F5:F34;0) ستكون القيمة صفرًا. إذا كانت E1 لا تحتوي على صفر ، ولكن هذه القيمة موجودة في الخليتين E2 و F2 ، فسيتم في هذه الحالة التعرف على النطاقات على أنها مطابقة ، ولكن جزئيًا فقط. في هذه الحالة ، ستتحول عناوين القوائم المقابلة إلى اللون البرتقالي.
5. وإذا قامت إحدى الصيغ الموضحة أعلاه بإرجاع قيمة غير صفرية ، فستكون القوائم غير متطابقة تمامًا.

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

مثال على الاختبار

في نسختنا من الجدول ، هناك ثلاثة أنواع من القوائم لكل نوع موصوف أعلاه. يحتوي على مطابقة جزئية وكاملة ، فضلاً عن عدم المطابقة.

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

إيجاد الاختلافات في قائمتين بطريقتين

دعنا نصف طريقتين أخريين للعثور على الاختلافات ، اعتمادًا على ما إذا كانت القوائم متزامنة أم لا.

الخيار 1. قوائم متزامنة

هذا خيار سهل. افترض أن لدينا مثل هذه القوائم.

لتحديد عدد المرات التي لم تتقارب فيها القيم ، يمكنك استخدام الصيغة: = SUMPRODUCT (- (A2: A20 <> B2: B20)). إذا حصلنا على 0 نتيجة لذلك ، فهذا يعني أن القائمتين متماثلتان.

الخيار 2: القوائم العشوائية

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

كيفية مقارنة عمودين صف بعد صف

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

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

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

يجب علينا كتابة هذه الصيغة لفهم ما إذا كانت القيم في كلا العمودين هي نفسها أم لا: = IF (A2 = B2، "Match"، ""). منطق هذه الوظيفة بسيط للغاية: فهو يقارن القيم في الخلايا A2 و B2 ، وإذا كانت متطابقة ، فإنها تعرض القيمة "Coincide". إذا كانت البيانات مختلفة ، فإنها لا تُرجع أي قيمة. يمكنك أيضًا التحقق من الخلايا لمعرفة ما إذا كان هناك تطابق بينها. في هذه الحالة ، الصيغة المستخدمة هي: = IF (A2 <> B2، "لا تطابق" ، ""). المبدأ هو نفسه ، يتم إجراء الفحص أولاً. إذا اتضح أن الخلايا تفي بالمعيار ، فسيتم عرض القيمة "غير متطابقة".

من الممكن أيضًا استخدام الصيغة التالية في حقل الصيغة لعرض "مطابقة" إذا كانت القيم هي نفسها ، و "عدم التطابق" إذا كانت مختلفة: = IF (A2 = B2؛ “تطابق”، “عدم تطابق”). يمكنك أيضًا استخدام عامل عدم المساواة بدلاً من عامل المساواة. سيكون ترتيب القيم التي سيتم عرضها في هذه الحالة فقط مختلفًا قليلاً: = IF (A2 <> B2، "لا تطابق" ، "تزامن"). بعد استخدام الإصدار الأول من الصيغة ، ستكون النتيجة كما يلي.

هذا الاختلاف في الصيغة غير حساس لحالة الأحرف. لذلك ، إذا كانت القيم الموجودة في أحد الأعمدة تختلف عن القيم الأخرى فقط من حيث أنها مكتوبة بأحرف كبيرة ، فلن يلاحظ البرنامج هذا الاختلاف. لجعل المقارنة حساسة لحالة الأحرف ، تحتاج إلى استخدام الوظيفة في المعايير EXACT. بقيت بقية الحجج دون تغيير: = IF (EXACT (A2، B2)، "Match"، "Unique").

كيفية مقارنة عدة أعمدة للمطابقات في صف واحد

من الممكن تحليل القيم في القوائم وفقًا لمجموعة كاملة من المعايير:

1. ابحث عن تلك الصفوف التي لها نفس القيم في كل مكان.
2. ابحث عن تلك الصفوف التي توجد بها مطابقات في قائمتين فقط.

لنلقِ نظرة على بعض الأمثلة حول كيفية المضي قدمًا في كل حالة من هذه الحالات.

مثال. كيفية البحث عن التطابقات في صف واحد في عدة أعمدة في الجدول

افترض أن لدينا سلسلة من الأعمدة التي تحتوي على المعلومات التي نحتاجها. نحن نواجه مهمة تحديد تلك الصفوف التي تكون فيها القيم هي نفسها. للقيام بذلك ، تحتاج إلى استخدام الصيغة التالية: = IF (AND (A2 = B2، A2 = C2)، "match"، "").

إذا كان هناك عدد كبير جدًا من الأعمدة في الجدول ، فأنت تحتاج فقط إلى استخدامه مع الوظيفة IF عامل COUNTIF: = IF (COUNTIF ($ A2: $ C2، $ A2) = 3 ؛ "تطابق" ؛ ""). يشير الرقم المستخدم في هذه الصيغة إلى عدد الأعمدة التي يجب التحقق منها. إذا كان الأمر مختلفًا ، فأنت بحاجة إلى الكتابة بقدر ما هو صحيح بالنسبة لموقفك.

مثال. كيفية البحث عن التطابقات في صف واحد في أي عمودين من الجدول

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

نستخدم هذه الصيغة: =ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “). قد يكون هناك موقف عندما يكون هناك الكثير من الأعمدة في الجدول. في هذه الحالة ، ستكون الصيغة ضخمة ، وقد يستغرق الأمر وقتًا طويلاً لتحديد كل التركيبات الضرورية. لحل هذه المشكلة ، تحتاج إلى استخدام الوظيفة COUNTIF: = IF (COUNTIF (B2: D2، A2) + COUNTIF (C2: D2، B2) + (C2 = D2) = 0؛ “سلسلة فريدة”؛ “سلسلة غير فريدة”)

نرى أن لدينا وظيفتين إجمالاً COUNTIF. مع الأول ، نحدد بالتناوب عدد الأعمدة التي لها تشابه مع A2 ، ومع العمود الثاني ، نتحقق من عدد أوجه التشابه مع قيمة B2. إذا حصلنا ، نتيجة الحساب بهذه الصيغة ، على قيمة صفرية ، فهذا يشير إلى أن جميع الصفوف في هذا العمود فريدة ، وإذا كان هناك المزيد ، فهناك أوجه تشابه. لذلك ، إذا حصلنا على قيمة صفرية نتيجة الحساب باستخدام صيغتين وإضافة النتائج النهائية ، فعندئذٍ يتم إرجاع القيمة النصية "سلسلة فريدة" ، وإذا كان هذا الرقم أكبر ، فيُكتب أن هذه السلسلة ليست فريدة.

كيفية مقارنة عمودين في Excel للمطابقات

الآن لنأخذ مثالا. لنفترض أن لدينا جدولاً به عمودين. تحتاج إلى التحقق مما إذا كانت متطابقة. للقيام بذلك ، تحتاج إلى تطبيق الصيغة ، حيث سيتم استخدام الوظيفة أيضًا IFو المشغل COUNTIF: = IF (COUNTIF ($ B: $ B، $ A5) = 0 ، "لا يوجد تطابق في العمود B" ، "هناك تطابقات في العمود B")

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

كيفية مقارنة عمودين في Excel للمطابقات وإبراز اللون

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

البحث عن وإبراز التطابقات حسب اللون في عدة أعمدة

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

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

إيجاد وتسليط الضوء على خطوط المطابقة

تقنية التحقق من تطابق السلاسل مختلفة قليلاً. أولاً ، نحتاج إلى إنشاء عمود إضافي ، وهناك سنستخدم القيم المجمعة باستخدام عامل التشغيل &. للقيام بذلك ، تحتاج إلى كتابة صيغة النموذج: = A2 & B2 & C2 & D2.

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

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