استخدام الصيغ لصياغة شرطي في Excel

تسمح لك إضافة التنسيق الشرطي في Excel بتطبيق خيارات تنسيق مختلفة على خلية أو نطاق من الخلايا التي تفي بشروط معينة تقوم بتعيينها. يتم تطبيق خيارات التنسيق فقط عندما تستوفي الخلايا المحددة هذه الشروط المحددة.

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

يحتوي Excel على عدد من الخيارات المضمنة للشروط الشائعة الاستخدام مثل البحث عن أرقام أكبر من أو أقل من قيمة معينة أو العثور على أرقام أعلى أو أقل من القيمة المتوسطة.

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

01 من 04

تطبيق قواعد متعددة في إكسل

يمكن تطبيق أكثر من قاعدة على نفس البيانات لاختبار ظروف مختلفة. على سبيل المثال ، قد تحتوي بيانات الميزانية على مجموعة شروط تطبق تغييرات التنسيق عندما يتم إنفاق مستويات معينة ، مثل 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 للتحقق من دقة قواعد التنسيق الشرطية.

02 من 04

وضع قواعد التنسيق الشرطي

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

تنسيق شرطي للعثور على زيادة أكبر من 25 بالمائة

= (A2-B2) / A2> 25٪

1. تسليط الضوء الخلايا B2 إلى B5 في ورقة العمل.
2. اضغط على علامة التبويب الصفحة الرئيسية من شريط.
3. اضغط على تنسيق مشروط أيقونة في شريط لفتح القائمة المنسدلة.
4. أختر قانون جديد لفتح قاعدة التنسيق الجديدة صندوق المحادثة.
5. من قلم المدقة: المربع ، حدد كلاسيكي اختيار.
6. في القائمة المنسدلة الثانية ، انقر فوق الخيار الأخيراستخدم صيغة لتحديد الخلايا التي تريد تنسيقها.
7. اكتب ال معادلة المقدمة أعلاه في المساحة المقدمة.
8. اضغط على التنسيق مع: المنسدلة ثم حدد تعبئة باللون الأحمر الفاتح مع نص أحمر داكن اختيار.
9. انقر حسنا لإغلاق مربعات الحوار والعودة إلى ورقة العمل.
10. عند هذه النقطة ، لون خلفية الخلايا B3 و B5 يجب أن تكون خضراء.

شرطي التنسيق للبحث عن أكبر من 50 في المئة زيادة

= (A2-B2) / A2> 50٪

تسليط الضوء الخلايا B2 إلى B5 في ورقة العمل.

1. اضغط على علامة التبويب الصفحة الرئيسية من شريط.
2. اضغط على تنسيق مشروط أيقونة في شريط لفتح القائمة المنسدلة.
3. أختر قانون جديد لفتح قاعدة التنسيق الجديدة صندوق المحادثة.
4. من قلم المدقة: المربع ، حدد كلاسيكي اختيار.
5. في القائمة المنسدلة الثانية ، انقر فوق الخيار الأخيراستخدم صيغة لتحديد الخلايا التي تريد تنسيقها.
6. اكتب ال معادلة المقدمة أعلاه في المساحة المقدمة.
7. اضغط على التنسيق مع: المنسدلة ثم حدد تعبئة خضراء فاتحة بنص أخضر داكن اختيار.
8. انقر حسنا لإغلاق مربعات الحوار والعودة إلى ورقة العمل.

لون الخلفية الخلية B3 يجب أن تظل باللون الأخضر مشيرة إلى أن الفرق في النسبة المئوية بين الأرقام في الخلايا A3 و B3 أكبر من 25 في المئة ولكن أقل من أو يساوي 50 في المئة. لون الخلفية الخلية B5 يجب أن يتغير إلى اللون الأحمر مشيرا إلى أن الفرق في المئة بين الأرقام في الخلايا A5 و B5 أكبر من 50 في المئة.

03 من 04

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

للتحقق من صحة قواعد التنسيق الشرطية المدخلة ، يمكننا إدخال الصيغ في الخلايا C2: C5 سيحسب الفرق الدقيق بين الأعداد في النطاقات A2: A5 و B2: B5.

= (A2-B2) / A2

1. انقر فوق الخلية C2 لجعلها الخلية النشطة.
2. اكتب الصيغة المذكورة أعلاه واضغط على أدخل المفتاح على لوحة المفاتيح.
3. يجب أن تظهر الإجابة بنسبة 10٪ الخلية C2، مشيرا إلى أن العدد في الخلية A2 أكبر من الرقم في 10٪ الخلية B2.
4. قد يكون من الضروري تغيير التنسيق الخلية C2 لعرض الإجابة كنسبة مئوية.
5. استخدم ال مقبض التعبئة لنسخ الصيغة من الخلية C2 إلى الخلايا C3 إلى C5.
6. الأجوبة ل الخلايا C3 إلى C5 يجب أن يكون 30٪ و 25٪ و 60٪.

توضح الإجابات في هذه الخلايا أن قواعد التنسيق الشرطية التي تم إنشاؤها صحيحة منذ الاختلاف بينهما الخلايا A3 و B3 أكبر من 25 في المئة والفرق بين الخلايا A5 و B5 أكبر من 50 في المئة.

الخلية B4 لم يتغير اللون لأن الفرق بين الخلايا A4 و B4 يساوي 25 بالمائة ، وحددت قاعدة التنسيق الشرطي لدينا أن نسبة مئوية أكبر من 25 بالمائة كانت مطلوبة لتغيير لون الخلفية إلى اللون الأخضر.

04 من 04

ترتيب الأسبقية للتنسيق الشرطي

عند تطبيق قواعد متعددة على نفس نطاق البيانات ، يحدد Excel أولاً ما إذا كانت القواعد تتعارض. القواعد المتعارضة هي تلك التي لا يمكن فيها تطبيق خيارات التنسيق المختارة لكل قاعدة على نفس البيانات.

في المثال المستخدم في هذا البرنامج التعليمي ، تتعارض القواعد نظرًا لأن كلا القاعدتين تستخدمان نفس خيار التنسيق - أي تغيير لون الخلية في الخلفية.

في الحالة التي تكون فيها القاعدة الثانية صحيحة (الفرق في القيمة أكبر من 50٪ بين خليتين) ، تكون القاعدة الأولى (الفرق في القيمة أكبر من 25 بالمائة) هي أيضاً صحيحة.

ترتيب الأسبقية في Excel

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

يتم تحديد القاعدة التي يتم تطبيقها من خلال ترتيب الأسبقية الخاص بـ Excel ، والذي ينص على أن الأسبقية في القائمة في مربع الحوار إدارة قواعد التنسيق الشرطي لها الأسبقية.

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

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

تطبيق قواعد غير متضاربة

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

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

كنتيجة ل، الخلية B5 سيكون له حد أخضر ولون خلفية حمراء ، نظرًا لأن الفرق بين الأرقام في الخلايا A5 و B5 أكبر من 25 و 50 في المئة.

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

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

إذا تم تطبيق لون خلفية أصفر في البداية على الخلايا B2 إلى B5 في المثال ، بمجرد إضافة قواعد التنسيق الشرطي ، فقط الخلايا B2 و B4 سيبقى أصفر. لأن قواعد التنسيق الشرطي التي تم إدخالها تنطبق على الخلايا B3 و B5، ستتغير ألوان الخلفية من الأصفر إلى الأخضر والأحمر على التوالي.