وقتی در یک کاربرگ اکسل با حجم زیادی از داده‌ها کار می‌کنید، چگونه می‌توانید بخش‌ها و داده‌های مهم آن را برجسته کنید؟ با اضافه کردن ویژگی‌های بصری به ردیف‌ها و ستون‌های خاصی از داده‌ها، نه تنها کاربرگ شما زیباتر می‌شود، بلکه تجزیه و تحلیل داده‌ها در مغز هم ساده‌تر می‌شود. با استفاده از قالب بندی شرطی در اکسل (Conditional Formatting) به سرعت می‌توانید داده‌های کاربرگ را متمایز و برجسته کنید تا نتیجه‌گیری و فهم اطلاعات راحت‌تر شود.

قالب‌بندی شرطی چیست؟

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

پیشنهاد مطالعه:

• جستجوی هدف تا Goal Seek در اکسل
• آموزش Solver برای حل مساله در اکسل
• تحلیل متغیرها با Data Table در اکسل

قوانین قالب‌بندی شرطی

اکسل تعداد زیادی قوانین از پیش تنظیم شده دارد. با این حال، شما می‌توانید با فرمول‌های سفارشی خود قوانینی را ایجاد کنید. شما با استفاده از فرمول خود شرایطی را که باعث ایجاد قانون می‌شود، تعیین می‌کنید و می‌توانید دقیقاً منطق مورد نیاز خود را اعمال کنید. فرمول‌ها حداکثر قدرت و انعطاف‌پذیری را به شما می‌دهند.

۳ گام اصلی برای قالب‌بندی شرطی

1. تعیین محدوده: شما با انتخاب سلول‌هایی که می‌خواهید قانون موردنظر روی آن‌ها اعمال شود، شروع می‌کنید. این محدوده ممکن است کل صفحه گسترده شما یا فقط مجموعه‌ای از ردیف‌ها یا ستون‌ها باشد.
2. تعیین قانون: در گام دوم باید قانون مورد نظر را انتخاب کنید. می‌توانید بیش از ده‌ها گزینه از جمله گزینه‌های بزرگتر از، کمتر از ، بین و غیره را انتخاب کنید.
3. تعیین قالب‌بندی: این گام پس از تعیین قانون است. اکسل برای هر قانون یک فرمت طراحی پیش فرض دارد، اما شما می‌توانید آن را شخصی‌سازی کنید.

معرفی بخش‌های Conditional Formatting

برای استفاده از Conditional Formatting به سربرگ Home بروید و در گروه Styles روی Conditional Formatting کلیک کنید.

این ابزار شامل ۳ بخش است:

بخش اول:

• Highlight cell rules
• Top/Bottom Rules

بخش دوم:

• Data Bars
• Color Scales
• Icon Sets

بخش سوم:

• New Rule
• Clear Rule
• Manage Rule

برجسته کردن داده‌های مهم

گزینه‌های Highlight cells rules شامل موارد زیر است:

1. Greater than: سلول (هایی) که محتوای آن از یک مقدار مشخص بیشتر است، متمایز ‌می‌کند.
2. Less than: سلول (هایی) که محتوای آن از یک مقدار مشخص کمتر است، متمایز می‌کند.
3. Between: سلول (هایی) که محتوای آن بین دو مقدار مشخص است، متمایز می‌کند.
4. Equal to: سلول (هایی) که محتوای آن دقیقا برابر یک مقدار مشخص است، متمایز می‌کند.
5. Text that contains:  سلول (هایی) که حاوی رشته‌ای خاص است، متمایز می‌کند.
6. A date occurring: سلول (هایی) که حاوی تاریخی خاص است، متمایز می‌کند.
7. Duplicate values: سلول (هایی) که محتوای تکراری دارند، متمایز می‌کند.
8. More rules: اگر از قالب‌بندی پیش‌فرض Excel رضایت ندارید، می‌توانید خودتان قالب‌بندی موردنظر را بسازید. روی More rules کلیک کنید سپس روی دکمه Format کلیک کنید و قالب‌بندی موردنظر را اعمال کنید. همچنین می‌توانید با استفاده از این قسمت قوانین جدید تعریف کنید.

