اگر ویژگی های Highlight Cells Rules (قوانین هایلایت کردن سلولها)، Top/Bottom Rules (قوانین بالا و پایین)، Data Bars (نوارهای داده)، Color Scales (مقیاس های رنگ) و Icon Sets (مجموعه آیکان) برای شما کافی نباشند، شما می توانید قانون جدیدی را ایجاد کنید. برای مثال، در اینجا می خواهیم مقادیر موجود در ستون code را که بیشتر از یکبار اتفاق افتاده اند و در محدوده سلولهای A2:A10 قرار دارند و فیلد score آنها بیش از 100 باشد، هایلایت کنیم.





➊ محدوده A2:A10 را انتخاب کنید.


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


➌ بر روی دستور New Rule کلیک کنید.


توجه: گزینه های Highlight Cells Rules, Top/Bottom Rules, Data Bars, Color Scales and Icon Sets میانبر می باشند. این گزینه ها را در قسمت New Rule نیز می توانید مشاهده نمایید.

➍ گزینه Use a formula to determine which cells to format را انتخاب کنید.

➎ فرمول زیر را وارد نمایید:

=AND(COUNTIF($A$2:$A$10,A2)>1,B2>100)

➏ یک سبک قالب بندی (formatting style) را انتخاب کرده و OK کنید.


نتیجه: اکسل سلول A5 را قالب بندی می کند، چرا که مقدار A بیش از یکبار در محدوده A2:A10 تکرار شده است و در ضمن مقدار 150 که در سلول B5 قرار دارد از عدد 100 بزرگتر می باشد.


توضیح: فرمول COUNTIF($A$2:$A$10,A2) تعداد کدهای موجود در محدوده A2:A10 را که برابر با کد موجود در سلول A2 باشند را می شمارد. اگر COUNTIF($A$2:$A$10,A2) > 1 باشد و B2 > 100 باشد، اکسل سلول A2 را قالب بندی می کند. از آنجا که ما قبل از کلیک بر روی دستور Conditional Formatting محدوده A2:A10 را انتخاب کرده بودیم، اکسل به صورت اتوماتیک فرمول را به سایر سلولها کپی می کند. بنابراین در سلولهای اشاره شده، فرمول های زیر را خواهیم داشت:

A3: =AND(COUNTIF($A$2:$A$10,A3)>1,B3>100)

A4: =AND(COUNTIF($A$2:$A$10,A4)>1,B4>100)

...

توجه کنید که چگونه از ارجاع مطلق (absolute reference) به شکل ($A$2:$A$10) استفاده کرده ایم.

دانلود فایل اکسل

فایل اکسل این مثال را می توانید از لینک زیر دانلود کنید:

دانلود فایل اکسل