طرق لمقارنة الجداول في Microsoft Excel

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

اقرأ أيضا: مقارنة بين وثيقتين في مايكروسوفت وورد

طرق المقارنة

هناك العديد من الطرق لمقارنة مناطق الجدول في Excel ، ولكن يمكن تقسيمها إلى ثلاث مجموعات كبيرة:

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

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

الطريقة 1: صيغة بسيطة

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

لذا ، لدينا جدولان بسيطان مع قوائم موظفي المؤسسة ورواتبهم. من الضروري مقارنة قوائم الموظفين وتحديد التناقضات بين الأعمدة التي يتم وضع الأسماء فيها.

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

   =A2=D2

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

2. نضغط على مفتاح Enter للحصول على نتائج المقارنة. كما ترى ، عند مقارنة الخلايا الأولى في كلا القائمتين ، أشار البرنامج إلى المؤشر "TRUE" ، مما يعني أن البيانات تتطابق.
3. الآن نحن بحاجة إلى إجراء عملية مماثلة مع الخلايا المتبقية من كلا الجدولين في الأعمدة التي نقارنها. ولكن يمكنك فقط نسخ الصيغة ، والتي ستوفر الكثير من الوقت. خاصة هذا العامل مهم عند مقارنة القوائم مع عدد كبير من الخطوط.

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

4. كما ترون ، الآن في العمود الإضافي تم عرض جميع نتائج مقارنة البيانات في عمودين من صفائف الجدول. في حالتنا ، لم تطابق البيانات سوى سطر واحد. عند المقارنة بينهما ، أنتجت الصيغة نتيجة "LIE" . بالنسبة إلى جميع الأسطر الأخرى ، كما نرى ، تم إصدار صيغة المقارنة من خلال المؤشر "TRUE" .
5. بالإضافة إلى ذلك ، من الممكن حساب عدد عدم التطابق باستخدام صيغة خاصة. للقيام بذلك ، حدد عنصر الورقة حيث سيتم الإخراج. ثم انقر فوق رمز "إدراج الدالة" .
6. في الإطار " معالج من الوظائف" ، ضمن مجموعة عوامل التشغيل "الرياضي" ، حدد اسم SUMPRODUCT . نضغط على زر "موافق" .
7. يتم تنشيط نافذة الوسيطة الخاصة بوظيفة SUMPRODUCT ، والتي تتمثل المهمة الرئيسية لها في حساب مجموع منتجات النطاق المحدد. ولكن هذه الوظيفة يمكن استخدامها لأغراضنا. بناء الجملة بسيط للغاية:

   =СУММПРОИЗВ(массив1;массив2;…)

   في المجموع ، يمكن استخدام ما يصل إلى 255 صفائف كحجج. ولكن في حالتنا سوف نستخدم فقط صفيفين ، بالإضافة إلى ذلك ، كحجة واحدة.

   وضعنا المؤشر في الحقل "Array1" وحدد على ورقة نطاق البيانات مقارنة في المنطقة الأولى. بعد ذلك ، ضع العلامة "ليست متساوية" ( <> ) في الحقل وحدد النطاق المقارن للمنطقة الثانية. بعد ذلك ، قم بلف التعبير الناتج بأقواس ، وضع قبلها اثنان من علامات "-" . في حالتنا ، كان التعبير:

   --(A2:A7<>D2:D7)

   نضغط على زر "موافق" .

8. يقوم المشغل بحساب النتيجة وعرضها. كما ترون ، في حالتنا تكون النتيجة مساوية للرقم "1" ، وهذا يعني أنه في قوائم المقارنة تم العثور على عدم تطابق واحد. إذا كانت القوائم متطابقة تمامًا ، فستكون النتيجة مساوية للرقم "0" .

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

=B2=Лист2!B2

أي ، كما ترى ، قبل إحداثيات البيانات ، الموجودة على أوراق أخرى ، تختلف عن مكان عرض نتيجة المقارنة ، يشار إلى رقم الورقة وعلامة تعجب.

الطريقة 2: تحديد مجموعات من الخلايا

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

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

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

2. يتم تنشيط نافذة انتقال صغيرة. نضغط على زر "اختر ..." في الزاوية السفلى اليسرى.
3. بعد ذلك ، أيًا كان الخياران أعلاه اللذين اخترتهما ، تبدأ نافذة تحديد مجموعات الخلايا. اضبط المفتاح على الوضع "Select by rows" . انقر على زر "موافق" .
4. كما ترون ، بعد ذلك سيتم تمييز قيم غير متطابقة للخطوط بنسق مختلف. بالإضافة إلى ذلك ، كما يمكن للمرء أن يحكم من محتويات خط الصيغة ، فإن البرنامج يجعل واحدة من الخلايا النشطة في الصفوف غير المتطابقة المشار إليها نشطة.

الطريقة الثالثة: التنسيق الشرطي

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

