7. التنسيق الشرطي

---

يسمح التنسيق الشرطي بتطبيق تنسيق (كألوان الخط أو الألوان الخلفية للخلايا ...) على الخلايا التي تستوفي شرطا. لهذه التقنية ميزة إبراز الخلايا التي تفي بمعايير معينة أو إظهار تنويعات أو اتجاهات.

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

لتحديد تنسيق شرطي ، نستخدم:

• إما الأمر التنسيق الشرطي على علامة التبويب الرئيسية للشريط
• إما الزر تحليل سريع

سنقدم لك نظرة عامة على الزر تحليل سريع هذا. ثم سنرى العديد من الأمثلة الملموسة للتنسيق الشرطي.

1.7. الزر تحليل سريع

أضيف هذا الزر ابتداءا من الإصدار 2013 Excel . و هو يظهر بمجرد تحديد نطاق من الخلايا ... في الزاوية اليمنى السفلى من النطاق المحدد :

انقر فوق الزر تحليل سريع للاطلاع على خيارات التنسيق الشرطي.

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

تتوفر أوامر أخرى للتنسيق الشرطي باستعمال الأمر تنسيق شرطي من المجموعة أنماط من علامة التبويب الرئيسية للشريط.

2.7. قيم أقل من ، أكبر من ، بين...

نعتبر المقتطف التالي من جدول البيانات:

لنطبق التنسيق للقيم في العمود B كما يلي:

• القيم أكبر من 8000 باللون الأخضر
• القيم بين 4000 و 8000 باللون الأصفر
• القيم أقل من 4000 باللون الأحمر

حدد النطاق B2:B14 وانقر على الأمر تنسيق شرطي على علامة التبويب الرئيسية للشريط ، واختر قواعد تمييز الخلايا ، ثم اختر أكبر من:

في مربع الحوار أكبر من الذي يظهر ، أدخل القيمة 8000 و اختر تعبئة بالأخضر مع نص أخضر داكن :

احتفظ بالنطاق B2:B14 محددًا وانقر على الأمر تنسيق شرطي على علامة التبويب الرئيسية للشريط ، واختر قواعد تمييز الخلايا ، ثم اختر بين:

في مربع الحوار بين، أدخل القيمة 4000 ، والقيمة 8000 و و اختر تعبئة بالأصفر مع نص أصفر داكن :

احتفظ بالنطاق B2:B14 محددًا وانقر على الأمر تنسيق شرطي على علامة التبويب الرئيسية للشريط ، واختر قواعد تمييز الخلايا ، ثم اختر أصغر من:

في مربع الحوار أصغر من ، أدخل القيمة 4000 وحدد تعبئة تعبئة بالأحمر الفاتح مع نص أحمر داكن :

وهذه النتيجة:

لنقم الآن بتعديل قيمة في العمود B. على سبيل المثال أدخل القيمة 2200 في الخلية B4 و أكد باستخدام مفتاح Enter على لوحة المفاتيح. لاحظ أن الخلية B4 تتغير تلقائيًا إلى اللون الأحمر:

3.7. الخلايا التي تحتوي على النص

نعتبر المقتطف التالي من جدول البيانات:

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

حدد النطاق C4:C16. يظهر الزر تحليل سريع أسفل النطاق المحدد. انقر فوق الزر تحليل سريع ثم الزر يحتوي على النص على :

يظهر مربع الحوار النص الذي يحتوي على:

هنا يمكنك إدخال نص البحث ، أي "خالد بن الوليد". ولكن ، حيث أن نص البحث مكتوب في الخلية B1 ، انقر فوق هذه الخلية. يضيف Excel القيمة "1$B$=". الميزة هي أنك إذا كنت تريد البحث عن نص آخر ، فقط قم بإدخاله في الخلية B1 ، دون الحاجة إلى إعادة التنسيق.

في قائمة التنسيقات ، يمكنك اختيار تنسيق من القائمة أو اختيار تنسيق مخصص ...

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

