В Microsoft Excel нет специальной встроенной функции чтобы посчитать возраст по дате рождения, однако существует множество различных методов, позволяющих определить его на основе даты рождения. В этом руководстве мы рассмотрим несколько способов, как вычислить возраст в Excel в полных годах, а также в годах, месяцах и днях на текущий момент или на определенную дату. Мы подробно объясним преимущества и недостатки каждого метода, покажем, как быстрое и точно сделать расчет возраста в Excel.
В повседневной жизни на вопрос «Сколько вам лет?» обычно отвечают количеством полных прожитых лет. В Microsoft Excel вы можете создать формулу, которая не только исчислит возраст в годах, но и точно определит продолжительность жизни в месяцах, днях, часах и даже минутах. Однако, следуя традициям, начнем с вычисления возраста в полных годах.
- Расчет возраста в годах: базовая формула
- Расчет возраста с помощью функции ДОЛЯГОДА
- Как вычислить возраст в Excel по дате рождения с помощью функции РАЗНДАТ
- Расчет возраста по дате рождения в Excel в годах, месяцах и днях
- Вычисление возраста на определенную дату
- Расчет возраста в определенном году
- Определение даты, когда человеку исполнится N лет
- Ответы на часто задаваемые вопросы
Расчет возраста в годах: базовая формула
Как обычно определяется возраст? Просто вычитаем дату рождения из текущей даты. Этот классический подход можно использовать и в Excel.
Предположим, что дата рождения находится в ячейке B2. Тогда чтобы вычислить возраст в годах, используйте следующую формулу:
=(СЕГОДНЯ()-B2)/365
В этой формуле первая часть (СЕГОДНЯ()-B2) вычисляет разницу между сегодняшней датой и датой рождения в днях. Затем, разделив полученное значение на 365, вы получаете в Excel возраст в годах по дате рождения.
Округление до полного количества лет
Формула проста и понятна, однако она возвращает дробное число. Чтобы получить количество полных лет, необходимо округлить результат до ближайшего целого. Вот несколько способов сделать это:
Округление вниз с помощью функции ЦЕЛОЕ:
=ЦЕЛОЕ((СЕГОДНЯ()-B2)/365)Функция ЦЕЛОЕ округляет число вниз до ближайшего целого.
Округление по математическим правилам с функцией ОКРУГЛ:
=ОКРУГЛ((СЕГОДНЯ()-B3)/365; 0)ОКРУГЛ округляет число по общим математическим правилам, где 0 означает количество десятичных знаков после запятой.
Округление в меньшую сторону с функцией ОКРУГЛВНИЗ:
=ОКРУГЛВНИЗ((СЕГОДНЯ()-B4)/365; 0)ОКРУГЛВНИЗ всегда округляет число вниз.
Отбрасывание дробной части с помощью функции ОТБР:
=ОТБР((СЕГОДНЯ()-B5)/365; 0)Функция ОТБР просто удаляет дробную часть числа.
Во всех этих формулах параметр 0 указывает на то, что в результате не должно быть десятичных знаков.
Недостатки данного метода
Какой расчет возраста в Excel дает приблизительные результаты, и не всегда точные. Деление на среднее количество дней в году (365) работает в большинстве случаев, однако может привести к ошибкам. Например, для тех, кто родился 29 февраля, данный метод будет неточным при расчете в невисокосный год.
Можно использовать знаменатель 365,25, учитывая високосные годы, но и этот подход не идеален. Например, при расчете возраста ребенка, который еще не прожил високосный год, результат будет неверным.
В целом, вычитание даты рождения из текущей даты подходит для общих расчетов, но не является самым точным методом считать возраст по дате рождения в Excel. Далее мы рассмотрим специальные функции, которые позволяют безошибочно вычислять возраст независимо от года рождения.
Расчет возраста с помощью функции ДОЛЯГОДА
Более надежным способом является использование функции ДОЛЯГОДА (YEARFRAC в английской версии Excel), которая возвращает дробную часть года между двумя датами.
Синтаксис функции:
=ДОЛЯГОДА(дата_начала; дата_окончания; [базис])Дата_начала: это дата рождения.
Дата_окончания: текущая дата или другая заданная дата.
Базис: метод подсчета дней. Рекомендуется использовать 1 для наиболее точного расчета (фактическое количество дней в месяце и в году).
Формула для расчета возраста:
=ДОЛЯГОДА(B2; СЕГОДНЯ(); 1)
Эта формула вычисляет точную дробную часть года между датой рождения в ячейке B2 и текущей датой.
Как и в предыдущем случае, результат будет дробным числом. Чтобы получить количество полных лет, используйте одну из функций округления:
Округление вниз:
=ЦЕЛОЕ(ДОЛЯГОДА(B2; СЕГОДНЯ(); 1))Округляем по математическим правилам:
=ОКРУГЛ(ДОЛЯГОДА(B3; СЕГОДНЯ(); 1); 0)Округление в меньшую сторону:
=ОКРУГЛВНИЗ(ДОЛЯГОДА(B4; СЕГОДНЯ(); 1); 0)Отбрасывание дробной части:
=ОТБР(ДОЛЯГОДА(B5; СЕГОДНЯ(); 1); 0)Выберите метод округления по вашему усмотрению.
Как вычислить возраст в Excel по дате рождения с помощью функции РАЗНДАТ
Еще одним эффективным способом является использование функции РАЗНДАТ (DATEDIF), чтобы вычислить разницу между двумя датами в различных единицах измерения.
Синтаксис функции:
=РАЗНДАТ(дата_нач; дата_кон; единица)Дата_нач: дата начала (дата рождения).
Дата_кон: дата окончания (текущая дата или другая заданная).
Единица: единица измерения разницы:
- «Y» – полные годы.
- «M» – полные месяцы.
- «D» – дни.
- «YM» – месяцы, игнорируя годы.
- «MD» – дни, игнорируя месяцы и годы.
- «YD» – дни, игнорируя годы.
Как вычислить возраст в Excel в полных годах:
=РАЗНДАТ(B2; СЕГОДНЯ(); "Y")
Дополнительное округление не требуется, так как в этом случае функция сразу возвращает количество полных лет.
Ещё больше примеров формул с этой функцией смотрите здесь: Разность дат и количество дней между датами в Excel.
Расчет возраста по дате рождения в Excel в годах, месяцах и днях
Иногда требуется знать не только количество полных лет, но и месяцы с днями. Для этого используем комбинацию функции РАЗНДАТ:
=РАЗНДАТ(B2; СЕГОДНЯ(); "Y") & " лет, " & РАЗНДАТ(B2; СЕГОДНЯ(); "YM") & " мес., " & РАЗНДАТ(B2; СЕГОДНЯ(); "MD") & " дн."Где B2 – ячейка с датой рождения.
Если вы хотите получить более изящный вывод с учетом склонений, используйте такую формулу:
=ЕСЛИ(РАЗНДАТ(B2; СЕГОДНЯ(); "Y"); РАЗНДАТ(B2; СЕГОДНЯ(); "Y") & " " & ТЕКСТ(РАЗНДАТ(B2; СЕГОДНЯ(); "Y"); "года;лет;лет") & " "; "") & ЕСЛИ(РАЗНДАТ(B2; СЕГОДНЯ(); "YM"); РАЗНДАТ(B2; СЕГОДНЯ(); "YM") & " " & ТЕКСТ(РАЗНДАТ(B2; СЕГОДНЯ(); "YM"); "месяц;месяца;месяцев") & " "; "") & ЕСЛИ(РАЗНДАТ(B2; СЕГОДНЯ(); "MD"); РАЗНДАТ(B2; СЕГОДНЯ(); "MD") & " " & ТЕКСТ(РАЗНДАТ(B2; СЕГОДНЯ(); "MD"); "день;дня;дней"); "")
Вычисление возраста на определенную дату
Если нужно сделать расчёт возраста в Excel на конкретную дату, то вместо функции СЕГОДНЯ() используйте ссылку на ячейку с желаемой датой. Например, если дата указана в ячейке C3:
=РАЗНДАТ(B2; C3; "Y") & " лет, " & РАЗНДАТ(B2; C3; "YM") & " мес., " & РАЗНДАТ(B2; C3; "MD") & " дн."Это особенно полезно для вычисления стажа работы или возраста на определенный момент времени.
Расчет возраста в определенном году
Когда точная дата неизвестна, и у вас есть только год, можно использовать следующую формулу. Предположим, что год указан в ячейке C2:
=РАЗНДАТ(B2; ДАТА(C2; 1; 1); "Y")
Функция ДАТА(C2; 1; 1) создает дату 1 января указанного года, и функция РАЗНДАТ вычисляет возраст на эту дату.
Определение даты, когда человеку исполнится N лет
Если вы хотите узнать, когда человеку исполнится определенное количество лет, например, 50, используйте следующую формулу:
=ДАТА(ГОД(B2) + 50; МЕСЯЦ(B2); ДЕНЬ(B2))Где B2 – дата рождения. Формула добавляет нужное количество лет к году рождения, сохраняя месяц и день.
Вы также можете заменить 50 ссылкой на ячейку, где указано требуемое количество лет.
Ответы на часто задаваемые вопросы
В этом руководстве мы рассмотрели различные способы расчета возраста по дате рождения в Excel. Вы можете выбрать подходящий метод в зависимости от ваших требований к точности и формату вывода. Использование функций ДОЛЯГОДА и РАЗНДАТ позволяет получить точные результаты при вычислении возраста в годах, месяцах и днях.
Благодаря этим инструментам, посчитать возраст в Excel становится совершенно несложной задачей.