محاسبه سن در اکسل با تاریخ شمسی – آموزش گام به گام

۱۵۵ بازدید
آخرین به‌روزرسانی: ۹ آبان ۱۴۰۳
زمان مطالعه: ۸ دقیقه
دانلود PDF مقاله
محاسبه سن در اکسل با تاریخ شمسی – آموزش گام به گاممحاسبه سن در اکسل با تاریخ شمسی – آموزش گام به گام

در اکسل تابع مشخصی برای محاسبه سن وجود ندارد. اما با استفاده از فرمول‌نویسی اکسل و چند تابع مختلف می‌توان با در دست داشتن تاریخ تولد، سن افراد را در شکل‌های مختلف سال، ماه، روز یا ترکیبی از آن‌ها تعیین کرد. در این مطلب از مجله فرادرس نحوه محاسبه سن در اکسل با تاریخ شمسی را از طریق پنج نوع فرمول‌نویسی با توابع YEARFRAC ،TODAY و DATEDIF همراه مثال بررسی می‌کنیم. نکته اصلی استفاده از این فرمول‌ها و توابع بر اساس تقویم شمسی، توجه به انجام تنظیمات مربوط در نرم‌افزار اکسل و ویندوز است. بنابراین در ابتدای بحث مروری بر این تنظیمات داریم تا هنگام انجام مثال‌ها با پیغام خطا مواجه نشویم.

997696

انجام تنظیمات اکسل برای محاسبه سن با تاریخ شمسی

محاسبه سن در اکسل با تاریخ‌های میلادی بدون نیاز به تنظیمات خاص قابل اجرا است. اما در تاریخ‌های شمسی قبل از شروع کار باید تنظیمات مربوط را در ویندوز و نرم‌افزار اکسل انجام دهیم. زیرا عدم توجه به هر یک از این موضوعات باعث نمایش اعداد منفی یا مشاهده عبارت #VALUE در محاسبه سن یا حتی نمایش تاریخ شمسی به شکل نادرست می‌شود. این تنظیمات در نسخه ۲۰۱۶ اکسل به بعد و ویندوز ۱۰ به شرح زیر هستند.

  • تنظیم فرمت سلول از بخش «Format Cells» روی لوکیشن و تقویم «Persian»
  • ایجاد قالب‌بندی سفارشی در سلول با کد [$-fa-IR,96]dd/mmmm/yyyy;@
  • تغییر تنظیمات ویندوز  مربوط به تاریخ سیستم از بخش «Customize Format» در کنترل پنل و انتخاب تقویم هجری شمسی

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

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

محاسبه سن با تابع TODAY

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

=(TODAY()-Cell address)/365

در این فرمول «cell address» نشانی همان سلولی است که می‌خواهیم محاسبات مربوط به سن را در مورد آن انجام دهیم.

بنابراین با فرض درست بودن تنظیم تاریخ شمسی در نرم‌افزار اکسل، اگر تاریخ تولد افراد مختلفی را در جدول اکسل مانند تصویر زیر داشته باشیم، فرمول =(TODAY()-B2)/365 سن فرد مورد نظر در سلول B2 را محاسبه می‌کند. با کپی کردن فرمول در سایر ردیف‌های ستون B سن همه افراد به‌دست می‌آید.

محاسبه سن با تابع today- سن در اکسل
برای مشاهده تصویر در ابعاد بزرگ‌تر بر رو یتصویر کلیک کنید.

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

=INT((TODAY()-cell address)/365)

در تصویر زیر نتایج فرمول‌نویسی ترکیبی با این تابع برای سلول B2 نمایش داده شده است.

ترکیب توابع int و today- محاسبه سن در اکسل
برای مشاهده تصویر در ابعاد بزرگ‌تر بر روی آن کلیک کنید.

هر چند فرمول‌نویسی با تابع TODAY برای محاسبه سن در اکسل، ساده و دقیق است، با این حال نتایج آن در مورد سال‌های کبیسه دقیق نیست. زیرا در این حالت دیگر نمی‌توانیم عدد ۳۶۵ را در فرمول استفاده کنیم و سن افراد یک روز بزرگ‌تر محاسبه می‌شود. البته در مورد این سال‌های خاص می‌توانیم عدد ۳۶۶ را در فرمول درج کنیم، اما اگر بچه‌ای با سن کمتر از یک‌سال داشته باشیم، نتیجه محاسبه حتی با درج این عدد هم صحیح نخواهد بود. بنابراین استفاده از این روش ایده‌آل نیست و بهترین کار محاسبه سن از طریق توابع دیگر اکسل است.

