كورس الأكسل

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

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

طريقة استخدام التنسيق الشرطي للمقارنة بين الارقام حسب الالوان في اكسل ( Conditional Formatting )

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

المقارنة بين الارقام حسب الالوان في اكسل باستخدام التنسيق الشرطي | خاصية Color Scales.

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

• أولاً، سنقوم بتحديد القيم التي نريد مقارنتها من خلال عملية تحديد وسحب بزر الفأرة الأيمن.

• بعد ذلك من شريط الأدوات لدينا النافذة ” Home “، ومن ثم الخيار ” Conditional Formatting “.

• بعد ذلك نضغط الخيار ” Color Scales ” من القائمة المنسدلة، ومن ثم نختار أحد مجموعات الألوان. حيث سيتم اعتماد هذه المجموعات لتلوين الخلايا وذلك بحيث يكون اللون الأول خاص بالأعداد الأكبر من ضمن القائمة، ومن ثم اللون الثاني للأعداد التي قيمتها أقل. وهكذا…

وبالتالي نلاحظ أن القيم الكبرى تلونت باللون الأخضر الغامق، ومن ثم القيم الأصغر منها تلونت باللون الأصفر، وهكذا.

معرفة الارقام التي هي اكبر من رقم معين في اكسل | خاصية Greater than.

بفرض نريد تحديد المبيعات التي تتجاوز الرقم 500، حيث لدينا مجموعة البيانات ذاتها التي استخدمناها في المثال السابق. عندئذ سوف نتبع الخطوات التالية:

• أولاً، نقوم بتحديد البيانات التي سوف نجري المقارنة عليها من خلال عملية تحديد وسحب على الخلايا التي نريدها باستخدام الزر الأيمن للفأرة.

• ومن ثم من شريط الأدوات لدينا النافذة ” Home “. ومن ثم نختار ” Conditional Formatting “.

•  وبعد ذلك نختار ” Highlight Cells Rules ” من ” Conditional Formatting “، ومن ثم سوف نختار ” Greater than “.

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

في مثالنا هذا سنفرض الرقم 500 ومن ثم نختار ” Ok “. ونتيجة لذلك نلاحظ أن الأرقام التي قيمتها أكبر من 500 أصبحت ملونة باللون الأحمر، وبالتالي تم تمييز كل الخلايا التي تحوي أعداد قيمتها أكبر من 500 باللون الأحمر.

معرفة الارقام التي هي اصغر من رقم معين في اكسل | خاصية Less than

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

من ثم من شريط الأدوات لدينا النافذة ” Home “. وبعد ذلك سنختار ” Conditional Formatting “.

وأخيراً، من ” Conditional Formatting ” سوف نختار ” Highlight Cells Rules “، ومن ثم سوف نختار أيضاً ” Less than “.

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

في مثالنا هذا سنفرض الرقم 500 ومن ثم نختار ” Ok “. ونتيجة لذلك نلاحظ أن الأرقام التي هي أصغر من 500 أصبحت ملونة باللون الأحمر.

معرفة الارقام التي هي تقع ضمن مجال معين استخدام التنسيق الشرطي | خاصية Between.

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

• من شريط الأدوات ومن ” Home ” نختار ” Conditional Formatting “، وبعد ذلك سوف نختار ” Highlight Cells Rules “.

• وهنا سوف تظهر قائمة منسدلة، والتي منها سوف نختار ” Between… “.

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

في مثالنا هذا قمنا باختيار المجال [400,450].

• نضغط ” Ok ” وتكون النتيجة تلوين الخلايا التي تحتوي القيم الواقعة ضمن المجال المحدد.

تحديد نصوص معينة باستخدام التنسيق الشرطي في اكسل | خاصية Text that Contain.

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

• ثانياً، من شريط الأدوات ومن ” Home ” نختار ” Conditional Formatting “.

ومن ” Conditional Formatting ” نختار ” Highlight Cells Rules “، ومنها سوف نختار أيضاً ” Text that Contains… “.

• وبعد ذلك سوف تظهر لنا لافتة نكتب فيها اسم العنصر (أو أي جزء من العنصر) الذي نريد أن يتم تلوينه كلما تكرر.

