Про работу с типом данных "дата и время" в Excel можно написать внушительный материал размером со среднюю брошюру. Конечно, среднестатистический офисный сотрудник пользуется только малой частью этого инструментария, но даже она может вызвать массу вопросов.
Я хотел выделить на тему работы с датами в Excel один пост. Однако, начав писать понял, что материала очень много, даже с учетом формата этого канала - "примеры из офисной жизни". Датам будет посвящено несколько постов и первый из них перед вами - сегодня поговорим о преобразовании текста в формат дат Excel.
Делитесь этим материалом в своих социальных сетях, подписывайтесь на канал и ставьте лайки.
Спасибо!
Зачастую, при выгрузке отчетов из разных систем в Excel (SAP, 1С и д.р.) колонки, содержащие даты воспринимаются как текстовые поля. Понять это можно очень просто - когда вы используете инструмент Фильтр, в колонке, содержащей даты, Excel автоматически сортирует данные по годам, месяцам и дням. Если же в колонке "кривые" данные, то фильтр выбрасывает все значения:
Не обязательно использовать фильтр, чтобы понять, что перед нами "лжедаты". В Excel есть функция ТИП(), которая позволяет определить тип данных в ячейке, которые переданы ей в качестве аргумента. Функция возвращает результат в виде числа, в котором закодирован тип данных в ячейке:
- 1 - число
- 2 - текст
- 4 - логическое значение
- 16 - значение ошибки
- 64 - массив
Если в ячейке дата - функция ТИП() вернет 1, если нет - 2.
Обратите внимание, что Excel хранит даты как числа от 1 до 2958466, что соответствует датам от 01.01.1900 до 31.12.9999. Здесь вы можете ознакомиться с принципом хранения дат и времени в Excel.
Если вместо дат, Excel отображает цифры в указанном выше диапазоне - попробуйте поменять тип данных - выделив диапазон ячеек и в меню "Формат ячеек" выбрать числовой формат "Дата" и нужный вариант отображения.
Такое искажение типов данных происходит по причине наличия в ячейке с датами дополнительных символов, например, пробелов или других, которые не отображаются Excel. С такими кривыми данными невозможно работать как с датами - они неправильно отображаются в фильтре, по ним сложно сделать сводную таблицу, к ним нельзя применять функции работы с датами.
Возникает логичный вопрос - как преобразовать эти данные?
Есть несколько вариантов:
Через инструмент "Найти и заменить":
- находим в ячейке символ, отличающийся от цифр и точек в формате даты, копируем его (лучше именно копировать, т.к. он может отображаться внешне как пробел, но на самом деле быть другим символом ASCII)
- выделяем диапазон ячеек, в котором нужно сделать преобразование
- запускаем инструмент "Найти и заменить" и в поле "Найти" вставляем скопированный ранее символ, а поле "Заменить" оставляем пустым и нажимаем "Заменить все"
Через сочетание функций ДАТА (ГОД(),МЕСЯЦ(),ДЕНЬ()):
Если вы не нашли "странных" символов внутри ячейки с "датами" то можно попробовать создать рядом чистую колонку и воспользоваться сочетанием функций ДАТА(ГОД(),МЕСЯЦ(),ДЕНЬ()) - в 80% случаев такой прием позволяет получить новую колонку с данными в нормальном формате, после чего ее можно скопировать и вставить "как значения":
Собрать дату "по крупицам" через функции работы с текстом - вариант, для особо тяжелых случаев, когда вместо дат в ячейках что-то вроде "26 января 2020 __" и т.п. - способ наиболее трудоемкий и потребует сделать "несколько" приседаний.
Нам понадобятся функции работы с текстом: ПСТР(), ЛЕВСИМВ(), ПРАВСИМВ(), ПОИСК(), ДЛСТР() - о том, как с ними работать, можно прочитать на этом канале здесь. Также, рекомендую вспомнить принцип работы функции ВПР() - об этом тоже есть информация на этом канале здесь. Для очистки данных нам понадобиться функция СЖПРОБЕЛЫ(текст) - она удаляет лишние пробелы в начале и конце текста.
Необходимо создать 4 колонки: в первых трех мы будем вытягивать данные о дне, месяце и годе, а в четвертой (колонка "E") собирать все воедино функцией ДАТА():
В колонке "День" нам необходимо поместить следующее выражение:
=ЛЕВСИМВ(A2;ПОИСК(" ";A2)-1) - мы вырезаем символы с лева до первого пробела, который находится в кавычках первого аргумента функции ПОИСК() - " ", а "-1" здесь как раз для того, чтобы пробел не попал в итоговый результат:
В колонке Месяц будет самое сложное выражение - много функций работы с текстом и ВПР(). Для ВПР () надо создать таблицу подстановки и назвать ее "таб_месяц":
Если кратко, то мы должны вырезать из ячеек колонки Дата название месяца и передать его функции ВПР() в качестве искомого текста, а уже при помощи ВПР() затянуть числовое значение месяца из таблицы подстановки. Готовое выражения выглядит вот так:
=ВПР(СЖПРОБЕЛЫ(ПСТР(A2;ПОИСК(" ";A2)+1;ПОИСК(" ";A2;ДЛСТР(B2)+2)-ДЛСТР(B2)-1));таб_месяц;2;0)
В качестве начальной позиции для вырезания внутри функции ПСТР() мы ищем позицию первого пробела " " в строке и прибавляем к этому значению 1 (чтобы не вырезать пробел), а чтобы определить количество знаков, которые необходимо вырезать - мы используем сложное выражение:ПОИСК(" ";A2;ДЛСТР(B2)+2)-ДЛСТР(B2)-1) - оно ищет позицию второго пробела при помощи функции ПОИСК, причем поиск осуществляется не с начала строки, а с позиции за первым пробелом. Для этого мы определяем длину строки в ячейке День и прибавляем 2 знака - ДЛСТР(B2)+2, далее из позиции второго пробела мы вычитаем длину второго месяца - 1 пробел: ДЛСТР(B2)-1. Все это обрамляем в функцию СЖПРОБЕЛЫ(), чтобы вырезать в начале и конце получившейся строки все пробелы, в противном случае функция ВПР может вызвать ошибку. В готовом виде выражение выглядит примерно так:
С колонкой Год все гораздо проще - просто вырезаем с правой стороны 4 символа =ПРАВСИМВ(A2;4):
В колонке Дата мы просто собираем полученные значения функцией ДАТА(ГОД;МЕСЯЦ;ДЕНЬ):
Таким образом, в колонке E у нас получилось значение с типом дата, к которому можно применять весь функционал Excel по работы с датами.
В итоге, сегодня мы научились определять тип данных в ячейке при помощи функции ТИП(), осознали как Excel хранит информацию в формате дата и время, а также научились преобразовывать текст в формат даты тремя способами. Рекомендую также почитать этот пост - в нем я косвенно касался примеров работы с датами. Возможно, вы найдете что-то полезное для себя.
