تاریخ شمسی در اکسل ، از صفر تا صد کار با آن
سلام به همراهان همیشگی سافت پلاس . به یکی دیگر از سری مطالب مربوط به آموزش اکسل خوش آمدید .
نمی شود فارسی زبان باشی ، با اکسل هم کار کنی ولی دغدغه استفاده از تاریخ شمسی توی اکسل را نداشته بشی .
امروز ما هم می خواهیم در مورد تاریخ شمسی در اکسل با هم صحبت کنیم .
اینکه چطور می شود تاریخ شمسی در اکسل را نمایش بدهیم؟
چطور محاسبات مربوط به تاریخ را بر اساس تاریخ شمسی انجام بدهیم ؟
اینکه نحوه تبدیل تاریخ شمسی به تاریخ های میلادی و یا قمری و یا برعکس ، تبدیل این دو تاریخ به تاریخ شمسی به چه صورت است؟
و کلا اینکه با روش های مختلف کار با تاریخ شمسی در اکسل می خواهیم بطور کامل و جامع آشنا شویم .
پس آموزش مان به سه تا بخش تقسیم می شود .
بخش اول : نحوه نمایش تاریخ شمسی در اکسل است .
بخش دوم : چطور می توانیم تاریخ های مختلف را به تاریخ شمسی و یا بر عکس تبدیل کنیم ؟
بخش سوم: چطور محاسبات مربوط به تاریخ را با استفاده از تاریخ شمسی انجام بدهیم؟
من علیرضا شهرآئینی هستم و از شما می خواهم که تا پایان این آموزش همراه من باشید تا همه آنچه که در مورد کار با تاریخ شمسی توی اکسل احتیاج دارید را به شما آموزش بدهم .
راستی یادتان باشد که اینجا سافت پلاس است و توی سافت پلاس هم این سادگی در آموزش است که حرف اول را می زند .
پس با من همراه باشید .
فهرست محتوا
مشکل تاریخ شمسی در اکسل چه بود ؟
توی اکسل ما یک چیزی داریم به اسم Data Type . با استفاده از دیتا تایپ به اکسل می گوییم که داده ای را که قرار است در داخل یک یا چند تا سلول وارد کنیم از چه نوعی است .
مثلا داده ما از نوع عدد ( Number ) است یا از نوع متن ( Text ) است یا داده ما یک واحد پولی (Currency ) است . یکی از داده های قابل استفاده در اکسل هم داده نوع تاریخ است یا همان Date .
در نسخه های قبلی از نرم افزار اکسل امکان استفاده و یا حتی نمایش تاریخ شمسی به صورت استاندارد وجود نداشت .
یعنی اگر شما می خواستید یک تاریخ شمسی را در اکسل ثبت کنید باید از نوع داده ای به غیر از داده Date استفاده می کردید .
شاید این کار برای نمایش تاریخ شمسی مناسب بود و کار ما را راه می انداخت اما مشکل جایی بروز می کرد که می خواستیم روی این داده های تاریخ که از نوع شمسی بودند یک سری محاسبات انجام بدهیم .
پس ما می توانستیم شکل ظاهری تاریخ شمسی را در اکسل بیاوریم اما اینکه بخواهیم محاسباتی روی آن انجام دهیم برای ما امکانپذیر نبود .
این مشکل کماکان برقرار بود تا اینکه دو تا اتفاق خیلی خوب افتاد .
اتفاق اول این بود که تعدادی از کاربران حرفه ای اکسل که با برنامه نویسی هم آشنا بودند دست به کار شده و یک سری افزونه هایی را ایجاد کردند که با اضافه شدن آنها به محیط اکسل تا حد زیادی مشکل کار با توابع شمسی را برای ما حل می کرد .
مهمترین و معروف ترین شان هم افزونه ای بود به اسم پرنیان . البته الان دیگر بعید می دانم که این افزونه وجود داشته باشد اما در زمان خودش کارایی خوبی داشت .
در ادامه یک سری کدهای برنامه نویسی VBA در قالب ماژول هایی نوشته شدند که با اضافه کردن آنها به محیط اکسل هم می شد تا حد زیادی کار محاسبات تاریخ شمسی را انجام داد .
اما اتفاق دوم این بود که خود شرکت مایکروسافت کار پشتیبانی از تاریخ شمسی را در داخل نرم افزار اکسل انجام داد و از نسخه 2016 به بعد این نرم افزار تا حد زیادی تاریخ شمسی را شناسایی و از آن استفاده می کند .
البته اینکه می گویم مشکل کار با تاریخ شمسی در نسخه های جدید تا حدی حل شده و یا پشتیبانی از تاریخ شمسی در نسخه های 2016 به بعد تا حدودی انجام می شود نیاز به توضیحات بیشتر دارد که در ادامه راجع به آنها بیشتر صحبت خواهم کرد .
اما اول برویم به سراغ نحوه نمایش تاریخ شمسی در اکسل .
چطور یک تاریخ شمسی را در اکسل نمایش بدهیم ؟
وقتی صحبت از تاریخ شمسی در اکسل می شود ما در واقع راجع به دو تا موضوع با هم می خواهیم صحبت کنیم .
موضوع اول نحوه نمایش یک تاریخ شمسی است و موضوع بعدی که اتفاقا خیلی مهمتر هم هست نحوه انجام محاسبات بر روی تاریخ شمسی است .
بطور کلی برای اینکه بتوانیم با تاریخ شمسی در اکسل کار کنیم 5 تا روش مختلف وجود دارد که در دیاگرام بالا این روش ها را می توانید مشاهده کنید .
در ادامه راجع به یک به یک این روش ها صحبت می کنیم و مزایا و محدودیت های هر کدام از آنها را با هم بررسی می کنیم .
استفاده از تاریخ شمسی در قالب متن
ساده ترین روش و در عین حال پیش پا افتاده ترین روشی که برای نمایش تاریخ شمسی توی اکسل وجود دارد این است که تاریخ را در قالب متن در اکسل وارد کنیم .
البته این کار اصلا اصولی و حرفه ای نیست اما در نسخه های قبلی اکسل این تنها راهی بود که با استفاده از آن می شد از تاریخ شمسی استفاده کرد . اما کی می شود از این روش استفاده کرد؟
اگر همه چیزی که از یک تاریخ شمسی می خواهید این است که صرفا یک تاریخ خاص را برای یک رویداد ثبت کنید خیلی راحت می توانید فرمت سلول های مورد نظر را روی Text قرار دهید .
حالا می توانید تاریخ مورد نظر خودتان را در سلول یا سلول های مورد نظر درج کنید .
البته باز هم می گویم توی این روش باید قید اینکه بخواهید روی تاریخ های خودتان محاسباتی انجام بدهید را بزنید مگر اینکه به سراغ روش های دیگر که در ادامه به آنها اشاره می کنم بروید .
به عنوان مثال به تصویر زیر نگاه کنید .
در ستون اول از تصویر بالا یک سری تاریخ بصورت شمسی درج شده است .
اما یک مشکلی که اینجا وجود دارد مربوط به خوانایی این تاریخ هاست . چون ما عادت داریم که روز ، ماه و سال را از هم جدا کنیم تا خواندن تاریخ برای ما ساده تر شود .
برای این کار روش های مختلفی وجود دارد .
یکی از این روش ها این است که از توابع داخلی خود اکسل کمک بگیریم تا روز و ماه و سال را در تاریخ های بالا از هم جدا کنیم .
برای این کار می توانید از فرمول زیر کمک بگیرید .
=LEFT(A2,4)&"/" & MID(A2,5,2)&"/" & RIGHT(A2,2)
این تابع به شما کمک می کند که خیلی راحت بخش های مختلف تاریخ را از هم مجزا کنید .
توضیحات بیشتر را می توانید داخل این ویدئوی کوتاه مشاهده کنید .
این یک روش خیلی ساده بود که با استفاده از آن می توانید تاریخ شمسی را در داخل اکسل نمایش بدهید .
برای نمایش تاریخ شمسی در اکسل روش های دیگری هم وجود دارد که در ادامه راجع به آنها هم صحبت خواهیم کرد.
استفاده از فرمت های سفارشی برای کار با تاریخ شمسی
این روش روش خیلی خوبی برای نمایش تاریخ شمسی توی اکسل می تواند باشد .
اول برویم و ببینیم که توی این روش چطور می شود یک تاریخ شمسی را نمایش داد .
در قدم اول باید محدوده مورد نظرتان را که می خواهید در آنها تاریخ های مورد نظرتان به صورت شمسی نمایش داده شود را به حالت انتخاب در بیاورید .
حالا دکمه های CTRL+1 را از روی صفحه کلید فشار دهید تا کادر فرمت سل برای تان نمایش داده شود .
البته برای نمایش این کادر روش های دیگری هم وجود دارد اما ساده ترین و سریع ترین آنها همین روش است .
حالا باید یک سری از تنظیمات را در داخل این کادر برای خودتان انجام بدهید . تصویر زیر بصورت کامل این تنظیمات و مراحلی را که باید طی کنید را برای شما نمایش می دهد .
در کادر شماره 2 از تصویر بالا 6 تا فرمت برای نمایش تاریخ شمسی در اختیار شما قرار می گیرد که می توانید هر کدام را که خواستید انتخاب کنید تا اکسل تاریخ های شمسی را برای شما به همان شکل و شمایل نمایش بدهد .
این روش یک روش فوق العاده عالی برای نمایش تاریخ های شمسی در اکسل است .
البته یک راه دیگر هم برای نمایش تاریخ بصورت شمسی وجود دارد آن هم این است کهرا که خواستید خودتان از یک فرمت سفارشی در اکسل استفاده کنید . می پرسید چه فرمتی و چطور باید از آن استفاده کرد ؟
فرمتی که از آن صحبت می کنم فرمت زیر است .
[$-fa-IR,96]dd/mmmm/yyyy;@
اما اینکه کجا باید از این فرمت استفاده کنید را می توانید با نگاه کردن در تصویر زیر متوجه شوید .
حالا چه اتفاقی می افتد ؟
توی این حالت اگر در داخل سلول هایی که این فرمت را برای تاریخ آنها در نظر گرفته اید یک تاریخ میلادی را وارد کنید اکسل به صورت خودکار آن را تبدیل به تاریخ شمسی معادلش می کند و به جای تاریخ میلادی معادل تاریخ شمسی را به کاربرتان نمایش می دهد .
خب تا اینجا ما دو تا روش را یاد گرفتیم که به کمک آنها می توانیم تاریخ شمسی را در اکسل نمایش بدهیم .
روش اول که استفاده از قالب متن بود و من اصلا توصیه نمی کنم که به سراغ استفاده از آن بروید حتی اگر در کار با اکسل چندان مهارت بالایی ندارید .
اما روش دوم استفاده از فرمت های سلولی بود . توی این روش خیلی راحت تر می شود تاریخ شمسی را در اکسل نمایش داد .
حتی از این روش می شود برای تبدیل تاریخ میلادی به شمسی استفاده کرد .
ولی این روش یک ایراد بزرگ دارد. ایرادش هم این است که توی این روش فقط می شود تاریخ شمسی را نمایش داد ولی از آن نمی شود برای انجام محاسبات توی اکسل استفاده کرد .
مخصوصا وقتی که بخواهیم از یک تاریخ شمسی به عنوان ورودی توابع اکسل استفاده کنیم به مشکل بر می خوریم .
برای این مشکل هم راه کارهایی وجود دارد که در ادامه راجع به این روش ها با هم صحبت می کنیم .
استفاده از تاریخ شمسی در قالب عدد
خاطرتان هست که در روش های قبلی گفتم که می شود از تاریخ شمسی در قالب یک متن توی سلول ها استفاده کرد .
حالا می خواهم راجع به این صحبت کنم که چه جوری می شود تاریخ شمسی را در قالب اعداد استفاده کرد.
ما معمولا برای اینکه روز ، ماه و سال را در تاریخ شمسی از هم جدا کنیم از علامت اعشار یا همان علامت ” / ” خودمان استفاده می کنیم .
اما اگر بخواهیم از تاریخ شمسی در قالب عدد استفاده کنیم نیازی به استفاده از کاراکتر ممیز نداریم . در این روش اعداد مربوط به روز ، ماه و سال را دقیقا پشت سر هم می نویسیم . مثلا به تصویر زیر نگاه کنید .
در کادر بالایی این تصویر من تاریخ را به همان صورت معمول خودش نوشته ام اما در کادر پایین همان تاریخ را بدون استفاده از علامت “/ ” و بصورت یک عدد چند رقمی نوشته ام .
اما قبول دارید که خواندن تاریخ در حالت دوم برای ما خیلی سخت است . پس چه کار می شود کرد که هم تاریخ را به شکل عدد داشته باشیم و هم اینکه راحت تر بشود آن را خواند .
بهترین راهکار این است که به سراغ فرمت های سفارشی خودمان برویم و برای آن یک فرمت را تعریف کنیم . من برای این کار فرمت سفارشی زیر را در نظر گرفته ام .
0000"/"00"/"00
کافی است که این فرمت را در قسمت فرمت های سفارشی سلول های مورد نظرتان درج کنید . اینطوری تاریخی که در سلول درج می شود به صورت یک عدد چند رقمی است اما برای این که ما راحت تر بتوانیم این تاریخ را بخوانیم اکسل روز ، ماه و سال آن را برای ما جدا می کند .
اما یک سوال به نظرتان می شود از این روش در انجام محاسبات و کار با تاریخ شمسی استفاده کرد ؟
پاسخ به این سوال مثبت است .اگر چه کمی زحمت دارد ولی نشدنی نیست . اما اگر دوست دارید بدانید چطوری باید این کار رو انجام بدید کافی است که آموزش ویدئویی زیر رو تا انتها مشاهده کنید .
استفاده از افزونه در کار با تاریخ شمسی
یکی دیگر از روش هایی که با استفاده از آن می شود از تاریخ شمسی توی اکسل استفاده کرد کمک کردن از افزونه هایی است که مخصوص این کار وجود دارند .
یک جستجوی کوتاه که توی گوگل کنید افزونه های زیادی را می توانید برای این کار پیدا کنید .
اولین چیزی که در کار با افزونه ها باید بلد باشید این است که بدانید که چطوری می شود یک افزونه یا همان Add in را به محیط اکسل اضافه کنید یا در اصطلاح چه جوری می شود یک افزونه را روی اکسا نصب کرد .
اگر می خواهید در این مورد بیشتر و بهتر بدانید پس از شما دعوت می کنم که تماشای ویدئوی آموزشی زیر را نگاه کنید .
انجام محاسبات بر روی تاریخ شمسی
می رسیم به مهمترین بخش از مبحث کار با تاریخ شمسی در اکسل و آن هم اینکه چطور می توانیم بر روی تاریخ شمسی محاسبات انجام بدهیم .
در ادامه قرار است راجع به حالت های مختلف مربوط به انجام محاسبات تاریخ شمسی در اکسل با هم صحبت کنیم.
روش اول : استفاده از افزونه ها
همانطور که از افزونه ها برای نمایش تاریخ شمسی در اکسل استفاده کردیم بعضی از آنها این امکان و قابلیت را در اختیار ما قرار می دهد که بتوانیم برای محاسبات بر مبنای تاریخ شمسی از آنها کمک بگیریم .
مطالب زیر را حتما مطالعه کنید
Xlookup در اکسل- تابعی با کلی قابلیت های کاربردی
تابع Hlookup در اکسل💥نکات و ترفندهای کار با آن +ویدئو
تابع فیلتر در اکسل 💥به همراه یک مثال کاربردی
تابع سفارشی در اکسل 💥چطور تابع مدنظرتان را در اکسل ایجاد کنید ؟
تابع countif در اکسل💚راهنمای کامل کار با آن
زوم کردن در سلول های اکسل + ۳ روش مختلف
12 دیدگاه
به گفتگوی ما بپیوندید و دیدگاه خود را با ما در میان بگذارید.
توی اکسل میشه از توابع هم برای کار با تاریخ شمسی استفاده کرد ؟
بله توی بروز رسانی های بعدی به این سوال پاسخ داده خواهد شد
سلام خسته نباشید در اکسل در قسمت format cells گزینه date ودرقسمت calendar type
زبانPersian نیست با کدام افزونه میشه نصب کرد ؟یااز تنظیمات درست میشه ؟
سلام
این گزینه هایی که نام بردید فقط در اکسل ۲۰۱۶ به بالا وجود دارد .
برای استفاده از این قابلیت ها باید اکسل خودتان را به نسخه های جدیدتر ارتقا بدهید .
مطلب را خوب شروع کردید ولی بنظر ناقص می آید .
بله نه این مطلب که کل مطالب سایت به اینصورت است که هسته اولیه نگارش شده و بعد از مدتی که مطلب توی گوگل ایندکس شد به تناوب به روزسانی های بعدی برای آن منتشر خواهد شد
سلام ، روش استفاده عددی از تاریخ اشتباه است ، در مثالی که ذکر کردید فاصله بین تاریخ ۱۵ فروردین تا ۳۱ اردیبهشت را محاسبه کردید و جواب شد ۱۱۶ روز ؛ در حالی که حدود ۴۷ روز است
سلام از حسن توجه شما ممنونم بله روش فوق برای روزهای موجود در یک ماه به درستی کار می کنه اما برای روزهای مختلف در دو ماه متفاوت خطا میده توی اولین فرصت مشکل رو برطرف و ویدپو رو اصلاح می کنم .
ممنون از نذکر شما
درود بر شما
در ویدیو شما تعداد روزهای بین تاریخ ۱۴۰۱/۰۱/۱۵ الی ۱۴۰۱/۰۲/۳۱ را ۱۱۶ روز محاسبه کردید در حالیکه تعداد روز بین این دو تاریخ ۴۷ روز بیشتر نیست !!
نیازمند محاسبات مختلف از جمله رأس گیری تعدادی چک دریافتی با استفاده از تاریخ شمسی هستم که متاسفانه روش شما جوابگو نیست. آیا پیشنهادی دارید ؟
با سلام روشی که توی ویدئو هست فقط برای بازه یک ماه جوابگوست و اگر دو تا ماه مختلف را بررسی کنیم محاسبات به هم می ریزد . در اولین بروزرسانی روش مناسب تر معرفی خواهد شد .
توی ویدیوی تاریخ انقضای محصول عدد ۱۱۶ که اشتباهه!!!! از تاریخ ۱/۱۵ تا ۲/۳۱ مگه میشه ۱۱۶ روز ؟؟؟؟
برای رفع مشکل باید عدد حاصل رو منهای عدد ۷۰ کنید