قالب بندی شرطی (Conditional Formatting) با اکسل (+ دانلود فیلم آموزش گام به گام)

استفاده از قالب‌بندی شرطی یا «Conditional Formatting» در اکسل این امکان را فراهم می‌کند که بتوانید برای سلول‌های مختلفی که شرایط مورد نظر شما در آن‌ها صدق می‌کند، قالب‌بندی خاصی در نظر بگیرید و این قالب‌بندی‌ها تنها زمانی اعمال می‌شوند که تمامی شرایط در نظر گرفته شده برقرار باشند.

قالب‌بندی‌های شما می‌توانند رنگ پس‌زمینه، قالب فونت، حاشیه‌بندی سلول، اضافه کردن قالب‌بندی متنی به داده و هر چیز دیگری باشند.

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

1. اعمال چند شرط در اکسل

برای استفاده از قالب‌بندی شرطی، محدود به یک شرط نیستید و می‌توانید از چندین شرط استفاده کنید. برای مثال داده‌های مالی شما ممکن است شرایطی را داشته باشند که با کمتر بودن از %100، %75 و %50 از مبلغ کل بودجه، قالب‌بندی مختلفی به خود بگیرند.

فیلم آموزش استفاده از توابع و فرمول نویسی در اکسل Excel در فرادرس

کلیک کنید

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

پیدا کردن مقداری که %25 یا %50 بیشتر از دیگری است

در این مثال، دو قانون قالب‌بندی شرطی وجود دارد که به سلول‌های B2 تا B5 اعمال می‌شود. این شروط به شرح زیر هستند:

• اولین شرط بررسی می‌کند که آیا داده‌های موجود در سلول‌های A2 تا A5 به میزان %25 از مقادیر موجود در سلول‌های B2 تا B5 بیشتر هستند یا خیر.
• شرط دوم بررسی می‌کند که آیا همان داده‌های A2 تا A5 به میزان بیشتر از %50 از مقادیر موجود در سلول‌های B2 تا B5 بیشتر هستند یا خیر.

همانطور که در تصویر بالا مشاهده می‌کنید، اگر هر کدام از شرایط برقرار باشد، رنگ پس‌زمینه در سلول‌های B2 تا B5 تغییر می‌کنند.

• برای داده‌هایی که فاصله‌ی آن‌ها %25 است، رنگ پس‌زمینه به سبز در می‌آید.
• اگر فاصله‌ی اعداد بیشتر از %50 باشد، رنگ پس‌زمینه قرمز خواهد شد.

قوانینی که برای رسیدن به این نتیجه استفاده می‌کنیم را باید با استفاده از کادر «New Formatting Rule» در بخش «Conditional Formatting» وارد نماییم. کار خود را با وارد کردن مقادیر موجود در سلول‌های A1 تا C5 که در تصویر بالا مشاهده می‌کنید آغاز نمایید.

2. پیاده‌سازی قوانین قالب‌بندی شرطی

همانطور که اشاره شد، شرایط مورد نیاز را باید از طریق کادر «New Formatting Rule» در بخش «Conditional Formatting» وارد نماییم. کار خود را با پیاده‌سازی قالب‌بندی شرطی سفارشی خود آغاز می‌کنیم.

فیلم آموزش ترفندهای کاربردی در اکسل Excel در فرادرس

کلیک کنید

Conditional Formatting برای پیدا کردن مقادیری که %25 بیشتر هستند

1= (A2-B2)/A2 > 25%

1. سلول‌های B2 تا B5 را در کاربرگ خود انتخاب کنید.
2. بر روی زبانه‌ی «Home» در نوار بالایی آفیس کلیک کنید.
3. بر روی گزینه‌ی «Conditional Formatting» کلیک کنید تا لیست کشویی باز شود.
4. گزینه‌ی «New Rule» را بزنید تا کادر «New Formatting Rule» باز شود.
5. از داخل کادر «Style» گزینه‌ی «Classic» را برگزینید.
6. در لیست کشویی دوم بر روی گزینه‌ی «Use a formula to determine which cells to format» کلیک کنید.
7. فرمول ارائه شده در بالا را در کادر مربوطه وارد نمایید.
8. بر روی لیست کشویی «Format with» کلیک کرده و گزینه‌ی «Light Red Fill with Dark Red Text» را انتخاب کنید.
9. بر روی OK کلیک کنید تا کادر بسته شده و به کاربرگ خود بازگردید.
10. هم اکنون باید رنگ پس‌زمینه سلول‌های B3 تا B5 سبز شده باشد.

Conditional Formatting برای پیدا کردن مقادیری که %50 بیشتر هستند

1 = (A2-B2)/A2 > 50%

1. سلول‌های B2 تا B5 را در کاربرگ خود انتخاب کنید.
2. بر روی زبانه‌ی «Home» در نوار بالایی آفیس کلیک کنید.
3. بر روی گزینه‌ی «Conditional Formatting» کلیک کنید تا لیست کشویی باز شود.
4. گزینه‌ی «New Rule» را بزنید تا کادر «New Formatting Rule» باز شود.
5. از داخل کادر «Style» گزینه‌ی «Classic» را برگزینید.
6. در لیست کشویی دوم بر روی گزینه‌ی «Use a formula to determine which cells to format» کلیک کنید.
7. فرمول ارائه شده در بالا را در کادر مربوطه وارد نمایید.
8. بر روی لیست کشویی «Format with» کلیک کرده و گزینه‌ی «Light Green Fill with Dark Green Text» را انتخاب کنید.
9. بر روی OK کلیک کنید تا کادر بسته شده و به کاربرگ خود بازگردید.

