سلام دوستان عزیز وقتتون بخیر؛ آرزوی اوقات خوشی رو براتون داریم 🙂 در این جلسه از سری آموزش های اکسل در خدمتتون هستیم برای تشخیص کبیسه بودن سال در یک تاریخ شمسی.
بررسی کبیسه بودن سال یک تاریخ شمسی در اکسل
اولین نکته ای که لازم به ذکره این هست که برای تشخیص کبیسه بودن سال یک تاریخ شمسی در اکسل باید از افزونه تقویم و تاریخ شمسی پویش پایدار استفاده کرد. در این افزونه تابعی به نام IsKabiseh وجود داره که یک آرگومان ورودی داره و اون هم تاریخ شمسی مورد نظر هست. ما یک تاریخ شمسی رو به این تابع میدیم و در صورت کبیسه بودن سال اون تاریخ، خروجی تابع برابر True و در غیر این صورت False هست.
جدول زیر رو در نظر بگیرید؛ در ستون اول این جدول تاریخ قراردادها به صورت شمسی و در ستون دوم تعرفه روزانه برای هر قرارداد درج شده و میخواهیم در ستون سوم، پرداختی تا انتهای ماه توسط فرمول برای ما محاسبه بشه.
جدولی از داده شامل تاریخ های کبیسه
حالت های مختلف یک تاریخ شمسی در مثال
بنابراین برای هر تاریخ باید تعداد روز مانده تا انتهای ماه محاسبه بشه. فرضا برای تاریخ اول یعنی 1396/02/10، تعداد روز مانده تا انتهای ماه (31 اردیبهشت) برابر با 21 روز هست. برای محاسبه این مورد سه حالت وجود داره:
1) تاریخ قرارداد در 6 ماهه اول سال هست.
2) تاریخ قرارداد در 6 ماهه دوم سال به جز اسفند هست.
3) تاریخ قرارداد در ماه اسفند هست.
مورد سوم خودش دو حالت داره:
- اسفند ماه 29 روزه هست.
- اسفند ماه 30 روزه هست (سال کبیسه).
بنابراین برای هر تاریخ ابتدا باید قسمت ماه رو جدا کنیم تا بررسی بشه که در کدوم نیمه از سال قرار گرفته. برای این کار باید از تابع ShamsiMonth در افزونه تقویم و تاریخ شمسی پویش پایدار استفاده کنیم.
فرمول مورد نظر به صورت تصویر زیر هست که در ادامه تشریح کردیم:
فرمول تشخیص سال کبیسه در اکسل
تشریح فرمول تشخیص سال کبیسه در اکسل
در فرمول بالا با استفاده از ShamsiMonth(A3) قسمت ماه تاریخ را جدا کردیم.
داخل این فرمول از سه if تو در تو استفاده شده است.
در if اول بررسی کرده ایم که آیا قسمت ماه تاریخ شمسی کوچکتر یا مساوی 6 هست یا نه (تاریخ در 6 ماهه اول قرار دارد یا نه). اگر این شرط صحیح باشد، برای به دست آوردن فاصله تاریخ تا انتهای ماه از فرمول 31-ShamsiDay(A3) استفاده میشود و اگر صحیح نباشد، در واقع تاریخ موجود در سلول A3 در 6 ماهه دوم قرار دارد. 6 ماهه دوم خودش دو حالت دارد. یا تاریخ در اسفند ماه هست یا ماه های دیگر. بنابراین به یک if دیگر نیاز داریم.
در if دوم بررسی کردیم که آیا ShamsiMonth(A3) برابر 12 هست یا نه. اگر شرط برقرار باشد، یعنی تاریخ در اسفند ماه است و اسفند خودش دو حالت دارد؛ 29 روزه یا 30 روزه. پس لازم است تا با یک if دیگر کبیسه بودن یا نبودن سال تاریخ را بررسی کنیم.
در if سوم با استفاده از فرمول IsKabiseh(A3) بررسی کردیم که آیا تاریخ در سال کبیسه هست یا نه. اگر شرط برقرار باشد، برای به دست آوردن فاصله تاریخ تا انتهای ماه از فرمول 30-ShamsiDay(A3) استفاده میشود و اگر نه از فرمول ShamsiMonth(A3)-29.
در نهایت اگر هیچ یک از این حالات نبود، یعنی تاریخ در 6 ماهه دوم به جز اسفند قرار دارد که برای به دست آوردن فاصله تاریخ تا انتهای ماه از فرمول 30-ShamsiMonth(A3) استفاده می کنیم.
پس از به دست آمدن فاصله تاریخ تا انتهای ماه از طریق if های تو در تو، نتیجه را در سلول B3 (تعرفه قرارداد) ضرب می کنیم و پرداختی تا انتهای ماه به دست می آید.
در آخر فرمول را برای سایر تاریخ ها به سمت پایین اتوفیل میکنیم.
ممنون از توجه شما 🙂
هر گونه سوال، پیشنهاد یا انتقادی در رابطه با دوره رایگان آموزش اکسل دارید در بخش دیدگاه‌ها مطرح نمایید تا بتوانیم بهترین آموزش ها را برای شما علاقمندان به آموزش اکسل تهیه کنیم.
