العثور على التناقضات في Excel - شرح 5 تقنيات سهلة

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

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

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

جدول المحتويات

• فهم التناقضات في Excel
• 5 طرق للبحث عن التناقضات في Excel
• # 1 - البحث عن التناقضات عبر المراجعة اليدوية في Excel
• # 2 - استخدام عامل التصفية في Excel لاكتشاف وإصلاح البيانات غير المتسقة
• 3 # - إبراز التناقضات باستخدام التنسيق الشرطي
• # 4 - البحث عن التناقضات باستخدام وظائف Excel المتقدمة
  • وظائف IF و IS
  • VLOOKUP و HLOOKUP و XLOOKUP
  • مباراة
• # 5 - استخدام وظائف Excel الإضافية لاكتشاف التناقض
• كيف يمكنك إنشاء قواعد التحقق من صحة البيانات لمنع التناقضات؟
• كلمتنا الأخيرة

فهم التناقضات في Excel

يمكن أن تنتج الاختلافات في الصفوف والأعمدة عن أخطاء الإدخال البشري أو الإدخالات المكررة أو المخالفات في تنسيق البيانات.

ألق نظرة على البيانات التالية. ستلاحظ أن خطأ إملائيًا في جنس والتر وايت وواين سينا ​​- Mael بدلاً من Male. في الواقع ، من السهل ملاحظة وإصلاح الأخطاء في هذا السيناريو. ولكن هذا ليس هو الحال مع جداول البيانات التي تحتوي على آلاف الصفوف والأعمدة.

للبحث عن هذه التناقضات وإدارتها ، يمكنك استخدام ميزات Excel التالية :

• عوامل التصفية: قم بتطبيق عوامل التصفية على أعمدة معينة لعرض القيم الفريدة واكتشاف البيانات غير المتسقة بسرعة.
• التنسيق الشرطي: استخدم قواعد التنسيق لتمييز الخلايا التي تفي بمعايير معينة أو البيانات غير المتطابقة بين الأعمدة.
• وظائف Excel المتقدمة: استخدم وظائف مثل MATCH و VLOOKUP لمقارنة مجموعات البيانات وتحديد التناقضات بينها.

من خلال فهم كيفية استخدام هذه الميزات في Excel ، يمكنك اكتشاف التناقضات وتصحيحها في مجموعات البيانات الخاصة بك بكفاءة.

5 طرق للبحث عن التناقضات في Excel

هناك 5 طرق رئيسية نحبها لمعالجة التناقضات في Excel:

1. مراجعة يدوية
2. الفلتره
3. تنسيق مشروط
4. وظائف Excel المتقدمة
5. وظائف Excel الإضافية

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

# 1 - البحث عن التناقضات عبر المراجعة اليدوية في Excel

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

فيما يلي بعض الخطوات التي يمكنك اتباعها لإجراء مراجعة يدوية:

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

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

# 2 - استخدام عامل التصفية في Excel لاكتشاف وإصلاح البيانات غير المتسقة

يعد استخدام عامل تصفية Excel من أسهل الطرق لاكتشاف البيانات غير المتسقة .

باستخدام الفلتر ، سنعزل البيانات غير الصحيحة في عمود الجنس في مجموعة البيانات التالية:

1. حدد فرز وتصفية في علامة التبويب الصفحة الرئيسية .

2. اختر تصفية - تأكد من وجود المؤشر في نطاق البيانات.

3. انقر فوق سهم القائمة المنسدلة في العمود الذي يحتوي على بيانات غير متسقة.

4. الآن ، قم بإلغاء تحديد كافة البيانات المطابقة الصحيحة. في حالتنا ، إنه ذكر . ثم انقر فوق "موافق" لحفظ التغييرات.

5. ستعرض ورقة عمل Excel الآن فقط الصفوف ذات القيم غير الصحيحة. لإصلاحها ، انقر فوق Find & Select في علامة التبويب الصفحة الرئيسية .

