افزودن قالب‌بندی شرطی در اکسل به شما امکان می‌دهد گزینه‌های قالب‌بندی مختلف را برای یک سلول یا محدوده سلول‌هایی اعمال کنید که شرایط خاصی را که شما تنظیم کرده‌اید برآورده می‌کند. تنظیم چنین شرایطی می تواند به سازماندهی صفحه گسترده شما کمک کند و اسکن آن را آسان تر کند. گزینه‌های قالب‌بندی که می‌توانید استفاده کنید شامل تغییر فونت و رنگ پس‌زمینه، سبک‌های فونت، حاشیه سلول‌ها و افزودن قالب‌بندی اعداد به داده‌ها است.
Excel دارای گزینه‌های داخلی برای شرایط رایج مانند یافتن اعدادی است که بزرگتر یا کمتر از یک مقدار خاص هستند یا یافتن اعدادی که بالاتر یا کمتر از مقدار متوسط هستند. علاوه بر این گزینه های از پیش تعیین شده، می توانید قوانین قالب بندی مشروط سفارشی را نیز با استفاده از فرمول های اکسل ایجاد کنید.
این دستورالعمل‌ها برای Excel 2019، 2016، 2013، 2010 و Excel برای Microsoft 365 اعمال می‌شود.
اعمال چند شرط در اکسل
می توانید بیش از یک قانون را برای آزمایش شرایط مختلف روی یک داده اعمال کنید. برای مثال، ممکن است داده‌های بودجه دارای شرایطی باشند که در صورت رسیدن به سطوح مشخصی از هزینه، تغییرات قالب‌بندی را اعمال می‌کنند، مانند 50، 75، و 100 درصد از کل بودجه.
در چنین شرایطی، اکسل ابتدا تعیین می کند که آیا قوانین مختلف مغایرت دارند یا خیر، و اگر چنین است، برنامه از یک ترتیب اولویتی پیروی می کند تا تعیین کند که کدام قانون قالب بندی شرطی برای داده اعمال شود.
پیدا کردن داده هایی که بیش از 25٪ و 50٪ افزایش می یابد
در مثال زیر، دو قانون قالب‌بندی مشروط سفارشی در محدوده سلول‌های B2 تا B5 اعمال خواهد شد..
- اولین قانون بررسی می کند که آیا داده های سلول های A2:A5 بزرگتر از مقدار مربوطه در B2:B5 توسط بیش از 25%.
- قانون دوم بررسی می کند که آیا همان داده در A2:A5 از مقدار متناظر در B2:B5 بیش از بیش از 50%.
همانطور که در تصویر بالا مشاهده می شود، اگر یکی از شرایط بالا درست باشد، رنگ پس زمینه سلول یا سلول های محدوده B1:B4 تغییر می کند.
- برای داده هایی که اختلاف آنها بیش از 25٪ است، رنگ پس زمینه سلول به سبز تغییر می کند.
- اگر تفاوت بیشتر از 50٪ باشد، رنگ پس‌زمینه سلول به قرمز تغییر می‌کند.
قوانین مورد استفاده برای انجام این کار با استفاده از کادر محاوره ای New Formatting Rule وارد می شود. با وارد کردن داده های نمونه در سلول های A1 تا C5 همانطور که در تصویر بالا مشاهده می شود، شروع کنید.
در بخش پایانی آموزش، فرمول هایی را به سلول های C2:C4 اضافه می کنیم که درصد دقیق تفاوت بین مقادیر در سلول های A2:A5 را نشان می دهد. و B2:B5; این به ما امکان می دهد صحت قوانین قالب بندی شرطی را بررسی کنیم.
تنظیم قوانین قالب بندی مشروط
ابتدا، قالب‌بندی شرطی را برای یافتن افزایش قابل توجه 25 درصد یا بیشتر اعمال می‌کنیم.
تابع به این شکل خواهد بود:
=(A2-B2)/A2>25%
- سلول های B2 تا B5 را در کاربرگ برجسته کنید.
- روی برگه Home ربن کلیک کنید.
- روی نماد Conditional Formatting در ribbon کلیک کنید تا کشویی باز شود.
- قانون جدید را برای باز کردن کادر محاوره ای New Formatting Rule انتخاب کنید.
- در یک نوع قانون را انتخاب کنید ، روی آخرین گزینه کلیک کنید: از فرمولی برای تعیین اینکه کدام سلول ها را قالب بندی کنید استفاده کنید.
-
فرمول را که در بالا ذکر شده در فضای زیر تایپ کنید مقادیر قالب را در جایی که این فرمول درست است تایپ کنید:
- روی دکمه Format کلیک کنید تا کادر محاوره ای باز شود. روی تب Fill کلیک کنید و رنگی را انتخاب کنید.
- OK را کلیک کنید تا کادرهای گفتگو ببندید و به کاربرگ بازگردید.
- رنگ پس‌زمینه سلول‌های B3 و B5 باید به رنگی که انتخاب کرده‌اید تغییر کند.
اکنون، قالب بندی شرطی را برای یافتن افزایش 50 درصدی یا بیشتر اعمال می کنیم. فرمول به این صورت خواهد بود:
- پنج مرحله اول بالا را تکرار کنید.
- فرمول ارائه شده در بالا را در فضای زیر تایپ کنید مقادیر قالب را در جایی که این فرمول درست است تایپ کنید:
- روی دکمه Format کلیک کنید تا کادر محاوره ای باز شود. روی تب Fill کلیک کنید و رنگی متفاوت از مجموعه مراحل قبلی انتخاب کنید.
- OK را کلیک کنید تا کادرهای گفتگو را ببندید و به کاربرگ بازگردید.
رنگ پس‌زمینه سلول B3 باید ثابت بماند که نشان می‌دهد درصد اختلاف بین اعداد سلول A3 وB3 بزرگتر از 25 درصد اما کمتر یا مساوی 50 درصد است. رنگ پس‌زمینه سلول B5 باید به رنگ جدیدی که انتخاب کرده‌اید تغییر کند که نشان‌دهنده این است که درصد تفاوت بین اعداد در سلول‌های A5 و B5 بیشتر از 50 درصد است.
بررسی قوانین قالب بندی مشروط
برای تأیید صحت قوانین قالب بندی شرطی وارد شده، می توانیم فرمول هایی را در سلول های C2:C5 وارد کنیم که درصد دقیق تفاوت بین اعداد در محدودهرا محاسبه می کند. A2:A5 و B2:B5.
فرمول سلول C2 به این صورت است:
=(A2-B2)/A2
- روی سلول C2 کلیک کنید تا به سلول فعال تبدیل شود.
- فرمول بالا را تایپ کنید و کلید Enter را روی صفحه کلید فشار دهید.
- پاسخ 10% باید در سلول C2 ظاهر شود، که نشان می دهد عدد در سلول A2 10% بزرگتر از عدد موجود است. سلول B2.
- ممکن است لازم باشد قالب بندی را در سلول C2 تغییر دهید تا پاسخ به صورت درصد نمایش داده شود.
- برای کپی کردن فرمول از سلول C2 به سلول های C3 از سلول های C3 استفاده کنید C5.
- پاسخ برای سلول های C3 تا C5 باید ۳۰٪، ۲۵٪ و ۶۰٪ باشد. باشد.
پاسخ در این سلول ها نشان می دهد که قوانین قالب بندی شرطی دقیق هستند زیرا تفاوت بین سلول های A3 و B3 بیشتر از ۲۵ است. درصد، و تفاوت بین سلول A5 و B5 بیشتر از ۵۰ درصد است.
سلول B4 تغییر رنگ نداد زیرا تفاوت بین سلول A4 و B4 برابر است 25 درصد، و قانون قالب‌بندی شرطی ما مشخص می‌کند که برای تغییر رنگ پس‌زمینه، درصدی بیشتر از 25 درصد لازم است.
ترتیب اولویت برای قالب بندی مشروط
هنگامی که چندین قانون را در محدوده یکسانی از داده ها اعمال می کنید، اکسل ابتدا تعیین می کند که آیا قوانین مغایرت دارند یا خیر. قوانین متناقض آنهایی هستند که در آن گزینه های قالب بندی هر دو نمی توانند روی یک داده اعمال شوند.
در مثال ما، قوانین با هم تضاد دارند زیرا هر دو از یک گزینه قالب بندی استفاده می کنند - تغییر رنگ سلول پس زمینه.
در شرایطی که قانون دوم درست است (تفاوت ارزش بیش از 50 درصد بین دو سلول است) قانون اول (تفاوت مقدار بیشتر از 25 درصد است) نیز صادق است.
از آنجایی که یک سلول نمی تواند همزمان دو پس زمینه رنگی متفاوت داشته باشد، اکسل باید بداند که کدام قانون قالب بندی شرطی را باید اعمال کند.
ترتیب اولویت اکسل بیان می کند که قاعده ای که در لیست بالاتر در کادر گفتگوی Conditional Formatting Rules Manager قرار دارد، ابتدا اعمال می شود.
همانطور که در تصویر بالا نشان داده شده است، قانون دوم استفاده شده در این آموزش در لیست بالاتر است و بنابراین بر قانون اول ارجحیت دارد. در نتیجه، رنگ پس زمینه سلول B5 سبز است.
به طور پیش فرض، قوانین جدید به بالای لیست می روند. برای تغییر ترتیب، از دکمه های پیکان بالا و پایین در کادر گفتگو استفاده کنید.
استفاده از قوانین غیر متناقض
اگر دو یا چند قانون قالب‌بندی شرطی با هم تضاد ندارند، هر دو زمانی اعمال می‌شوند که شرطی که هر قانون در حال آزمایش است درست شود.
اگر اولین قانون قالب‌بندی شرطی در مثال ما محدوده سلول‌های B2:B5 را با حاشیه نارنجی به جای رنگ پس‌زمینه نارنجی قالب‌بندی می‌کرد، دو قانون قالب‌بندی شرطی این کار را انجام نمی‌داد. تضاد چون هر دو قالب را می توان بدون تداخل با دیگری اعمال کرد.
قالب‌بندی مشروط در مقابل قالب‌بندی معمولی
در صورت تضاد بین قوانین قالب‌بندی شرطی و گزینه‌های قالب‌بندی دستی اعمال‌شده، قانون قالب‌بندی شرطی همیشه اولویت دارد و به‌جای هر گزینه قالب‌بندی اضافه‌شده دستی اعمال می‌شود.
