Conditional Formatting در اکسل

---

• کاربرد Conditional Formatting در اکسل چیست؟

در ادامه مباحث آموزشی اینبار به مبحث Conditional Formatting در اکسل می پردازیم. یکی از کاربردهای این ابزار تغییر رنگ متن یک سلول با توجه به عدد سلول مقابل آن است که در ادامه با نحوه پیاده سازی آن آشنا خواهید شد. با استفاده از این ابزار می توانیم سلول هایی را که دارای شرط خاصی هستند را با ظاهری متفاوت نسبت به سایر سلول ها به نمایش در آوریم. اینکار را با اعمال Rules رُل ها انجام خواهیم داد.

این بخش یکی از مباحث بسیار مهم و کاربردی در آموزش اکسل است و یادگیری آن می تواند دست ما را در بکارگیری جلوه های بصری برای نمایش اطلاعات بسیار باز بگذارد. به تصویر زیر توجه کنید :

 

 

فرض کنید در جدول بالا که لیستی از نمرات دانش آموزان است ؛ قصد داریم افرادی را که نمره کتبی آنها کمتر از 8 می باشد با رنگی کردن پس زمینه سلول در نمره کتبی از سایر افراد متمایز کنیم ؛ برای انجام اینکار باید از ابزار کاندیشنال فرمتینگ در اکسل استفاده کنیم. البته کارهای مختلفی را می توان برای ایجاد تمایز انجام داد که در ادامه به معرفی آنها خواهیم پرداخت. در تصویر زیر جایگاه این ابزار را مشاهده می کنید :

 

 

خوب در ادامه به سراغ نحوه کار با این ابزار می رویم و با هم خواهیم دید که چگونه می توان از این ابزار استفاده کرد. ابتدا به معرفی گزینه های قرار گرفته در این ابزار و کاربرد آنها می پردازیم. پس از انجام کلیک روی ابزار که در تصویر بالا آن را ملاحظه می کنید منویی به شکل زیر باز می شود :

 

 

• • Highlight Cells Rules

با استفاده از این ابزار می توانیم در صورتی که سلول یا مجموعه ای از سلول ها دارای شرایط خاصی که در زیر به آنها اشاره می کنیم ؛ بودند ؛ سلول یا سلول ها هایلایت شوند. در گزینه های زیر به معرفی هریک از گزینه ها و تأثیری که خواهند گذاشت می پردازیم :

• • Greater Than : در صورتی که مقدار یک یا مجموعه سلول انتخاب شده از یک مقداری که ما تعیین می کنیم بیشتر باشد. بطور مثال میخواهیم بگوئیم هرگاه مقدار سلول A1 بزرگتر از 10 یا از آدرس سلول دیگری بزرگتر بود ؛ آنگاه سلول A1 هایلایت شود.

همانطور که در تصویر بالا ملاحظه می کنید ؛ عدد 12 را در داخل سلول A1 وارد کرده ایم و با توجه به شرط تعریف شده در کادری که با فلش زرد رنگ مشخص کردیم و نوع رنگی که در لیست سمت راست با فلش سبز رنگ مشخص شده است ؛ این سلول به رنگ قرمز رنگ در آمد.

• • Less Than : همانند مطالب و مثال بالا اینبار در صورتی که عدد وارد شده در سلول A1 کمتر از مقدار مشخص باشد ؛ این سلول به رنگی که در لیست برای آن مشخص کردیم در خواهد آمد.
  • Between : در صورتی که مقدار وارد شده در سلول A1 بین دو عدد ؛ یا بهتر بگوییم در یک بازه عددی قرار گرفته باشد ؛ رنگ سلول مطابق آنچه برایش تعریق شده است تغییر خواهد نمود.

 

 

• • Equal To : در صورتی که مقدار وارد شده در سلول A1 برابر با عددی خاص که در کادر مربوطه وارد می کنیم یا آن که از طریق آدرس دهی برای آن مشخص می کنیم ؛ باشد ؛ آنگاه رنگ سلول مطابق آنچه برایش تعریق شده است تغییر خواهد نمود.

• • Text That Contains : در صورتی که مقدار وارد شده در سلول A1 برابر با عبارت متنی خاصی باشد ؛ رنگ سلول برابر تعریف انجام شده تغییر خواهد کرد.
  • A date Occurring : در صورتی که مقدار وارد شده در سلول A1 از نوع تاریخ بوده و برابر با تاریخ های خاصی ؛ مثل تاریخ امروز ؛ دیروز و … باشد ؛ آنگاه رنگ سلول با توجه به تعریف انجام شده تغییر خواهد کرد.

 

 

• • Duplicate Values : برای پیدا کردن مقدایر تکراری در یک لیست و هایلایت کردن آنها از این گزینه استفاده می کنیم ، به تصویر زیر دقت کنید.

 

• • More Rules : با کلیک کردن بر روی این گزینه وارد یک پنجره بشکل زیر خواهید شد که تفاوت چندانی از نظر شرط های ایجاد کننده با آنچه که در بالا به آن اشاره شد ندارد. تنها تفاوت آن این است که در لیست باز شده برخی ویژگی های جدید را برای سلول حاوی شرط در دسترس شما قرار می دهد که می توانید در تصویر زیر به بخشی که فلش سبز رنگ به آن اشاره دارد نگاه کنید.

 

• • Top/Bottom Rules

با استفاده از این ابزار می توانیم همانند مواردی که در بالا راجع به آن صحبت کردیم ؛ متناسب با شروطی که اعمال میکنیم ؛ نتایج را بصورت اعمال هایلایت بر روی سلولها مشاهده کنیم.

• • Top 10 Items :

    • با استفاده از این گزینه می توان به تعداد مشخصی که بصورت پیشفرض این تعداد 10 تعیین شده ؛ اقدام به یافتن اعداد بزرگتر نسبت به اعداد قرار گرفته در یک بازه کنیم. به تصویر زیر نگاه کنید ؛ در تصویر شماره 1 به اکسل گفته ایم که بیا و از بین محدوده ای از اعداد که برایت مشخص کردیم که دارای 12 عدد است ؛ 10 عدد بزرگتر نسبت به بقیه را مشخص کن و ملاحظه می کنید که این اتفاق در تصویر شماره 1 افتاده است. در تصویر شماره 2 همان عدد 10 را به 7 تغییر دادیم و درواقع گفتیم که از بین 12 عدد موجود در بازه اعداد 7 عدد بزرگتر را هایلایت کن.

 

 

• • Top 10%  :

    • با استفاده از این گزینه می توان همانند آنچه در مورد قبلی به آن اشاره کردیم اقدام کنیم ؛ اما اینبار مبنای محاسبات بر حسب درصد خواهد بود. بدین معنی که به اکسل میگوئیم از بین 12 عددی که در بازه ما قرار دارد ؛ 10 درصد اعدای که بزرگتر از بقیه هستند را هایلایت کن ؛ اگر میزان این درصد را تغییر دهیم ؛ تعداد اعداد بیشتر خواهد شد ؛ مثلأ اگر بگوئیم 25% اعداد بزرگتر را برای ما هایلایت کن ؛ اعداد 60 و 75 و 90 هایلایت خواهد شد ؛ در واقع 3 عدد از 12 عدد می شود همان 25% از اعداد و چون گفته ایم Top ؛ بزرگترین اعداد گفتیم هایلایت خواهند شد.

• • Bottom 10 Items :

    • این گزینه برخلاف Top 10 items تعداد 10 رقمی را که در یک بازه مشخص ؛ نسبت به بقیه کوچکتر هستند ؛ هایلایت خواهد کرد.

  • Bottom 10%  :

    • با استفاده از این گزینه بر خلاف Top 10% بدین صورت عمل می کند که 10% اعدادی که در یک بازه مشخص ؛ نسبت به بقیه کوچکتر هستند را هایلایت خواهد کرد.

  • Above Average  :

    • این گزینه ابتدا میانگین سلول های انتخاب شده را بدست می آورد ؛ سپس اعدای را که در همان بازه ؛ بزرگتر از میانگین هستند را هایلایت می کند.

  • Below Average  :

    • این گزینه ابتدا میانگین سلول های انتخاب شده را بدست می آورد ؛ سپس اعدای را که در همان بازه ؛ کوچکتر از میانگین هستند را هایلایت می کند.

 

• • Data Bars

این ابزار نسبت به بزرگ یا کوچک بودن اعداد قرار گرفته در یک یا مجموعه ای از سلول ها ؛ بصورت Bar مانند عمل هایلایت را انجام میدهد ؛ برای درک بهتر از عملکرد این ابزار به تصویر زیر نگاه کنید.

• • Color Scales

این ابزار هم مانند Data Bars نمایش دیگر از کوچک یا بزرگ بودن اعداد قرار گرفته در سلول ها را به نمایش در می آورد. به تصویر زیر نگاه کنید.

 

 

• • Icon Sets

در این ابزار میزان کوچک یا بزرگ بودن اعداد نسبت به هم در یک بازه عدد ؛ بصورت آیکون هایی به نمایش در می آید. به تصویر زیر نگاه کنید.

 

• Clear Rules

با استفاده از این گزینه Rule رُل های تعریف شده را که هریک ؛ با توجه به کوچک و بزرگ بودن عدد از نظر گرافیکی تغییراتی را در داخل یک یا مجموعه ای از سلول ها ایجاد می کردند ؛ می توانیم پاک کنیم.

یکی از موارد مهمی که در استفاده از ابزار Conditional Formatting بایستی به آن توجه داشت این است ؛ که در صورتی که بخواهیم یک Rule جدید را روی یک یا مجموعه ای از سلول ها اعمال کنیم ؛ برای آنکه اگر احیانأ نخواهیم رول ها روی هم بیفتند و در کنار هم نمایش داده نشوند ؛ بایستی از طریق گزینه Clear Rules یکی از آنها را پاک کنیم.

نحوه کار با این ابزار هم بسیار ساده است ؛ کافی است سلول یا محدوده ای از سلول ها را انتخاب کرده مطابق تصویر زیر در مرحله 1 روی گزینه Clear Rules کلیک کنیم ؛ در این حالت یک منو که شامل 4 گزینه است به نمایش در می آید که در این بخش به معرفی گزینه های اول و دوم این لیست که با شماره های 2 و 3 نمایش داده شده می پردازیم.

 

• Clear Rules From Selected Cells : با انتخاب این گزینه ؛ کلیه Rule های اعمال شده بر روی سلول های انتخابی از بین خواهد رفت.
• Clear Rules From Entire Sheet : با انتخاب این گزینه ؛ کلیه Rule های اعمال شده بر روی شیتی که در درون آن قرار داریم ؛ از بین خواهد رفت.

• New Rules

علاوه بر مواردی که برای ساخت Rule های آماده و از پیش ساخته شده در اکسل گفتیم ؛ از بخش دیگری بنام New Rules این امکان وجود دارد تا بتوانیم بصورت دستی رُل هایی را ایجاد کنیم. بدین منظور مطابق تصویر بالا ملاجظه می کنید که گزینه New Rules در بالای Clear Rules قرار گرفته است ؛ با کلیک کردن بر روی این گزینه ، پنجره زیر به نمایش در خواهد آمد :

 

• • Format all cells based on their values :

  • Format Style : در این بخش می توانیم نوع نمایش گرافیکی سلول ها را پس از اعمال آن مشخص کنیم که شامل گزینه های زیر است :
    • 2Color Scale : ایجاد فرمت گرافیکی با دو رنگ
    • 3Color Scale : ایجاد فرمت گرافیکی با دو سه رنگ
    • Data Bar : ایجاد فرمت بصورت data bar
    • Icon Sets : ایجاد فرمت بصورت نمایش از طریق آیکون ها

با انتخاب هریک از گزینه هایی که اشاره شد ؛ وارد بخش تنظیمات آنها می شویم ؛ در این قسمت برخی موارد متناسب با نوع گزینه به نمایش در می آید که شامل موارد زبر است :

• • 2Color Scale

• • • Type : در این قسمت نوع دیتای قرار گرفته در داخل سلول ها را تعیین می کنیم ؛ که در دو بخش Minimum و Maximum باید مشخص شود که گزینه های قرار گرفته در داخل این لیست شامل : Lowest Value در بخش Minimum و Highest Value در بخش Maximum ؛ Number ؛ Percent ؛ Formula ؛ Percentile
    • Value : در این گزینه در بخش Minimum بایستی مقدار حداقل و در بخش Maximum مقدار حداکثر را وارد کنیم.
    • Color : نوع رنگ را تعیین می کنیم.

نکته ای که باید به آن توجه داشت این است که با انتخاب سایر گزینه ها در بخش Format Style برخی گزینه های دیگر متناسب با این انتخاب به پنجره تنظیمات اضافه خواهد شد. بطور مثال با انتخاب 3Color Scale گزینه ای مابین Minimum و Maximum به نام Midpoint به آن اضافه می شود که تصویر آن را در زیر ملاحظه می کنید.

• • 3Color Scale

• • Data Bar

در صورتی که Data Bar را انتخاب کنیم پنجره تنظیمات آن بصورت زیر خواهد بود :

 

همانطور که در تصویر بالا ملاحظه می کنید ؛ بخش Bar Appearance به تنظیمات اضافه شده است که شامل گزینه های زیر است :

• • Fill : در این قسمت می توان مشخص نمود که فرمت اعمال شده از نوع توپر یا هایلایت باشد.
  • Color : در این قسمت رنگ مربوط به Fill را مشخص می کنیم
  • Border : مشخص کردن قاب دور
  • Color : در این قسمت رنگ مربوط به Border را مشخص میکنیم
  • Negative Value and Axis : تنظیم مقادیر منفی و نحوه نمایش آنها
  • Bar Direction : در این قسمت جهت نمایش Bar را تعیین میکنیم ؛ در صورتی که روی Context قرار داشته باشد ؛ جهت نمایش از تنظیمات ویندوز تبعیت خواهد کرد.

• • Icon Sets

آخرین گزینه قرار گرفته در داخل Format Style بخش Icon Sets است ؛ با استفاده از این ابزار می توانیم از آیکون ها برای فرمت بندی قالب سلول ها استفاده کنیم ؛ البته بالاتر راجع به این مسأله صحبت کردیم ؛ اما موارد تکمیلی را همراه با تصویر زیر توضیح خواهیم دارد

 

• • Icon Style : از این بخش و گزینه های قرار گرفته در داخل آن می توان نوع شکل آیکون های مورد نظر خود را انتخاب کنیم.
  • در بخش Display each icon according to htese rules هم می توانیم بازه هایی از اعداد ؛ نوع دیتا و شکل آیکنی که مورد نظر برای هر بازه است را تعیین کنیم.

• • Format Only Cells That Contain :

 

با انتخاب گزینه های قرار گرفته در داخل لیست اول که با Cell Value شروع می شود ؛ می توان متناسب با گزینه های قرار گرفته بعدی ؛ شروطی را اعمال نمود که با برقرار بودن آن شروط قالب بندی مورد نظر ما که از طریق زدن دکمه Format در بخش پائینی پنجره قرار گرفته است ؛ تنظیم می شود.

• • Format only top or bottom ranked values

 

این بخش هم همانند آنچه در Top /Bottom Rules گفتیم ؛ به تعداد مشخصی از اعداد بزرگتر یا کوچکتر از بقیه را متناسب با تنظیماتی که با زدن دکمه Format میتوانیم ؛ اعمال کنیم ؛ هایلایت خواهد کرد. با زدن تیک گزینه of the selected range% محاسبات پیدا کردن اعداد بزرگ یا کوچک بر مبنای درصد انجام خواهد شد.

• • Format only Values that are above or below average

 

 

این بخش هم عملکردی Above Average و Below Avreage دارد و از طریق آن میتوان اعدادی را که کمتر یا بیشتر از میانگین محدوده اعداد انتخابی است را هایلایت کنیم.

• • Format only unique or duplicate values

 

از طریق این گزینه سلول های تکراری یا یونیک قابل فرمت بندی کردن خواهند بود و برای تنظیم شکل فرمت بندی از دکمه Format می توانیم ؛ اقدام کنیم.

• • Use a Formula to determine which cells to format

 

 

با استفاده از این گزینه میتوان با نوشتن یک شرط در داخل کادر مربوطه ؛ فرمت بندی را انجام داد ؛ قبلأ در آموزش مقایسه دو لیست در اکسل با نوشتن یک شرط به نحوه انجام کار با این قسمت پرداخته ایم.

• Manage Rules

با کلیک بر روی گزینه Manage Rules پنجره Conditional Formatting Rules Manager بصورت شکل زیر باز خواهد شد. در تصویر زیر مثالی از اعمال یک رُول بر روی محدوده ای از سلول ها و نمایش آن در پنجره مدیریت رُل ها را مشاهده می کنید که دارای اجزائی می باشد که در زیر به آنها می پردازیم :

 

• show formatting rules for  (بخش 1) :

  • در این گزینه مشخص می کنیم که قرار است Rule های مربوط به کجا را ویرایش کنیم ؛ در حالت پیشفرض این گزینه بر روی Current Selection قرار دارد و به این معنی است که در حال ویرایش رُل های موجود در محدوده انتخابی قرار داریم ؛ همانطور که در تصویر بالا ملاحظه می کنید ؛ ما یک محدوده ای از اعداد را انتخاب کرده ایم و یک رُل نیز در این محدوده اعمال شده است که برابر است با Cell Value > 50 و رُل دیگری در محدوده انتخاب شده نداریم. حالا اگر از داخل لیست کشویی گزینه This WorkSheet را انتخاب کنیم ؛ در صورتی که علاوه بر محدوده انتخاب شده در محل دیگری از شیت یک یا چند رول دیگر اعمال کرده باشیم در لیست رُل ها آنها را خواهیم دید. همچنین با انتخاب گزینه های بعدی که به نام سایر شیت های موجود در فایل اکسل ما می باشد ؛ می توانیم رُل های موجود در آن شیت ها را نیز ببینیم.

• New Rule  (بخش 2) :

  • با استفاده از این گزینه ؛ قادر خواهیم بود تا یک Rule جدید به رل های موجود اضافه کنیم.

• Edit Rule  (بخش 3) :

  • برای ویرایش Rule های موجود می توانیم پس از انتخاب هر رُل و کلیک بر روی این گزینه آن را ویرایش کنیم.

• Delete Rule  (بخش 4) :

  • بعد از انتخاب رُل یا مجموعه ای از رُل ها ؛ با کلیک بر روی این گزینه عمل حذف انجام خواهد شد.

• (بخش 5) :

  • در این بخش Rule های ایجاد شده را می توانید ملاحظه کنید ؛ در حال حاضر مطابق تصویر بالا یک Rule داریم.

• (بخش 6) :

  • با استفاده از این گزینه می توان بین Rule های موجود بالا یا پائین رفت و حرکت نمود. در واقع انتخابگر با استفاده از این رُل ها جابجا می شود.

---