Вы когда-нибудь импортируете или вводите в 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 или получить поддержку в сообществах.