conditional formatting در اکسل، ویژگی قدرتمندی است که فرمت های مختلفی را در صورت وجود شرایط مشخص شده بر روی سلول ها اعمال میکند. این ویژگی به شما کمک میکند مهمترین اطلاعات ورک شیت خود را هایلایت کنید و به کاربر این امکان را میدهد که با یک نگاه سریع تفاوت های مقادیر را شناسایی کند.

با اینحال، conditional formatting همیشه و به خصوص برای مبتدی ها، یکی از قسمت های پیچیده و خاص اکسل بوده است. اگر شما هم از این ویژگی اکسل وحشت زده شده اید، لطفاً ترس خود را کنترل کنید. در حقیقت، conditional formatting در اکسل بسیار ساده است ، این موضوع را 5 دقیقه دیگر و زمانی که مطالعه این مطلب را به پایان رساندید، تصدیق خواهید کرد.

• اصول conditional formatting در اکسل
• conditional formatting در کجای اکسل قرار دارد.
• نحوه ایجاد قوانین conditional formatting
• پیاده سازی چند قانون بر روی یک سلول
• اصلاح قوانین conditional formatting
• کپی کردن conditional formatting در اکسل
• حذف قوانین conditional formatting

اصول conditional formatting در اکسل

مثل دیگر روش های فرمت دهی به سلول ها، از conditional formatting نیز برای فرمت دهی به سلول ها، تغییر رنگ، تغییر فونت و خطوط حاشیه سلول استفاده میشود. تنها تفاوت این است که conditional formatting بسیار انعطاف پذیر است، این ویژگی به شما امکان میدهد که سلول هایی را فرمت کنید که یک سری شرایط را داشته باشند.

شما میتوانید conditional formatting را به یک یا چند سلول، ردیف، ستون، یا کل یک جدول بر اساس مقدار همان سلول یا مقدار یک سلول دیگر اعمال کنید. این کار با ایجاد قوانین انجام میشود که در ادامه توضیح داده خواهد شد.

conditional formatting در کدام قسمت از اکسل قرار دارد.

برای شروع، ببینیم conditional formatting در کجای اکسل قرار دارد. خبر خوب این است که در تمام ورژن های مدرن اکسل، conditional formatting در یک جای خاص قرار دارد. پنجره home، گروه styles

conditional formatting در اکسل 2007:

conditional formatting در اکسل 2010:

conditional formatting در اکسل 2013 و 2016:

حال که با مکان conditional formatting در اکسل آشنا شدید ببینیم که چه قوانینی در این ویژگی از اکسل وجود دارد و ما میتوانیم چه قوانینی ایجاد کنیم.

نحوه ایجاد قوانین conditional formatting در اکسل

برای استفاده مؤثر از conditional formatting در اکسل میبایست نحوه ساخت و استفاده از قوانین را بدانید. این کار شما را در درک پروژه ای که بر روی آن کار میکنید کمک خواهد کرد.

قوانین conditional formatting در اکسل دو نکته کلیدی را تعریف میکند:

• conditional formatting می بایست بر روی کدام سلول ها اعمال شود.
• چه شرط هایی می بایست وجود داشته باشد.

در اینجا از ورژن 2010 اکسل استفاده خواهیم کرد زیرا به نظر می رسد امروز بیشتر از این ورژن استفاده میکنند.

با اینحال امکانات و اختیارات تمام ورژن های 2007 به بعد در این زمینه یکسان است و میتوانید بدون توجه به ورژن اکسل خود این دستورالعمل ها را دنبال کنید.

1. سلول هایی را که میخواهید فرمت کنید، در ورک شیت خود انتخاب کنید.

برای این مثال، یک جدول ایجاد کردیم که لیست قیمت نفت خام را به صورت ماهانه نشان میدهد. ما میخواهیم کاهش ارزش را هایلایت کنیم. به این معنی که سلول هایی که مقدار آنها در ستون تغییرات منفی است را مشخص سازیم. بنابراین سلول های C2:C9 انتخاب میکنیم.

