فرمت دهی شرطی (Conditional formatting) در اکسل

فرمت دهی شرطی (Conditional Formatting) در اکسل، ویژگی قدرتمندی است که با استفاده از آن می توانید به سادگی ظاهر سلول ها و محدوده های سلولی مورد نظر را بر اساس شرط هایی که تعیین می کنید، تغییر دهید تا بهتر نمایش داده شوند.

این ویژگی تفسیر و درک داده ها را برای کاربر راحت می کند. همچنین فرمت دهی شرطی در مرتب سازی داده ها کاربرد دارد.

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

با باز کردن منوی Conditional Formatting در تب Home، مشاهده می کنیم که این ابزار شامل دو بخش اصلی می باشد.

بخش اول (الگو های از پیش تعریف شده)

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

گزینه Highlight Cells Rule

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

این قوانین به شرح زیر می باشد:

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

به عنوان مثال، می خواهیم به ستون B در جدول زیر، فرمتی بدهیم به این صورت که سلول هایی که شامل اعداد بیشتر از 200 هستند، رنگ آبی بگیرند و سلول های شامل اعداد کمتر از 200 رنگ نارنجی داشته باشند.
– ابتدا سلول های ستون B را انتخاب می کنیم و پس از انتخاب Highlight Cells Rules در Conditional Formatting، قانون Greater Than را کلیک می کنیم.

سپس شرایط مورد نظر را بر حسب نیاز و سلیقه اعمال می کنیم:
– مقدار مورد نظر را در باکس Format cells that are Greater Than موجود در سمت راست پنجره وارد می کنیم. در اینجا، می بایست 200 وارد شود. زمانی که مقدار را وارد می کنیم، اکسل سلول هایی را که شرط ما را داشته باشند، هایلایت می کند.
– از منوی سمت راست پنجره، می توانیم یکی از فرمت های از پیش تعریف شده را انتخاب کرده یا بر روی Custom Format کلیک کنیم و فرمت مورد نظر خود را تنظیم نمائیم.
– در پنجره format cell، می توانیم نوع فونت، خطوط حاشیه ای و رنگ مورد نظر خود را انتخاب کنیم. برای این کار می توانیم بین پنجره هایی که برای همین منظور قرار داده شده است جابه جا شویم. اگر می خواهیم یک رنگ پیش زمینه مدرج (gradient) را انتخاب کنیم، در پنجره fill، گزینه fill effect را انتخاب کرده و ویژگی مورد نظر را انتخاب می کنیم.

برای نارنجی کردن اعداد کوچکتر از 200 هم مراحل بالا را طی می کنیم با این تفاوت که بجای Greater Than، Less Than را انتخاب می کنیم و بجای رنگ آبی رنگ نارنجی را انتخاب می کنیم.
پس نتیجه به صورت زیر نمایش داده می شود.

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

گزینه Top/Bottom Rules

این گزینه هم قوانین زیر را در اختیار شما قرار می دهد:

• Top 10 Items و Top 10% که تعدادی یا درصدی دلخواه از بیشترین مقادیر ستون را فرمت دهی می کند. عدد 10 پیش فرض این قسمت از ابزار Conditional Formatting هست. با انتخاب یکی از این گزینه ها، پنجره ای باز می شود که شما را قادر به تغییر عدد 10 می کند.
• Bottom 10 Items و Bottom 10% تعدادی یا درصدی دلخواه (در اینجا به عنوان پیش فرض 10 گفته شده) از کمترین مقادیر ستون را فرمت دهی می کند.
• Above Average و Below Average به ترتیب مقادیر بیشتر و کمتر از میانگین مقادیر کل ستون را هایلایت می کند.

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

پس از OK کردن می توانیم نتیجه را مشاهده کنیم.

گزینه Data Bars

با استفاده از این مورد می توانیم از نمودار های رنگی درون سلولی برای مصور سازی داده ها استفاده کنیم. یعنی بین یک محدوده عدد، بزرگترین عدد را پر می کند و بقیه اعداد را نسبت به آن، محاسبه می کند و سلول را پر می کند.
برای نمونه سلول های ستون F را انتخاب می کنیم. در Conditional Formatting با انتخاب گزینه Data Bars به نمودار ها با رنگ های مختلفی دسترسی پیدا می کنیم. می توانیم یکی از نمودار های از پیش تعریف شده را انتخاب کنیم.

برای مشاهده انتخاب های بیشتر ما More Rules را کلیک کردیم تا پنجره New Formatting Rule باز شود و به رنگ ها و گزینه های بیشتری دسترسی پیدا کنیم.

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

گزینه Color Scales

