كيف يمكن تطبيق قواعد تحقق من صحة بيانات متعددة على خلية واحدة في ورقة عمل Excel؟
في ورقة عمل Excel، من الشائع جدًّا تطبيق قاعدة تحقق واحدة على خلية، وهو ما يُسهم في الحفاظ على اتساق البيانات ودقتها. لكن قد تواجه سيناريوهات تتطلب فرض عدة معايير تحقق داخل نفس الخلية—مثل السماح إما بإدخال رقم صالح أو اختيار قيمة من قائمة محددة، أو الجمع بين شروط نصية معينة مع نطاق تواريخ مسموح به. ويُمكّنك التعامل مع هذه متطلبات التحقق الأكثر تعقيدًا في Excel من التحكّم الأفضل في عمليات إدخال البيانات، ومنع الأخطاء، ورفع جودة البيانات بشكل ملحوظ.
فيما يلي، يأخذك هذا المقال في جولة عبر أمثلة عملية متنوعة لفرض قواعد تحقق متعددة داخل خلية واحدة في Excel. ويستعرض كل مثال سيناريو فريدًا، لتتمكن من اختيار النهج الأنسب لاحتياجاتك الخاصة. بالإضافة إلى ذلك، تم تقديم طرق بديلة—مثل استخدام VBA—للتعامل مع الحالات التي تتطلب مرونة أكبر أو منطقًا أكثر تقدمًا.
تطبيق التحقق من صحة بيانات متعددة في خلية واحدة (المثال 1)
تطبيق التحقق من صحة بيانات متعددة في خلية واحدة (المثال 2)
تطبيق التحقق من صحة بيانات متعددة في خلية واحدة (المثال 3)
تطبيق التحقق من صحة بيانات متعددة باستخدام VBA (متقدم)
تطبيق التحقق من صحة بيانات متعددة في خلية واحدة (المثال 1)
افترض أنك تريد إعداد خلية تقبل القيم فقط إذا استوفت أحد الشرطين التاليين:
- إذا كانت القيمة المدخلة رقمًا، فيجب أن تكون أقل من 100.
- وإذا كانت القيمة نصًّا، فيجب أن يكون موجودًا في قائمة محددة (مثل النطاق D2 إلى D7).
غالبًا ما تظهر هذه الحالة عندما تحتاج إلى جمع رموز كمية أو إجابات فئوية معرفة مسبقًا ضمن نفس الحقل. ومن خلال دمج قواعد التحقق، يمكنك تجنُّب استخدام حقول منفصلة للأرقام والنصوص، مما يعزز الوضوح والكفاءة.
1. حدد الخلية أو النطاق الذي تريد تطبيق معايير التحقق المتعددة عليه. بعد ذلك، في علامة التبويببيانات، انقر فوقالتحقق من صحة البيانات > التحقق من صحة البياناتمن الشريط، كما هو موضح أدناه:
2. في مربع حوارالتحقق من صحة البيانات، انتقل إلى علامة التبويبالإعداداتوقم بالتكوين التالي:
- (1.) من قائمةالسماحالمنسدلة، اخترمخصص.
- (2.) في حقلالصيغة، أدخل الصيغة التالية:=OR(A2<$C$2,COUNTIF($D$2:$D$7,A2)=1)
ملاحظة:في هذه الصيغة، يُشيرA2 إلى خلية التحقق، ويشيرC2 إلى القيمة القصوى المسموح بها، بينما يحتويD2:D7 على قائمة الإدخالات النصية المسموح بها. حدّث هذه المراجع لتناسب ورقة عملك حسب الحاجة.
3. انقر فوقموافقللتطبيق. الآن، لن تقبل الخلايا المحددة سوى الإدخالات التي تكون إما أرقامًا أقل من 100 أو سلاسل نصية موجودة في D2:D7. وإذا حاول المستخدم إدخال قيمة لا تستوفي أيًّا من الشرطين، فسيعرض Excel تحذيرًا فوريًّا لإعلام المستخدمين على الفور بالإدخالات غير الصالحة.
هذه الطريقة مثالية للسيناريوهات البسيطة ذات حدود القواعد الواضحة. ومع ذلك، بالنسبة للمتطلبات الأكثر دقة أو تفاعلًا، مثل المطالبات الشرطية أو المنطق متعدد الخطوات، فقد تكون التحقق المعتمد على الصيغ محدودًا. وفي مثل هذه الحالات، يمكن أن توفر طريقة VBA المقدمة أدناه مرونة أكبر.
تطبيق التحقق من صحة بيانات متعددة في خلية واحدة (المثال 2)
في هذا السيناريو، قد ترغب في السماح بإدخال البيانات فقط إذا استوفت أحد هذين الشرطين:
- أن تكون القيمة المدخلة هي النص الدقيق «Kutools لـ Excel»
- أو أن تكون القيمة المدخلة تاريخًا يقع بين 12/1/2017 و12/31/2017
يُعد هذا النوع من التحقق المتعدد عمليًا عندما يتطلب نموذج الاستبيان أو البيانات الخاص بك إدخال رمز تأكيد (سلسلة نصية دقيقة) أو تاريخًا يقع ضمن نطاق مشروع معيّن.
1. افتح مربع حوارالتحقق من صحة البياناتللخلايا المستهدفة. في المربع الحواري، نفّذ الخطوات التالية:
- (1.) انتقل إلى علامة التبويبالإعدادات.
- (2.) اخترمخصصمن قائمةالسماحالمنسدلة.
- (3.) اكتب هذه الصيغة في مساحةالصيغة:=OR(A2=$C$2,AND(A2>=DATE(2017,12,1), A2<=DATE(2017,12,31)))
ملاحظة:هنا، تشير الخليةA2 إلى خلية التحقق، ويجب أن تحتوي الخليةC2على النص المستهدف «Kutools لـ Excel»، كما أن نطاق التاريخ مُعرَّف باستخدامDATE(2017,12,1) وDATE(2017,12,31). يُرجى تعديل المراجع وفقًا لإعدادات ورقتك.
2. أكد بالنقر فوقموافق. لن تسمح الخلية (أو الخلايا) بعد الآن سوى بالنص المحدد أو التاريخ ضمن النطاق المعرّف، وسيتم حظر أي نوع آخر من الإدخال أو أي نص خارج هذه الحدود، مع عرض ملاحظة فورية كما هو موضح هنا:
تعمل هذه الطريقة بشكل ممتاز في سيناريوهات الإدخال الصارمة التي تقتصر القيم المقبولة فيها على التطابق الدقيق أو التواريخ الثابتة. ومع ذلك، إذا تضمّن التحقق الخاص بك تبعيات معقدة أو عمليات حسابية أو تفاعلًا مباشرًا مع المستخدم، ففكّر في اعتماد حل مبني على VBA لمزيد من التحكم والمرونة.
تطبيق التحقق من صحة بيانات متعددة في خلية واحدة (المثال 3)
بالنسبة للمثال الثالث، دعنا ننظر في حالة يجب أن تسمح فيها الخلية فقط بالإدخالات التي تبدأ بنص معيّن ولها عدد أحرف معيّن:
- يجب أن تبدأ الخلية بـ «KTE» وأن تكون مكوّنة من 6 أحرف بالضبط
- أو أن تبدأ بـ «www» وأن تكون مكوّنة من 10 أحرف بالضبط
تُعد هذه المعايير شائعة عند فرض تنسيق موحّد للأكواد أو عناوين URL، ويساهم تطبيق فحوصات طول الأحرف والبادئة بشكل كبير في الحد من أخطاء الإدخال.
لحل هذه المسألة، قم بإعداد التحقق من صحة البيانات باستخدام الصيغة أدناه:
1. افتح مربع حوارالتحقق من صحة البيانات. في الإعدادات، أكمل الخطوات التالية:
- (1.) اختر علامة التبويبالإعدادات.
- (2.) اخترمخصصمن القائمة المنسدلة «السماح».
- (3.) في حقل الصيغة، أدخل:=OR(AND(LEFT(A2,3)=«KTE»,LEN(A2)=6),AND(LEFT(A2,3)="www",LEN(A2)=10))
ملاحظة:استبدلA2بمراجع الخلايا الفعلية الخاصة بك إذا لزم الأمر. كما يمكنك تغيير «KTE» و«www» وعدد الأحرف حسب سياقك الخاص.
2. انقر فوقموافق. لن تقبل الخلية الآن سوى القيم المتوافقة مع قواعد البادئة والطول التي حددتها. وسيؤدي أي إدخال يخالف أيًّا من هذين الشرطين إلى تشغيل خطأ تحقق، كما هو موضح:
تلميح:إذا كانت لديك أفكار أخرى حول عمليات التحقق متعددة المعايير، يمكنك إنشاء صيغ مخصصة باستخدام الدوال المضمنة في Excel لتلبية احتياجاتك.
يتمثّل أحد قيود التحقق القائم على الصيغ في صعوبة إدارته أو تعديله عندما تزداد قواعد التحقق تعقيدًا أو تفاعليةً—مثلًا، عند رغبتك في عرض رسائل خطأ مخصصة أو التعامل مع معايير تتغيّر ديناميكيًّا. وفي مثل هذه الحالات، يمكن لاعتماد حلٍّ مبني على VBA أن يعزّز المرونة بشكل كبير.
تطبيق التحقق من صحة بيانات متعددة باستخدام VBA (متقدم)
عندما لا يكفي التحقق القائم على الصيغ—مثل الحاجة إلى التحقق بناءً على مجموعة من العوامل، أو فرض قواعد تتغيّر وفقًا لقيم خلايا أخرى، أو عرض رسائل مخصصة في الوقت الفعلي—يمكنك استخدام ماكروهات VBA (Visual Basic for Applications) لتطبيق قواعد تحقق متقدمة أو ديناميكية داخل الخلية.
تشمل السيناريوهات النموذجية:
- التحقق من صحة الإدخال استنادًا إلى أكثر من شرطين في آنٍ واحد
- السماح بالتفاعل مع المستخدم، مثل عرض رسائل منبثقة تحتوي على إرشادات تفصيلية
- إعادة البيانات غير الصالحة تلقائيًا وتقديم تعليمات مخصصة
فيما يلي مثال لحلٍّ باستخدام VBA، حيث يجب أن تستوفي إدخالات البيانات فيB2أحد الشرطين التاليين:
- أن تكون عددًا صحيحًا بين 1 و50
- أو أن تكون إحدى الكلمات المسموح بها في النطاقD2:D5
ملاحظة:يمكنك تعديل المعايير أو نطاقات الهدف أو منطق التحقق في الكود حسب احتياجاتك.
1. اضغطAlt+F11 لفتح محررVisual Basic for Applications. في محرر VBA، انقر نقرًا مزدوجًا على ورقة العمل التي تريد إضافة التحقق المتعدد إليها في جزء المشروع. بعد ذلك، انسخ الماكرو التالي إلى نافذة الكود الخاصة بتلك الورقة:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ValidList As Range
Dim InputValue As Variant
Dim IsValid As Boolean
Dim xTitleId As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
' Only validate B2 (you can set this to your desired cell or range)
If Not Intersect(Target, Range("B2")) Is Nothing Then
InputValue = Target.Value
Set ValidList = Range("D2:D5") ' Change as needed
IsValid = False
' Check for whole number between 1 and 50
If IsNumeric(InputValue) And InputValue = Int(InputValue) Then
If InputValue >= 1 And InputValue <= 50 Then
IsValid = True
End If
End If
' Check if input matches allowed list
If WorksheetFunction.CountIf(ValidList, InputValue) > 0 Then
IsValid = True
End If
If Not IsValid Then
MsgBox "Entry must be an integer between 1 and 50 OR one of the values listed in D2:D5.", vbExclamation, xTitleId
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
End If
End If
End Sub2. جرّب إدخال قيم في الخليةB2. إذا أدخلت عددًا صحيحًا بين 1 و50، أو كلمة موجودة في النطاق D2:D5، فسيتم الاحتفاظ بالإدخال. وإلا، ستظهر رسالة وسيُحذف الإدخال غير الصالح فورًا. يمكنك ضبط خلية (أو خلايا) الهدف ونطاق القيم الصالحة داخل كود VBA بما يتوافق مع متطلباتك.
نصائح واستكشاف الأخطاء وإصلاحها:
- احفظ ملفك دائمًا قبل تشغيل VBA، إذ قد تؤدي التعليمات البرمجية غير المقصودة إلى فقدان البيانات.
- إذا كان جدولك يحتوي على خلايا تحقق متعددة، يمكنك تعديل الكود ليتحقق من أي نطاق تريده، وليس من الخلية B2 فقط.
- إذا لم يُنفَّذ الكود، فتأكد من تفعيل ماكروهات ووجود الكود في ورقة العمل الصحيحة.
- يمكنك تحسين الكود لعرض رسائل مخصصة أو تسجيل الإدخالات غير الصالحة، وفقًا لمتطلباتك.
تتميّز حلول VBA بمرونتها العالية وقدرتها على تلبية متطلبات التحقق المتقدمة. ومع ذلك، يجدر بك مراعاة أن الماكروهات تتطلب من المستخدمين تمكين VBA في بيئة Excel الخاصة بهم، وقد لا تكون متاحة في جميع إعدادات الأمان.
باختصار، عند تطبيق قواعد تحقق متعددة على خلية في Excel، يمكنك الاعتماد على الصيغ لتلبية معظم الاحتياجات البسيطة، أو استخدام VBA لإنشاء سير عمل أكثر ديناميكية وتعقيدًا للتحقق من البيانات. فكّر بعناية في الطريقة الأنسب لأهدافك ومستوى تعقيد بياناتك وبيئة المستخدم لتحقيق أفضل النتائج.
أفضل أدوات الإنتاجية لمكتبتك
عزِّز مهاراتك في Excel باستخدام Kutools لـ Excel، وعايش الكفاءة كما لم تفعل من قبل.يقدّم Kutools لـ Excel أكثر من 300 ميزة متقدمة لتعزيز الإنتاجية ووقت الحفظ.انقر هنا للحصول على الميزة التي تحتاجها أكثر من غيرها...
يجلب Office Tab واجهة ذات علامات تبويب إلى Office، ويجعل عملك أسهل بكثير
- تمكّن من التحرير والقراءة باستخدام علامات التبويب في Word وExcel وPowerPoint، وPublisher وAccess وVisio وProject.
- افتح وأنشئ مستندات متعددة في علامات تبويب جديدة داخل النافذة نفسها، بدلاً من فتح نوافذ جديدة.
- يزيد إنتاجيتك بنسبة 50% ويوفّر لك مئات نقرات الفأرة كل يوم!
جميع الإضافات من Kutools في برنامج تثبيت واحد!
Kutools for Office حزمةٌ تحتوي على إضافاتٍ مخصصة لتطبيقات Excel وWord وOutlook وPowerPoint، إلى جانب Office Tab Pro، مما يجعلها الخيار المثالي للفِرق التي تعمل عبر تطبيقات Office.
- حزمة شاملة واحدة— إضافات Excel وWord وOutlook وPowerPoint بالإضافة إلى Office Tab Pro
- برنامج تثبيت واحد، ترخيص واحد— الإعداد خلال دقائق (جاهز لـ MSI)
- يعمل بشكل أفضل معًا— إنتاجية ميسَّرة عبر تطبيقات Office
- تجربة مجانية لمدة 30 يومًا بكامل الميزات— بدون تسجيل، بدون بطاقة ائتمان
- أفضل قيمة— وفِّر مقارنةً بشراء الإضافات بشكل منفصل