img

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

/
/
/

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

توابع تاریخ و زمان اکسل که ما در این جا مورد بررسی قرار می دهیم-از جمله تابع‌های

EDATE, YEARFRAC , EOMONTH, NETWORKDAYS.INTL

چهار تابع از پر استفاده ترین توابع در اکسل برای شمارش ایام می باشند. در هر تابعی که در زیر قرار گرفته است، ابتدا آن تابع را تعریف می کنیم، سپس اجزای و نحوه کارکردن آن تابع را توضیح می دهیم، که در این مرحله باید مقادیری را به تابع بدهیم تا محاسبات را برای ما انجام دهد. سپس نمونه ای از ترکیب دستورات موجود در این توابع را به شما نشان خواهیم داد-از جمله چیزهایی که در این مرحله یاد می گیرید این است که چگونه فرمول را بنویسید، که این کار در بردارنده مراحل نام تابع، پرانتزها، جداکننده های کاما، و اجزای دیگر تشکیل دهنده دستور می باشد.  توجه کنید که آرگومان ها همیشه در بین پرانتز ها قرار می گیرند، و تک تک این آرگومان ها نیز به وسیله کاما از هم جدا می شوند.
تابع EDATE()
EDATE یک تابع کاربردی برای نمایش دادن یک تاریخ، شماره ای از ماه در گذشته یا آینده، با استفاده از یک مقدار ثبت برای تاریخ های آینده و یک مقدار منفی برای تاریخ های گذشته می باشد. برای نمونه، شما می توانید از این تابع برای محاسبه یک تاریخ بازنشستگی یا تاریخ انقضاء استفاده کنید و این کار را با استفاده از محاسبه سن فردی از روی تاریخ تولد، یا اضافه کردن یک عدد مشخص از سال به یک تاریخ مشخص انجام دهید.
آرگومان های متعلق به این تابع عبارتند از:
Start_date : یک تاریخ را مشخص کنید تا تاریخ شروع (البته باید به فرمت صحیح اکسل در قالب اعداد پشت سر هم باشد) را به شما نشان دهد.
Months : تعداد ماه ها قبل یا بعد از تاریخ شروع را برای شما نمایش می دهد. و قالب دستور آن به شکل زیر است:

=EDATE(start_date,months)

۱- برای مثال، در این جا می خواهیم تاریخ بازنشستگی را برای یکی از همکاران خودمان محاسبه کنیم. باید یک صفحه کاری جدید را در اکسل باز کنید و در فیلد عنوان، اسامیName (نام)، Birthday (تاریخ تولد)، Retirement Date (تاریخ بازنشستگی)، Time Left (زمان باقی مانده) و In Years (سال ها) را برای ستون های A3، B3، C3، D3 و E3 در این صفحه وارد کنید.
۲- در ستون های A و B نام ها و تاریخ تولدها را وارد کنید.
۳- بر روی سلول C4 کلیک کنید، سپس به سربرگ Formulas > Data & Time بروید و تابع EDATE را از لیست مورد نظر انتخاب کنید. شما همچنین می توانید با وارد کردن فرمول EDATE() به صورت دستی در سلول C4 یعنی =EDATE(B4,12*62) وارد کنید. اگر شما نمی خواهید تعدادی از ماه ها را وارد و محاسبه کنید، پس کار خیلی راحت تر این است که ۱۲ را در سن بازنشستگی ضرب کنید (یعنی، ۱۲ ماه در سال ضربدر سن ۶۲ سالگی).
۴- فرمول موجود در سلول C4 را کپی کنید و در سلول های C5 تا C13 قرار دهید، به این ترتیب شما تاریخ بازنشستگی هریک از افراد را خواهید داشت. تابعد بعدی یعنی تابع YEARFRAC() را مطالعه کنید تا مدت زمان باقی مانده برای هر کارمند را ببینید.

تابع YEARFRAC()
این تابع تعداد روزهای بین دو تاریخ را به صورت یک عدد اعشاری محاسبه می کند، که منجر به یک کسر خواهد شد. این تابع استفاده های خاصی دارد زیرا دیگر توابع زمان و تاریخ اکسل تنها یک عدد را به شما نمایش می دهند. از این فرمول برای محاسبه تاریخ بازنشستگی استفاده کنید تا سن فردی را از روی تاریخ تولد به دست آورید، تا بتوانید سال های این تاریخ ها را محاسبع کنید، تا مطمئن شوید که درصدی از سال کامل شده را به دست آورده اید، و استفاده های زیاد دیگری که می توانید از آن داشته باشید. توجه کنید که اکسل از روزهای کامل بین دو تاریخ برای محاسبه کسری از سال به صورت یک مقدار اعشاری استفاده می کند. آرگومان های مورد استفاده برای این تابع عبارتند از:
Start_date: (تاریخ شروع)
End_date: (تاریخ پایان)
Basis (optional): نوعی از مبنا برای شما روز (یا کد)
آن گاه دستور نوشتنی این کار به صورت زیر است:

