نویسنده
درباره نویسنده
بهنام خیرالهی
تعداد پست ها 64

درباره:

دانش آموخته مقطع کارشناسی دانشگاه Staffordshire University در رشته علوم کامپیوتر گرایش امنیت و مقطع کارشناسی دانشگاه Asia Pacific University در رشته فناوری اطلاعات هستم. از سال 1392 به ایران برگشتم و به صورت تخصصی در زمینه طراحی وب، طراحی گرافیک و برنامه نویسی NET. فعالیت دارم.

  • بکارگیری تاریخ شمسی

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

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

با استفاده از قالب تاریخ شمسی در اکسل 2016 می‌توانیم ظاهر تاریخ میلادی را به تاریخ شمسی تبدیل کنیم، در این روش فقط قالب سلول تغییر کرده و مقدار اصلی تاریخ میلادی می‌باشد. این بدین معنی است که محاسبات انجام شده بر اساس تاریخ میلادی صورت می‌گیرد.

فرض کنید می‌خواهیم در یک سلول مقداری را بر مبنای تاریخ شمسی وارد کنیم، برای این‌کار سلول مورد نظر را انتخاب کرده و به مسیر زیر می‌رویم.

  • Home
  • Number
  • Number Format
  • Date
  1. در قسمت Local (location) گزینه Persian (Iran) را انتخاب می‌کنیم.
  2. در قسمت Calendar type گزینه Persian را انتخاب می‌کنیم.
  3. در قسمت Type الگوی مورد نظر را انتخاب می‌کنیم.

به طور پیش‌فرض باید مقدار سلول را بر مبنای تاریخ میلادی وارد کرده تا اکسل آن را به تاریخ شمسی تبدیل کند. اما اگر گزینه Input dates according to selected calender را فعال کنیم، می‌توانیم مقدار سلول را بر مبنای تاریخ شمسی وارد کنیم.

قالب تاریخ شمسی در اکسل 2016

قالب تاریخ شمسی در اکسل 2016

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

28/05/1363

تاریخ شمسی در اکسل 2016

تاریخ شمسی در اکسل 2016

روش دوم: درج تاریخ به صورت عدد

می‌توانیم با وارد کردن تاریخ به صورت عدد و با استفاده از چند فرمول محاسبات مختلفی را به روی تاریخ شمسی انجام دهیم. ایجاد / بین اعداد باعث می‌شود که آنها خاصیت عددی خود را از دست داده و تبدیل به رشته شوند. برای اینکه بتوانیم محاسبات را به روی تاریخ‌های شمسی انجام دهیم باید آنها را به صورت عدد وارد کنیم. مثلا تاریخ مورد نظر را به صورت زیر در یک سلول وارد می‌کنیم.

13630528

سلول مربوطه را انتخاب کرده و به مسیر زیر می‌رویم. در قسمت Type کد 0000"/"00"/"00 را وارد کرده و به روی دکمه Ok کلیک می‌کنیم. با انجام این‌کار تاریخ درج شده به صورت 1363/05/28 نشان داده می‌شود.

  • Home
  • Number
  • Format Cells
  • Custom

تنظیم نمایش عدد به صورت تاریخ

تنظیم نمایش عدد به صورت تاریخ

قالب بندی اعداد فقط نحوه نمایش عدد درج شده در سلول را تغییر می‌دهد و محتوای سلول همان عدد باقی می‌ماند.

روش محاسبه

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

وضعیت کارکرد بین دو بازه زمانی

وضعیت کارکرد بین دو بازه زمانی

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

می‌خواهیم فاصله زمانی بین اولین تاریخ تا آخرین تاریخ را محاسبه کنیم. در یک سلول به دلخواه تابع Countifs را به صورت زیر تعریف می‌کنیم. این فرمول اعدادی که بزرگتر یا مساوی 13970924 و کوچکتر یا مساوی 13971006 هستند را شمارش کرده و عدد 13 را به عنوان نتیجه برمی‌گرداند.

=COUNTIFS(A2:A14,">=13970924",A2:A14,"<=13971006")
مثال دوم: محاسبه ماموریت‌های بین دو بازه تاریخی

