Длинные числа отображаются неправильно в Excel
Обновлено: 03.07.2024
Я пытаюсь обработать некоторые данные в Excel. Данные включают числовые номера счетов и другие длинные числовые строки (например, MEID мобильных телефонов). Я не выполняю математические операции с этими строками, поэтому хочу, чтобы Excel обрабатывал их как обычный текст.
Вот что меня бесит (это Excel 2010):
- Возьмите длинное число, например 1240800388917, и вставьте его в ячейку на новом листе.
- Формат ячеек Excel по умолчанию является общим, поэтому строка представлена в экспоненциальном представлении как 1,2408E+12
- Щелкните правой кнопкой мыши по ячейке, выберите "Формат ячеек", установите формат "Текст".
Ячейка по-прежнему отображается в экспоненциальном представлении, несмотря на то, что установлен текстовый формат.
Теперь, если я сделаю шаги в другом порядке:
- Отформатируйте пустую ячейку как текст. Щелкните правой кнопкой мыши ячейку, выберите "Формат ячеек", установите формат "Текст".
- Возьмите длинное число, например 1240800388917, и вставьте его в текстовую ячейку.
Теперь ячейка отображается в виде строки, а не в экспоненциальном представлении.
Результаты, остающиеся в экспоненциальном представлении, даже если ячейка отформатирована как текст, просто кажутся мне нарушенными. Я видел предлагаемые обходные пути, такие как: использовать импорт CSV и установить текстовый формат, добавить символ пробела в начало каждой числовой строки и другие.
Есть ли простой хороший способ легко сохранить эти строки в текстовом формате?
Зачем Excel это делает?
14 ответов 14
Ах, воспоминания о том, как я копался в данных, когда я выполнял перекрестную проверку массивных чисел в Excel (никогда больше). Когда вы вводите в Excel длинные числовые строки, попробуйте, скажем, 12345678901234567890 (20 цифр), Excel обычно < em>преобразовать его для вас, что означает, что 20-значное число, которое вы только что набрали, было сокращено до пятнадцати значащих цифр.
Также обратите внимание: преобразование представляет собой усечение, а не округление. (12345678901234567890 равно 1.23456789012346E+19, но Excel покажет вам 1.23456789012345E+19)
Это происходит в точке ввода, поэтому после ее ввода все дополнительные сведения теряются, поэтому даже если вы сообщаете Excel, что на самом деле имели в виду текст, а не число, вам не повезло, и, следовательно, почему ваша первая последовательность не работает.
Да, я вижу такое поведение, когда моя числовая строка содержит более 15 значащих цифр. 12345678901234567890 становится 1.23456789012345E+19, как вы говорите. Когда я нажимаю на ячейку, в поле редактирования отображается строка, усеченная и дополненная нулями, как 12345678901234500000. Странно то, что она делает то же самое для чисел, которые не превышают 15-значный предел Excel. Если я вставлю 12345678901234, оно отобразится как 1.23457E+13, но когда я нажму на него, в поле редактирования по-прежнему будет отображаться исходная строка. Теперь, что странно, если я отформатирую эту ячейку в текст, она все равно будет отображаться в экспоненциальном представлении. Раздражает.
На самом деле это небольшая особенность отображения: установка формата с общего на текстовый не сразу отобразит его как исходную строку, но если вы войдете и отредактируете данные (например, просто щелкните в зоне редактирования и нажмите «Выйти»), он обновится и станет именно тем, что вы ввели.
Если я нажму на ячейку, я увижу, что в Excel есть все число, но независимо от того, сделаю ли я все ячейки текстом, он все равно будет использовать экспоненциальное представление. Так обидно!
Это сработало для меня в Excel 2010. Просто выберите столбец, щелкните правой кнопкой мыши, выберите "Формат ячеек", "Пользовательский" и выберите параметр, который говорит 0 (второй вариант ниже "Общие").
Это намного лучше, чем принятый ответ, в котором говорится: «Вам немного не повезло»! У меня был CSV-файл с этой проблемой, и пользовательское решение для форматирования работало отлично.
В моем тесте это по-прежнему усекается. Используя Excel 2013, я ввел следующее в ячейку 12345678901234567890. Когда я нажимаю ввод, это отображается как 1.23457E+19, а базовое значение изменяется на 12345678901234500000, поэтому происходит потеря данных. Это не отвечает на исходный вопрос.
Этот ответ исправляет форматирование, но не сохраняет введенные данные. Вот почему я не могу принять это как правильный ответ. Я разместил короткое видео, чтобы показать. Когда вы следуете этим указаниям, а затем вводите длинное целое число, например 12345678901234567890, вы теряете точность при обработке его как числового, даже если оно отображается в ненаучной нотации. Excel обрабатывает его как значение 12345678901234500000. Только рассматривая его как текст, вы можете сохранить все цифры. См. youtu.be/7EDcR7TQpYs
Попробуйте это. это работает для меня, Excel 2013.
где a1 содержит числовое значение. После этого скопируйте столбец и вставьте в исходный столбец.
Я обнаружил, что в Office 2010, если сначала отформатировать столбец, а затем вставить данные, большие числа будут отображаться правильно.
Возможно, это связано с тем, что он хранится в виде строки.Что произойдет, если вы сделаете другую ячейку, скажем, содержимым ячейки, умноженным на 2?
Это работает в Excel 2016/Office 365. Я выбрал все ячейки, отформатировал их в текст, а затем вставил скопированные данные, и они не отображались с надоедливой научной нотацией. Спасибо!
Это то, что также сработало для меня в Google Таблицах. «Формат > Число > 0» перед вставкой в vals и не преобразовывался в научное представление, а также НЕ терял конечные значения (округление vals в меньшую сторону), что происходило слишком часто.
Один апостроф ' перед числом заставит Excel рассматривать число как текст (включая выравнивание по левому краю по умолчанию). И если у вас есть помеченные ошибки, это будет отображаться как число, сохраненное как текстовая ошибка в ячейке.
Серин, справедливое замечание: никогда не недооценивайте способность Excel (или Microsoft) делать что-то совершенно необъяснимое или повторяемое. Однако трюк с апострофом обычно работает так, как рекламируется.
Я знаю о возрасте вопроса, но это тот, который я получил после поиска в Google и который не ответил на мой вопрос (почему форматирование в виде текста не работает и почему число сокращено до 15 цифры после форматирования в текст).
Короткий ответ: вы не можете работать над своим номером так, как вам нравится ПОСЛЕ того, как вы ввели числовое значение и нажали Enter . В тот момент, когда вы это сделаете, число будет усечено до 15 цифр и представлено в экспоненциальном виде.
Это не ошибка, это функция.
Все, что вы будете делать после этого, будет использовать усеченное значение, поэтому никакие трюки с форматированием не помогут. Однако вы можете использовать параметр «Текст в столбцы», чтобы преобразовать экспоненциальную запись в текст (щелкните заголовок столбца, нажмите «Текст в данные», затем «Разделенные», «Далее», снимите все флажки разделителей, «Выберите текст в формате данных столбца», а затем «Готово»), чтобы значения преобразуются в текст и немедленно отображаются в виде 15-значного числа. Но это будет только 15 цифр, поэтому, если у вас был более длинный номер, остальное потеряно.
Чтобы число в электронной таблице было именно таким, каким вы его ввели, вы должны сохранить его как текст. Таким образом, у вас есть два способа сделать это, но они будут работать только ДО/ВО ВРЕМЯ редактирования:
вам нужно поставить перед ним апостроф или отформатировать ячейки как текст, прежде чем вводить/копировать в него значения,
вы должны отформатировать целевой столбец как текст перед вводом.
Если вы используете метод 1, и он "иногда не работает", то, извините, вы либо делаете что-то не так, либо у вас включена какая-то автозамена (если вы используете апостроф и большое число >15 цифр, которые Excel обрабатывает это как неправильное значение и помечает ячейку предупреждающим сообщением, поэтому это замечание не является личным или критическим).
Метод 2 очень полезен, потому что он работает с копированием и вставкой даже с большими наборами данных.
Другой способ сделать это для массовых чисел — импортировать значения из текстового файла. Осмелюсь сказать, что это единственный способ для большинства ситуаций. И обязательно импортируйте файл, а не просто открывайте его, так как Excel обрабатывает тип csv как собственный формат и, конечно же, обрезает большие числа до 15 цифр.
Теперь последний вопрос. Если вы введете большое числовое значение в ячейку Excel, отформатированную как текст, оно все равно будет отображаться в экспоненциальном представлении, если ячейка недостаточно широка. Это раздражает, но это побочный эффект некоторых внутренних алгоритмов Excel. Просто сделайте ячейку шире, и каждый раз вы будете видеть полное значение.
Office 365 профессиональный плюс переименовывается в Приложения Microsoft 365 для предприятий. Для получения дополнительной информации об этом изменении прочитайте эту запись в блоге.
Симптомы
После ввода длинного числа (например, номера кредитной карты) в ячейку Excel число отображается неправильно в Excel. Например,
Формат чисел по умолчанию в Excel — общий, поэтому в ячейке может отображаться до 11 цифр.
Временное решение
Чтобы обойти эту проблему, воспользуйтесь одним из следующих способов.
Способ 1. Отформатируйте ячейку как текст
Для этого выполните следующие действия:
Щелкните правой кнопкой мыши целевую ячейку и выберите "Формат ячеек".
На вкладке "Число" выберите "Текст" и нажмите "ОК".
Затем введите длинное число. (Обязательно установите формат ячейки, прежде чем вводить число)
Если вы не хотите видеть предупреждающие стрелки, нажмите маленькую стрелку и выберите «Игнорировать ошибку».
Способ 2. Используйте одинарную кавычку
Когда вы вводите длинное число, сначала введите одинарную кавычку (') в ячейке, а затем введите длинное число.
Например, введите '1234567890123456789, и кавычки не будут отображаться после нажатия клавиши ВВОД.
Статьи по теме
Ваше мнение важно для нас! Не стесняйтесь сообщить нам, что вы думаете об этой статье, используя поле для комментариев, расположенное внизу документа. Это позволит нам улучшить содержание. Заранее спасибо!
Вы когда-нибудь вводили длинные числа в Excel и обнаруживали, что цифры в середине были сокращены? На самом деле это из-за экспоненциального представления , которое автоматически сокращает число длиной более 11 цифр.
Как же мы можем полностью отобразить длинные числа в Excel? Одним из решений этой ситуации является преобразование свойства ячеек для отключения экспоненциального представления. Давайте посмотрим.
Подходящее чтение
Вам также может понравиться
Заявление об авторских правах. Любое копирование или использование любых публикаций на этом веб-сайте должно осуществляться с письменного разрешения или авторизации Myofficetricks.
Excel позволяет отображать до 11 цифр. Если введенное число состоит из более чем 11 цифр, оно автоматически выражается в экспоненциальном представлении. Если введенный номер содержит более 15 цифр, все цифры, начиная с 16-й, преобразуются в 0 и не могут быть восстановлены. Итак, как вводить длинные числа в Excel? Excel поддерживает ввод нескольких текстов в одну ячейку. Вы можете ввести длинное число, отобразив длинное число в виде текста.
Помимо ввода длинных чисел (больших чисел), иногда формулы также возвращают длинные числа. Как можно полностью отобразить такое длинное число, превышающее 15 цифр? В этом случае вам нужно преобразовать числа в текст с помощью функции. Если длинные числа представлены в экспоненциальном представлении по умолчанию при создании документа и вы хотите отобразить их все, что мне делать? Тот же метод используется для преобразования чисел в текст.Вы открываете текстовый файл с длинными числами прямо в Excel, все длинные числа по умолчанию также представлены экспоненциальной записью, а числа, превышающие 15 цифр, преобразуются в 0, поэтому их нельзя открыть напрямую, их нужно импортировать в Excel.< /p>
I, Как вводить длинные числа в Excel и отображать их полностью
(I) Преобразование чисел в текст с использованием кавычек (')
<р>1. Выберите или дважды щелкните ячейку, в которую вы хотите ввести длинные числа, введите кавычки ('), затем введите 15-значные длинные числа и нажмите Enter после завершения ввода, все введенные числа отображаются, они не выражаются автоматически в экспоненциальном представлении, а предшествующие кавычки автоматически скрываются; этапы операции показаны на рисунке 1: <р>2. Здесь функция кавычек заключается в преобразовании введенных чисел в текст. Именно благодаря конвертации все числа отображаются полностью.(II) Установите ячейку «Форматы» как текст
<р>1. Выберите ячейку, в которую вы хотите ввести длинные числа (например, A2), нажмите сочетание клавиш Ctrl + 1, откройте диалоговое окно «Формат ячеек», выберите вкладку «Число», а затем выберите «Текст» слева. , нажмите «ОК», установите формат A2 на «Текст», введите длинные цифры, нажмите Enter, все цифры, которые нужно ввести, отображаются, и никакие последующие цифры не превращаются в 0; этапы рабочего процесса, как показано на рисунке 2: <р>2. Если длинные числа готовы, вы можете напрямую скопировать их в ячейки, отформатированные как текст, чтобы они не отображались неполными из-за слишком длинного числа.II. Как отобразить результаты, возвращаемые формулами, в виде длинных чисел в Excel?
<р>1. Дважды щелкните ячейку C2, введите формулу =A2*B2 и нажмите Enter, чтобы вернуть результат, выраженный в экспоненциальном представлении; выберите C2, переместите мышь к маркеру заполнения ячейки в правом нижнем углу C2, и после того, как мышь станет жирным черным знаком плюс, дважды щелкните левой кнопкой, все возвращаемые результаты выражаются в экспоненциальном представлении. Дважды щелкните C2, измените формулу на =TEXT(A2*B2,"0"), нажмите Enter, чтобы вернуть строку длинных чисел, верните оставшиеся результаты методом двойного щелчка маркера заполнения ячейки C2 и все они показывают числа полностью; этапы процесса показаны на рисунке 3: <р>2. Описание формулы:А. Формула =A2*B2 является произведением A2 и B2. Поскольку A2 и B2 являются относительными ссылками на столбцы и строки, при перетаскивании вниз A2 автоматически становится A3, A4 и т. д., а B2 автоматически становится B3, B4 и т. д., поэтому произведение ячеек ниже A2 и B2 может рассчитать.
Б. Формула =ТЕКСТ(A2*B2;"0") используется для округления и преобразования произведения A2 и B2 в текст. Второй аргумент «0» используется для форматирования, «0» означает, что сохраняются только целые числа. Функция Text используется для преобразования чисел, дат и времени в текст.
III, Длинные числа в Excel отменяют научную запись (длинные числа не отображаются должным образом в Excel)
(I) Метод пользовательского формата ячейки
<р>1. Выберите два длинных числа в окне текстового файла, нажмите Ctrl + C, чтобы скопировать, щелкните в любом месте окна Excel, чтобы перейти к окну, выберите ячейку A1 и нажмите Ctrl + V, чтобы вставить два длинных числа в A1 и A2 и автоматически отображать в экспоненциальном представлении. <р>2. Нажмите Ctrl + 1, откройте диалоговое окно «Формат ячеек», выберите вкладку «Число», затем выберите «Пользовательский» в левом нижнем углу диалогового окна, введите 0 в поле «Тип» и нажмите «ОК», затем у длинных чисел отменяется экспоненциальное представление, и отображаются все числа, но последняя цифра чисел в A2 изменилась с 5 на 0; этапы операции показаны на рисунке 4:Совет. Хотя можно полностью отобразить числа, представленные в экспоненциальном представлении, в пользовательском формате ячейки, если длинные числа превышают 15 цифр, все цифры после 15-й цифры превращаются в 0 и не могут быть восстановлены (поскольку Excel содержит не более 15 цифр), поэтому длинные цифры, длина которых превышает 15 цифр, должны быть преобразованы в текст.
(II) Используйте "Текст в столбцы"
<р>1. Выберите A2: A4, перейдите на вкладку «Данные» и нажмите «Текст в столбцы» в группе «Инструменты данных», чтобы открыть диалоговое окно «Мастер преобразования текста в столбцы»; выберите «С разделителями» для «Выберите тип файла, который лучше всего описывает ваши данные», нажмите «Далее»; перейдите к шагу 2, оставьте значение по умолчанию для «Разделителей», нажмите «Далее»; перейдите к шагу 3, выберите «Текст» для «Формат данных столбца» и нажмите «Готово». Отображаются все длинные числа, выраженные в экспоненциальном представлении; этапы рабочего процесса показаны на рисунке 5: <р>2. Этот метод аналогичен вышеописанному. Если число превышает 15 цифр, цифры после 15 цифр будут полностью преобразованы в 0 и не могут быть восстановлены.IV. Длинные числа открываются в Excel, а последние несколько цифр становятся равными 0 (длинные числа не отображаются должным образом в Excel), как решить
(I) Работа в Excel 2019
<р>1. Откройте Excel, создайте пустую книгу, выберите вкладку «Данные», нажмите «Из текста/CSV» в верхнем левом углу окна, откройте диалоговое окно «Импорт данных» и перейдите в папку, в которой должен быть файл. импортированных (например, F:\upimg\Office\Excel\document), дважды щелкните файл "long number.csv", чтобы открыть диалоговое окно "Загрузить". <р>2. Нажмите «Преобразовать данные» в правом нижнем углу диалогового окна, откройте диалоговое окно «Редактор Power Query»; нажмите «Shift», нажмите «Цифра 2», чтобы выбрать два столбца; выберите вкладку «Преобразование», нажмите «Формат» в группе «Текстовый столбец», выберите «Каждое слово с заглавной буквы» (или нижний или верхний регистр) во всплывающем меню, отобразятся длинные числа, которые необходимо выбрать.< /p> <р>3. Нажмите «Файл» в левом верхнем углу экрана, выберите «Закрыть и загрузить», длинные числа импортируются в Excel и отображаются полностью, последние несколько цифр преобразуются в 0; этапы рабочего процесса показаны на рисунке 6:(II) Работа в Excel 2016
<р>1. Откройте Excel, создайте новую пустую книгу, выберите вкладку «Данные», нажмите «Текст в столбцы» в верхнем левом углу экрана, откройте диалоговое окно «Мастер импорта текста» и перейдите в папку, где находится файл для (например, файл G:\Excel\документ), дважды щелкните файл, который необходимо импортировать, чтобы открыть «Мастер преобразования текста в столбцы». <р>2. Выберите «С разделителями» для «Выберите тип файла, который лучше всего описывает ваши данные», «Начать импорт в строке» оставьте значение по умолчанию 1, что означает импорт из строки 1, если не импортировать из первой строки, введите конкретный номер; Нажмите «Далее», чтобы перейти к шагу 2, отмените «Tab» и установите флажок «Запятая» для «Разделителей», а затем установите флажок «Обрабатывать последовательные разделители как один». <р>3. Нажмите «Далее», чтобы перейти к шагу 3. Выберите «Текст» для «Формат данных столбца», затем первый столбец в разделе «Предварительный просмотр данных» станет текстовым, затем выберите второй столбец и снова выберите «Текст», затем второй столбец также станет текстовым. , нажмите «Готово», перейдите в диалоговое окно «Импорт данных», не меняйте «Куда вы хотите поместить данные?», нажмите «ОК». Затем выбранный файл импортируется в Excel и делится на две части. колонки, все длинные числа отображаются полностью и не обнуляются после 15-й цифры.4. Если импортируемые данные имеют несколько столбцов, необходимо выбрать «Текст» для «Формат данных столбца» один раз для каждого столбца на шаге 3, иначе только один столбец будет преобразован в текст. Если вы хотите поместить импортированные данные на другой рабочий лист, выберите «Новый рабочий лист» для «Куда вы хотите поместить данные?».
Читайте также: