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

مه

در اکسل می توان تاریخ ها را جمع و یا از همدیگه کم کرد، چرا که اکسل با تاریخ ها مانند اعداد صحیح رفتار می کند. ما در این مطلب از وبسایت اوپن مایند ، از این خاصیت اکسل برای ساخت یک روزشمار تا یک تاریخ مشخص و یا یک رویداد تکرارشونده استفاده کنیم. به طور دقیق تر، قرار است به کارگیری توابع DATE ، YEAR ، MONTH ، DAY و TODAY برای نشان دادن تعداد روزهای باقی مانده تا تولد بعدی شما یا هر رویداد سالانه دیگر را آموزش دهیم.

همان طور که گفته شد، اکسل تاریخ ها را در قالب عدد صحیح (integer) ذخیره و پردازش می کند. به طور پیشفرض، اکسل عدد ۱ را برای بازنمایی ۰۱/۰۱/۱۹۰۰ استفاده میکند و هر روز بعد از این تاریخ یکی بیشتر از عدد قبلی است. مثلا اگر در اکسل تاریخ ۰۱/۰۱/۲۰۰۰ را بنویسید و سپس فرمت آن را از تاریخ به عدد تغییر دهید، شما ۳۶۵۲۶ را به جای آن تاریخ خواهید دید. در واقع با کم کردن ۱ از این عدد (برای خود روز ۰۱/۰۱/۲۰۰۰) ، می توانید بگویید که ۳۶۵۲۵ روز در قرن ۲۰ ام میلادی وجود داشته است که این کاملاً صحیح است.

به همین شیوه، در اکسل شما قادر هستید که تاریخی از آینده را وارد کنید و سپس مقدار تاریخ اجرای تابع TODAY را از آن کم کنید تا ببینید چند روز از امروز تا آن تاریخ باقی مانده است.

مروری بر توابع مربوط به تاریخ در اکسل

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

تابع TODAY

=TODAY()

خروجی فراخوانی: تاریخ روز جاری

تابع DATE

=DATE(year,month,day)

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

تابع YEAR

=YEAR(date)

خروجی فراخوانی: عدد سال تاریخی که به عنوان ورودی داده شده است

تابع MONTH

=MONTH(date)

خروجی فراخوانی: عدد ماه تاریخی که به عنوان ورودی داده شده است (از ۱ تا ۱۲)

تابع DAY

=DAY(date)

خروجی فراخوانی: عدد روز تاریخی که به عنوان ورودی داده شده است

ساخت مرحله به مرحله روزشمار

ما سه رویداد را که سالانه در همان روز تکرار می شوند را در نظر می گیریم و ضمن محاسبه تاریخ تکرار بعدی، تعداد روزهایی که میان تاریخ امروز و تاریخ تکرار بعدی وجود دارند را محاسبه می کنیم.

ابتدا در مثال خود چهار ستون Event و Date و Next_Occurrence و Days_Until_Next را ساخته ایم. سپس تاریخ های چند رویداد شامل: یک تاریخ تصادفی به عنوان تاریخ تولد، تاریخ روز پرداخت مالیات و در نهایت هالووین را اضافه کرده ایم. هر رویداد و تاریخ دیگری که به طور سالانه در یک روز مشخص از ماه تکرار می شود را می توانستیم در این مثال استفاده کنیم. اما توجه کنید که رویدادهایی مثل چهارشنبه سوری که در یک روز خاص هفته از یک ماه خاص تکرار می شوند، تاریخ تکرار ثابتی ندارند و برای این مثال درست نیستند.

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

برای ایجاد مقادیر ستون Next_Occurrence از همان روز و ماه تاریخ های ستون Date استفاده می کنیم (با استفاده از دو فراخوانی تابع

(MONTH(F3

برای ماه و

(DAY(F3

برای روز). چرا که این رویدادها در همان روز و ماه هر سال تکرار می شوند.
پس کار دشواری در پیش نداریم و تنها کافی است بررسی کنیم که آیا زمان تکرار رویداد در سال جاری گذشته است یا خیر. اگر گذشته باشد، عدد سال تاریخ تکرار بعدی رویداد را سال بعد در نظر می گیریم و در غیر این صورت در روزهای پیش روی همین امسال تکرار انجام خواهد شد و در تاریخ تکرار بعدی رویداد، عدد سال را همین امسال در نظر می گیریم.

تاریخ تکرار رویداد در سال جاری را ابتدا با فراخوانی زیر می سازیم و سپس در یک دستور IF بررسی می کنیم.

=DATE(YEAR(TODAY()),MONTH(F3),DAY(F3))

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

=IF(DATE(YEAR(TODAY()),MONTH(F3),DAY(F3))>=TODAY(),YEAR(TODAY()),YEAR(TODAY())+1)

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

YEAR(TODAY())+1

است.

تا اینجای کار عدد صحیح سال تکرار بعدی رویداد را به دست آورده ایم. شماره روز و ماه را هم از قبل می دانستیم. پس با ترکیب این سه، تاریخ صحیح تکرار بعدی را می سازیم. فرمول IF اخیر را به صورت زیر کامل تر می کنیم تا تاریخ مذکور را در ستون Next_Occurrence پر کنیم.

=DATE(IF(DATE(YEAR(TODAY()),MONTH(F3),DAY(F3))>=TODAY(),YEAR(TODAY()),YEAR(TODAY())+1),MONTH(F3),DAY(F3))

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

برای اعمال فرمول روی تمام خانه های ستون Next_Occurrence ابتدا خانه ها رو انتخاب (هایلایت) کنید و سپس Ctrl+D را با هم بزنید.

تنها کاری که باقی مانده، آن است که تاریخ به دست آمده از تکرار بعدی رویداد را از تاریخ امروز کم کنیم تا ببینیم چند روز از امروز تا آن روز باقی مانده است. بنابراین فرمول زیر را در خانه H3 می نویسیم و سپس با Ctrl+D در خانه ای زیر ستون Days_Until_Next کپی می کنیم.