در اینجا من ابتدا برای گزینه Less than و Text that contains و Duplicate values مثالی ذکر میکنم تا موضوع را بهتر درک کنید.

مثال گزینه Less than

وقتی شما مجموعه بزرگی از داده‌ها را دارید و در آن به دنبال عددی خاص هستید،  این کار بسیار خسته‌کننده است. مثلا فرض کنید شما تعدادی نمایندگی فروش دارید و می‌خواهید ببینید کدام نمایندگی در حوزه فروش سخت‌افزار فروش کمتر از ۶۵۰۰۰۰۰ داشته است. این کار را به Conditional formatting بسپارید تا نتایج را در کثری از ثانیه با رنگ مشخص به شما نشان دهد.

ابتدا ستون موردنظر را انتخاب کنید، سپس روی Conditional formatting کلیک کنید. گزینه Highlight cell rules را انتخاب کنید و روی Less than کلیک کنید.

پنجره Less than باز می‌شود. سپس در کادر سمت چپ مقدار ۶۵۰۰۰۰۰ را تایپ کنید ، در کادر سمت راست به صورت پیش‌فرض گزینه Light red fill with dark red text فعال است، من در اینجا Light Green fill with dark Green text را انتخاب کردم.

 اگر روی OK کلیک کنید می‌بینید،  رنگ عدد مربوط به سلول‌هایی که کمتر از ۶۵۰۰۰۰۰ است، سبز تیره و رنگ خود سلول به سبز روشن در می‌آید. می توانید با استفاده از گزینه‌های موجود در لیست کشویی حالت‌های دیگر را هم امتحان کنید.

مثال گزینه Text that contains

اگر به دنبال رشته خاصی در یک لیست هستید می‌توانید با استفاده از قالب‌بندی شرطی به راحتی آن رشته خاص را پیدا کنید. مثلا لیستی از مشخصات کارمندان را در اختیار دارید و می‌خواهید افرادی که تحصیلات آنها فوق لیسانس است، متمایز شود. ابتدا ستون تحصیلات را انتخاب کنید. سپس روی conditional formatting کلیک کنید. سپس از قسمت Highlight cells rules گزینه Text that contains را انتخاب کنید.

پنجره Text That Contains باز می‌شود. در کادر سمت چپ فوق لیسانس را وارد کنید و در کادر سمت راست رنگ موردنظر را انتخاب کنید و روی OK کلیک کنید.

نتیجه را در تصویر زیر می‌بینید:

مثال گزینه Duplicate value

از قالب‌بندی شرطی می‌توان برای شناسایی مقادیر تکراری در یک مجموعه داده استفاده کرد. بین حذف تکراری و مشخص کردن مقادیر تکراری تفاوت وجود دارد.  ابزار Remove duplicate در اکسل مقادیر تکراری را حذف می‌کند بدون اینکه به شما اطلاع دهد کدام یک را حذف کرده‌اید.

این ابزار در سربرگ Data قرار دارد.

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

برای این کار مراحل زیر را انجام دهید:

داده‌های خود را انتخاب کنید. سپس Conditional formatting را انتخاب کنید و گزینه Highlight cell rules را انتخاب کنید و سپس Duplicate values را انتخاب کنید و مقادیر تکراری که موردنظرتان هست را حذف کنید.

قوانین بیشترین/کمترین (Top/Bottom rules)

گزینه‌های  Top/Bottom rulesشامل موارد زیر است:

