إكسل الصيغ الشرطي الصيغ

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

يتم تطبيق خيارات التنسيق فقط عندما تستوفي الخلايا المحددة هذه الشروط المحددة.

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

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

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

تطبيق قواعد متعددة

يمكن تطبيق أكثر من قاعدة على نفس البيانات لاختبار ظروف مختلفة. على سبيل المثال ، قد تحتوي بيانات الميزانية على مجموعة شروط تطبق تغييرات التنسيق عند إنفاق مستويات معينة - مثل 50٪ و 75٪ و 100٪ - من إجمالي الميزانية.

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

مثال: البحث عن البيانات التي تتجاوز 25٪ و 50٪ زيادة مع التنسيق الشرطي

في المثال التالي ، سيتم تطبيق قاعدتي تنسيق شرطيين مخصصين على نطاق الخلايا من B2 إلى B5.

• تتحقق القاعدة الأولى لمعرفة ما إذا كانت البيانات في الخلايا من A2 إلى A5 أكبر من القيمة المقابلة في B2 إلى B5 بأكثر من 25٪.
• تتحقق القاعدة الثانية لمعرفة ما إذا كانت البيانات نفسها في A2: A5 تتجاوز القيمة المقابلة في B2: B5 بأكثر من 50٪.

كما يمكن رؤيته في الصورة أعلاه ، إذا كان أيًا من الشروط المذكورة أعلاه صحيحًا ، فسيتم تغيير لون الخلفية للخلية أو الخلايا في النطاق B1: B4.

• للبيانات التي يكون فيها الفرق أكبر من 25٪ ، سيتغير لون خلفية الخلية إلى اللون الأزرق.
• إذا كان الفرق أكبر من 50٪ سيتغير لون خلفية الخلية إلى اللون الأحمر.

القواعد المستخدمة لإنجاز هذه المهمة ،

= (A2-B2) / A2> 25٪ = (A2-B2) / A2> 50٪

سيتم إدخاله باستخدام مربع الحوار الشرطي تنسيق Formatting Rule جديد .

دخول بيانات البرنامج التعليمي

1. أدخل البيانات في الخلايا من A1 إلى C5 كما هو موضح في الصورة أعلاه

ملاحظة: ستضيف الخطوة 3 من البرنامج التعليمي صيغًا إلى الخلايا C2: C4 تعرض الفرق الدقيق بين قيم الخلايا A2: A5 و B2: B5 للتحقق من دقة قواعد التنسيق الشرطية.

تعيين قواعد تنسيق Condtional

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

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

1. تمييز الخلايا من B2 إلى B5 في ورقة العمل.
2. انقر فوق علامة التبويب الصفحة الرئيسية للشريط.
3. انقر على أيقونة التنسيق الشرطي في الشريط لفتح القائمة المنسدلة.
4. اختر New Rule لفتح مربع الحوار New Formatting Rule كما هو موضح في الصورة أعلاه.
5. في النصف العلوي من مربع الحوار ، انقر فوق الخيار الأخير: استخدم صيغة لتحديد الخلايا المطلوب تنسيقها.
6. في النصف السفلي من مربع الحوار ، انقر فوق قيم تنسيق حيث تكون هذه الصيغة true: سطر.
7. اكتب الصيغة : = (A2-B2) / A2> 25٪ في المساحة المتوفرة
8. انقر فوق الزر " تنسيق" لفتح مربع الحوار "تنسيق خلايا".
9. في مربع الحوار هذا ، انقر فوق علامة التبويب التعبئة واختر لون تعبئة أزرق.
10. انقر فوق موافق مرتين لإغلاق مربعات الحوار والعودة إلى ورقة العمل.
11. عند هذه النقطة ، يجب أن يكون لون خلفية الخلايا B3 و B5 باللون الأزرق.

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

1. مع تحديد الخلايا من B2 إلى B5 ، كرر الخطوات من 1 إلى 6 أعلاه.
2. اكتب الصيغة: = (A2-B2) / A2> 50٪ في المساحة المتوفرة.
3. انقر فوق الزر " تنسيق" لفتح مربع الحوار "تنسيق خلايا".
4. انقر فوق علامة التبويب تعبئة واختر لون تعبئة أحمر.
5. انقر فوق موافق مرتين لإغلاق مربعات الحوار والعودة إلى ورقة العمل .
6. يجب أن يكون لون الخلفية B3 للخلية باللون الأزرق يشير إلى أن الفرق بين الأرقام في الخلايا A3 و B3 أكبر من 25٪ ولكن أقل من أو يساوي 50٪.
7. يجب أن يتغير لون خلفية الخلية B5 إلى اللون الأحمر مشيرًا إلى أن الفرق بين الأرقام في الخلايا A5 و B5 أكبر من 50٪.

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

حساب الفرق ٪

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

1. انقر فوق الخلية C2 لجعلها الخلية النشطة.
2. اكتب في الصيغة = (A2-B2) / A2 ثم اضغط على مفتاح Enter على لوحة المفاتيح.
3. يجب أن تظهر الإجابة 10٪ في الخلية C2 ، مشيرة إلى أن الرقم الموجود في الخلية A2 أكبر بنسبة 10٪ من الرقم الموجود في الخلية B2.
4. قد يكون من الضروري تغيير التنسيق على الخلية C2 لعرض الإجابة كنسبة مئوية.
5. استخدم مقبض التعبئة لنسخ الصيغة من الخلية C2 إلى الخلايا من C3 إلى C5.
6. يجب أن تكون إجابات الخلايا من C3 إلى C5 هي: 30٪ و 25٪ و 60٪.
7. توضح الإجابات في هذه الخلايا أن قواعد التنسيق الشرطية التي تم إنشاؤها صحيحة لأن الفرق بين الخلايا A3 و B3 أكبر من 25٪ والفرق بين الخلايا A5 و B5 أكبر من 50٪.
8. لم يتغير لون الخلية B4 نظرًا لأن الفرق بين الخلايا A4 و B4 يساوي 25٪ ، وحددت قاعدة التنسيق الشرطي لدينا أن نسبة مئوية أكبر من 25٪ كانت مطلوبة لتغيير لون الخلفية إلى اللون الأزرق.

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

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

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

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

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

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

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

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

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

كما هو مبين في الصورة أعلاه ، القاعدة الثانية المستخدمة في هذا البرنامج التعليمي (= (A2-B2) / A2> 50٪) أعلى في القائمة ، وبالتالي ، لها الأسبقية على القاعدة الأولى.

نتيجة لذلك ، يتم تغيير لون خلفية الخلية B5 إلى اللون الأحمر.

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

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

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

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

إذا كانت أول قاعدة تنسيق شرطي في المثال الخاص بنا (= (A2-B2) / A2> 25٪) قامت بتنسيق نطاق الخلايا B2: B5 مع حد أزرق بدلاً من لون خلفية زرقاء ، فإن قاعدتي التنسيق الشرطي لن تتعارضان منذ كلا الصيغتين يمكن تطبيقها دون التدخل في الآخر.

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

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

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

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

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