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

Причины этого:

1. Несоответствие регионального формата исходных данных региональным настройкам вашей операционной системы. В исходных данных в качестве десятичного разделителя используется точка (американский формат), а у вас – региональные настройки, в которых десятичным разделителем является запятая, а точка воспринимается как разделитель отдельных составляющих даты – день, месяц, год (например, 17.09.202.
2. Особое поведение Excel для ячеек в «Общем» числовом формате (который установлен по умолчанию): если вводить в ячейку что-то похожее на число, то такое значение сохраняется как число, если вводить что-либо, похожее на дату – то такое значение сохраняется как дата. Если вводимое значение не похоже ни на число, ни на дату – то это текст.

В данном примере (выше) поврежденные данные:

Исходные данные сохранились как текст:

• Ячейка D3 – исходное значение (179.3287) мало похоже на дату, так как номер месяца 179 не существует (а вот год 3287 возможен, в далеком будущем).
• Ячейка D4 – исходное значение похоже на дату (июль 1275 года). Однако в Excel точкой отсчета дат является 1 января 1900 года. Поэтому число 1275 (ранее 1900 года) не воспринимается как год.

 

Исходные данные были преобразованы в даты (и, соответственно, утеряны):

• Ячейка D5 – в исходном значении (7.1972) число до точки похоже на номер месяца 7 (июль), а число после точки – на год 1972 (на самом деле произошло преобразование в дату 01.07.1972)
• Ячейка D6 – в исходном значении (11.2024) число до точки воспринимается как номер месяца (ноябрь), а после точки – год 2024 (произошло преобразование в дату 01.11.2024)

 

Какие есть способы решения данной проблемы?

Начнем с самых кардинальных и, соответственно, не самых лучших.

 

Способ-1. Поменять региональные настройки операционной системы (на американские).

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

 

Способ-2. Локально в Excel (в параметрах Excel) заблаговременно изменить десятичный разделитель с запятой на точку:

 

Способ-3. Заблаговременно установить «текстовый» формат ячеек (вместо «общего»), в которые будут вставляться значения.

Тогда мы избавимся от авто преобразования «чисел», похожих на даты, в непосредственно даты, и получим значения в исходном виде, только в формате текста. Далее этот текст легко будет преобразовать в числа с помощью простой формулы:

=SUBSTITUTE(A3;".";",")*1 (в диапазоне C3:C9)

либо еще лучше, формулы:

=NUMBERVALUE(A3;".") (в диапазоне E3:E9)

 

Русские варианты этих формул:

=ПОДСТАВИТЬ(A3;".";",")*1

и

=ЧЗНАЧ(A3;"."),

соответственно

Способ-4. Формулой (без какой-либо предварительной подготовки):

Можно попробовать восстановить уже испорченные числа формулой

Формула в ячейке G3:

=1*IF(CELL("format";D3)="G"; SUBSTITUTE(D3;".";","); TEXT(D3;"M,YYYY"))

или русский вариант:

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

 

Формула работает следующим образом:

CELL() (ЯЧЕЙКА()) – функция из категории «Информационные». Если первый ее аргумент (тип информации) указан “format”, то результат работы функции - "G" - для текста или чисел и "D3" - для дат.

Если в ячейке находится текст, то функция SUBSTITUTE() (ПОДСТАВИТЬ()) заменяет точку на запятую и умножение на 1 в начале формулы (применение арифметической операции) приводит к преобразованию текста, состоящего из цифр с запятой, - в число.

Если в ячейке не текст (а дата), то функция TEXT() (ТЕКСТ()) преобразует дату в текст в соответствии с кодом формата, указанного в качестве второго ее аргумента. И далее такой текст легко преобразуется в число путем умножения на 1 (в начале формулы).

Используя код формата в функции TEXT, нужно учитывать то, что и даже в англоязычной версии, в зависимости от региональных настроек, вместо “M,YYYY”, возможно, нужно указывать “М,ГГГГ” (где “M” – кириллическое).

 

ВАЖНО! Формула не является универсальным решением (на все случаи жизни)

Она хорошо работает, если после точки находится от 3-х и более цифр.

А что, если имеем такое:

В первых трех ячейках уже цифры до точки воспринимаются как номер дня (а не номер месяца), а цифры после точки – как номер месяца (а не номер года).

А в последних двух ячейках разные исходные числа привели к получению одной и той же даты.

Если в первом случае можно было бы еще добавить проверку дополнительных условий в формуле, то в последнем – исходные значения безвозвратно утеряны и узнать какие они были – уже не представляется возможным. Очевидно, в таком случае, единственный вариант – это не допустить автоматического преобразования «как бы чисел» в даты, то есть указанный выше способ 3 – чуть ли не единственное решение (не считая кардинальных изменений региональных настроек).

Такие и подобные тонкости работы в Excel мы прорабатываем на курсе "Excel бизнес-анализ и прогнозирование" 

За 27 часов изучения Excel у вас не останется никаких вопросов  его работе, а если они и возникнут, то наши тренера-поддержки онлайн помогут с ответами.

 

Автор статьи CEO и тренер DATAbi Евгений Довженко

 

Узнайте больше про полную программу "EXCEL: бизнес-анализ и прогнозирование"