تطبيق التحقق من صحة البيانات على الخلايا

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

تنزيل الأمثلة التي نقدمها

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

تنزيل أمثلة التحقق من صحة البيانات في Excel‏

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

ملاحظة: إن الخطوات الثلاثة الأولى الموجودة في هذا القسم مخصصة لإضافة أي نوع من أنواع التحقق من صحة البيانات. والخطوات من 4 إلى 8 مخصصة تحديداً لإنشاء قائمة منسدلة.

  1. حدد خلية أو أكثر للتحقق من الصحة.

  2. في علامة التبويب البيانات، في مجموعة أدوات البيانات، انقر فوق التحقق من صحة البيانات.

    التحقق من صحة البيانات الموجودة في علامة التبويب "بيانات"، المجموعة "أدوات البيانات"
  3. ضمن علامة التبويب إعدادات، في المربع السماح، حدد قائمة.

    علامة التبويب «التحقق من الصحة» في مربع الحوار «التحقق من صحة البيانات»
  4. في المربع مصدر، اكتب قيم القائمة الخاصة بك مع فصلها بفواصل. على سبيل المثال:

    1. لتحديد خيارين فقط لإجابة (على سبيل المثال، "هل لديك أطفال؟")، اكتب نعم، لا.

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

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

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

      إدخال اسم ذي معنى للقائمة في المربع «الاسم»

    والآن، بدلاً من كتابة قيم القائمة في المربع المصدر للتحقق صحة من البيانات، أضف الاسم الذي قمت بتحديده للتو، مسبوقاً بعلامة التساوي (=).

    بدء اسم الجدول بعلامة التساوي =

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

    ملاحظة: من المستحسن وضع قوائمك بورقة عمل منفصلة (مخفية إذا لزم الأمر) حتى لا يتمكن أي شخص من تحريرها.

  5. تأكد من تحديد خانة الاختيار قائمة منسدلة في الخلية. وإلا، فلن تتمكن من مشاهدة السهم المنسدل الموجود بجانب الخلية.

    ظهور قائمة منسدلة في الخلية بجانب الخلية
  6. لتحديد الطريقة التي تريد بها معالجة القيم الفارغة (الخالية)، قم بتحديد خانة الاختيار تجاهل الفراغ أو إلغاء تحديدها.

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

  7. اختبر التحقق من صحة البيانات للتأكد من عمله بشكلٍ صحيح. جرب إدخال بيانات صحيحة وأخرى غير صحيحة في الخلايا للتأكد من عمل الإعدادات كما هو مطلوب وللتأكد من ظهور الرسائل عند الحاجة إليها.

ملاحظات: 

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

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

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

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

للقيام بذلك:

اتبع الخطوات التالية:

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

  1. اتبع الخطوات من 1 إلى 3 في إضافة التحقق من صحة البيانات إلى خلية أو نطاق أعلاه.

  2. من القائمة السماح، حدد عدد صحيح.

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

  4. أدخل قيمة الحد الأدنى أو الحد الأقصى أو قيمة معينة للسماح.

    مربع حوار معايير التحقق من الصحة

    يمكنك أيضاً إدخال صيغة التي ترجع قيمة رقمية.

    على سبيل المثال، لنفترض أنك كنت تتحقق من صحة البيانات في الخلية F1. لتعيين حد أدنى للخصومات إلى ضعف عدد الأطفال في تلك الخلية، حدد أكبر من أو يساوي في المربع بيانات ثم أدخل الصيغة ‎=2*F1 في المربع الحد الأدنى.

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

  1. اتبع الخطوات من 1 إلى 3 في إضافة التحقق من صحة البيانات إلى خلية أو نطاق أعلاه.

  2. حدد عدد عشري في المربع السماح.

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

  4. أدخل قيمة الحد الأدنى أو الحد الأقصى أو قيمة معينة للسماح.

    يمكنك أيضاً إدخال صيغة تُرجع قيمة رقمية. على سبيل المثال، لتعيين الحد الأقصى للعمولات والمكافآت إلى 6% من مرتب مندوب مبيعات في الخلية E1، حدد أقل من أو يساوي في المربع البيانات، ثم أدخل الصيغة، ‎=E1*6%، في المربع الحد الأقصى.

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