حالا باید رنگ پس‌زمینه سلول B3 سبز باشد که مشخص می‌کند که مقدار سلول A3 از B3 بین %25 تا %50 بیشتر است. رنگ سلول B5 نیز باید قرمز باشد که نشان می‌دهد مقدار سلول A5 از B5 به میزان %50 بیشتر است.

3. بررسی شرایط قالب‌بندی شرطی

برای این که مطمئن شویم قالب‌بندی شرطی وارد شده صحیح است، می‌توانید فرمول‌های خود را در سلول‌های C2 تا C5 وارد کنیم تا درصد تفاوت مقادیر سلول‌های A2 تا A5 و B2 تا B5 را به دست بیاوریم.

فیلم آموزش کاربرد اکسل در حسابداری و مدیریت در فرادرس

کلیک کنید

1= (A2-B2)/A2

1. بر روی سلول C2 کلیک کنید تا فعال شود.
2. فرمول بالا را در آن نوشته و کلید Enter را فشار دهید.
3. حال باید مقدار %10 در سلول C2 نمایش داده شود که نشان می‌دهد سلول A2 به میزان %10 از سلول B2 بزرگتر است.
4. ممکن است برای نمایش عدد به صورت درصد، نیاز به تغییر قالب‌بندی سلول C2 داشته باشید.
5. فرمول سلول C2 را در سلول‌های C3 تا C5 کپی کنید.
6. مقادیر %30، %25 و %60 باید به ترتیب در سلول‌ها نمایش داده شوند.

پاسخ‌های نوشته شده در این سلول‌ها نشان دهنده‌ی صحت قالب‌بندی شرطی ما هستند، چراکه فاصله سلول A3 و B3 بیشتر از %25، و فاصله سلول‌های A5 تا B5 بیشتر از %50 است.

رنگ سلول B4 تغییری نکرد، چراکه فاصله آن‌ها برابر با %25 است، در حالی که در فرمول ما گفته شده که باید بزرگتر از %25 باشد تا رنگ پس‌زمینه آن تغییر کنید.

4. اولویت اعمال قالب‌بندی شرطی

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

فیلم آموزش اکسل – محاسبات ریاضی و ماتریسی با Microsoft Excel 2016 در فرادرس

کلیک کنید

در مثال ما این تداخل وجود دارد، چراکه هر دو شرط ما رنگ پس‌زمینه سلول را تغییر می‌دهند.

در شرایطی که شرط دوم برقرار باشد (مقادیر باید %50 با یکدیگر فاصله داشته باشند)، پس حتما شرط اول نیز برقرار است (مقادیر باید %25 با یکدیگر فاصله داشته باشند). از آنجایی که یک سلول نمی‌تواند هم پس‌زمینه سبز داشته باشد و هم پس‌زمینه قرمز، اکسل باید بداند که کدام قالب‌بندی شرطی باید اعمال شود.

اینکه کدام قانون اعمال می‌شود، بر اساس اولویت قالب‌بندی شرطی اکسل مشخص می‌گردد که می‌گوید هر شرطی که در کادر «Conditional Formatting Rules Manager» بالاتر از بقیه باشد، اولویت دارد. همانطور که در تصویر بالا مشاهده می‌کنید، قانون دومی که در این آموزش استفاده شد، بالاتر در لیست است، از همین رو بر قانون اول اولیویت دارد. در نتیجه رنگ پس‌زمینه سلول B5 به قرمز تغییر پیدا کرده است.

به طور پیشفرض قوانین جدید در بالای لیست قرار می‌گیرند و در نتیجه اولویت بالاتری دارند. برای تغییر اولیت قوانین می‌توانید از فلش‌های بالا و پایین در کادری که در تصویر بالا مشاهده می‌کنید استفاده نمایید.

اعمال قوانین بدون تداخل

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

در نتیجه سلول B5 یک حاشیه سبز و یک پس‌زمینه قرمز داشت، چراکه فاصله مقادیر A5 و B5 هم بیشتر از %25 و هم بیشتر از %50 است.

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

در صورت وجود تداخل در بین قالب‌بندی‌های اعمال شده دستی و قالب‌بندی شرطی، همیشه اولویت با قالب‌بندی شرطی است.

اگر در ابتدا یک پس‌زمینه زرد برای سلول‌های B2 تا B5 انتخاب می‌کردیم، زمانی که قالب‌بندی شرطی اعمال می‌شد تنها سلول‌های B2 و B4 زرد باقی می‌ماندند، چراکه قالب‌بندی شرطی فقط به سلول‌های B3 و B5 اعمال می‌شدند و پس‌زمینه‌ی آن‌ها از رنگ زرد به سبز یا قرمز تغییر می‌کرد.

اگر این مطلب برای شما مفید بوده است، احتمالا آموزش‌های زیر نیز برایتان کاربرد خواهند داشت:

• مجموعه آموزش‌های مایکروسافت اکسل
• آموزش‌های مجموعه نرم‌افزاری آفیس
• آموزش استفاده از توابع و فرمول نویسی در اکسل
• توابع INDEX و MATCH در اکسل؛ جست‌وجوی سریعتر در صفحات گسترده
• چگونه با «Conditional Formatting» سطرها را در اکسل برجسته کنیم؟ — آموزش گام‌به‌گام

^^