Вам когда-нибудь приходилось импортировать или вводить в Excel данные, содержащие начальные нули (например, 00123) или большие числа (например, 1234 5678 9087 6543)? Это могут быть номера социального страхования, телефонные номера, номера кредитных карт, коды продуктов, номера счетов или почтовые индексы. Excel автоматически удаляет начальные нули и преобразует большие числа в экспоненциальное представление (например, 1,23E+15), чтобы их можно было использовать в формулах и математических операциях. В этой статье объясняется, как сохранить данные в исходном формате, который Excel обрабатывает как текст.
Настройка автоматического преобразования данных
Важно: Эта функция доступна в Excel для Microsoft 365, Excel для Microsoft 365 для Mac, Excel 2024, Excel 2024 для Mac.
Используйте функцию автоматического преобразования данных Excel, чтобы изменить поведение Excel по умолчанию для следующих автоматических преобразований данных:
-
Удаление начальных нулей из числового текста и преобразование в число.
-
Усечение числовых данных до 15 цифр точности и преобразование в число, отображаемое в научной нотации.
-
Преобразование числовых данных, окружающих букву "E", в научную нотацию.
-
Преобразование непрерывной строки букв и цифр в дату.
Дополнительные сведения см. в статье Настройка автоматического преобразования данных.
Преобразование чисел в текст при импорте текстовых данных
Для форматирования отдельных столбцов в виде текста при импорте данных в Excel используйте функцию Получить и преобразовать (Power Query). В этом случае импортируется текстовый файл, однако данные проходят те же этапы преобразования, что и при импорте из других источников, таких как XML, Интернет, JSON и т. д.
-
Перейдите на вкладку Данные , а затем — Из текста или CSV рядом с кнопкой Получить данные . Если вы не видите кнопку Получить данные, выберите Создать запрос > Из файла > Из текста, найдите нужный файл и нажмите кнопку Импорт.
-
Excel загрузит данные в область предварительного просмотра. В области предварительного просмотра нажмите кнопку Изменить, чтобы загрузить Редактор запросов.
-
Если какие-либо столбцы нужно преобразовать в текст, выделите их, щелкнув заголовок, затем выберите Главная > Преобразовать > Тип данных > Текст.
Совет: Чтобы выбрать несколько столбцов, щелкните их левой кнопкой мыши, удерживая нажатой клавишу CTRL.
-
Затем выберите Заменить текущий в диалоговом окне Изменение типа столбца , и Excel преобразует выбранные столбцы в текст.
-
По завершении выберите Закрыть & загрузить, и Excel вернет данные запроса на лист.
Если в дальнейшем ваши данные изменятся, на вкладке Данные нажмите кнопку Обновить, и Excel их автоматически обновит и преобразует.
Применение пользовательского формата для сохранения начальных нулей
Если эту задачу необходимо решить в пределах книги, поскольку она не используется другими программами в качестве источника данных, для сохранения начальных нулей можно использовать пользовательский или специальный формат. Этот способ подходит для цифровых кодов, содержащих менее 16 цифр. Цифровые коды также можно отформатировать с использованием дефисов или других знаков препинания. Например, чтобы сделать телефонные номера более удобными для чтения, можно добавить дефисы между международным кодом, кодом области, префиксом и остальными цифрами.
|
Цифровой код |
Пример |
Пользовательский числовой формат |
|---|---|---|
|
Общественный безопасность |
012345678 |
000-00-0000 012-34-5678 |
|
Телефон |
0012345556789 |
00-0-000-000-0000 00-1-234-555-6789 |
|
Почтовый код |
00123 |
00000 00123 |
Инструкции
-
Выделите ячейку или диапазон ячеек, которые вы хотите отформатировать.
-
Нажмите клавиши CTRL+1, чтобы открыть диалоговое окно Формат ячеек.
-
Перейдите на вкладку Число , затем в списке Категория выберите Пользовательский , а затем в поле Тип введите формат номера, например 000-00-0000 для номера социального страхования или 00000 для пятизначного почтового индекса.
Совет: Вы также можете выбрать Специальный, а затем выбрать Почтовый индекс, Почтовый индекс + 4, Номер телефона или Номер социального страхования.
Дополнительные сведения о пользовательских кодах см. в статье Создание и удаление пользовательских числовых форматов.
Примечание: Этот способ не позволяет восстановить начальные нули, которые были удалены до форматирования. Он действует только на числа, введенные после применения формата.
Применение функции ТЕКСТ для форматирования
Для преобразования данных в нужный формат можно использовать расположенный рядом с ними пустой столбец и функцию ТЕКСТ.
|
Цифровой код |
Пример (в ячейке A1) |
Функция ТЕКСТ и новый формат |
|---|---|---|
|
Общественный безопасность |
012345678 |
=TEXT(A1;"000-00-0000") 012-34-5678 |
|
Телефон |
0012345556789 |
=TEXT(A1;"00-0-000-000-0000") 00-1-234-555-6789 |
|
Почтовый код |
00123 |
=ТЕКСТ(A1,"00000") 00123 |
Округление номеров кредитных карт
Максимальная точность Excel составляет 15 значатся значащее число. Это означает, что для любого числа, содержащего 16 или более цифр, таких как кредитное карта число, все числа после 15-й цифры округляются до нуля. В случае числовых кодов, которые имеют 16 цифр или больше, необходимо использовать текстовый формат. Для этого можно выполнить одно из двух действий:
-
Форматирование столбца в виде текста
Выберите диапазон данных и нажмите клавиши CTRL+1 , чтобы открыть диалоговое окно Форматирование ячеек . На вкладке Число выберите Текст.
Примечание: Этот способ не позволяет изменить числа, которые были введены ранее. Он действует только на числа, введенные после применения формата.
-
Использование апострофа
Перед числом можно ввести апостроф ('), и Excel будет обрабатывать его как текст.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
