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

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

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

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

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

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

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

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

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

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

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

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

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

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

گام سوم: استفاده از فرمت دهی شرطی Conditional Formatting

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

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

شکل ۴- فرمول نویسی در فرمت دهی شرطی 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<۰ بررسی میکنه که آیا عدد داخل سلول E3 منفی هست یا نه. اگر منفی بود یعنی خروجی فرمول True هست، پس فرمت مورد نظر (رنگ قرمز) رو اعمال میکنه. اگر هم عدد داخل سلول E3 منفی نباشه، خروجی فرمول False خواهد بود. پس فرمت تعیین شده اعمال نمیشه.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

آلارم در اکسل با استفاده از VBA

خیلی وقت ها در فایل اکسل این موضوع پیش میاد که بخوایم در شرایط مختلفی مثلا (انتخاب یک شیت خاص، باز کردن فایل، قبل از بستن فایل و ….) پیامی رو در قالب آلارم در اکسل نمایش بدیم. این قبیل عملیات مثل انتخاب شیت و … در اکسل، با استفاده از توابع قابل شناسایی نیست و برای همین نمیتونیم مستقیما از Conditional Formatting و فرمول نویسی در این ابزار استفاده کنیم. برای این موضوع باید بریم سراغ کدنویسی VBA. از طریق کدنویسی میتونیم روی عملیاتی مثل انتخاب شیت خاص یا باز شدن فایل و … هشدارهای دلخواه رو نمایش بدیم. پس بریم ببینیم که برای نمایش پیام دلخواه در اکسل چه کاری باید انجام بدیم.

مرحله اول: ورود به محیط VBA

برای اینکه شروع به کدنویسی در اکسل بکنیم، اول از همه باید وارد محیط VBA بشیم. برای این کار کافیه یا کلید Alt+F11 رو بزنیم، یا روی سربرگ شیت (Sheet Tab) کلیک راست کرده و گزینه View Code رو بزنیم. یا اینکه از تب Developer روی گزینه Visual Basic کلیک کنیم. هر ۳ مسیر، ختم میشن به محیط کدنویسی VBA.

مرحله دوم: تعیین شرایط نمایش آلارم

اول از همه باید مشخص کنیم که چه زمانی و در چه حالتی میخواهیم این پیام نمایش داده بشه. مثلا به محض باز کردن فایل اکسل، قبل از بستن فایل اکسل، انتخاب شیت خاص و …..

به هر کدوم از این فعالیت ها در محیط VBA و زبان برنامه نویسی ، Event گفته میشه. مثلا فرض کنید میخواهیم روی باز شدن فایل اکسل، پیام خاصی نمایش داده بشه. برای این کار از سمت چپ، روی This Workbook دبل کلیک میکنیم. حالا میتونیم از سمت راست، تنظیمات دلخواه رو انتخاب کنیم. (شکل ۱)

شکل ۱- کلیک روی this workbook

حالا از سمت راست و از لیست کشویی نمایش داده شده در شکل ۲، گزینه Workbook رو انتخاب میکنیم. به محض انتخاب Workbook سمت راست، گزینه Open نمایش داده میشه و در محیط کدنویسی، عبارتی مشابه زیر نمایش داده میشه:

Private Sub Workbook_Open()

End Sub

این یک Sub است که کارش نگهداری کد VBA هست. مثلا فرض کنید ما میخواهیم پیغامی نمایش داده بشه، پس باید کد مربوط به نمایش پیغام رو در این Sub بنویسیم. حالا این کد چه زمانی اجرا میشه؟ زمانی که فایل اکسل یا همون Workbook باز بشه.

شکل ۲- انتخاب Workbook

اگر بخواهیم کد در زمانی غیر از باز شدن فایل اکسل اجرا بشه، کافیه لیست کشویی سمت راست رو باز کنیم و حالت دلخواه رو انتخاب کنیم: (شکل ۳)

شکل ۳- انتخاب event مورد نظر

در ادامه چند مورد رو شرح میدهیم:

NewSheet: یعنی به محض ایجاد شیت جدید، کد مورد نظر که در Sub قرار گرفته ایجاد بشه.

BeforeSave: یعنی کد مورد نظر قبل از سیو کردن اجرا بشه.

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

