Іноді виникає потреба забезпечити збереження нулів на початку чисел у програмі Excel. Наприклад, коли ви формуєте таблицю з артикулами, важливо, щоб цифри відображалися у повному вигляді.
По суті, необхідно, щоб цифри були написані аналогічно тим, що представлені на ілюстрації нижче.
Отже, я продемонструю кілька варіантів, як можна перетворити числа в потрібний формат.
Усі методи, безумовно, мають свої переваги та недоліки, про що я детальніше зупинюся у статті.
Метод 1: модифікуємо формат комірки
У нас є ідентифікатори товарів в магазині, як показано на зображенні нижче.
І нам необхідно вносити ці самі ID, приміром, у форматі 00001, але Excel автоматично перетворює його на 1.
Суть полягає в тому, що Excel прагне спростити числові значення. Він порівнює, наприклад, 00002 і 2, усвідомлюючи, що ці числа однакові (з точки зору значення) і трансформує 00002 в спрощену форму 2.
Проте нам все ж потрібно зафіксувати 00002, після чого можемо змінити тип комірки на текстовий.
Послідовний гід:
- Оберіть клітинки з цифрами, які слід записати з нулями;
- Перейдіть до розділу «Головна» та оберіть формат «Текстовий»;
У принципі, це все. Тепер спробуйте ще раз ввести ваші числа (необхідно їх заново записати. Числа, які Excel вже конвертував у «спрощений» формат, не можна буде повернути назад).
Зверніть увагу: цей метод може бути непридатним, якщо ви плануєте використовувати ці осередки для розрахунків у майбутньому, оскільки вони зараз представлені в текстовому форматі, а обчислення можливі лише з числовими значеннями.
Метод 2: визначаємо формат відображення цифр
Цей метод надасть вам можливість використовувати зафіксовані числа у подальших розрахунках. Зміст у чому полягає:
Числа можна представляти по-різному, приміром, 2000 та 2000.00 означають одне й те ж. Відповідно до цього, ми можемо лише змінити спосіб, у який число відображається. Інакше кажучи, ми можемо надати числу інший вигляд, але формат комірки залишиться числовим.
Таким чином, вміст осередку залишиться незмінним.
Інструкція по етапах:
- Оберіть необхідні комірки;
- Натисніть на «Головна» і оберіть стрілку, що спрямована вниз, в секції «Число»;
- У вікні на вкладці «Число» натисніть на «(усі формати)»;
Ось перероблений текст з збереженням основного змісту:
- В полі “Тип” (де на даний момент зазначено “Основний”) введіть “00000”;
- Будь ласка, підтверджуйте.
Отже, що сталося: ми змінили формат відображення всіх чисел у виділених клітинках на п’ятизначний. Це означає, що всі числа в цих клітинках будуть показуватися щонайменше у вигляді п’яти знаків. Наприклад, цифра “2” тепер з’являтиметься як “00002”. А число “372847” відображатиметься у звичному вигляді – “372847”.
У цілому, усе залежить від вашої конкретної ситуації. Ви можете ввести стільки нулів у полі «Тип», скільки вважаєте за необхідне. Також існує можливість налаштувати відображення у десятизначному форматі та інше.
Зверніть увагу: даний метод діє лише для осередків, які мають формат «Число». Якщо ваш осередок буде у текстовому форматі, цей спосіб не буде ефективним.
Метод 3: використання текстової функції
Таким чином, необхідно усвідомлювати, що функція ТЕКСТ змінює лише формат конкретного значення, а не всієї клітини.
Однак загалом, цей метод підходить, якщо вам потрібно, щоб число відображалось саме так, як ви цього бажаєте.
Не забувайте, що активація цієї можливості змінить значення на текстовий формат. Це означає, що для Excel ваше число (наприклад, 00002) сприйматиметься як звичайний текст, що унеможливить його використання в розрахунках.
Формула виглядає наступним чином: = Текст(A2; “00000”)
Щодо значення, яке зберігається в комірці “A2”.
Таким чином, число буде представлено у п’ятизначному вигляді (наприклад, якщо число дорівнює “3”, воно перетвориться на “00003”).
Перевагою цього підходу є те, що ці числа можна буде знаходити за допомогою функцій пошуку. У зв’язку з тим, що вони трактуються як звичайний текст, пошук по тексту буде ефективним.
Зверніть увагу: цей метод може виявитися непридатним для вас, якщо в подальшому плануєте використовувати ці значення для обчислень, адже вони тепер сприйматимуться як простий текст.
Метод 4: коригуємо довжину рядка
Недоліком методу з використанням текстової функції є те, що вона не може перетворювати числа на текст, коли ці числа поєднуються з літерними символами. У такому випадку вона просто не буде діяти.
В такому випадку можна застосувати метод з використанням функцій ПОВТОР та ДЛСТР.
Формула виглядає наступним чином:
=ПОВТОР(0;5-ДЛИНА(A2))&A2
Під час виклику цієї функції довжина стрічки перетвориться на п'ятизначну. Це свідчить про те, що всі числа будуть формалізовані у п'ятизначному форматі (наприклад, число “3” буде виглядати як “00003”).
Яка роль цієї функції:
- Функція ДЛСТР(A2) визначає кількість символів у рядку з числом.
- Формула =ПОВТОР(0;5-ДЛСТР(A2)) обчислює, скільки нулів потрібно додати перед нашим числом. Це ж правило діє для кожного нового рядка.
Якщо коротко, ця функція визначає, скільки нулів слід додати на початок числа, щоб отримати загальну кількість знаків, рівну 5 (оскільки в формулі ми вказали саме 5).
Метод 5: застосування Visual Basic
Метод, що базується на Visual Basic, ідеально підходить для тих, хто регулярно має потребу фіксувати числові дані у конкретному форматі.
Цей код розробляє вашу індивідуальну функцію для виконання такої задачі:
Function AddLeadingZeroes(ref As Range, Length As Integer)
Dim i As Integer
Dim Result As String
Dim StrLen As Integer
StrLen = Len(ref)
For i = 1 To Length
If i <= StrLen Then
Result = Result & Mid(ref, i , 1)
Else
Result = "0" & Result
End If
Next i
AddLeadingZeroes = Result
End Function
Щоб функціональність автоматичного додавання чисел з нулями з’явилася у вашому Excel, необхідно вставити вищезгаданий код у модуль Visual Basic.
Крім того, є можливість поділитися цією функцією з колегами.