قم بتقييد إدخال البيانات إلى التواريخ التي تقع ضمن نطاق تاريخ.

  1. اتبع الخطوات من 1 إلى 3 في إضافة التحقق من صحة البيانات إلى خلية أو نطاق أعلاه.

  2. حدد تاريخ في المربع السماح.

  3. حدد نوع التقييد الذي تريده في المربع بيانات. على سبيل المثال، للسماح بتواريخ بعد يوم معين، حدد أكبر من.

  4. أدخل تاريخ البدء أو الانتهاء أو تاريخاً محدداً للسماح.

    يمكنك أيضاً إدخال صيغة تًرجع تاريخاً. على سبيل المثال، لتعيين إطار زمني بين تاريخ اليوم وثلاثة أيام من تاريخ اليوم، حدد بين في المربع البيانات، أدخل ‎=TODAY()‎ في المربع تاريخ البدء، ثم أدخل ‎=TODAY()+3 في المربع تاريخ الانتهاء.

    إعدادات معايير التحقق من الصحة لتقييد إدخال التاريخ إلى إطار زمني معين

قم بتقييد إدخال البيانات إلى وقت يقع ضمن حدود إطار زمني.

  1. اتبع الخطوات من 1 إلى 3 في إضافة التحقق من صحة البيانات إلى خلية أو نطاق أعلاه.

  2. حدد وقت في المربع السماح.

  3. حدد نوع التقييد الذي تريده في المربع بيانات. على سبيل المثال، للسماح بأوقات قبل وقت معين من اليوم، حدد أصغر من.

  4. أدخل وقت البدء أو وقت الانتهاء أو وقتاً محدداً تريد السماح به. إذا أردت إدخال أوقات محددة، فاستخدم تنسيق الوقت س س: د د.

    على سبيل المثال، نفترض أنه تم إعداد الخلية E2 بوقت بدء (8:00 ص) والخلية F2 بوقت انتهاء (5:00 م)، وتريد تحديد أوقات الاجتماعات بين هذين الوقتين، فعليك تحديد بين في المربع بيانات وإدخال ‎=E2 في المربع وقت البدء، ثم إدخال ‎=F2 في المربع وقت الانتهاء.

    إعدادات التحقق من الصحة لتقييد إدخال الوقت ضمن إطار زمني

قم بتقييد إدخال البيانات إلى نص لا يزيد طوله عن طول محدد.

  1. اتبع الخطوات من 1 إلى 3 في إضافة التحقق من صحة البيانات إلى خلية أو نطاق أعلاه.

  2. حدد طول النص في المربع السماح.

  3. حدد نوع التقييد الذي تريده في المربع بيانات. على سبيل المثال، للسماح بعدد أحرف معين كحد أقصى، حدد أصغر من أو يساوي.

  4. في هذه الحالة مثلاً نريد تقييد الإدخالات إلى 25 حرفاً فقط. لفعل ذلك قم بتحديد أقل أو يساوي في مربع البيانات ثم أدخل ‏25 ‏في مربع الحد الأقصى.

    مثال للتحقق من صحة البيانات بطول نص محدد

احسب المحتوى المسموح به وفقاً لمحتوى خليةٍ أخرى.

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

  2. حدد نوع التقييد الذي تريده في المربع بيانات.

  3. انقر فوق الخلية التي تريد استخدامها لتحديد المسموح به في المربع أو المربعات الموجودة أسفل المربع بيانات.

    على سبيل المثال، للسماح بإجراء إدخالات لحساب فقط إذا كانت النتيجة لا تتجاوز الموازنة الموجودة في الخلية E1، حدد السماح >العدد الصحيح، البيانات، أقل من أو تساوي، والحد الأقصى >= ‎=E1

    إعدادات التحقق من الصحة للحساب استناداً إلى محتوى الخلية الأخرى

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

للتأكد من

أدخل الصيغة التالية

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

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

مثال 6: الصيغ في التحقق من صحة البيانات

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

=ISTEXT(D2)

مثال 2: الصيغ في التحقق من صحة البيانات

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

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

مثال للتحقق من صحة البيانات لتقييد إدخال إلى سن أدنى.

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

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

مثال 4: الصيغ في التحقق من صحة البيانات

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

لمزيد من المعلومات

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

