Как добавить ведущие нули в Excel - все, что вам нужно знать

Бывают ситуации, когда вам нужно добавить начальные нули в набор данных в Excel. Это может быть так, если вы ведете записи в Excel, например идентификаторы сотрудников или идентификаторы транзакций.

Например, вы можете захотеть получить единообразный вид в своем наборе данных, как показано ниже:

В этом руководстве вы научитесь различным способам добавления ведущих нулей в Excel:

• Преобразование формата в текст
• Использование произвольного форматирования чисел
• Использование текстовой функции
• Использование функций REPT / LEN
• Использование VBA

У каждого из этих методов есть свои достоинства и недостатки (они описаны в каждом разделе).

Давайте посмотрим, как работает каждый из них.

Добавление ведущих нулей путем преобразования формата в текст

Когда использовать: Когда у вас есть небольшой набор числовых данных, и вы планируете выполнять это редактирование вручную.

Предположим, у вас есть идентификаторы сотрудников отдела маркетинга, как показано ниже, и вы хотите, чтобы эти идентификаторы выглядели согласованно, добавив начальные нули.

Итак, вы пытаетесь изменить идентификатор, вводя ведущие нули (00001 вместо 1).

Но, к вашему удивлению, Excel снова преобразует его в 1.

Это происходит, поскольку Excel понимает, что 00001 и 1 - это одни и те же числа и должны соответствовать одним и тем же правилам отображения.

Каким бы разочаровывающим это ни было для вас, у Excel есть свои причины.

Итак, чтобы выполнять работу, не нарушая правил Excel, вам нужно воспользоваться тем фактом, что это правило не применяется к форматированию текста.

Итак, вот что вам нужно сделать:

1. Выделите ячейки, в которые вы хотите вручную добавить ведущие нули.
2. Перейдите в раздел «Домой» → «Группа номеров» и выберите «Текст» в раскрывающемся списке.

Вот и все!

Теперь, когда вы вводите начальные нули вручную, Excel с готовностью согласится.

Внимание: при преобразовании формата в текстовый некоторые функции Excel не будут работать должным образом. Например, функция СУММ / СЧЁТ игнорирует ячейку, поскольку она находится в текстовом формате.

Добавление ведущих нулей с помощью пользовательского форматирования чисел

Когда использовать: Когда у вас есть числовой набор данных, и вы хотите, чтобы результат был числовым (а не текстовым).

Когда вы отображаете число в определенном формате, это не меняет базовое значение числа. Например, я могу отобразить число 1000 как 1000 или 1000, или 1000,00, или 001000, или 26-09-1902 (даже даты - это числа в бэкэнде в Excel).

При всех различных способах отображения числа значение числа никогда не меняется. Меняется только способ отображения.

Чтобы добавить начальные нули, мы можем отформатировать его так, чтобы оно отображалось таким образом, в то время как базовое значение останется неизменным.

Вот шаги, чтобы использовать этот метод для добавления начальных нулей в Excel:

1. Выделите ячейки, в которые вы хотите добавить ведущие нули.
2. Перейдите на главную → Группа номеров и щелкните средство запуска диалогового окна (небольшая наклонная стрелка в правом нижнем углу). Откроется диалоговое окно «Формат ячеек». Кроме того, вы также можете использовать сочетание клавиш: Control + 1.
3. В диалоговом окне «Формат ячеек» на вкладке «Число» выберите «Пользовательский» в списке «Категория».
4. В поле Тип введите 00000.
5. Щелкните ОК.

При этом все числа всегда будут отображаться в виде пяти цифр, при этом ведущие нули автоматически добавляются, если число меньше 5 цифр. Таким образом, 10 станет 00010, а 100 станет 00100.

В этом случае мы использовали шесть нулей, но если в ваших данных есть числа с большим количеством цифр, вам необходимо использовать соответствующий формат.

Примечание. Этот метод работает только для числового набора данных. Если у вас есть идентификаторы сотрудников, такие как A1, A2, A3 и т. Д., То это текст, который не изменится при применении настраиваемого формата, как показано выше.

Добавить ведущие нули с помощью функции ТЕКСТ

Когда использовать: Когда вы хотите, чтобы результат был текстовым.

Функция ТЕКСТ позволяет изменить значение на желаемый формат.

Например, если вы хотите, чтобы 1 отображалось как 001, вы можете использовать для этого функцию ТЕКСТ.

Однако помните, что функция ТЕКСТ изменит формат и сделает его ТЕКСТОВЫМ. Это означает, что когда вы устанавливаете 1 как 001, Excel обрабатывает новый результат как текст с тремя символами (точно так же, как abc или xyz).

Вот как добавить начальные нули с помощью функции ТЕКСТ:

1. Если у вас есть числа в столбце A (скажем, из A2: A100), выберите B2: B100 и введите следующую формулу:
   = ТЕКСТ (A2; ”00000 ″)
2. Нажмите Control + Enter, чтобы применить формулу ко всем выбранным ячейкам.

Это отобразит все числа в виде пяти цифр, при этом ведущие нули автоматически добавляются, если число меньше 5 цифр.

Одним из преимуществ преобразования данных в текст является то, что теперь вы можете использовать его в формулах поиска, таких как VLOOKUP или INDEX / MATCH, для получения сведений о сотруднике, используя его / ее идентификатор сотрудника.

Примечание. Этот метод работает только для числового набора данных. Если у вас есть идентификаторы сотрудников, такие как A1, A2, A3 и т. Д., То это текст, который не изменится при применении настраиваемого формата, как показано выше..

Добавление ведущих нулей с помощью функций REPT и LEN

Когда использовать: Если у вас есть набор данных, который является числовым / буквенно-цифровым, и вы хотите, чтобы результат был текстовым.

Недостатком использования функции ТЕКСТ было то, что она работала только с числовыми данными. Но если у вас есть буквенно-цифровой набор данных (скажем, A1, A2, A3 и т. Д.), Функция ТЕКСТ не сработает.

В таких случаях помогает комбинация функций REPT и LEN.

Вот как это сделать:

1. Если у вас есть числа в столбце A (скажем, из A2: A100), выберите B2: B100 и введите следующую формулу:
   = ПОВТОР (0,5-LEN (A2)) & A2
2. Нажмите 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 (ссылка как диапазон, длина как целое число) Dim i как целое число Dim Результат как строка Dim StrLen как целое число StrLen = Len (ref) для i = 1 По длине, если i <= StrLen Then Result = Result & Mid (ref, i, 1) Else Result = "0" & ​​Result End If Next i AddLeadingZeroes = Result End Function

Просто добавьте этот код в окно кода модуля, и вы сможете использовать его, как любую другую функцию рабочего листа.

Или создайте для него надстройку и поделитесь ею со своими коллегами.