التحقق من صحة البيانات في الخلايا

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

التحقق من صحة البيانات في Excel لـ Windows

الخطوات:

1. حدد الخلية (الخلايا) التي تريد إنشاء قاعدة تحقق من الصحة لها.

التحقق من صحة البيانات في الخلايا
التحقق من صحة البيانات في الخلايا

2. قم بتحديد البيانات >التحقق من صحة البيانات.

3. على علامة التبويب إعدادات، ضمن السماح، حدد أحد الخيارات التالية:

  • الرقم بالكامل: لتقييد الخلية لقبول الأرقام الصحيحة فقط.
  • الرقم العشري: لتقييد الخلية لقبول الأرقام العشرية فقط.
  • قائمة: لاختيار البيانات من القائمة المنسدلة.
  • التاريخ: لتقييد الخلية لقبول التاريخ فقط.
  • الوقت: لتقييد الخلية لقبول الوقت فقط.
  • طول النص: لتقييد طول النص.
  • مخصص: لاستخدام صيغة مخصصة.

4.  ضمن البيانات، حدد شرطًا من الشروط التالية لينطبق على قيمة الخلية:

  • تقع بين
  • لا تقع بين
  • تساوي
  • لا تساوي
  • أكبر من
  • أقل من
  • أكبر من أو تساوي
  • أصغر من أو تساوي

5. على علامة التبويب إعدادات، ضمن السماح، حدد أحد الخيارات التالية:

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

6. حدد موافق.

والآن، إذا حاول المستخدم إدخال قيمة غير صالحة، سيظهر العنصر المنبثق مع الرسالة التالية: “لا تتطابق هذه القيمة مع قيود التحقق من صحة البيانات لهذه الخلية”.

التحقق من صحة البيانات في Excel لـ macOS

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

تقييد إدخال البيانات

  1. حدد الخلايا التي تريد تقييد إدخال البيانات فيها.
  2. ثم على علامة التبويب بيانات، انقر فوق التحقق من صحة البيانات > التحقق من صحة البيانات.
  3. ملاحظة: إذا لم يكن الأمر التحقق من الصحة متوفرًا، فقد تكون الورقة محمية أو قد يكون المصنف مشتركًا. لا يمكنك تغيير إعدادات التحقق من صحة البيانات في حالة مشاركة المصنف أو حماية الورقة.
  4. في المربع السماح، حدد نوع البيانات التي تريد السماح بها، وقم بتعبئة المعايير والقيم المحددة.
  5. ملاحظة: سيتم تسمية المربعات التي تقوم فيها بإدخال القيم المحددة استنادًا إلى البيانات وتحديد المعايير التي اخترتها. على سبيل المثال، إذا اخترت التاريخ كنوع البيانات، ستتمكن من إدخال القيم القصوى في مربعي الحد الأدنى والحد الأقصى المسمى تاريخ البدء وتاريخ الانتهاء.

مطالبه المستخدمين بالإدخالات الصالحة

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

  1. حدد الخلايا حيث تريد مطالبة المستخدمين بإدخالات بيانات صالحة.
  2. ثم على علامة التبويب بيانات، انقر فوق التحقق من صحة البيانات > التحقق من صحة البيانات.
  3. ملاحظة: إذا لم يكن الأمر التحقق من الصحة متوفرًا، فقد تكون الورقة محمية أو قد يكون المصنف مشتركًا. لا يمكنك تغيير إعدادات التحقق من صحة البيانات في حالة مشاركة المصنف أو حماية الورقة.
  4. على علامة التبويب رسالة إدخال، حدد خانة الاختيار إظهار رسالة الإدخال عند تحديد الخلية.
  5. في مربع العنوان، اكتب عنوانًا لرسالتك.
  6. ثم في المربع رسالة إدخال، اكتب الرسالة التي تريد عرضها.

عرض رسالة خطأ عند إدخال بيانات غير صالحة

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

  1. حدد الخلايا التي تريد عرض رسالة الخطأ فيها.
  2. ثم على علامة التبويب بيانات، انقر فوق التحقق من صحة البيانات > التحقق من صحة البيانات.
  3. ملاحظة: إذا لم يكن الأمر التحقق من الصحة متوفرًا، فقد تكون الورقة محمية أو قد يكون المصنف مشتركًا. لا يمكنك تغيير إعدادات التحقق من صحة البيانات في حالة مشاركة المصنف أو حماية الورقة.
  4. على علامة التبويب تنبيه إلى الخطأ، ثم في المربع العنوان، اكتب عنوانًا لرسالتك.
  5. في المربع رسالة خطأ، اكتب الرسالة التي تريد عرضها إذا تم إدخال بيانات غير صالحة.
  6. نفّذ أحد الإجراءات التالية:
من أجل:على علامة نمط القائمة المنبثقة: حدد
مطالبه المستخدمين بتصحيح الخطا قبل المتابعةإيقاف
تحذير المستخدمين من أن البيانات غير صالحة، والمطالبة بتحديد نعم أو لا للإشارة إلى ما إذا كانوا يريدون الاستمرارتحذير
وتحذير المستخدمين من أن البيانات غير صالحة، ولكن مع عدم السماح لهم بالمتابعة بعد استبعاد رسالة التحذيرمهم
خيارات رسالة الخطأ عند إدخال بيانات غير صالحة

