Бувають ситуації, коли потрібно додати початкові нулі до набору даних у Excel. Це може статися, якщо ви ведете записи в Excel, наприклад, ідентифікатори співробітників або ідентифікатори транзакцій.
Наприклад, вам може знадобитися послідовно подивитися у набір даних, як показано нижче:
У цьому посібнику ви дізнаєтесь про різні способи додавання початкових нулів у Excel:
- Перетворення формату в текст
- Використання користувацького форматування чисел
- Використання текстової функції
- Використання функцій REPT/LEN
- Використання VBA
Кожен із цих методів має деякі переваги та недоліки (розглянуті в кожному розділі).
Давайте подивимося, як працює кожен із цих.
Додайте провідні нулі, перетворивши формат у текст
Коли використовувати: Якщо у вас є невеликий числовий набір даних, і ви плануєте зробити це редагування вручну.
Припустимо, що у вас є ідентифікатори співробітників відділу маркетингу, як показано нижче, і ви хочете, щоб ці ідентифікатори виглядали послідовними, додавши початкові нулі.
Тому ви намагаєтесь змінити ідентифікатор, ввівши початкові нулі (00001 замість 1).
Але, на ваше здивування, Excel перетворює його назад на 1.
Це відбувається, коли Excel розуміє, що 00001 і 1 - це однакові цифри і повинні дотримуватися однакових правил відображення.
Як би це вас не засмучувало, у Excel є свої причини.
Отже, щоб виконати роботу без викривлення правил Excel, вам доведеться скористатися тим, що це правило не застосовується до форматування тексту.
Отже, ось що вам потрібно зробити:
- Виберіть клітинки, до яких потрібно вручну додати початкові нулі.
- Перейдіть додому → Група номерів і виберіть Текст зі спадного меню.
Це воно!
Тепер, коли ви вводите початкові нулі вручну, Excel з легкістю дотримуватиметься вимог.
Увага: під час перетворення формату на текст деякі функції Excel не працюватимуть належним чином. Наприклад, функція SUM/COUNT ігнорує клітинку, оскільки вона у текстовому форматі.
Додайте провідні нулі за допомогою користувацького форматування чисел
Коли використовувати: Якщо у вас є числовий набір даних, і ви хочете, щоб результат був числовим (а не текстовим).
Коли ви відображаєте число у певному форматі, це не змінює основне значення числа. Наприклад, я можу відобразити число 1000 як 1000 або 1000 або 1000,00 або 001000 чи 26-09-1902 (навіть дати-це числа у бекенді в Excel).
У всіх різних способах відображення числа значення числа ніколи не змінюється. Змінюється лише спосіб його відображення.
Щоб додати початкові нулі, ми можемо відформатувати його таким чином, тоді як базове значення залишиться незмінним.
Нижче наведено кроки для використання цієї техніки для додавання початкових нулів у Excel:
- Виберіть клітинки, до яких потрібно додати початкові нулі.
- Перейдіть додому → Група номерів і натисніть на панель запуску діалогового вікна (маленька нахилена стрілка внизу праворуч). Відкриється діалогове вікно Форматування клітинок. Крім того, ви також можете скористатися комбінацією клавіш: Control + 1.
- У діалоговому вікні Форматування клітинок на вкладці Номер виберіть Спеціальне у списку Категорії.
- У полі Тип введіть 00000
- Натисніть OK.
Це завжди відображатиме всі цифри у вигляді п’яти цифр, де початкові 0 автоматично додаються, якщо число менше 5 цифр. Отже, 10 стане 00010, а 100 стане 00100.
У цьому випадку ми використали шість нулів, але якщо у ваших даних є цифри з більшою кількістю цифр, вам потрібно відповідно використовувати формат.
Примітка: Ця техніка буде працювати тільки для числового набору даних. Якщо у вас є ідентифікатори співробітників, такі як A1, A2, A3 тощо, то вони є текстовими і не змінюватимуться, якщо ви застосуєте власний формат, як показано вище.
Додайте провідні нулі за допомогою функції TEXT
Коли використовувати: Якщо ви хочете, щоб результат був текстовим.
Функція TEXT дозволяє змінити значення на потрібний формат.
Наприклад, якщо ви хочете, щоб 1 відображався як 001, ви можете використовувати для цього функцію ТЕКСТ.
Однак пам’ятайте, що функція TEXT змінить формат і зробить його TEXT. Це означає, що коли ви робите 1 як 001, Excel розглядає новий результат як текст з трьома символами (так само, як abc або xyz).
Ось як додати початкові нулі за допомогою функції TEXT:
- Якщо у вас є цифри у стовпці A (скажімо, з A2: A100), виберіть B2: B100 і введіть таку формулу:
= ТЕКСТ (A2, «00000 ″) - Натисніть клавіші Control + Enter, щоб застосувати формулу до всіх виділених клітинок.
Це відобразить усі числа у вигляді п’яти цифр, де початкові 0 автоматично додаються, якщо число менше 5 цифр.
Однією з переваг перетворення даних у текст є те, що тепер ви можете використовувати їх у формулах пошуку, таких як VLOOKUP або INDEX/MATCH, щоб отримати відомості про працівника за його ідентифікатором працівника.
Примітка: Ця техніка буде працювати тільки для числового набору даних. Якщо у вас є ідентифікатори співробітників, такі як A1, A2, A3 тощо, то це текст і вони не змінюються, коли ви застосовуєте власний формат, як показано вище.
Додайте провідні нулі за допомогою функцій REPT і LEN
Коли використовувати: Якщо у вас є числовий/буквено -цифровий набір даних, і ви хочете, щоб результат був текстовим.
Недоліком використання функції TEXT було те, що вона буде працювати тільки з числовими даними. Але якщо у вас є буквено -цифровий набір даних (скажімо, A1, A2, A3 тощо), то функція TEXT вийде з ладу.
У таких випадках поєднання функцій REPT та LEN робить свою справу.
Ось як це зробити:
- Якщо у вас є цифри у стовпці A (скажімо, з A2: A100), то виберіть B2: B100 і введіть таку формулу:
= REPT (0,5-LEN (A2)) & A2 - Натисніть клавіші Control + Enter, щоб застосувати формулу до всіх виділених клітинок.
Це зробить усі числа/рядки довжиною 5 символів з нулями, де це потрібно.
Ось як працює ця формула:
- LEN (A2) дає довжину рядка/чисел у комірці.
- = REPT (0,5-LEN (A2)) дасть число 0, яке слід додати. Тут я використав 5 у формулі, оскільки це була максимальна довжина рядка/числа в моєму наборі даних. Ви можете змінити це відповідно до ваших даних.
- = REPT (0,5-LEN (A2)) & A2 просто додасть кількість нулів до значення комірки. Наприклад, якщо значення в комірці 123, це поверне 00123.
Додайте провідні нулі за допомогою користувацької функції (VBA)
Якщо додавання початкових нулів в Excel - це те, що вам потрібно робити досить часто, використання користувацької функції є гарною ідеєю.
Ось код VBA, який створить просту функцію для додавання початкових нулів:
'Код від Sumit Bansal з http://trumpexcel.com Функція AddLeadingZeroes (посилання As Range, Length As Integer) Dim i As Integer Dim Результат As String Dim StrLen As Integer StrLen = Len (ref) For i = 1 To Length If i <= StrLen Тоді Результат = Результат & Середина (посилання, i, 1) Інший результат = "0" & Результат завершення Якщо далі i AddLeadingZeroes = Функція завершення результату
Просто додайте цей код у вікно коду модуля, і ви зможете використовувати його, як і будь -яку іншу функцію аркуша.
Або створіть надбудову для неї та зможете поділитися нею з колегами.