اختر تنسيقا و أكد بالنقر على الزر موافق.

ثم انقر فوق الزر موافق في مربع الحوار النص الذي يحتوي على. لاحظ الخلايا المميزة بالتنسيق الذي اخترته:

الآن قم بتعديل نص الخلية B1 ب "الرسالة" على سبيل المثال ، و أكد باستخدام مفتاح Enter على لوحة المفاتيح. لاحظ أن التنسيق يتم تحديثه تلقائيًا :

4.7. القيم المتكررة

نعتبر المقتطف التالي من جدول البيانات الذي يحتوي على عمود "عنوان البريد الإلكتروني":

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

حدد النطاق A2:A14 وانقر فوق الزر تحليل سريع ثم الزر القيم المتكررة:

يسلط Excel الضوء على القيم المتكررة في النطاق المحدد:

5.7. أشرطة البيانات

نعتبر المقتطف التالي من جدول البيانات:

حدد النطاق B2:B14 وانقر على الزر تحليل سريع ثم على الزر أشرطة البيانات:

كما يمكنك القيام بخلاف ذلك. حدد نطاق B2:B14 وانقر على الأمر التنسيق الشرطي في علامة التبويب الرئيسية للشريط ، واختر أشرطة البيانات ، ثم اختر اللون المناسب لك:

يوضح Excel أهمية القيم في النطاق المحدد بطريقة مشابهة للرسم البياني:

6.7. مجموعة الأيقونات

نعتبر المقتطف التالي من جدول البيانات :

حدد النطاق B2:B15 وانقر على الأمر التنسيق الشرطي على علامة التبويب الرئيسية للشريط ، واختر مجموعات الأيقونات، تم اختر المجموعة التي تناسبك:

والنتيجة على النحو التالي:

العيب في هذه النتيجة هو أن اللون الأخضر يشير إلى الأعداد الكبيرة من المخالفات (6 و 7) ، في حين أنه يجب أن يشير إلى الأعداد الصغيرة. من ناحية أخرى ، أفترض أننا نريد توزيعًا مختلفًا بين الألوان الثلاثة ، على سبيل المثال:

• اللون الأحمر ل 4 مخالفات و أكتر
• اللون البرتقالي ل 2 أو 3 مخالفات
• اللون الأخضر لمخالفة واحدة

لتحقيق هذه النتيجة ، اتبع الخطوات التالية:

حدد النطاق B2:B15 مرة أخرى وانقر على الأمر التنسيق الشرطي على علامة التبويب الرئيسية للشريط ، واختر مجموعات الأيقونات، تم اختر قواعد إضافية...:

يظهر مربع الحوار قاعدة تنسيق جديدة:

انقر فوق الزر عكس ترتيب الأيقونة ، وقم بتغيير النوع إلى "رقم" والقيم كما يلي:

اضغط على الزر موافق. يجب أن تكون النتيجة كالتالي:

7.7. قاعدة تنسيق جديدة

نعتبر المقتطف التالي من جدول البيانات :

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

• حدد نطاق الخلايا في العمود A حيث هي مسجلة أسماء المشتركين. على سبيل المثال النطاق A2:A80.
• انقر على الأمر التنسيق الشرطي في علامة التبويب الرئيسية للشريط ، واختر قاعدة جديدة....

في مربع الحوار قاعدة تنسيق جديدة الذي يظهر:

• انقر فوق استخدام صيغة لتحديد الخلايا التي سيتم تنسيقها من القائمة تحديد نوع القاعدة.
• في الحقل تنسيق القيم عندما تكون هذه الصيغة صحيحة ، أدخل الصيغة:

=AND(E2>20000;F2="الرياض")

تكون نتيجة هذه الصيغة صحيحة عندما يكون كلا الشرطان "E2> 20000" و "الرياض"=F2 صحيحان. راجع موضوعاتنا حول دوال Excel 2016 لمزيد من المعلومات حول الدالة AND.

