Большое число в ячейке excel

Сохранение начальных нулей и больших чисел

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).

Вам когда-нибудь приходилось импортировать или вводить в Excel данные, содержащие начальные нули (например, 00123) или большие числа (например, 1234 5678 9087 6543)? Это могут быть номера социального страхования, телефонные номера, номера кредитных карт, коды продуктов, номера счетов или почтовые индексы. Excel автоматически удаляет начальные нули и преобразует большие числа в экспоненциальное представление (например, 1,23E+15), чтобы их можно было использовать в формулах и математических операциях. В этой статье объясняется, как сохранить данные в исходном формате, который Excel обрабатывает как текст.

Преобразование чисел в текст при импорте текстовых данных

Для форматирования отдельных столбцов в виде текста при импорте данных в Excel используйте функцию Получить и преобразовать (Power Query). В этом случае импортируется текстовый файл, однако данные проходят те же этапы преобразования, что и при импорте из других источников, таких как XML, Интернет, JSON и т. д.

Откройте вкладку Данные, нажмите кнопку Получить данные и выберите вариант Из текстового/CSV-файла. Если вы не видите кнопку Получить данные, выберите Создать запрос > Из файла > Из текста, найдите нужный файл и нажмите кнопку Импорт.

Excel загрузит данные в область предварительного просмотра. Нажмите кнопку » изменить » в области предварительного просмотра, чтобы загрузить Редактор запросов.

Если какие-либо столбцы нужно преобразовать в текст, выделите их, щелкнув заголовок, затем выберите Главная > Преобразовать > Тип данных > Текст.

Совет: Чтобы выбрать несколько столбцов, щелкните их левой кнопкой мыши, удерживая нажатой клавишу CTRL.

В диалоговом окне Изменение типа столбца выберите команду Заменить текущие, и Excel преобразует выделенные столбцы в текст.

преобразование данных в текст» />

По завершении нажмите кнопку Закрыть и загрузить, и Excel вернет данные запроса на лист.

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

В Excel 2010 и Excel 2013 импортировать текстовые файлы и преобразовывать числа в текст можно двумя способами. Рекомендуется использовать Power Query (для этого нужно скачать надстройку Power Query). Если надстройку Power Query скачать не удается, можно воспользоваться мастером импорта текста. В этом случае импортируется текстовый файл, однако данные проходят те же этапы преобразования, что и при импорте из других источников, таких как XML, Интернет, JSON и т. д.

На ленте откройте вкладку Power Query и выберите Получение внешних данных > Из текста.

Совет: Чтобы выбрать несколько столбцов, щелкните их левой кнопкой мыши, удерживая нажатой клавишу CTRL.

преобразование данных в текст» />

По завершении нажмите кнопку Закрыть и загрузить, и Excel вернет данные запроса на лист.

Применение пользовательского формата для сохранения начальных нулей

Если эту задачу необходимо решить в пределах книги, поскольку она не используется другими программами в качестве источника данных, для сохранения начальных нулей можно использовать пользовательский или специальный формат. Этот способ подходит для цифровых кодов, содержащих менее 16 цифр. Цифровые коды также можно отформатировать с использованием дефисов или других знаков препинания. Например, чтобы сделать телефонные номера более удобными для чтения, можно добавить дефисы между международным кодом, кодом области, префиксом и остальными цифрами.

Преобразование чисел-как-текст в нормальные числа

Если для каких-либо ячеек на листе был установлен текстовый формат (это мог сделать пользователь или программа при выгрузке данных в Excel), то введенные потом в эти ячейки числа Excel начинает считать текстом. Иногда такие ячейки помечаются зеленым индикатором, который вы, скорее всего, видели:

Причем иногда такой индикатор не появляется (что гораздо хуже).

В общем и целом, появление в ваших данных чисел-как-текст обычно приводит к большому количеству весьма печальных последствий:

Читать еще: Как выводить сумму в excel

перестает нормально работать сортировка — «псевдочисла» выдавливаются вниз, а не располагаются по-порядку как положено:
функции типа ВПР (VLOOKUP) не находят требуемые значения, потому как для них число и такое же число-как-текст различаются:
при фильтрации псевдочисла отбираются ошибочно
многие другие функции Excel также перестают нормально работать:
и т.д.