1. Top 10 items: محتوای سلول‌های یک محدوده خاص را بررسی کرده و ۱۰ سلولی که بیشترین مقدار را دارند، متمایز می‌کند.
2. Top 10 %: محتوای سلول‌های یک محدوده خاص را بررسی کرده و سلول‌هایی که مقدار آنها برابر ۱۰% بالای یک مقدار مشخص است، متمایز می‌کند.
3. Bottom 10 items: محتوای سلول‌های یک محدوده خاص را بررسی کرده و ۱۰ سلولی که کمترین مقدار را دارند، متمایز می‌کند.
4. Bottom 10 %: محتوای سلول‌های یک محدوده خاص را بررسی کرده و سلول‌هایی که مقدار آنها برابر ۱۰% پایین یک مقدار مشخص است، متمایز می‌کند.
5. Above average: محتوای سلول‌های یک محدوده خاص را بررسی کرده و سلولی که محتوای آن بیشتر از میانگین است، متمایز می‌کند.
6. Below average: محتوای سلول‌های یک محدوده خاص را بررسی کرده و سلولی که محتوای آن کمتر از میانگین است، متمایز می‌کند.
7. More rules: اگر از قالب‌بندی پیش‌فرض Excel رضایت ندارید، می‌توانید خودتان قالب‌بندی موردنظر را بسازید. روی More rules کلیک کنید سپس روی دکمه Format کلیک کنید و قالب‌بندی موردنظر را اعمال کنید. همچنین می‌توانید با استفاده از این قسمت قوانین جدید تعریف کنید.

پیشنهاد مطالعه:

• محاسبه بزرگ‌ترین مقدار با تابع MAX
• محاسبه کوچیک‌ترین مقدار با تابع MIN
• محاسبه میانگین سلول‌ها با تابع AVERAGE

مثال قوانین بیشترین/کمترین:

مثلا در لیست نمایندگی‌ها می‌خواهید ۱۰ فروش بیشتر برای شما متمایز شود. محدوده موردنظر را انتخاب کنید، روی Conditional formatting کلیک کنید و سپس روی Top/Bottom rules کلیک کنید و بعد از آن روی Top 10 items کلیک کنید.

در کادر سمت چپ عدد موردنظر را وارد کنید و در کادر سمت راست رنگ موردنظر را انتخاب کنید.

نتیجه را در تصویر زیر ببینید.

برای سفارشی کردن این گزینه می‌توانید روی گزینه More rules کلیک کنید.

مثلا می‌خواهید ۵ فروش بیشتر نمایش داده شود، روی More rules کلیک کنید. (در تصویر قوانین بالا/پایین با شماره ۷ شماره‌گذاری شده است.) پنجره New formatting rule باز می‌شود.  در کادر سمت راست عدد ۵ را تایپ کنید

نکته: اگر تیک گزینه Of the selected range را بزنید برحسب درصد محاسبه می‌شود.

سپس با کلیک روی دکمه  Format پنجره Format cells باز می‌شود. رنگ موردنظر را انتخاب کنید و روی OK کلیک کنید.

نتیجه را در تصویر زیر ببینید.

استفاده از میله‌های داده برای نمایش سریع

اگر مجموعه داده شما خیلی بزرگ باشد و به دنبال مقادیر خاص یا متن در داده‌های خود نیستید، میله‌های داده مناسب هستند تا مقادیر بالا و پایین را به شما نشان دهند. ابتدا محدوده داده‌های موردنظر را انتخاب کنید و سپس روی Conditional formatting کلیک کنید و روی Data bars کلیک کنید. این قسمت شامل دو بخش است:

• Gradient Fill: رنگ هر نوار رنگی از پررنگ شروع و رفته رفته کمرنگ‌تر می‌شود.
• Solid Fill: کل نوار یک رنگ است.

در مثال زیر من  برای مشاهده درصد پیشرفت پروژه‌ها از Data bars و قسمت Solid Fill کمک گرفته‌ام.

مقیاس رنگ (Color scales)

این گزینه مشابه با میله‌های داده است. ابزار مناسبی برای مرور کلی اطلاعات در اکسل برای خواننده محسوب می‌شود.

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

مجموعه آیکون

اگر به دنبال جلو‌ه‌هایی خاص هستید از Icon sets کمک بگیرید.

این قسمت شامل Directional، Shapes، Indicators، Ratings است.

