Вы когда-нибудь сталкивались с колонкой дат, с которой вы ничего не могли сделать? Ну то есть ни определить день недели, ни номер месяца, ни прибавить сколько-нибудь дней?
Не так давно мне задали вопрос – что делать с такими данными, как их исправить. Выглядели данные нормально, то есть «1 января 2010», «10 октября 2008», «17 ноября 2009» и т.д.. Но если в соседнем столбце попытаться определить день недели с помощью функции НОМНЕДЕЛИ (WEEKNUM), Excel выдаст ошибку #ЗНАЧ! (#VALUE!)
Скорее всего это происходит из-за того, что изначально дата была введена в текстовом формате (или импортировалась в текстовом формате), и Excel не «распознал» в ней дату, с которой он был бы готов работать.
Поэтому задача звучит так – как из текста получить дату в формате, «понятном» Excel.
Представим, что в файле Excel, начиная с ячейки A2 и ниже, в первом столбце расположены такие даты. Первым делом нам необходимо вычленить из этого текста номер дня и номер года в соседних столбцах. Для этого используем функции ЛЕВСИМВ(LEFT), ПРАВСИМВ(RIGHT), ПОИСК(SEARCH), ПСТР(MID)
Заполняем столбец B – номер дня
С помощью формулы для определения дня мы отрежем такое количество символов, сколько расположено до первого пробела. Чтобы не захватить первый пробел, используем -1. Итого формула для определения дня (для ячейки B2):
[code lang=sh]
=ЛЕВСИМВ(A2;ПОИСК(" ";A2)-1)
=LEFT(A2;SEARCH(" ";A2)-1)
[/code]
Заполняем столбец C – год
С годом проще – мы знаем, что это всегда 4 символа, поэтому можно просто указать
[code lang=sh]
=ПРАВСИМВ(A2;4)
=RIGHT(A2;4)
[/code]
Заполняем столбец D – три буквы месяца
С месяцем немного сложнее – его нужно получить в два этапа. Во-первых, получить текстовое определение месяца, затем найти номер месяца, чтобы потом его можно было бы использовать. Определить месяц можно по первым трем буквам. Их мы получим опять же через поиск символа и функцию ПСТР(MID):
[code lang=sh]
=ПСТР(A2;ПОИСК(" ";A2)+1;3)
=MID(A2;SEARCH(" ";A2)+1;3)
[/code]
Заполняем столбцы I и J вспомогательной таблицей
Затем, для того, чтобы преобразовать полученное текстовое значение в номер месяца, необходимо создать дополнительную табличку, в которой для каждой такой трехбуквенной аббревиатуры будет указан номер месяца. Для примера ее можно создать начиная с ячейки I1. В ячейке I2 – «Янв», J2 – 1, I3 – «Фев», J3 – 2, I4 – «Мар», J4 – 3 и т.д.. Обратите внимание, что дата, записанная текстом будет использовать другое окончание для месяца май – мая, поэтому нужно сделать соответствующую корректировку в случае автоматического заполнения списка.
Заполняем столбец E – номер месяца
После этого мы можем осуществить подстановку номера месяца по ее трехбуквенной аббревиатуре при помощи функции ВПР(VLOOKUP)
[code lang=sh]
=ВПР(D2;$I$2:$J$13;2;ЛОЖЬ)
=VLOOKUP(D2;$I$2:$J$13;2;FALSE)
[/code]
Заполняем столбец F – дата
Осталось только скрепить воедино дату при наличии дня, месяца и года. Для этого используем функцию ДАТА(DATE):
[code lang=sh]
=ДАТА(C2;E2;B2)
=DATE(C2;E2;B2)
[/code]
И в столбце F мы получаем дату, которую понимает Excel, с которой можно делать много чего интересного — начиная от простого формата чисел, и заканчивая функция ЧИСТРАБДНИ(NETWORKDAYS) и так далее.
Ссылка на скачивание примера: Text_data_sample