Как написать 01 вместо 1 в Excel

Обновлено: 22.11.2024

В Excel есть несколько правил по умолчанию, которые могут вас раздражать. Одно из таких правил – нельзя добавлять ноль перед числом.

Причина этого в том, что ноль перед числом не имеет смысла и не имеет значения для самого числа.

Всякий раз, когда вы пытаетесь вставить ноль перед числом, Excel удаляет его, и вы получаете только число, начинающееся с фактического числа.

Теперь дело в том, что есть несколько уникальных ситуаций, когда нам нужно добавить ноль перед числом, и мы хотим, чтобы ноль остался там.

Например, номера телефонов, почтовые индексы, номера счетов и т. д.

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

Преобразование числа в текст для добавления нулей

Вот что: если вы измените формат ячейки с числа на текст, вы сможете без проблем добавить начальный ноль.

В этом случае Excel не будет рассматривать ваше число как фактическое число и не удалит из него нули.

  • Выберите ячейки, в которые вы хотите добавить ведущие нули.
  • Перейдите в раздел Главная ➜ Число ➜ Измените числовой формат на текстовый.

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

Использовать функцию конкатенации для добавления нуля

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

С функцией СЦЕПИТЬ вы объединяете нули с действительными числами. Вы можете использовать приведенную ниже формулу, чтобы добавить ноль перед числами в ячейке.

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

Добавить динамические ведущие нули с функцией REPT

ПОВТОР — одна из моих любимых функций Excel, которая может многократно повторять текст. Теперь позвольте мне показать вам, как это может помочь нам добавить префикс нуля в ячейке.

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

Если в ячейке 6 чисел, то остальные 2 цифры будут равны нулю, а если в ячейке 4 числа, то остальные 4 числа будут равны нулю.

Это динамическая формула для получения нуля перед числами.

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

Использование функции ТЕКСТ для добавления формата начальных нулей

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

В приведенном выше примере мы преобразовали число в текст, в котором вы всегда получите 7 цифр.

Если у вас есть 5 цифр, остальные 2 будут нулями в начале.

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

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

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

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

Я считаю, что это лучший метод для использования, и он имеет дополнительные преимущества по сравнению со всеми другими методами.

  1. Выберите ячейку, в которую вы хотите добавить нули в качестве префикса.
  2. Щелкните правой кнопкой мыши ➜ Параметр «Формат» или воспользуйтесь сочетанием клавиш Ctrl + 1, чтобы открыть «Диалоговое окно «Формат ячейки».
  3. В разделе «Категория» ➜ выберите «Пользовательский».
  4. В диалоговом окне пользовательского ввода введите «0000000».
  5. Нажмите "ОК".

В приведенном выше примере мы отформатировали ячейки с 7-значными числами, в которых вы всегда получите 7 цифр. Если у вас есть 5-значное число, остальные 2 будут нулями в начале.

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

Заключение

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

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

Но если это произойдет, теперь у вас есть 5 различных способов справиться с этим, и я надеюсь, что эти методы помогут вам, но теперь скажите мне одну вещь.

Есть ли у вас какой-либо другой способ добавления ведущих нулей перед числом?

Не забудьте поделиться со мной своим мнением в разделе комментариев, я буду рад услышать от вас. И, пожалуйста, не забудьте поделиться этим постом с друзьями, я уверен, что они это оценят.

Если вы используете Excel не только для вычисления чисел, но и для ведения таких записей, как почтовые индексы, коды безопасности или идентификаторы сотрудников, вам может потребоваться оставить в ячейках ведущие нули. Однако если вы попытаетесь ввести в ячейке почтовый индекс, например "00123", Excel немедленно урежет его до "123".

Дело в том, что Microsoft Excel обрабатывает почтовые индексы, номера телефонов и другие подобные записи как числа, применяет к ним общий или числовой формат и автоматически удаляет предшествующие нули. К счастью, Excel также предоставляет средства для сохранения начальных нулей в ячейках, и далее в этом руководстве вы найдете несколько способов сделать это.

Как сохранить ведущие нули в Excel при вводе

Для начала давайте посмотрим, как поставить 0 перед числом в Excel, например, ввести 01 в ячейку. Для этого просто измените формат ячейки на Текст:

  • Выберите ячейки, в которых вы хотите добавить к числам префикс 0.
  • Перейдите на вкладку Главная в группу Число и выберите Текст в поле Числовой формат.< /li>

