Excel works
Excel works!
Пробовали вводить в ячейку Excel огромные числа? К примеру, серийный номер номер запчасти (2927930504052004) либо номер пластмассовой карты (0000111100002222) — выходит безобразно как на картинке Е+ и так дальше. Почему Excel округляет огромные числа? Как с сиим биться и пара остальных хитростей в статье.
Ячейка программки принимает и обрабатывает числа лишь с точностью до 15 символов, а показывает для числового значения не наиболее 11 цифр. Все, что больше, записывает в экспоненциальном (научном) виде т.е. 1,111E+11.
Экспоненциальное представление числа в Excel
Экспоненциальное — это представление реальных чисел в комфортном виде отображения весьма огромных и весьма малых чисел.
Для нашего примера 0000111100002222 перевоплотился в 111100002222 и отображается как 1,111E+11
1,111 — дробная часть логарифма числа, т.н. мантисса
E — буковка, значащая «*10^» (умножение на 10 в степени p)
p — степень числа 10, в примере 11
Таковой формат можно избрать и принудительно в Формате ячейки
Импорт данных и автоматический перевод в число
Самое противное, если у числа обрезаются нули. Достаточно нередко с таковой неувязкой сталкиваешься при выгрузке из учетной системы либо текстового файла, файла CSV. В таком случае Excel автоматом распознает эти данные как число и 0000111100002222 и превращает его в 111 100 002 222,00. Если не знаешь таковой хитрости, то можно и не увидеть, а это может попортить последующую привязку к данным, ну и просто ввести в заблуждение.
Верно выгрузить будет через инструмент Данные — Получить наружные данные — Из текста
Excel округляет огромные числа. Как поправить такую запись?
- Поставьте перед числом апостроф — верхнюю запятую — знак ‘
2. За ранее сделайте формат ячейки текстовым, если число уже обрезалось по нулям, то это к огорчению не поможет.
3. Если вводить такое число с пробелом, то Excel примет его как текст сходу
Принудительное округление чисел
Как округлить число формулой принудительно можно прочесть в статье.
Еще аспекты:
Таковой формат числа нередко употребляется в графиках и диаграммах при установлении цены деления шкалы (если естественно употребляются огромные числа). Сейчас вы понимаете что же все-таки это такое 🙂
Нередко спрашивают: «Можно ли «уничтожить» такое обрезание чисел?» К огорчению нет, это одна из основ MS Excel, даже макросом не получится. Приходится принимать правила игры. Но зато можно макросом проставить апостроф в начало числа.
Как запретить Excel округлять числа (десятичные / дробные)
Excel — это умопомрачительно отлично разработанный инструмент для работы с электрическими таблицами, который был разработан, чтоб очень повысить эффективность его юзеров. Хотя почти всегда это работает подабающим образом, в неких вариантах это быть может незначительно контрпродуктивным. Одна из таковых областей — это когда Excel решает округлить числа.
В этом уроке я покажу для вас, как взять под контроль и предупредить округление чисел в Excel .
Почему Excel округляет числа?
Вот несколько вероятных обстоятельств, по которым Excel может округлять числа:
- Ширина столбца недостаточна для размещения всех чисел, потому Excel округляет числа, чтоб оно могло соответствовать окончательному значению в ячейке, которое оно может показывать на сто процентов.
- Число очень велико и отображается в экспоненциальном формате.
- Excel может показывать лишь числа длиной до 15 цифр, и для хоть какого числа длиннее этого будет отображаться 0 заместо числа опосля 15-й числа.
- Ячейка отформатирована таковым образом, чтоб округлять числа и показывать лишь определенные числа (либо определенные числа опосля десятичной дроби) в числе.
Сейчас в таблице разглядим любой из этих сценариев и то, как можно предупредить округление чисел в Excel в любом случае.
Закончить округление числа методом роста ширины столбца
Когда вы вводите число в ячейку, Excel пробует очень уместить это число в заданную ширину ячейки.
И в случае, если ширины столбца, в который вводится число, недостаточно, Excel делает последующее наилучшее — округляет число, чтоб показать значение в ячейке (при сохранении начального числа в ячейке).
Как вы сможете созидать ниже на скриншоте, я ввел огромное число (12354,546), которое на сто процентов отображается в строке формул, но округлено в ячейке.
Как это поправить:
Все, что для вас необходимо создать, это прирастить ширину столбца и создать его довольно широким, чтоб у чисел было пространство для полного отображения.
Для этого расположите курсор на край заголовка столбца (алфавит столбца). Вы заметите, что ваш курсор поменяется на двунаправленную стрелку. Два раза щелкните, и он автоматом подгонит ширину столбца для размещения содержимого, которое занимает самую большую ширину.
Как приостановить Excel от округления огромных чисел
Когда дело доходит до ввода огромных чисел в Excel, есть несколько обстоятельств, по которым Excel может округлять ваши огромные числа.
- Для формата ячейки установлено значение «Общий», и отображается лишь определенная длина числа.
- Номер длиннее 15 цифр, и хоть какое число опосля 15-й числа отображается как 0
Давайте разглядим любой из этих сценариев и поглядим, как мы можем приостановить Excel от округления наших чисел в любом случае.
Изменение формата ячейки с общего на числовой
Если для формата ячейки установлено значение «Общий», Excel указывает лишь определенное количество цифр в ячейке, и хоть какое число, превышающее его, отображается в экспоненциальном формате (также именуемом научным форматом).
Ниже приведен пример числа в экспоненциальном формате в ячейке A2.
Как вы сможете созидать на снимке экрана выше, когда у меня есть число из 11 цифр, в Excel нет заморочек с отображением всего числа.
Но когда у меня есть число из 12 цифр, не считая преобразования его в экспоненциальный формат.
Как это поправить:
У этого есть легкое исправление. Все, что для вас необходимо создать, это поменять формат ячейки с общего на числовой.
Ниже приведены аннотации по выполнению этого деяния:
- Выделите ячейки либо спектр ячеек, для которых вы желаете поменять формат
- Перейдите на вкладку «Основная»
- В группе «Число» щелкните раскрывающееся меню форматирования.
- Изберите «Число» в качестве формата.
Вышеупомянутые шаги должны вынудить ваши числа отображаться подабающим образом.
Когда вы меняете формат ячейки на числовой формат, Excel автоматом добавляет 2 числа опосля десятичной точки. Если они для вас не необходимы, щелкните значок «Уменьшить десятичный разделитель» на ленте (он находится на вкладке «Основная»).
Не считая того, если вы видите серию хеш-символов заместо числа в ячейке, просто разверните столбец. Как столбец станет довольно широким, чтоб вместить все число, вы должны узреть его.
Отображение в числовом тексте наиболее 15 цифр
Еще одна неувязка, с которой вы сможете столкнуться при работе с большенными числами в Excel, состоит в том, что опосля 15 цифр в числе Excel конвертирует всякую цифру в 0.
Это изготовлено преднамеренно, и Excel считает важными лишь 15 цифр, и неважно какая цифра опосля этого автоматом преобразуется в 0.
Это быть может неувязкой, когда вы работаете с большенными числами (таковыми как номера кредитных карт либо номера заказов).
Ниже приведен пример, в каком, когда я ввожу цифру 1 16 раз в ячейку, показываются 1-ые 15 экземпляров, а 16-й преобразуется в 0.
Внимание: в отличие от остальных способов, Excel не попросту указывает мне 0 опосля 15-й числа, а фактическое число находится в задней части. Даже в задней части Excel конвертирует числа опосля 15-й числа в 0.
Как с сиим совладать:
Чтоб Excel не делал этого, для вас необходимо конвертировать ваш номер в текстовый формат. Хотя Excel за ранее запрограммирован на обработку чисел определенным образом, он не вмешивается в текстовый формат (к счастью).
И самый обычной метод создать это — добавить апостроф перед числом.
Это принудит Excel разглядывать все, что следует за апострофом, как текстовую строчку, и в то же время, как вы нажмете кнопку ввода, апостроф не будет отображаться в ячейке (так что вы можете узреть все число без апостроф)
Ниже приведен пример, в каком число в ячейке A1 имеет апостроф перед ним и указывает все число, но число в ячейке A2 не имеет апострофа и конвертирует последнюю цифру в 0.
Приостановить Excel от округления десятичных чисел / валют
Excel также дает юзерам возможность решать, сколько цифр опосля десятичного разряда обязано отображаться.
Это также может означать, что если у вас есть больше цифр опосля десятичной дроби, они будут округляться лишь для отображения обозначенного количества цифр.
Это нередко случается, когда вы используете формат валюты либо бухгалтерский формат, где числа постоянно показываются с 2-мя цифрами опосля десятичной точки. И если у вас есть число, которое состоит из 3-х, 4 либо 5 цифр опосля десятичной точки, оно округляется до 2-ух цифр.
Ниже приведен пример, в каком у меня есть число, состоящее наиболее чем из 2-ух цифр опосля десятичной дроби, но оно округлено и показываются лишь две числа.
Как это поправить:
Это происходит из-за числового формата ячеек, в каких есть числа.
И чтоб Excel не округлял эти числа, измените формат ячейки, чтоб он мог показывать больше чисел, чем то, что отображается в истинное время.
Ниже приведены аннотации по изменению формата ячейки и отображению большего количества чисел:
- Выделите ячейки с числами
- Удерживая кнопку Control, нажмите кнопку 1 (раскроется диалоговое окно Формат ячеек).
- Удостоверьтесь, что вы находитесь на вкладке «Число».
- Изберите Число на левой панели (либо Валюта / Бухгалтерский учет, если вы используете эти форматы).
- Измените значение «Десятичных символов» с 2 на 3 либо 4 (либо хоть какое другое количество цифр, которое вы желаете показать).
- Закройте диалоговое окно
Вышеупомянутые шаги обеспечат, чтоб Excel постоянно показывал ваши числа с обозначенным количеством цифр опосля десятичной дроби. Любые числа опосля обозначенного значения будут округлены (что приемлемо почти всегда).
Также направьте внимание, что ничто из этого по сути не меняет значение в ячейке. Это лишь меняет метод отображения.
Потому, если вы вводите число, которое имеет 5 цифр опосля десятичной точки, а в ячейке показываются лишь две, начальное число все равно остается таковым, каким вы его ввели.
Итак, вот некие из методов предупредить округление чисел в Excel .