Извлечение года из даты в Excel

Иван Корнев·21.05.2024·⏱4 мин

Чтобы получить год из даты в Excel, используйте функцию =ЯЧЕЙКА_ДАТЫ(адрес), где вместо ЯЧЕЙКА_ДАТЫ подставляется нужная функция. Самый быстрый способ — формула =ГОД(A1), которая вернет числовое значение года (например, 2023) из ячейки A1, содержащей корректную дату. Этот метод работает во всех версиях Excel, включая Microsoft 365, и позволяет автоматически обновлять данные при изменении исходной даты.

Базовый метод: функция ГОД (YEAR)

Функция ГОД (в английской версии YEAR) предназначена для извлечения года из серийного номера даты. Результатом всегда является целое число в диапазоне от 1900 до 9999.

Алгоритм действий:

  1. Кликните в ячейку, где должен появиться результат.
  2. Введите формулу: =ГОД(A1), заменив A1 на адрес вашей ячейки с датой.
  3. Нажмите Enter.

Если в ячейке A1 находится дата 15.06.2023, формула вернет число 2023. Функция игнорирует день и месяц, а также время, если оно указано в ячейке.

Для применения формулы ко всему столбцу нажмите на маленький квадрат в правом нижнем углу ячейки с формулой и протяните его вниз до конца таблицы.

Альтернатива: функция ТЕКСТ для форматирования

Иногда требуется получить год не как число, а как текст (например, для создания заголовков отчетов или склейки с другими словами). В этом случае используется функция ТЕКСТ (TEXT).

Синтаксис: =ТЕКСТ(ячейка; "формат")

Варианты использования:

  • Полный год: =ТЕКСТ(A1; "ГГГГ") → вернет «2023» (текст).
  • Короткий год: =ТЕКСТ(A1; "ГГ") → вернет «23».
  • Конкатенация: ="Отчет за "&ТЕКСТ(A1;"ГГГГ")&" год" → результат: «Отчет за 2023 год».

Разница между ГОД и ТЕКСТ критична при дальнейших вычислениях. Результат функции ГОД можно использовать в математических операциях (например, сложить годы), а результат ТЕКСТ — только для отображения или объединения строк.

Работа с нестандартными данными и ошибками

Часто пользователи сталкиваются с ситуацией, когда формула возвращает ошибку #ЗНАЧ! (#VALUE!). Это происходит, если Excel воспринимает содержимое ячейки как текст, а не как дату.

Как исправить ошибку:

  1. Проверьте формат ячейки. Выделите проблемную ячейку, нажмите Ctrl+1 и убедитесь, что выбран формат «Дата». Если там стоит «Текстовый», измените его на «Дата» и перепечатайте значение.
  2. Преобразование текста в дату. Если дата пришла из внешней системы в виде строки «2023-06-15», используйте функцию =ДАТАЗНАЧ(A1) внутри формулы года: =ГОД(ДАТАЗНАЧ(A1)).
  3. Извлечение из текстовой строки. Если дата записана нестандартно, например «Июнь 2023», и преобразование невозможно, можно попробовать извлечь последние символы: =ПРАВСИМВ(A1;4). Однако этот метод ненадежен и работает только при фиксированном формате записи.

Функция ГОД не работает с текстом напрямую. Если вы видите в ячейке дату, но формула выдает ошибку, скорее всего, перед вами текстовая имитация даты. Используйте инструмент «Текст по столбцам» на вкладке «Данные» для массового исправления формата.

Сравнение методов извлечения года

МетодТип результатаКогда применятьОсобенности
=ГОД(A1)ЧислоДля расчетов, фильтров, сводных таблицТребует корректного формата даты
=ТЕКСТ(A1;"ГГГГ")ТекстДля заголовков, подписей, сцепки строкЗамедляет вычисления на огромных массивах
=ПРАВСИМВ(A1;4)ТекстТолько если дата — это неизменяемый текстНе проверяет валидность даты

Продвинутые сценарии использования

Группировка данных по годам

Для создания уникальных идентификаторов периода можно объединить год и месяц: =ГОД(A1)&"-"&МЕСЯЦ(A1) Результат: 2023-6. Это удобно для сортировки хронологических данных.

Определение високосного года

Формула проверит, является ли год високосным: =ЕСЛИ(ОСТАТ(ГОД(A1);4)=0; "Високосный"; "Обычный") Примечание: для полной точности правила григорианского календаря условие должно быть сложнее (учет деления на 100 и 400), но для большинства задач достаточно проверки на 4.

Массовая обработка в Power Query

Если у вас десятки тысяч строк и обычные формулы тормозят работу файла:

  1. Выделите таблицу и выберите Данные → Из таблицы/диапазона.
  2. В редакторе Power Query выберите столбец с датой.
  3. На вкладке Добавление столбца выберите Дата → Год → Год.
  4. Нажмите Закрыть и загрузить. Новый столбец будет создан без использования формул в ячейках.

Частые ошибки

  • Ошибка #ЗНАЧ!: Возникает, когда в ячейке находится текст, похожий на дату, но не являющийся ею (например, лишние пробелы или апостроф перед числом).
  • Неверный год (1900 или 1904): Появляется, если система ошибочно интерпретирует число как дату. Проверьте настройки даты в параметрах Excel.
  • Ссылка на пустую ячейку: Функция вернет 1900, так как в Excel пустая ячейка в контексте даты часто равна нулю, а 0 соответствует 0 января 1900 года. Используйте проверку: =ЕСЛИ(A1=""; ""; ГОД(A1)).

FAQ

Можно ли извлечь год, если в ячейке есть время? Да, функции ГОД и ТЕКСТ игнорируют время. Из значения 15.06.2023 14:30 будет извлечено 2023.

Как быстро заполнить год для всей колонки? Введите формулу в первую ячейку, затем дважды кликните по маркеру автозаполнения (маленький квадрат в правом нижнем углу ячейки). Формула скопируется до конца заполненного списка слева.

Почему после извлечения года в ячейке отображается дата? Если результат формулы — число 2023, а ячейка отформатирована как «Дата», Excel покажет странную дату (например, 07.07.1905). Измените формат ячейки на «Общий» или «Числовой» через меню на вкладке «Главная».