می‌خواهیم تعداد ماموریت‌های بین اولین تاریخ تا آخرین تاریخ را محاسبه کنیم. در یک سلول به دلخواه تابع Countifs را به صورت زیر تعریف می‌کنیم. این فرمول اعدادی که بزرگتر یا مساوی 13970924 و کوچکتر یا مساوی 13971006 هستند، به شرط اینکه سلول مجاور آنها عبارت ماموریت باشد را شمارش کرده و عدد 3 را به عنوان نتیجه برمی‌گرداند.

=COUNTIFS(A2:A14,">=13970924",A2:A14,"<=13971006",B2:B14,"ماموریت")

روش سوم: برنامه‌نویسی VBA

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

نصب افزونه

برای نصب این افزونه به مسیر رفته و به روی دکمه Browse کلیک می‌کنیم. افزونه را انتخاب کرده و به روی دکمه Ok کلیک می‌کنیم. با انجام این‌کار یک سری توابع که در دسته User Defined قرار دارند به اکسل اضافه می‌شوند.

  • Options
  • Add-ins
  • Manage
  • Excel Add-ins
  • Go

نصب افزونه توسط Add-ins

نصب افزونه توسط Add-ins

جابه‌جا کردن فایل‌های دارای Add-ins باعث می‌شود که این فایل‌ها عملکرد درستی نداشته و از لیست افزونه‌های اکسل حذف شوند. برای رفع این مشکل در صورت جابه‌جایی فایل، می‌توانیم با استفاده از ویژگی Drag & Drop کدهای موجود در افزونه را به فایل اصلی انتقال دهیم.

توابع تاریخ شمسی

تابع m2s

تابع m2s جزء توابع User Defined می‌باشد که یک تاریخ میلادی را به تاریخ شمسی تبدیل می‌کند.

ساختار تابع

M2S(mydate,format)
آرگومانشرح
mydateاختصاص این آرگومان ضروری بوده و می‌تواند شامل تاریخ یا آدرس سلول باشد.
formatاختصاص این آرگومان اختیاری بوده و می‌توانند شامل عدد باشد.

آرگومان‌های تابع m2s

مقدارشرح
0روز، ماه و سال مربوط به تاریخ شمسی را با کاراکتر / از یکدیگر جدا کرده و آن را به عنوان نتیجه برمی‌گرداند.
1تاریخ شمسی را به همراه روز هفته به عنوان نتیجه برمی‌گرداند.
2مقدار تاریخ شمسی را به حروف تبدیل کرده و آن را به عنوان نتیجه برمی‌گرداند.
3سال مربوط به تاریخ شمسی را به عنوان نتیجه برمی‌گرداند.
4ماه مربوط به تاریخ شمسی را به عنوان نتیجه برمی‌گرداند.
5روز مربوط به تاریخ شمسی را به عنوان نتیجه برمی‌گرداند.

مقادیر آرگومان format در تابع m2s

ویژگی‌های تابع

  1. تاریخی که مستقیما در آرگومان mydate تعریف می‌شود باید بین "" قرار بگیرد.
  2. پیش‌فرض آرگومان format عدد صفر می‌باشد.
مثال

تابع زیر تاریخ جاری سیستم را تبدیل به تاریخ شمسی کرده و مقدار 1397/10/6 را به عنوان نتیجه برمی‌گرداند.

=M2S(TODAY())

تابع s2m

تابع s2m جزء توابع User Defined می‌باشد که یک تاریخ شمسی را به تاریخ میلادی تبدیل می‌کند.

ساختار تابع

S2M(mydate)
آرگومانشرح
mydateاختصاص این آرگومان ضروری بوده و می‌تواند شامل تاریخ یا آدرس سلول باشد.

آرگومان‌های تابع s2m

ویژگی‌های تابع

  1. تاریخی که مستقیما در آرگومان mydate تعریف می‌شود باید بین "" قرار بگیرد.
مثال

تابع زیر تاریخ 1397/10/6 را تبدیل به تاریخ میلادی کرده و مقدار 2018/12/27 را به عنوان نتیجه برمی‌گرداند.

=S2M("1397/10/6")

روش چهارم: ترکیب توابع اکسل

روشی دیگر برای کار کردن با تاریخ شمسی استفاده از تاریخ میلادی و برخی از توابع موجود در اکسل و در نهایت تبدیل نتیجه آن به تاریخ شمسی می‌باشد. برای این‌کار باید از ترکیب فرمول‌های مختلفی مثل توابع منطقی، ریاضی و رشته‌ای استفاده کنیم.

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

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

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