این گزینه، طیفی از یک رنگ یا چند رنگ مختلف را به یک بازه از بیشترین عدد به کمترین می دهد. برای انجام این نوع فرمت دهی در ابتدا گزینه Color Scales را از Conditional Formatting انتخاب می کنیم. سپس می توانیم یکی از الگو های رنگی از پیش تعریف شده را به ستون انتخاب شده اعمال کنیم و یا در More Rules به رنگ های بیشتری دسترسی پیدا کنیم.

بنابراین با انجام این عمل ستون H شامل طیفی از رنگ نارنجی (از نارنجی پررنگ برای کمترین مقدار به نارنجی کم رنگ برای بیشترین مقدار) می شود.

گزینه Icon Sets

در این قسمت می توانیم از آیکون های مختلف برای فرمت دهی شرطی استفاده کنیم. به عنوان مثال در ستون J می توانیم به مقادیر مثبت، صفر و منفی آیکون های مختلفی بدهیم تا کاربر با یک نگاه سریع به روند بازار پی ببرد. برای انجام این نوع فرمت دهی، ابتدا ستون مورد نظر را انتخاب می کنیم. گزینه Icon Sets را از Conditional Formatting کلیک می کنیم و آیکون های دلخواه را انتخاب می کنیم. و یا با انتخاب More Rules، شرایط دلخواه خود را در پنجره New Formatting Rule تعریف می کنیم.

همانطور که در تصویر مشاهده می کنید، در پنجره New Formatting Rule برای عدد های بیشتر از 1، فلش سبز رنگ رو به بالا، برای مقادیر کوچکتر از 1-، فلش قرمز رنگ رو به پایین و برای رقم های بین 1 و 1- هم فلش مستقیم زرد رنگ را در نظر گرفته ایم.

بنابراین برای هر سلول متناسب با شرطی که تعیین کردیم، آیکون مناسب در نظر گرفته می شود.

بخش دوم (مدیریت قوانین)

این بخش شامل گزینه هایی برای تعریف قوانین دلخواه خود، حذف قوانین و ویرایش قوانین اعمال شده، است.

گزینه New Rule

اگر هیچ یک از قوانین conditional formatting نیاز شما را برطرف نمی کند، می توانید یک قانون جدید ایجاد کنید و تمام جزئیات آن را مطابق میل خود تنظیم کنید.

سلول های مورد نظر را انتخاب کنید. سپس Conditional Formatting → New Rule را انتخاب کنید.
پنجره New Formatting Rule باز می شود و می توانید قانون مورد نظر خود را انتخاب کنید. برای مثال، می توانید “Use a formula to determine which cells to format” را انتخاب کنید و فرمول خود را بسته به نیاز در کادر وارد کنید. قانون شما تنها زمانی اعمال خواهد شد که نتیجه‌ی فرمول‌تان “True” باشد، یعنی شرط شما درست باشد.

گزینه Clear Rule

زمانی که شما قصد داشتید یک یا همه قوانین مربوط به Conditional Formatting را حذف کنید، به این گزینه نیاز دارید. با کلیک بر روی این گزینه، دو انتخاب پیش روی شماست:

Clear Rules from Selected Cell

با انتخاب این مورد می توانید فقط یک قانون خاص را با انتخاب سلول های شامل آن قانون، حذف کنید.

Clear Rules from Entire Sheet

این مورد تمامی فرمت های تعریف شده در Sheet را حذف می کند.

گزینه Manage Rules

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

سپس پنجره Conditional Formatting Rules Manager، باز می شود. قانونی را که می خواهید تغییر دهید انتخاب کنید و با انتخاب دکمه edit rule آن را تغییر دهید.
توجه داشته باشید که شما می توانید برای هر محدوده یا سلول چند قانون تعریف کنید. اگر تمام قوانین در مورد آن محدوده درست باشد، قانونی اجرا می شود که بعد از قوانین دیگر اعمال شده است. این قانون در پنجره Rules Manager، در قسمت بالاتری نوشته شده است.
برای تغییر اولویت قانون ها از فلش های بالا و پایین استفاده کنید. قانونی را که می بایست در ابتدا پیاده سازی شود انتخاب کنید و با استفاده از فلش بالا، آن را به بالا منتقل کنید.

برای مشاهده تمام قوانین اعمال شده در کاربرگ خود می توانید از منوی Show formatting rules for، گزینه This Worksheet را انتخاب کنید.

در این مطلب با نحوه فرمت دهی شرطی در اکسل آشنا شدیم. تنظیماتی که توضیح داده شد تا حد زیادی قابل تغییر هستند و کافیست با کمی تمرین با سایر تنظیمات و الگو های Conditional Formatting آشنا شوید.