مرحله سوم: کدنویسی ایجاد آلارم

حالا که مشخص کردیم که کد مورد نظر چه زمانی و در چه صورتی باید اجرا بشه، باید دستور مورد نظر رو وارد Sub مربوطه کنیم. ما میخواستیم هنگام باز شدن فایل اکسل، پیغامی نمایش داده بشه. پس باید کد مربوط به نمایش پیغام رو وارد کنیم. کد مربوط به نمایش پیام MSGbox هست که ساختار این کد رو در زیر مشاهده میکنید:

MsgBox (“به اکسل پدیا خوش آمدید!”)

حالا کافیه این کد رو به Sub مربوطه انتقال بدیم. یعنی:

Private Sub Workbook_Open()
MsgBox ("به اکسل پدیا خوش آمدید!")
End Sub

این ۳ خط کد یعنی اینکه موقع باز کردن فایل پیام “به اکسل پدیا خوش آمدید!” نمایش داده بشه.

مرحله چهارم: ذخیره فایل اکسل برای نگهداری کدها

حالا باید فایل اکسل رو با فرمت MacroEnable.xlsm ذخیره کنیم. دقت داشته باشید که اگر فایل رو با پسوند ذکر شده ذخیره نکنیم، ابتدا پیام شکل ۴ نمایش داده میشه که هشدار میده که فایل باید Xlsm ذخیره بشه و اگر Yes بزنیم فایل رو بصورت معمولی ذخیره و همه کدهای نوشته شده را حذف میکند. پس No رو انتخاب میکنیم که پنجره مربوط به ذخیره کردن فایل باز بشه و فرمت Excel Macro-anable رو انتخاب کنیم.

شکل ۴- پیغام هشدار جهت ذخیره فایل بصورت Macro-enabled

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

مثلا میخواهیم این پیام، در لحظه انتخاب شیت سوم اجرا بشه. برای این کار کافیه اول روی sheet3 دبل کلیک کرده و از اولین لیست کشویی گزینه Worksheet رو انتخاب کنیم (در مثال قبل Workbook رو انتخاب کردیم) و از لیست کشویی دوم Activate رو انتخاب کنیم، به این ترتیب Sub زیر ایجاد میشه:

Private Sub Worksheet_Activate()

End Sub

در این Sub هر کدی نوشته بشه، زمانی اجرا میشه که شیت ۳ فعال بشه. پس کد بصورت زیر تغییر میکند:

Private Sub Worksheet_Activate()
MsgBox ("به اکسل پدیا خوش آمدید!")
End Sub

شکل ۵- نمایش پیغام دلخواه در زمان انتخاب شیت خاص

کدی که ما در اینجا نوشتیم، فقط نمایش ساده یک پیغام هست. این کد میتونه چندین خط و شامل دستورات متنوعی باشه، فقط باید این نکته رو بدونیم که هر دستوری در این Sub نوشته بشه، زمانی اجرا میشه که شیت مورد نظر، انتخاب و فعال بشه. مثلا فرض کنید بخوایم بگیم که بعد از نمایش پیغام، سلول خاصی مثلا F10 رو هم انتخاب کنه. در این صورت باید کد مربوط به انتخاب یک سلول رو هم اضافه کنیم. کد بصورت زیر خواهد بود:

Private Sub Worksheet_Activate()
MsgBox ("به اکسل پدیا خوش آمدید!")
Range("F10").Select
End Sub

پس مشاهده میکنید که در حالت های مختلفی میشه کدهای مختلفی رو اجرا کرد.

یکی از راه های خیلی خوب برای اینکه مثلا ببینیم کد مربوط به انتخاب سلول در محیط VBA چی هست اینه که ماکرو ضبط کنیم و کدها رو مشاهده کنیم. اگر با این موضوع آشنا نیستید حتما مقاله مربوط به ضبط ماکرو در اکسل رو مطالعه کنید.

میتونید مطالب این مقاله رو در ویدئو زیر هم مشاهده کنید و هر سوالی داشتید در ادامه همین مقاله و در قالب کامنت ثبت بفرمایید.

مشاهده ویدئو نحوه ایجاد آلارم در اکسل با VBA

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

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