6. انقر فوق "استبدال" عند ظهور القائمة المنسدلة.

7. اكتب القيمة غير الصحيحة في Find what والقيمة الصحيحة في Replace with . ثم انقر فوق "استبدال الكل" .

8. سيظهر مربع حوار يؤكد التغييرات. انقر فوق "موافق" للمتابعة.

3 # - إبراز التناقضات باستخدام التنسيق الشرطي

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

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

سنستخدم نموذج جدول لنبين لك قوة التنسيق الشرطي:

1. حدد الجدول أو الصفوف والأعمدة المفضلة. نختار العمود أ (الشركة) والعمود ب (الدولة). ثم حدد تنسيق شرطي في علامة التبويب الصفحة الرئيسية .

2. حدد نوع القاعدة ثم القاعدة. نحن نختار تسليط الضوء على قواعد الخلية والقيم المكررة ...

3. الآن ، يتعين علينا تحديد ما إذا كان سيتم تمييز القيم المكررة أو الفريدة و Fill Color & Font لتنسيق الخلايا. بمجرد الانتهاء ، انقر فوق "موافق" .

4. يتم الآن تمييز جميع القيم المكررة بنص أحمر فاتح مع تعبئة بنص أحمر داكن.

# 4 - البحث عن التناقضات باستخدام وظائف Excel المتقدمة

وظائف IF و IS

لمقارنة الخلايا وتحديد الاختلافات ، يمكنك استخدام دالات IF و IS.

على سبيل المثال ، استخدمنا الصيغة =IF(A2=B2,"MATCH","NOT MATCHED")لمقارنة الخلايا A2 و B2. وقمنا بسحب الصيغة لتطبيقها على خلايا أخرى. لذا ، فإن الخلايا المتطابقة تعيد MATCH وإلا فلن تتطابق .

يمكنك دمج IF مع دالات IS مثل ISNUMBER أو ISTEXT للتحقق من أنواع بيانات معينة. =IF(ISNUMBER(B2), "Number", "NaN")على سبيل المثال ، ستُرجع الصيغة رقم في العمود c إذا كان B2 رقميًا و NaN على خلاف ذلك.

VLOOKUP و HLOOKUP و XLOOKUP

تعد وظائف VLOOKUP (عموديًا) و HLOOKUP (أفقيًا) مفيدة عند مقارنة القيم في أعمدة أو صفوف مختلفة من جدولين. للقيام بذلك ، استخدم الصيغة التالية: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) أو =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]).

استبدل lookup_value بالقيمة التي تريد البحث عنها في الجدول الآخر ، أو table_array بنطاق الخلايا في الجدول الثاني ، أو col_index_num أو row_index_num برقم فهرس العمود أو الصف الذي تريد إرجاع قيمة منه ، و [range_lookup] مع FALSE للمطابقات التامة أو TRUE لمطابقة تقريبية.

في البرنامج التعليمي التالي ، نستخدم وظيفة VLOOKUP في العمود E لمقارنة أسماء الموظفين في العمود A والعمود D لسحب رواتبهم من العمود B إذا تم العثور على تطابق.

XLOOKUP هو إصدار محدث من VLOOKUP و HLOOKUP متاح في Excel 2021 و Excel 365. إلى صيغة XLOOKUP هي =XLOOKUP(lookup_value, lookup_array, return_array,[if_not_found]):.

استبدل lookup_value بالقيمة التي تريد البحث عنها في الجدول الآخر ، وابحث عن نطاق الخلايا للبحث في الجدول الثاني ، و return_array بالمصفوفة التي تريد إرجاع قيمة منها ، و [if_nout_found] بقيمة نصية إذا لم يكن هناك تطابق تم العثور على القيم.

إليك عينة سريعة من XLOOKUP بنفس الجداول:

مباراة

يمكن أيضًا استخدام وظيفة MATCH لمقارنة قائمتين للتناقضات.

تبحث وظيفة MATCH ، =MATCH(lookup_value, lookup_array, [match_type])عن عنصر محدد في نطاق وترجع الموضع النسبي للعنصر داخل هذا النطاق.

هنا ، نجد موضع البطيخ في المصفوفة A2: A8 باستخدام MATCH. نحن نستخدم match_type كـ 0 للعثور على القيمة الأولى التي تساوي تمامًا lookup_value .

# 5 - استخدام وظائف Excel الإضافية لاكتشاف التناقض

يوفر Excel العديد من الوظائف الإضافية والأدوات المضمنة التي يمكن أن تساعدك في اكتشاف التناقضات وتحليلها بشكل أكثر كفاءة. في هذا القسم ، سنناقش بعض تلك الوظائف الإضافية المفيدة.

أولاً ، ضع في اعتبارك استخدام الوظيفة الإضافية Spreadsheet Inquire . يمكن أن يساعدك هذا في مقارنة مصنفين وتمييز الاختلافات خلية تلو الأخرى. لتمكين الوظيفة الإضافية Spreadsheet Inquire:

1. انقر فوق علامة التبويب ملف .

2. حدد "خيارات" ، ثم انقر فوق فئة الوظائف الإضافية .

3. في مربع "إدارة" ، حدد وظائف COM الإضافية وانقر فوق "انتقال" .

4. حدد مربع استعلام جدول البيانات وانقر فوق موافق .

5. بمجرد التمكين ، انتقل إلى علامة التبويب "استفسار " لاستخدام الأمر "مقارنة الملفات" .

أداة أخرى مفيدة لاكتشاف التناقض هي Analysis ToolPak . توفر هذه الوظيفة الإضافية وظائف إحصائية متقدمة وأدوات تحليل البيانات ، والتي يمكن أن تكون مفيدة عند تحليل التناقضات.

لتمكين Analysis ToolPak ، اتبع نفس الخطوات لتمكين الوظيفة الإضافية Spreadsheet Inquire. ولكن حدد وظائف Excel الإضافية في المربع إدارة وحدد مربع Analysis ToolPak (حزمة أدوات التحليل) .

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

كيف يمكنك إنشاء قواعد التحقق من صحة البيانات لمنع التناقضات؟

لإنشاء قواعد التحقق من صحة البيانات في Excel والتي يمكن أن تساعد في منع التناقضات ، اتبع الخطوات التالية:

1. حدد الخلايا التي تريد تقييدها. يمكن أن يكون هذا خلية مفردة أو نطاق من الخلايا أو عمود بأكمله.

2. انتقل إلى علامة التبويب "البيانات" في شريط الأدوات ، ثم انقر فوق الزر "التحقق من صحة البيانات " (تم تمييزه بمربعين أفقيين وعلامة اختيار خضراء ودائرة حمراء متقاطعة).

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

هناك أنواع مختلفة من معايير التحقق المتوفرة في Excel ، وبعضها يشمل:

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

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

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

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

كلمتنا الأخيرة

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

• استخدام القوائم المنسدلة للتصفية في الأعمدة لتحديد القيم الفريدة والبيانات غير المتسقة.
• استخدام التنسيق الشرطي في الخلايا المحددة لتمييز التناقضات باستخدام مجموعة من قوالب القواعد.
• تطبيق وظائف Excel المتقدمة مثل وظائف MATCH و VLOOKUP و XLOOKUP وما إلى ذلك للتحقق من البيانات في قائمتين أو جدولين.
• استخدام وظائف Excel الإضافية لاكتشاف التناقضات.
• إعداد قواعد التحقق من صحة البيانات لمنع الأخطاء في البيانات

باستخدام هذه التقنيات في مجموعة مهارات Excel الخاصة بك ، ستكون بياناتك أكثر دقة وموثوقية.

تذكر ممارسة هذه الأساليب بانتظام لتصبح أكثر كفاءة في اكتشاف التناقضات في جداول البيانات الخاصة بك.

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