Особенно забавно, что естественное желание просто изменить формат ячейки на числовой — не помогает. Т.е. вы, буквально, выделяете ячейки, щелкаете по ним правой кнопкой мыши, выбираете Формат ячеек (Format Cells) , меняете формат на Числовой (Number) , жмете ОК — и ничего не происходит! Совсем!

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

Способ 1. Зеленый уголок-индикатор

Если на ячейке с числом с текстовом формате вы видите зеленый уголок-индикатор, то считайте, что вам повезло. Можно просто выделить все ячейки с данными и нажать на всплывающий желтый значок с восклицательным знаком, а затем выбрать команду Преобразовать в число (Convert to number) :

Все числа в выделенном диапазоне будут преобразованы в полноценные.

Если зеленых уголков нет совсем, то проверьте — не выключены ли они в настройках вашего Excel (Файл — Параметры — Формулы — Числа, отформатированные как текст или с предшествующим апострофом).

Способ 2. Повторный ввод

Если ячеек немного, то можно поменять их формат на числовой, а затем повторно ввести данные, чтобы изменение формата вступило-таки в силу. Проще всего это сделать, встав на ячейку и нажав последовательно клавиши F2 (вход в режим редактирования, в ячейке начинает мигаеть курсор) и затем Enter. Также вместо F2 можно просто делать двойной щелчок левой кнопкой мыши по ячейке.

Само-собой, что если ячеек много, то такой способ, конечно, не подойдет.

Способ 3. Формула

Можно быстро преобразовать псевдочисла в нормальные, если сделать рядом с данными дополнительный столбец с элементарной формулой:

Двойной минус, в данном случае, означает, на самом деле, умножение на -1 два раза. Минус на минус даст плюс и значение в ячейке это не изменит, но сам факт выполнения математической операции переключает формат данных на нужный нам числовой.

Само-собой, вместо умножения на 1 можно использовать любую другую безобидную математическую операцию: деление на 1 или прибавление-вычитание нуля. Эффект будет тот же.

Способ 4. Специальная вставка

Этот способ использовали еще в старых версиях Excel, когда современные эффективные менеджеры под стол ходили зеленого уголка-индикатора еще не было в принципе (он появился только с 2003 года). Алгоритм такой:

в любую пустую ячейку введите 1
скопируйте ее
выделите ячейки с числами в текстовом формате и поменяйте у них формат на числовой (ничего не произойдет)
щелкните по ячейкам с псевдочислами правой кнопкой мыши и выберите команду Специальная вставка (Paste Special) или используйте сочетание клавиш Ctrl+Alt+V
в открывшемся окне выберите вариант Значения (Values) и Умножить (Multiply)

По-сути, мы выполняем то же самое, что и в прошлом способе — умножение содержимого ячеек на единицу — но не формулами, а напрямую из буфера.

Способ 5. Текст по столбцам

Если псеводчисла, которые надо преобразовать, вдобавок еще и записаны с неправильными разделителями целой и дробной части или тысяч, то можно использовать другой подход. Выделите исходный диапазон с данными и нажмите кнопку Текст по столбцам (Text to columns) на вкладке Данные (Data) . На самом деле этот инструмент предназначен для деления слипшегося текста по столбцам, но, в данном случае, мы используем его с другой целью.

Пропустите первых два шага нажатием на кнопку Далее (Next) , а на третьем воспользуйтесь кнопкой Дополнительно (Advanced) . Откроется диалоговое окно, где можно задать имеющиеся сейчас в нашем тексте символы-разделители:

После нажатия на Готово Excel преобразует наш текст в нормальные числа.

Способ 6. Макрос

Если подобные преобразования вам приходится делать часто, то имеет смысл автоматизировать этот процесс при помощи несложного макроса. Нажмите сочетание клавиш Alt+F11 или откройте вкладку Разработчик (Developer) и нажмите кнопку Visual Basic. В появившемся окне редактора добавьте новый модуль через меню Insert — Module и скопируйте туда следующий код:

