Исправление чисел, превратившихся в даты

При импорте в Excel данных из внешних программ, иногда возникает весьма неприятная проблема - дробные числа превращаются в даты:

Испорченные данные

Так обычно происходит, если региональные настройки внешней программы не совпадают с региональными настройками Windows и Excel. Например, вы загружаете данные с американского сайта или европейской учётной системы (где между целой и дробной частью - точка), а в Excel у вас российские настройки (где между целой и дробной частью - запятая, а точка используется как разделитель в дате).

При импорте Excel, как положено, пытается распознать тип входных данных и следует простой логике - если что-то содержит точку (т.е. российский разделитель дат) и похоже на дату - оно будет конвертировано в дату. Всё, что на дату не похоже - останется текстом.

Давайте рассмотрим все возможные сценарии на примере испорченных данных на картинке выше:

  • В ячейке A1 исходное число 153.4182 осталось текстом, т.к. на дату совсем не похоже (не бывает 153-го месяца)
  • В ячейке A2 число 5.1067 тоже осталось текстом, т.к. в Excel не может быть даты мая 1067 года - самая ранняя дата, с которой может работать Excel - 1 января 1900 г.
  • А вот в ячейке А3 изначально было число 5.1987, которое на дату как раз очень похоже, поэтому Excel превратил его в 1 мая 1987, услужливо добавив единичку в качестве дня:

    Неправильная дата

  • То же самое случилось и в ячейке А4, где изначально было число 12.6923. Причем 6923-й год Excel тут нисколько не смутил - для него главное, чтобы дата была не раньше 1900 года. Так что добро пожаловать в далекое будущее:

    Еще одна неправильная дата

  • Единственное, что распознаётся корректно - это числа без дробной части (число 1000 в ячейке А6). И на том спасибо :)

Вот такие варианты. И если текстовые числа ещё можно вылечить банальной заменой точки на запятую, то с числами превратившимися в даты такой номер уже не пройдет. А попытка поменять их формат на числовой выведет нам уже не исходные значения, а внутренние коды дат Excel - количество дней от 01.01.1900 до текущей даты:

Неправильное число после изменения формата

Лечится вся эта история тремя принципиально разными способами.

Способ 1. Заранее в настройках

Если данные ещё не загружены, то можно заранее установить точку в качестве разделителя целой и дробной части через Файл - Параметры - Дополнительно (File - Options - Advanced):

Настройка разделителей в окне параметров Excel

Снимаем флажок Использовать системные разделители (Use system separators) и вводим точку в поле Разделитель целой и дробной части (Decimal separator).

После этого можно смело импортировать данные - проблем не будет.

Способ 2. Формулой

Если данные уже загружены, то для получения исходных чисел из поврежденной дата-тексто-числовой каши можно использовать простую формулу:

Формула исправления чисел из дат

=--ЕСЛИ(ЯЧЕЙКА("формат";A1)="G"; ПОДСТАВИТЬ(A1;".";","); ТЕКСТ(A1;"М,ГГГГ"))

В английской версии это будет:

=--IF(CELL("format";A1)="G"; SUBSTITUTE(A1;".";","); TEXT(A1;"M,YYYY"))

Логика здесь простая:

  • Функция ЯЧЕЙКА (CELL) определяет числовой формат исходной ячейки и выдаёт в качестве результата "G" для текста/чисел или "D3" для дат.
  • Если в исходной ячейке текст, то выполняем замену точки на запятую с помощью функции ПОДСТАВИТЬ (SUBSTITUTE).
  • Если в исходной ячейке дата, то выводим её в формате "номер месяца - запятая - номер года" с помощью функции ТЕКСТ (TEXT).
  • Чтобы преобразовать получившееся текстовое значение в полноценное число - выполняем бессмысленную математическую операцию - добавляем два знака минус перед формулой, имитируя двойное умножение на -1.

Способ 3. Макросом

Если подобную процедуру лечения испорченных чисел приходится выполнять часто, то имеет смысл автоматизировать процесс макросом. Для этого жмём сочетание клавиш Alt+F11 или кнопку Visual Basic на вкладке Разработчик (Developer), вставляем в нашу книгу новый пустой модуль через меню Insert - Module и копируем туда такой код:

Sub Fix_Numbers_From_Dates()
    Dim num As Double, cell As Range

    For Each cell In Selection
        If Not IsEmpty(cell) Then
            If cell.NumberFormat = "General" Then
                num = CDbl(Replace(cell, ".", ","))
            Else
                num = CDbl(Format(cell, "m,yyyy"))
            End If
            cell.Clear
            cell.Value = num
        End If
    Next cell
End Sub

Останется выделить проблемные ячейки и запустить созданный макрос сочетанием клавиш Alt+F8 или через команду Макросы на вкладке Разработчик (Developer - Macros). Все испорченные числа будут немедленно исправлены.

Ссылки по теме



20.09.2020 20:21:19
Доброго вечера. 1.5 будет выглядеть, как 01.05.2020. и М,ГГГ не прокатит. Нужно ДД.ММ =--ПОДСТАВИТЬ(A1;A1;ТЕКСТ(A1;"ДД,ММ"))
20.09.2020 20:48:36
Есть и четвертый вариант - с помощью дядя Power query))
Там нужно поиграться с инменением формата, а именно пункт локал отвечает на этот вопрос. Если ситуация именно такая как на видео, то нужно выбрат в локале английский (Великобритании)
23.09.2020 17:54:07
В данном вопросе самое главное это выгружать в текст, а импортировать потом в Эксель из текста через Power Query. Да даже стандартным мастером импорта. Просто в pq можно на ходу и распарсить из текста всякое.
19.10.2020 17:00:23
Добрый день! Спасибо большое. Но все это решает только частный случай проблемы - когда число с 4 знаками после запятой и дробная часть предстаёт как год. В иных случаях будет полная чехарда: 25.12, 5.5 и тд..
Наверх