في بعض الأحيان تكون هناك حاجة لمقارنة ملفين من ملفات MS Excel. قد يكون هذا هو العثور على تناقضات في أسعار عناصر معينة أو تغيير أي مؤشرات، لا يهم، الشيء الرئيسي هو أنه من الضروري العثور على بعض التناقضات.
لن يكون من الخطأ أن نذكر أنه إذا كان هناك سجلان في ملف MS Excel، فلا فائدة من اللجوء إلى الأتمتة. إذا كان الملف يحتوي على عدة مئات، أو حتى آلاف السجلات، فمن المستحيل القيام به دون مساعدة قوة الحوسبة للكمبيوتر.
دعونا نحاكي موقفًا حيث يكون لملفين نفس عدد الأسطر، ويجب البحث عن التناقض في عمود معين أو في عدة أعمدة. هذا الموقف ممكن، على سبيل المثال، إذا كنت بحاجة إلى مقارنة أسعار البضائع وفقًا لقائمة أسعار، أو مقارنة قياسات الرياضيين قبل موسم التدريب وبعده، على الرغم من أنه يجب أن يكون هناك الكثير منها لمثل هذه الأتمتة.
كمثال عملي، لنأخذ ملفًا يتضمن أداء المشاركين الوهميين: الجري لمسافة 100 متر، والجري لمسافة 3000 متر، وعمليات السحب. الملف الأول قياس في بداية الموسم والثاني نهاية الموسم.
الطريقة الأولى لحل المشكلة. الحل هو فقط باستخدام صيغ MS Excel.
وبما أن السجلات مرتبة عموديًا (الترتيب الأكثر منطقية)، فمن الضروري استخدام الوظيفة. إذا كنت تستخدم وضعًا أفقيًا للسجلات، فسيتعين عليك استخدام الوظيفة.
لمقارنة أداء الجري لمسافة 100 متر، تكون الصيغة كما يلي:
=IF(VLOOKUP($B2,Sheet2!$B$2:$F$13,3,TRUE)<>D2;D2-VLOOKUP($B2;Sheet2!$B$2:$F$13,3,TRUE);"لا فرق")
إذا لم يكن هناك فرق، يتم عرض رسالة تفيد بأنه لا يوجد فرق، وإذا كان هناك فرق، فسيتم طرح القيمة في نهاية الموسم من القيمة في بداية الموسم.
صيغة سباق 3000 متر هي كما يلي:
=IF(VLOOKUP($B2,Sheet2!$B$2:$F$13,4,TRUE)<>E2;"هناك فرق";"لا يوجد فرق")
إذا كانت القيم النهائية والأولية غير متساوية، يتم عرض الرسالة المقابلة. يمكن أن تكون صيغة السحب مشابهة لأي من تلك السابقة، فلا معنى لإعطائها بالإضافة إلى ذلك. الملف النهائي مع التناقضات الموجودة موضح أدناه.
القليل من التوضيح. لتسهيل قراءة الصيغ، تم نقل البيانات من الملفين إلى ملف واحد (على أوراق مختلفة)، ولكن لم يكن من الممكن القيام بذلك.
فيديو يقارن بين ملفين من ملفات MS Excel باستخدامهما ووظائفهما.
الطريقة الثانية لحل المشكلة. الحل باستخدام MS Access.
يمكن حل هذه المشكلة إذا قمت أولاً باستيراد ملفات MS Excel إلى Access. أما بالنسبة لطريقة استيراد البيانات الخارجية نفسها، فلا يوجد فرق في العثور على حقول مختلفة (أي من الخيارات المقدمة سيفي بالغرض).
هذا الأخير عبارة عن اتصال بين ملفات Excel وAccess، لذلك عند تغيير البيانات في ملفات Excel، سيتم العثور على التناقضات تلقائيًا عند تشغيل استعلام في MS Access.
الخطوة التالية بعد الاستيراد هي إنشاء علاقات بين الجداول. كحقل متصل، حدد الحقل الفريد "رقم الصنف".
الخطوة الثالثة هي الإنشاء طلب بسيطللاختيار باستخدام مصمم الاستعلام.
في العمود الأول، نشير إلى السجلات التي يجب عرضها، وفي الثانية - تحت أي شروط سيتم عرض السجلات. وبطبيعة الحال، بالنسبة للمجالين الثاني والثالث ستكون الإجراءات متشابهة.
فيديو يقارن ملفات MS بملفات Excel باستخدام MS Access.
ونتيجة لعمليات التلاعب التي تم إجراؤها، يتم عرض جميع السجلات ببيانات مختلفة في الحقل: "الجري 100 متر". ملف MS Access معروض أدناه (لسوء الحظ، SkyDrive لا يسمح بالتضمين كملف Excel)
توجد هاتان الطريقتان للعثور على التناقضات في جداول MS Excel. ولكل منها مزايا وعيوب. من الواضح أن هذه ليست قائمة شاملة للمقارنات بين ملفي Excel. نحن في انتظار اقتراحاتكم في التعليقات.
يتلقى مسؤول الموارد البشرية كل شهر قائمة بأسماء الموظفين مع رواتبهم. يقوم بنسخ القائمة إلى ورقة جديدة في مصنف Excel. المهمة هي كما يلي: مقارنة رواتب الموظفين التي تغيرت مقارنة بالشهر السابق. للقيام بذلك، تحتاج إلى مقارنة البيانات في Excel على أوراق مختلفة. دعونا نستخدم التنسيق الشرطي. بهذه الطريقة لن نقوم فقط بالعثور تلقائيًا على جميع الاختلافات في قيم الخلايا، ولكن أيضًا نسلط الضوء عليها بالألوان.
مقارنة ورقتين في Excel
قد يكون لدى الشركة أكثر من مائة موظف، من بينهم البعض يستقيل، والبعض الآخر يتم تعيينه، والبعض الآخر يذهب في إجازة أو إجازة مرضية، وما إلى ذلك. ونتيجة لذلك، قد يكون من الصعب مقارنة بيانات الرواتب. على سبيل المثال، ستكون الأسماء الأخيرة للموظفين دائمًا بتسلسلات مختلفة. كيفية إجراء مقارنة بين اثنين جداول اكسلعلى أوراق مختلفة؟
سيساعدنا التنسيق الشرطي في حل هذه المشكلة الصعبة. على سبيل المثال، لنأخذ بيانات شهري فبراير ومارس، كما هو موضح في الشكل:
للعثور على التغييرات في قسائم الدفع:
بعد إدخال كافة الشروط تنسيق اكسليتم تمييز الموظفين الذين تغيرت رواتبهم تلقائيًا بالألوان مقارنة بالشهر السابق.
مبدأ مقارنة نطاقين من البيانات في Excel على أوراق مختلفة:
في ظروف معينة، تكون وظيفة MATCH ضرورية. تحتوي وسيطتها الأولى على زوج من القيم التي يجب العثور عليها في الورقة المصدر للشهر التالي، أي "مارس". يتم تعريف النطاق القابل للتصفح على أنه زوج من قيم النطاق المحددة بالأسماء. بهذه الطريقة، تتم مقارنة السلاسل بناءً على خاصيتين: الاسم الأخير والراتب. بالنسبة للتطابقات التي تم العثور عليها، يتم إرجاع رقم، وهو ما ينطبق بشكل أساسي على Excel. ولذلك، يجب عليك استخدام الدالة =NOT()، والتي تسمح لك باستبدال القيمة TRUE بالقيمة FALSE. وبخلاف ذلك، سيتم تطبيق التنسيق على الخلايا التي تتطابق قيمها. لكل زوج من القيم التي لم يتم العثور عليها (أي عدم تطابق) &B2&$C2 في النطاق LastName&Salary، ترجع الدالة MATCH خطأً. قيمة الخطأ ليست قيمة منطقية. ولذلك، نستخدم الدالة IFERROR، والتي ستقوم بتعيين قيمة منطقية لكل خطأ - TRUE. وهذا يسهل تعيين تنسيق جديد فقط للخلايا دون مطابقة قيم الراتب فيما يتعلق بالشهر التالي - مارس.
غالبًا ما تكون المهمة هي مقارنة قائمتين من العناصر. يعد القيام بذلك يدويًا أمرًا مملًا للغاية، كما لا يمكن استبعاد احتمال حدوث أخطاء. يجعل Excel هذه العملية سهلة. تصف هذه النصيحة طريقة تستخدم التنسيق الشرطي.
في التين. يوضح الشكل 164.1 مثالاً لقائمتين من الأسماء متعددة الأعمدة. يمكن أن يؤدي استخدام التنسيق الشرطي إلى جعل الاختلافات في القوائم واضحة. تحتوي أمثلة القائمة هذه على نص، لكن الطريقة المعنية تعمل مع البيانات الرقمية أيضًا.
القائمة الأولى هي A2:B31، ويسمى هذا النطاق القائمة القديمة. القائمة الثانية هي D2:E31، ويسمى النطاق قائمة جديدة. تمت تسمية النطاقات باستخدام الأمر الصيغ الأسماء المعرفة تعيين اسم. ليس من الضروري تسمية النطاقات، لكن ذلك يجعل العمل معها أسهل.
لنبدأ بإضافة التنسيق الشرطي إلى القائمة القديمة.
- حدد الخلايا في نطاق القائمة القديمة.
- يختار.
- فى الشباك إنشاء قاعدة التنسيقحدد العنصر المسمى استخدم الصيغة
- أدخل هذه الصيغة في حقل النافذة (الشكل 164.2): =COUNTIF(NewList;A2)=0.
- انقر فوق الزر شكلوحدد التنسيق الذي سيتم تطبيقه عندما يكون الشرط صحيحًا. من الأفضل اختيار ألوان تعبئة مختلفة.
- انقر فوق موافق.
الخلايا في النطاق قائمة جديدةاستخدم صيغة تنسيق شرطي مماثلة.
- حدد الخلايا في نطاق قائمة جديدة.
- يختار الصفحة الرئيسية التنسيق الشرطي إنشاء قاعدةلفتح مربع حوار إنشاء قاعدة التنسيق.
- فى الشباك إنشاء قاعدةتنسيق العنصر المحدد استخدم الصيغةلتحديد الخلايا التي سيتم تنسيقها.
- أدخل هذه الصيغة في حقل النافذة: =COUNTIF(OldList;D2)=0 .
- انقر فوق الزر شكلوقم بتعيين التنسيق الذي سيتم تطبيقه عندما يكون الشرط صحيحًا (لون تعبئة مختلف).
- انقر فوق موافق.
ونتيجة لذلك، سيتم تسليط الضوء على الأسماء الموجودة في القائمة القديمة، ولكن ليس في القائمة الجديدة (الشكل 164.3). بالإضافة إلى ذلك، يتم أيضًا تمييز الأسماء الموجودة في القائمة الجديدة غير الموجودة في القائمة القديمة، ولكن بلون مختلف. لا يتم تمييز الأسماء التي تظهر في كلتا القائمتين.
تستخدم كل من صيغ التنسيق الشرطي الدالة كونتيف. يقوم بحساب عدد المرات التي تظهر فيها قيمة معينة في نطاق ما. إذا كانت الصيغة ترجع 0، فهذا يعني أن العنصر ليس في النطاق. بهذه الطريقة، يتولى التنسيق الشرطي ويتغير لون خلفية الخلية.
قراءة هذا المقال ستأخذ منك حوالي 10 دقائق. في الدقائق الخمس التالية، يمكنك بسهولة مقارنة عمودين في Excel ومعرفة ما إذا كان هناك تكرارات فيهما أو حذفهما أو تمييزهما بالألوان. لقد حان الوقت!
يعد Excel تطبيقًا قويًا جدًا ورائعًا حقًا لإنشاء ومعالجة كميات كبيرة من البيانات. إذا كان لديك مصنفات متعددة تحتوي على بيانات (أو جدول واحد ضخم فقط)، فربما تريد مقارنة عمودين، والعثور على قيم مكررة، ثم القيام بشيء ما بها، مثل حذف المحتويات أو تمييزها أو مسحها. يمكن أن تكون الأعمدة في نفس الجدول، متجاورة أو غير متجاورة، وتقع على ورقتين مختلفتين أو حتى في مصنفات مختلفة.
تخيل أن لدينا عمودين لأسماء الأشخاص - 5 أسماء في كل عمود أو3 أسماء في عمود ب. تحتاج إلى مقارنة الأسماء في هذين العمودين والعثور على أي تكرارات. وكما تفهم، هذه بيانات وهمية تم أخذها لأغراض توضيحية فقط. في الجداول الحقيقية، نتعامل مع آلاف، أو حتى عشرات الآلاف من السجلات.
الخيار أ:كلا العمودين موجودان على نفس الورقة. على سبيل المثال، العمود أوالعمود ب.
الخيار ب:توجد الأعمدة على أوراق مختلفة. على سبيل المثال، العمود أعلى ورقة الورقة2والعمود أعلى ورقة ورقة3.
يحتوي Excel 2013 و2010 و2007 على أداة مدمجة إزالة التكرارات(إزالة التكرارات) ولكنها عاجزة في هذه الحالة لأنها لا تستطيع مقارنة البيانات في عمودين. علاوة على ذلك، يمكنه فقط إزالة التكرارات. ولا توجد خيارات أخرى، مثل التمييز أو تغيير الألوان. والفترة!
قارن عمودين في Excel وابحث عن الإدخالات المكررة باستخدام الصيغ
الخيار أ: كلا العمودين موجودان في نفس الورقة
فكرة:في الجداول الكبيرة، سيكون نسخ الصيغة أسرع إذا كنت تستخدم اختصارات لوحة المفاتيح. حدد خلية ج1و اضغط السيطرة + C(لنسخ الصيغة إلى الحافظة)، ثم انقر فوق السيطرة + التحول + النهاية(لتحديد كافة الخلايا غير الفارغة في العمود C) ثم انقر أخيرًا السيطرة+V(للصق الصيغة في كافة الخلايا المحددة).
الخيار ب: عمودان موجودان على أوراق مختلفة (في كتب مختلفة)
معالجة وجدت التكرارات
رائع، لقد وجدنا إدخالات في العمود الأول موجودة أيضًا في العمود الثاني. الآن علينا أن نفعل شيئا معهم. يعد المرور عبر كافة الإدخالات المكررة في الجدول يدويًا أمرًا غير فعال تمامًا ويستغرق الكثير من الوقت. هناك طرق أفضل.
إظهار الصفوف المكررة فقط في العمود A
إذا كانت أعمدتك لا تحتوي على عناوين، فأنت بحاجة إلى إضافتها. للقيام بذلك، ضع المؤشر على الرقم الذي يشير إلى السطر الأول، وسوف يتحول إلى سهم أسود، كما هو موضح في الشكل أدناه:
انقر بزر الماوس الأيمن و قائمة السياقيختار إدراج(إدراج):
قم بتسمية الأعمدة، على سبيل المثال، " اسم" و " ينسخ؟" ثم افتح علامة التبويب بيانات(البيانات) ثم اضغط منقي(منقي):
بعد ذلك، انقر فوق السهم الرمادي الصغير بجوار " ينسخ؟" لتوسيع قائمة التصفية؛ قم بإلغاء تحديد كافة العناصر في هذه القائمة باستثناء ينسخ، و اضغط نعم.
هذا كل شيء، والآن ترى فقط عناصر الأعمدة تلك أ، وهي مكررة في العمود في. لا يوجد سوى خليتين فقط في طاولة التدريب لدينا، ولكن، كما تفهم، سيكون هناك الكثير منها في الممارسة العملية.
لعرض كافة صفوف العمود مرة أخرى أ، انقر فوق رمز عامل التصفية في العمود في، والذي يبدو الآن وكأنه قمع به سهم صغير ثم حدده اختر الكل(اختر الكل). أو يمكنك أن تفعل الشيء نفسه من خلال الشريط بالنقر فوق بيانات(بيانات) > تحديد وتصفية(فرز وتصفية) > واضح(واضح) كما هو موضح في الصورة أدناه:
تغيير اللون أو تسليط الضوء على التكرارات الموجودة
إذا كانت العلامات " ينسخ"لا يكفي لأغراضك، وتريد وضع علامة على الخلايا المكررة بلون خط مختلف أو لون تعبئة أو بطريقة أخرى...
في هذه الحالة، قم بتصفية التكرارات كما هو موضح أعلاه، وحدد جميع الخلايا التي تمت تصفيتها وانقر فوق السيطرة+1لفتح مربع الحوار تنسيق الخلايا(تنسيق الخلية). على سبيل المثال، دعونا نغير لون تعبئة الخلايا في الصفوف ذات التكرارات إلى اللون الأصفر الفاتح. بالطبع، يمكنك تغيير لون التعبئة باستخدام الأداة يملأعلامة التبويب (لون التعبئة). بيت(الصفحة الرئيسية)، ولكن ميزة مربع الحوار تنسيق الخلايا(تنسيق الخلايا) هو أنه يمكنك تكوين جميع خيارات التنسيق مرة واحدة.
الآن لن تفوتك بالتأكيد خلية واحدة تحتوي على نسخ مكررة:
إزالة القيم المكررة من العمود الأول
قم بتصفية الجدول لإظهار الخلايا التي تحتوي على قيم مكررة فقط، ثم حدد تلك الخلايا.
إذا كان العمودان اللذان تقارنهما موجودين على أوراق مختلفة، أي في جداول مختلفة، انقر بزر الماوس الأيمن فوق النطاق المحدد وحدد من قائمة السياق احذف صف(حذف السطر):
انقر نعمعندما يطلب منك Excel تأكيد رغبتك في حذف صف ورقة العمل بالكامل ثم مسح عامل التصفية. كما ترون، تبقى فقط الصفوف ذات القيم الفريدة:
إذا كان هناك عمودين على ورقة واحدة، قريبة من بعضها البعض (مجاورة) أو غير قريبة من بعضها البعض (غير مجاورة)، فإن عملية إزالة التكرارات ستكون أكثر صعوبة قليلاً. لا يمكننا حذف الصف بأكمله الذي يحتوي على قيم مكررة لأن ذلك سيؤدي إلى حذف الخلايا من العمود الثاني أيضًا. لذلك، للاحتفاظ فقط بالإدخالات الفريدة في العمود أ، قم بما يلي:
كما ترى، فإن إزالة التكرارات من عمودين في Excel باستخدام الصيغ ليس بالأمر الصعب.
يقدم المقال إجابات على الأسئلة التالية:
- كيفية مقارنة جدولين في إكسيل؟
- كيفية مقارنة الجداول المعقدة في إكسيل؟
- كيفية مقارنة الجداول في Excel باستخدام الدالة VLOOKUP()؟
- كيفية إنشاء معرفات صفوف فريدة إذا تم تحديد تفردها في البداية من خلال مجموعة من القيم في عدة أعمدة؟
- كيفية إصلاح قيم الخلايا في الصيغ عند نسخ الصيغ؟
عند التعامل مع كميات كبيرة من المعلومات، قد يواجه المستخدم مهمة مثل مقارنة مصدرين للبيانات الجدولية. عند تخزين البيانات في نظام موحدالمحاسبة (على سبيل المثال، الأنظمة المستندة إلى 1C Enterprise، والأنظمة التي تستخدم قاعدة بيانات SQLالبيانات)، يمكن استخدام الإمكانات المضمنة في النظام أو نظام إدارة قواعد البيانات (DBMS) لمقارنة البيانات. كقاعدة عامة، للقيام بذلك، يكفي جذب المبرمج الذي سيكتب استعلامًا إلى قاعدة البيانات، أو آلية الإبلاغ عن البرامج. يمكن للمستخدم ذو الخبرة الذي لديه مهارة كتابة استعلامات 1C أو SQL التعامل مع الطلب.
تبدأ المشاكل عندما تكون هناك حاجة ملحة لإكمال مهمة مقارنة البيانات، وقد يؤدي تعيين مبرمج وكتابة استعلام أو تقرير برنامج إلى تجاوز المواعيد النهائية المحددة لحل المهمة. مشكلة أخرى شائعة بنفس القدر هي الحاجة إلى مقارنة المعلومات من مصادر مختلفة. في هذه الحالة، فإن بيان المشكلة بالنسبة للمبرمج سيبدو وكأنه تكامل بين نظامين. سيتطلب حل مثل هذه المشكلة مبرمجًا أكثر كفاءة وسيستغرق أيضًا وقتًا أطول من التطوير في نظام واحد.
لحل هذه المشكلات، الأسلوب المثالي هو استخدام محرر جداول البيانات لمقارنة البيانات. مايكروسوفت اكسل. تدعم معظم أنظمة المحاسبة الإدارية والتنظيمية الشائعة التحميل إلى تنسيق Excel. ستتطلب هذه المهمة فقط مؤهل مستخدم معين للعمل معها جناح المكتبولا يتطلب مهارات البرمجة.
دعونا نلقي نظرة على حل مشكلة مقارنة الجداول في Excel باستخدام مثال. لدينا جدولين يحتويان على قوائم الشقق. تحميل المصادر - 1C Enterprise (محاسبة البناء) وجدول Excel (محاسبة المبيعات). يتم وضع الجداول في مصنف Excel على الورقة الأولى والثانية، على التوالي.
مهمتنا هي مقارنة هذه القوائم حسب العنوان. الجدول الأول يحتوي على جميع الشقق في المبنى. الجدول الثاني يحتوي فقط على الشقق المباعة واسم المشتري. الهدف النهائي هو عرض اسم المشتري في الجدول الأول لكل شقة (تلك الشقق التي تم بيعها). وتتعقد المهمة لأن عنوان الشقة في كل جدول هو عنوان مبنى ويتكون من عدة حقول: 1) عنوان المبنى (المنزل)، 2) القسم (المدخل)، 3) الطابق، 4) الرقم الموجود في الجدول. الكلمة (على سبيل المثال، من 1 إلى 4).
لمقارنة جدولين في Excel، نحتاج إلى التأكد من أنه في كلا الجدولين يتم تحديد كل صف بواسطة حقل واحد، وليس أربعة. يمكنك الحصول على مثل هذا الحقل من خلال دمج قيم حقول العناوين الأربعة مع وظيفة Concatenate(). الغرض من وظيفة Concatenate() هو الجمع بين عدة قيم النصفي سطر واحد. يتم إدراج القيم الموجودة في الوظيفة مفصولة بالرمز "؛". يمكن أن تكون القيم إما عناوين خلايا أو نصًا عشوائيًا محددًا بين علامتي اقتباس.
الخطوة 1.لنقم بإدراج عمود فارغ "A" في بداية الجدول الأول ونكتب الصيغة في خلية هذا العمود المقابلة للصف الأول الذي يحتوي على البيانات:
=CONCATENATE(B3;"-";C3;"-";D3;"-";E3)
ولتسهيل الإدراك البصري، قمنا بوضع رموز "-" بين قيم الخلايا التي يتم دمجها.
الخطوة 2.لننسخ الصيغة إلى الخلايا التالية في العمود A.
الخطوة 4.لمقارنة جداول Excel حسب القيم، استخدم الدالة VLOOKUP(). الغرض من الدالة VLOOKUP() هو البحث عن قيمة في العمود الموجود في أقصى يسار الجدول وإرجاع قيمة الخلية الموجودة في العمود المحدد من نفس الصف. المعلمة الأولى هي القيمة المطلوبة. المعلمة الثانية هي الجدول الذي سيتم البحث فيه عن القيمة. المعلمة الثالثة هي رقم العمود من الخلية التي سيتم إرجاع القيمة الخاصة بها في الصف الموجود. المعلمة الرابعة هي نوع البحث: خطأ - تطابق تام، صحيح - تطابق تقريبي.
نظرًا لأنه يجب وضع معلومات الإخراج في الجدول الأول (كان علينا إضافة أسماء العملاء فيه)، فسنكتب الصيغة فيه. لنقم بإنشاء صيغة في العمود الحر الموجود على يمين الجدول المقابل للصف الأول من البيانات:
=VLOOKUP(A3,Sheet2!$A$3:$F$10,6,FALSE)
عندما تقوم بنسخ الصيغ، يقوم Smart Excel تلقائيًا بتغيير عنوان الخلية. في حالتنا، ستتغير القيمة التي تم البحث عنها لكل صف: A3، A4، وما إلى ذلك، ولكن يجب أن يظل عنوان الجدول الذي يتم فيه البحث دون تغيير. للقيام بذلك، نقوم بإصلاح الخلايا الموجودة في معلمة عنوان الجدول بالرموز "$". بدلاً من "Sheet2!A3:F10" نقوم بإنشاء "Sheet2!$A$3:$F$10".