=IF(MOD(VALUE(LEFT(A2,4)),4)=0,(VALUE(LEFT(A2,4))-1)*365+(IF((VALUE(MID(A2,6,2))-1)<7,(VALUE(MID(A2,6,2))-1)*31,IF((VALUE(MID(A2,6,2))-1)>6,(VALUE(MID(A2,6,2))-1)*30+6)))+VALUE(RIGHT(A2,2))+INT((VALUE(LEFT(A2,4))-1)/4)+1,(VALUE(LEFT(A2,4))-1)*365+(IF((VALUE(MID(A2,6,2))-1)<7,(VALUE(MID(A2,6,2))-1)*31,IF((VALUE(MID(A2,6,2))-1)>6,(VALUE(MID(A2,6,2))-1)*30+6)))+VALUE(RIGHT(A2,2))+INT((VALUE(LEFT(A2,4))-1)/4))-466710

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

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

=CONCATENATE(INT((A2-7385)/365.25+1299),"/",IF(MOD(IF(INT(MOD((A2-7385)*100,36525)/100)<186,INT(INT(MOD((A2-7385)*100,36525)/100)/31),IF(MOD(INT((A2-7385)/365.25),4)=0,INT((INT(MOD((A2-7385)*100,36525)/100)-186)/30)+6,IF(INT(MOD((A2-7385)*100,36525)/100)<336,INT((INT(MOD((A2-7385)*100,36525)/100)-186)/30)+6,INT((INT(MOD((A2-7385)*100,36525)/100)-336)/29)+11))),12)+1<10,CONCATENATE(0,MOD(IF(INT(MOD((A2-7385)*100,36525)/100)<186,INT(INT(MOD((A2-7385)*100,36525)/100)/31),IF(MOD(INT((A2-7385)/365.25),4)=0,INT((INT(MOD((A2-7385)*100,36525)/100)-186)/30)+6,IF(INT(MOD((A2-7385)*100,36525)/100)<336,INT((INT(MOD((A2-7385)*100,36525)/100)-186)/30)+6,INT((INT(MOD((A2-7385)*100,36525)/100)-336)/29)+11))),12)+1),MOD(IF(INT(MOD((A2-7385)*100,36525)/100)<186,INT(INT(MOD((A2-7385)*100,36525)/100)/31),IF(MOD(INT((A2-7385)/365.25),4)=0,INT((INT(MOD((A2-7385)*100,36525)/100)-186)/30)+6,IF(INT(MOD((A2-7385)*100,36525)/100)<336,INT((INT(MOD((A2-7385)*100,36525)/100)-186)/30)+6,INT((INT(MOD((A2-7385)*100,36525)/100)-336)/29)+11))),12)+1),"/",IF(IF(INT(MOD((A2-7385)*100,36525)/100)<186,MOD(INT(MOD((A2-7385)*100,36525)/100),31)+1,IF(MOD(INT((A2-7385)/365.25),4)=0,MOD(INT(MOD((A2-7385)*100,36525)/100)-186,30)+1,IF(INT(MOD((A2-7385)*100,36525)/100)<336,MOD(INT(MOD((A2-7385)*100,36525)/100)-186,30)+1,MOD(INT(MOD((A2-7385)*100,36525)/100)-336,29)+1)))<10,CONCATENATE(0,IF(INT(MOD((A2-7385)*100,36525)/100)<186,MOD(INT(MOD((A2-7385)*100,36525)/100),31)+1,IF(MOD(INT((A2-7385)/365.25),4)=0,MOD(INT(MOD((A2-7385)*100,36525)/100)-186,30)+1,IF(INT(MOD((A2-7385)*100,36525)/100)<336,MOD(INT(MOD((A2-7385)*100,36525)/100)-186,30)+1,MOD(INT(MOD((A2-7385)*100,36525)/100)-336,29)+1)))),IF(INT(MOD((A2-7385)*100,36525)/100)<186,MOD(INT(MOD((A2-7385)*100,36525)/100),31)+1,IF(MOD(INT((A2-7385)/365.25),4)=0,MOD(INT(MOD((A2-7385)*100,36525)/100)-186,30)+1,IF(INT(MOD((A2-7385)*100,36525)/100)<336,MOD(INT(MOD((A2-7385)*100,36525)/100)-186,30)+1,MOD(INT(MOD((A2-7385)*100,36525)/100)-336,29)+1)))))

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

دریافت فایل

رمز فایل: www.w3blog.ir