2. به پنجره home بروید و در گروه styles گزینه conditional formatting را انتخاب کنید. شما تعدادی قوانین فرمت دهی مختلف از جمله data bars،color scales و  icon sets را مشاهده می کنید .
3. از آنجایی که میخواهیم conditional formatting بر روی سلول هایی انجام شود که مقدار کمتر از صفر دارند، گزینه های Highlight Cells Rules > Less Than… را انتخاب میکنیم.

البته، شما میتوانید از هر نوع قانون دیگری که مناسب داده های شماست استفاده کنید. برای مثال:

• مقادیر بزرگتر، کوچکتر یا مساوی یک مقدار خاص را فرمت کنید.
• متن هایی که شامل کاراکتر(های) خاص میشود را هایلایت کنید.
• مقادیر تکراری را نشان دهید.
• تاریخ های مشخص را فرمت کنید.

4. مقدار مورد نظر را در باکس “Format cells that are LESS THAN” موجود در سمت راست پنجره وارد کنید. در اینجا، می بایست 0 وارد شود. زمانی که مقدار را وارد کنید، اکسل سلول هایی را که شرط شما را داشته باشند، هایلایت میکند.
5. از لیست کشویی فرمتی را که میخواهید انتخاب کنید. شما میتوانید یکی از فرمت های از پیش تعریف شده را انتخاب کنید یا بر روی Custom Format کلیک کنید و فرمت مورد نظر خود را تنظیم نمائید.

6.در پنجره format cell، نوع فونت، خطوط حاشیه ای و رنگ مورد نظر خود را انتخاب کنید. برای این کار میتوانید بین پنجره هایی که برای همین منظور قرار داده شده است جابجا شوید. در پنجره های fill و font به محض اینکه یکی از گزینه ها را انتخاب کنید، تغییرات ایجاد شده را مشاهده خواهید کرد.

7. زمانی که این کارها را انجام دادید، کلید ok را بزنید .

نکات:

• اگر غیر از گزینه هایی که ارائه شده است، رنگ ها یا پیش زمینه های دیگری نیاز دارید، به پنجره fill یا font بروید.
• اگر میخواهید یک رنگ پیش زمینه مدرج(gradient) را انتخاب کنید، در پنجره fill، گزینه fill effect را انتخاب کنید و ویژگی مورد نظر را انتخاب کنید.
• Ok را بزنید تا پنجره less than بسته شود و بررسی کنید که آیا conditional formatting به درستی برروی سلول های شما اعمال شده است یا خیر.

همانطور که در تصویر زیر مشاهده میکنید، قانون conditional formatting که ایجاد کردیم به درستی کار میکند، و سلول هایی را که دارای تفاوت قیمت منفی هستند را نشان میدهد.

ایجاد یک قانون conditional formatting در اکسل

اگر هیچ یک از قوانین conditional formatting موجود نیاز شما را برطرف نمیکند، شما میتوانید یک قانون جدید ایجاد کنید.

1. سلول های مورد نظر را انتخاب کنید و Conditional Formatting > New Rule. را انتخاب کنید.

2.قسمت New Formatting Rule باز میشود و شما میتوانید نوع مورد نظر را انتخاب کنید. برای مثال، “Format only cells that contain” را انتخاب کنید و درخواست کنید سلول های بین 60 تا 70 فرمت شوند.

3. Format… را کلیک کنید و نوع فرمت دهی مورد نظر را دقیقاً شبیه به قسمت قبل تنظیم کنید.

4. دوبار ok را بزنید تا فرمت دهی شما تایید شود و conditional formatting نیز بسته شود.

conditional formatting در اکسل بر اساس مقدار سلول ها

در هر دو مثال قبل، ما با وارد کردن اعداد قوانین فرمت دهی را مشخص میکردیم. با اینحال در بعضی موارد می بایست بر اساس مقدار سلول های دیگر فرمت دهی را انجام داد. مزیت این روش این است که اگر مقدار سلول مورد نظر تغییر یابد فرمت conditional formatting شما نیز به صورت اتوماتیک تغییر خواهد کرد.

برای مثال، یک بار دیگر مثال قسمت نفت را در نظر بگیرید، ولی این بار تمام قیمت های ستون B را که بزرگتر از قیمت های ستون 14 تیر هستند، را هایلایت کنید.

در اینجا نیز مثل قسمت قبل از دستور Conditional formatting  > Highlight Cells Rules > Greater Than… استفاده میکنیم . ولی ، به جای وارد کردن یک عدد در گام 4، با کلیک کردن بر روی آیکن انتخاب محدوده، سلول B6 را انتخاب می کنید. در نتیجه، قیمت ها به شکلی که در تصویر زیر مشاهده میکنید فرمت دهی میشوند.

این ساده ترین شکل فرمت دهی بر اساس مقدار یک سلول دیگر می باشد. در سناریو های پیچیده تر ممکن است نیاز باشد که از فرمول استفاده کنیم. شما میتوانید مثال های فرمولی به همراه دستورالعمل های گام به گام را در اینجا مشاهده کنید.

امیدورایم که شیوه هایی که توضیح داده شد باعث شده باشد، شکل اولیه و رویکرد اساسی conditional formatting را فرا گرفته باشید.

اعمال چند قانون conditional formatting به یک سلول/ جدول

زمانی که از conditional formatting استفاده میکنید، حتماً نباید از یک قانون برای هر سلول استفاده کنید. شما میتوانید هر تعداد قانون که لازم است را به یک سلول اعمال کنید.

برای مثال، سه قانون طراحی کنید که در جدول دما، دمای بالاتر از 60 درجه را زرد، بالاتر از 70 درجه را نارنجی و بالاتر از 80 درجه را قرمز رنگ کند.

احتمالاً تا به حال با نحوه طراحی اینگونه قوانین conditional formatting آشنا شده اید – مسیر Conditional Formatting > Highlight Cells rules > Greater than را انتخاب کنید.- برای اینکه قوانین به درستی کار کند می بایست اولیت های آنان را به شکل زیر مشخص سازید.

1. گزینه Conditional Formatting > Manage Rules… را انتخاب کنید تا rule manager باز شود.
2. قانونی را که می بایست در ابتدا پیاده سازی شود انتخاب کنید و با استفاده از فلش بالا، آن را به بالا منتقل کنید. همین کار را برای قانونی که دومین اولویت را داراست انجام دهید.پ
3. چک مربوط به Stop if true مربوط به دو قانون اول را قرار دهید، چون نمیخواهید اگر یکی از قوانین اعمال شد، قانون دیگر نیز اعمال شود.

استفاده از stop if true در قوانین conditional formatting

قبلاً، در مثال قبل از گزینه stop if true استفاده کردیم تا اگر یکی از قوانین صحیح بود و اعمال شد، از پیاده سازی قانون بعدی جلوگیری شود. استفاده از این گزینه بسیار ساده است. حال به بررسی دو مثال دیگر می پردازیم که در آنها استفاده از stop if true واضح و آسان نیست ولی بسیار کمک کننده است.

مثال1. فقط بعضی از icon set ها نشان داده شود.

فرض کنید، icon set های زیر را به گزارش فروش خود اضافه کرده اید.

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

1. یک قانون جدید conditional formatting را با استفاده از Conditional formatting > New Rule > Format only Cells that contain ایجاد کنید.
2. حال میبایست به شکلی از این قانون استفاده کنید که فقط به مقادیر بیشتر از میانگین اعمال شود. این کار را با استفاده از فرمول =average() و به شکلی که در تصویر زیر نشان داده شده است، انجام میدهیم.

نکته: شما میتوانید همیشه با استفاده از آیکن انتخاب محدوده، یک محدوده از سلول ها را انتخاب کنید یا محدوده را به صورت دستی و درون براکت قرار دهید. اگر از روش دوم استفاده میکنید به یاد داشته باشید که با استفاده از $ آدرس ها را ثابت کنید.بدون تنظیم هیچ نوع فرمتی ok را بزنید.

3. Conditional Formatting > Manage Rules… را بزنید و در کنار قانونی که ایجاد کرده اید، تیک مربوط stop if true را قرار دهید. نتیجه در تصویر زیر نشان داده شده است.

مثال 2. حذف Conditional Formatting از سلول های خالی

