قالببندی شرطی (Conditional Formatting)- بخش 2
01 اسفند 1400
دقیقه
یک مقیاس رنگی درست همانند گزینه Highlight Cells Rules از تغییر رنگ سلولها برای نشاندادن تفاوتهای مقادیر داخل سلول استفاده میکند.
آخرین بهروزرسانی: 27 دی 1401
در سری مقاله های آموزش اکسل، در فصل گذشته به بررسی بخش اول قالب بندی شرطی (Conditional Formatting) پرداختیم، در این مقاله به ادامه آموزش می پردازیم.
مقیاسهای رنگی چگونه کار میکنند؟
حالا بیاید از مقیاسهای رنگی برای خلاصه کردن برخی از مجموعهدادهها استفاده کنیم. یک مقیاس رنگی درست همانند گزینه Highlight Cells Rules از تغییر رنگ سلولها برای نشاندادن تفاوتهای مقادیر داخل سلول استفاده میکند. در اینجا مثالی را از یک مقیاس سه رنگی توضیح میدهیم (فایلی به نام Colorscales2016.xlsx و تصویر 12-24 نحوه استفاده از این مقیاس سه رنگ را نشان میدهد. توجه کنید که ردیفهای 19 الی 80 پنهان شدهاند. برای خارجشدن آنها از این جالت ردیفهای 18 و 81 را انتخاب کرده، روی انتخاب راست کلیک نموده و سپس گزینه Unhide را کلیک کنید.)
تصویر 12-24 مقیاسهای سه رنگ
برای مثال بازده سالیانه سهام، اسناد خزانهداری و اوراق قرضه خزانهداری را در سلولهای E6:G93 انتخاب کردهایم. در قدم بعدی به ترتیب روی گزینههای Conditional Formattin، Color Scales وMore Rules کلیک میکنیم تا کادر محاورهای New Formatting Rule نمایش داده شود و آن را طبق تصویر 13-24 تغییر میدهیم.
تصویر 13-24 تغییر یک مقیاس رنگی
همانطور که در تصویر 13-24 نشاندادهشده رنگ قرمز را برای نشاندادن کمترین بازده، سبز را برای نشاندادن بالاترین بازده و زرد را برای نشاندادن بازده متوسط انتخاب کردهایم. اکسل به شکل شگفتانگیزی بر اساس مقدار موجود در هر سلول تغییراتی را بر رنگبندی هر سلول اعمال مینماید. در ستوان E از تصویر 12-24 کمترین بازده سهام به رنگ قرمز تغییر داده شده. توجه کنید که سال 1931 و 2008(همانطور که میدانیم) برای سهام سالهای بسیار پر ضرری بودند. همچنانکه بازده به پنجاهمین صدک میرسد رنگ سلولها بهتدریج به زرد تغییر پیدا میکند و بعد با افزایش بازدهها از صدک پنجاه به رقمهایی بالاتر، رنگ سلولها به رنگ سبز تغییر پیدا میکند.
بیشتر سلولهای رنگ سبز و قرمز مرتبط با سهام هستند چون بازده سالیانه سهام متغیرتر از اوراق قرضه و یا اسناد خزانهداری هستند. این قابلیت تغییر باعث میشود که بازدههای سهام بالا و پایین به شکل مکرری ظاهر شوند. تقریباً تمامی بازدههای سالیانه اسناد خزانهداری و اوراق قرضه خزانهداری زرد هستند چرا که قابلیت تغییر پایین بازده سالیانه این سرمایهگذاریها به معنای آن است که اغلب اوقات تنها دارای بازدههایی متوسط میباشند.
برای فایلی به نام Scalesiconsdatabars.xlsx مقیاسی دورنگ ایجاد کردهایم که در تصویر 14-24 نشاندادهشده است. برای انجام این کار ابتدا محدوده سلولها را انتخاب کرده و سپس به ترتیب روی گزینههای Conditional Formatting و Color Scales کلیک کردیم. میتوانید با کلیک روی گزینه More Rules ترکیب رنگ موردنظرتان را از لیستی که در اختیارتان قرار داده شده انتخاب کنید.
برای این مثال مقیاسی دورنگ انتخاب کرده ایم که مشخصکننده مقادیر پایین به رنگ سفید و مقادیر بالا به رنگ آبی تیره میباشد:
- در محدوده سلول D19:D28 تصمیم بر آن شد که پایینترین مقادیر به رنگ سفید و بالاترین مقادیر به رنگ آبی باشند. با افزایش مقادیر سلولها رنگ آنها میبایست تیرهتر میشد.
- در محدوده سلولهای میخواهیم مقادیر کمتر یا برابر با 3 به رنگ سفید و مقادیر بزرگتر یا برابر با 8 آبی باشند. در ارتباط با عددهای بین 3 و هشت، با افزایش اعداد، رنگبندی سلولها به سمت رنگ تیرهتر میرود.
- در محدوده F19:F28 میخواهیم مقادیر پایین 20 درصد محدوده به رنگ سفید و مقادیر بالای 20 درصد به رنگ آبی باشند. برای عددهای میانه 60 درصد، رنگبندی سلولها بهتدریج با افزایش مقدار اعداد تیرهتر میشود.
تصویر 14-24 مقیاس دورنگ
مجموعه نمادها (آیکونها) چگونه کار میکنند؟
تفاوتهای عددی را میتوان با استفاده از مجموعه آیکونها نیز نشان داد که در تصویر 15-24 و فایلی به نام Scalesiconsdatabars.xlsx نمایشدادهشده است. یک مجموعه آیکون حاوی سه یا چهار نماد است. میتوان معیارهایی برای ارتباط دادن این آیکونها با هریک از مقادیر درون محدوده سلول تعیین کرد. مثلاً میتوان از یک پیکان با جهت پایین برای اعداد کمتر، یک پیکان به جهت بالا برای نمایش اعداد بیشتر و یک پیکان افقی برای نمایش مقادیر میانگین استفاده کرد. محدوده سلول E32:F41 حاوی دو نمایش از موارد استفاده مجموعه آیکونها میباشد. در اینجا برای هر ستون از نمادهای پیکان سرپایین قرمز، پیکان افقی زرد و پیکان سربالای سبز استفاده شده است.
تصویر 15-24 استفاده از مجموعه آیکونها (Icon Set)
چگونه میتوان آیکونها را به محدوده مقادیر عددی تخصیص داد؟
- پس از آنکه اعداد محدوده E32:E41 را انتخاب میکنیم، به ترتیب روی گزینههای Conditional Formatting، Icon Sets و بالاخره More Rules کلیک کرده و مجموعه آیکونهای سه پیکان (رنگی) را در بالای فهرست Icon Style انتخاب میکنیم. میخواهم در ستونهای E و I عددهای کمتر از 4 با پیکانهای رو به پایین، عددهای از 4 تا 7 با پیکانهای افقی و عددهای 8 یا بیشتر با پیکانهای روبهبالا نشان داده شوند. برای دستیابی به این هدف در کادر محاورهای New Formatting Rule در هر دو بخش Type مقدار را به Number تغییر میدهیم. سپس همانطور که در تصویر 24-16 نشاندادهشده، عدد 8 را در کادر Value بالایی و مقدار 4 را در کادر Value پایینی وارد میکنیم.
تصویر 16-24 تخصیص آیکونها به مقادیر عددی
به شکلی مشابه قاعده قالببندی ای برای محدوده F31:F42 مشخص میکنیم که بر اساس آن پیکانهای سربالا در سلولهای حاوی ارقام هشتادمین صدک یا بالاتر از تمامی مقادیر باشند (8=<) و پیکانهای سرپایین در سلولهای حاوی ارقام بیستمین صدم یا پایین تراز تمامی مقادیر باشند (1=>) در کادر محاورهای New Formatting Rule همان شکل آیکون را انتخاب میکنیم و سپس در بخش Type عبارت از پیش تعیین شده Perecent را دستنخورده باقی گذاشته و مقدار بخش بالایی قسمت Value را به 80 و بخش پایینی را به 20 تغییر میدهیم. تصویر 17-24 تنظیمات قالببندی موردنیاز را به ما نشان میدهد.
تصویر 17-24 تخصیص آیکون به مقادیر صدک
تنظیمات دلخواه شامل گزینه Reverse Icon Order میباشد که آیکونهای سمت چپ را با اعداد کمتر و آیکونهای سمت راست را با اعداد بزرگتر مرتبط میکند، گزینه بعدی Show Icon Only است که محتویات سلولها را پنهان میکند. در اکسل 2019 قادر هستیم تا زیرمجموعهای از آیکونها را پنهان کنیم. همچنین میتوان مجموعه آیکونها را بهدلخواه تغییر شکل داد. در کاربرگی به نام Customize Icons در فایل Historicalinvest.xlsx از سه آیکون برای نمایش خلاصه بازده سرمایهگذاری استفاده شده است. یکسوم بالای مقادیر بازده با پیکانهای خاکستری سربالا ظاهر شدهاند، یکسوم پایین حاوی پیکانهای قرمز سرپایین میباشند. در اینجا پیکانهای افقی شاخص یکسوم میانه این بازدهها پنهان شدهاند. تصویر 24-18 این آیکونهای نهایی را نشان میدهد و تصویر 19-24 کادر محاورهای را نشان میدهد (که با استفاده از منوی Manage Rules ظاهر شده) که برای این قالببندی از آن استفاده شد. در اینجا مجموعه سه آیکون پیکان خاکستری را بهگونهای تغییر دادیم که پیکان سرپایین قرمزرنگ جایگزین نمونه خاکستری آن شود.
تصویر 18-24 تغییر دلخواه مجموعه آیکونها با پنهانسازی یک آیکون
تصویر 19-24 کادر محاورهای برای تغییر دلخواه و پنهانسازی آیکونها
- چگونه میتوان بازده سهام ماهیانه را با رنگ بهگونهای کدگذاری نمود تا هر ماه پرسود با یک رنگ و ماههای پر ضرر با رنگ دیگری نمایش داده شوند؟
فایلی به نام Sandp.xlsx که در تصویر 20-24 نشاندادهشده حاوی مقادیر و بازده ماهیانه شاخص سهام Standard& Poor (S&P) میباشد. فرض کنید میخواهید هر ماهی را که در آن شاخص اساندپی بیش از 3 درصد بالا رفته به رنگ سبز و هر ماهی را که بیش از سه درصد کاهش پیدا کرده را با رنگ قرمز مشخص کنید.
تصویر 20-24 قالببندی شرطی بازدهها را در شاخص سهام اس ان پی با رنگ مشخص مینماید.
در ابتدا به سراغ سلول C10 (اولین ماه حاوی یک بازده اساندپی) رفته و بعد تمامی بازدههای ماهیانه را با فشاردادن کلیدهای Ctrl+Shift+ Down Arrow انتخاب میکنیم. حالا بر روی گزینه Conditional Formatting کلیک کرده گزینههای Highlight Cells و پس از آن Greater than را انتخاب کرده و کادر محاورهای را همانطور که در تصویر 21-24 نشاندادهشده پر میکنیم.
تصویر 21-24 اعمال قالببندی ویژه به بازده اساندپی بزرگتر از 3 درصد.
چنانچه از لیست ارائه شده در بخش With گزینه Custom Format را انتخاب کنید، کادر محاورهای Format Cells ظاهر میشود. توجه کنید که در کادر محاورهای Format Cells فهرستی از فونتها و یا اندازههای آنها در دسترس نیست بنابراین قالببندی انتخابی شما نمیتواند این مشخصهها را تغییر دهد. تب Fill حق انتخاب برای رنگبندی سلولها به رنگ انتخابی را در اختیار قرار میدهد و تب Borders نیز اجازه میدهد تا برای سلولها بر اساس معیارهای شرطی حاشیههایی ایجاد نمایید. (میتوان رنگ حاشیهها را از فهرست رنگها تغییر داد) .
مجددا بر روی گزینه Conditional Formatting کلیک کرده گزینههای Highlight Cells و پس از آن Less than را انتخاب کرده و کادر محاورهای را همانطور که در تصویر 211-24 نشاندادهشده پر میکنیم.
حالا دوباره روی گزینه Mange Rule در منوی Conditional Formatting کلیک میکنیم و اطمینان حاصل میکنیم که تمامی سلولها حاوی ارقامی کمتر از 0.03- به رنگ قرمز میباشند. (تصویر 22-24)
تصویر 22-24 بازده سهام کمتر از 3- درصد به رنگ قرمز و بزرگتر از 3 به رنگ سبز درآمده است.
وقتی روی Ok کلیک کنیم، تمامی ماهها با بازده اساندپی بزرگتر از 3 درصد (مثلاً سلول C23 را ببینید) به رنگ سبز و تمام ماههای دارای بازده اساندپی کمتر از 3- درصد (سلول C18 را ببینید) به رنگ قرمز نمایش داده میشوند. سلولهایی که بازده ماهیانه آنها با هیچ یک از این شرایط جور درنمیآیند در قالببندی اصلی خود باقی میمانند.
چند نکته مفید در مورد قالببندی شرطی
- برای حذف قالببندیهای شرطی (هر نوع قالببندی دیگری) که به محدوده سلولها اعمال شده، بهسادگی میتوانید محدوده سلولها را انتخاب کرده، سپس روی گزینه Conditional Formatting کلیک کرده و گزینه Clear Rules را انتخاب نموده و دست آخر گزینه Clerar Rules From Selected Cells را انتخاب کنید.
- برای انتخاب تمامی سلولهای یک کاربرگ که قالببندی شرطی بر آنها اعمال شده، کلید F5 را فشار میدهیم تا کادر محاورهای Go To نمایش داده شود، روی دکمه Special در گوشه سمت چپ کلیک میکنیم، گزینه Conditional Format در گوشه پایین سمت راست را انتخاب کرده و روی دکمه Ok کلیک میکنیم.
- اگر تمایل داشته باشیم که یکقاعده قالببندی شرطی را ویرایش کنیم، روی گزینه Conditional Formatting و سپس Manage Rules کلیک کرده و دست آخر روی قاعده موردنظر دو بار کلیک کرده و یا یکقاعده را انتخاب کرده و روی گزینه Edite Rule کلیک میکنیم.
- میتوان یکقاعده قالببندی شرطی را با کلیک کردن روی گزینه Conditional Formatting، پس از آن Manage Rules، انتخاب قاعده موردنظر و سپس کلیک روی گزینه Delete Rule پاک کرد.
توجه کنید که پس از آنکه هر دو قاعده تعیین شدند، قاعده قالببندی قرمز در فهرست، اول قرار میگیرد (چرا که بعد از قاعده قالببندی سبزرنگ ایجاد شده بود). در کادر محاورهای Conditional Formatting Rules Manager، قواعد به ترتیب تقدم فهرست میشوند. در این مثال اهمیتی ندارد که کدام قاعده ابتدا ایجاد شده است چرا که هیچ سلولی با معیارهای هر دو قاعده نمیخواند. هرچند اگر قواعد باهم تداخل داشته باشند قاعده ای که در فهرست اولین باشد متقدمتر میشود. برای تغییر نظم قواعد قالببندی شرطی، قاعده موردنظر را انتخاب نموده و روی پیکان بالا کلیک کنید تا قاعده در پروسه تقدم بالاتر برود و همچنین میتوان روی پیکان پایین کلیک کرد تا قاعده در پروسه تقدم پایینتر برود.
- با داشتن درآمد فصلی شرکت، چگونه میتوان فصلهایی را که در آن میزان درآمد نسبت به فصل قبلی افزایش داشته است را با یک رنگ و فصلهایی را که در آن درآمد نسبت به فصل قبلی کاهش داشته است را با رنگ دیگری نمایش داد؟
فایلی به نام Amazon.xlsx حاوی درآمدهای فصلی (به میلیون) شرکت Amaznon.com در طی سالهای 1995 تا 2017 است. میتوانید درآمدهای 1995 تا 2002 را در شکل 23-24 ببینید. میخواهیم فصلهایی را که در آن درآمدها نسبت به فصل قبل افزایش داشتند را به رنگ سبز و فصلهایی را که درآمدها نسبت به فصل قبل کاهش داشتند را به رنگ قرمز نمایش دهیم.
تصویر 23-24 مشخصکردن افزایش فروش به رنگ سبز و کاهش فروش به رنگ قرمز
گزینه Use A Formula در کادر محاورهای New Formatting Rule(بر روی گزینه New Rule در کادر محاورهای Conditional Formatting Manager کلیک کنید) ما را قادر میسازد تا فرمول خاصی را مشخص کنیم که تعیینکننده شرایطی است که اکسل میبایست پیش از اعمال قالببندی به سلول آن را بررسی نماید. این گزینه را در همین بخش بررسی میکنیم اما پیش از آن گزینه فرمول را مورد بررسی قرار میدهیم. بیایید ببینیم اکسل چگونه برخی از توابع منطقی را ارزشیابی میکند. فایل کار شده در اینجا Logicalexamples.xlsx نام دارد.
وقتی که فرمولی مثل =B3<2 را در سلول B4 کپی میکنیم چه اتفاقی میافتد؟ اگر مقدار موجود در سلول B3 عددی کوچکتر از 2 باشد، اکسل مقدار True یا صحیح را برمیگرداند، در غیر این صورت اکسل مقدار False یا نادرست را برمیگرداند. برای مثالهای دیگری مثل این که شامل عبارتهای And، Or و یا Not در فرمولها باشد، میتوانید به فایلی به نام Logicalexamples.xlsx که در تصویر 24-24 نشاندادهشده مراجعه کنید:
- در سلول B6 فرمول =OR(B3<3, C3>5) چنانچه شروط B3<3 و C3>5 درست باشند مقدار True را برمیگرداند. ازآنجاکه مقدار C3 بزرگتر از 5 است پس اکسل مقدار True را برمیگرداند.
- در سلول B7، فرمول =AND(B3=3,C3>5) چنانچه B3=3 و C3>5 باشد مقدار True را برمیگرداند. ازآنجاکه B3 با عدد 3 برابر نیست، اکسل عبارت False را به ما داده است. هرچند در سلول B8، فرمول a =AND(B3>3,C3>5) عبارت True را برمیگرداند، چرا که B3>3 و C3>5 هر دو صحیح میباشند.
- در سلول B9، فرمول =NOT(B3<2) عبارت True را بر میگرداند چراکه B3<2 عبارت False را به ما میدهد و یک مقدار Not False(نا غلط) در واقع True یا صحیح میشود.
تصویر 24-24 فرمولهای منطقی
حالا بیایید ببینیم چگونه گزینه Use A Formula ما را قادر میسازد تا قالببندی شرطی ای در محدودهای از سلولها ایجاد کنیم. کار را با انتخاب محدودهای از سلولهایی که میخواهیم قالببندی شرطی به آنها اعمال کنیم شروع مینماییم. سپس به ترتیب بر گزینههای Conditional Formatting و Manage Rules کلیک میکنیم تا کادر محاورهای Conditional Formatting Rules Manager ظاهر شود. بر روی گزینه New Rule کلیک میکنیم و بعد گزینه Use A Formula To Determine Which Cells To Format(انتخاب فرمول برای مشخصکردن محدوده سلولهایی که باید قالببندی شوند) را انتخاب میکنیم. (این گزینه آخرین گزینه محسوب میشود که آن را به شکل خلاصه گزینه فرمول مینامیم) با استفاده از گزینه فرمول میتوانید فرمولی را وارد کنید (این فرمول میبایست با علامت مساوی شروع شود) که تنها و تنها وقتی درست است که بخواهید سلول در گوشه سمت چپ بالا به فرمت انتخابی اختصاص داده شود. فرمول منطقی مثل هر فرمول عادی دیگری به یادآوری کننده بخش انتخاب شده کپی میشود، پس نیاز به استفاده عاقلانه از علامت دلار ($) است تا اطمینان حاصل کنیم که برای هریک از محدوده انتخاب شده تنها فقط و فقط اگر بخواهید فرمت بر سلول اعمال شود فرمول موردنظر صحیح خواهد بود. حالا بر گزینه Format کلیک کرده و قالببندی ای را که میخواهیم وارد میکنیم. روی Ok کلیک میکنیم. پس از کلیک کردن بر Ok در کادر محاورهای New Formatting Rule فرمول و قالببندی موردنظر بهتمامی محدوده سلول کپی میشود. قالببندی بهتمامی سلولهای محدوده انتخاب شده که شرایط مشخص شده توسط فرمول را دارا میباشند اعمال میگردد.
دوباره به سراغ فایل Amazon.xlsx میرویم. بیایید بر روی سبز کردن فصلهایی که درآمد در آنها افزایش پیدا میکند تمرکز کنیم. اساساً آنچه قرار است انجام دهیم این است که محدوده E6:E93 را انتخاب کرده (پیشازاین فصلی نیست که ارقام درآمد در سلول E5 را با آن مقایسه کنیم) و پس از آن به اکسل میگوییم که چنانچه مقدار یک سلول بیشتر از سلول بالایی آن باشد، سلول را به رنگ سبز در آورد. تصویر 25-24 به شما نشان میدهد چگونه این قاعده را ایجاد کنید.
تصویر 25-24 تنظیمات قالببندی شرطی که فصلهایی با افزایش درآمد را سبزرنگ نشان میدهد.
اگر با اشاره به سلولهای موردنظر فرمول =E6>E5 را وارد کرده باشید، اطمینان حاصل کنید که علامت دالر ($) را از فرمول در کادر محاورهای Conditional Formatting حذف کرده باشید وگرنه فرمول کپی نخواهد شد. احتمالاً آسانترین راه برای قراردادن یا حذف علامت دالر استفاده از کلید F4 است. وقتی که یک سلول مرجع مثل A3 ر ا مشخص میکنید، فشار دادن کلید F4 علامت دالر را بهاینترتیب اضافه میکند: A3, $A$3, A$3, $A3؛ بنابراین اگر فرمول را با $A$3 شروع کرده باشید، فشار دادن F4 سلول مرجع را به A$3 تغییر میدهد. فرمول در این مثال اطمینان حاصل پیدا میکند که سلول E6 به رنگ سبز درآمده فقط و فقط اگر فروشهای آن فصل از فصل گذشته بیشتر شده باشد. پس از کلیک بر دکمه Ok متوجه میشوید که تمامی فصلهایی که درآمد آنها افزایش پیدا کرده سبزرنگ شدهاند. توجه کنید که بهعنوانمثال در سلول E7 فرمول به شکل معمولش کپی شده و تبدیل شده به =E7>E6.
برای اضافهکردن شرطی برای قالببندی سلولهایی که در آن میزان درآمد افزایش پیدا کرده، در ابتدا محدوده E6:E93 را دوباره انتخاب میکنیم، کادر محاورهای Conditional Formatting Rules Manager را بازکرده بر روی گزینه New Rule کلیک کرده و سپس گزینه Use A Formula To Determine Which Cells To Format را انتخاب میکنیم. فرمول =E6<E5 را وارد کرده و سپس روی دکمه Format کلیک میکنیم. در تب Fill رنگ را به رنگ قرمز تغییر میدهیم و روی دکمه Ok کلیک کرده و بعد دوباره روی دکمه Ok کلیک میکنیم. اکنون کادر محاورهای Conditional Formatting Rules Manager به شکلی که در تصویر 26-24 نشاندادهشده نمایش داده میشود.
تصویر 26-24 شروط تعیین شده برای نمایش فصلهای افزایش درآمد به رنگ سبز و فصلهای کاهش درآمد به رنگ قرمز.
میتوان از گزینه فرمولنویسی به همراه مقیاسهای رنگی، نوارهای داده و مجموعه آیکونها استفاده کرد. برای این کار بهسادگی میتوان به هنگام اعمال معیار موردنظر برای مقیاس رنگی، نوارهای داده و مجموعه آیکونها، گزینه فرمول را انتخاب نمود.