Почему в excel вместо числа появляется

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

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

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

Вот такие варианты. И если текстовые числа ещё можно вылечить банальной заменой точки на запятую, то с числами превратившимися в даты такой номер уже не пройдет. А попытка поменять их формат на числовой выведет нам уже не исходные значения, а внутренние коды дат 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 на самом деле работает с датами и временем
• Замена текста функцией ПОДСТАВИТЬ
• Функция ВПР и числа-как-текст

Отмена автоматической замены чисел датами

Microsoft Excel предварительно программируется, чтобы упростить ввод дат. Например, 12/2 изменяется на 2-дек. Это очень неприятно, если вы вводите что-то, что не хотите менять на дату. К сожалению, отключить эту возможность не получится. Однако существует несколько способов обойти это.

Предформатация ячеек, в которые нужно ввести числа, в виде текста. В этом случае Excel не будет пытаться изменить то, что вы вводите в даты.

Если нужно ввести всего несколько чисел, можно сделать так, чтобы Excel не превнося их в даты, введите:

• Пробел перед вводом числа. После нажатия ввод пробел останется в ячейке. (См. заметки)
• Апостроф (‘) перед вводом числа, например ’11-53 или ‘1/47. Апостроф не отображается в ячейке после нажатия ввода.
• Нуль и пробел перед вводом дроби, например 1/2 или 3/4, чтобы они не менялись, например, на 2-янв или 4-мар. Введите 0 0 1/2 или 0 3/4. Ноль не остается в ячейке после нажатия ввод, и ячейка становится типом дробного числа.

1. Выйдите из ячеек, в которые нужно ввести числа.
2. Нажмите клавиши CTRL+1 (1 в строке чисел над клавишами QWERTY), чтобы открыть формат ячеек.
3. Выберите тексти нажмите кнопку ОК.

1. Выберите ячейки, в которые необходимо ввести числа.
2. Щелкните Главная >Числовой формат >Текст.

• Рекомендуется использовать апостроф вместо пробела для ввода данных, если вы планируете использовать функции подменю для данных. Такие функции, как ПОИСКПОЗ и ВПР, не учитывают апострофы при вычислении результатов.
• Если число в ячейке выровнено по левому краю, обычно это означает, что оно не отформатировано как число.
• При введении в ячейку числа с буквой «е», например 1e9, оно автоматически преобразуется в научное число: 1,00E+09. Чтобы избежать этого, введите перед числом апостроф: ‘1e9
• В зависимости от введенного числа в левом верхнем углу ячейки может появиться маленький зеленый треугольник, указывающий на то, что число хранится как текст ,что для Excel является ошибкой. Не обращайте внимание на треугольник или щелкните его. Слева появится поле. Щелкните поле, а затем выберитеигнорировать ошибку , из-за чего треугольник будет отопуститься.

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Исправление ошибки #####

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Еще. Меньше

Приложение Microsoft Excel может отображать в ячейках символы #####, если ширины столбца недостаточно для отображения всего содержимого ячейки. В формулах, возвращающих дату и время как отрицательные значения, также может отображаться строка #####.

Чтобы расширить столбец и отобразить все содержимое ячейки, дважды щелкните правый край заголовка столбца или перетащите его на нужное расстояние.

Также попробуйте сделать следующее.

• Чтобы уменьшить содержимое ячейки, нажмите кнопку Главная >рядом с кнопкой Выравнивание ,а затем в диалоговом окне Формат ячеек выберите нужный размер.
• Если число содержит слишком много знаков после запятой, щелкните Главная >Уменьшить разрядность.
• Если даты слишком длинные, щелкните Главная, затем — стрелку рядом с элементом Формат чисел и выберите Краткий формат даты.

Исправление отрицательных значений даты или времени

Если Excel отображает символы #####, потому что ячейка содержит отрицательное значение даты или времени, сделайте следующее.

• При использовании системы дат 1900 убедитесь, что дата и время заданы положительными значениями.
• Используйте формулу для вычитания дат или правильного сложить или вычесть время, чтобы избежать отрицательных значений даты или времени.
• Для ячеек с отрицательными результатами в формате даты или времени выберите другой числовой формат.

Почему в excel вместо числа появляется

Сообщений: 16 Регистрация: 10.01.2013

18.10.2009 15:30:38

Ни с того ни с сего появился такой глюк — при вводе числа в ячейку оно отображается в 100 раз меньше. Например, ввожу 1, отображается 0,01, ввожу 1000 — отображается 10. Вне зависимости от формата ячейки, кроме текстового, но выдает ошибку что число введено как текст. Глюк проявляется во всех книгах — и в уже существующих и в новосозданных.
Помогите понять что это.

Пользователь

Сообщений: 16 Регистрация: 10.01.2013

18.10.2009 17:47:07

Неужели никто не знает как это побороть и в чем дело? Облазил весь гугль, нашел на одном форуме описание точно такой же проблеммы, но там нет ответа ((( «Правка — Очистить — Форматы» не помогает. Если ввести число с запятой, например, 1,25, то оно так и отображается, но если ввсети целое число, оно автоматически делится на 100. Я в отчаянии, необходимо работать, а в таком режиме ни о какой работе речи быть не может.

Сообщений: 60949 Регистрация: 14.09.2012