Conditional Formatting با فرمول: رنگ‌آمیزی ردیف، سررسید و تکراری‌ها

بگذارید داده خودش حرف بزند

Home ← Conditional Formatting قالب سلول را بر اساس مقدارش تغییر می‌دهد. قاعده‌های آماده (بزرگ‌تر از، ده مورد برتر، تکراری، Data Bar، Color Scale، Icon Set) برای شروع خوب‌اند، اما قدرت اصلی در گزینه‌ی New Rule ← Use a formula to determine which cells to format است: هر فرمولی که TRUE برگرداند، قالب را فعال می‌کند.

قانون طلایی: فرمول برای سلول بالا-چپ نوشته می‌شود

اول کل محدوده را انتخاب کنید (مثلاً A2:H500)، سپس فرمول را طوری بنویسید که برای اولین سلول آن (A2) درست باشد. اکسل آن را مثل یک فرمول کپی‌شده برای بقیه‌ی سلول‌ها تنظیم می‌کند. برای رنگ کردن کل ردیف بر اساس یک ستون، ستون را با $ ثابت کنید و سطر را آزاد بگذارید.

محدوده: A2:H500   (جدول فروش؛ F=مبلغ، D=تاریخ سررسید، G=وضعیت)

کل ردیف فاکتورهای بالای 100 میلیون:
=$F2>=1E8

چک‌های سررسیدگذشته‌ای که وصول نشده‌اند:
=AND($D2<TODAY(), $G2<>"وصول شد")

سررسید در هفت روز آینده:
=AND($D2>=TODAY(), $D2<=TODAY()+7)

شماره فاکتور تکراری:
=COUNTIF($B$2:$B$500, $B2)>1

روزهای جمعه:
=WEEKDAY($A2, 16)=7

ردیف‌های یکی در میان (بدون Table):
=MOD(ROW(), 2)=0

مقایسه با هدف در شیت دیگر (نام Target):
=$F2<Target

مدیریت قاعده‌ها

Conditional Formatting ← Manage Rules: در بالای پنجره «Show formatting rules for» را روی This Worksheet بگذارید تا همه را ببینید. قاعده‌ها از بالا به پایین ارزیابی می‌شوند؛ با فلش‌ها ترتیب را عوض کنید و برای قاعده‌ای که نباید با بقیه ترکیب شود، Stop If True را بزنید. ستون «Applies to» را همیشه بررسی کنید.

نکته‌هایی که کمتر کسی می‌داند

• Cut، Paste و درج ردیف، قاعده‌ها را تکه‌تکه می‌کند؛ بعد از مدتی صدها قاعده‌ی تکراری با محدوده‌های پراکنده خواهید داشت که فایل را کند می‌کنند. هر چند وقت یک بار Manage Rules را مرتب کنید، یا داده را در Table نگه دارید.
• در Icon Set گزینه‌ی Show Icon Only عدد را پنهان می‌کند؛ کنار ستون عددی یک ستون «=F2» بسازید و فقط آیکون نشان دهید تا جدول شلوغ نشود.
• Conditional Formatting نمی‌تواند به فایل دیگر ارجاع دهد و در نسخه‌های قدیمی به شیت دیگر هم نمی‌توانست؛ از یک Named Range واسط استفاده کنید.
• برای کشیدن خط جداکننده‌ی ماه‌ها در یک گزارش مرتب‌شده بر اساس تاریخ، از =MONTH($A2)<>MONTH($A3) با فقط Border پایین استفاده کنید؛ گزارش ماهانه بدون درج ردیف خالی خوانا می‌شود.
• Format Painter و Paste Special ← Formats قاعده‌ها را هم کپی می‌کنند؛ راه سریع انتقال یک قاعده‌ی پیچیده به ستون‌های دیگر.