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

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

برای آشنایی بیشتر با نحوه کار با اکسل و فرمول‌نویسی در آن، بهتر است نوشتارهای آموزش اکسل — به زبان ساده و فرمول نویسی در اکسل — به زبان ساده از مجله فرادرس را مطالعه کنید. همچنین خواندن مطالب توابع جدید اکسل ۲۰۱۹ — راهنمای کاربردی و قالب بندی شرطی (Conditional Formatting) با اکسل نیز خالی از لطف نیست.

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

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

فیلم آموزش اکسل – ابزارهای کاربردی Excel در فرادرس

کلیک کنید

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

فرمت سلول برای تاریخ شمسی

تاریخ شمسی در بسیاری از کشورها بخصوص در ایران و افغانستان به عنوان تقویم رسمی کشور، به کار می‌رود. به همین جهت شرکت مایکروسافت، براساس قالب یا فرمت جدید که ایجاد کرده است، اجازه ثبت و ذخیره سازی تاریخ شمسی در اکسل را می‌دهد. فرض کنید می‌خواهیم تاریخ اول فروردین ماه سال ۱۳۹۹ را در یک سلول کاربرگ ثبت کنیم.

به منظور انجام این کار، مراحل یا گام‌های زیر را بر می‌داریم.

1. سلول مورد نظرتان برای ثبت تاریخ شمسی را فعال کنید. برای مثال ما سلول A1 را انتخاب می‌کنیم.
2. از برگه Home قسمت Number روی دکمه نمایش قالب‌بندی (Number Format) کلیک کنید. محل قرار گیری این دکمه در تصویر ۱ دیده می‌شود.
3. در پنجره Format Cells در برگه Numbers، از لیست Category، گزینه Date را انتخاب کنید.
4. از لیست سمت راست، قسمت (Locale (Location ‌گزینه (Persian (Iran را انتخاب کرده و نوع تقویم (Calendar Type) را Persian انتخاب کنید. در تصویر ۲، محل قرارگیری هر یک از این گزینه‌ها، مشخص شده است.
5. دکمه OK را کلیک کرده و در سلول انتخابی تاریخ شمسی را به صورت دو رقم برای روز، دو رقم برای ماه و چهار رقم برای سال وارد کنید. البته می‌دانید که هر یک از این بخش‌ها باید به کمک علامت "/"‌ از یکدیگر جدا شوند تا اکسل متوجه تاریخی بودن مقدار عددی بشود.

گام‌های تنظیم مربوط به پنجره Format Cells با شماره، در تصویر ۲، مشخص شده‌اند.

نکته: انتخاب گزینه ...Input dates according باعث می‌شود، شیوه ورودی اجزای تاریخی شمسی (مثل روز، ماه و سال) در سلول نیز به شیوه تعیین شده در قالب‌بندی یا فرمت، صورت گیرد.

به این ترتیب سلول انتخابی، با فرمت تاریخ شمسی تنظیم می‌شود. حال تاریخ اول فروردین ماه ۱۳۹۹ را به شکل «۱/۱/۱۳۹۹» در این سلول وارد می‌کنیم. البته توجه داشته باشید که اولویت ماه و روز را در تعیین تاریخ رعایت کنید. ممکن است در تنظیمات سیستم عامل ویندوز برای ثبت تاریخ، ابتدا ماه، بعد روز و سپس سال در نظر گرفته شده باشد. کافی است به تنظیمات ویندوز (Settings) مراجعه کرده و در برنامه Customize Format، در بخش Date، تنظیمات را انجام دهید. کافی است کد dd/MM/yyyy را برای نمایش تاریخ به صورت کوتاه Short date وارد کرده و دکمه OK را کلیک کنید. نتیجه این کار را در تصویر ۳، مشاهده می‌کنید. مسیر دسترسی به این پنجره در ادامه آمده است.

Control Panel\Clock and Region\Change date, time, or number formats\Additional settings...\Date\Date Formats

نکته: اگر می‌خواهید قالب نمایش یا ورود تاریخ شمسی براساس اول سال، ماه و بعد روز باشد، شکل قالب را برای Short Date یا Long Date به صورت yyyy/MM/dd بنویسید. البته به یاد داشته باشید که با توجه به تعداد حروف به کار رفته باید هنگام ثبت تاریخ، رقم به کار ببرید. برای مثال، نماد yyyy نشانگر ثبت و نمایش سال براساس چهار رقم است. همچنین توجه داشته باشید که MMMM یا dddd نشانگر نمایش نام ماه به جای رقم یا نام روز به جای شماره روز است.

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

حال فرض کنید در سلول A1‌، تاریخ شمسی را به صورت «21/۱/1399» وارد کرده باشیم و بخواهیم یک دنباله تاریخی از آن ایجاد کنیم که به فاصله یک روز تنظیم شده‌اند. روش انجام این کار در ادامه مورد بررسی قرار گرفته است.

فیلم آموزش ترفندهای کاربردی در اکسل Excel در فرادرس

کلیک کنید

از آنجایی که قالب سلول A1 برای نمایش تاریخ شمسی در اکسل تنظیم شده و تنظیمات مطابق با تصویر ۱، صورت گرفته است، مقادیر تاریخی که با قالب ذکر شده هماهنگ باشند، همگی به عنوان تاریخ شمسی در نظر گرفته می‌شوند. بنابراین کافی است برای ایجاد دنباله با «عمل کشیدن و رها کردن» (Drag and Drop) روی دکمه پر کردن خودکار (Fill Handle)، دنباله را از سلول انتخاب شده، ایجاد کنید. به منظور آشنایی بیشتر با نحوه کار با این دکمه به متن دنباله در اکسل — از صفر تا صد مراجعه کنید.

دکمه پر کردن خودکار را به سمت پایین و روی سلول‌‌های A2 به بعد بکشید. نتیجه مطابق با تصویر ۴ خواهد بود.

همانطور که در تصویر شماره ۴، مشخص است، بعد از ۳۱ فروردین ماه، تاریخ بعدی به صورت ۱ اردیبهشت ماه در سلول نارنجی رنگ، مشخص شده است. اگر اکسل، تاریخ شمسی را به درستی به کار نبرده بود یا شما قالب‌بندی را به اشتباه به کار برده بودید، در سلول نارنجی، باید مقدار 32/01/1399 قرار می‌گرفت. این اشکال، بیانگر آن بود که اکسل این دنباله را به صورت تاریخی درک نکرده و فقط یک دنباله عددی ساخته است. پس همیشه دقت کنید که محتویات تاریخی توسط اکسل به درستی تعیین شده‌اند. یک راه ساده برای تشخیص صحت ورود مقادیر تاریخی، تراز یا نحوه قرارگیری آن‌ها در سلول است. از آنجایی که تاریخ، مانند ارقام، ماهیت عددی دارند، باید در حالت عادی با تراز راست در سلول ظاهر شوند. به این ترتیب متوجه می‌شویم که تاریخ را به درستی ثبت کرده‌ایم.

تغییر قالب نمایش تاریخ شمسی

همیشه به یاد داشته باشید که تغییر قالب سلول، ماهیت مقدار سلول را تغییر نمی‌دهد ولی می‌تواند نحوه نمایش محتویات یک سلول را به دلخواه کاربر درآورد. در این قسمت می‌خواهیم به کمک قالب‌بندی سلول (Format Cell) برای نمایش تاریخ شمسی در اکسل یک قالب سفارشی (Custom Format) ایجاد کنیم. برای مثال می‌خواهیم به جای نمایش شماره ماه، نام ماه به همراه سال و روز ظاهر شود. برای انجام این کار سلولی که حاوی مقدار تاریخ شمسی است را انتخاب کنید، سپس پنجره قالب‌بندی را با کلیک راست روی آن و اجرای دستور Format Cell، ظاهر سازید.

فیلم آموزش استفاده از توابع و فرمول نویسی در اکسل Excel در فرادرس

کلیک کنید

از قسمت Category از برگه Numbers، گزینه Custom را فعال کنید. در کادر سمت راست و در قسمت Type، کد زیر را وارد کنید.

[$-fa-IR,96]dd/mmmm/yyyy;@

واضح است که mmmm بیانگر نمایش نام ماه در تاریخ شمسی در اکسل است. همچنین $$\text{\$-fa-IR,96}$$ نیز قالب تاریخ شمسی در اکسل را نشان می‌دهد. نتیجه این کار را در تصویر ۵، مشاهده می‌کنید. به مقداری که در نوار فرمول دیده می‌شود و تفاوت آن با نمایش محتویات سلول نیز توجه کنید.

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

با نحوه مرتب‌سازی مقادیر عددی و متنی در اکسل آشنا هستید. کافی است سری به نوشتار مرتب سازی در اکسل — راهنمای کاربردی بزنید تا با کم و کیف انجام این کار آشنا شوید. در اینجا نشان می‌دهیم که تاریخ شمسی در اکسل نیز قابلیت مرتب‌سازی دارد. کاربرگ مربوط به تصویر ۴، را در نظر بگیرید. می‌خواهیم این لیست را براساس «تاریخ جدید به قدیم» (Sort Newest to Oldest) مرتب کنیم. مراحل زیرا برای انجام این کار پی می‌گیریم.

1. یکی از سلول‌های این لیست را انتخاب کنید.
2. از برگه Home و قسمت Editing روی گزینه Sort & Filter کلیک کنید.
3. از لیست ظاهر شده، گزینه Sort Newest to Oldest (مرتب سازی از جدید به قدیم) را انتخاب نمایید.

نکته: اکسل متوجه ماهیت تاریخی سلول‌های ستون A شده است. به همین دلیل، گزینه‌های مربوط به مرتب‌سازی تاریخ را به کاربر پیشنهاد کرده. در غیر اینصورت ممکن بود مرتب‌سازی براساس اعداد یا متن صورت بگیرد.

نتیجه مرتب‌سازی را در تصویر ۶، مشاهده می‌کنید.

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

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

برای اجرای یک مثال در این زمینه، باز هم از کاربرگ مربوط به تصویر ۵، کمک می‌گیریم. فرض کنید می‌خواهیم اختلاف اولین تاریخ در این لیست یعنی «09/02/1399» را از تاریخ «01/02/1399» بدست آورده و تعداد روزهای بین این دو تاریخ را در سلول B10، مشخص ‌کنیم.

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

کلیک کنید

درست به مانند محاسبه یک فرمول، ابتدا از علامت «=» برای شروع یک فرمول درون یک سلول خالی، استفاده می‌کنیم. سپس سلول با تاریخ بزرگتر (یعنی 09/02/1399) را، با آدرس A2، انتخاب و با زدن کلید «-»، عملگر تفاضل را مشخص کرده و در ادامه روی سلول با تاریخ کوچکتر (01/02/1399)، با آدرس A10، کلیک می‌کنیم. با توجه به آدرس سلول‌ها در کاربرگ مورد نظر، فرمول به صورت زیر نوشته خواهد شد.

B10: = A2 - A10

نتیجه برابر با 8 خواهد بود به این ترتیب مشخص است که بین این دو تاریخ، هشت روز فاصله است.

خوشبختانه علاوه بر محاسباتی نظیر جمع یا تفریق براساس تاریخ شمسی، از سلول‌های با این گونه مقادیر، در جدول محوری (Pivot Table) نیز می‌توان کمک گرفت. فیلتر کردن و جداسازی سطرهای کاربرگ براساس تاریخ شمسی نیز در نسخه‌های ۲۰۱۶ به بعد اکسل امکان‌پذیر است.

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

خلاصه و جمع‌بندی

در این نوشتار از مجله فرادرس با تغییر فرمت سلول برای نمایش تاریخ شمسی در اکسل آشنا شدیم. همچنین نحوه محاسبات روی تاریخ شمسی نیز معرفی و به کار گرفته شد. به این ترتیب توانستیم اختلاف دو تاریخ شمسی در اکسل را محاسبه کرده و تعداد روزهای بین آن‌ها را بدست آوریم. همچنین به کارگیری مرتب سازی تاریخ شمسی در اکسل نیز در جدول‌ها یا کاربرگ‌های اطلاعاتی، از مواردی بود که در این متن به آن پرداخته شد. به این ترتیب، بدون استفاده از هیچ افزونه‌ای، کار با تاریخ شمسی را در اکسل به کار گرفتیم. البته برای تسلط بیشتر و انجام محاسبات پیچیده‌تر روی تاریخ شمسی لازم است از افزونه‌های تاریخ شمسی (Excel Addins) در اکسل استفاده شود.

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