مدیریت نحوه نمایش تاریخ‌ها در اکسل با Conditional Formatting — راهنمای جامع + فیلم

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

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

• تاریخ‌ها
• داده‌های تکراری
• مقادیر کمتر یا بیشتر از میانگین یک محدوده از سلول‌ها

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

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

بررسی تاریخ‌هایی که بین 30 روز، 60 روز و 90 روز از آن‌ها گذشته است

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

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

کلیک کنید

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

بررسی تاریخ‌هایی که 30 روز از آن‌ها گذشته است

برای شروع کار یک صفحه گسترده ایجاد کرده و سلول‌های C1 تا C4 را انتخاب می‌کنیم. این بخش انتخابی همان محدوده‌ای است که می‌خواهیم قالب‌بندی شرطی ما بر روی آن اعمال شود. حال گام‌های زیر را طی می‌کنیم:

1. از طریق منوی «Home» به بخش «Conditional Formatting» رفته و گزینه‌ی «New Rule» را بزنید تا کادر «New Formatting Rule» باز شود.
2. بر روی گزینه‌ی «Use a Formula to determine which cells to format» کلیک کنید.
3. فرمول زیر را در کادری که در زیر بخش «Format values where this value is true» قرار دارد وارد کنید:

   1=TODAY()-C1 >30

   این فرمول بررسی می‌کند که آیا از تاریخ‌های نوشته شده در سلول‌های C1 تا C4 بیش از 30 روز گذشته است یا خیر.

4. بر روی دکمه‌ی «Format» کلیک کنید تا کادر «Format Cells» باز شود.
5. بر روی زبانه‌ی «Fill» کلیک کنید تا گزینه‌های مربوط به رنگ پس‌زمینه نمایش داده شوند.
6. یک رنگ برای پس‌زمینه برگزینید.
7. وارد زبانه‌ی «Font» شوید تا گزینه‌های مربوط به قالب‌بندی فونت برایتان نمایان شوند.
8. رنگ فونت مورد نظر خود را برگزینید.
9. دو دفعه بر روی گزینه‌ی «OK» کلیک کنید تا این کادر بسته شده و به کاربرگ خود بازگردید.
10. رنگ پس‌زمینه‌ی سلول‌های C1 تا C4 به رنگ انتخاب شده تغییر پیدا خواهند کرد، هرچند که مقداری در این سلول‌ها وجود ندارد.

بررسی تاریخ‌هایی که بیش از 60 روز از آن‌ها گذشته باشد

به جای این که تمام گام‌های قبلی را برای ایجاد این شرط دنبال کنیم، از گزینه‌ی «Manage Rules» استفاده می‌کنیم تا بتوانیم شرط‌های بعدی خود را یکجا ایجاد نماییم.

1. سلول‌های C1 تا C4 را انتخاب کنید.
2. وارد زبانه‌ی «Home» شده و از بخش «Conditional Formatting» گزینه‌ی «Manage Rules» را بزنید تا کادر «Conditional Formatting Rules Manager» باز شود.
3. بر روی گزینه‌ی «New Rule» در بالا سمت چپ کادر کلیک نمایید.
4. بر روی گزینه‌ی «Use a Formula to determine which cells to format» در بالای پنجره کلیک کنید.
5. فرمول زیر را در کادری که در زیر بخش «Format Values where this value is» قرار دارد وارد نمایید:

   1=TODAY()-C1>60

   این فرمول بررسی می‌کند که آیا از تاریخ موجود در سلول‌های C1 تا C4 بیش از 60 روز گذشته است یا خیر.

6. بر روی دکمه‌ی «Format» کلیک کنید تا کادر «Format Cells» باز شود.
7. وارد زبانه‌ی «Fill» شوید تا گزینه‌های مربوط به رنگ کادر پیش‌زمینه نمایان شوند.
8. یک رنگ پیش‌زمینه برگزینید.
9. دو دفعه بر روی گزینه‌ی «OK» کلیک کنید تا به کادر «Conditional Formatting Rules Manager» بازگردید.

