ساخت کاربرگ‌های حرفه‌ای با قالب‌بندی شرطی در اکسل

کاربرگ‌های حرفه‌ای با قالب‌بندی شرطی

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

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

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

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

معرفی بخش‌های 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 شامل موارد زیر است:

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

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

مثال گزینه: Less than

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

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

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

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

مثال گزینه : 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شامل موارد زیر است:

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

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

مثلا در لیست نمایندگی‌ها می‌خواهید ۱۰ فروش بیشتر برای شما متمایز شود. محدوده موردنظر را انتخاب کنید، روی 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 می‌توانید قوانین جدید تعریف کنید

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

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

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

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