فرض کنید از قانون between برای هایلات مقادیر بین 0 تا 1000 دلار استفاده کرده اید. ولی مشکل اینجاست که سلول های خالی نیز هایلایت شده اند.

برای حل این مشکل، می بایست یک قانون از نوع “Format only cells that contain” ایجاد کنید. در دیالوگ New Formatting rule از لیست کشویی blank را انتخاب کنید.

دوباره، بدون تنظیم هیچ گونه فرمتی ok را انتخاب کنید.

در آخر، Conditional Formatting Rule Manager را باز کنید و در کنار قانون “blank” تیک مربوط به stop if true را قرار دهید.

همانطور که مشاهده میکنید نتیجه دقیقاً شبیه به انتظار شماست.

نحوه اصلاح قوانین Conditional Formatting در اکسل

اگر به دقت به تصویر بالا دقت کنید، احتمالاً متوجه دکمه  Edit rule خواهید شد. اگر میخواهید یک قانون فرمت دهی موجود را تغییر دهید، می بایست به شکل زیر عمل کنید.

1. سلول هایی را که قانون بر روی آنها اعمال میشود را انتخاب کنید و Conditional Formatting > Manage Rules… را انتخاب کنید.
2. در قسمت Conditional Manager Rules Manager dialog ، قانونی را که میخواهید تغییر دهید را انتخاب کنید و دکمه edit rule را بزنید.

3. تغییرات مورد نظر را در پنجره Edit Formatting Rule انجام دهید و ok را بزنید تا تغییرات ذخیره شود.

پنجره Edit Formatting Rule با پنجره New Formatting Rule که در زمان ایجاد قانون از آن استفاده کردید بسیار شباهت دارد و شما هیچ مشکلی با آن نخواهید داشت.

نکته:

اگر قانونی را که میخواهید تغییر دهید، پیدا نمیکنید، گزینه This Worksheet را از “Show formatting rules for” انتخاب کنید تا لیست تمام قوانین موجود در این ورک شیت را نمایش دهد.

نحوه کپی کردن Conditional Formatting در اکسل

اگر میخواهید Conditional Formatting را که قبلاً ایجاد کرده اید بر روی دیگر داده ها موجود در ورک شیت خود پیاده سازی کنید، نیازی نیست که دوباره قوانین را ایجاد کنید. برای اینکار کافیست از Format Painter برای کپی کردن فرمت به سلول های دیگر استفاده کنید.

1. بر روی سلولی که دارای Conditional Formatting دلخواه است کلیک کنید .
2. Home > Format Painter را انتخاب کنید . این کار نشانه گر ماوی را تبدیل به یک بروس نقاشی میکند.

نکته: اگر میخواهید conditional formatting را در چند نقطه کپی کنید میتوانید بر روی Format painter دابل کلیک کنید.

3. برای کپی کردن conditional formatting بر روی سلول اول محدوده مورد نظر کلیک کنید، و تا سلول آخر را انتخاب کنید.

4. زمانی که این کار را انجام دادید، دکمه esc را بزنید تا از حالت format painter خارج شوید.

نکته: اگر از conditional formatting با استفاده از فرمول، بهره برده اید، می بایست آدرس سلول ها را در درون فرمول ها بعد از کپی کردن آدرس های درون فرمول، تغییر دهید.

نحوه حذف قوانین مربوط به conditional formatting

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

• قسمت Conditional ManagerRules Manager را باز کنید( اگر به یاد داشته باشید، این قسمت را Conditional Formatting > Manage Rules باز کنید). قانون مورد نظر را انتخاب کنید و بر روی دکمه Delete Rule کلیک کنید.
• محدوده مورد نظر را انتخاب کنید، Conditional Formatting > Clear Rules را انتخاب کنید و یکی از گزینه های در دسترس را انتخاب کنید.

حال شما دانش اولیه در مورد Conditional Formatting را دارا هستید.

برای دیدن آموزش های مربوط به conditional formatting  کلیک کنید: فصل چهارم: آشنایی با فرمت دهی شرطی و اعتبار سنجی داده ها – دوره اکسل از صفر

ممنون که با ما همراه هستید : )