بررسی تاریخ‌هایی که بیش از 90 روز از آن‌ها گذشته باشد

پنج گام اول را همانند قبل دنبال نمایید تا یک شرط جدید ایجاد گردد.

1. از فرمول زیر استفاده نمایید:

   1=TODAY()-C1>90
2. یک رنگ پس‌زمینه انتخاب کنید.
3. رنگ فونت را برگزینید.
4. دو دفعه بر روی گزینه‌ی «OK» کلیک کنید تا کادر فعلی بسته شده و به کادر «Conditional Formatting Rules Manager» بازگردید.
5. بر روی گزینه‌ی «OK» کلیک کنید تا این کادر نیز بسته شده و به کاربرگ خود بازگردید.
6. رنگ پس‌زمینه‌ی سلول‌های C1 تا C4 به آخرین رنگ انتخاب شده در خواهند آمد.

آزمایش قوانین اعمال شده در قالب‌بندی شرطی ایجاد شده

با وارد کردن چند تاریخ (به میلادی) می‌توانید صحت شرط‌های ایجاد شده‌ی خود را آزمایش کنید:

• تاریخ امروز را وارد نمایید. این سلول باید رنگ پس‌زمینه‌ی پیشفرض (سفید) را به خود گرفته و رنگ متن آن نیز مشکی باشد، چراکه هیچ یک از شروط بر روی آن اعمال نخواهد شد.
• تاریخی مربوط به 40 روز پیش را وارد نمایید. این سلول باید قالب‌بندی تنظیم شده برای تاریخ‌های 30 روز قبل را به خود بگیرد. با استفاده از فرمول زیر می‌توانید تاریخ 40 روز پیش را در سلول وارد نمایید:

  1=TODAY() - 40
• تاریخ 70 روز پیش را وارد کنید. قالب‌بندی این سلول باید به چیزی که برای تاریخ‌های بیش از 60 روز قبل وارد کرده بودید تغییر کند.
• تاریخ 100 روز پیش را وارد کنید. قالب‌بندی این سلول باید به چیزی که برای تاریخ‌های بیش از 90 روز وارده کرده بودید تغییر کند.

شرط‌های بیشتر در قالب‌بندی شرطی

اگر کاربرگ شما نیز همانند بیشتر کاربرگ‌ها تاریخ امروز را نمایش می‌دهد، می‌توانید از یک فرمول دیگر به جای استفاده از فرمول «TODAY» برای نمایش تاریخ استفاده کنید.

فیلم آموزش اکسل Microsoft Excel 2019 – مقدماتی در فرادرس

کلیک کنید

برای مثال اگر تاریخ امروز را در سلول B4 وارد کرده باشید، می‌توانید برای بررسی این که یک تاریخ مربوط به بیش از 30 روز پیش است یا خیر، از فرمول جایگزین زیر استفاده نمایید:

1=$B$4 > 30

علامت دلار ($) در کنار B و 4 باعث می‌شود که در صورت کپی کردن این فرمول در سلول‌های دیگر، این فرمول تغییری نکند. در واقع علامت دلار برای ایجاد یک ارجاع مطلق به یک سلول مورد استفاده قرار می‌گیرد. اگر علامت دلار را از فرمول خود حذف کنید و سپس فرمول را به سلول یا سلول‌های دیگری کپی کنید، با خطای «!REF#» مواجه خواهید شد.

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

• آموزش‌های مجموعه نرم‌افزاری آفیس
• گنجینه آموزش های اکسل (Microsoft Excel)
• آموزش استفاده از توابع و فرمول نویسی در اکسل
• ۱۶ فرمول اکسل که به حل مشکلات روزمره کمک می‌کنند
• فرمول نویسی در اکسل – آموزش مقدماتی

^^