=YEARFRAC(start_date,end_date,basis)

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

=YEARFRAC(TODAY(),C41)

را در سلول D4: وارد کنید. آرگومان start_date در این فرمول عبارت TODAY() است-که مربوط به یک روز معمولی است. تابع TODAY() تاریخ امروز را به شما نشان می دهد. تاریخ مربوط به سلول C4 تاریخ پایان را نشان می دهد، و گزینه اضافی basis عدد ۱ می باشد که به معنی روزهای ماه و روزهای سال می باشد.
شاید این کارها کمی گیج کننده به نظر برسد، اما نرم افزار اکسل پنج گزینه را برای محاسبه روزها و سال ها در این فرمول در اختیار شما قرار می دهد. برخی از حسابداران در اروپا و آمریکا بر روی ماه های ۳۰ روزه و سال های ۳۶۰ روزه کار می کنند؛ سیستم های دیگر کارهای خود بر پایه روزهای یک ماه بناکرده اند، اما یک سال را بازهم ۳۶۰ روز در نظر می گیرند. و برخی افراد روزها را در ماه ها و سال ها به صورت دلخواه تعریف می کنند. کدهای مربوط به هریک از این حالت ها به صورت زیر نوشته می شوند:
ماه های ۳۰ روزی NASD آمریکا/سال های ۳۶۰ روزی =۰
روزهای دلخواه در ماه/روزهای دلخواه در سال ها =۱
روزهای دلخواه در ماه ها/سال های ۳۶۰ روزی = ۳
روزهای دلخواه در ماه ها/سال های ۳۶۵ روزی = ۴
ماه های ۳۰ روزی اروپایی/ سال های ۳۶۰ روزی =۵
زمانی که این تابع را وارد کردید، اگر شما هر کلیدی (مانند کلید space) را بعد از آخرین آرگومان (سلول C4) بزنید، نرم افزار اکسل یک منو را برای شما باز می کند که در این منو پنج گزینه را در اختیار شما قرار می دهد. کد مناسب را برای کار خود از این لیست انتخاب کنید و Enter را فشار دهید.
اگر شما عدد basis را وارد نکنید، عدد پیش فرض آن ۰ خواهد بود، که به معنی ماه های ۳۰ روزی و سال های ۳۶۰ روزی می باشد-که از به دنبال دریافت نتایج زنده (real-time) هستید این گزینه پیش فرض برای شما مناسب نیست. آخرین ستون (E) در این صفحه گسترده نشان می دهد که زمانی که ستون D به صورت یک کسر قالب بندی شود چه شکلی خواهد شد. برای مثال، سلول E4 کمی کمتر از ۲ سال خواهد بود، در حالی که E5 کمی بزرگتر از ۱ سال است. سلول E6 برابر با ۱-۱/۴ سال است، سلول E7 برابر ۸-۳/۴ سال است و به همین ترتیب.