Теперь после выделения диапазона всегда можно открыть вкладку Разрабочик — Макросы (Developer — Macros) , выбрать наш макрос в списке, нажать кнопку Выполнить (Run ) — и моментально преобразовать псевдочисла в полноценные.

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

С датами бывает та же история. Некоторые даты тоже могут распознаваться Excel’ем как текст, поэтому не будет работать группировка и сортировка. Решения — те же самые, что и для чисел, только формат вместо числового нужно заменить на дату-время.

Читать еще: Как закрепить ячейку в excel

Тема: Длинное число в Excel

Опции темы

Поиск по теме

Длинное число в Excel

Помогите разобраться.
Я в документе Excel в одной из ячеек пишу очень длинное число- расчетный счет. И вместо нужных мне последних трех цифр получаются нули. Формат и общий делаю, и числовой. Не получается. Что нужно сделать?

нужно установить текстовый формат (или набрать перед номером счета апостроф).

текстовый формат дает вообще бред- набор цифр и букв (!)
А что такое «апострф» (извините, я чайник)

Это как? Вводите цифры. а они превращаются в буквы? Вы сначала набираете число, а потом применяете формат, или наоборот?

сначала- набрала число. Получила нули. Потом стала менять форматы.
Текстовый дал, например: 4,07028Е+19

Сделала наоборот- и все получилось

длинное число в Экселе вводится нормально, для ячейки формата — число ( я вводил и 20 и 25 знаков. ).
номер расчетного счета числом не является, т.к. по определению, делится не на единицы, десятки, сотни и т.д. а на фасеты т.е. группы цифр длина которых устанавливается произвольно.
Поэтому, логичнее использовать здесь текстовый, а не числовой формат ячейки.
Если формат ячейки установить самому, а не довериться Экселю, все будет отражено правильно.
ИМХО

Ошибочка
Вы нам показали экспоненциальную запись числа. Excel так «нещадно» поступает, если для отображения числа недостаточно ширины столбца. Он (Excel) по доброте своей неодушевленной пытается нам помочь.
Наибольшее возможное число в Excel — 9E+307.

Не Станиславский, но не верю
Очень хочется увидеть в Excel ЧИСЛОВУЮ запись ЧИСЛА длиной более 15 знаков с младшими разрядами, отличными от нуля.

Утверждение правильно в части «числом не является».
Но ведь р/счет может быть записан и без пробелов (без деления на «фасеты») и это не является нарушением. В такой записи набор цифр образует число, которое может участвовать в математических операциях.

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

Ворчун (может, и не совсем старый? ), мой текст относился к сообщению YUM о возможности ввода такого длинного числа. Потому-то и «Очень хочется увидеть. «.

Речь не об осмысленности математических операций, а о том, что ЕВС при вводе р/счета получает не совсем то, что нужно. Т.е. автор вводит не набор «фасеток», а последовательность символов, которая является-таки числом.
И вопрос не в том, что эту последовательность можно разделить на СтоМиллионовТысяч. Эта последовательность — ЧИСЛО и воспринимается программой как число.
Поэтому данные такого вида должны вводиться как текст: предварительно выставив текстовый формат ячейки; добавив где-либо пробелы; поставив апостроф перед данными.

Последовательность символов является последователеностью символов, просто разные приложения «по умолчанию» интерпретируют эту последовательность по-разному. Ексель любую последовательность сначала пытается интерпретировать как число в том или ином формате. Если ему это удается, то результат интерпретации заносится в ячейку. Если как число последовательность не интерпретируется, то Ексель пытается интерпретировать ее как текст или формулу. Если та же последовательность вводится в Вроде, то она интерпретируется как текст. А спор о том, чем является номер расчетного счета, числом или текстом, смысла не имеет. Номер счета в зависимости от решениях задач можно расматривать и как число, и как текст.

Я не пытаюсь доказать, что р/счет — число или не число.
Разговор о конкретной ситуации: при заполнении листа данными введенная в ячейку информация была воспринята Excel’ем как число.

Почему? Потому что для него это число.
Как обойти? Показать табличному монстру, что это текст.

Доброго времени суток, уважаемые)

