محاسبات در یک بازه تاریخی

کار کردن با تاریخ در اکسل

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

1. تاریخ میلادی است یا شمسی؟
2. اگر شمسی است از چه جنسی است؟

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

محاسبه اختلاف دو تاریخ میلادی

وقتی میخوایم دو تاریخ میلادی رو از هم کم کنیم، با توجه به اینکه میدونیم تاریخ میلادی از جنس عدد هست، فقط کافیه که این دو تاریخ رو مثل دو تا عدد از هم کم کنیم. مثلا میخواهیم ببینیم مجموع فروش بین دو تاریخ ۰۲/۰۵/۲۰۱۹ و ۰۴/۱۳/۲۰۱۹ چقدر بوده؟روش حل این مسئله دقیقا مثل مفهوم اینه که: مجموع فروش محصولاتی که بین ۱۰۰ تا ۲۰۰ فروش رفتن چقدر هست. برای حل همچین مسئله ای چکار میکردیم؟ میومدیم شرط های فرمول رو >۱۰۰ و <۲۰۰ میذاشتیم و محاسبات رو انجام میدیم. حالا بجای ۱۰۰ و ۲۰۰ تاریخ های مورد نظر رو میذاریم. چرا که میدونیم تاریخ میلادی عددی است که به فرمت تاریخ نمایش داده میشه.

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

همونطور که در شکل ۱ نمایش داده شده سلول های قرمز رنگ، مقادیری هستند که در بازه تاریخ مشخص شده فروش رفته اند و جمع این اعداد برابر است با ۱۹۳۰۰۰.

محاسبه اختلاف دو تاریخ شمسی- حالت اول

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

محاسبه اختلاف دو تاریخ شمسی در نسخه ۲۰۱۶ به بعد

همونطور که میدونیم از ورژن ۲۰۱۶ اکسل به بعد، نمایش تاریخ شمسی پشتیبانی میشه. یعنی تاریخ رو ثبت میکنیم و میتونیم بصورت شمسی ببینیمش. اما منطق و جنس تاریخ همچنان میلادی هست (یعنی همه مسائلی که برای تاریخ میلادی قبلا توضیح دادیم صادقه).پس مسئله بالا رو با تاریخ شمسی در ورژن ۲۰۱۶ به بعد حل میکنیم.برای این کار کافیه که سلول های تاریخ رو انتخاب کنیم و فرمت اونها رو به فرمت تاریخ شمسی تغییر بدیم:

شکل ۲ – تغییر فرمت تاریخ میلادی به شمسی از نسخه ۲۰۱۶ به بعد

با این کار همه سلول های شامل تاریخ به صورت شمسی نمایش داده میشه ولی چون ماهیت اونها تاریخ میلادی هست، محاسبات به همون شیوه قبلی انجام میشه.

شکل ۳ – انجام محاسبات برای تاریخ شمسی در اکسل ۲۰۱۶

 

شکل ۴- محاسبه فاصله بین دو تاریخ شمسی و نحوه استفاده از تاریخ داخل فرمول

محاسبه اختلاف دو تاریخ شمسی در نسخه های قبل از ۲۰۱۶

اگر با نسخه ماقبل از ۲۰۱۶ سر و کار داشته باشید، به دلیل اینکه تاریخ شمسی در این نسخه ها به صورت یک متن شناخته میشوند تا تاریخ و این مشکل بزرگی در کار با تاریخ شمسی هست. پیشنهاد میکنیم از این روش استفاده کنید:قبلا در تشریح انواع حالت های تاریخ شمسی، توضیح دادیم که میتونیم تاریخ شمسی رو بصورت یک عدد هشت رقمی (۴ رقم سال، دو رقم ماه و دو رقم روز) در نظر بگیریم و ظاهر رو از طریق فرمت سل به فرمت تاریخ نمایش بدیم. د رادامه این روش رو شرح میدهیم:تاریخ ها رو بصورت یک کد هشت رقمی تایپ میکنیم مثل ۱۳۹۹۰۵۰۵ و برای اینکه ظاهر تاریخ داشته باشه از طریق فرمت سل کد زیر رو در قسمت Custom وارد میکنیم.

0000″/’00″/”00

شکل ۵ – نمونه ای از کار با تاریخ شمسی در اکسل در ورژن های قبل از  ۲۰۱۶- تغییر ظاهر

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

شکل ۶ – نمونه محاسبه اختلاف دو تاریخ شمسی در ورژن های قبل از ۲۰۱۶

برای آنکه بتونیم تاریخ رو داخل خود فرمول هم بکار ببریم، باید بدون / و عینا همون کد هشت رقمی رو ثبت کنیم. یعنی:

=SUMIFS ( B2:B13 , A2:A13 , “>13981116” , A2:A13 , “<13990124” )

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

دانلود فایل این آموزش

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