تابع EOMONTH()
از این تابع برای تعیین تاریخ آخرین روز ماه (در آینده یا در گذشته) استفاده کنید. به این دلیل که زمان ساختن یک صفحه گسترده با بسیاری از محاسبات، شعرها و تقویم ها شما نمی توانید این گونه نتایج را با استفاده فرمول های ذکر شده محاسبه کنید زیرا این فرمول ها نتایج عددی می دهند. این تابع یک عدد متوالی را که یک تاریخ مشخص را در اکسل نتیجه می دهد، نشان خواهد داد. آرگومان های این تابع عبارتند از:
Start_date: یک تاریخی که تاریخ شروع را در یک فرمت عدد متوالی مناسب در اکسل به شما ارائه می دهد.
Months : تعداد ماه های قبل یا بعد از تاریخ شروع یا همان start_date را به شما نشان می دهد.
کدنویسی مربوط به این تابع به صورت =EOMONTH(start_date,months) می باشد. برای آرگومان months، از اعداد مثبت برای نشان دادن آینده و از اعداد منفی برای تاریخ گذشته استفاده کنید.
۱- در سلول های A4 تا A13 ده تاریخ وارد کنید.
۲- فرمول =EOMONTH(A4,1) را در سلول B4 وارد کنید. سلول A4 تاریخ مربوط به آن سلول است، و تعداد ۱ در این جا به معنی یک ماه است. این به این معنی است که آخرین روز ماه موردنظر، یک ماه از تاریخ موجود در سلول A4 گذشته است. توجه کنید که این برنامه یک عدد متوالی را در اکسل به شما نشان خواهد داد.
۳- سپس، نشانگر ماوس خود را بر روی سلول B4 قرار دهید و کلید تابعی F2 (Edit) را فشار دهید. با حرکت دادن نشانگر بر روی واژه A در این فرمول و فشردن سه بار پشت سر هم کلید تابعی F4، یا تا زمانی که فرمول شما شبیه به =EOMONTH($A4,1) باشد این کار را انجام دهید.
۴٫ افرادی که با این قابلیت آشنایی ندارند، علامت دلار در جلوی واژه A به این معنی است که منبع ستون با کپی کرد فرمول تغییر نخواهد کرد. حال، زمانی که ما این فرمول را در سلول A4 کپی می کنیم و به سمت پایین می کشیم، سطرهای سلول ها تغییر خواهند کرد ولی ستون ها مانند قبل باقی می مانند.
۵- سپس، عدد متوالی موردنظر را در سلول B4 با فرمت تاریخ متوسط-بلند (medium-long date) تنظیم کنید، یعنی به صورت Mon Feb 26, 2016 (به معنی روز دوشنبه ماه فوریه روز ۲۶ ام سال ۲۰۱۶) قرار دهید.
۶- سلول B4 را به طرف پایین از B5 تا B13 کپی کنید. توجه کنید که فرمت عدد متوسط-بلند هم به همراه فرمول موردنظر کپی خواهد شد.
۷- سپس، این فرمول را در B4 تا C4، D4 و E4 کپی کنید. هریک از فرمول ها را ویرایش کنید تا اعداد ماه های جدید را نشان دهند: عدد ۱ را در سلول C4 به عدد ۶ تغییر دهید. عدد ۱ را در سلول D4 به عدد ۱۲ تغییر دهید و عدد ۱ در سلول E4 را به عدد ۱۸ تغییر دهید. حال سلول های C4 تا E4 را در سلول ها C13 تا E13 کپی کنید.
۸- به یاد داشته باشید که ستون ها را برای جای گرفتن فرمت های جدید به اندازه کافی پهن کنید، سپس خواهید دید که به چه سرعتی می توانی به استفاده از این تابع بسیار مفید، این اطلاعات را پیدا کنید.

