Збереження початкових нулів і великих чисел

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

Перетворення чисел на текст під час імпорту текстових даних

Щоб відформатувати окремі стовпці як текст під час імпорту даних до Excel, скористайтеся набором функцій Завантажити та перетворити (Power Query). Так ви імпортуєте текстовий файл, проте дані пройдуть через етапи перетворення, передбачені для імпорту даних з інших джерел, як-от XML, Інтернет, JSON тощо.

  1. Перейдіть на вкладку Дані й натисніть кнопку З текстового файлу або CSV-файлу біля кнопки Отримати дані. Якщо кнопка Отримати дані не відображається, виберіть Новий запит > З файлу > З тексту, а потім знайдіть потрібний текстовий файл і натисніть кнопку Імпорт.

  2. Excel завантажить дані до області перегляду. Натисніть кнопку Редагувати в області перегляду, щоб відобразити область Редактор запитів.

  3. Якщо певні стовпці потрібно перетворити на текст, виберіть їх, клацнувши заголовок, а потім на вкладці Головна в групі Перетворення виберіть Текст зі списку Тип даних.

    Power Query – дані після перетворення на текст

    Порада : Щоб вибрати кілька стовпців, клацніть їх лівою кнопкою миші, утримуючи натиснутою клавішу Ctrl.

  4. Потім у діалоговому вікні Змінити тип стовпця натисніть кнопку Замінити поточне значення, і Excel перетворить вибрані стовпці на текст.

    "Завантажити та перетворити" > перетворення даних на текст
  5. Коли все буде готово, натисніть кнопку Закрити й завантажити, і Excel відобразить дані запиту на аркуші.

    Якщо в майбутньому ваші дані зміняться, на вкладці Дані натисніть кнопку Оновити все. Excel автоматично оновить і перетворить дані.

В Excel 2010 та Excel 2013 імпортувати текстові файли й перетворювати числа на текст можна двома способами. Радимо використовувати надбудову Power Query, яку можна завантажити тут. Якщо надбудову Power Query завантажити не вдається, можна скористатися майстром імпорту тексту. Так ви імпортуєте текстовий файл, проте дані пройдуть через етапи перетворення, передбачені для імпорту даних з інших джерел, як-от XML, Інтернет, JSON тощо.

  1. Перейдіть на вкладку Power Query й виберіть Отримати зовнішні дані > З тексту.

  2. Excel завантажить дані до області перегляду. Натисніть кнопку Редагувати в області перегляду, щоб відобразити область Редактор запитів.

  3. Якщо певні стовпці потрібно перетворити на текст, виберіть їх, клацнувши заголовок, а потім на вкладці Головна в групі Перетворення виберіть Текст зі списку Тип даних.

    Power Query – дані після перетворення на текст

    Порада : Щоб вибрати кілька стовпців, клацніть їх лівою кнопкою миші, утримуючи натиснутою клавішу Ctrl.

  4. Потім у діалоговому вікні Змінити тип стовпця натисніть кнопку Замінити поточне значення, і Excel перетворить вибрані стовпці на текст.

    "Завантажити та перетворити" > перетворення даних на текст
  5. Коли все буде готово, натисніть кнопку Закрити й завантажити, і Excel відобразить дані запиту на аркуші.

    Якщо в майбутньому ваші дані зміняться, на вкладці Дані натисніть кнопку Оновити все. Excel автоматично оновить і перетворить дані.

Використання спеціального формату для збереження початкових нулів

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

Цифровий код

Приклад

Спеціальний числовий формат

Номер соціального
страхування

012345678

000–00–0000 
012–34–5678 

Телефон

0012345556789

00–0–000–000–0000 
00–1–234–555–6789 

Поштовий
індекс

00123

00000 
00123 

Інструкції    

  1. Виберіть клітинку або діапазон клітинок, які потрібно відформатувати.

  2. Натисніть клавіші Ctrl+1, щоб відкрити діалогове вікно Формат клітинок.

  3. Перейдіть на вкладку Число та зі списку Числові формати виберіть (усі формати), а потім у поле Тип введіть числовий формат, наприклад 000-00-0000 для номера соціального страхування або 00000 для п’ятизначного поштового індексу.

    Порада : Крім того, можна клацнути Додатковий, а потім вибрати Індекс, Індекс + 4, Телефонний номер або Табельний номер.

    Докладні відомості про спеціальні формати див. в статті Створення та видалення спеціального числового формату.

    Примітка : Цей спосіб не дає змогу відновити початкові нулі, вилучені до форматування. Він впливає тільки на числа, введені після застосування формату.

Використання функції TEXT для форматування

Щоб подати дані в потрібному форматі, можна скористатися розташованим поруч із ними порожнім стовпцем і функцією TEXT.

Цифровий код

Приклад (у клітинці A1)

Функція TEXT і новий формат

Номер соціального
страхування

012345678

=TEXT(A1,"000–00–0000")
012–34–5678

Телефон

0012345556789

=TEXT(A1,"00–0–000–000–0000")
00–1–234–555–6789

Поштовий
індекс

00123

=TEXT(A1,"00000")
00123

Округлення номерів кредитних карт

Максимальна точність чисел в Excel становить 15 значущих цифр. Це означає, що в будь-якому числі, яке складається з 16 й більше цифр, наприклад у номері кредитної карти, усі цифри після 15-ї округлюються до нуля. Для цифрових кодів, які складаються із 16 й більше цифр, слід використовувати текстовий формат. Для цього виконайте одну з наведених нижче дій.

  • Форматування стовпця як тексту

    Виберіть діапазон даних і натисніть клавіші Ctrl+1, щоб відкрити діалогове вікно Формат клітинок. На вкладці Число виберіть Текстовий.

    Примітка : Цей спосіб не дає змогу змінити числа, введені раніше. Він впливає тільки на числа, введені після застосування формату.

  • Використання символу апострофа

    Перед числом можна ввести апостроф (), і Excel оброблятиме його як текст.

На початок сторінки

Потрібна додаткова довідка?

Ви завжди можете поставити запитання експерту в спільноті Tech (у розділі Excel), отримати підтримку в спільноті, що допомагає знайти відповіді на запитання, або запропонувати нову функцію чи вдосконалення на форумі Excel User Voice.

Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

Дякуємо за відгук! Схоже, вам може стати в нагоді допомога одного з наших спеціалістів служби підтримки Office, з яким ми вас можемо з’єднати.

×