آموزش Conditional Formatting اکسل – قالب بندی شرطی

آموزش Conditional Formatting اکسل

قبل از اینکه توضیح دهیم ابزار Conditional Formatting چیست و چطور می تواند نیاز های ما را برآورده کند، با ذکر یک مثال ساده  یکی قابلیت های جذاب این ابزار را به شما آموزش می دهیم.

فرض کنید یک ستون دارید که در آن کد ملی افراد مختلف درج شده است.متاسفانه در این ستون کد ملی بعضی از افراد تکرار شده است.از شما می خواهند کد ملی که بیش از یکبار تکرار شده است را با رنگ (یا فرمت) مشخص کنید.

برای این کار ابتدا داده های خود را انتخاب می کنیم،سپس مسیر زیر را طی می کنیم:

Home>Styles>Conditional Formatting>Duplicate Values

همان طور که مشاهده می کنید،به کمک این ابزار توانستیم،داده های تکراری را پیدا کرده و آن را را با رنگ (یا فرمت) دیگر متمایز کنیم.اگر تمایل دارید که کل داده های تکراری را مشاهده کنید، می توانید از ابزار Filter در اکسل استفاده نمایید. اگر قصد دارید داده های تکراری را کاملا حذف کنید، به مطلب ” حذف تکراری ها در اکسل” مراجعه نمایید. اگر می خواهید با ابزار Conditional Formatting این کار را انجام دهید به مثال آخر این مقاله توجه کنید.

توجه:این رنگ بندی با رنگ بندی که به صورت فرمت عددی قابل نمایش می باشد، کاملا متفاوت می باشد.

با این مقدمه کوتاه،به شما نشان دادیم که این ابزار به صورت بصری تا چه حد می تواند سرعت کار شما را بالا ببرد.تصور کنید اگر قرار بود به صورت چشمی این کار را انجام دهید، تا چه حد سخت و همراه با خطا این کار صورت می گرفت.

رنگ ها بهترین ابزار برای متمایز سازی هستند و در تمام قسمت های زندگی ما انسان ها نقش اساسی دارند.به عنوان مثال یک آکواریم پر از ماهی را در نظر بگیرید.اگر همه ماهی ها به صورت یک رنگ باشند،شما نمی توانید یک ماهی را به طور خاص دنبال کنید ولی اگر هر ماهی دارای رنگ خاص باشد، می توانید به راحتی آن را در این آکواریوم دنبال کنید.

فلسفه ایجاد ابزار Conditional Formatting همین می باشد. یعنی می خواهد رنگ بندی را مطابق با خواسته های شما در کاربرگ اکسل ایجاد نماید.اما چرا از واژه Formatting استفاده کرده است؟ زیرا که در اکسل رنگ را می توانیم به متن یا سلول یا بردر سلول و… اعمال کنیم.به همین دلیل چون هر کدام از این اجزا فرمت یا قالب مخصوص خود را دارند، از عبارت کلمه فرمت استفاده شده است.

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

بخش های Conditional Formatting :

بخش اول :                                                        بخش دوم:                                            بخش سوم:

New Rule                                                 Data Bars                                             Highlight Cells Rules

Clear Rules                                              Color Scales                                              Top/Bottom Rules

Manage Rules                                                 Icon Sets                                              Top/Bottom Rules

بخش اول : 

اجزا بخش Highlight Cells Rules:

Grater Than: اعداد داخل سلول ها را که از یک مقدار مشخص بیشتر باشد،فرمت دهی می کند.
Less Than: اعداد داخل سلول ها را که از یک مقدار مشخص کمتر باشد،فرمت دهی می کند.
Between : اعداد داخل سلول ها را که بین دو مقدار مشخص باشد،فرمت دهی می کند.
Equal : اعداد داخل سلول ها را که برابر با یک مقدار مشخص باشد،فرمت دهی می کند.
Text That Contains : متون داخل یک سلول را با یک متن خاص مقایسه کرده و اگر یکسان بودند، آنگاه آن ها را فرمت دهی می کند.
A Date Occurring : سلول هایی که تاریخ آن ها در محدوده خاصی باشد را فرمت دهی می کند.
Duplicate Values: داده های درون یک محدوده را با یکدیگر مقایسه کرده و چنانچه تکراری باشند، آن ها را برای ما فرمت دهی می کند.