تابع NETWORKDAYS.INTL()
این تابع تعداد روزهای کاری بین دو تاریخ مشخص را، به غیر از روزهای پایان هفته، برای شما محاسبه می کند. این تابع در صورتی برای شما مفید است که شما قصد محاسبه تعداد روزهای کاری (یا روزهای مدرسه) را در یک سال، سه ماه از یک سال، یا یک ترم را داشته باشید.
تفاوت بین این تابع با دیگر تابع ها این است که این تابع دارای گزینه هایی است که می توان به وسیله آن ها روزها را به عنوان روزهای پایان هفته حساب کرد. شرایط همه افراد این گونه نیست که روزهای شنبه و یکشنبه را تعطیل باشند. برخی دوشنبه و سه شنبه را تعطیل هستند، برخی دیگر روزهای چهارشنبه و جمعه را تعطیل هستند. با استفاده از این تابع، شما می توانید این روزهای پایان هفته را برای اکسل تعریف کنید تا با زمانبندی شخص شما انطباق داشته باشد.
علاوه بر این، این تابع به شما اجازه می دهد تا روزهای گردش مختص خود را در یک قالب زمانی مشخص انتخاب و تنظیم کنید. برای مثال، در سه ماهه آخر سال از تقویم سالیانه شما، دو روز تعطیل در ماه اکتبر، دو روز تعطیل در ماه نوامبر، و دو روز تعطیل در ماه دسامبر قرار دارد البته اگر شما روز هالووین و روز تعطیلی کریسمس (کریسمس ایو) را نیز به حساب بیاورید. اگر این دو شب خاص را در نظر نگیرید، آنگاه شما می توانید تعداد تعطیلی های ماه چهارم از سه ماه آخر سال خود را باید بر روی شش تنظیم کنید.
دستورات زیر، کدهایی هستند که شما می توانید روزهای پایان هفته خود را به دلخواه توسط آن ها تعریف کنید:
عدد روزهای آخر هفته
۱٫ شنبه، یکشنبه
۲٫ یکشنبه، دوشنبه
۳٫ دوشنبه، سه شنبه
۴٫ سه شنبه، چهارشنبه
۵٫ چهارشنبه، پنج شبه
۶٫ پنج شنبه، جمعه
۷٫ جمعه، شنبه
۱۱٫ فقط یکشنبه
۱۲٫ فقط دوشنبه
۱۳٫ فقط سه شنبه
۱۴٫ فقط چهارشنبه
۱۵٫ فقط پنج شنبه
۱۶٫ فقط جمعه
۱۷٫ فقط شنبه
اگر شما این پارامتر را در دستور موردنظر خالی (یا تعریف نشده) رها کنید، به صورت پیش فرض اکسل آن را ۱ درنظر می گیرد یعنی روزهای آخر هفته شما را همان روزهای شنبه و یکشنبه درنظر می گیرد.
روزهای تعطیلی را به صورت دامنه ای از سلول ها، جایی که شما تاریخ های اصلی تعطیلی (مانند سلول های F4 تا F10) را مشخص کرده اید یا لیستی از اعداد متوالی را که نشان دهنده تاریخ های واقعی تعطیلی شما هستند، وارد کنید.
آرگومان های مربوط به این تابع به صورت زیر هستند:
Start_date: تاریخ شروع
End_date : تاریخ پایان
Weekend: تنظیم کردن این که چه روزهایی از هفته را به عنوان روزهای آخر هفته درنظر بگیرد (پارامتر اضافی).
Holidays: یک منبع که اشاره به این دارد که تاریخ ها باید به عنوان روزهایی در نظر گرفته شوند که فرد در این روزها بیکار است (پارامتر اضافی).
کدنویسی مربوط به استفاده از این تابع به صورت

= NETWORKDAYS.INTL(start_date , end_date,[weekend],[holidays])

نوشته می شود.
۱- در صفحه گسترده خود عنوان های Start Date (تاریخ شروع)، End Date (تاریخ پایان)، Number of Word Days (تعداد روزهای کاری) را به ترتیب در سربرگ های مربوط به ستون های A، B، C، F، و G وارد کنید. درنهایت، عنوان Holidays را وارد کنید (که طبق تصویر در بالای هردو ستون F و G در یک سلول یکپارچه یا merge شده قرار می گیرد.)
۲- تعدادی از تاریخ های تصادفی را در ستون های A و B وارد کنید. مطمئن شوید که ستون End Date تاریخی مربوط به قبل از تاریخ Start Date نباشد.
۳- تعداد از روزهای تعطیلی تصادفی (نام ها و تاریخ ها) را در ستون های F و G وارد کنید.
۴- نشانگر ماوس خود را بر روی سلول C4 قرار دهید و به سربرگ Formula از اکسل رفته و گزینه Data & Time را باز کنید و تابع NETWORKDAYS.INTL را از این منو انتخاب کنید.
۵- در جعبه محاوره ای Function Arguments، در داخل جعبه فیلد Start_Date کلیک کنید، سپس بر روی A4 کلیک کنید.
۶- کلید Tab را بر روی جعبه فیلد End_Date نگه دارید و با استفاده از ماوس خود بر روی سلول A4 کلیک کنید.
۷- کلید Tab را بر روی فیلد Weekend نگه دارید و یکی از کدهای تعریف شده آخر هفته را در آن وارد کنید (به یاد داشته باشید که عدد ۱ به معنی شنبه و یکشنبه است).
۸٫ بر روی فیلد Holidays کلید Tab را فشار داده و نگه دارید، سپس سلول های G4 تا G11 را انتخاب یا برجسته نمایید و سپس بر روی OK کلیک کنید.
قبل از این که این فرمول را از سلول C4 به داخل سلول های C5 تا C11 کپی کنید، از کلید تابعی F4 استفاده کنید تا سلول های Holiday را مثبت کنید: دستور

= NETWORKDAYS.INTL (A4,B4,1,$G$4:$G$11)

را وارد کنید تا روزهای موجود در Holidays همیشه از G4 تا G11 تغییر کنند.

نظر بدهید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

It is main inner container footer text