1. بادئ ذي بدء ، نختار أي منطقة الجدول سوف نعتبر الرئيسي ، وكيفية البحث عن الاختلافات. دعونا نفعل هذا الأخير في الجدول الثاني. لذلك ، نختار قائمة الموظفين الموجودين فيها. بعد الانتقال إلى علامة التبويب "الصفحة الرئيسية" ، نضغط على زر "تنسيق شرطي" ، الذي يحتوي على موقع على الشريط في كتلة "الأنماط" . من القائمة المنسدلة ، انتقل إلى "إدارة القواعد" .
2. يتم تنشيط نافذة مدير القواعد. انقر على زر "إنشاء قاعدة" في ذلك.
3. في النافذة التي تفتح ، نقوم باختيار العنصر "استخدم الصيغة" . في الحقل "تنسيق الخلايا" نكتب صيغة تحتوي على عناوين الخلايا الأولى من نطاقات الأعمدة المقارنة ، مفصولة بعلامة "لا تساوي" ( <> ). فقط قبل هذا التعبير هذه المرة سيكون هناك علامة "=" . بالإضافة إلى ذلك ، إلى كافة إحداثيات العمود في هذه الصيغة ، يجب عليك تطبيق عنونة مطلقة. للقيام بذلك ، حدد الصيغة مع المؤشر واضغط F4 ثلاث مرات. كما ترون ، حول كل عناوين العمود كان هناك علامة الدولار ، مما يعني تحويل الروابط إلى عناوين مطلقة. بالنسبة لحالتنا المحددة ، تأخذ الصيغة النموذج التالي:

   =$A2<>$D2

   نكتب هذا التعبير في الحقل أعلاه. بعد ذلك ، انقر فوق الزر "تنسيق ..." .

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

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

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

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

وبالتالي ، سيتم إبراز تلك المؤشرات التي لا تتوافق.

الدرس: التنسيق الشرطي في Excel

الطريقة الرابعة: الصيغة المعقدة

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

يشير مشغل COUNTRY إلى مجموعة إحصائية من الوظائف. وتتمثل مهمتها في حساب عدد الخلايا التي تفي قيمها بالشرط المحدد. بناء جملة هذا المشغل هو:

=СЧЁТЕСЛИ(диапазон;критерий)

وسيطة "المدى" هي عنوان المصفوفة التي يتم فيها حساب قيم المطابقة.

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

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

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

   كما ترون ، أخذ الرابط شكلاً مطلقًا ، والذي يتميز بوجود علامات الدولار.

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

4. يتم عرض النتيجة في عنصر الورقة. يساوي الرقم "1" . هذا يعني أنه في قائمة أسماء الجدول الثاني ، يقع اللقب "Grinev VP" ، وهو الأول في قائمة مصفوفة الجدول الأول ، مرة واحدة.
5. الآن نحن بحاجة إلى إنشاء تعبير مماثل لجميع العناصر الأخرى في الجدول الأول. للقيام بذلك ، قم بإجراء النسخ باستخدام علامة التعبئة ، كما فعلنا من قبل. ضع المؤشر في الجزء الأيمن السفلي من عنصر الورقة ، والذي يحتوي على الدالة COUNTIF ، وبعد تحويله إلى علامة التعبئة ، نقوم بتثبيت زر الماوس الأيسر وسحب المؤشر لأسفل.
6. كما ترى ، قام البرنامج بحساب المصادفات عن طريق مقارنة كل خلية من الجدول الأول مع البيانات الموجودة في نطاق الجدول الثاني. في أربع حالات ، كانت النتيجة "1" ، وفي حالتين ، "0" . بمعنى ، تعذر على البرنامج العثور في الجدول الثاني القيمتين المتوفرة في صفيف الجدول الأول.

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

سنجعل القيم المتوفرة في الجدول الثاني ، ولكن ليس في الجدول الأول ، سيتم عرضها في قائمة منفصلة.

1. بادئ ذي بدء ، سوف نعيد صياغة صيغتنا للبلد ، قليلا ، دعونا نجعلها واحدة من حجج مشغل IF . للقيام بذلك ، حدد أول خلية يوجد فيها مشغل COUNTRY . في السطر من الصيغ قبله نضيف التعبير "IF" بدون علامات اقتباس وفتح القوس. علاوة على ذلك ، لتسهيل علينا العمل ، نختار القيمة "IF" في سطر الصيغة ونضغط على أيقونة "Insert function" .
2. تفتح نافذة الوسيطة الخاصة بوظيفة IF . كما ترى ، يتم ملء الحقل الأول من الإطار بقيمة عامل COUNCIL بالفعل . لكننا نحتاج إلى إنهاء شيء آخر في هذا المجال. لقد وضعنا المؤشر هناك وأضفنا "= 0" بدون علامات اقتباس للتعبير الموجود بالفعل.

   بعد ذلك ، انتقل إلى الحقل "القيمة إذا كانت صحيحة" . سنستخدم هنا دالة أخرى متداخلة - STRING . ندخل الكلمة "LINE" بدون علامات اقتباس ، ثم نفتح الأقواس ونحدد إحداثيات الخلية الأولى بالاسم الأخير في الجدول الثاني ، ثم نغلق الأقواس. على وجه التحديد ، في حالتنا في الحقل "القيمة إن صح التعبير" تم الحصول على التعبير التالي:

   СТРОКА(D2)

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