При составление различных расчетных таблиц в EXSEL получается вот какая ситуация: при расчете 10839,60 * 2,6% = 281,8296. По-моему мнению, это 281,83, а по EXSEL так и есть 281,8296. Даже если выставить формат ячейки «числовой»- «2 знака после запятой», EXSEL все равно держит «в уме» остальные цифры и при суммировании их учитывает.
Когда в таблице 20 строк (например, по количеству человек) итоговая сумма, чаще всего, получается на 1 или 2 копейки больше. Приходится каждый раз копировать полученные значения, вставлять через «Специальная вставка» — «значение» в эту же колонку, и в ручную убирать лишнее знаки после запятой.

Может быть есть какие-то настройки.

Читать еще: Документ excel онлайн

Доброго времени суток, уважаемые)

Может быть есть какие-то настройки.

Когда в таблице 20 строк (например, по количеству человек) итоговая сумма, чаще всего, получается на 1 или 2 копейки больше.

Может быть есть какие-то настройки.

есть: параметры — вычисления — точность как на экране. позволяет избавиться от многочисленных «ОКРУГЛ()» в ячейках.

Но копейка всё равно может вылезти.
Ибо:
1,00/3,00=0,33
0,33*3,00=0,99

Доброго времени суток, уважаемые)

Может быть есть какие-то настройки.

Дело в том, что формат хранения чисел актуален не только для Экселя.
Проблема «лишней копейки» описана на форуме не один десяток раз. В результате, удалось-таки убедить составителей отчетности в ПФР в том, что нельзя два, взаимоконтролируемых отчета, составлять с копейками и без. (Не знаю, надолго ли).
На одном из форумов программистов проблему вменяемого округления предлагалось решать так : умножаем оба числа на миллион. Делим, множим, вычитаем, а уже полученный итог делим обратно на миллион.
Говорят, что при работе с целыми числами компьютеры сильно умнеют
Может быть это вариант Вам поможет?

Как отобразить текст и число в одной ячейке

Для того чтобы в одной ячейке совместить как текст так и значение можно использовать следующие способы:

Конкатенация;
Функция СЦЕПИТЬ;
Функция ТЕКСТ;
Пользовательский формат.

Разберем эти способы и рассмотрим плюсы и минусы каждого из них.

Использование конкатенации

Один из самых простых способов реализовать сочетание текста и значения — использовать конкатенацию (символ &).

Допустим ячейка A1 содержит итоговое значение 123,45, тогда в любой другой ячейке можно записать формулу
=»Итого: «&A1

В итоге результатом будет следующее содержание ячейки Итого: 123,45.

Это простое решение, однако имеет много минусов.

Результатом формулы будет текстовое значение, которое нельзя будет использовать при дальнейших вычислениях.
Значение ячейки A1 будет выводится в общем формате, без возможности всякого форматирования. В следствие чего этот метод не всегда применим.

Применение функции СЦЕПИТЬ

Аналогичное простое решение, но с теми же недостатками — использование функции СЦЕПИТЬ. Применяется она так: =СЦЕПИТЬ(«Итого: «;A1). Результаты ее использования аналогичные:

Применение функции ТЕКСТ

Функция ТЕКСТ позволяет не только объединить текст и значение, но еще и отформатировать значение в нужном формате. Если мы применим следующую формулу =ТЕКСТ(A1;»»»Итого: «»##0»), то мы получим такой результат Итого: 123.

В качестве второго аргумента функция ТЕКСТ принимает строку с числовым форматом. Более подробно о числовых форматах вы можете прочитать в статье Применение пользовательских форматов.

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

Использование пользовательского формата

Не такой простой способ как предыдущие, но наиболее функциональный. Заключается в применении к итоговой ячейки пользовательского числового формата. Чтобы добавить текст «Итого» к ячейке A1 необходимы следующие действия:

Выберите ячейку A1.
Откройте диалоговое окно Формат ячейки.
В поле Тип укажите нужный формат. В нашем случае «Итого: «# ##0.

В результате ячейка A1 будет содержать Итого: 123.

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

Плюсы и минусы методов

В таблице далее сведены плюсы и минусы. В зависимости от ситуации можно пользоваться тем или иным способом обращая на особенности каждого.