كيف يمكنك التحقق مما إذا كانت الخلية تحتوي على إحدى القيم العديدة في Excel؟
في العديد من سيناريوهات الأعمال أو التحليل أو مراجعة البيانات، قد تجد نفسك أمام قائمة من السلاسل النصية في العمود A وتحتاج إلى التحقق مما إذا كانت كل خلية تحتوي على أيٍّ من القيم المحددة—مثل تلك الموجودة في النطاق D2:D7. فعلى سبيل المثال، عند التعامل مع بيانات الاستبيانات أو السجلات أو قوائم المنتجات، يُعدّ تحديد ما إذا كانت الإدخالات تتضمّن كلمات مفتاحية أو رموز منتجات أو مصطلحات محظورة أمرًا بالغ الأهمية. إذا احتوت الخلية على أيٍّ من العناصر المدرجة في قائمتك، يمكنك جعل Excel يُرجعصواب؛ وإلا فإنه يُرجعخطأ، كما هو موضح في لقطة الشاشة أدناه. وتشرح هذه المقالة طرقًا عملية للتحقق مما إذا كانت الخلية تحتوي على إحدى القيم العديدة من نطاق آخر، مع تقديم حلول متعددة تناسب مختلف إصدارات Excel واحتياجات المستخدمين.
التحقق مما إذا كانت الخلية تحتوي على إحدى القيم العديدة من قائمة باستخدام الصيغ
عرض المطابقات إذا احتوت الخلية على إحدى القيم العديدة من قائمة باستخدام الصيغ
تمييز المطابقات إذا احتوت الخلية على إحدى القيم العديدة من قائمة باستخدام ميزة مفيدة
التحقق مما إذا كانت الخلية تحتوي على إحدى القيم العديدة باستخدام استخدم تنسيق الشروط
التحقق مما إذا كانت الخلية تحتوي على إحدى القيم العديدة من قائمة باستخدام الصيغ
لتحديد ما إذا كانت الخلية تحتوي على أيٍّ من القيم النصية الموجودة في نطاق آخر، يمكنك استخدام صيغة صفيفية فعّالة حتى مع مجموعات البيانات الكبيرة. وتُعد هذه الطريقة مثاليةً عندما تحتاج إلى نتائج على هيئة قيم منطقية ()صواب/خطأ) لاستخدامها في صيغ إضافية أو التصفية أو الاختبارات المنطقية. وتعمل هذه التقنية بسلاسة في معظم إصدارات Excel الحديثة.
أدخل الصيغة التالية في خلية فارغة (مثل الخلية B2 بجانب بياناتك الأصلية)، ثم اسحب مقبض التعبئة لأسفل لتطبيقها على الخلايا الأخرى حسب الحاجة. إذا احتوت الخلية على أيٍّ من القيم النصية الموجودة في نطاقك المحدد، فستُرجع «صواب»؛ وإلا فستُرجع «خطأ». راجع لقطة الشاشة:
نصائح وملاحظات:
- تنفّذ هذه الصيغة عملية تحقُّق غير حساسة لحالة الأحرف. إذا كنت بحاجة إلى التمييز بين الأحرف الكبيرة والصغيرة، ففكّر في استخدام عمود مساعد أو دمج الصيغ بطرق أكثر تقدُّمًا.
- لتكييف النتيجة لتكون «نعم» أو «لا» بدلاً من True/False، استخدم الصيغة المعدّلة التالية:
- D2:D7 هو نطاق القيم الخاص بك (القائمة)، وA2 هي الخلية التي تريد اختبارها.
- انتبه إلى الخلايا الفارغة أو البيانات غير النصية، إذ تتطلب دالة SEARCH إدخال نصٍّ صالح، وقد تُنتج القيم الفارغة نتائج غير متوقعة (مثل «True»).
عرض المطابقات إذا احتوت الخلية على إحدى القيم العديدة من قائمة باستخدام الصيغ
أحيانًا، قد يكون من الأكثر فائدة عرض القيم الفعلية من القائمة الظاهرة في كل خلية، بدلًا من الاكتفاء بعرض نتيجة «صواب» أو «خطأ». على سبيل المثال، عند فحص أوصاف المنتجات أو التعليقات للبحث عن كلمات مفتاحية محددة، قد ترغب في استرجاع جميع القيم المطابقة لتحليلها أو إعداد تقارير عنها. يمكنك استخدام الصيغة التالية لعرض جميع القيم المطابقة مفصّلة بفواصل، كما هو موضح هنا:
أدخل هذه الصيغة في خلية فارغة (مثل B2)، وستدرج جميع القيم منD2:D7الموجودة داخلA2، مفصولة بفواصل:
ملاحظة: هنا،D2:D7 هي مجموعة القيم المراد البحث عنها، وA2 هي الخلية المراد البحث فيها.
بعد كتابة الصيغة، اضغط علىCtrl + Shift + Enter، ثم اسحب مقبض التعبئة لأسفل لتطبيق الصيغة على الصفوف الأخرى، كما هو موضح في لقطة الشاشة الخاصة بالنتائج:
- دالةTEXTJOIN متوفرة فقط في Excel 2019 وOffice 365. أما في الإصدارات السابقة من Excel، فاستخدم الصيغة الصفيفية التالية: أدخلها في خلية فارغة، ثم اضغط علىCtrl + Shift + Enter.
اسحب الصيغة يمينًا لتغطية أكبر عدد ممكن من الأعمدة لالتقاط جميع المطابقات الممكنة، ثم اسحبها لأسفل عبر كل صف. وستظل الأعمدة الإضافية فارغة إذا كان عدد المطابقات أقل. ويُعدّ هذا التنسيق مفيدًا بشكل خاص عندما تحتاج إلى عرض المطابقات في أعمدة منفصلة:
إذا واجهت أخطاءً، فتأكد مجددًا من النطاقات، وتحقق من أن المنطقةD2:D7 صحيحة، وتأكد من استخدامك المحدد الصحيح لإعداداتك المحلية (فاصلة أو فاصلة منقوطة).
تمييز المطابقات إذا احتوت الخلية على إحدى القيم العديدة من قائمة باستخدام ميزة مفيدة
إذا كنت بحاجة إلى تمييز الكلمات المفتاحية أو العبارات التي تطابق أي قيمة من قائمتك داخل كل خلية بصريًّا، فهذا يساعدك على جعل البيانات المهمة بارزة للمراجعة أو اتخاذ الإجراءات بسرعة. صُمِّمت ميزةوضع علامات على الكلمات الرئيسيةفيKutools لـ Excelخصيصًا لهذا السيناريو، لتتيح لك تمييز جميع مثيلات الكلمات المحددة في نطاق البيانات بنقرة واحدة—بدون الحاجة إلى كتابة صيغ أو أكواد VBA. وتكون هذه الميزة لا غنى عنها في الأوراق الكبيرة أو مجموعات البيانات المعقدة، حيث يصبح الفحص اليدوي أمرًا غير عملي.
بعد تثبيتKutools لـ Excel، اتبع الخطوات التالية:
1. انتقل إلىKutools > نص > وضع علامات على الكلمات الرئيسيةلفتح مربع الحوار، كما هو موضح:
2. في مربع حواروضع علامات على الكلمات الرئيسية، قم بما يلي:
- اختر نطاق البيانات الهدف الخاص بك من مربعالنطاق.
- اختر أو أدخل الكلمات المفتاحية يدويًّا في مربعالكلمة المفتاحية (مفصولة بفواصل).
- حدّد لون التمييز باستخدام خيارلون خط الكلمة الرئيسية.
3. انقر فوقموافق. سيتم تمييز جميع مطابقات Word الموجودة ضمن نطاق التحديد باستخدام لون الخط الذي اخترته:
- تقوم هذه الميزة بتعديل تنسيق العرض الخاص بالكلمات المفتاحية المطابقة مباشرةً، مما يجعل مراجعة النتائج أو مشاركتها أكثر وضوحًا للفِرق التي قد لا تفهم مخرجات الصيغ.
- وهي فعّالة بشكل خاص عندما تكون قائمة الكلمات المفتاحية أو نطاق النص واسعًا جدًّا، أو عندما تحتاج إلى تمييز معايير متعددة في آنٍ واحد.
- تحقق دائمًا مرتين من تحديد النطاق ومدخلات الكلمات المفتاحية قبل التأكيد لتجنب التمييز غير المقصود.
تحقق مما إذا كانت الخلية تحتوي على إحدى القيم العديدة باستخدام استخدم تنسيق الشروط
ميزةاستخدم تنسيق الشروطالمضمنة في Excel هي طريقة فعّالة أخرى لتمييز الخلايا التي تحتوي على أي قيمة من القائمة التي حددتها. يُعد هذا الحل مثاليًا للتعرُّف البصري السريع على الصفوف ذات الصلة، خاصةً أثناء مراجعة البيانات أو التحقق من الأخطاء أو تنفيذ مهام الامتثال. وعلى عكسوضع علامات على الكلمات الرئيسيةالخاصة بـ Kutools، لا تتطلب هذه الطريقة أي إضافات، مما يجعلها متاحة مباشرةً في تثبيتات Excel القياسية.
إليك كيفية استخدام استخدم تنسيق الشروط مع صيغة:
- حدد الخلايا في نطاق البيانات الخاص بك (مثل)A2:A20) التي ترغب في مراقبتها.
- انتقل إلى علامة التبويبالصفحة الرئيسية، ثم انقر علىاستخدم تنسيق الشروط > قاعدة جديدة.
- في مربّع حوار القاعدة التنسيقية الجديدة، اختراستخدام صيغة لتحديد الخلايا التي سيتم تنسيقها.
- أدخل هذه الصيغة، بافتراض أن D2:D7 يحتوي على قيمك، وأن A2 هي أول خلية بيانات:
=SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$7,A2)))>0- انقر علىتنسيق، وعيّن التنسيق المطلوب (مثل تعبئة اللون)، ثم اضغط علىموافق.
سيتم الآن تمييز جميع الخلايا التي تحتوي على أي عنصر من القائمة D2:D7 تلقائيًّا.
- هذه الطريقة ديناميكية: بمجرد تحديث النطاق D2:D7، سيُعدَّل التنسيق تلقائيًّا.
- استخدم التنسيق الشرطي للعرض فقط: فهو يُبرز الخلايا بصريًّا دون إنشاء نتائج في عمود منفصل أو استخدامها في عمليات حسابية إضافية.
- التنسيق القائم على الصيغة باستخدام «تنسيق شرطي» قوي، لكنه قد يؤدي إلى تباطؤ الأداء مع مجموعات البيانات الكبيرة جدًّا بسبب الحسابات المتكررة.
- ضع في اعتبارك أن دالة SEARCH لا تميّز بين الأحرف الكبيرة والصغيرة. ولجعل هذا الأسلوب حساسًا لحالة الأحرف، قد تحتاج إلى استخدام تقنيات أكثر تقدمًا أو أعمدة مساعدة.
مقالات ذات صلة إضافية:
- مقارنة سلسلتين نصيتين أو أكثر في Excel
- إذا كنت ترغب في مقارنة سلسلتين نصيتين أو أكثر في ورقة العمل—مع مراعاة حساسية الحالة أو تجاهلها، كما هو موضح في لقطة الشاشة التالية—فستتناول هذه المقالة بعض الصيغ المفيدة التي تساعدك على تنفيذ هذه المهمة بسهولة في Excel.
- إذا احتوت الخلية على نص، فاعرضه في Excel
- إذا كانت لديك قائمة من السلاسل النصية في العمود A وقائمة من الكلمات المفتاحية، فستحتاج الآن إلى التحقق مما إذا كانت أيٌّ من هذه الكلمات المفتاحية تظهر في كل سلسلة نصية. ففي حال ظهور إحدى الكلمات المفتاحية في الخلية، يتم عرضها؛ أما إذا لم تظهر، فتُعرض خلية فارغة، كما هو موضح في لقطة الشاشة التالية.
- عدّ الكلمات المفتاحية التي تحتويها الخلية بناءً على قائمة
- إذا كنت ترغب في حساب عدد الكلمات المفتاحية التي تظهر في خلية بناءً على قائمة من الخلايا، فإن دمج دوال SUMPRODUCT وISNUMBER وSEARCH في Excel سيُسهّل عليك حل هذه المشكلة.
- البحث والاستبدال قيم متعددة في Excel
- عادةً ما تساعدك ميزة البحث والاستبدال في العثور على نص معيّن واستبداله بنص آخر، لكنك قد تحتاج أحيانًا إلى البحث عن قيم متعددة واستبدالها دفعة واحدة. على سبيل المثال، استبدال كل النصوص «Excel» بـ "Excel2019"، و«Outlook» بـ "Outlook2019"، وهكذا كما يظهر في لقطة الشاشة أدناه. وفي هذه المقالة، سأقدّم لك صيغة فعّالة لأداء هذه المهمة في Excel.
أفضل أدوات الإنتاجية لمكتبتك
عزِّز مهاراتك في 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 يومًا بكامل الميزات— بدون تسجيل، بدون بطاقة ائتمان
- أفضل قيمة— وفِّر مقارنةً بشراء الإضافات بشكل منفصل