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

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

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

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

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

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

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

تبدیل تاریخ میلادی به شمسی در اکسل به کمک قالب‌بندی سلول

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

کاربرگ زیر را در اکسل در نظر بگیرید. ستون A شامل مقادیری است که به صورت تاریخ میلادی ثبت شده‌اند. می‌خواهیم معادل شمسی هر یک از این تاریخ‌ها را در ستون دیگری مانند B نمایش دهیم.

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

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

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

قالب بندی برای تبدیل تاریخ میلادی به شمسی
پنجره قالب بندی برای تبدیل تاریخ میلادی به شمسی

حال کافی است به کمک فرمول B1: = A1 مقدار سلول‌های ستون A را در ستون B کپی کنیم. از آنجایی می‌خواهیم قالب‌بندی ستون B تغییر نکند، از فرمول‌نویسی کمک گرفته‌ایم. البته به جای فرمول‌نویسی برای ایجاد ستون B، می‌توانستیم ابتدا همه مقادیر تاریخ را کپی کرده، سپس قالب‌بندی را تغییر دهیم.

به این ترتیب تمامی تاریخ‌های نوشته شده در ستون A به صورت تاریخ شمسی در ستون B ظاهر می‌شوند. جالب است که انجام هر گونه محاسبه یا به کارگیری توابع تاریخی روی ستون B، امکان‌پذیر است و کاملا با جنبه محتویات تقویمی میلادی مطابقت دارد.

تبدیل تاریخ میلادی به شمسی در اکسل با قالب بندی
تبدیل تاریخ شمسی با قالب‌بندی

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

  • سلول یا سلول‌های مورد نظرتان را انتخاب کنید.
  • پنجره قالب‌بندی سلول را به کمک کلید میانبر ترکیبی با فشردن کلیدهای همزمان Ctrl+1 ظاهر کنید.
  • گزینه Custom را از کادر سمت راست (Category) انتخاب کرده و در بخش سمت راست، در کادر Type کد زیر را وارد کرده، سپس دکمه OK را کلیک کنید.

[$-fa-IR,16]dd/mm/yyyy;@

نکته: عبارت fa-IR,16 کدی است که برای نمایش تاریخ به صورت شمسی در سیستم عامل ویندوز ۱۰ به کار می‌رود و بخش دوم نیز قالب‌بندی نمایش تاریخ به شکل، ابتدا و در سمت راست، «سال»، در وسط «ماه» و سپس در سمت چپ، «روز»، را تعیین می‌کند. بنابراین کد به شکل dd/mm/yyyy به کار رفته است. البته می‌توانید این ترتیب را به دلخواه خود تغییر دهید. اگر به جای حروف dd از عبارت ddd استفاده کنید، نام روز هفته (مثلا چهارشنبه) در کنار تاریخ قرار می‌گیرد. همچنین کدی که به صورت mmm نوشته شود نام ماه (مثلا اردیبهشت) را درون سلول نمایش می‌دهد.

پنجره‌ Format Cells در اکسل
پنجره‌ Format Cells در اکسل

به منظور آشنایی بیشتر با اکسل و همچنین به کارگیری ابزارهای آن فرادرس یک فیلم آموزشی منتشر کرده است که به بیشتر موارد کاربردی اکسل اشاره دارد. برای مشاهده این فیلم آموزشی، به لینکی که در ادامه آورده شده، مراجعه کنید.

تبدیل تاریخ میلادی به شمسی به کمک کدنویسی VBA

این بار با استفاده از کدنویسی و به کارگیری زبان VBA در اکسل، تبدیل تاریخ را انجام می‌دهیم. به این منظور یک تابع با نام m2s ساخته شده که پارامترهای آن، یک سلول با تاریخ میلادی (با قالب متنی) است. کدی که در ادامه مشاهده می‌کنید به این منظور تهیه شده است.

برای مثال اگر فرمول زیر را به کمک تابع m2s به کار ببرید، تاریخ شمسی متناظر با تاریخ ۲۱ می ۲۰۲۱ بدست خواهید آورد. این تاریخ مصادف است با ۳۱ اردیبهشت ۱۴۰۰.

=m2s(“5/21/2021”)

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

Excel add ins
تصویر دسترسی به دستور نصب افزونه‌ها

در تصویر بالا، پس از اینکه دکمه Go را کلیک کردید، در پنجره بعدی، با دکمه Browse محل فایل FarsiTools.xla را مشخص کنید. به این ترتیب این افزونه به لیست ابزارهای شما اضافه شده و توابع مربوطه نیز در بخش User Defined (توابع دست‌ساز کاربر) قابل دسترس هستند. کافی است از نوار فرمول (Formula bar) دکمه fx را کلیک کنید تا پنجره‌ای به مانند زیر ظاهر شود.

user define functions
توابع دست ساز کاربر و انتخاب تابع تبدیل تاریخ میلادی به شمسی در اکسل

نکته: توجه داشته باشید که اگر تاریخ شمسی را بوسیله این توابع درون سلول ایجاد کرده‌اید، قالب‌بندی (Format Cell) و تبدیل تاریخ میلادی به شمسی در اکسل که در قسمت قبل بیان شده، تغییری در ظاهر سلول ایجاد نمی‌کند و شیوه نمایش محتویات، توسط فرمول و کد VBA تعیین می‌شود.

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

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

تبدیل تاریخ شمسی به میلادی در اکسل به کمک قالب‌بندی سلول

