تسمح لك إضافة التنسيق الشرطي في Excel بتطبيق خيارات تنسيق مختلفة على خلية أو نطاق من الخلايا التي تفي بشروط معينة قمت بتعيينها. يمكن أن يساعد تعيين مثل هذه الشروط في تنظيم جدول البيانات الخاص بك وتسهيل عملية المسح الضوئي. تتضمن خيارات التنسيق التي يمكنك استخدامها تغييرات الخط ولون الخلفية وأنماط الخط وحدود الخلية وإضافة تنسيق الأرقام إلى البيانات.
يحتوي Excel على خيارات مضمنة للحالات شائعة الاستخدام مثل البحث عن أرقام أكبر من أو أقل من قيمة معينة أو العثور على أرقام أعلى أو أقل من متوسط القيمة. بالإضافة إلى هذه الخيارات المحددة مسبقًا ، يمكنك أيضًا إنشاء قواعد تنسيق شرطي مخصصة باستخدام صيغ Excel.
تنطبق هذه الإرشادات على Excel 2019 و 2016 و 2013 و 2010 و Excel for Microsoft 365.
تطبيق شروط متعددة في Excel
يمكنك تطبيق أكثر من قاعدة واحدة على نفس البيانات لاختبار الظروف المختلفة. على سبيل المثال ، قد تحتوي بيانات الميزانية على مجموعة شروط تطبق تغييرات التنسيق عند الوصول إلى مستويات معينة من الإنفاق ، مثل 50٪ و 75٪ و 100٪ من إجمالي الميزانية.
في مثل هذه الظروف ، يحدد Excel أولاً ما إذا كانت القواعد المختلفة تتعارض ، وإذا كان الأمر كذلك ، فإن البرنامج يتبع ترتيبًا محددًا للأولوية لتحديد قاعدة التنسيق الشرطي التي سيتم تطبيقها على البيانات.
العثور على البيانات التي تتجاوز 25٪ وزيادة 50٪
في المثال التالي ، سيتم تطبيق قاعدتين مخصصتين للتنسيق الشرطي على نطاق خلايا B2 إلى B5.
- تتحقق القاعدة الأولى لمعرفة ما إذا كانت البيانات الموجودة في خلايا A2: A5 أكبر من القيمة المقابلة في B2: B5بواسطة أكثر من 25٪
- تتحقق القاعدة الثانية لمعرفة ما إذا كانت البيانات نفسها في A2: A5 تتجاوز القيمة المقابلة في B2: B5بأكثر من 50٪.
كما يتضح في الصورة أعلاه ، إذا كان أي من الشروط المذكورة أعلاه صحيحًا ، سيتغير لون خلفية الخلية أو الخلايا الموجودة في النطاق B1: B4.
- بالنسبة للبيانات التي يكون الاختلاف فيها أكثر من 25٪ ، سيتغير لون خلفية الخلية إلى اللون الأخضر.
- إذا كان الفرق أكبر من 50٪ ، سيتغير لون خلفية الخلية إلى اللون الأحمر.
سيتم إدخال القواعد المستخدمة لإنجاز هذه المهمة باستخدام مربع الحوار قاعدة تنسيق جديدة. ابدأ بإدخال بيانات العينة في خلايا A1 إلى C5كما هو موضح في الصورة أعلاه.
في الجزء الأخير من البرنامج التعليمي ، سنضيف الصيغ إلى الخلايا C2: C4 التي تُظهر فرق النسبة المئوية الدقيق بين القيم في الخلايا A2: A5 و B2: B5؛ سيسمح لنا هذا بالتحقق من دقة قواعد التنسيق الشرطي.
إعداد قواعد التنسيق الشرطي
أولاً ، سنطبق التنسيق الشرطي لإيجاد زيادة كبيرة بنسبة 25 بالمائة أو أكثر.
ستبدو الوظيفة على النحو التالي:
=(A2-B2) / A2>25٪
- تسليط الضوء الخلايا B2 إلى B5في ورقة العمل.
- انقر فوق علامة التبويب الصفحة الرئيسية من الشريط.
- انقر على أيقونة تنسيق شرطي في الشريط لفتح القائمة المنسدلة.
- اختر قاعدة جديدة لفتح مربع الحوار قاعدة التنسيق الجديدة.
- ضمن حدد نوع القاعدة ، انقر فوق الخيار الأخير: استخدم صيغة لتحديد الخلايا المراد تنسيقها.
- اكتب الصيغة المذكورة أعلاه في المساحة الموجودة أدناه تنسيق القيم حيث تكون هذه الصيغة صحيحة:
-
انقر فوق الزر Format لفتح مربع الحوار. انقر فوق علامة التبويب Fillواختر لونًا.
- انقر فوق OKلإغلاق مربعات الحوار والعودة إلى ورقة العمل.
- يجب أن يتغير لون خلفية خلايا B3 و B5إلى اللون الذي حددته.
الآن ، سنطبق التنسيق الشرطي لإيجاد زيادة بنسبة 50 بالمائة أو أكثر. ستبدو الصيغة كما يلي:
- كرر الخطوات الخمس الأولى أعلاه.
- اكتب الصيغة الموضحة أعلاه في المساحة الموجودة أدناه تنسيق القيم حيث تكون هذه الصيغة صحيحة:
- انقر فوق الزر Format لفتح مربع الحوار. انقر فوق علامة التبويب Fillواختر لونًا مختلفًا عما فعلت في مجموعة الخطوات السابقة.
- انقر فوق OKلإغلاق مربعات الحوار والعودة إلى ورقة العمل.
لون خلفية خلية B3 يجب أن يظل كما هو للإشارة إلى أن الفرق بالنسبة المئوية بين الأرقام في الخلايا A3 وB3 أكبر من 25 بالمائة ولكنها أقل من أو تساوي 50 بالمائة. يجب أن يتغير لون خلفية خلية B5 إلى اللون الجديد الذي حددته للإشارة إلى أن الفرق بالنسبة المئوية بين الأرقام في الخلايا A5 و B5 أكبر من 50 بالمائة
التحقق من قواعد التنسيق الشرطي
للتحقق من صحة قواعد التنسيق الشرطي التي تم إدخالها ، يمكننا إدخال الصيغ في الخلايا C2: C5 التي ستحسب النسبة المئوية الدقيقة للفرق بين الأرقام في النطاقات A2: A5 وB2: B5.
تبدو الصيغة في الخلية C2 كما يلي:
=(A2-B2) / A2
- انقر فوق خلية C2لجعلها الخلية النشطة.
- اكتب الصيغة أعلاه واضغط على مفتاح Enterعلى لوحة المفاتيح.
- يجب أن تظهر الإجابة 10٪ في خلية C2 ، للإشارة إلى أن الرقم الموجود في الخلية A2 أكبر بنسبة 10٪ من الرقم الموجود في خلية B2.
- قد يكون من الضروري تغيير التنسيق في خلية C2لعرض الإجابة كنسبة مئوية.
- استخدم مقبض التعبئة لنسخ الصيغة من خلية C2 إلى خلايا C3 إلى C5.
- إجابات خلايا C3 إلى C5يجب أن تكون 30٪ ، 25٪ ، و 60٪.
تظهر الإجابات في هذه الخلايا أن قواعد التنسيق الشرطي دقيقة لأن الفرق بين الخلايا A3 و B3 أكبر من 25 النسبة المئوية ، والفرق بين خلايا A5 و B5أكبر من 50 بالمائة.
لم يتغير لون الخلية B4 لأن الفرق بين الخلايا A4 و B4يساوي 25 بالمائة ، وحددت قاعدة التنسيق الشرطي الخاصة بنا أن النسبة المئوية أكبر من 25 بالمائة كانت مطلوبة لتغيير لون الخلفية.
ترتيب الأسبقية للتنسيق الشرطي
عند تطبيق قواعد متعددة على نفس نطاق البيانات ، يحدد Excel أولاً ما إذا كانت القواعد تتعارض. القواعد المتعارضة هي تلك التي لا يمكن فيها تطبيق خياري التنسيق على نفس البيانات.
في مثالنا ، تتعارض القواعد لأن كلاهما يستخدم نفس خيار التنسيق - تغيير لون خلية الخلفية.
في الحالة التي تكون فيها القاعدة الثانية صحيحة (الفرق في القيمة أكثر من 50 بالمائة بين خليتين) ، فإن القاعدة الأولى (الفرق في القيمة أكبر من 25 بالمائة) صحيحة أيضًا.
نظرًا لأن الخلية لا يمكن أن تحتوي على خلفيتين مختلفتين في نفس الوقت ، يحتاج Excel إلى معرفة قاعدة التنسيق الشرطي التي يجب تطبيقها.
ينص ترتيب أسبقية Excel على تطبيق القاعدة الأعلى في القائمة في مربع الحوار "إدارة قواعد التنسيق الشرطي" أولاً.
كما هو موضح في الصورة أعلاه ، القاعدة الثانية المستخدمة في هذا البرنامج التعليمي أعلى في القائمة ، وبالتالي لها الأسبقية على القاعدة الأولى. نتيجة لذلك ، فإن لون خلفية خلية B5أخضر.
بشكل افتراضي ، تنتقل القواعد الجديدة إلى أعلى القائمة ؛ لتغيير الترتيب ، استخدم أزرار الأسهم Up and Downفي مربع الحوار.
تطبيق القواعد غير المتضاربة
إذا لم تتعارض قاعدتان أو أكثر من قواعد التنسيق الشرطي ، فسيتم تطبيق كليهما عندما يصبح الشرط الذي تختبره كل قاعدة صحيحًا.
إذا قامت قاعدة التنسيق الشرطي الأولى في مثالنا بتنسيق نطاق الخلايا B2: B5بحد برتقالي بدلاً من لون الخلفية البرتقالي ، فإن قاعدتي التنسيق الشرطي لن تكون كذلك التعارض حيث يمكن تطبيق كلا التنسيقين دون التدخل مع الآخر.
التنسيق الشرطي مقابل التنسيق العادي
في حالة وجود تعارض بين قواعد التنسيق الشرطي وخيارات التنسيق المطبقة يدويًا ، تكون الأولوية دائمًا لقاعدة التنسيق الشرطي وسيتم تطبيقها بدلاً من أي خيارات تنسيق مضافة يدويًا.
