Пробовали вводить в ячейку Excel большие числа? Например, серийный номер номер запчасти (2927930504052004) или номер пластиковой карты (0000111100002222) — получается некрасиво как на картинке Е+ и так далее. Почему Excel округляет большие числа? Как с этим бороться и пара других хитростей в статье.
Ячейка программы воспринимает и обрабатывает числа только с точностью до 15 знаков, а отображает для числового значения не более 11 цифр. Все, что больше, записывает в экспоненциальном (научном) виде т.е. 1,111E+11.
Содержание
Экспоненциальное представление числа в Excel
Экспоненциальное — это представление действительных чисел в удобном виде отображения очень больших и очень малых чисел.
Для нашего примера 0000111100002222 превратилось в 111100002222 и отображается как 1,111E+11
1,111 — дробная часть логарифма числа, т.н. мантисса
E — буква, означающая «*10^» (умножение на десять в степени p)
p — степень числа 10, в примере 11
Такой формат можно выбрать и принудительно в Формате ячейки
Импорт данных и автоматический перевод в число
Самое неприятное, если у числа обрезаются нули. Довольно часто с такой проблемой сталкиваешься при выгрузке из учетной системы или текстового файла, файла CSV. В таком случае Excel автоматически распознает эти данные как число и 0000111100002222 и превращает его в 111 100 002 222,00. Если не знаешь такой хитрости, то можно и не заметить, а это может испортить дальнейшую привязку к данным, да и просто ввести в заблуждение.
Правильно выгрузить будет через инструмент Данные — Получить внешние данные — Из текста
Excel округляет большие числа. Как исправить такую запись?
- Поставьте перед числом апостроф — верхнюю запятую — символ ‘
2. Предварительно сделайте формат ячейки текстовым, если число уже обрезалось по нулям, то это к сожалению не поможет.
3. Если вводить такое число с пробелом, то Excel воспримет его как текст сразу
Принудительное округление чисел
Как округлить число формулой принудительно можно прочитать в статье.
Еще нюансы:
Такой формат числа часто используется в графиках и диаграммах при установлении цены деления шкалы (если конечно используются большие числа). Теперь вы знаете что это такое :)
Часто спрашивают: «Можно ли «убить» такое обрезание чисел?» К сожалению нет, это одна из основ MS Excel, даже макросом не получится. Приходится принимать правила игры. Но зато можно макросом проставить апостроф в начало числа.
Sub Dobavit_Apostrof() Dim c As Range For Each c In Selection If (Not IsEmpty(c)) And Not (Left(c, 1) = "'") Then c = "'" & c End If Next End Sub
Добавляет апостроф при выполнение макроса во все выделенные ячейки.
Комментарии:
К этой записи пока нет комментариев. Возможно вы будете первым?