التحقق من صحة البيانات في Excel للويب

إضافة التحقق من صحة البيانات إلى خلية أو نطاق

ملاحظة: أول خطوتين في هذا القسم هي لإضافة أي نوع من التحقق من صحة البيانات. الخطوات 3-7 خصيصًا لإنشاء قائمة منسدلة.

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

ملاحظات

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

إضافة أنواع أخرى من التحقق من صحة البيانات

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

الأعداد

للقيام بذلك:اتبع الخطوات التالية:
تقييد إدخال البيانات إلى الأعداد الصحيحة فقط التي تقع ضمن الحدود1. اتبع الخطوات من 1-2 أعلاه.
2. من القائمة السماح، حدد عدد صحيح.
3. حدد نوع التقييد الذي تريده في المربع بيانات. على سبيل المثال، لتعيين حدود عليا وحدود دنيا، حدد بين.
4. أدخل قيمة الحد الأدنى أو الحد الأقصى أو قيمة معينة للسماح. يمكنك أيضًا إدخال صيغة معينة والتي ترجع قيمة رقمية. على سبيل المثال، لنفترض أنك كنت تتحقق من صحة البيانات في الخلية F1. لتعيين حد أدنى للخصومات إلى ضعف عدد الأطفال في تلك الخلية، حدد أكبر من أو يساوي في المربع بيانات ثم أدخل الصيغة ‎=2*F1 في المربع الحد الأدنى.
تقييد إدخال البيانات إلى الأعداد العشرية التي تقع ضمن الحدود1. اتبع الخطوات من 1-2 أعلاه.
2. حدد عدد عشري في المربع السماح.
3. حدد نوع التقييد الذي تريده في المربع بيانات. على سبيل المثال، لتعيين حدود عليا وحدود دنيا، حدد بين.
4. أدخل قيمة الحد الأدنى أو الحد الأقصى أو قيمة معينة للسماح. يمكنك أيضًا إدخال صيغة معينة والتي ترجع قيمة رقمية. على سبيل المثال، لتعيين حد أقصى لعمولات المرتبات والمكافآت التي تبلغ 6% من راتب البائع في الخلية E1، حدد أصغر من أو يساوي في المربع بيانات وأدخل الصيغة = E1 * 6%، في المربع الحد الأقصى. ملاحظة: للسماح لمستخدم بإدخال النسب المئوية، على سبيل المثال 20%، حدد الرقم العشري في المربع السماح، وحدد نوع التقييد الذي تريده في مربع البيانات، وأدخل الحد الأدنى أو الحد الأقصى أو القيمة المحددة كرقم عشري، على سبيل المثال 2، ثم اعرض خلية التحقق من صحة البيانات كنسبة مئوية من خلال تحديد الخلية والنقر فوق نمط النسبة المئوية صورة الزر في المجموعة الرقم ضمن علامة التبويب الصفحة الرئيسية.
سرد أنواع من التحقق من صحة البيانات في إكسل Excel، وطرق إضافتها إلى أوراق العمل – الأعداد

التاريخ

للقيام بذلك:اتبع الخطوات التالية:
تقييد إدخال البيانات إلى التواريخ التي تقع ضمن نطاق تاريخ1. اتبع الخطوات من 1-2 أعلاه.
2. حدد تاريخ في المربع السماح.
3. حدد نوع التقييد الذي تريده في المربع بيانات. على سبيل المثال، للسماح بتواريخ بعد يوم معين، حدد أكبر من.
4. أدخل تاريخ البدء أو الانتهاء أو تاريخًا محددًا للسماح. يمكنك أيضا إدخال صيغه تُرجع تاريخًا. على سبيل المثال، لتعيين إطار زمني بين تاريخ اليوم وزيادة 3 أيام من تاريخ اليوم، حدد بين في المربع بيانات، وادخل الصيغة = today () في مربع تاريخ البدء، وأدخل = اليوم () + 3 في مربع تاريخ الانتهاء.
تقييد إدخال البيانات إلى وقت يقع ضمن حدود إطار زمني1. اتبع الخطوات من 1-2 أعلاه.
2. حدد وقت في المربع السماح.
3. حدد نوع التقييد الذي تريده في المربع بيانات. على سبيل المثال، للسماح بأوقات قبل وقت معين من اليوم، حدد أصغر من.
4. أدخل وقت البدء أو وقت الانتهاء أو وقتًا محددًا تريد السماح به. إذا أردت إدخال أوقات محددة، فاستخدم تنسيق الوقت س س: د د. على سبيل المثال، نفرض أن لديك خلية E2 تم اعدادها باستخدام وقت البدء (8:00 ص)، والخلية F2 مع وقت الانتهاء (5:00 م)، وتريد تقييد أوقات الاجتماع بين تلك الأوقات، قم بتحديد بين في مربع البيانات، ثم أدخل = E2 في المربع وقت البدء، ثم أدخل = F2 في المربع وقت الانتهاء .
سرد أنواع من التحقق من صحة البيانات، وطرق إضافتها إلى أوراق العمل – تاريخ