• ثم انقر فوق الزر تنسيق... لفتح مربع الحوار تنسيق خلايا واختر التنسيق الذي يناسبك. لقد تم ذكر استخدام مربع الحوار هذا في القسم 3.7. أعلاه.
• تم اضغط على الزر موافق للتأكيد.

و النتيجة كما يلي:

أود أن ألفت انتباهكم إلى حقيقة أنني في الصيغة استخدمت عناوين الخليتان E2 و F2. و هتان الخليتان تقابل الخلية الأولى من التحديد. أي التي تقع في أعلى يمين التحديد ؛ و هي الخلية A2 في هذه الحالة. و يتم تطبيق الصيغة على خلايا التحديد الأخرى بطريقة مشابهة لطريقة التعبئة التلقائية.

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

هذا ما سنراه في القسم التالي.

8.7. عرض وتحرير قواعد التنسيق

لنقم بتعديل القاعدة المحددة في القسم السابق لتمييز السطر بالكامل الذي يكون الشرط صحيحًا فيه:

• انقر فوق خلية في النطاق A2:A80 ؛ حيث تم تطبيق التنسيق الشرطي
• انقر على الأمر التنسيق الشرطي في علامة التبويب الرئيسية للشريط ، واختر إدارة القواعد...

• يفتح مربع الحوار إدارة قواعد التنسيق الشرطي

على مستوى مربع الحوار هذا ،

• يمكنك عرض قواعد التنسيق لـ:
  • التحديد الحالي
  • ورقة العمل الحالية
  • أو أية ورقة من المصنف الحالي
• يتم عرض قائمة القواعد في الجدول أدناه. ففي المقتطف أعلاه ، نجد صفين لقاعدتين للتنسيق
• كما تشير أسمائهم ، تسمح الأزرار قاعدة جديدة... و تحرير القاعدة... و حذف القاعدة ب:
  • إنشاء قاعدة تنسيق جديدة
  • تغيير القاعدة المحددة في الجدول أدناه
  • حذف القاعدة المحددة في الجدول أدناه

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

• قم بتحيين الحقل ينطبق على للقاعدة كما يلي:

=$A$2:$F$80

ملاحظة : لاحظ أن هذا الحقل قابل للتحرير في مربع الحوار.

ملاحظة : يمكنك الآن النقر فوق الزر موافق ورؤية النتيجة. و لكن لا يأخذ باقي السطر التنسيق المحدد.
لماذا؟ أذكرك مرة أخرى أنه مع مراجع الخلية النسبية E2 و F2 ، يزيد Excel من E و F بالنسبة لخلايا الأعمدة B ، C ، ... . وهذا يعني أنه بالنسبة للخلية B2 على سبيل المثال ، فExcel تتحقق من الشرطين في الخلايا F2 و G2 ...
لذلك ، فمن الضروري تعديل صيغة القاعدة إلى: =AND($E2>20000;$F2="الرياض"). و لهذا:

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

=AND($E2>20000;$F2="الرياض")

• انقر فوق الزر موافق للرجوع إلى مربع الحوار إدارة قواعد التنسيق الشرطي
• انقر فوق الزر موافق وشاهد النتيجة:

9.7. إلغاء التنسيق الشرطي

لإلغاء التنسيق الشرطي لنطاق من الخلايا ، حدد نطاق الخلايا واستخدم الزر تحليل سريع ، ثم الزر مسح القواعد:

 

10.7. نسخ التنسيق الشرطي

ككل تنسيق ، يتم نسخ التنسيق الشرطي من نطاق من الخلايا إلى نطاق آخر باستخدام الزر نسخ التنسيق

ولكن ، إذا قمت بنسخ محتوى الخلية ، على سبيل المثال ، باستخدام تقنية التعبئة التلقائية ، فسيتم نسخ التنسيق في نفس الوقت.

تمارين

التمرين - البحث عن التكرارات

---

---