آخرین مطالب ارسالی
Last Posts
آهنگ های ویژه
Best Posts
کاشت مو
بهترین سایت کاشت مو با کمترین قیمت
کلینیک زیبایی
آلارم در اکسل | سر رسید چک در اکسل تصویری

ایجاد Alarm (هشدار) در اکسل

۷ رأی، میانگین: ۵

آلارم در اکسل یک مسئله گرافیکی

مسئله تعریف هشدار یا آلارم در اکسل یکی از مسائلی هست که حالت های بسیار متنوعی داره. مثلا یک لیست از کارها داریم که تاریخ انجام مشخصی دارن؛ میخوایم به گونه ای تنظیم بشه که بعد از انجام شدن و حتی چند روز قبل از تاریخ مورد نظر، با رنگ های مختلف به ما آلارم داده بشه که مثلا فعالیت ۲ و ۳ دو روز بیشتر مهلت انجام ندارن. یا مثلا میخوایم آلارم به گونه ای باشه که به محض انتخاب یک شیت، پیام مشخصی نمایش داده بشه.
همونطور که گفتم مسئله تعریف ایجاد آلارم در اکسل بسیار متنوع هست و حالت های مختلفی داره. اما موضوعی که بین همه اینها مشترک هست اینه که این یک مسئله شرطی هست، یعنی آلارم مورد نظر ما (می تونه تغییر رنگ باشه، نمایش متن خاصی باشه، نمایش یک پنجره پیغام باشه و …) به یک شرط بستگی داره و اگه اون شرط برقرار باشه آلارم نمایش داده میشه.

در این مقاله به چگونگی ایجاد هشدار یا آلارم بصورت تغییر رنگ در اکسل می پردازم. فرض کنید لیستی از فعالیت ها (مطابق شکل ۱) داریم که هر کدام تاریخ مشخصی برای انجام دارند.

دیتابیس فعالیت ها

شکل ۱- ایجاد آلارم در اکسل – دیتابیس فعالیت ها

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

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

نکته:
اگر از تاریخ شمسی در اکسل ۲۰۱۶ استفاده میکنید، نیازی به فرمول تبدیل تاریخ شمسی به میلادی ندارید. چون تاریخ شمسی در اکسل در افیس ۲۰۱۶، صرفا فرمت هست و منطق تاریخ، همون تاریخ میلادی هست.

 

منطق کلی کار به شرح زیر است:

گام اول: تعیین تاریخ معیار برای محاسبه فاصله تاریخ هر فعالیت

حتما بخوانید:  ۳ روش برای شماره ردیف خودکار در اکسل

همونطور که میدونید تاریخ روز سیستم (تاریخ معیار) با استفاده از تابع Today() بدست میاد. پس کافیه Today رو از تاریخ هر فعالیت کم کنیم.

محاسبه فاصله تاریخ روز سیستم و مهلت انجام هر فعالیت

شکل ۲- محاسبه فاصله تاریخ روز سیستم و مهلت انجام هر فعالیت

گام دوم: مقایسه نتیجه با شاخص مورد نظر

حالا میخوایم نتیجه فاصله دو تاریخ رو با شاخص های مورد نظر مقایسه کنیم. بعبارتی، اگر فاصله دو تاریخ از ۳ روز کمتر بود، زرد، اگر از ۳ روز بیشتر بود ، سبز و اگر منفی بود (یعنی از تاریخ مورد نظر رد شده)، قرمز رو نمایش بده.

مقایسه فاصله دو تاریخ با شاخص های مورد نظر

شکل ۳- مقایسه فاصله دو تاریخ با شاخص های مورد نظر

نکته:
در واقع ما برای ایجاد این سیستم، نیازی به ستون F نداریم. فقط جهت درک بهتر منطق Logical و منطق نوشتن IF این گام اضافه شده است.

 

گام سوم: استفاده از فرمت دهی شرطی Conditional Formatting برای رنگی کردن فعالیت های مورد نظر.

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