مشخص است که اگر تاریخی به صورت شمسی در کاربرگ اکسل ثبت شده باشد، به راحتی با تغییر قالب‌بندی می‌توان آن را به تاریخ میلادی تغییر داد. کافی است یکی از روش‌های استاندارد تاریخ را از پنجره Format Cell‌ و برگه Number در لیست Date انتخاب کنید. البته توجه داشته باشید که در بخش Calendar Type یکی از گزینه‌های Gregorian یا English را انتخاب کرده باشید. البته به قالبی که برای نمایش (ترتیب سال، ماه و روز) وجود دارد نیز دقت کنید تا تاریخ مورد نظرتان مطابق با سلیقه شما نمایش داده شود.

تبدیل تاریخ شمسی به میلادی در اکسل به کمک کد نویسی VBA

کد تبدیل تاریخ شمسی به میلادی را در ادامه مشاهده می‌کنید. البته توجه داشته باشید که هم در این کد و کد تبدیل تاریخ میلادی به شمسی، توابع دیگری به کار رفته‌اند که درون افزونه ثبت شده‌اند. در نتیجه فقط کپی کردن این کد درون پنجره VBA در اکسل نمی‌تواند عمل مورد نظرتان را اجرا کند. به عنوان مثال تابع persian_jdn و civil_jdn از این گونه توابع هستند که در هر دو کد به کار رفته‌اند. برای مشاهده این دو کد باید از محیط ویرایشگر VBA اکسل، استفاده کرده و برنامه‌ها را مرور کنید.

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

=s2m(“1387/6/19”)

توجه دارید که تاریخ شمسی به صورت یک عبارت متنی درون گیومه (“”) برای این تابع معرفی شده است. در تصویر زیر نمونه‌ای از کاربردهای دو تابع s2m و m2s همچنین horofi را مشاهده می‌کنید.

توابع موجود در ابزار farsitools

نکته: کدهای نوشته شده در این افزونه متعلق به سایت دیگری غیر از فرادرس است ولی می‌توانید آن را به صورت رایگان به همراه ابزارهای دیگر تبدیل تاریخ میلادی به شمسی در اکسل با کلیک کردن روی لینک (+) دریافت کنید.

معرفی فیلم آموزش Excel 2019 – مقدماتی

excel 2019 tutorial

اکسل یک نرم ‌افزار کاربردی (Application) محسوب شده که در انجام محاسبات به صورت سطری و ستونی (جدولی) به خوبی در خدمت کاربران قرار دارد. به همین جهت در بین متخصصین و کسانی که با اطلاعات و ارقام سروکار دارند از محبوبیت خاصی برخوردار است. نسخه ۲۰۱۹ این محصول یکی از موفق‌‌ترین صفحه گسترده‌ های الکترونیکی است که توسط شرکت مایکروسافت تهیه و توزیع شده است. امکانات جدید زیادی در این نسخه از نرم ‌افزار اضافه شده است که در این آموزش به برخی از آن‌ ها اشاره می گردد. بعضی از ابزارهای مهم تحلیل داده و توابعی که بیشتر کاربرد دارند، در این آموزش مورد توجه بوده و با ذکر مثال‌ هایی به آن‌ ها پرداخته شده است. فهرست سرفصل ها و مباحث مطرح شده در این دوره آموزشی، در ادامه آمده است:

درس اول: آشنایی با محیط اکسل که شامل معرفی انواع ورودی های سلول و شیوه ورود و ثبت اطلاعات در کاربرگ، ذخیره و بازیابی کارپوشه ها (انواع فایل های ذخیره سازی) است. درس دوم: با عنوان معرفی آدرس و مراجع در اکسل از بخش‌های مهم این آموزش محسوب می‌شود. درس سوم: با تیتر قالب‌ بندی در اکسل به موضوعاتی نظیر خط کشی و قالب بندی سلول، سطر، ستون و همچنین کاربرگ می‌پردازد. درس چهارم: سبک‌ها و قالب‌بندی شرطی (Conditional Formatting) را شرح می‌دهد. درس پنجم: آدرس مطلق و توابع اکسل و تابع شرطی را مورد توجه قرار داده است. درس ششم: تابع IFS و VLOOKUP در این درس، هدف آموزش قرار گرفته است. درس هفتم: مرتب‌سازی و فیلتر با زیر عنوان‌های مرتب‌سازی و طبقه بندی جدول‌ها، از موارد آموزشی در این درس هستند. استفاده از فیلتر برای جمع بندی توسط تابع Subtotal و جدول محوری (Pivot Table) و کاربردهای آن محتویات بخش بعدی از این درس خواهد بود. درس هشتم: رسم نمودار و انواع آن‌ها را مورد تجزیه و تحلیل قرار داده. درس نهم: تنظیمات نمایشی و چاپ، بزرگ نمایی و تنظیمات صفحه نمایش برای جدول های بزرگ (Freeze و Split)، ذخیره محتویات درس نهم هستند. درس دهم: شکل‌ها و ترسیمات، درج تصویر در کاربرگ، ترسیم خطوط و اشکال، مواردی است که درس دهم را تشکیل داده است. درس یازدهم: ابزارهای جستجو و تغییر پیش ‌فرض‌های اکسل آخرین درس این آموزش است.

این فیلم آموزشی برای کسانی که در رشته مدیریت یا صنایع همچنین حسابداری فعالیت می‌کنند مفید است. البته با توجه به گستردگی قابلیت‌های اکسل، تمامی کسانی که با لیست و اعداد و تهیه گزارش‌های عملکرد یا مالی سروکار دارند، احتیاج به فراگیری اکسل دارند که با مشاهده این فیلم، قادر به اجرای امور دلخواه خود، با سرعت و دقت خواهند بود. زمان این فیلم آموزشی ۸ ساعت و ۳۷ دقیقه است.

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

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

اگر این مطلب برای شما مفید بوده است، آموزش‌ها و مطالب زیر نیز به شما پیشنهاد می‌شوند:

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

بر اساس رای 1 نفر

آیا این مطلب برای شما مفید بود؟

نظر شما چیست؟

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