عمومی، کاربردی ۴۶۶۱۵ بازدید

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

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

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

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

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

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

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

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

number format cell command
تصویر ۱: دسترسی به فرمان قالب‌بندی سلول برای مقادیر عددی (Number Format)
  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 dialog box
تصویر ۲: پنجره تنظیمات قالب‌بندی تاریخ و نمایش تاریخ شمسی در اکسل

گام‌های تنظیم مربوط به پنجره 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

customize format
تصویر ۳: تنظیم قالب‌بندی برای نمایش تاریخ شمسی در سیستم عامل ویندوز به ترتیب سال/ماه/روز

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

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

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

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

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

time series in excel
تصویر ۴: ایجاد دنباله تاریخ شمسی در اکسل

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

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

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

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

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

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

customize shamsi date format
تصویر ۵: تغییر قابل نمایش تاریخ شمسی در اکسل

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

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

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

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

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

sort shamsi dates
تصویر ۶: مرتب سازی تاریخ شمسی در اکسل

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

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

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

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

B10: = A2 – A10

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

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

filter shamsi dates
تصویر ۷: فیلتر گذاری روی ستون با مقادیر تاریخ شمسی در اکسل

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

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

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

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

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

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

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

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

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

  • سلام و وقت بخیر
    با تشکر
    من میخوام با استفاده از تاریخ شمسی در اول هر ماه به مانده مرخصی من دو روز اضافه بشه. این امکان وجود داره؟ و اگر اول هر ماه روز جمعه باشد و چون تعطیل هست امکان داره خودش بروز رسانی بشه؟

  • درود
    با تشکر از توضیحات آقا آرمان استاد محترم. من میخوام در یک ستون تاریخ روز به صورت اتومات درج بشه ولی تاریخ سطرهای قبلی که برای روزهای قبل هست آپدیت نشه. ممکنه راهنمایی بفرمایید؟
    سپاس فراوان

  • سلام. توضیح مشکلم یخورده سخته
    من دارم یه گانت چارت تهیه میکنم و توی یک ستون تاریخهای شمسی انجام کارهای مختلف رو وارد کردم. توی ردیف افقی بالا میخوام روزهای ماه رو فق به صورت عدد روز نشون بده و ماه و سال رو نشون نده. ولی فقط در تاریخ میلادی این امکان وجود داره. میخواستم ببینم راهی واسه این کار وجود داره ؟

  • سلام و درود

    ضمن سپاس از مطالب ارزشمند شما. در قسمت کلندر تایپ اصلا نام ایران یا پرشن وجود ندارد.
    از کجا میتوانم این موضوع را اضافه کنم. اکسلم ۲۰۱۶ است

    1. سلام، وقت شما بخیر؛

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

      از اینکه با مجله فرادرس همراه هستید از شما بسیار سپاسگزاریم.

    1. سلام، وقت شما بخیر؛

      حتما توجه کنید که گزینه Input dates را در پایین پنجره فعال کرده باشید. در غیر اینصورت تاریخ ورودی باز هم میلادی است. استفاده از قالب بندی زمانی که تاریخ میلادی نوشته شده و می‌خواهید به قالب شمسی نمایش داده شود بدون فعال سازی این گزینه میسر خواهد بود.

      موفق و پیروز باشید.

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

    از توجه شما به مجله فرادرس سپاسگزاریم.

    پاینده باشید.

  • سلام.ابتدا متشکرم به خاطر مطلب بسیار عالیتون
    فقط من یه مشکل دارم.
    سلول هایی کع از قبل تاریخ داشتن رو وقتی فرمتشون رو به تاریخ شمسی تغییر میدم اتفاقی نمیفته.دوبار که کلیک می کنم، ینی برای نوشتن تو اون سلول،بعدش به فرمت صحیح درمیاد؟
    چیکار باید بکنم؟
    ممنون میشم راهنمایی بفرمایید

    1. سلام و درود،

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

      پیروز و سربلند باشید.

نظر شما چیست؟

نشانی ایمیل شما منتشر نخواهد شد.

مشاهده بیشتر