Как только вы введете нули перед числом, Excel отобразит маленький зеленый треугольник в верхнем левом углу ячейки, указывая на то, что с содержимым ячейки что-то не так. Чтобы удалить этот индикатор ошибки, выберите ячейку (ячейки), щелкните знак предупреждения, а затем нажмите Игнорировать ошибку.

На следующем снимке экрана показан результат:

Еще один способ оставить начальные нули в Excel – добавить перед числом апостроф ('). Например, вместо ввода 01 введите '01. В этом случае вам не нужно менять формат ячейки.

Итог: Этот простой метод имеет существенное ограничение: результирующее значение представляет собой текстовую строку, а не число, и, следовательно, его нельзя использовать в вычислениях и числовых формулах. Если это не то, что вам нужно, измените только визуальное представление значения, применив пользовательский числовой формат, как показано в следующем примере.

Как отобразить начальные нули в Excel, используя собственный числовой формат

Чтобы отобразить ведущие нули, примените пользовательский числовой формат, выполнив следующие действия:

  1. Выберите ячейку (ячейки), в которой вы хотите отобразить ведущие нули, и нажмите Ctrl+1, чтобы открыть диалоговое окно Формат ячеек.
  2. В разделе Категория выберите Пользовательский.
  3. Введите код формата в поле Тип.

Например, чтобы добавить ведущие нули для создания 5-значного числа, используйте следующий код формата: 00000

Используя пользовательские форматы чисел Excel, вы можете добавлять ведущие нули для создания чисел фиксированной длины, как в приведенном выше примере, и чисел переменной длины. Все сводится к тому, какой заполнитель вы используете в коде формата:

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

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

Совет. В Excel есть несколько предопределенных специальных форматов для почтовых индексов, номеров телефонов и номеров социального страхования, как показано на снимке экрана ниже:

Итог: Этот метод лучше всего использовать в ситуациях, когда вы работаете с числовым набором данных, и результаты должны быть числами, а не текстом. Он изменяет только отображение числа, но не само число: в ячейках отображаются ведущие нули, фактическое значение отображается в строке формул. Когда вы ссылаетесь на такие ячейки в формулах, расчеты ароматизируются исходными значениями. Пользовательские форматы можно применять только к числовым данным (числам и датам), и результат также является числом или датой.

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

В то время как в пользовательском числовом формате ноль отображается перед числом без фактического изменения базового значения, функция ТЕКСТ в Excel дополняет числа нулями, "физически" вставляя ведущие нули в ячейки.

Чтобы добавить ведущие нули с формулой ТЕКСТ(значение, format_text), используйте те же коды формата, что и в настраиваемых числовых форматах. Однако результатом функции ТЕКСТ всегда является текстовая строка, даже если она очень похожа на число.

Например, чтобы вставить 0 перед значением в ячейке A2, используйте следующую формулу:

Чтобы создать строку фиксированной длины с нулевым префиксом, например, строку из 5 символов, используйте этот вариант:

Обратите внимание, что функция ТЕКСТ требует заключения кодов форматов в кавычки. А вот как результаты будут выглядеть в Excel:

Дополнительную информацию о текстовых формулах см. в статье Использование функции ТЕКСТ в Excel.

Итог: Функция ТЕКСТ Excel всегда возвращает текстовую строку, а не число, и поэтому вы не сможете использовать результаты в арифметических вычислениях и других формулах, если вам не нужно сравнивать вывод с другими текстовыми строками.

Как добавить ведущие нули в текстовые строки

В предыдущих примерах вы узнали, как добавить ноль перед числом в Excel. Но что, если вам нужно поставить нули перед текстовой строкой, например 0A102? В этом случае ни ТЕКСТ, ни пользовательский формат не будут работать, поскольку они работают только с числовыми значениями.

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

Формула 1. Добавление ведущих нулей с помощью функции ПРАВИЛЬНО

Самый простой способ поставить ведущие нули перед текстовыми строками в Excel — использовать функцию ПРАВИЛЬНО:

  • "0000" – это максимальное количество нулей, которое вы хотите добавить. Например, чтобы добавить 2 нуля, введите "00".
  • Ячейка – это ссылка на ячейку, содержащую исходное значение.
  • Длина_строки — количество символов, которое должна содержать результирующая строка.

Например, чтобы создать 6-символьную строку с нулевым префиксом на основе значения в ячейке A2, используйте следующую формулу:

Что делает формула, так это добавляет 6 нулей к значению в A2 ("000000"&A2), а затем извлекает правильные 6 символов. В результате он вставляет ровно столько нулей, сколько необходимо для достижения заданного общего предела строки:

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

В зависимости от вашей бизнес-логики вы можете указать разное количество нулей и общее количество символов, например:

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

Формула 2. Дополнение ведущими нулями с помощью функций ПОВТОР и ДЛСТР

Еще один способ вставить начальные нули перед текстовой строкой в ​​Excel — это сочетание функций ПОВТОР и ДЛСТР:

Например, чтобы добавить ведущие нули к значению в A2 для создания строки из 6 символов, эта формула выглядит следующим образом:

Как работает эта формула:

Зная, что функция ПОВТОР повторяет заданный символ заданное количество раз, а ДЛСТР возвращает общую длину строки, логику формулы легко понять:

  • LEN(A2) получает общее количество символов в ячейке A2.
  • REPT(0, 6-LEN(A2)) добавляет необходимое количество нулей. Чтобы вычислить, сколько нулей нужно добавить, вы вычитаете длину строки в A2 из максимального количества нулей.
  • Наконец, вы объединяете нули со значением A2 и получаете следующий результат:

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

Как добавить фиксированное количество предшествующих нулей

Чтобы поставить перед всеми значениями в столбце (числа или текстовые строки) определенное количество нулей, используйте функцию СЦЕПИТЬ или оператор амперсанда.

Например, чтобы поставить 0 перед числом в ячейке A2, используйте одну из следующих формул:

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

Таким же образом вы можете вставить 2 начальных нуля (00), 3 нуля (000) или любое количество нулей перед числами и текстовыми строками.

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

Как удалить ведущие нули в Excel

Метод, который вы используете для удаления лидирующих нулей в Excel, зависит от того, как эти нули были добавлены:

  • Если предшествующие нули были добавлены в пользовательском числовом формате (нули видны в ячейке, но не в строке формул), примените другой пользовательский формат или вернитесь к общему, как показано здесь.
  • Если в ячейки, отформатированные как текст, были введены нули или введены иным образом (в верхнем левом углу ячейки отображается маленький зеленый треугольник), преобразуйте текст в число.
  • Если ведущие нули были добавлены с помощью формулы (формула появляется в строке формул при выборе ячейки), используйте функцию ЗНАЧ, чтобы удалить их.

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

Удалите ведущие нули, изменив формат ячейки

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

Удалить ведущие нули, преобразовав текст в число

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

Удалить ведущие нули с помощью формулы

Если предшествующие нули добавляются с помощью формулы, используйте другую формулу, чтобы удалить их. Формула удаления нуля проста:

Где A2 – это ячейка, из которой вы хотите удалить предшествующие нули.

Этот метод также можно использовать для избавления от нулей, введенных непосредственно в ячейки (как в предыдущем примере) или импортированных в Excel из какого-либо внешнего источника. В целом, если вы имеете дело со строкой с нулевым префиксом, представляющей число, вы можете использовать функцию ЗНАЧ для преобразования текста в число и удаления ведущих нулей по пути.

На следующем снимке экрана показаны две формулы:

  • Формула текста в ячейке B2 добавляет нули к значению в ячейке A2 и
  • Формула значения в C2 удаляет начальные нули из значения в B2.

Как скрыть нули в Excel

Если вы не хотите отображать нулевые значения на листе Excel, у вас есть два следующих варианта:

Для этого выберите ячейки, в которых вы хотите скрыть нули, нажмите Ctrl+1, чтобы открыть диалоговое окно Формат ячеек, выберите Пользовательский в разделе Категория и введите приведенный выше код формата в поле Тип.

На снимке экрана ниже показано, что ячейка B2 содержит нулевое значение, но оно не отображается в ячейке:

Легкий способ добавления и удаления нулей в Excel

Наконец, хорошие новости для пользователей Ultimate Suite for Excel: выпущен новый инструмент, специально разработанный для работы с нулями! Приветствуем добавление/удаление начальных нулей.

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

Чтобы добавить ведущие нули, выполните следующие действия:

  1. Выберите целевые ячейки и запустите инструмент Добавить/удалить ведущие нули
  2. Укажите общее количество отображаемых символов.
  3. Нажмите Применить.

Для удаления ведущих нулей шаги очень похожи:

  1. Выберите ячейки со своими номерами и запустите надстройку.
  2. Укажите, сколько символов должно отображаться. Чтобы получить максимальное количество значащих цифр в выбранном диапазоне, нажмите Получить максимальную длину
  3. Нажмите Применить.

Надстройка может добавлять ведущие нули как к числам, так и к строкам:

  • Для чисел задается пользовательский числовой формат, т. е. изменяется только визуальное представление числа, а не базовое значение.
  • Буквенно-цифровые строки начинаются с ведущих нулей, т. е. нули физически вставляются в ячейки.

Вот как вы можете добавлять, удалять и скрывать нули в 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:

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

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

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

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

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

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

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

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

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

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

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

Одним из преимуществ преобразования данных в текст является то, что теперь вы можете использовать его в формулах поиска, таких как ВПР или ИНДЕКС/ПОИСКПОЗ, для получения сведений о сотруднике с использованием его/ее идентификатора сотрудника.

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

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

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

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

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

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

  1. Если у вас есть числа в столбце A (скажем, из A2:A100), выберите B2:B100 и введите следующую формулу:
    =REPT(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, который создаст простую функцию для добавления начальных нулей:

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

Вы когда-нибудь пытались ввести данные, например 000123, в Excel?

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

Это может сильно раздражать, если вы хотите, чтобы в ваших данных были начальные нули, и вы не знаете, как заставить Excel сохранить их.

К счастью, есть несколько способов дополнить числа нулями в начале.

В этом посте я покажу вам 9 способов добавить или оставить начальные нули в ваших числах.

Видеоруководство

Форматировать как текст

Есть возможность изменить формат диапазона на текстовый.

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

Измените формат с «Общий» на «Текст».

  1. Выберите диапазон ячеек, в которые вы хотите ввести ведущие нули.
  2. Перейдите на вкладку "Главная".
  3. В разделе «Числа» щелкните раскрывающийся список «Формат».
  4. Выберите «Текст» в параметрах формата.

Теперь, если вы попытаетесь ввести числа с ведущими нулями, они не исчезнут, поскольку вводятся как текстовые значения, а не числа.

Пользовательский формат

Вы можете добавить собственное форматирование для форматирования чисел с ведущими нулями.

Однако они будут иметь только начальные нули. Базовые данные не будут преобразованы в текст с добавленными нулями.

Добавьте собственный формат для отображения лидирующих нулей.

  1. Выберите диапазон ячеек, к которым вы хотите добавить ведущие нули, и откройте диалоговое окно «Формат ячеек».
    • Нажмите правой кнопкой мыши и выберите "Формат ячеек".
    • Используйте сочетание клавиш Ctrl + 1.
  2. Перейдите на вкладку "Число".
  3. Выберите «Пользовательский» в параметрах категории.
  4. Добавьте новый пользовательский формат в поле ввода "Тип". Если вы хотите, чтобы общее количество цифр, включая начальные нули, равнялось 6, добавьте 000000 в качестве пользовательского формата.
  5. Нажмите кнопку ОК.

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

При выборе ячейки с форматированием в строке формул по-прежнему будет отображаться исходное число. Они появляются на листе только с начальным нулевым форматом.

Это также означает, что если вы скопируете и вставите данные как значения, вы потеряете начальные нули.

Ведущий апостроф

Вы можете заставить Excel вводить число как текст, используя начальный апостроф.

Это означает, что вы сможете оставить нули впереди при вводе данных.

Этот метод позволяет быстро и легко вводить данные. Просто введите символ ' перед любым числом. Это сообщит Excel, что данные должны быть текстовыми, а не числовыми.

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

Функция ТЕКСТ

Функция ТЕКСТ позволит вам применить настраиваемое форматирование к любым числовым данным, уже имеющимся в вашей электронной таблице.

  • Значение. Это значение, которое вы хотите преобразовать в текст и применить к нему форматирование.
  • Формат. Это применяемое форматирование.

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

ВПРАВО

Еще один способ поставить нули перед формулой — использовать функцию ПРАВИЛЬНО. Вы можете соединить строку нулей с числом, а затем отрезать лишнее с помощью функции ПРАВИЛЬНО.

Функция RIGHT извлечет из текстового значения N самых правых символов.

  • Текст. Это текст, из которого вы хотите извлечь символы.
  • Число (Необязательно) – это количество символов, которое нужно извлечь из текста. Если этот аргумент не указан, будет извлечен только первый символ.

Приведенная выше формула объединяет несколько нулей в начало числа в ячейке B3, затем возвращает самые правые 6 символов, что приводит к некоторым ведущим нулям.

БАЗОВАЯ функция

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

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

Функции BASE позволяют преобразовать число в текстовое представление с заданным основанием.

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

  • Число. Это число, которое вы хотите преобразовать в текстовое значение в другом формате.
  • База – это база, в которую вы хотите преобразовать значение.
  • MinLength (Необязательно) — это минимальная длина символов преобразованного текстового значения.

Приведенная выше формула преобразует число в ячейке B3 в число с основанием 10 (это уже число с основанием 10, но это сохранит его как основание 10) и преобразует его в текстовое значение, содержащее не менее 6 символов. Если преобразованное значение содержит менее 6 символов, оно будет дополнено нулями, чтобы получить как минимум 6 символов.

Определенно крутое новое использование функции, которую я никогда не использую.

Power Query и функция Text.PadStart

Power query — это преобразование данных, поэтому неудивительно, что вы можете преобразовывать свои числа, добавляя ведущие нули в power query.

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

После импорта данных в power query вы можете добавить новый столбец с приведенной выше формулой, чтобы создать ведущие нули.

  1. Перейдите на вкладку "Добавить столбец".
  2. Выберите «Пользовательский столбец» на ленте.
  3. Создайте новое имя для пользовательского столбца и добавьте приведенную выше формулу в поле формулы пользовательского столбца.
  4. Нажмите кнопку ОК.

Это создаст новый столбец, в каждой строке которого будет не менее 6 символов, дополненных нулями, если это необходимо.

Сводная таблица и показатель DAX

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

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

Обратите внимание, что функция FORMAT DAX точно такая же, как функция TEXT в Excel, но меры DAX должны быть объединены в одно значение, поэтому вам нужно обернуть их внутри функции CONCATENATEX.

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

Вычисляемый столбец Power Pivot

Есть еще один способ добавить нули в сводную таблицу. Вы можете добавить их в вычисляемый столбец в Power Pivot.

Таким образом, вы можете использовать новый столбец в области "Фильтр", "Строки" или "Столбец" сводной таблицы.

В надстройке Power Pivot вы можете добавить новый столбец и использовать функцию ФОРМАТ, чтобы создать ведущие нули в столбце с формулой, как показано выше.

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

Теперь вы можете использовать этот новый столбец в области строк сводной таблицы, как и любой другой столбец данных!

Выводы

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

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

Каковы бы ни были ваши требования, для вас обязательно найдется хорошее решение.

Есть ли у вас любимый способ, который я пропустил?

Об авторе

Джон МакДугалл

Джон — Microsoft MVP, внештатный консультант и тренер, специализирующийся на Excel, Power BI, Power Automate, Power Apps и SharePoint. Другие интересные статьи Джона можно найти в его блоге или на канале YouTube.

Подписаться

Похожие сообщения

7 способов переименовать лист в Microsoft Excel

Важно поддерживать порядок в электронной таблице! Хорошо организованная электронная таблица.

8 способов объединения ячеек в Microsoft Excel

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

6 способов подсчета цветных ячеек в Microsoft Excel [иллюстрированное руководство]

Возможно, вы использовали цветовое кодирование в данных Excel или видели его в a.

Комментарии

4 комментария

Эй, босс, мне нужны начальные нули в моих данных, так как я делаю импорт в другой отстойный продукт Microsoft (powerapps), а нули должны быть там.

мой столбец данных имеет общий формат и содержит CS000000035678. (последние 5 цифр различаются) я заменяю все, чтобы удалить CS0000 из всех значений в столбце. конечно, ведущие нули удалены, и я получаю 35678. это данность.

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

Я форматирую столбец в текст и заменяю все, снова получается 35678.

Я редактирую ячейку в столбце и удаляю CS0000, а нули остались, и я получаю 00035678. (Похоже, у меня устанут пальцы после ручного обновления 4000 ячеек)

Огромное спасибо, отстойный продукт microsft. (мне за 50, и я видел это так много раз с продуктами M$. почему я не мог получить работу, ориентированную на яблоко! LOL) M$ делает много замечательных вещей, но затем они терпят неудачу в крошечных вещах, которые должны работать. В замене есть что-то, что даже в столбце с текстовым форматированием удаляет нули.

Я попробую использовать апостроф, но сомневаюсь, что мой процесс импорта примет его.

ответьте мне, если у вас есть подсказка/решение для меня. Спасибо, диджей

Вы можете импортировать в Power Apps как есть, а затем взять последние 8 символов с помощью функции RIGHT в Power Apps.

Спасибо за ответ. Ваш метод использования формулы BASE сработал. это даже позволило мне добавить апостроф перед нулями. Апостроф не отображается в ячейке, но отображается в строке формул при нажатии на ячейку.

Отличное решение для меня и других.

Существует так много методов. Вы также можете использовать функцию REPT & LEN.

Например, вам нужно не менее 14 символов. Ячейка, которую вы читаете, может иметь любую длину. Вы можете написать следующую формулу.

Читайте также: