Data validation التحقق من البيانات excel 2021
ما هو التحقق من صحة البيانات في إكسل؟
التحقق من صحة البيانات في إكسل هو أداة تساعدك على ضمان صحة البيانات في ورقة العمل الخاصة بك. يمكنك استخدامها لتحديد أنواع البيانات المسموح بها في الخلايا، وفرض قيود على النطاقات، ومنع إدخال البيانات غير الصحيحة.
هناك العديد من أنواع التحقق من صحة البيانات المتاحة، بما في ذلك:
تكمن وظيفة هذا التبويب في تحديد الشروط الخاصة بإدخال البيانات. حيث يتم وضع Validation Criteria معايير التحقق من الصحة، وفقاً لما سيتم اختياره من القائمة Allow:
1. أي قيمة Any Value وهي الحالة التلقائية العادية حيث يسمح اكسل الكتابة ضمن الخلايا دون أي تقييد.
2. الأرقام الصحيحة Whole number، يشترط أن لا تحتوي الأرقام هنا على الفاصلة العشرية.
3. عشري Decimal، للتعامل مع القيم ذات الفاصلة العشرية.
4. قائمة List، لتقييد الإدخال بالاختيار من قائمة مخصصة من القيم النصية.
5. تاريخ Date، إدخال قيم من النوع “تاريخ” وفق شروط معينة.
6. وقت Time، إدخال البيانات ذات النوع “وقت” وبشروط محددة.
7. طول النص Text Length، أن لا تتجاوز البيانات النصية المدخلة عدد محارف معين.
8. مخصص Custom، لإنشاء صيغ Formula وهنا نستطيع استخدام الدالات البسيطة أو المركبة. بناء عليه، يجب أن تكون خبيراً في توظيف الدالات لإنشاء شروط معينة لإدخال البيانات.
رسالة الإدخال Input Message
رسالة إدخال التحقق من صحة البيانات في إكسل
التحقق من صحة البيانات في إكسل، لتبسيط الأمور وتوضيحها على مدخل البيانات، نكتب في هذا التبويب الرسالة النصية التي توضح شرط إدخال البيانات.
رسالة الخطأ Error Alert
رسالة خطأ التحقق من صحة البيانات في إكسل
التحقق من صحة البيانات في إكسل، يتم في هذا التبويب إعداد الرسالة النصية التي ستظهر في حال مخالفة شرط الإدخال وكتابة بيانات غير صالحة. من الضروري تفعيل الخانة عند Show error alert after invalid data is entered وذلك لإظهار الرسالة التحذيرية بعد إدخال البيانات الغير صحيحة.
أنواع الرسائل التحذيرية
من القائمة Style النمط، يتم اختيار نوع الرسالة التحذيرية، حيث يوجد 3 أنواع كالآتي:
الإيقاف Stop
يعد الخيار الأفضل لمنع إدخال البيانات الغير صالحة في الخلية. تظهر رسالة الخطأ في صندوق الحوار الناتج بالإضافة إلى أيقونة X حمراء على اليسار. هنالك زرين Retry إعادة المحاولة، Cancel إلغاء. يمكن النقر على الزر Retry لإعادة إدخال البيانات وفقاً للشرط المطلوب، أما عند النقر على الزر إلغاء Cancel فيتم تجاهل عملية الكتابة في الخلية.
التحذيرية Warning
هذه الرسالة تلفت نظر المستخدم إلى أن البيانات غير صالحة، لكنها لا توقف عملية الإدخال. تظهر أيقونة لمثلث أصفر يحتوي على علامة تعجب. أما الأزرار فهي ثلاثة؛ نعم Yes بحيث يتم قبول القيم التي لا تحقق الشرط، لا No لتعديل القيمة المدخلة، إلغاء Cancel لحذف القيمة المدخلة.
المعلومات Information
رسالة إخبارية للمستخدم تفيد بأن البيانات المدخلة غير صالحة. أيقونة هذه الرسالة دائرة زرقاء تحتوي على حرف i أبيض. في الحقيقة هذا النوع من الرسائل التحذيرية غير فعال حيث لا تتم عملية إيقاف إدخال البيانات الغير صحيحة. تحتوي النافذة على زرين Ok لقبول البيانات مع أنها لا تحقق الشرط، و Cancel للإلغاء
أمثلة عن التحقق من صحة البيانات المدخلة في إكسل
إدخال تاريخ بعد تاريخ اليوم الحالي
بما أن الشرط المطلوب لإدخال البيانات هو أن يكون التاريخ أكبر تماماً من تاريخ اليوم الحالي، سنختار معامل المقارنة أكبر من Greater than وعند Start date سيتم استخدام دالة Today.
التحقق من صحة البيانات في إكسل – التاريخ
إدخال تواريخ سنة محددة فقط
قد يطلب منا التقيد بإدخال التواريخ لفترة زمنية محددة مثل سنة أو أكثر. وبالتالي لا بد من تحديد تاريخ البدء Start Date وتاريخ الانتهاء End Date.التحقق من صحة البيانات في إكسل وفقاً لتواريخ محددة
===============================================
=AND(WEEKDAY(A2)<>1,WEEKDAY(A2)<>7)
================================================
The formula =AND(WEEKDAY(A2)<>1,WEEKDAY(A2)<>7) returns TRUE if the day of the week in cell A2 is not Sunday or Saturday, and FALSE otherwise.
The WEEKDAY function returns the day of the week of a given date, with 1 being Sunday and 7 being Saturday. The AND function returns TRUE if all of its arguments are TRUE, and FALSE otherwise.
Therefore, the formula =AND(WEEKDAY(A2)<>1,WEEKDAY(A2)<>7) can be used to check if a given date is a weekday. This can be useful for a variety of purposes, such as filtering data or creating conditional formatting rules.
Here are some examples of how the formula can be used:
=AND(WEEKDAY(A2)<>1,WEEKDAY(A2)<>7)
This formula will return TRUE if the date in cell A2 is a weekday, and FALSE otherwise.
=SUMIF(A:A,"=AND(WEEKDAY(A2)<>1,WEEKDAY(A2)<>7)",B:B)
This formula will return the sum of the values in column B for all rows where the date in column A is a weekday.
=IF(AND(WEEKDAY(A2)<>1,WEEKDAY(A2)<>7),"Weekday","Weekend")
This formula will return the text "Weekday" if the date in cell A2 is a weekday, and the text "Weekend" otherwise.
Conditional formatting rules can also be used to highlight cells based on the result of the =AND(WEEKDAY(A2)<>1,WEEKDAY(A2)<>7) formula. For example, you could create a rule that highlights all cells where the date is a weekday in green.
Overall, the =AND(WEEKDAY(A2)<>1,WEEKDAY(A2)<>7) formula is a versatile tool that can be used for a variety of purposes.
================================
إنشاء قائمة نصية تحتوي على قيم ثابتة
التحقق من صحة البيانات في إكسل، تساعد طريقة إنشاء قوائم نصية منسدلة تحتوي على قيم ثابتة، في الحد من أخطاء الإدخال وتوحيد القيم النصية المدخلة. على سبيل المثال لإنشاء قائمة بأسماء الأقسام الموجودة في الشركة، من صندوق الحوار Data Validation، ننتقي من القائمة Allow الخيار List ثم عند Source نستطيع كتابة اسم كل قسم مباشرة بحيث يفصل بين الأسماء فاصلة عادية أو منقوطة حسب نسخة الإكسل.
أما الطريقة الثانية،
فتكون بوجود أسماء الأقسام في خلايا معينة ضمن ورقة العمل وبشكل عمودي متتالي، وفي هذه الحالة نضع المؤشر عند Source ثم نقوم بتحديد الخلايا لتظهر مراجعها هنا.إنشاء قائمة التحقق من صحة البيانات في إكسل
منع تكرار القيم
عدم السماح بالقيم المكررة، يمكن تطبيق هذا المثال على الأرقام الخاصة بالموظفين، بحيث يكون لكل موظف رقم واحد. نحدد الخلايا المطلوبة، ومن صندوق الحوار Data validation نختار Custom من القائمة Allow ومن ثم نكتب الصيغة الآتية:
COUNTIF($A$2:$A$10;A2)=1=
فائدة الدالة COUNTIF تكمن في حساب عدد مرات تواجد القيمة في الخلية. فإذا لم تكن موجودة سابقاً سترجع الدالة القيمة 1 وسيتم قبول القيمة المدخلة إلى الخلية، أما إذا كانت القيمة موجودة سترجع الدالة قيمة أكبر من 1 وبالتالي سيتم رفض القيمة المدخلة.عدم تكرار القيم في التحقق من صحة البيانات في إكسل
إدخال القيم الرقمية فقط
السماح بالقيم الرقمية فقط ضمن الخلايا، يمكن استخدام هذا الشرط مع الرواتب على سبيل المثال. نحدد خلايا الرواتب وفي صندوق حوار التحقق من الصحة نكتب الصيغة الآتية:
ISNUMBER (F2)=
حيث بدءا من الخلية الأولى F2 سيتم فحص القيم المدخلة فإذا لم تكن أرقاماً فلن يتم قبولها أبداً.التحقق من صحة البيانات في إكسل للأرقام
طول النص
يتم تقييد إدخالات الخلايا ذات نوع البيانات نص، بحيث لا تتجاوز عدد محارف معينة. على سبيل المثال، عدم تجاوز العنوان أكثر من 100 محرف. مع العلم أن المحرف Character قد يكون حرف أو رقم أو رمز أو فراغ.التحقق من صحة البيانات في إكسل وفق طول النص
التوقيت
إدخال الوقت ضمن فترة زمنية محددة. من الساعة الثامنة صباحاً حتى الساعة الرابعة بعد الظهر.التحقق من صحة البيانات في إكسل للوقت
تمييز الخلايا ذات البيانات الغير صالحة
تعد الحالة المثالية في العمل، هي تحديد الخلايا الفارغة وتطبيق قاعدة التحقق من الصحة عليها ومن ثم إدخال البيانات بشكل صحيح. لكن ما العمل إذا كانت البيانات موجودة مسبقاً؟
في هذه الحالة سنطبق التحقق من صحة البيانات في إكسل على الخلايا المطلوبة، ولدفع أي شك بوجود بيانات لا تحقق شرط الإدخال، سنختار الأمر إحاطة البيانات غير الصحيحة Circle Invalid Data وبالتالي ستظهر دوائر حمراء اللون حول الخلايا لتمييزها، ثم ببساطة نقوم بتعديل محتوى الخلايا وفق الشروط فتختفي الدوائر الحمراء.
وفي حال أردنا متابعة العمل وأجلنا عملية تدقيق البيانات وتصحيحها، فيمكننا اختيار الأمر مسح دوائر التحقيق Clear Validation Circles وبالتالي سيتم حذف الدوائر الحمراء الموجودة حول الخلايا.
استخدام القائمة لتحديد قائمة من القيم المسموح بها: يمكنك استخدام هذا النوع لضمان إدخال المستخدمين فقط قيمًا محددة في الخلايا. على سبيل المثال، يمكنك استخدام القائمة لتحديد قائمة من البلدان المسموح بها في عمود "الدولة".
استخدام النطاق لتحديد نطاق من القيم المسموح بها: يمكنك استخدام هذا النوع لضمان إدخال المستخدمين فقط قيمًا تقع ضمن نطاق معين. على سبيل المثال، يمكنك استخدام النطاق لتحديد نطاق من الأعمار المسموح بها في عمود "العمر".
استخدام الطول لتحديد الحد الأقصى أو الأدنى لعدد الأحرف المسموح بها: يمكنك استخدام هذا النوع لضمان عدم إدخال المستخدمين نصًا طويلًا جدًا أو قصيرًا جدًا في الخلايا. على سبيل المثال، يمكنك استخدام الطول لتحديد حد أقصى لعدد الأحرف المسموح بها في عمود "التعليقات".
استخدام الرقم لتحديد نوع الرقم المسموح به: يمكنك استخدام هذا النوع لضمان إدخال المستخدمين فقط أرقامًا صحيحة أو عشرية. على سبيل المثال، يمكنك استخدام الرقم لتحديد نوع الرقم المسموح به في عمود "الأسعار".
استخدام التاريخ والوقت لتحديد نوع التاريخ أو الوقت المسموح به: يمكنك استخدام هذا النوع لضمان إدخال المستخدمين فقط تواريخ أو أوقات صالحة. على سبيل المثال، يمكنك استخدام التاريخ والوقت لتحديد نوع التاريخ أو الوقت المسموح به في عمود "تاريخ الميلاد".
استخدام النص لتحديد نوع النص المسموح به: يمكنك استخدام هذا النوع لضمان إدخال المستخدمين فقط نصًا كاملًا أو نصًا رقميًا. على سبيل المثال، يمكنك استخدام النص لتحديد نوع النص المسموح به في عمود "الاسم".
الخلاصة
يمكن أن يكون التحقق من صحة البيانات أداة مفيدة لضمان صحة البيانات في ورقة العمل الخاصة بك. من خلال استخدام التحقق من صحة البيانات، يمكنك المساعدة في منع المستخدمين من إدخال بيانات غير صالحة، مما قد يؤدي إلى أخطاء في البيانات أو مشاكل أخرى.
إزالة التحقق من صحة البيانات في Excel
إذا لم تعد بحاجة إلى عملية تقييد إدخال البيانات وفق شروط معينة، يمكنك حذف التحقق من الصحة عن الخلايا بسهولة، كما يأتي:
1. حدد الخلايا التي طبقت عليها سابقاً التحقق من الصحة.
2. اختر من التبويب بيانات Data أمر التحقق من الصحة Data Validation.
3. من صندوق الحوار الناتج انقر فوق الزر مسح الكل Clear All.
التحقق من صحة البيانات في إكسل، إحدى الميزات الفعالة في برنامج الجداول الإلكترونية Excel للحصول على البيانات الصحيحة وذلك بتوحيد عملية الإدخال لتلافي الأخطاء التي قد تؤثر على نتائج مستقبلية فيما بعد. وكلما كنت خبيراً في استخدام الدالات وإعداد الصيغ المناسبة ستتمكن من تطويع هذه الميزة وفق احتياجاتك.
كيفية استخدام التحقق من صحة البيانات
لإنشاء قيد التحقق من صحة البيانات، اتبع الخطوات التالية:
حدد الخلايا التي تريد تطبيق القيد عليها.
في علامة التبويب "بيانات"، انقر فوق "التحقق من صحة البيانات".
في مربع الحوار "التحقق من صحة البيانات"، حدد نوع القيد الذي تريد استخدامه.
في قسم "إدخال البيانات"، حدد ما إذا كنت تريد السماح للمستخدمين بإدخال بيانات جديدة أو تعديل البيانات الموجودة.
في قسم "تحذير أو خطأ"، حدد ما إذا كنت تريد عرض رسالة تحذير أو خطأ عند يحاول المستخدم إدخال بيانات غير صالحة.
انقر فوق "موافق".