النص

للقيام بذلك:اتبع الخطوات التالية:
تقييد إدخال البيانات إلى نص لا يزيد طوله عن طول محدد1. اتبع الخطوات من 1-2 أعلاه.
2. حدد طول النص في المربع السماح.
3. حدد نوع التقييد الذي تريده في المربع بيانات. على سبيل المثال، للسماح بعدد أحرف معين كحد أقصى.
4. حدد أصغر من أو يساوي. مثلا، لو أردنا الحد من الإدخال حتى 25 حرفًا، لذا قم بتحديد أصغر من أو يساوي في المربع بيانات وادخل 25 في المربع الحد الأقصى.
حساب المحتوى المسموح به وفقًا لمحتوى خليةٍ أخرى1. اتبع الخطوات من 1-2 أعلاه.
2. في المربع السماح، حدد نوع البيانات الذي تريده.
3. حدد نوع التقييد الذي تريده في المربع بيانات.
4. انقر فوق الخلية التي تريد استخدامها لتحديد المسموح به في المربع أو المربعات الموجودة أسفل المربع بيانات. على سبيل المثال، للسماح بإدخالات حساب فقط إذا لم تتجاوز النتيجة الميزانية الموجودة في الخلية E1، فحدد السماح > بالأرقام الكاملة أو البيانات التي تقل عنها (>) أو المساوية لها (=) أو الحد الأقصى = E1.
سرد أنواع أخرى من التحقق من صحة البيانات، وطرق إضافتها إلى أوراق العمل – النص

أمثلة على استخدام الصيغ في التحقق من صحة البيانات

ملاحظات

  • الأمثلة التالية تستخدم الخيار «مخصص» حيث تكتب صيغًا لتحديد شروطك، لا داعي للقلق حول ما يعرضه مربع البيانات، لأنه يتم تعطيله مع الخيار «مخصص».
  • تم أخذ لقطات الشاشة في هذه المقالة في Excel 2016؛ ولكن الوظيفة هي نفسها في Excel للويب.

1. تبدأ دائمًا الخلية التي تحتوي على معرف المنتج (C2) بالبادئة القياسية «-ID» كما تتكون من 10 أحرف على الأقل (أكثر من 9).

إدخال الصيغة التالية:

= AND (LEFT (C2, 3) = “ID-“, LEN (C2) >9)

مثال استخدام الصيغ في التحقق من أن البيانات لها بادئة محددة - أمثلة استخدام الصيغ في التحقق من صحة البيانات المدخلة
مثال استخدام الصيغ في التحقق من أن البيانات لها بادئة محددة

2. تحتوي الخلية التي تتضمن اسم المنتج (D2) على نص فقط.

إدخال الصيغة التالية:

‎=ISTEXT(D2)

مثال استخدام الصيغ في التحقق من احتواء الخلية على نص - أمثلة استخدام الصيغ في التحقق من صحة البيانات المدخلة
مثال استخدام الصيغ في التحقق من احتواء الخلية على نص

3. للتأكيد من أنه يجب أن تكون الخلية التي تحتوي على عيد ميلاد شخص (B6) أكبر من عدد السنوات في الخلية B4.

قم بإدخال الصيغة التالية:

‎= ‎‎IF(B6<=(TODAY()-(365*B4)),TRUE,FALSE)

مثال استخدام الصيغ في التحقق من السن - أمثلة استخدام الصيغ في التحقق من صحة البيانات المدخلة
مثال استخدام الصيغ في التحقق من السن

4. للتأكد من أن كل البيانات الموجودة في نطاق الخلايا A2:A10 تحتوي على قيم فريدة.

إدخال الصيغة التالية:

=COUNTIF($A$2:$A$10,A2)

مثال استخدام الصيغ في التحقق من القيم الفريدة
مثال استخدام الصيغ في التحقق من القيم الفريدة

‎ملاحظة: يجب أولا إدخال صيغة التحقق من صحة البيانات للخلية A2، ثم نسخ A2 إلى A3:A10 بحيث تتطابق الوسيطة الثانية للدالة COUNTIF مع الخلية الحالية، وسيؤدي ذلك إلى تغيير الجزء A2)=1 إلى A3)=1, A4)=1 وما إلى ذلك.

5. للتأكد من أن إدخال عنوان البريد الإلكتروني في الخلية B4 يحتوى على الرمز @.

قم بإدخال الصيغة التالية:

= ISNUMBER (FIND (“@”, B4))

مثال استخدام الصيغة في التأكد من احتواء عنوان البريد الإلكتروني على الرمز @
مثال استخدام الصيغة في التأكد من احتواء عنوان البريد الإلكتروني على الرمز @

المراجع

  • برنامج الجداول الإلكترونية مايكروسوفت إكسل Microsoft Excel، ترجمة وإعداد: د. م. مصطفى عبيد، مركز البحوث والدراسات متعدد التخصصات، 2021.
  • الموقع الرسمي لشركة مايكروسوفت Microsoft.
error:
Scroll to Top