MS Excel, או Excel, היא תוכנת גיליונות אלקטרוניים בשימוש נרחב עם מגוון רחב של כלים ותכונות מובנים. זה עוזר לנו להקליט מערכי נתונים שונים ולבצע חישובים על פניהם עם מספר תאים. דוגמה לחישוב קלאסי היא חישוב ההפרש בין שני תאריכים. עם זאת, זה לא נשמע כמו חישוב שימושי. אבל, אותה טכניקה היא חיונית במקצת למציאת או חישוב גילו של מישהו או משהו ב-Microsoft Excel.
מלבד מציאת גילו של כל אדם, אנו יכולים להשתמש באותו קונספט לחישוב משך כל פרויקט, שנות קיומה של כל חברה, מספר השנים שחלפו בין התאריכים שצוינו וכו'.
במדריך זה, אנו דנים בשיטות או פתרונות שונים כיצד לחשב גיל באקסל. המדריך עוזר לנו ללמוד לחשב גילאים כמספרים של שנים, חודשים וימים שלמים בתאריך הנוכחי או בכל תאריך ספציפי.
כיצד נוכל לחשב את הגיל באקסל?
למרות שאין פונקציה ספציפית באקסל לחישוב גיל, יש לנו דרכים רבות לחשב גיל בצורה שונה בתרחישים שונים. עם זאת, עלינו לדעת את תאריך הלידה המקורי של הפרט (D.O.B.). לאחר מכן אנו מספקים את תאריך הלידה בשילוב עם פונקציות האקסל, במיוחד DATEDIF ו-TODAY, כדי לחשב את הגיל או להבדיל בין תאריכים. הבה נדון כעת בתרחישים הנפוצים של מציאת הגיל:
חישוב גיל בשנים
בדרך כלל אנו לוקחים בחשבון מספר גורמים בזמן חישוב גילו של כל אדם. לדוגמה, ייתכן שנצטרך לחשב את הגיל בשנים, חודשים, ימים או כל אלה יחד. עם זאת, חישוב גילו של אדם בשנים היא המשימה הנפוצה ביותר של אקסל.
בהנחה שאנו יודעים את תאריך הלידה, כמה פונקציות שונות באקסל יכולות לעזור לנו לחשב את גילו של אדם בשנים. הבה נדון בפירוט בכל נוסחת גיל שימושית:
שימוש בפונקציית DATEDIF
הפונקציה DATEDIF באקסל היא הפונקציה הנפוצה ביותר לחישוב גילו של אדם. זוהי פונקציה קלה לשימוש, מובנית והמתאימה ביותר שמקבלת את תאריך הלידה כתאריך קלט ומחזירה את גיל האדם כנתוני פלט.
בדרך אחרת, הפונקציה DATEDIF ממירה בדרך כלל את תאריך הלידה לגילו של האדם המתאים. היתרון העיקרי של הפונקציה DATEDIF הוא שניתן להשתמש בה לחישוב גיל בפורמטים שונים, כגון שנים בלבד, חודשים בלבד, ימים בלבד, או הצורה המשולבת של שנים, חודשים ותאריכים וכו'.
שלא כמו שאר הפונקציות של Excel, הפונקציה DATEDIF לא מופיעה ברשימת הפונקציות המהירה. זה אומר שאנחנו לא רואים את פונקציית DATEDIF כהצעה כשאנחנו מתחילים להקליד אותה בתוך תא Excel אחרי סימן שוויון. עם זאת, הפונקציה פועלת בכל הגירסאות של Excel. עלינו לדעת את התחביר ואת הארגומנטים הנדרשים כדי להשתמש בפונקציה DATEDIF באקסל.
התחביר הגנרי של הפונקציה DATEDIF מוגדר כדלקמן:
=DATEDIF(start_date, end_date, unit)
כפי שמוצג כאן, הפונקציה דורשת את שלושת הארגומנטים הבאים:
חשוב לציין ש-Y, M ו-D מחזירים את המספרים בשנים, חודשים וימים שלמים, בהתאמה. לעומת זאת, י'מ מחזירה רק את הפרש התאריכים בחודשים תוך התעלמות מהימים והשנים המקבילים, מ'ד מחזירה רק את הפרש התאריכים בימים תוך התעלמות מהחודשים והשנים הקשורים, וי'ד מחזירה את הפרש התאריכים בימים תוך התעלמות מהשנים המתאימות.
בעת חישוב הגיל בשנים באמצעות הפונקציה DATEDIF, ניתן להגדיר את התחביר המוכר יותר כ:
=DATEDIF(Birth_Date,Specific_Date,'Y')
כדי לחשב את הגיל מתאריך הלידה ועד לתאריך, נוכל לציין את התאריך הנוכחי במקום Specific_Date. חוץ מזה, אנחנו יכולים גם להשתמש בפונקציה TODAY במקום התאריך הנוכחי. להלן הנוסחה המתאימה לחישוב גילו של אדם בשנים עד תאריך היום:
=DATEDIF(Birth_Date,TODAY(),'Y')
נניח שיש לנו תאריך לידה של אדם בתא B2, ועלינו לחשב את הגיל הנוכחי בשנים. לאחר מכן, נוכל להשתמש בהתייחסות של תאריך הלידה בנוסחה האחרונה באופן הבא:
=DATEDIF(B2,TODAY(),'Y')
לפעמים, אנו עשויים לראות תאריך ספציפי במקום הגיל בשנים. במקרה כזה, עלינו לנווט בלשונית בית > תבנית מספרים הנפתחת > לבחור 'כללי' במקום 'תאריך'.
שימוש בפונקציית YEARFRAC
שיטה שימושית נוספת לחישוב הגיל באקסל כוללת שימוש בפונקציה YEARFRAC. זוהי פונקציית Excel קלה לשימוש ומשמשת לעתים קרובות לחישוב גילאים בשנים. זה עוזר לנו לאחזר את הגיל מתאריך הלידה הנתון לתאריך מוגדר.
התחביר הגנרי לחישוב הגיל של אדם המשתמש בפונקציה YEARFRAC מוגדר כדלקמן:
=YEARFRAC(Birth_Date,Specific_Date)
אם אנחנו צריכים לחשב את הגיל מהלידה ועד התאריך של היום, נוכל לציין את התאריך הנוכחי במקום Specific_Date. לחלופין, אנו יכולים גם להצטרף לפונקציית YEARFRAC עם הפונקציה TODAY באופן הבא:
=YEARFRAC(Birth_Date,TODAY())
כברירת מחדל, הנוסחה לעיל מחזירה את התוצאות במספרים עשרוניים. זה לא נראה טוב כשמחשבים את גילו של אדם. לכן, אנו משלבים או מקיפים את הנוסחה בתוך הפונקציה INT כדי להחזיר את הגיל המתאים כמספר שלם. לפיכך, הנוסחה המלאה לחישוב הגיל באקסל באמצעות הפונקציה YEARFRAC מוגדרת כדלקמן:
=INT(YEARFRAC(Birth_Date,Specific_Date))
נניח שיש לנו את התאריך הנוכחי בתא A2 ואת תאריך הלידה של האדם בתא B2. במקרה זה, נוכל לחשב את גילו של אותו אדם מסוים באמצעות הנוסחה שלהלן:
=INT(YEARFRAC(B2,A2))
כתב כתיבה עבור לולאה
אם נשתמש בפונקציה YEARFRAC עם הפונקציה TODAY כדי לחשב גיל בשנים, הנוסחה נראית כך:
=INT(YEARFRAC(B2, TODAY()))
בשילוב עם הפונקציה TODAY, הפונקציה YEARFRAC מחזירה רק את הגיל הנוכחי או האחרון בשנים.
שימוש בפונקציית ROUNDDOWN
למרות שמשתמשים בהם לעתים רחוקות, אנו יכולים גם להשתמש בפונקציה ROUNDDOWN כדי לחשב גיל באקסל. להלן התחביר לחישוב גיל ב- 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 לחישוב גילו של אדם באקסל מומלץ תמיד.
נניח שיש לנו את התאריך הנוכחי בתא A2 ואת ה-D.O.B של מישהו. בתא B2. במקרה זה, נוכל לחשב את גילו של אותו אדם מסוים באמצעות הנוסחה שלהלן:
=ROUNDDOWN((A2-B2)/365.25,0)
בנוסף, נוכל גם להשתמש בפונקציה TODAY במקום Specific_Date כדי לחשב את גילו של האדם עד לתאריך הנוכחי.
שימוש בפונקציית TODAY
מכיוון שהגיל לרוב מחושב על ידי הפחתת תאריך הלידה מהתאריך הנוכחי, הפונקציה TODAY באקסל גם עוזרת לנו לחשב את הגיל במידה מסוימת. כמו נוסחת ה-ROUNDDOWN, גם הנוסחה TODAY אינה מושלמת לחישוב גילו של מישהו באקסל.
נניח שיש לנו תאריך לידה של מישהו בתא B2; אנו יכולים ליישם את נוסחת TODAY לחישוב גיל באופן הבא:
=(TODAY()-B2)/365
בהתבסס על מקרים מסוימים, ייתכן שנצטרך לפעמים לחלק ב-365.25 במקום ב-365. בנוסחה זו, החלק הראשון (TODAY()-B2) מחשב בדרך כלל את ההפרש בין התאריך הנוכחי לתאריך הלידה. החלק השני של הנוסחה עוזר לחלק את ההפרש ב-365 כדי לקבל את מספר השנים (כלומר, גיל בשנים).
למרבה הצער, נוסחת TODAY כאן מספקת את התוצאות בעשרוניות, בדיוק כמו הפונקציה YEARFRAC. לכן, אנו מקיפים את נוסחת TODAY בתוך הפונקציה INT כדי להציג את הגיל בשנים שלמות או את הערך השלם הקרוב ביותר. אז, הנוסחה הסופית של TOTAL לחישוב גיל בשנים הולכת כדלקמן:
=INT((TODAY()-B2)/365)
חישוב גיל בחודשים
כפי שאמרנו קודם, הפונקציה DATEDIF יכולה לעזור לנו לחשב את הגיל של מישהו בפורמטים שונים. אז, אנחנו יכולים להשתמש באותה נוסחת DATEDIF שוב בגליון העבודה שלנו. עם זאת, עלינו לשנות את היחידה בנוסחה מ-'Y' ל-'M'. זה אומר לאקסל להציג או להחזיר גיל בחודשים.
הבה נשקול מחדש את אותו מערך נתונים לדוגמה שבו יש לנו תאריך לידה של מישהו בתא B2. אנחנו צריכים לחשב את הגיל הנוכחי בחודשים. לאחר מכן, נוכל להשתמש בהתייחסות של תאריך הלידה בנוסחת DATEDIF באופן הבא:
הדפס java
=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')
ניתן לשנות את ארגומנט היחידה לפי הצורך. בנוסחה לעיל, בדרך כלל נוכל לספק את הפניה לתא עבור שני התאריכים, והתוצאה תופיע בתא היעד.
שיטה טיפוסית נוספת להשתמש באותה נוסחה כדי לחשב את גילו של האדם בכל תאריך ספציפי היא לספק את התאריך הרצוי ישירות בנוסחה. לדוגמה, נניח שיש לנו את ה-D.O.B של מישהו (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')& 'ימים')