‎=ISUMBER(FIND("@",B4)

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

  • لماذا أمر "التحقق من صحة البيانات" غير ممكن على الشريط؟ قد يكون الأمر غير متوفر بسبب:

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

    • ربما تقوم حالياً بإدخال البيانات أمر التحقق من صحة البيانات غير مُمَكن على علامة التبويب بيانات أثناء إدخال البيانات في خلية. لإنهاء عملية إدخال البيانات، اضغط على مفتاح الإدخال Enter أو مفتاح الخروج ESC.

    • قد تكون ورقة العمل محمية أو مشتركة لا يمكنك تغيير إعدادات التحقق من صحة البيانات إذا كان المصنف مشتركاً أو محمياً. لمعرفة المزيد من المعلومات حول كيفية إيقاف مشاركة أو إزالة حماية من مصنف، راجع حماية مصنف.

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

  • هل توجد طريقة يمكن من خلالها التحديد التلقائي أو التعبئة التلقائية للتحقق من صحة البيانات أثناء الكتابة؟ لا، ولكن إذا استخدمت مربع تحرير وسرد ActiveX‏، فستتوفر لديك هذه الوظيفة.

  • هل يمكن إجراء تحديدات متعددة في قائمة التحقق من صحة البيانات؟ لا يمكن ذلك إلا إذا استخدمت تحرير وسرد ActiveX‏ أو مربع قائمة.

  • هل يمكن تحديد عنصر في قائمة التحقق من صحة البيانات وتعبئة قائمة أخرى من خلاله؟ نعم! يسمى هذا "التحقق من صحة البيانات التابعة". للحصول على مزيد من المعلومات، راجع إنشاء قوائم منسدلة تابعة.

  • كيف يمكنني إزالة كل عمليات التحقق من صحة البيانات في ورقة عمل؟ يمكنك استخدام مربع الحوار الانتقال إلى > خاص. ضمن علامة التبويب الشريط الرئيسي > تحرير > بحث وتحديد (أو اضغط على F5 أو Ctrl+G على لوحة المفاتيح)، ثم خاص > التحقق من صحة البيانات وحدد إما الكل(للبحث عن كل الخلايا ذات عمليات التحقق من صحة البيانات) أو نفس (للبحث عن الخلايا التي تطابق الإعدادات المحددة لعملية التحقق من صحة البيانات).

    الانتقال إلى مربع الحوار «خاص»

    بعد ذلك، قم باستدعاء مربع حوار التحقق من صحة البيانات (علامة التبويب «البيانات» > التحقق من صحة البيانات)، واضغط على زر مسح الكل، ثم موافق.

  • هل يمكن إجبار شخص على إجراء إدخال في خلية (خلايا) باستخدام التحقق من صحة البيانات؟ لا، ولكن يمكنك استخدام VBA‏ (Visual Basic for Applications) للتحقق مما إذا كان هناك شخص قد أجرى إدخالاً في حالات معينة، مثل قبل حفظه للمصنف أو إغلاقه. إذا لم تكن قد أجريت تحديداً، يمكنك إلغاء الحدث وعدم السماح له بالمتابعة حتى يتم إجراء تحديد.

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

    تنسيق الخلايا التي تحتوي على خيار فقط
  • كيف يمكن التحقق من صحة عنوان بريد إلكتروني؟ يمكنك استخدام الأسلوب مخصص > الصيغة والتحقق مما إذا كان الرمز @ موجوداً في الإدخال. في هذه الحالة، تكون الصيغة المستخدمة هي ‎=ISNUMBER(FIND(“@”,D2)). تبحث الدالة FIND عن الرمز @، وفي حالة العثور عليه، تقوم بإرجاع الموضع الرقمي الخاص به في السلسلة النصية وتسمح بالإدخال. وفي حالة عدم العثور عليه، تقوم الدالة FIND بإرجاع خطأ وتمنع الإدخال.

هل لديك سؤال يتعلق بدالة محددة؟

نشر سؤال في منتدى مجتمع Excel‏

ساعدنا في تحسين Excel

هل لديك اقتراحات حول الطريقة التي يمكننا من خلالها تحسين الإصدار التالي من Excel؟ إذا كان الأمر كذلك، فالرجاء الاطلاع على المواضيع في Excel User Voice.

انظر أيضاً

مزيد من المعلومات حول التحقق من صحة البيانات

فيديو: إنشاء قوائم منسدلة وإدارتها

إضافة عناصر إلى قائمة منسدلة أو إزالتها منها

إزالة قائمة منسدلة

توسيع المهارات
استكشاف التدريب
الحصول على الميزات الجديدة أولاً
الانضمام إلى Office Insider

هل كانت المعلومات مفيدة؟

نشكرك على ملاحظاتك!

شكراً لك على الملاحظات! يبدو أنه من المفيد إيصالك بأحد وكلاء دعم Office لدينا.

×