محاسبه سن در اکسل با تاریخ شمسی – آموزش گام به گام
در اکسل تابع مشخصی برای محاسبه سن وجود ندارد. اما با استفاده از فرمولنویسی اکسل و چند تابع مختلف میتوان با در دست داشتن تاریخ تولد، سن افراد را در شکلهای مختلف سال، ماه، روز یا ترکیبی از آنها تعیین کرد. در این مطلب از مجله فرادرس نحوه محاسبه سن در اکسل با تاریخ شمسی را از طریق پنج نوع فرمولنویسی با توابع YEARFRAC ،TODAY و DATEDIF همراه مثال بررسی میکنیم. نکته اصلی استفاده از این فرمولها و توابع بر اساس تقویم شمسی، توجه به انجام تنظیمات مربوط در نرمافزار اکسل و ویندوز است. بنابراین در ابتدای بحث مروری بر این تنظیمات داریم تا هنگام انجام مثالها با پیغام خطا مواجه نشویم.
انجام تنظیمات اکسل برای محاسبه سن با تاریخ شمسی
محاسبه سن در اکسل با تاریخهای میلادی بدون نیاز به تنظیمات خاص قابل اجرا است. اما در تاریخهای شمسی قبل از شروع کار باید تنظیمات مربوط را در ویندوز و نرمافزار اکسل انجام دهیم. زیرا عدم توجه به هر یک از این موضوعات باعث نمایش اعداد منفی یا مشاهده عبارت #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 سن همه افراد بهدست میآید.
این فرمول ساده است. با این حال همانطور که در نتایج میبینیم، تعداد رقمهای اعشاری بسیاری در نتیجه حاصل وجود دارد. معمولا این نوع از نمایش اعداد برای سن یک فرد بهکار نمیرود. بنابراین لازم است با استفاده از تابع INT نتیجه حاصل را به نزدیکترین عدد صحیح به سمت پایین رند کنیم. بنابراین فرمول زیر را جایگزین میکنیم.
=INT((TODAY()-cell address)/365)
در تصویر زیر نتایج فرمولنویسی ترکیبی با این تابع برای سلول B2 نمایش داده شده است.
هر چند فرمولنویسی با تابع TODAY برای محاسبه سن در اکسل، ساده و دقیق است، با این حال نتایج آن در مورد سالهای کبیسه دقیق نیست. زیرا در این حالت دیگر نمیتوانیم عدد ۳۶۵ را در فرمول استفاده کنیم و سن افراد یک روز بزرگتر محاسبه میشود. البته در مورد این سالهای خاص میتوانیم عدد ۳۶۶ را در فرمول درج کنیم، اما اگر بچهای با سن کمتر از یکسال داشته باشیم، نتیجه محاسبه حتی با درج این عدد هم صحیح نخواهد بود. بنابراین استفاده از این روش ایدهآل نیست و بهترین کار محاسبه سن از طریق توابع دیگر اکسل است.
محاسبه سن با تابع YEARFRAC
روش قابل اطمینانتر برای محاسبه سن در اکسل بر حسب تعداد سال، فرمولنویسی با تابع YEARFRAC است. برای این کار فرمول زیر را استفاده میکنیم.
=YEARFRAC(start_date, end_date, [basis])
در این فرمول تعریف هر یک از آرگومانها عبارتند از:
- آرگومان «Start_date»: تاریخ تولد
- آرگومان «End_date»: تاریخ کنونی که توسط تابع TODAY مشخص میشود.
- آرگومان «Basis»: در این آرگومان اختیاری بر حسب استاندارد روز و ماه کشورهای مختلف اعداد ۰ تا ۴ با تعاریف زیر درج میشوند:
- عدد ۰: محاسبه بر اساس سیستم آمریکایی با در نظر گرفتن ماههای ۳۰ روزه و سال ۳۶۰ روزه
- عدد ۱: محاسبه بر اساس ماه و روز واقعی
- عدد ۲: محاسبه بر اساس تعداد روز واقعی ماه و سال ۳۶۰ روزه
- عدد ۳: محاسبه بر اساس تعداد روز واقعی ماه و سال ۳۶۵ روزه
- عدد ۴: محاسبه بر اساس سیستم اروپایی با در نظر گرفتن ماههای ۳۰ روزه و سال ۳۶۰ روزه
برای درک بهتر عملیات، سن هر یک از افراد در مثال قبل را اینبار از طریق فرمول =YEARFRAC(B2, TODAY(), 1) محاسبه میکنیم. در این فرمول برای نمایش نتیجه بر اساس ماه و روز واقعی، مقدار «Basis» را عدد ۱ در نظر میگیریم.
همانطور که مشخص است، بار دیگر، نتایج به شکل اعشاری هستند. بنابراین میتوانیم علاوه بر تابع 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»: نمایش باقیمانده سن بر حسب روز با حذف سال