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

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

الجدول المصدر مع القيم النصية

اسمحوا في النطاق ج9:أ17 توجد قائمة بقائمة الأدوات (انظر ملف المثال). تحتوي القائمة على كل من خلايا الكلمات المفردة والسلاسل النصية (أسطر متعددة مفصولة بمسافات).

المهمة 1 (البحث عن قيم في قائمة تطابق معيار واحد)

لنحدد الخلايا التي تحتوي على قيم تفي بمعيار واحد (بدون ). أدخل المعيار في الخلية ب 6 . بالإضافة إلى ذلك، سوف نقوم بتعيين 4 أنواع بحث:

• يطابق تمامًا (يحدد الخلايا التي تحتوي على قيم تتطابق تمامًا مع نص البحث)؛
• يحتوي على (يحدد الخلايا التي تحتوي على نص البحث في بداية السطر أو نهايته أو منتصفه)؛
• يبدأ (تحديد الخلايا التي تحتوي على نص البحث في بداية السطر)؛
• ينتهي (يحدد الخلايا التي تحتوي على نص البحث في نهاية السطر).

وكمثال على المعيار نستخدم النص " حفر"، والتي سوف ندخلها في الخلية ب 6 . وبطبيعة الحال، يمكن استخدام أي نص آخر للمعيار.

حل

نختار نوع البحث باستخدام المجموعة والتبديل. دعونا نربط كل شيء بالخلية ب9 . الآن عندما تحدد نوع البحث يطابق بالضبطستحتوي هذه الخلية على الرقم 1، عند التحديد يتضمن - 2, يبدأ - 3, ينتهي- 4 (انظر مثال ملف Sheet معيار واحد (نص)).

المهمة 2 (البحث عن الكلمات في القائمة التي تتطابق مع معيار واحد)

تحديد ما إذا كان هناك كلمةفي القائمة أكثر صعوبة إلى حد ما من تحديد ما إذا كان هناك نص معين موجود في القائمة. ويظهر الاختلاف في هذه الأساليب عندما تكون هناك كلمات في القائمة تحتوي على النص الذي تم البحث عنه، ولكنها لا تطابقه. على سبيل المثال، الكلمة الموجودة في القائمة التي تحتوي على Motor Drill نصالحفر، ولكن كلمةالتدريبات غير موجودة في هذه الخلية.

لتحديد الخلايا في القائمة التي تحتوي على كلمةحفر، تحتاج إلى استخدام الصيغ في ملف المثال على ورقة "معيار واحد (كلمة)".

من الواضح أنك بحاجة إلى تحديد الخلايا التي:

• يحتوي بالضبط على كلمة Drill OR؛
• يبدأ سطر النص بالكلمة Drill OR؛
• ينتهي سطر النص بكلمة Drill OR؛
• كلمة Drill في المنتصف سلسلة نصية(المعيار محاط بمسافات).

من حيث المبدأ، يمكنك كتابة صيغة واحدة كبيرة وعدم استخدام أعمدة إضافية (C:F)

OR(OR(IFERROR(A9=$A$6,0));
OR(IFERROR(SEARCH(" "&$A$6&" ";A9);0));
OR(IFERROR(SEARCH($A$6&" ";LEFT(A9,LENGTH($A$6)+1));0));
OR(IFERROR(" "&$A$6=RIGHT(A9,LENGTH($A$6)+1),0))
)

المهمة 3 (البحث عن عدة كلمات في القائمة)

فكر في القوائم التي تتكون عناصرها من كلمات فردية (وليست عبارات). سنختار فقط الخلية التي تحتوي على أي من الكلمات المعيارية (انظر مثال الملف Sheet عدة معايير (كلمة بالضبط)).

يمكن القيام بذلك بعدة طرق.

هذه الطريقة ليست مريحة للغاية، لأنها... عند تغيير المعايير، سيتعين عليك تغيير مجموعة الثوابت في مدير الأسماء.

الخيار باستخدام نطاق مسمى.صيغة =أو($B2=Words_in_range)مشابه للاسم السابق، ولكن الاسم Words_in_range يشير الآن إلى نطاق من خلايا الورقة (راجع المقالة). عند إضافة كلمات جديدة إلى الخلايا في العمود F، سيتعين عليك إعادة تعريف النطاق المسمى.

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

خيار دون استخدام الأسماء. صيغة =أو($D2="word1";$D2="word2";$D2="word3")يسمح لك برفض استخدام الأسماء. وسيكون ثمن ذلك هو الحاجة إلى تغيير القاعدة التنسيق الشرطيفي كل مرة تتغير كلمات المعايير.

المشكلة 4 (البحث عن عدة كلمات في القائمة، حالة عامة)

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

لا تظهر كلمة "فئة" في القائمة (لا يتم احتساب الانحرافات). لكن كلمة فني تظهر في بداية العبارة وفي وسطها ونهايتها.

على الرغم من أن الصيغ مشابهة لتلك المستخدمة في المشكلة 2، إلا أنها تختلف عنها بشكل أساسي، لأن نكون .

العمل مع مشروع VB (11)
التنسيق الشرطي (5)
القوائم والنطاقات (5)
وحدات الماكرو (إجراءات VBA) (62)
متنوعة (38)

ابحث عن أي كلمة من القائمة في الخلية

لنفترض أنك تتلقى من المورد/العميل/العميل جدولاً مكتملاً يحتوي على قائمة المنتجات:

ويجب مقارنة هذا الجدول بأرقام المقالات/أكواد المنتجات الموجودة في كتالوج المنتجات الموجودة لديك:

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



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

(49.5 كيلو بايت، 7,734 تنزيل)

يوجد في ورقة "الطلب" في هذا الملف جدول تم استلامه من العميل، وفي ورقة "الكتالوج" توجد مقالاتنا.
الصيغة نفسها، باستخدام ملف المثال، ستبدو كما يلي:

BROWSE(2,1/SEARCH(الدليل!$A$2:$A$11, A2);الدليل!$A$2:$A$11)
=LOOKUP(2,1/SEARCH(Directory!$A$2:$A$11,A2),Directory!$A$2:$A$11)
ستعيد هذه الصيغة اسم المقالة إذا كان النص يحتوي على مقالة واحدة على الأقل من الكتالوج و #غير متاح (#غير متاح)إذا لم يتم العثور على المادة في الكتالوج.
قبل أن نقوم بتحسين هذه الصيغة بجميع أنواع الإضافات (مثل إزالة #N/A غير الضرورية)، فلنتعرف على كيفية عملها.
تبحث الدالة LOOKUP عن القيمة المحددة (2) في النطاق المحدد (الصفيف - الوسيطة الثانية). النطاق عادة ما يكون عبارة عن مصفوفة من الخلايا، ولكن الدالة VIEW لديها الميزة الأولى التي نحتاجها - فهي تحاول تحويل أي تعبير مكتوب كوسيطة ثانية مباشرة إلى مصفوفة. بمعنى آخر، يقوم بتقييم التعبير في هذه الوسيطة، والذي نستخدمه عن طريق استبدال التعبير باعتباره الوسيط الثاني: 1/SEARCH(Directory!$A$2:$A$11;A2) . يقوم جزء البحث (الكتالوج!$A$2:$A$11;A2) بالبحث بدوره عن كل قيمة من قائمة الكتالوج في الخلية A2 (الاسم من جدول العملاء). إذا تم العثور على قيمة، فسيتم إرجاع رقم موضع الحرف الأول من القيمة التي تم العثور عليها. إذا لم يتم العثور على القيمة، فسيتم إرجاع قيمة الخطأ #VALUE! (#VALUE!). الآن الميزة الثانية: تتطلب الوظيفة ترتيب البيانات في المصفوفة بترتيب تصاعدي. إذا تم تحديد موقع البيانات بشكل مختلف، فستقوم الوظيفة بفحص المصفوفة حتى تجد قيمة أكبر من القيمة التي تم البحث عنها، ولكنها أقرب ما يمكن إليها (على الرغم من أنه إذا كانت البيانات تسمح بذلك، فلا يزال من الأفضل إجراء بحث أكثر دقة لفرز القائمة بترتيب تصاعدي). لذلك، نقسم 1 أولاً على التعبير SEARCH(Directory!$A$2:$A$11,A2) للحصول على مصفوفة من النموذج: (0.0181818181818182:#VALUE!:#VALUE!:#VALUE!:#VALUE!: #القيمة!:#القيمة!:#القيمة!:#القيمة!:#القيمة! !}
حسنًا، كقيمة مرغوبة نعطي الدالة رقمًا 2 - بوضوح عدد أكبر، مما يمكن العثور عليه عمومًا في المصفوفة (نظرًا لأن واحدًا مقسومًا على أي رقم سيكون أقل من اثنين). ونتيجة لذلك، سنحصل على الموضع في المصفوفة حيث تحدث آخر مطابقة من الدليل. وبعد ذلك ستتذكر الدالة VIEW هذا الموضع وترجع القيمة من دليل المصفوفة! $A$2:$A$11 (الوسيطة الثالثة) المكتوبة في هذا المصفوفة لهذا الموضع.
يمكنك الاطلاع على مراحل حساب الدالة بنفسك لكل خلية، وسأقوم هنا ببساطة بعرض المراحل بشكل موسع قليلًا للفهم:

1. =BROWSE(2,1/SEARCH(Directory!$A$2:$A$11, A2);Directory!$A$2:$A$11)
2. =عرض(2;
   1/(55:#VALUE!:#VALUE!:#VALUE!:#VALUE!:#VALUE!:#VALUE!:#VALUE!:#VALUE!:#VALUE!:#VALUE;!}
   الكتالوج!$A$2:$A$11)
3. =VIEW(2;(0.0181818181818182:#VALUE!:#VALUE!:#VALUE!:#VALUE!:#VALUE!:#VALUE!:#VALUE!:#VALUE!:#VALUE!:#VALUE;Каталог!$A$2:$A$11)!}
4. =عرض(2;
   1;
   ("FM2-3320": "CV455689": "Q5949X": "CE321A": "CE322A": "CE323A": "00064073": "CX292708": "CX292709": "CX292710"))
5. ="FM2-3320"

الآن دعونا نقوم بتحسين الوظيفة قليلاً ونجري المزيد من عمليات التنفيذ
التنفيذ 1:
بدلاً من أرقام المقالات و#N/A، سنعرض "نعم" للمواضع التي تم العثور عليها، و"غير موجود في الكتالوج" للمواضع المفقودة:
=IF(END(VIEW(2,1/SEARCH(Catalog!$A$2:$A$11,A2)));"غير موجود في الكتالوج"،"نعم")
=IF(ISNA(LOOKUP(2,1/SEARCH(Directory!$A$2:$A$11,A2)))،,"غير موجود في الدليل"،"نعم")
تشغيل الدالة بسيط - لقد قمنا بفرز LOOKUP، لذا كل ما تبقى هو UND وIF.
UNM (ISNA) تعود حقيقيإذا كان التعبير الموجود بداخله يُرجع قيمة خطأ #غير متاح (#غير متاح)و خطأ شنيعإذا كان التعبير الموجود بالداخل لا يُرجع قيمة الخطأ هذه.
IF (IF) تُرجع ما تم تحديده بواسطة الوسيطة الثانية إذا كان التعبير في الوسيطة الأولى يساوي حقيقيوما تحدده الوسيطة الثالثة، إذا كان التعبير عن الوسيطة الأولى خطأ شنيع.

التنفيذ 2:
بدلاً من #N/A سوف نعرض "غير موجود في الكتالوج"، أما إذا تم العثور على المقالات فسنعرض أسماء هذه المقالات:
=IFERROR(VIEW(2,1/SEARCH(Catalog!$A$2:$A$11,A2),Catalog!$A$2:$A$11);"غير موجود في الكتالوج")
=IFERROR(LOOKUP(2,1/SEARCH(Catalog!$A$2:$A$11,A2),Catalogue!$A$2:$A$11),,"ليس في الكتالوج")
لقد تحدثت عن وظيفة IFERROR بالتفصيل في هذه المقالة:.
باختصار، إذا كان التعبير المحدد بواسطة الوسيط الأول للدالة يُرجع قيمة أي خطأ، فستُرجع الدالة ما هو مكتوب بواسطة الوسيط الثاني (في حالتنا، النص “غير موجود في الدليل”). إذا لم يُرجع التعبير خطأ، فستكتب الدالة IFERROR القيمة التي تم الحصول عليها بواسطة التعبير في الوسيطة الأولى (في حالتنا، سيكون هذا هو اسم المقالة).

التنفيذ 3
من الضروري ليس فقط تحديد المقالة التي تتوافق معها، ولكن أيضًا عرض سعر العنصر الخاص بهذه المقالة (يجب أن تكون الأسعار نفسها موجودة في العمود B من ورقة الكتالوج):
=IFERROR(VIEW(2,1/SEARCH(Directory!$A$2:$A$11,A2),Directory!$B$2:$B$11);"")
=IFERROR(LOOKUP(2,1/SEARCH(Directory!$A$2:$A$11,A2),Directory!$B$2:$B$11),"")

بعض الملاحظات المهمة:

• يجب ألا تحتوي البيانات الموجودة على الورقة التي تحتوي على أرقام المقالات على خلايا فارغة. خلاف ذلك، مع وجود درجة عالية من الاحتمال، ستعيد الصيغة القيمة بالضبط خلية فارغة، وليس الذي يتوافق مع شروط البحث
• تبحث الصيغة بطريقة يتم من خلالها العثور على أي تطابق. على سبيل المثال، يتم كتابة رقم كمقالة 1 ، وفي سطر الأسماء، بالإضافة إلى 1 بأكمله، قد يكون هناك أيضًا 123 , 651123 , FG1412NMإلخ. لجميع هذه العناصر، يمكن اختيار المادة رقم 1، لأن فهو موجود في كل عنوان. عادةً ما يمكن أن يحدث هذا إذا كانت المقالة 1 موجودة في نهاية القائمة

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

في المثال المرفق في بداية المقال ستجد جميع الخيارات التي تم تحليلها.

إذا كنت بحاجة إلى عرض كافة الأسماء، فيمكنك استخدام الوظيفة من الوظيفة الإضافية الخاصة بي.

هل ساعد المقال؟ شارك الرابط مع أصدقائك! دروس الفيديو

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

الخطوة 1. تحديد ما إذا كان الحرف المطلوب موجودًا في الخلية (الشكل 1).

أرز. 1. هل الحرف المطلوب موجود في الخلية المحددة؟

FIND($C$1;A1) - يبحث عن الحرف المخزن في $C$1 في السلسلة A1؛ إذا تم العثور عليه، فسيتم إرجاع موضع هذا الحرف في السلسلة؛ إذا لم يتم العثور عليه، فإنه يُرجع الخطأ #VALUE!

قم بتنزيل المذكرة بالتنسيق والأمثلة بالتنسيق

الخطوة 2. تخلص من القيم الخاطئة (الشكل 2).

أرز. 2. استبدال القيم الخاطئة بالأصفار

IFERROR(FIND($C$1,A1),0) – إرجاع 0 إذا كانت قيمة الدالة FIND($C$1,A1) تنتج خطأ، وإلا يتم إرجاع قيمة الدالة FIND($C$1,A1) نفسها .

خطوة. 3. استبدال رقم المنصب برقم واحد

أرز. 3. تخفيض جميع القيم الإيجابية إلى قيمة واحدة

IFERROR(ORRUP(FIND($C$1,A1)/1000,1);0) - تحويل يسمح لك بالحصول على 1 لأي ​​قيمة موضع (ليس خطأ)؛ يتم تقسيم رقم الموضع الذي يتم إرجاعه بواسطة الدالة FIND($C$1;A1) على 1000 ويتم تقريبه إلى أقرب عدد صحيح (على وجه الدقة، خلية اكسليمكن أن يحتوي على 32,767 حرفًا كحد أقصى، لذلك "لكي تكون في الجانب الآمن" يمكنك استبدال 1000 بـ 32,767 :); وفي هذه الحالة، سوف تتجنب الأخطاء المتعلقة بمحتويات أي خلية).

وبدلاً من ذلك، يمكنك استخدام الدالة IF:

IFERROR(IF(FIND($C$1,A1)>0,1);0) – إذا كانت قيمة الدالة FIND($C$1,A1) أكبر من الصفر، فإن الدالة IF تستبدل هذه القيمة بواحدة. لاحظ أن الوسيطة الثالثة للدالة IF مفقودة لأن FIND($C$1;A1) لا يمكنها إرجاع قيمة أقل من أو تساوي 0 (FIND($C$1;A1) تُرجع القيم الموجبة فقط أو #VALUE! ) خطأ.

خطوة. 4. دعونا نلخص كل منها. اسمحوا لي أن أذكرك أن الواحد يتوافق مع خلية تحتوي على الرمز المطلوب، والصفر يتوافق مع خلية لا تحتوي على الرمز المطلوب (الشكل 4).

أرز. 4. مجموع الخلايا التي تحتوي على الرمز المطلوب

في الخلية C2 استخدمت صيغة صفيف:

(=SUM(IFERROR(OCRUP(FIND($C$1,A1:A14)/1000,1),0)))

تقوم الدالة بحساب القيم بشكل تسلسلي =IFERROR(OCRUP(FIND($C$1,A 1 )/1000;1);0), =IFERROR(OCRUP(FIND($C$1,A 2 )/1000;1);0) ... وهكذا حتى =IFERROR(OCRUP(FIND($C$1;A 14 )/1000;1);0)

يتم تخزين نتيجة الحساب (0 أو 1) في الذاكرة، وتشكيل مصفوفة افتراضية (1,1,1,0,1,1,0,1,1,0,0,0,1). تقوم الدالة SUM ببساطة بجمع كل تلك العناصر.

دالة بديلة: =SUM(IFERROR(IF(FIND($C$1,A1:A14)>0,1),0))

ملاحظة: لا تقم بإدخال أقواس متعرجة في شريط الصيغة، ولكن اكتب الصيغة بأكملها بدون أقواس متعرجة واضغط على Ctrl+Shift+Enter في نفس الوقت