برای یجاد تقویم شمسی در اکسل باید در مورد نحوه استفاده از تاریخ شمسی بدانیم که قبلا توضیح داده ایم و اگر آشنایی با آن ندارید پیشنهاد می کنم ابتدا آن مطلب را مشاهده نمائید . در این آموزش هم قصد داریم با استفاده از همان ماژولها و چند ترفند ساده این تقویم کاربردی را اضافه نمائیم.
این تقویم به گونه ایست که داینامیک می باشد یعنی با انتخاب ماه و سال تقویم بروزرسانی می شود و حتی تعطیلات موجود در آن ماه هم با رنگ متمایزی نمایش داده می شود .
فهرست محتوا
مراحل ایجاد تقویم شمسی در اکسل
ابتدا جدیدترین نسخه ماژولهای شمسی را دانلود کنید که در حال حاضر نسخه 2.7.8 می باشد و سپس یک فایل جدید اکسل ایجاد کنید و ماژول را به آن اضافه کنید و با پسوند XLSM آن را ذخیره کنید .
مراحلی که بالا گفته شد قبلا در مطلب مربوط به تاریخ شمسی در اکسل آموزش داده شده است.
پس از انجام کارهای بالا ، یکی فایل اکسل باز خواهید داشت .
فرض ما بر این است که می خواهیم نام ماه در C3 باشد و نام سال در D3 مانند تصویر زیر
ساخت لیست پائین افتادنی نام ماهها
برای اینکه در سلول C3 مطابق تصویر بالا نام ماههای سال را داشته باشیم باشد کارهای زیر را انجام دهیم.
- در فضای خالی مثلا E3 تا E13 اعداد 1 تا 12 را می نویسیم .
- در سلول F3 تا F13 فرمول زیر را قرار می دهیم.
=mahName(E3)
خواهید دید که در محدوده F3:F13 نام ماهها از فروردین تا اسفند نمایش داده می شود.
کافیست در یک سلول خالی مثلا G3 فرمول زیر را بنویسید
=TEXTJOIN(",",TRUE,F3:F13)- با اجرای کد بالا ماههای سال پشت سر هم قرار گرفته و با کاما از هم جدا شده اند .
- سلول G3 را کپی کنید و در همان سلول Past Values کنید .
- مجددا سلول G3 را کپی کنید .
- در سلول C3 قرار بگیرید و کلیدهای Alt + A + V + V را بزنید
- از پنجره باز شده List را انتخاب کنید و در باکس مربوطه عبارت کپی شده را وارد کنید
- در نهایت باید به این شکل باشد .
در سلول D3 هم عدد 1402 را بنویسید که البته می توانید آن را تغییر دهید . حالا ما ماههای سال و سال را به ترتیب در C3 و D3 داریم و می توانیم ادامه کار ایجاد تقویم شمسی در اکسل را انجام دهیم. باید برای شروع تاریخ شروع ماه انتخاب شده در C3 را داشته باشیم و برای ان منظور در سلول A1 فرمول زیر را قرار می دهیم .
=D3&TEXT(mahConvert(C3),"00")&"01"
فرمول بالا همواره اولین روز ماه و سال انتخاب شده در سلول C3 و D3 را نمایش می دهد .
به عنوان مثال اگر در C3 شهریور و در D3 مقدار 1402 را وارد کرده باشیم نتیجه فرمول بالا مطابق 14020601 می شود .
- از سلول C4 تا I4 نام روزهای هفته را از شنبه تا جمعه قرار دهید
- در سلول C5 فرمول زیر را بنویسیذ که اختلاف شماره روز هفته اول را از تاریخ کم کند .
- و سپس در D5:i5 هم فرمول خط بعد را وارد کنید .
- در ردیف های بعدی هم فرمول را طوری بنویسیذ که یک روز به روز قبلی اضافه کند.
=SubDay(A1,DayWeekNo(A1)) =AddDay($C$5,COLUMN(A1))
در فرمولهای بالا که برای ساخت تقویم شمسی در اکسل ضرورت دارد نکاتی نهفته است که پیشنهاد می شود حتما ویدیو آموزشی اول پست را با دقت ببینید.
نمایش صحیح روز شروع تقویم شمسی در اکسل
تمام مراحل بالارا به درستی انجام داده باشید خواهید دید که چالشی که داریم این است که روز اول تاریخ شروع اگر مثلا دوشنبه باشد در روزهای یکشنبه و شنبه هم اطلاعات نشان می دهد که درست نیست و برای عدم نمایش آنها باید مراحل زیر را انجام دهیم .
- محدوده C3:I3 را انتخاب می کنیم
- از تب Home بخش Conditional Formatting گزینه آخر را نتخاب می کنیم.
- در پنجره باز شده New Rule را انتخاب می کنیم و پنحره جدید که باز شد
- فرمول زیر را در آن قرار می دهیم.
=--(RIGHT(C5,2))>24
نتیجه باشد مانند تصویر زیر شود .
در تصویر بالا باید روی Format کلیک کنیم و از پنجره باز شده روی تب Number و قسمت Custom رفته و سه سیمی کالم قرار دهیم ;;; به این شکل ، این کار باعث می شود که محتویات سلول نمایش داده نشود .
بدین ترتیب محتویات سلولهایی که روز آنها کمتر از 24 باشد نمایش داده نمی شود .
رنگی کردن تعطیلات در تقویم شمسی
برای این منظور یاد لیست تعطیلات سال شمسی را داشته باشیم که می توانید لیست تعطیلات سال 1402 را از مطلب مربوط به افزودن تاریخ شمسی به اکسل دانلود نمائید .
برای بقیه تعطیلات هم می توانید از وبسایت Time.ir استفاده نمائید.
حالا فرض میکنیم که لیست تعطیلات سال را دارید ، باید آنها را در محدوده A2 تا A100 یا هر جا که ادامه پسدا کرد در ستون A وارد کنید .
- توجه داشته باشید که وارد کردن فقط تاریخ تعطیلات کفایت می کند .
- سپس کافیست که محدوده تقویم را انتخاب کرده و وارد کاندیشنال فرمتینگ شوید .
- در قسمت New Rule شرط زیر را وارد کنید.
=COUNTIF($A$5:$A$700,C5)>0
فرمول بالا ستون A را از محدوده A5:A700 بررسی می کند و در واقع با شرط اینکه در تقویم باشد شمارش می کند و اگر نتیجه بزرگتر از 0 شد یعنی آن تاریخ در لیست تعطیلات تقویم شمسی وجود دارد و سپس در بخش Format می توانید استایل دلخواه را برای آن انتخاب کنید .
پیشنهاد می شود برای درک بهتر و ایجاد صحیح تقویم شمسی در اکسل ابتدا ویدیو آموزشی را ببیند و سپس فایل آماده را در ادامه همین مطلب دانلود نمائید.
آپدیت و اضافه شدن تقویم 1403
در این آپدیت هم مناسبتهای مربوط به سال 1403 اضافه شده و هم امکان دیگری افزوده شده که وقتی روز تعطیل را انتخاب کنید ، مناسبت آن روز در ردیف اول نمایش داده می شود و از باکس دانلود می توانید آن را دریافت کنید .