به عنوان مثال جدول زیر گردش مالی سه شخص در ماه های مختلف می باشد، از شما می خواهند گردش مالی هایی که کمتر از 2000 بوده اند را پیدا کنید.برای این کار از گزینه Less Than استفاده می کنیم:

اجزا بخش Top/Bottom Rules:

Top 10 Items: اعداد داخل یک محدوده را بررسی کرده و 10 عددی که بیشترین مقدار را دارند، فرمت دهی می کند.
Top 10% : اعداد داخل یک محدوده را بررسی کرده و %10 عددی که بیشترین مقدار را دارند، فرمت دهی می کند.
Bottom 10 Items : اعداد داخل یک محدوده را بررسی کرده و 10 اعددی که کمترین مقدار را دارند، فرمت دهی می کند.
Bottom 10% : اعداد داخل یک محدوده را بررسی کرده و %10 اعددی که کمترین مقدار را دارند، فرمت دهی می کند.
Above Average : اعداد داخل یک محدوده را بررسی کرده و اعددی که بیشتر از میانگین هستند را فرمت دهی می کند.
Below Average : اعداد داخل یک محدوده را بررسی کرده و اعددی که کمتر از میانگین هستند را فرمت دهی می کند.

به عنوان مثال جدول زیر گردش مالی سه شخص در ماه های مختلف می باشد، از شما می خواهند گردش مالی هایی که بیشتر از میانگین کل هستند را پیدا کنید.برای این کار از گزینه Above Average استفاده می کنیم:

بخش دوم: شامل سه گزینه Data Bars و Color Scales و Icon Sets می باشد.این بخش ها به مقادیر محدوده ما یک حالت گرافیکی به صورت اشیا مختلف اعمال می کنند.نکته ای که باید برای این بخش ها در نظر داشته باشید این است که برای بالاترین اعداد ما بزرگترین شی را در نظر می گیرند.یعنی مبنای اشیا ما بر اساس بزرگترین داده ما می باشد.

بخش سوم: شامل New Rule و Clear Rules و Manage Rules می باشد. این بخش در واقع برای زمانی می باشد که هیچ کدام از ابزارهای های آماده Conditional Formatting پاسخگوی نیاز شما نباشند و باید بر اساس نیاز خود آن ها را ایجاد کنید،حذف کنید یا مدیریت کنید.

با یک مثال ساده این بخش را آموزش می دهیم.مثال اول این مقاله را در نظر بگیرید،اگر دقت کرده باشید ابزار خود اکسل یعنی Duplicate Values داده های تکراری را پیدا کرد اما خواسته ما این می باشد که اگر بیشتر از یکی تکراری داشتیم، آنگاه همه را به جز اولی برای ما فرمت دهی (رنگی) کند.مانند تصویر زیر:

برای این کار بر روی گزینه Manage Rules کلیک می کنیم

 سپس بر روی گزینه New Rule کلیک می کنیم:

از پنجره باز شده گزینه Use a formula to determine which cells to format را انتخاب می کنیم:

=IF(COUNTIF($A$1:A1,A1)>1,1,0)

و سپس سربرگ Fill را انتخاب می کنیم و رنگ مورد نظر را انتخاب کنید:

اکنون بر روی تمام پنجره ها بر روی OK کلیک می کنیم تا به پنجره زیر برسیم، سپس در قسمت Applies to محدوده که قصد داریم تا Conditional Formatting بر آن اعمال گردد را قرار می دهیم:

خوب اکنون به خواسته خود رسیدید و می توانید به راحتی با ابزار فیلتر، تکراری ها را جدا نمایید.

یک مثال کاربردی دیگر از این ابزار را در محاسبه ضرب دو ماتریس مشاهده نمایید.