Почему эксель меняет последние цифры на нули

Почему эксель меняет последние цифры на нули

Argument ‘Topic id’ is null or empty

Сейчас на форуме

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949

ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071

Длинные числа отображаются неправильно в Excel

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

Обходной путь

Для решения проблемы используйте один из указанных ниже способов.

Способ 1. Форматирование ячейки как текста

Для этого выполните следующие действия:

1. Щелкните правой кнопкой мыши поле со списком и выберите пункт Формат ячеек.
2. На вкладке Число выберите Текст, а затем нажмите ОК.
3. Затем введите длинное число. (Не забудьте установить формат ячейки, прежде чем ввести число)
4. Если вы не хотите видеть предупреждающие стрелки, нажмите на маленькую стрелку, а затем Пропустить ошибку.

Способ 2. Использование одинарной кавычки

При вводе длинного числа сначала введите одинарную кавычку (‘) в ячейке, а затем длинное число.

Например, введите ‘1234567890123456789, и одинарная кавычка не будет отображаться после нажатия ENTER.

Связанные статьи

Ваше мнение важно для нас! Не стесняйтесь сообщать, что вы думаете об этой статье, используя поле комментария, расположенное внизу документа. Это позволит нам улучшить содержимое. Заранее спасибо!

Обратная связь

Были ли сведения на этой странице полезными?

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

При импорте в 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 на самом деле работает с датами и временем
• Замена текста функцией ПОДСТАВИТЬ
• Функция ВПР и числа-как-текст

Почему Excel исправляет последнюю цифру в данном значении на 0?

После внесения значения в любую ячейку, любая последняя указанная цифра автоматически заменяется на 0, например число: 5106216345345354, где последняя (цифра 4) и последующие цифры заменяются на 0.
Пробовал любой формат ячейки, всё равно происходит автоматическая замена, т. е. Excel не способен распознать такие длинные значения?

Дополнен 7 лет назад

«»Для временного решения этой проблемы отформатируйте ячейку как текст, а затем введите цифры. После этого в ячейке может отображаться до 1024 символов.»

Это единственный способ?

Лучший ответ
загугли лучше
Остальные ответы

Это — единственный способ. Дело в том, что Excel воспринимает до 15 ЗНАЧАЩИХ цифр в числе. 16-значные номера карт не могут быть ТОЧНО представлены в виде числа. Но спокойно могут быть введены, как текст. А зачем нужны именно ЧИСЛА? Что, что-то через них вычислять придётся?!