محدودهٔ موردنظر را انتخاب کنید. روی Conditional formatting کلیک کنید.

گزینه Icon sets را انتخاب کنید. اکسل مقادیری را به صورت پیش‌فرض برای تعیین رنگ دایره‌ها در نظر می‌گیرد شما می‌توانید این مقادیر را بر حسب نیاز خود تغییر دهید. (در قسمت Manage rule به صورت مفصل توضیح داده شده است.)

استفاده از گزینه New Rule

با استفاده از گزینه New rule می‌توانید قوانین جدید تعریف کنید مثلا من در مثال نمایندگی‌های فروش می‌خواهم بگویم که اگر فروش سخت افزار بیشتر از ۵۰۰۰۰۰۰ بود آن سلول‌ها را متمایز کن.

1. روی conditional formatting کلیک کنید گزینه New rule را کلیک کنید و سپس روی Use a Formula to determine which cells to format کلیک کنید
2. در کادر کلیک کنید علامت مساوی را قرار دهید و سپس محدوده موردنظر را انتخاب کنید و علامت $ را از پشت ۲ و ۱۰ پاک کنید (تا مقدار هر سلول با مقدار ۵۰۰۰۰۰۰ مقایسه شود) و سپس علامت بزرگتر قرار دهید و عدد ۵۰۰۰۰۰۰ را تایپ کنید.
3. از قسمت Format رنگ موردنظر را انتخاب کنید.

نتیجه را در تصویر زیر ببینید.

حذف قالب‌بندی شرطی در اکسل

شاید نیاز باشد قالب‌بندی شرطی را حذف کنید برای این کار مراحل زیر را انجام دهید.

1. روی Conditional formatting کلیک کنید و سپس روی Clear rules کلیک کنید.
2. اگر می‌خواهید فقط قوانین مربوط به سلول‌های انتخابی حدف شود، گزینه Clear rules from Selected cells را انتخاب کنید
3. اگر می‌خواهید تمامی قوانین موجود در Sheet که در آن قرار دارید، حذف شود گزینه Clear rules from Entire sheet را انتخاب کنید.

برای حذف یک قانون خاص روی گزینه Manage rules کلیک کنید.

پنجره Conditional Formatting rules manager باز می‌شود.

1. از قسمت Show Formatting rules کاربرگ موردنظر را انتخاب کنید
2. سپس روی قانون موردنظر کلیک کنید
3. سپس دکمه Delete rules را بزنید.
4. در نهایت روی OK کلیک کنید.

ویرایش قالب‌بندی شرطی با گزینه Manage rule

شاید نیاز باشد قالب‌بندی شرطی را ویرایش کنید، مثلا می‌خواهیم در مثال Icon sets در کنار نمرات دانشجویان Shape (دایره‌های رنگی)  دایره سبز نشان‌دهندهٔ نمرات بزرگتر و مساوی ۱۸ و دایره زرد نشان‌دهندهٔ نمرات کمتر از ۱۸ و بزرگتر مساوی ۱۴ و دایره قرمز نشان‌دهندهٔ نمرات کمتر از ۱۴ و بزرگتر مساوی ۱۰ است و دایره مشکی نشان‌دهندهٔ نمرات کمتر از ۱۰ است، درج شود.

• برای این کار به قسمت Manage rules (مدیریت قوانین) بروید.
• با کلیک روی آن، پنجره Conditional Formatting rules manager باز می‌شود. 
• ابتدا از قسمت Show Formatting rules for کاربرگ موردنظر را انتخاب کنید.
• قانونی که در لیست می‌خواهید ویرایش کنید انتخاب کنید.
• سپس روی Edit rules کلیک کنید.

پنجره Edit Formatting rule باز می‌شود. در قسمت Value مقادیر مورد نظر را وارد کنید و روی OK کلیک کنید.

پس اگر در قانونی اشتباه کردید نیاز نیست آن را حذف کنید بلکه آن را ویرایش کنید.

جمع‌بندی

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