MS Excel، أو Excel، هو برنامج جداول بيانات يستخدم على نطاق واسع مع مجموعة واسعة من الأدوات والميزات المضمنة. فهو يساعدنا على تسجيل مجموعات البيانات المختلفة وإجراء العمليات الحسابية عبرها باستخدام خلايا متعددة. مثال على الحساب الكلاسيكي هو حساب الفرق بين تاريخين. ومع ذلك، فإنه لا يبدو وكأنه عملية حسابية مفيدة. لكن نفس الأسلوب يعد أمرًا بالغ الأهمية إلى حد ما للعثور على أو حساب عمر شخص ما أو شيء ما في Microsoft Excel.
بصرف النظر عن معرفة عمر أي شخص، يمكننا استخدام نفس المفهوم لحساب مدة أي مشروع، وسنوات وجود أي شركة، وعدد السنوات المنقضية بين التواريخ المحددة، وما إلى ذلك.
في هذا البرنامج التعليمي، نناقش طرقًا أو حلولًا مختلفة حول كيفية حساب العمر في برنامج Excel. يساعدنا البرنامج التعليمي على تعلم حساب الأعمار كأعداد السنوات والأشهر والأيام الكاملة في التاريخ الحالي أو أي تاريخ محدد.
كيف يمكننا حساب العمر في إكسيل؟
على الرغم من عدم وجود وظيفة محددة في Excel لحساب العمر، إلا أن لدينا العديد من الطرق لحساب العمر بشكل مختلف في سيناريوهات مختلفة. ومع ذلك، يجب أن نعرف تاريخ الميلاد الأصلي للفرد (D.O.B.). نقوم بعد ذلك بتوفير تاريخ الميلاد جنبًا إلى جنب مع وظائف Excel، وتحديدًا DATEDIF وTODAY، لحساب العمر أو التمييز بين التواريخ. دعونا الآن نناقش السيناريوهات الشائعة للعثور على العمر:
حساب العمر بالسنوات
عادة ما نأخذ في الاعتبار عدة عوامل أثناء حساب عمر أي شخص. على سبيل المثال، قد يتعين علينا حساب العمر بالسنوات أو الأشهر أو الأيام أو كل ذلك معًا. ومع ذلك، فإن حساب عمر الشخص بالسنوات هو المهمة الأكثر استخدامًا في برنامج Excel.
على افتراض أننا نعرف تاريخ الميلاد، يمكن لبعض الوظائف المختلفة في برنامج Excel أن تساعدنا في حساب عمر الشخص بالسنوات. دعونا نناقش كل صيغة عمرية مفيدة بالتفصيل:
باستخدام الدالة DATEDIF
تعد وظيفة DATEDIF في Excel هي الوظيفة الأكثر شيوعًا لحساب عمر الشخص. إنها وظيفة سهلة الاستخدام ومدمجة والأكثر ملائمة حيث تقبل تاريخ الميلاد كتاريخ إدخال وترجع عمر الشخص كبيانات مخرجة.
وبطريقة أخرى، تقوم الدالة DATEDIF عادةً بتحويل تاريخ الميلاد إلى عمر الشخص المقابل. الميزة الأساسية للدالة DATEDIF هي أنه يمكن استخدامها لحساب العمر بتنسيقات مختلفة، مثل السنوات فقط، أو الأشهر فقط، أو الأيام فقط، أو النموذج المدمج للسنوات والأشهر والتواريخ، وما إلى ذلك.
على عكس وظائف Excel الأخرى، لا تظهر الدالة DATEDIF في القائمة السريعة للوظائف. وهذا يعني أننا لا نرى دالة DATEDIF كاقتراح عندما نبدأ في كتابتها داخل خلية Excel بعد علامة المساواة. ومع ذلك، تعمل الوظيفة في كافة إصدارات Excel. يجب أن نعرف بناء الجملة والوسائط المطلوبة لاستخدام الدالة DATEDIF في Excel.
يتم تعريف بناء الجملة العام للدالة DATEDIF على النحو التالي:
=DATEDIF(start_date, end_date, unit)
كما هو موضح هنا، تتطلب الدالة الوسائط الثلاث التالية:
من المهم ملاحظة أن Y وM وD تُرجع الأرقام بالسنوات والأشهر والأيام الكاملة، على التوالي. في المقابل، تقوم YM بإرجاع فرق التاريخ بالأشهر فقط مع تجاهل الأيام والسنوات المقابلة، وتقوم MD بإرجاع فرق التاريخ بالأيام فقط مع تجاهل الأشهر والسنوات المرتبطة، وترجع YD فرق التاريخ بالأيام مع تجاهل السنوات المقابلة.
عند حساب العمر بالسنوات باستخدام الدالة DATEDIF، يمكن تعريف بناء الجملة الأكثر شيوعًا على النحو التالي:
=DATEDIF(Birth_Date,Specific_Date,'Y')
لحساب العمر من تاريخ الميلاد حتى تاريخه، يمكننا تحديد التاريخ الحالي مكان Special_Date. علاوة على ذلك، يمكننا أيضًا استخدام الدالة TODAY بدلاً من التاريخ الحالي. فيما يلي الصيغة المناسبة لحساب عمر الشخص بالسنوات حتى تاريخ اليوم:
=DATEDIF(Birth_Date,TODAY(),'Y')
لنفترض أن لدينا تاريخ ميلاد شخص ما في الخلية B2، ونحتاج إلى حساب العمر الحالي بالسنوات. بعد ذلك يمكننا استخدام مرجع تاريخ الميلاد في الصيغة الأخيرة بالطريقة التالية:
=DATEDIF(B2,TODAY(),'Y')
في بعض الأحيان، قد نرى تاريخًا محددًا بدلاً من العمر بالسنوات. في مثل هذه الحالة، يجب علينا التنقل في علامة التبويب الصفحة الرئيسية > القائمة المنسدلة تنسيق الأرقام > تحديد 'عام' بدلاً من 'التاريخ'.
باستخدام وظيفة YEARFRAC
هناك طريقة أخرى مفيدة لحساب العمر في برنامج Excel تتضمن استخدام الدالة YEARFRAC. إنها وظيفة Excel سهلة الاستخدام وغالبًا ما تُستخدم لحساب الأعمار بالسنوات. يساعدنا في استرداد العمر من تاريخ الميلاد المحدد إلى تاريخ محدد.
يتم تعريف بناء الجملة العام لحساب عمر الشخص باستخدام الدالة YEARFRAC على النحو التالي:
=YEARFRAC(Birth_Date,Specific_Date)
إذا أردنا حساب العمر منذ الميلاد وحتى تاريخ اليوم، فيمكننا تحديد التاريخ الحالي بدلاً من Special_Date. وبدلاً من ذلك، يمكننا أيضًا ربط الدالة YEARFRAC مع الدالة TODAY بالطريقة التالية:
=YEARFRAC(Birth_Date,TODAY())
بشكل افتراضي، تقوم الصيغة أعلاه بإرجاع النتائج بالأرقام العشرية. هذا لا يبدو جيدًا عند حساب عمر الشخص. لذلك، نقوم بدمج الصيغة أو تضمينها داخل الدالة INT لإرجاع العمر المقابل كعدد صحيح. وبالتالي، يتم تعريف الصيغة الكاملة لحساب العمر في Excel باستخدام الدالة YEARFRAC على النحو التالي:
=INT(YEARFRAC(تاريخ_الميلاد،التاريخ_الخاص))
لنفترض أن لدينا التاريخ الحالي في الخلية A2 وتاريخ ميلاد الشخص في الخلية B2. في هذه الحالة، يمكننا حساب عمر هذا الشخص المعين باستخدام الصيغة التالية:
=INT(YEARFRAC(B2,A2))
رد الفعل الجدول
إذا استخدمنا الدالة YEARFRAC مع الدالة TODAY لحساب العمر بالسنوات، فستبدو الصيغة كما يلي:
=INT(YEARFRAC(B2, TODAY()))
عند دمجها مع الدالة TODAY، تقوم الدالة YEARFRAC بإرجاع العمر الحالي أو الأحدث بالسنوات فقط.
استخدام وظيفة ROUNDDOWN
على الرغم من ندرة استخدامها، يمكننا أيضًا استخدام الدالة ROUNDDOWN لحساب العمر في Excel. فيما يلي بناء الجملة لحساب العمر في Excel باستخدام صيغة ROUNDDOWN:
=ROUNDDOWN((Specific_Date - Birth_Date)/365.25,0)
بشكل عام، تساعد الدالة ROUNDDOWN على تقريب المنازل العشرية للأسفل. ومع ذلك، فقد قمنا بتخصيص الصيغة بطريقة تحسب العمر بالسنوات. في الصيغة، نستخدم 365.25 للسنة الكبيسة (366 يومًا في السنة) التي تأتي كل أربع سنوات. نستخدم 0 كوسيطة أخيرة في الدالة ROUNDDOWN لتجاهل المنازل العشرية في العمر.
تعتبر صيغة ROUNDDOWN ممارسة جيدة لحساب العمر، ولكن لم يتم اقتراحها لأنها ليست خالية من العيوب. لنفترض أن الطفل لم يعش بعد أي سنة كبيسة، وقمنا بحساب العمر باستخدام هذه الصيغة مقسومًا على 365.25؛ ستُرجع الصيغة العمر الخطأ.
إن القسمة على متوسط عدد الأيام في السنة تعمل أيضًا بشكل جيد في معظم الحالات، مما يعني أنه يمكننا القسمة على 365 يومًا بدلاً من 365.25. ومع ذلك، فإن هذه الحالة أيضًا بها بعض المشكلات وتؤدي إلى نتائج خاطئة في بعض الأحيان. على سبيل المثال، لنفترض أن D.O.B لشخص ما. هو 29 فبراير، والتاريخ الحالي هو 28 فبراير. في هذه الحالة، إذا قسمنا على 365، فسيكون العمر المسترد بواسطة الصيغة أكبر بيوم واحد. لذلك، يجب علينا القسمة على 365.25 لحساب التاريخ في هذه الحالة. وبالتالي، فإن هذين النهجين ليسا مثاليين. يوصى دائمًا باستخدام الدالة DATEDIF لحساب عمر الشخص في Excel.
لنفترض أن لدينا التاريخ الحالي في الخلية A2 وD.O.B لشخص ما. في الخلية B2. في هذه الحالة، يمكننا حساب عمر هذا الشخص المعين باستخدام الصيغة التالية:
=ROUNDDOWN((A2-B2)/365.25,0)
بالإضافة إلى ذلك، يمكننا أيضًا استخدام الدالة TODAY بدلاً من Special_Date لحساب عمر الشخص حتى التاريخ الحالي.
استخدام الدالة TODAY
نظرًا لأن العمر يتم حسابه بشكل شائع عن طريق طرح تاريخ الميلاد من التاريخ الحالي، فإن وظيفة TODAY في Excel تساعدنا أيضًا في حساب العمر إلى حد ما. مثل صيغة ROUNDDOWN، فإن صيغة TODAY أيضًا ليست مثالية لحساب عمر شخص ما في Excel.
لنفترض أن لدينا تاريخ ميلاد شخص ما في الخلية B2؛ يمكننا تطبيق معادلة TODAY لحساب العمر بالطريقة التالية:
=(اليوم()-B2)/365
بناءً على حالات معينة، قد نحتاج أحيانًا إلى القسمة على 365.25 بدلاً من 365. في هذه الصيغة، عادةً ما يحسب الجزء الأول (TODAY()-B2) الفرق بين التاريخ الحالي وتاريخ الميلاد. يساعد الجزء الثاني من الصيغة على قسمة الفرق على 365 للحصول على عدد السنوات (أي العمر بالسنوات).
لسوء الحظ، توفر صيغة TODAY هنا النتائج بالكسور العشرية، تمامًا مثل الدالة YEARFRAC. لذلك، قمنا بإدراج صيغة TODAY داخل الدالة INT لعرض العمر بالسنوات الكاملة أو أقرب قيمة عددية. لذا، فإن صيغة الإجمالي النهائية لحساب العمر بالسنوات هي كما يلي:
=INT((TODAY()-B2)/365)
حساب العمر بالأشهر
كما قلنا سابقًا، يمكن أن تساعدنا الدالة DATEDIF في حساب عمر شخص ما بتنسيقات مختلفة. لذا، يمكننا استخدام نفس صيغة DATEDIF مرة أخرى في ورقة العمل الخاصة بنا. ومع ذلك، يجب علينا تغيير الوحدة في الصيغة من 'Y' إلى 'M'. يؤدي ذلك إلى إعلام Excel بعرض العمر أو إرجاعه بالأشهر.
دعونا نعيد النظر في نفس مجموعة البيانات النموذجية حيث لدينا تاريخ ميلاد شخص ما في الخلية B2. نحن بحاجة لحساب العمر الحالي بالأشهر. بعد ذلك، يمكننا استخدام مرجع تاريخ الميلاد في صيغة DATEDIF بالطريقة التالية:
=DATEDIF(B2,TODAY(),'M')
حساب العمر بالأيام
يصبح حساب العمر بالأيام أمرًا سهلاً عندما نعرف بالفعل بناء جملة الدالة DATEDIF. كما في المثال السابق، قمنا بتغيير وسيطة الوحدة من 'Y' إلى 'M'، مما ساعدنا في حساب عمر الشخص بالأشهر. وبالمثل، إذا قمنا بتغيير وسيطة الوحدة من 'M' إلى 'D'، فستعيد الدالة العمر بالأيام. وبالتالي، مع الأخذ في الاعتبار أنه إذا كان D.O.B. في الخلية B2، ستكون الصيغة كما يلي:
=DATEDIF(B2,TODAY(),'D')
حساب العمر بالسنوات والأشهر والأيام معًا
كما نوقش أعلاه، فإن حساب عمر الشخص بالسنوات والأشهر والأيام الفردية أمر سهل بشكل مدهش. ومع ذلك، قد لا يكون هذا كافيا دائما. قد تكون هناك حالات يتعين علينا فيها إيجاد أو حساب العمر الدقيق للشخص بالسنوات والأشهر والأيام تمامًا. في مثل هذه الحالات، تصبح الصيغة طويلة بعض الشيء ولكنها لا تزال سهلة.
لحساب العمر الدقيق للشخص بالسنوات والأشهر والأيام الكاملة، يجب علينا استخدام ثلاث دوال DATEDIF مختلفة ودمجها في صيغة في وقت واحد. لنفترض أنه إذا كان تاريخ ميلاد الشخص موجودًا في الخلية B2، فستكون وظائف DATEDIF الثلاث المختلفة كما يلي:
- لحساب أعداد السنوات الكاملة: =DATEDIF(B2,TODAY(),'Y')
- لحساب أعداد الأشهر المتبقية: =DATEDIF(B2,TODAY(),'YM')
- لحساب عدد الأيام المتبقية: =DATEDIF(B2,TODAY(),'MD')
نقوم الآن بدمج كل وظائف DATEDIF هذه باستخدام عامل التشغيل '&' بالطريقة التالية:
=DATEDIF(B2,TODAY(),'Y')&DATEDIF(B2,TODAY(),'YM')&DATEDIF(B2,TODAY(),'MD')
على الرغم من أننا نحصل على العمر بالسنوات والأشهر والتواريخ كسلسلة واحدة، إلا أنه ليس له معنى. ولجعل النتائج (أو العمر) فعالة أو مفهومة، نقوم بفصل كل وحدة باستخدام الفاصلة وتحديد معنى كل قيمة. لذلك تصبح الصيغة كما يلي:
=DATEDIF(B2,TODAY(),'Y') & ' سنوات, ' & DATEDIF(B2,TODAY(),'YM') & ' أشهر, ' & DATEDIF(B2,TODAY(),'MD') & ' أيام'
جافا استبدال الحرف في السلسلة
توضح الصورة أعلاه أن النتائج العمرية أكثر أهمية نسبيًا من النتائج السابقة. ومع ذلك، فإنه يعرض أيضًا بعض القيم الصفرية. يمكننا تحسين صيغة DATEDIF بشكل أكبر من خلال دمجها ضمن عبارات IF الثلاثة المختلفة للتحقق من الأصفار وإزالتها. لذلك، تصبح صيغة العمر النهائية لبرنامج Excel لحساب العمر الحالي لشخص ما بالسنوات والأشهر والأيام كما يلي:
=IF(DATEDIF(B2, TODAY(),'Y')=0,'',DATEDIF(B2, TODAY(),'Y')&' السنوات, ')& IF(DATEDIF(B2, TODAY(), 'YM')=0,'',DATEDIF(B2, TODAY(),'YM')&' الأشهر, ')& IF(DATEDIF(B2, TODAY(),'MD')=0,'',DATEDIF (B2، TODAY()،'MD')&'أيام')
في الصورة أعلاه، نرى فقط قيمًا غير صفرية لعمر الشخص. ومع ذلك، فإن الصيغة تعثر فقط على العمر الحالي للشخص.
حساب العمر في أي تاريخ محدد/محدد
في بناء جملة الصيغ أعلاه، ناقشنا بالفعل الطريقة التي تخبرنا بالعثور على عمر شخص ما في تاريخ محدد. على سبيل المثال، تقوم صيغة DATEDIF أدناه بحساب عمر الشخص في تاريخ محدد:
=DATEDIF(Birth_Date,Specific_Date,'Y')
يمكن تغيير وسيطة الوحدة كما هو مطلوب. في الصيغة أعلاه، يمكننا عادةً توفير مرجع الخلية لكلا التاريخين، وستظهر النتيجة في الخلية الوجهة.
هناك طريقة نموذجية أخرى لاستخدام نفس الصيغة لحساب عمر الشخص في أي تاريخ محدد وهي توفير التاريخ المطلوب مباشرة في الصيغة. على سبيل المثال، لنفترض أن لدينا تاريخ ميلاد شخص ما (30/05/1995) في الخلية B2، ونريد معرفة عمر الشخص في 01/01/2021. لذلك، يمكننا استخدام الدالة DATEDIF بالتزامن مع الدالة DATE بالطريقة التالية:
=DATEDIF(B2,DATE(2021,1,1),'Y')
في الصورة أعلاه، نستخدم الدالة DATE لتوفير التاريخ المحدد مباشرة في الدالة DATEDIF، بينما يتم استخدام المرجع B2 لـ D.O.B.
بصرف النظر عن هذا، إذا أردنا العثور على تاريخ الشخص بالسنوات والأشهر والأيام في تاريخ معين، فيمكننا استخدام نفس مفهوم DATEDIF الذي تمت مناقشته أعلاه حيث قمنا بدمج ثلاث وظائف DATEDIF. ومع ذلك، يجب علينا استبدال الدالة TODAY() في الوسيطة الثانية بالتاريخ المطلوب.
لذلك، عندما يكون تاريخ ميلاد الشخص في الخلية B2 ونحتاج إلى حساب العمر اعتبارًا من 01/01/2021، نستخدم الصيغة التالية:
=IF(DATEDIF(B2, '1/1/2021','Y')=0,'',DATEDIF(B2, '1/1/2021','Y')&' السنوات, ')& IF( DATEDIF(B2, '1/1/2021','YM')=0,'',DATEDIF(B2, '1/1/2021','YM')&' أشهر, ')& IF(DATEDIF(B2 , '1/1/2021','MD')=0,'',DATEDIF(B2, '1/1/2021','MD')&' أيام')
بدلاً من وضع تاريخ محدد في الصيغة، يمكننا استخدام مرجع خلية للتاريخ المحدد وجعل صيغتنا سهلة الفهم. لنفترض أن تاريخ ميلاد الشخص موجود في الخلية B2، والتاريخ المحدد الذي نريد حساب العمر فيه موجود في الخلية C2، فإن صيغة العمر المرنة ستكون كما يلي:
=IF(DATEDIF(B2, C2,'Y')=0,'',DATEDIF(B2, C2,'Y')&' السنوات, ')& IF(DATEDIF(B2, C2,'YM')=0 ,'',DATEDIF(B2, C2,'YM')&' الأشهر, ')& IF(DATEDIF(B2, C2,'MD')=0,'',DATEDIF(B2, C2,'MD')& ' أيام')
