اكسل

التنسيق الشرطي بالصيغ (10 أمثلة)

Conditional Formatting With Formulas

بداية سريعة | أمثلة | استكشاف الأخطاء وإصلاحها | تمرين

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



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

على سبيل المثال ، باستخدام الإعداد المسبق 'يساوي' ، من السهل تمييز الخلايا التي تساوي 'تفاحة'.





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

قاعدة لتمييز x أو y أو z



فيما يلي نتيجة القاعدة المطبقة على النطاق B4: F8 في جدول البيانات هذا:

التنسيق الشرطي مع الدالة OR

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

 
= OR (B4='apple',B4='kiwi',B4='lime')

بداية سريعة

يمكنك إنشاء قاعدة تنسيق شرطي تستند إلى صيغة بأربع خطوات سهلة:

1. حدد الخلايا التي تريد تنسيقها.

حدد الخلايا المراد تنسيقها

2. أنشئ قاعدة تنسيق شرطي ، وحدد خيار الصيغة

حدد خيار الصيغة

3. أدخل صيغة تُرجع TRUE أو FALSE.

أدخل الصيغة المتعلقة بالخلية النشطة

4. تعيين خيارات التنسيق وحفظ القاعدة.

عيّن خيارات التنسيق

ال دالة ISODD تقوم بإرجاع TRUE للأرقام الفردية فقط ، مما يؤدي إلى تشغيل القاعدة:

كيفية استخدام وظيفة البحث

ترجع الدالة ISODD القيمة TRUE للأرقام الفردية ، مما يؤدي إلى تشغيل القاعدة

فيديو: كيفية تطبيق التنسيق الشرطي باستخدام صيغة

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

منطق الصيغة

يجب أن تُرجع الصيغ التي تطبق التنسيق الشرطي TRUE أو FALSE أو مكافئات رقمية. وهنا بعض الأمثلة:

تحويل تاريخ جوليان إلى تاريخ التقويم في Excel
 
= ISODD (A1) = ISNUMBER (A1) =A1>100 = AND (A1>100,B1<50) = OR (F1='MN',F1='WI')

ترجع جميع الصيغ المذكورة أعلاه TRUE أو FALSE ، لذا فهي تعمل بشكل مثالي كمحفز للتنسيق الشرطي.

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

أمثلة الصيغة

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

انظر أيضا: أكثر من 30 صيغة تنسيق شرطي

تسليط الضوء على الطلبات من ولاية تكساس

لتمييز الصفوف التي تمثل الطلبات من تكساس (اختصار TX) ، استخدم صيغة تقوم بتأمين المرجع للعمود F:

 
=$F5='TX'

استخدم صيغة لتمييز الصفوف حيث state =

لمزيد من التفاصيل انظر هذه المادة: قم بتمييز الصفوف بالتنسيق الشرطي .

فيديو: كيفية تمييز الصفوف باستخدام التنسيق الشرطي

قم بتمييز التواريخ في الثلاثين يومًا القادمة

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

 
= AND (B4> NOW (),B4<=( NOW ()+30))

بتاريخ 18 أغسطس 2016 الحالي ، يميز التنسيق الشرطي التواريخ على النحو التالي:

تنسيق شرطي لتمييز التواريخ في الثلاثين يومًا القادمة

ال الآن وظيفة إرجاع التاريخ والوقت الحاليين. للحصول على تفاصيل حول كيفية عمل هذه الصيغة ، راجع هذه المقالة: قم بتمييز التواريخ في الأيام N التالية .

قم بتمييز الاختلافات في العمود

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

 
=$B4$C4

التنسيق الشرطي لمقارنة الأعمدة

أنظر أيضا: إصدار من هذه الصيغة يستخدم الدالة EXACT لإجراء مقارنة حساسة لحالة الأحرف .

قم بتمييز القيم المفقودة

لتمييز القيم الموجودة في إحدى القوائم المفقودة من قائمة أخرى ، يمكنك استخدام صيغة تستند إلى ملف دالة COUNTIF :

 
= COUNTIF (list,B5)=0

قم بتمييز القيم المفقودة باستخدام التنسيق الشرطي

هذه الصيغة تتحقق ببساطة من كل قيمة القائمة أ مقابل القيم في 'قائمة' النطاق المسمى (D5: D10). عندما يكون العدد صفرًا ، تُرجع الصيغة TRUE وتقوم بتشغيل القاعدة ، التي تبرز القيم في القائمة أ المفقودة من القائمة ب .

فيديو: كيفية البحث عن القيم الناقصة باستخدام COUNTIF

تسليط الضوء على العقارات التي تحتوي على 3 غرف نوم أو أقل من 350 ألف دولار

للعثور على عقارات في هذه القائمة تحتوي على 3 غرف نوم على الأقل ولكنها أقل من 300000 دولار ، يمكنك استخدام صيغة تستند إلى وظيفة AND:

 
= AND ($C5<350000,$D5>=3)

تقوم علامات الدولار ($) بتأمين الإشارة إلى العمودين C و D ، و ووظيفة يتم استخدامه للتأكد من أن كلا الشرطين صحيحان. في الصفوف حيث ترجع الدالة AND القيمة TRUE ، يتم تطبيق التنسيق الشرطي:

التنسيق الشرطي لتمييز قوائم الممتلكات

تسليط الضوء على القيم العليا (مثال ديناميكي)

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

التنسيق الشرطي الديناميكي للقيم العليا

الصيغة المستخدمة لهذه القاعدة هي:

كيفية عمل رسم بياني في Excel 2010
 
=B4>= LARGE (data,input)

حيث تكون 'البيانات' هي النطاق المسمى B4: G11 ، و 'الإدخال' هو النطاق المسمى F2. تحتوي هذه الصفحة على التفاصيل وشرح كامل .

مخططات غانت

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

استخدام التنسيق الشرطي لإنشاء مخطط جانت

تستخدم ورقة العمل هذه قاعدتين ، واحدة للأشرطة والأخرى لتظليل نهاية الأسبوع:

 
= AND (D>=$B5,D<=$C5) // bars = WEEKDAY (D,2)>5 // weekends

تشرح هذه المقالة صيغة الأشرطة ، و تشرح هذه المقالة صيغة تظليل نهاية الأسبوع .

مربع بحث بسيط

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

مربع بحث التنسيق الشرطي

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

 
= ISNUMBER ( SEARCH ($F,B2))

لمزيد من التفاصيل وشرح كامل راجع:

استكشاف الأخطاء وإصلاحها

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

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

صيغ وهمية

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

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

استخدم الصيغ الوهمية للتحقق من صيغ التنسيق الشرطي

فيديو: اختبر التنسيق الشرطي بالصيغ الوهمية

محددات

توجد بعض القيود التي تأتي مع التنسيق الشرطي المستند إلى الصيغة:

  1. لا يمكنك تطبيق الرموز أو مقاييس الألوان أو أشرطة البيانات باستخدام صيغة مخصصة. أنت مقيد بتنسيق الخلية القياسي ، بما في ذلك تنسيقات الأرقام والخط ولون التعبئة وخيارات الحدود.
  2. لا يمكنك استخدام بنيات صيغ معينة مثل الاتحادات أو التقاطعات أو ثوابت الصفيف لمعايير التنسيق الشرطي.
  3. لا يمكنك الرجوع إلى مصنفات أخرى في صيغة تنسيق شرطي.

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

المزيد من موارد صيغة CF

  • أكثر من 30 مثالاً على صيغ التنسيق الشرطي
  • التدريب بالفيديو مع أوراق العمل التدريبية
المؤلف ديف برونز


^