• وعلى فرض أننا نريد أن يتم تلوين الاسم ” الفيل الأزرق ” في كل مرة يتكرر فيها، إذاً سوف نكتب ” الفيل الأزرق ” ومن ثم سنضغط ” Ok “.

حيث تفيد هذه العملية في حذف التكرار، أو إيجاد عبارة لا نتذكر منها إلاّ جزء بسيط في اكسل.

فرز المواعيد والتواريخ باستخدام التنسيق الشرطي | خاصية Date Occurring.

• أولاً، نقوم بتحديد البيانات التي سنجري المقارنة عليها وذلك من خلال عملية تحديد وسحب على الخلايا المطلوبة باستخدام زر الفأرة الأيمن..
• ثانياً، من شريط الأدوات لدينا النافذة ” Home “. ومن ثم الخيار ” Conditional Formatting “.
• ثالثاً، نختار ” Conditional Formatting “. وبعد ذلك ” Highlight Cells Rules “، ومنها أيضاً لدينا ” Date Occurring A “.
• وأخيراً، تظهر لنا لافتة نختار منها التاريخ الذي نريد أن يتم تلوينه.

تحديد النصوص المتكررة عبر تلوينها باستخدام التنسيق الشرطي في اكسل | خاصية   Duplicate Values.

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

• من شريط الأدوات نختار ” Home “، ومن ثم ” Conditional Formatting “.

• ومن ثم من ” Conditional Formatting ” نختار ” Highlight Cells Rules “، ومنها سوف نختار أيضاً ” Duplicate Values “.

• تظهر لنا اللافتة التالية.

نضغط ” Ok ” نلاحظ كيف تلونت الخلايا المكررة باللون الأحمر.

تحديد اعلى قيمة عبر تلوينها باستخدام التنسيق الشرطي في اكسل | خاصية   Top 10 Items.

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

• من شريط الأدوات لدينا النافذة ” Home ” . ومن ثم لدينا ” Conditional Formatting “.

• وأخيراً، من ” Conditional Formatting ” نختار ” Top/Bottom Rules “، ومنها سوف نختار أيضاً ” Top 10 Items”.

• وكنتيجة سوف تظهر لنا اللافتة التالية، ويدل فيها الرقم الموجود على اليسار على عدد أعلى القيم البيانات التي قمنا بتحديدها.

وهنا سوف نختار أن يقوم اكسل بتلوين أعلى 3 قيم من ضمن البيانات، أي سنضع الرقم 3.

• نختار ” Ok “.

ملاحظة: تطبق نفس الخطوات في حال الخاصية Bottom 10″ Items ” التي سوف تعطينا أقل القيم من ضمن البيانات المحددة.

تحديد اعلى n% قيمة عبر تلوينها باستخدام التنسيق الشرطي في اكسل | خاصية   Top 10 %.

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

• وبعد ذلك من شريط الأدوات لدينا النافذة ” Home “. ومن ثم الخيار ” Conditional Formatting “.

• من ” Conditional Formatting ” لدينا الخيار ” Top/Bottom Rules “، ومن ثم سوف نختار منه ” Top 10 %”.

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

وهنا سوف نختار أن يقوم اكسل بتلوين أعلى 3 بالمئة قيم من ضمن البيانات، أي سنضع الرقم 3.

• نختار ” Ok “.

ملاحظة: تطبق نفس الخطوات في حال الخاصية” Bottom 10% ” التي سوف تعطينا أقل القيم من ضمن البيانات المحددة مقاسة كنسبة مئوية.

تحديد اعلى القيم عن التوسط عبر تلوينها | خاصية  Above Average.

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

• من شريط الأدوات ومن ” Home ” نختار ” Conditional Formatting “.

• بعد ذلك من ” Conditional Formatting ” لدينا ” Top/Bottom Rules “، ومن ثم سوف نختار أيضاً ” Above Average “.

• تظهر لنا اللافتة، يقوم الاكسل في هذه الخاصية بحساب المتوسط (Average) للأرقام أو البيانات المحددة، ومن ثم يحدد الأرقام التي قيمتها أكبر من هذا المتوسط.

• نختار ” Ok “.

ملاحظة: تطبق نفس الخطوات في حال الخاصية ” Below Average   ” التي سوف تعطينا الأرقام التي قيمتها أقل من المتوسط الحسابي للبيانات المحددة.