3. كما ترى ، يتم عرض النتيجة الأولى على أنها "FALSE" . هذا يعني أن القيمة لا تستوفي شروط مشغل IF . وهذا هو ، أول لقب موجود في كلا القائمتين.
4. باستخدام علامة التعبئة ، نقوم بنسخ تعبير مشغل IF إلى العمود بالكامل بالطريقة المعتادة. كما ترى ، للعنصرين المتواجدين في الجدول الثاني ، ولكن ليس في الأول ، تعطي الصيغة أرقام الأسطر.
5. نرجع من منطقة الجدول إلى اليمين وتملأ العمود بالأرقام بالترتيب ، بدءًا من 1 . Количество номеров должно совпадать с количеством строк во второй сравниваемой таблице. Чтобы ускорить процедуру нумерации, можно также воспользоваться маркером заполнения.
6. После этого выделяем первую ячейку справа от колонки с номерами и щелкаем по значку «Вставить функцию» .
7. يفتح معالج الدالة . Переходим в категорию «Статистические» и производим выбор наименования «НАИМЕНЬШИЙ» . نضغط على زر "موافق" .
8. Функция НАИМЕНЬШИЙ , окно аргументов которой было раскрыто, предназначена для вывода указанного по счету наименьшего значения.

   В поле «Массив» следует указать координаты диапазона дополнительного столбца «Количество совпадений» , который мы ранее преобразовали с помощью функции ЕСЛИ . Делаем все ссылки абсолютными.

   В поле «K» указывается, какое по счету наименьшее значение нужно вывести. Тут указываем координаты первой ячейки столбца с нумерацией, который мы недавно добавили. Адрес оставляем относительным. نضغط على زر "موافق" .

9. Оператор выводит результат – число 3 . Именно оно наименьшее из нумерации несовпадающих строк табличных массивов. С помощью маркера заполнения копируем формулу до самого низа.
10. Теперь, зная номера строк несовпадающих элементов, мы можем вставить в ячейку и их значения с помощью функции ИНДЕКС . Выделяем первый элемент листа, содержащий формулу НАИМЕНЬШИЙ . После этого переходим в строку формул и перед наименованием «НАИМЕНЬШИЙ» дописываем название «ИНДЕКС» без кавычек, тут же открываем скобку и ставим точку с запятой ( ; ). Затем выделяем в строке формул наименование «ИНДЕКС» и кликаем по пиктограмме «Вставить функцию» .
11. После этого открывается небольшое окошко, в котором нужно определить, ссылочный вид должна иметь функция ИНДЕКС или предназначенный для работы с массивами. Нам нужен второй вариант. Он установлен по умолчанию, так что в данном окошке просто щелкаем по кнопке «OK» .
12. Запускается окно аргументов функции ИНДЕКС . Данный оператор предназначен для вывода значения, которое расположено в определенном массиве в указанной строке.

    Как видим, поле «Номер строки» уже заполнено значениями функции НАИМЕНЬШИЙ . От уже существующего там значения следует отнять разность между нумерацией листа Excel и внутренней нумерацией табличной области. Как видим, над табличными значениями у нас только шапка. Это значит, что разница составляет одну строку. Поэтому дописываем в поле «Номер строки» значение «-1» без кавычек.

    В поле «Массив» указываем адрес диапазона значений второй таблицы. При этом все координаты делаем абсолютными, то есть, ставим перед ними знак доллара уже ранее описанным нами способом.

    انقر على زر "موافق" .

13. После вывода результат на экран протягиваем функцию с помощью маркера заполнения до конца столбца вниз. Как видим, обе фамилии, которые присутствуют во второй таблице, но отсутствуют в первой, выведены в отдельный диапазон.

Способ 5: сравнение массивов в разных книгах

При сравнении диапазонов в разных книгах можно использовать перечисленные выше способы, исключая те варианты, где требуется размещение обоих табличных областей на одном листе. Главное условие для проведения процедуры сравнения в этом случае – это открытие окон обоих файлов одновременно. Для версий Excel 2013 и позже, а также для версий до Excel 2007 с выполнением этого условия нет никаких проблем. Но в Excel 2007 и Excel 2010 для того, чтобы открыть оба окна одновременно, требуется провести дополнительные манипуляции. Как это сделать рассказывается в отдельном уроке.

الدرس: Как открыть Эксель в разных окнах

Как видим, существует целый ряд возможностей сравнить таблицы между собой. Какой именно вариант использовать зависит от того, где именно расположены табличные данные относительно друг друга (на одном листе, в разных книгах, на разных листах), а также от того, как именно пользователь желает, чтобы это сравнение выводилось на экран.

• ← استعلامات SQL في Microsoft Excel
• العمل مع الجداول المرتبطة في Microsoft Excel →