سبد خرید
0

سبد خرید شما خالی است.

تمام دسته ها
  • تمام دسته ها
  • Power BI
  • Power Pivot
  • Power Query
  • ابزارها
  • افزونه ها
  • اکسل مدیا
  • توابع اکسل
  • دسته‌بندی نشده
  • دیده بان
  • گوگل شیت
  • مسائل کاربردی
  • معرفی کتاب
  • نمودار ها
  • وی بی - ماکرو

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

اختلاف دو تاریخ شمسی
۴.۵/۵ - (۴ امتیاز)

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

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

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

سوال اول: تاریخ شمسی است یا میلادی

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

سوال دوم: اگر شمسی است از چه جنسی است ؟

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

در ادامه یکی از مهم ترین مسائل کار با تاریخ یعنی محاسبات مربوط بین دو تاریخ رو انجام میدیم:

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

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

روش حل این مسئله دقیقا مثل مفهوم اینه که: مجموع فروش محصولاتی که بین ۱۰۰ تا ۲۰۰ فروش رفتن چقدر هست. برای حل همچین مسئله ای چکار میکردیم؟ میومدیم شرط های فرمول رو >100 و <200 میذاشتیم و محاسبات رو انجام میدیم. حالا بجای ۱۰۰ و ۲۰۰ تاریخ های مورد نظر رو میذاریم. چرا که میدونیم تاریخ میلادی عددی است که به فرمت تاریخ نمایش داده میشه.

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

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

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

نکته:
راجع به تابع SUMIFS و نکات مربوط به نحوه تخصیص شرط، مقاله مربوط به جمع شرطی رو مطالعه کنید.

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

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

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

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

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

پس مسئله بالا رو با تاریخ شمسی در ورژن ۲۰۱۶ به بعد حل میکنیم.

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

تغییر فرمت تاریخ میلادی به شمسی از نسخه 2016 به بعد

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

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

محاسبه در اکسل 2016

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

نکته مهم:
اگر شرط های فرمول رو از سلول انتخاب کنیم، همین روش درسته و نیازی به انجام کار خاصی نیست. اما اگر بخوایم شرط ها رو داخل فرمول بنویسیم، باید از معادل میلادی تاریخ استفاده کنیم. یعنی اگر فرمول رو به این شکل بنویسیم، غلطه و محاسبات انجام نمیشه. چرا؟ چون جنس تاریخ میلادی است و شرط به این شکل، متن در نظر گرفته میشه:
SUMIFS (B2:B13, A2:A13 , “>11/16/1397” , A2:A13 , “<1/24/1398”)
برای اینکه بتونیم شرط رو داخل فرمول داشته باشیم، باید از معادل میلادی اون در فرمول استفاده کنیم. بصورت زیر:
SUMIFS ( B2:B13 , A2:A13 , “>2/5/2019” , A2:A13 , “<4/13/2019”)

 

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

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

Persian-Calendar-Icon

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

مشاهده افزونه تقویم شمسی در اکسل

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

اگر با نسخه ماقبل از ۲۰۱۶ سر و کار داشته باشید، به دلیل اینکه تاریخ شمسی در این نسخه ها به صورت یک متن شناخته میشوند تا تاریخ و این مشکل بزرگی در کار با تاریخ شمسی هست. پیشنهاد میکنیم از این روش استفاده کنید:

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

تاریخ ها رو بصورت یک کد هشت رقمی تایپ میکنیم مثل ۱۳۹۹۰۵۰۵ و برای اینکه ظاهر تاریخ داشته باشه از طریق فرمت سل کد زیر رو در قسمت Custom وارد میکنیم.

۰۰۰۰″/’۰۰″/”۰۰

نمونه ای در ورژن های قبل از  2016- تغییر ظاهر

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

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

انجام این محاسبه در نسخه های قبل از 2016

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

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

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

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

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

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

 

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

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

کلیدواژه : تابع SUMIFS
آواتار
127

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

