فرادرسساخت تقویم شمسی در اکسل — راهنمای گام به گام
تقویم شمسی یکی از دقیق‌ترین گاه‌شمارها (Calendar) در جهان است. این تقویم در کشورهای ایران و افغانستان و بعضی از نقاط دیگر جهان به کار می‌رود. خوشبختانه تاریخ شمسی در نسخه‌های جدید اکسل (۲۰۱۶ به بعد) گنجانده شده و دیگر احتیاجی به استفاده از افزونه‌های تجاری برای به کارگیری تاریخ شمسی نیست. در این نوشتار می‌خواهیم با نحوه ایجاد یا ساخت تقویم شمسی در اکسل آشنا شویم و به کمک فرمول‌نویسی، یک «تقویم رومیزی» (Desktop Calendar) بسازیم. در نوشتارهای دیگر از مجله فرادرس با توابع مرتبط با تاریخ آشنا شده‌اید. در اینجا هم با استفاده از آن‌ها، تقویم شمسی را کامل می‌کنیم.
به منظور آشنایی بیشتر با نحوه کار با تاریخ شمسی در اکسل بهتر است نوشتارهای تاریخ شمسی در اکسل | راهنمای کاربردی و تبدیل تاریخ شمسی به میلادی در اکسل --- راهنمای کاربردی را مطالعه کنید. همچنین برای تسلط به نحوه فرمول نویسی و به کارگیری توابع در اکسل هم، خواندن آموزش اکسل — به زبان ساده و فرمول نویسی در اکسل — به زبان ساده توصیه می‌شود.
ساخت تقویم شمسی در اکسل
تصور کنید که یک تقویم شمسی به صورت تصویر ۱ در اکسل داشته باشید. هر چند ممکن است نوشتن تاریخ‌ها در همه این سلول‌ها کار مشکلی به نظر برسد ولی ما سعی داریم به کمک فرمول نویسی و با استفاده از توابع اکسل، چنین تقویمی را ایجاد کنیم. البته توجه داشته باشید که به عنوان یک پارامتر برای انجام محاسبات به تاریخ اولین روز سال احتیاج داریم تا هم متوجه شویم که تقویم را باید برای چه سالی ایجاد کنیم.
توجه داشته باشید که قرار است ساخت تقویم شمسی در اکسل را به گونه‌ای انجام دهیم که با تغییر تاریخ اولین روز سال، اعداد و ارقام درون جدول‌ها با توجه به موقعیت تاریخ در روزهای هفته جابجا شده و تقویم رومیزی اکسل برحسب سال مورد نظر، ایجاد شود.
فرض کنید تاریخ اولین روز سال را با توجه به قالب اکسل برای ثبت و نمایش تاریخ شمسی در سلول R1 نوشته‌ایم. برای ایجاد تقویم شمسی ابتدا باید به سوالات زیر پاسخ بدهیم. این پاسخ‌ها، گام‌های تولید تقویم شمسی در اکسل را مشخص می‌کنند.
- اولین روز سال، چند شنبه است؟
- تاریخ روزهای بعدی در هفته اول چگونه مشخص شوند؟
- اولین روز از هفته بعدی از روز اول سال، چه تاریخی است؟
- ماه جاری چه تعداد روز دارد؟
- اولین روز ماه بعد، چند شنبه است؟
همانطور که گفتیم، پاسخ به این سوالات می‌تواند گام‌ها و مراحل فعالیت‌های تولید یا ساخت تقویم شمسی را برایمان مشخص کند. پس از ساخت تقویم رومیزی، رنگ‌آمیزی و کادر کشی به زیبایی کار افزوده و نتیجه حاصل، کاربردی خواهد شد.
گام اول: اولین روز سال چند شنبه است؟
برای پاسخ به این پرسش از تابع WEEKDAY در اکسل استفاده می‌کنیم. با توجه به اینکه تاریخ شمسی و میلادی از لحاظ روز هفته مشابه عمل می‌کنند، کافی است به این تابع، تاریخ اولین روز سال را به عنوان پارامتر داده، و شماره روز در هفته را دریافت کنیم. به این ترتیب از فرمول زیر کمک می‌گیریم. به یاد داشته باشید که تاریخ اولین روز سال در سلول R1 نوشته شده است. فرض کنید مقدار این سلول نیز به صورت 01/01/1399 ثبت شده باشد.
نکته: اگر می‌خواهید این تاریخ به شکل 1399/01/01 باشد، باید یک قالب سفارشی در اکسل ایجاد کنید. برای این منظور بهتر است متن تاریخ شمسی در اکسل | راهنمای کاربردی را مطالعه کنید.
=WEEKDAY(R1)
در تصویر ۲، پارامترهای این تابع مشخص شده‌اند. واضح است که پارامتر اول، تاریخی است که به دنبال روز هفته آن می‌گردیم. پارامتر دوم نیز الگوی شمارش روزهای هفته است. از آنجایی که اگر این تابع را بدون پارامتر دوم به کار ببریم، اولین روز هفته یکشنبه (SUNDAY) یا مقدار ۱ و آخرین روز هفته نیز شنبه (SATURDAY) با مقدار ۷ منظور می‌شود، ما هم از همین الگو استفاده کرده‌ایم.
پاسخ این تابع برای تاریخ اولین روز سال ۱۳۹۹، برابر با ۶ است. یعنی اولین روز این سال جمعه محسوب می‌شود. برای مقایسه این مقدار با روزهای هفته و قرار دادن عدد ۱ در اولین روز سال در تقویم از یک ترفند ساده کمک می‌گیریم. کافی است ایام هفته را برحسب نیازمان، شماره یا کد گذاری کنیم. از آنجایی که آخرین روز هفته برای ما جمعه است ولی در تابع WEEKDAY، مقدار آن شش است، جدولی به صورت زیر تشکیل می‌دهیم.
گام دوم: تعیین تاریخ روزهای بعدی در هفته اول
به این ترتیب با توجه به مقدار ۶ برای این تابع، می‌دانیم که باید تاریخ اولین روز سال را برای جمعه در نظر بگیریم. ولی برای خودکار سازی آن و استفاده از این موضوع برای ماه‌های دیگر سال، از فرمول زیر استفاده می‌کنیم.
=IF($B5=E$3,1,IF($B5<E$3,D5+1,""))
به این ترتیب با توجه به آدرس ترکیبی برای سلول‌های پیش‌نیاز و قرارگیری تابع WEEKDAY در سلول B5، اگر فرمول بالا را در سلول E5 نوشته و برای سلول‌های دیگر این سطر کپی کنیم، روز جمعه اولین روز سال در تقویم شمسی تعیین می‌شود.
همانطور که دیده می‌شود، اگر نتیجه حاصل از تابع WEEKDAY برابر با یکی از ایام هفته باشد، مقدار ۱ در آن سلول قرار گرفته و در صورتی که مقدار تابع WEEKDAY کوچکتر از کد روز هفته باشد، یعنی که باید مقدار تاریخ مربوط به روز قبل، یک واحد اضافه شده و در محل جاری قرار گیرد. به این ترتیب مجبور به استفاده از دو تابع IF به صورت تو در تو هستیم.
نکته: برای اینکه فرمول یک دست باشد، مقدار سلول D5 را صفر در نظر گرفته و در فرمول برای سلول اول به کار برده‌ایم. به این ترتیب با کشیدن و رها کردن (Drag & Drop)، فرمول را برای سلول‌های بعدی در همین هفته کپی کنید. با این کار، تاریخ روز قبل با یک جمع شده و تاریخ روز جاری را نشان می‌دهد.
گام سوم: تعیین تاریخ اولین روز هفته بعدی
تا اینجا مشخص شد که تاریخ برای هفته اول ماه چگونه باید مشخص شود که البته از ترکیب دو تابع IF کمک گرفتیم. ولی برای هفته دوم به بعد عمل ساده‌ای می‌تواند صورت بگیرد. کافی است برای اولین روز هفته دوم، مقدار تاریخ ماکزیمم هفته اول را بدست آورده و با یک جمع کنیم. روزهای بعدی نیز با افزایش یک واحدی به ترتیب با تاریخ قبلی ساخته می‌شوند. بنابراین فقط کافی است یک فرمول برای پیدا کردن حداکثر تاریخ روز هفته قبل پیدا کنیم.
فرمول را به صورت زیر می‌نویسیم. به این ترتیب به تاریخ آخرین روز هفته قبل یک واحد اضافه کرده و به عنوان تاریخ اولین روز هفته بعد به کار می‌بریم.
MAX($E5:$K5)+E$3+1
گام چهارم: تعداد روزهای ماه جاری
پس از نوشتن فرمول قبلی تقریبا کار برای ماه فروردین تمام شده است. فقط باید به این موضوع نیز توجه داشته باشیم که نباید تاریخ روزهای این ماه، بزرگتر از ۳۱ روز باشد. برای کنترل این شرط، مجبور به استفاده از یک تابع IF هستیم تا نتیجه افزایش روزانه تاریخ را با مقدار ۳۱ گزارش داده و برای چنین وضعیتی مقدار خالی ثبت شود. به این ترتیب رابطه بالا به صورت فرمول زیر در خواهد آمد.
=IF(MAX($E5:$K5)+E$3+1<=31,MAX($E5:$K5)+E$3+1,"")
حال به راحتی این فرمول را برای سطرها یا ستون‌های دیگر روزهای مربوط به ماه فروردین کپی کنید تا تقویم مربوط به این ماه حاصل شود. واضح است که میزان افزایش برحسب شماره‌های موجود در ردیف سوم جدول (E3 تا K3) صورت گرفته است. همانطور که می‌بینید یک واحد به هر یک از مقادیر این سلول‌ها اضافه شده (E$3+1) و با مقدار حداکثر تاریخ در هفته قبل جمع شده‌اند. به این ترتیب تاریخ روز مربوط به ایام هفته دوم تعیین می‌شوند.
گام پنجم: تعیین تاریخ اولین روز ماه بعد
همانطور که برای ماه فروردین، تاریخ و روز اول ماه را مشخص کردیم، برای ماه اردیبهشت و ماه‌های بعدی نیز احتیاج است که این کار صورت گیرد. بقیه مراحل مانند تعیین تاریخ روز اول هفته دوم به بعد ماه، به صورتی که در گام‌های دوم و سوم گفته شد، قابل تکرار است تا ساخت تقویم شمسی در اکسل تکمیل شود.
از آنجایی که اکسل، تاریخ شمسی را می‌شناسد، برای تعیین تاریخ اولین روز ماه اردیبهشت، کافی است به تاریخ اولین روز سال (یعنی 01/01/1399)، ۳۱ واحد اضافه کنیم و مثلا در سلول S1 آن را ثبت کرده و محاسبات را از گام اول طبق این سلول انجام دهیم.
برای ماه‌های بعدی نیز کافی است دوباره به S1، به تعداد ۳۱ روز بیفزاییم تا تاریخ اولین روز ماه خرداد حاصل شود و نتیجه را در سلول T1 ذخیره کنیم. فقط توجه داشته باشید که برای ماه‌های پاییز و زمستان میزان افزایش باید ۳۰ روزه باشد.
در انتها، جدول کشی، رنگ‌آمیزی و چیدمان سلول‌ها برای ساخت تقویم شمسی در اکسل را به سلیقه خودتان انجام دهید. حتی می‌توانید تصویر یا عکسی از خودتان را به عنوان زمینه برای کاربرگ به کمک دستور Background از برگه Page Layout در بخش Page Layout در نظر بگیرید.
نکته: برای نمایش اعداد در تقویم به صورت فارسی، کافی است که از یک قلم فارسی برای نمایش آن‌ها استفاده کنید.
اگر می‌خواهیم تقویم شمسی ساخته شده توسط این توابع را در قالب یک فایل اکسل دریافت کنید، کافی است، اینجا کلیک کرده و این فایل را به صورت فشرده دریافت کنید. به این ترتیب با خارج کردن آن از حالت فشرده می‌توانید کارپوشه taghvim.xlsx را در اکسل بارگذاری کرده و با ثبت تاریخ اول سال، در سلول R1، تقویم آن سال را مشاهده کنید. البته تقویم این فایل براساس روز و ماه‌های سال ۱۳۹۹ نوشته شده است.
خلاصه و جمع‌بندی
خوشبختانه در نسخه‌های جدید اکسل (۲۰۱۶ به بعد) امکان بهره‌گیری از تاریخ شمسی وجود دارد. البته این موضوع بیشتر به ساختار ویندوز ۱۰ بر می‌گردد. به این ترتیب به سادگی و با تغییر قالب نمایش تاریخ، قادر به محاسبه براساس تاریخ شمسی هستیم. در اینجا به کمک توابع ساده مانند IF ،MAX و همچنین تابع موثر WEEKDAY، ساخت تقویم شمسی در اکسل را با طی کردن پنج گام مهم، اجرا کردیم. در حقیقت این گام‌ها، پاسخی اساسی به سوالاتی را فراهمی می‌آوردند که برای ساخت تقویم شمسی در اکسل بسیار با اهمیت بودند.
البته زیبا سازی و ساخت تقویم رومیزی به وسیله اکسل مطابق با سلیقه شما صورت می‌گیرد. به همین جهت جنبه‌های قالب‌بندی سلول و جدول کشی در چنین جدولی را به نوشتارهای دیگر مجله فرادرس واگذار کرده‌ایم. بخش مهم ساخت تقویم شمسی در اکسل به عهده تابع WEEKDAY بود که البته به ساختار تاریخ شمسی ارتباطی نداشت. به این ترتیب بدون اینکه لازم باشد، از توابعی که توسط افزونه‌های تجاری اکسل برای ثبت تاریخ شمسی ارائه می‌شوند، استفاده کنیم، ساخت تقویم شمسی در اکسل را اجرا کردیم.
