MS Excel, або Excel, широко використовуване програмне забезпечення для роботи з електронними таблицями з широким набором вбудованих інструментів і функцій. Це допомагає нам записувати різні набори даних і виконувати обчислення в них із кількома клітинками. Прикладом класичного розрахунку є обчислення різниці між двома датами. Однак це не звучить як корисний розрахунок. Але та сама техніка є певною мірою важливою для пошуку або обчислення віку когось чи чогось у Microsoft Excel.
Окрім визначення віку будь-якої особи, ми можемо використовувати те саме поняття для розрахунку тривалості будь-якого проекту, років існування будь-якої компанії, кількості років, що минули між вказаними датами тощо.
У цьому підручнику ми обговорюємо різні методи або рішення, як обчислити вік у 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(Дата_народження;Конкретна_Дата;'Y')
Щоб обчислити вік від дати народження до дати, ми можемо вказати поточну дату замість Specific_Date. Крім того, ми також можемо використовувати функцію СЬОГОДНІ замість поточної дати. Ось відповідна формула для розрахунку віку людини в роках до сьогоднішньої дати:
=DATEDIF(Дата_народження,СЬОГОДНІ(),'Y')
Припустімо, у клітинці B2 є дата народження людини, і нам потрібно обчислити поточний вік у роках. Тоді ми можемо використати посилання на дату народження в останній формулі таким чином:
=DATEDIF(B2,TODAY(),'Y')
Іноді ми можемо побачити конкретну дату замість віку в роках. У такому випадку ми повинні перейти на вкладку «Домашня сторінка» > спадне меню «Формат числа» > вибрати «Загальні» замість «Дата».
Використання функції YEARFRAC
Інший корисний метод обчислення віку в Excel передбачає використання функції YEARFRAC. Це проста у використанні функція Excel, яка часто використовується для обчислення віку в роках. Це допомагає нам отримати вік від заданої дати народження до вказаної дати.
Нижче наведено загальний синтаксис для обчислення віку людини за допомогою функції YEARFRAC:
=YEARFRAC(Birth_Date,Specific_Date)
Якщо нам потрібно обчислити вік від народження до сьогоднішньої дати, ми можемо вказати поточну дату замість Specific_Date. Крім того, ми також можемо об’єднати функцію YEARFRAC із функцією TODAY у такий спосіб:
=YEARFRAC(Дата_народження,СЬОГОДНІ())
За замовчуванням наведена вище формула повертає результати в десяткових числах. Це виглядає не дуже добре, якщо обчислити вік людини. Отже, ми об’єднуємо або вкладаємо формулу у функцію INT, щоб повернути відповідний вік як ціле число. Таким чином, повна формула для розрахунку віку в Excel за допомогою функції YEARFRAC визначається так:
=INT(YEARFRAC(Дата_народження;Конкретна_дата))
Припустимо, ми маємо поточну дату в клітинці A2 і дату народження людини в клітинці B2. У такому випадку ми можемо розрахувати вік конкретної людини за наведеною нижче формулою:
=INT(YEARFRAC(B2;A2))
java викидає виняток
Якщо ми використовуємо функцію YEARFRAC із функцією TODAY для обчислення віку в роках, формула виглядатиме так:
=INT(YEARFRAC(B2, СЬОГОДНІ()))
У поєднанні з функцією 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. У такому випадку ми можемо розрахувати вік конкретної людини за наведеною нижче формулою:
=ОКРУГЛИЙ ВНИЗ((A2-B2)/365,25;0)
Крім того, ми також можемо використовувати функцію TODAY замість Specific_Date, щоб обчислити вік людини до поточної дати.
Використання функції СЬОГОДНІ
Оскільки вік найчастіше обчислюється шляхом віднімання дати народження від поточної дати, функція СЬОГОДНІ в Excel також допомагає нам певною мірою обчислити вік. Як і формула ROUNDDOWN, формула TODAY також не ідеальна для обчислення чийогось віку в Excel.
Припустімо, що ми маємо дату народження людини в клітинці B2; ми можемо застосувати формулу TODAY для розрахунку віку таким чином:
=(СЬОГОДНІ()-B2)/365
У деяких випадках нам може знадобитися поділити на 365,25 замість 365. У цій формулі перша частина (СЬОГОДНІ()-B2) зазвичай обчислює різницю між поточною датою та датою народження. Друга частина формули допомагає розділити різницю на 365, щоб отримати кількість років (тобто вік у роках).
На жаль, формула TODAY тут надає результати в десяткових дробах, як і функція YEARFRAC. Тому ми вкладаємо формулу TODAY у функцію INT, щоб відобразити вік у цілих роках або найближче ціле значення. Отже, остаточна ЗАГАЛЬНА формула для розрахунку віку в роках виглядає наступним чином:
=INT((СЬОГОДНІ()-B2)/365)
Розрахунок віку в місяцях
Як ми вже говорили раніше, функція DATEDIF може допомогти нам обчислити чийсь вік у різних форматах. Отже, ми можемо знову використати ту саму формулу DATEDIF на нашому аркуші. Однак ми повинні змінити одиницю у формулі з «Y» на «M». Це вказує Excel відображати або повертати вік у місяцях.
Давайте ще раз розглянемо той самий приклад набору даних, у якому в комірці B2 є чиясь дата народження. Нам потрібно обчислити поточний вік у місяцях. Тоді ми можемо використати посилання на дату народження у формулі DATEDIF таким чином:
=DATEDIF(B2;СЬОГОДНІ();'M')
Розрахунок віку в днях
Розрахувати вік у днях стає легко, коли ми вже знаємо синтаксис функції DATEDIF. Як і в попередньому прикладі, ми змінили аргумент одиниці з «Y» на «M», що допомогло нам обчислити вік людини в місяцях. Так само, якщо ми змінимо аргумент одиниці з «M» на «D», функція поверне вік у днях. Таким чином, враховуючи, що якщо D.O.B. у комірці B2, формула матиме такий вигляд:
=DATEDIF(B2;СЬОГОДНІ(),'D')
Розрахунок віку в роках, місяцях і днях разом
Як обговорювалося вище, обчислити вік людини в окремих роках, місяцях і днях напрочуд легко. Однак цього не завжди може бути достатньо. Бувають випадки, коли нам потрібно знайти або обчислити точний вік людини в роках, місяцях і днях разом. У таких випадках формула стає трохи довгою, але все одно легкою.
Щоб обчислити точний вік людини в цілих роках, місяцях і днях, ми повинні використовувати три різні функції DATEDIF і об’єднати їх у формулу одночасно. Припустімо, якщо дата народження людини вказана в клітинці B2, три різні функції DATEDIF будуть такими:
- Щоб обчислити кількість повних років: =DATEDIF(B2,TODAY(),'Y')
- Щоб обчислити кількість місяців, що залишилися: =DATEDIF(B2,TODAY(),'YM')
- Щоб обчислити кількість днів, що залишилися: =DATEDIF(B2,TODAY(),'MD')
Тепер ми об’єднуємо всі ці функції DATEDIF за допомогою оператора «&» таким чином:
python друкує до 2 знаків після коми
=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')&' Months, ')& IF(DATEDIF(B2, TODAY(),'MD')=0,'',DATEDIF (B2, СЬОГОДНІ(),'MD')&'Дні')
На зображенні вище ми бачимо лише ненульові значення віку людини. Однак формула визначає лише поточний вік людини.
Розрахунок віку на будь-яку конкретну/конкретну дату
У синтаксисі формул вище ми вже обговорювали спосіб визначення віку людини на певну дату. Наприклад, наведена нижче формула DATEDIF обчислює вік людини на певну дату:
=DATEDIF(Дата_народження;Конкретна_Дата;'Y')
Аргумент одиниці можна змінити за потреби. У наведеній вище формулі ми зазвичай можемо надати посилання на клітинку для обох дат, і результат з’явиться в клітинці призначення.
Іншим типовим методом використання тієї самої формули для розрахунку віку людини на будь-яку конкретну дату є введення потрібної дати безпосередньо у формулу. Наприклад, припустімо, що у клітинці B2 є дата народження людини (30/05/1995), і ми хочемо знати вік людини на 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')& «Дні»)
