Исправление чисел, превратившихся в даты
При импорте в 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):
Снимаем флажок Использовать системные разделители (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). Все испорченные числа будут немедленно исправлены.
Ссылки по теме
- Как Excel на самом деле работает с датами и временем
- Замена текста функцией ПОДСТАВИТЬ
- Функция ВПР и числа-как-текст
Там нужно поиграться с инменением формата, а именно пункт локал отвечает на этот вопрос. Если ситуация именно такая как на видео, то нужно выбрат в локале английский (Великобритании)
на строке num = CDbl(Replace(cell, ".", ",";))