حالا نتیجه ای که در گام دوم و با استفاده از If بدست آوردیم رو میخوایم از طریق ابزار Conditional Formatting انجام بدیم. در واقع رنگ رو روی هر سطر اعمال کنیم.

برای این کار باید با فرمول نویسی در این ابزار آشنا باشیم. فرمول نویسی در ابزار فرمت دهی شرطی از مسیر Home/ Conditional Formatting/ New Rule و قسمت نشان داده شده در شکل ۴ انجام میشه.

فرمول نویسی در فرمت دهی شرطی Conditional Formatting

شکل ۴- فرمول نویسی در فرمت دهی شرطی Conditional Formatting

منطق فرمول نویسی در این ابزار، IF یا همون منطق Logical هست. یعنی همواره باید گزاره ای نوشته بشه که نتیجه اون True یا False باشه. اگر True باشه، فرمت مورد نظر اعمال میشه و اگر False باشه، فرمت مورد نظر اعمال نمیشه. حالا طبق صورت مسئله، سه تا شرط داریم، برای اختلاف های منفی، بزرگتر از ۳ و کوچکتر از ۳:

شرط اول، اختلاف دو تاریخ، منفی (کوچکتر از صفر) باشه:

روی سلول A3 کلیک کرده و از مسیر زیر، به قسمت فرمول نویسی در Conditional Formatting رفته و فرمول زیر رو می نویسیم. سپس از قسمت Format، رنگ مورد نظر (قرمز برای اختلاف کمتر از صفر) رو انتخاب میکنیم.(شکل ۵)

Home/ Conditional Formatting/ New Rule/ Use a formula to determine which cells to format

مال شرط اول، قرمز شدن اختلاف هایی که منفی هستن

شکل ۵- اعمال شرط اول، قرمز شدن اختلاف هایی که منفی هستن

فرمول $E3<0 بررسی میکنه که آیا عدد داخل سلول E3 منفی هست یا نه. اگر منفی بود یعنی خروجی فرمول True هست، پس فرمت مورد نظر (رنگ قرمز) رو اعمال میکنه. اگر هم عدد داخل سلول E3 منفی نباشه، خروجی فرمول False خواهد بود. پس فرمت تعیین شده اعمال نمیشه.

حتما بخوانید:  رسم نمودار هیستوگرام در اکسل

بعد از زدن OK، اگر سلول E3 منفی باشه، سلول A3 قرمز میشه. دقت کنید چون شرط رو روی سلول A3 نوشتیم، رنگ هم روی همون سلول اعمال میشه.

حالا میخوایم کل سطر مربوط به هر عدد منفی قرمز بشه، (یعنی اگر هر داده ای از ستون E منفی بود، کل سطر مربوط به اون سلول، از ستون A تا D رو رنگی بشه). برای این کار از مسیر Home/ Conditional Formatting، گزینه Manage Rules رو انتخاب میکنیم. از پنجره نمایش داده شده و از قسمت Applies to محدوده مورد نظر رو تعیین میکنیم. (مطابق شکل ۶)

گسترش فرمت تعیین شده به محدوده دلخواه

شکل ۶- گسترش فرمت تعیین شده به محدوده دلخواه

با این کار و بعد از زدن OK، نتیجه رو در جدول مورد نظر مشاهده میکنیم و هر جا در ستون E عدد منفی وجود داشته باشه، کل سطر مربوط به اون سلول، تغییر رنگ میده.

شرط دوم، اختلاف دو تاریخ، بزرگتر از ۳ باشه:

مجددا روی سلول A3 کلیک کرده و از مسیر قبلی، فرمول زیر رو می نویسیم. سپس از قسمت Format، رنگ سبز برای اختلاف بیشتر از ۳ رو انتخاب میکنیم.(شکل ۷)

اضافه کردن شرط دوم برای اختلاف های بزرگتر از 3

شکل ۷- اضافه کردن شرط دوم برای اختلاف های بزرگتر از ۳

مانند شرط اول، محدوده این شرط رو هم باید به کل جدول گسترش بدیم که هر سلولی در ستون E بزرگتر از ۳ بود، سبز بشه. مطابق شکل ۸