نمای مینیمال یک لپ تاپ با نمودار های اکسل روی میز کار

محاسبه سن با تابع YEARFRAC

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

=YEARFRAC(start_date, end_date, [basis])

در این فرمول تعریف هر یک از آرگومان‌ها عبارتند از:

  • آرگومان «Start_date»: تاریخ تولد
  • آرگومان «End_date»: تاریخ کنونی که توسط تابع TODAY مشخص می‌شود.
  • آرگومان «Basis»: در این آرگومان اختیاری بر حسب استاندارد روز و ماه کشورهای مختلف اعداد ۰ تا ۴ با تعاریف زیر درج می‌شوند:
    • عدد ۰: محاسبه بر اساس سیستم آمریکایی با در نظر گرفتن ماه‌های ۳۰ روزه و سال ۳۶۰ روزه
    • عدد ۱: محاسبه بر اساس ماه و روز واقعی
    • عدد ۲: محاسبه بر اساس تعداد روز واقعی ماه و سال ۳۶۰ روزه
    • عدد ۳: محاسبه بر اساس تعداد روز واقعی ماه و سال ۳۶۵ روزه
    • عدد ۴: محاسبه بر اساس سیستم اروپایی با در نظر گرفتن ماه‌های ۳۰ روزه و سال ۳۶۰ روزه

برای درک بهتر عملیات، سن هر یک از افراد در مثال قبل را این‌بار از طریق فرمول =YEARFRAC(B2, TODAY(), 1) محاسبه می‌کنیم. در این فرمول برای نمایش نتیجه بر اساس ماه و روز واقعی، مقدار «Basis» را عدد ۱ در نظر می‌گیریم.

مثال با تابع yearfrac- تعیین age در excel
برای مشاهده تصویر در ابعاد بزرگ‌تر بر روی آن کلیک کنید.

همان‌طور که مشخص است، بار دیگر، نتایج به شکل اعشاری هستند. بنابراین می‌توانیم علاوه بر تابع INT از تابع ROUNDDOWN نیز برای رند کردن اعداد به سمت پایین و با حذف تمامی اعشار آن‌ها به‌شکل زیر استفاده کنیم. در این فرمول اکسل به‌دلیل آن‌که نمی‌خواهیم هیچ عدد اعشاری در نتیجه نمایش داده نشود، مقدار صفر را برای آرگومان مربوط به تابع ROUNDDOWN در نظر می‌گیریم.

=ROUNDDOWN(YEARFRAC(B2, TODAY(), 1), 0)

یادگیری بهتر توابع و فرمول‌نویسی در فرادرس

دوره اموزش توابع اکسل فرادرس-محاسبه سن در اکسل با تاریخ شمسی
برای مشاهده مجموعه فیلم آموزش توابع در فرادرس روی تصویر کلیک کنید.

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

همچنین دو مجموعه زیر تکمیل‌کننده مهارت‌های اصلی اکسل هستند که امکان دسترسی طبقه‌بندی شده آموزش‌ها را در یک قالب فراهم می‌کنند.

محاسبه سن با تابع DATEDIF

روش سوم برای محاسبه سن در اکسل با تاریخ شمسی فرمول‌نویسی با تابع DATEIF به‌شکل زیر است.

=DATEDIF(start_date, end_date, unit)

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

  • خروجی «Y»: نمایش سن بر حسب تعداد کل سال
  • خروجی «M»: نمایش سن بر حسب تعداد کل ماه‌ها
  • خروجی «D»: نمایش سن بر حسب تعداد کل روزها
  • خروجی «YM»: نمایش باقیمانده سن بر حسب ماه با حذف سال
  • خروجی «MD»: نمایش باقیمانده سن بر حسب روز با حذف ماه
  • خروجی «YD»: نمایش باقیمانده سن بر حسب روز با حذف سال
بر اساس رای ۰ نفر
آیا این مطلب برای شما مفید بود؟
اگر بازخوردی درباره این مطلب دارید یا پرسشی دارید که بدون پاسخ مانده است، آن را از طریق بخش نظرات مطرح کنید.
منابع:
Ablebits.comEXCELJET
نظر شما چیست؟

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *