Мини-конкурс на знание Excel
Недавно в социальной сети мне пришло сообщение от бывшего коллеги. Цитирую:
"...Вот что мне пришлось придумать, чтобы преобразовать числа вида 1/24/2014 (бывшие даты) в нормальные даты: =DATEVALUE(CONCATENATE(MID(A1;SEARCH("/";A1;1)+1;SEARCH("/";A1;4)-SEARCH("/";A1;1)-1);".";LEFT(A1;SEARCH("/";A1;1)-1);".";RIGHT(A1;4)))
..Интересно, найдется ли решение попроще?"
Друзья, этот вопрос и стал нашим заданием на мини-конкурс:
- Если вы используете более простой и доступный способ преобразования дат - опишите его в комментариях к этому посту.
- Обязательно укажите свой контакт или ник - мы не сможем выдать приз анониму.
- Победителя определит комиссия из сотрудников Microsoft.
- Комментарии принимаются на конкурс до 7 февраля 2014 года. Мы будем рады узнать ваш вариант решения и после этой даты, но он уже не будет иметь шанса на приз.
- Приз - ключ на Office 365 Home Premium, на год.
Удачи!
Comments
- Anonymous
January 01, 2003
Боюсь, что тут одним форматом не отделаешься. Раз дата автоматом не распозналась, значит Excel определил содержимое как текст, и без вмешательства танца с бубном не обойдется. Судя по исходной формуле, нам нужно m/d/yyyy преобразовать в d.m.yyyy либо dd.mm.yyyy (будет зависеть от региональных настроек для дат в системе). Я придумал три варианта преобразования с помощью формулы. 1. "Из пушки по воробьям" (формула на 164 символа) Данный метод - формульная альтернатива text to columns (если пробел не является криичным символом): =DATEVALUE( SUBSTITUTE( TRIM(LEFT(RIGHT(SUBSTITUTE(A1;"/";REPT(" ";LEN(A1)));2*LEN(A1));LEN(A1)) & LEFT(SUBSTITUTE(A1;"/";REPT(" ";LEN(A1)));LEN(A1))&RIGHT(A1;4));" ";".")) 2. "Симметричный" (125 символов) =DATEVALUE(SUBSTITUTE(RIGHT(MID(A1;1;LEN(A1)-5);2);"/";"") & "." & SUBSTITUTE(LEFT(MID(A1;1;LEN(A1)-5);2);"/";"") & "." &RIGHT(A1;4)) 3. "Магия" (93 символа) =DATEVALUE(SUBSTITUTE(MID(A1;3;LEN(A1)-7);"/";"")&"."&LEFT(A1;FIND("/";A1)-1)&"."&RIGHT(A1;4)) Спасибо за интересную задачу! :) - Anonymous
January 01, 2003
Друзья, по правилам конкурса (ответы до 7 февраля) и результатам голосования, с учетом мнения автора вопроса, победителем становится Марат. Марат, мы попытаемся с вами связаться, для надежности - напишите сообщение в форме обратной связи http://blogs.technet.com/b/tasush/about.aspx - Anonymous
January 01, 2003
По-видимому, проблема заключается в том, что региональные настройки для России не предусматривают варианта написания даты в формате месяц/день/год, а источником данных как раз является таблица, созданная в версии Excel локализированной для США. Изменение формата представления даты в этом случае скорее всего не поможет. Могу предположить, что изменение региональных настроек для windows сможет решить проблему с датами (сам не проверял, поэтому утверждать не могу). На мой взгляд, самым простым способом конвертации дат является использование функции Text-to-Columns (Tab: DATA) на третьем шаге нужно выбрать Column Data Format: Date: MDY (для нашего конкретного случая). Если же локализация Excel англоязычная (США), то прекрасно работают следующие варианты (или просто поменять формат отображения даты...): =DAY(A1)&"."&MONTH(A1)&"."&YEAR(A1) =TEXT(A1, "dd.mm.yyyy") - Anonymous
January 01, 2003
Друзья, спасибо за ответы! Я впечатлена, и боюсь, что за судейством придется обратиться к автору вопроса. - Anonymous
January 01, 2003
Другой вариант. Длиннее, медленнее и совсем не практично, но зато совершенно с другой идеей: {=MAX(IF(COLUMN($A$1:$L$31)&"/"&ROW($A$1:$L$31)&RIGHT(A1;5)=A1;DATE(RIGHT(A1;4);COLUMN($A$1:$L$31);ROW($A$1:$L$31))))} По-русски: {=МАКС(ЕСЛИ(СТОЛБЕЦ($A$1:$L$31)&"/"&СТРОКА($A$1:$L$31)&ПРАВСИМВ(A1;5)=A1;ДАТА(ПРАВСИМВ(A1;4);СТОЛБЕЦ($A$1:$L$31);СТРОКА($A$1:$L$31))))} (фигурные скобки не нужны - вводить через Ctrl+Shift+Enter) - Anonymous
January 01, 2003
использую формулу =ПОДСТАВИТЬ(A1; "/"; ".") - Anonymous
January 01, 2003
Немного покороче, но не принципиально: =DATE(RIGHT(A1;4);SUBSTITUTE(LEFT(A1;2);"/";"");SUBSTITUTE(MID(A1;FIND("/";A1)+1;2);"/";"")) В переводе на русский: =ДАТА(ПРАВСИМВ(A1;4);ПОДСТАВИТЬ(ЛЕВСИМВ(A1;2);"/";"");ПОДСТАВИТЬ(ПСТР(A1;НАЙТИ("/";A1)+1;2);"/";"")) - Anonymous
January 01, 2003
:)) правильнее формула выглядит: =ЕСЛИ(ДЛСТР(A1)=10;ПСТР(A1;4;2)&"."&ПСТР(A1;1;2)&"."&ПСТР(A1;7;4);ПСТР(A1;3;2)&"."&ПСТР(A1;1;1)&"."&ПСТР(A1;6;4)) результатом будет, при значении месяца до 10, например 1/24/2014 результат 24.1.2014, при значении месяца от 10 результатом будет 24.10.2014 - Anonymous
January 01, 2003
А можно и так: =ЕСЛИ(ДЛСТР(A6)=10;ПСТР(A6;4;2)&"."&ПСТР(A6;1;2)&"."&ПСТР(A6;7;4);ПСТР(A2;3;2)&"."&ПСТР(A2;1;1)&"."&ПСТР(A2;6;4)) - Anonymous
January 01, 2003
Можно эту задачу решить и при помощи формулы: =ПСТР(A2;3;2)&"."&ПСТР(A2;1;1)&"."&ПСТР(A2;6;4) - Anonymous
January 01, 2003
2 Жук Отличное решение! - Anonymous
January 01, 2003
использую формулу =ПОДСТАВИТЬ(A1; "/"; ".") - Anonymous
January 31, 2014
Эта задача решаются выбором формата представления даты в ячейке вообще без формул. - Anonymous
February 05, 2014
использую формулу =ПОДСТАВИТЬ(A1; "/"; ".") - Anonymous
February 05, 2014
Доброго дня. Можно и вот так :) =ДАТА(ЕСЛИ(ЛЕВСИМВ(B22;1)="0";ПСТР(B22;7;4);ПСТР(B22;6;4));ЕСЛИ(ЛЕВСИМВ(B22;1)="0";ЛЕВСИМВ(B22;2);ЛЕВСИМВ(B22;1));ЕСЛИ(ЛЕВСИМВ(B22;1)="0";ПСТР(B22;4;2);ПСТР(B22;3;2))) - Anonymous
February 05, 2014
Жук, молодец :) - Anonymous
February 05, 2014
Жук, одна проблема: в американской формате сначала идет месяц (в условиях как раз такой пример), так что получится дата вида 1.24.2014. Не совсем то, что нужно :-) - Anonymous
February 11, 2014
Если это один столбец с такими "датами", то предлагаю такое решение. 1) Выделить столбец. Ctrl+H - окно замены, найти /, заменить - . (точка). Заменить все. Результат - столбец со значениями вида 1.24.2014 (или 12/24/2014 - для двузначных номеров месяцев) 2) Столбец выделен. Данные - Работа с данными - Текст по столбцам. На первом шаге выбираем: формат исходных данных - с разделителями, Далее. На втором шаге: символом-разделителем является: - "галка" напротив "пробел", и справа флажок включить рядом со "считать последовательные разделители одним", Далее. На третьем шаге - Формат ряда данных столбца - переключатель на "дата", там же рядом в списке выбрать МДГ. ГОТОВО! - Anonymous
February 11, 2014
Если это один столбец с такими "датами", то предлагаю такое решение. 1) Выделить столбец. Ctrl+H - окно замены, найти /, заменить - . (точка). Заменить все. Результат - столбец со значениями вида 1.24.2014 (или 12/24/2014 - для двузначных номеров месяцев) 2) Столбец выделен. Данные - Работа с данными - Текст по столбцам. На первом шаге выбираем: формат исходных данных - с разделителями, Далее. На втором шаге: символом-разделителем является: - "галка" напротив "пробел", и справа флажок включить рядом со "считать последовательные разделители одним", Далее. На третьем шаге - Формат ряда данных столбца - переключатель на "дата", там же рядом в списке выбрать МДГ. ГОТОВО!