گسترش محدوده شرط دوم به کل جدول مورد نظر

شکل ۸- گسترش محدوده شرط دوم به کل جدول مورد نظر

با زدن OK ردیف هر سلولی در ستون E که بزرگتر از ۳ هست، به رنگ سبز در میاد.

شرط سوم، اختلاف دو تاریخ، کوچکتر و مساوی ۳ باشه:

مجددا روی سلول A3 کلیک کرده و از مسیر قبلی، فرمول زیر رو می نویسیم. این بار باید یک شرط دیگه هم اضافه کنیم. چون عددی که کوچکتر از ۰ باشه، کوچکتر از ۳ هم هست. پس ممکنه شرط ها با هم تداخل داشته باشن و اعداد منفی هم زرد بشن. برای اینکه این مسئله رو حل کنیم، یک راه اینه که یک شرط رو برای فرمول اضافه کنیم. یعنی علاوه بر اینکه کوچکتر مساوی ۳ هست، بزرگتر مساوی صفر هم باشه. که این دو شرط رو با تابع AND می نویسیم:

حتما بخوانید:  دو روش برای ایجاد سطر جدید در یک سلول

=AND ( $E3<=3 , $E3>=0 )

حالا از قسمت Format، رنگ زرد برای اختلاف کمتر و مساوی ۳ (در واقع کارهایی که ۳ روز و یا کمتر تا تاریخ انجام، مهلت دارند) رو انتخاب میکنیم.(شکل ۹)

افزودن شرط سوم برای اختلاف تاریخ کمتر از 3

شکل ۹- افزودن شرط سوم برای اختلاف تاریخ کمتر از ۳

و دوباره محدوده این شرط رو هم باید به کل جدول مورد نظر گسترش بدیم. برای این کار مطابق مراحل قبلی از قسمت Manage Rules محدوده شرط زرد رنگ رو A3:D12 تغییر میدیم. با زدن Ok، نتیجه نهایی بصورت شکل ۱۰ خواهد بود:

نتیجه

شکل ۱۰- ایجاد آلارم در اکسل – نتیجه

حالا اگر بخوایم بدون سلول کمکی و بطور مستقیم این کار رو انجام بدیم، کافیه فرمول موجود در ستون E که اختلاف تاریخ مورد نظر رو با تاریخ روز سیستم محاسبه میکرد رو جایگزین $E3 در فرمول هایی که نوشتیم بکنیم. به شکل ۱۱ دقت کنید، فرمول های نوشته در سه شرط به شرح شکل ۱۱ تغییر خواهد کرد.

حذف سلول های کمکی و تغییر فرمول ها

شکل ۱۱- حذف سلول های کمکی و تغییر فرمول ها

نکته:
مبحث آدرس دهی ($) در فرمت دهی شرطی اهمیت خیلی زیادی داره. در مثال که حل شد، دیدید که ستون سلول E3 مطلق بود یعنی $E3. اگر اینکار رو نمیکردیم، با گسترش محدوده Apply، شرط هم از ستون E به ستون های کناری منتقل میشد و نتیجه دلخواه رو نمیگرفتیم. در واقع انتقال شرط به سایر محدوده ها به منزله درگ کردن فرمول هست. برای همین بحث $ها در ابزار Conditional Formatting خیلی خیلی اهمیت پیدا میکنن.

 

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

اینها نکات خیلی مهمی در بکارگیری هر چه بهینه تر و دقیق تر ابزار فرمت دهی شرطی Conditional Formatting هستند. حتما خوب مطالعه و تمرین کنید.

توجه داشته باشید این فایل بر اساس تاریخ روز سیستم (Today) کار میکنه و بسته به اینکه چه روزی این فایل باز بشه نتیجه فرق میکنه. با تغییر تاریخ ها نتایج مختلف رو بررسی کنید.

دانلود فایل آموزش ایجاد آلارم در اکسل

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

آلارم در اکسل | سر رسید چک در اکسل تصویری

با عضویت در سایت به صورت مستقیم دانلود کنید

آلارم در اکسل | سر رسید چک در اکسل تصویری