دیدگاه کاربران
  • سروش ۲۲ تیر ۱۴۰۱ / ۱:۲۶ ب٫ظ

    ببخشید اگر بخواهیم اختلاف دوتا تاریخ شمسی رو بدست بیاریم وقتی تاریخ رو روی فارسی بزاریم و دوتا رو از هم منها کنیم بازم ارور value میده

    • آواتار
      حسنا خاکزاد ۲۲ تیر ۱۴۰۱ / ۳:۴۸ ب٫ظ

      درود
      جنس تاریخ مهمه
      صرف تغییر فرمت از روی فرمت سل اتفاق نمیفته
      باید قابل محاسبه باشه
      این مقاله رو بخونید
      https://excelpedia.net/excel-date-function/

  • داریوش ایزدی ۱۲ بهمن ۱۴۰۰ / ۹:۴۶ ق٫ظ

    باسلام من کالایی دارم که در صورت میانگین وزن۲ کیلو کیلویی۱۰۰۰۰۰ریال هرکیلو محاسبه میشود ولی در صورتی که میانگین از ۲ کیلو به ۲/۵رسید به ازاءهر۱۰۰گرم اضافه مبلغ۱۰۰۰ریال به قیمت پایه اضافه می شود ویا برعکس درصورتی که زیر دوکیلو شد به ازا هر ۱۰۰گرم ۱۰۰۰ریال از فی پایه کسر میگردد حال میخواستم در اکسل فرمولی اتخاذ کنم که به محض اینکه میانگین محاسبه شد به صورت خود کار فی فروش مشخص شود من را راهنمایی کنید یااینکه زحمت فرمول را برایم بکشید متشکرم

    • آواتار
      حسنا خاکزاد ۱۲ بهمن ۱۴۰۰ / ۴:۳۲ ب٫ظ

      درود بر شما
      بر فرض اینکه در سلول F2 میانگین وزن نوشته بشه، این فرمول رو نوشتم:

      مبلغ پایه ۲۰۰۰۰۰ برای دو کیلو هست. بعد میانگین محاسبه شده منهای ۲ میشه، اگه بیشتر باشه، مثبت در غیر اینصورت منفی خواهد بود.
      حالا مثلا ۲.۵-۲ میشه ۰.۵ که در ۱۰ ضرب میشه میشه ۵ یعنی ۵ تا صد گرم. که به ازای هر صد گرم، ۱۰۰۰ تومن هست.

  • مسعود ۱۱ اسفند ۱۳۹۹ / ۴:۱۳ ب٫ظ

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

    • آواتار
      حسنا خاکزاد ۱۲ اسفند ۱۳۹۹ / ۱۱:۴۵ ق٫ظ

      درود بر شما
      اشکال رو بفرمایید لطفا تا اصلاح بشه ممنون از توجهتون

  • علاقه مند به اکسل ۲۸ بهمن ۱۳۹۹ / ۱۲:۲۱ ب٫ظ

    سلام
    میخوام فرمولی برای یک سلول بنویسم که مقدار آن به صورت خودکار بعد از ۶ روز که اکسل رو باز می کنم ۲۵۰ تا اضافه بشه. اما برای دفعات بعد که اکسل رو باز می کنم، دوباره به آن اضافه نشود.
    بنابراین میخوام فرمولی برای این سلول بنویسم که فقط یکبار و آن هم بعد از اینکه ۶ روز گذشت، اضافه بشه.

    • آواتار
      حسنا خاکزاد ۲۸ بهمن ۱۳۹۹ / ۱:۱۱ ب٫ظ

      درود
      این قبیل کارها رو با وی بی باید انجام بدید

  • امین ۲۵ بهمن ۱۳۹۹ / ۰:۴۱ ق٫ظ

    نحو دانلود فایل عوض کنید فایل اکسل در موبایل بچه در من میخوره

    • سامان چراغی ۲۵ بهمن ۱۳۹۹ / ۸:۵۴ ق٫ظ

      سلام
      روی لپ تاپ یا کامپیوتر هم میتونید دانلود کنید.

  • وحید ۳۰ دی ۱۳۹۹ / ۱۱:۱۵ ق٫ظ

    سلام من با اکسل ۲۰۱۰ کار می کنم و میخواهم اکسل برای من محاسبه کند که تعداد روز تاریخ های شمسی که در دو سلول مختلف وارد کرده ام محاسبه شود . توضیحات مقاله فوق را مطالعه کردم و افزونه جلالی را هم نصب کرده ام ولی موفق به انجام این کار نشدم لطفا راهنمایی فرمایید. با تشکر

  • سجاد ۱۰ دی ۱۳۹۹ / ۱۲:۵۸ ب٫ظ

    سلام وقت بخیر
    صورت مسیه به این صورته که یه تاریخ میلادی نامه داریم که در مقابلش پاسخ این نامه و تاریخش میاد حداکثر زمان برای پاسخ هم ۱۴ روزه،الان فرمولی که نوشتم شرط ۱۴ روزه رو میده که اگه بزرگتر از ۰ بود رنگ سلول رو قرمز کنه ولی میخوام یه رنگ نارنجی هم داشته باشم که اگه ۱۰ روز رو رد کرد رنگ سلول رو نارنجی کنه.
    ممنون میشم تو این مورد راهنمایی کنید.

    • آواتار
      حسنا خاکزاد ۱۵ دی ۱۳۹۹ / ۱۲:۱۰ ب٫ظ

      درود
      روی همون شرط، یک شرط دیگه اضافه کنید برای نارنجی. در conditional formating/ manage rule/new rule
      مشکلی نیست برای اضافه کردن شرط

  • Zaman ۶ دی ۱۳۹۹ / ۴:۲۸ ب٫ظ

    با سلام و خسته نباشید
    می خواهم فاصله بین دو مانده مرخصی را از هم کسر یا با هم جمع کنم مثلا ۱۸/۳/۱۵ یعنی ۱۸ روز و ۳ ساعت و ۱۵ دقیقه منهای ۲۰/۲/۴۵ در واقع به دست آوردن مرخصی های استفاده شده
    ممنون می شم کمک کنید

    • آواتار
      حسنا خاکزاد ۶ دی ۱۳۹۹ / ۵:۳۴ ب٫ظ

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

      • Zaman ۷ دی ۱۳۹۹ / ۷:۵۷ ق٫ظ

        سپاس

  • تسنیم ۱۷ آبان ۱۳۹۹ / ۱۰:۰۹ ق٫ظ

    سلام
    میخواهم یک ساعت مشخص از یک روز (تاریخ به شمسی باشد حتما) را به مدت های مختلفی اضافه کنم و تاریخ جدید را بدست بیاورم
    مثلا ساعت شروع فعالیت ۲۱ روز ۱۱ اردیبهشت ۹۹ باشد که با ۶۱ ساعت جمع شود ساعت پایان و تاریخ پایان را به دست بیاورم؟ میشه راهنمایی کنید
    متشکرم

  • حسین ۶ مهر ۱۳۹۹ / ۱۱:۲۹ ق٫ظ

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

    • سامان چراغی ۸ مهر ۱۳۹۹ / ۸:۵۵ ق٫ظ

      سلام
      میتونید با استفاده از افزونه های تبدیل تاریخ شمسی به میلادی ابتدا تاریخ ها رو به تاریخ میلادی تبدیل کنید و ساعت های مربوطه رو به انتهای هر تاریخ اضافه کنید. نهایتا در سلول دیگر این دو سلول تاریخ رو از هم کم کنید.

  • احسان کوهی ۳۰ شهریور ۱۳۹۹ / ۱۱:۳۰ ب٫ظ

    سلام چطور میتونم اختلاف روز دو تا تاریخ مثلا ۱۳۹۹/٠۳/۲۵ با ۱۴۰۰/٠۹/۲۵ رو در اکسل محاسبه کنم، خیلی ضروریه

    • سامان چراغی ۶ مهر ۱۳۹۹ / ۸:۵۴ ق٫ظ

      سلام
      کل این مطلب برای انجام همین کار هست. لطفا یکبار دیگه متن رو دقیق بخونید.

  • nima ۳۱ مرداد ۱۳۹۹ / ۱۱:۳۹ ب٫ظ

    سلام من میخوام بازه زمانی بین یک ساعت در یک تاریخ را با ساعت مشخص دیگه از یه تاریخ دیگه مثلا سه روز بعد بر حسب ساعت بدست بیارم لطفا راهنمایی کنید.
    مثلا ساعت ۱۸:۱۰ تاریخ ۱۳۹۹/۵/۲۰ با ساعت ۹:۲۰ تاریخ ۱۳۹۹/۵/۲۵
    با تبدیل تاریخ به میلادی مشکلی ندارم اگر میلادی هم بشه حسابشون کرد کارمو راه میندازه. ممنون

    • آواتار
      حسنا خاکزاد ۱ شهریور ۱۳۹۹ / ۱۱:۰۵ ق٫ظ

      درود
      اگر تاریخ میلادی باشه و هر دو با هم در یک سلول باشن، مثلا : ۲۰۲۰/۰۸/۲۲ ۸:۱۵ ، کافیه که سلول ها رو از هم کم کنید. چون مقدار عددیش محاسبه میشه
      اما اگر سلول ها جدا باشه، باید با ترکیب If این اختلاف ها رو حساب کنید مثلا در مثال خودتون روز بیست مرداد رو از ساعت ۱۸ تا ۱۲ شب رو حساب کنید. بعد اختلاف تاریخ ها و بعد باز با ساعت روز اخر جمع ککنید.
      در واقع مسئله اکسلی خاصی نیست، ریاضیش رو باید درک کنید که بتونید محاسبات انجام بدید.
      برای درک زمان و تاریخ، این دو مقاله رو بخونید

  • صادق ۲۳ مرداد ۱۳۹۹ / ۱۰:۰۴ ق٫ظ

    با عرض سلام خدا قوت
    چند لیست حضور و غیاب حدودا ۲۵۰ نفره دارم که در حین کار خیلی کند میشه و همش اخطار not respondig میده
    چطوری مشکل کنم
    نسخه اکسل۲۰۱۳ می باشد
    باتشکر

    • آواتار
      حسنا خاکزاد ۲۳ مرداد ۱۳۹۹ / ۸:۵۳ ب٫ظ

      درود
      ۲۵۰ نفر که داده زیادی نیست
      میتونید مقاله کا با فایل های سنگین رو بخونید و ببینید میتونید فایل رو بهینه کنید یا نه
      اما در کل بعضی نسخه های ورژن ۲۰۱۳ زیاد هنگ میکنن. شاید لازم باشه عوض کنید نرم افزار رو

      • Farshad ۱۲ دی ۱۳۹۹ / ۱:۴۱ ق٫ظ

        سلام و درود برشما
        میخاستم تعداد کارکرد برای یک نفر را در یک ماه حساب کنم…تابع ifبرای تاریخش رو باید چه جوری نوشت؟
        مثلاسوال اینجوری باشه که تعداد کارکرد علی را حساب کنید(بازه یک ماه)
        تو ستون اول تاریخ باشه،ستون دوم نام باشه و ستون سوم برای مثال میزان تولید
        امیدوارم تونسته باشم منظورمو برسونم
        ممنون میشم پاسخ بدید

        • آواتار
          حسنا خاکزاد ۱۵ دی ۱۳۹۹ / ۱۲:۱۵ ب٫ظ

          درود
          متاسفانه اصلا واضح نیست
          ضمن اینکه شما با ترکیب توابع if و با منطق تاریخ میتونید حل کنید این مسئله رو
          مقالات متبط رو داخل سایت مطالعه کنید

ارسال دیدگاه

نشانی ایمیل شما منتشر نخواهد شد.

توسط
تومان