Почему Excel меняет последние цифры на нули
Обновлено: 21.11.2024
Важно помнить, что при подготовке данных для импорта они должны точно совпадать с теми, что есть в Acctivate. Это включает в себя любые начальные нули для идентификатора продукта или UPC
Activate QuickBooks Inventory Software позволяет пользователям импортировать данные в систему. Ниже подробно описано, как подготовить эти данные в Excel для бесшовного импорта.
Если вы когда-либо пытались вводить числа в электронную таблицу Excel, вы, скорее всего, обнаружили, что Excel переформатирует ваше число во что-то другое, удаляя ведущие нули, заменяя дробь на дату или изменяя длинную строку чисел или десятичных дробей в экспоненциальное представление. Это может раздражать и мешать корректному импорту ваших записей. Чтобы запретить Microsoft Excel изменять форматирование ваших данных, просто выберите один из приведенных ниже способов.
Метод апострофа
Помещая апостроф в ячейку перед числом, вы указываете Microsoft Excel игнорировать форматирование ячейки и отображать число так, как оно было введено в ячейку. Этот метод означает, что даже если кто-то вернет форматирование ячейки к общему и попытается отредактировать ячейку, она продолжит выглядеть так же, а не будет автоматически отформатирована Excel.
- Добавьте апостроф перед числом в ячейке
- (Пример: '001234 будет отображаться как 001234 в Excel)
Форматировать ячейку как текст
Второй по распространенности метод простого форматирования всего листа или группы ячеек в виде текста перед вводом данных. Форматирование ячеек для отображения в виде текста заставит Excel отображать данные точно так, как они были введены. (Не забудьте отформатировать ячейки перед вводом данных)
- Выделить весь лист или группу ячеек.
- Далее вы можете либо щелкнуть правой кнопкой мыши в выбранных ячейках, либо выбрать формат ячейки в раскрывающемся списке на вкладке "Главная".
- Выберите «Текст» в качестве метода форматирования ячеек.
- Введите свои данные, и они будут отображаться точно так, как они были введены.
Форматировать ячейку как пользовательский номер
Еще один вариант — отформатировать ячейку с помощью «Пользовательского» формата, задав определенное количество символов. Чтобы это было параметром, длина данных должна быть согласованной. Например, если все ваши идентификаторы имеют длину 5 символов и вы вводите 1234, Excel преобразует число в строку из 5 символов, что сделает его 01234.
- Выделите весь столбец, в котором удалены начальные нули.
- Щелкните правой кнопкой мыши в столбце и выберите формат ячейки.
- Перейдите на вкладку "Число" и выберите "Пользовательский" в разделе "Категория".
- В поле "Тип" введите 0 (ноль) пять раз
- Нажмите "ОК".
- Теперь введите свой номер в любое поле в столбце, Excel гарантирует, что каждое число состоит не менее чем из 5 символов, добавляя 0 в начале числа.
Как предотвратить изменение последнего числа на ноль в Excel?
В Excel, как правило, количество вводимых чисел ограничено 15 символами, если числовая строка длиннее 15 символов, и число будет отображаться как 0 с 16-го символа, как показано ниже. Эта проблема должна вызвать проблемы у многих финансовых сотрудников, потому что номер кредитной карты, который им нужно ввести в листы, обычно длиннее 15 цифр. Здесь я расскажу о хитростях, которые помогут вам предотвратить изменение числа на ноль в Excel.
Чтобы предотвратить изменение числа на ноль, вы можете добавить одинарную кавычку перед вводом чисел в ячейку.
Выберите пустую ячейку и введите в нее ', а затем введите числа, и вы увидите, что все числа точно отображаются в ячейке. Смотрите скриншот:\
Дополнительно к добавлению одиночных кавычек по одной, чтобы предотвратить изменение числа на ноль, вы также можете отформатировать ячейки как текстовое форматирование перед вводом чисел.
<р>1. Выберите ячейки, в которые вы хотите ввести числа длиннее 15 цифр, а затем нажмите Ctrl + 1, чтобы открыть диалоговое окно «Формат ячеек».<р>2. В появившемся диалоговом окне «Формат ячеек» на вкладке «Число» выберите «Текст» в списке «Категория». Смотрите скриншот:
<р>3. Затем нажмите «ОК», и при вводе чисел в выбранные ячейки число больше не будет меняться на ноль.Поскольку Excel хранит только 15 значащих цифр в числе, Excel заменяет все оставшиеся цифры нулями. Поскольку форматирование ячейки — числа, Excel интерпретирует числа как предназначенные для вычисления. Итак, мы можем переформатировать их как текст или что-то еще? Ну нет, один раз ввели, нельзя просто переформатировать. Вместо этого вам нужно будет отформатировать новые ячейки как текст, а затем снова ввести числа.
Решение, позволяющее решить, почему Excel меняет последнюю цифру на ноль.
Хммм, а есть ли что-то еще, что мы можем сделать, чтобы избежать повторного ввода всех этих чисел?… есть несколько вещей.
Решение 1. Начальный апостроф.
Первое решение – вставить ведущее число перед номером. Так, например, «2560089996999987» будет отображаться правильно. Вы можете видеть на снимке экрана ниже без апострофа Excel меняет цифру на ноль.
Решение 2. Использование мастера импорта текста.
Теперь переходим ко второму решению. Если вы импортируете свои данные из файла .rtf или .csv, когда появится мастер импорта текста, прокрутите до своего поля, содержащего оскорбительный номер «длиннее 15 цифр». Нажмите на него, затем в верхней правой части окна измените поле типа столбца с «Общие» на «Текст».
Примечание. Мастер импорта текста — это устаревшая функция. Если вы используете более раннюю версию Execl, все будет в порядке. Если у вас более новая версия Excel, вы увидите инструменты «Получить и преобразовать». Поэтому, поскольку в Excel 365 и 2016 «Мастер импорта текста» был удален. Но если вы хотите использовать устаревшую версию мастера импорта текста, вы можете вернуть его.
Восстановить мастер импорта текста.
Следуйте приведенным ниже инструкциям, чтобы восстановить исходный текст мастера вставки.
- ФАЙЛ | Опции | Данные
- Выберите «Показать мастера импорта устаревших данных».
- Нажмите "ОК".
Теперь вы можете продолжить и использовать функцию импорта устаревших данных, чтобы импортировать ваши данные так, как вам удобно.
Далее выберите файл для импорта. После выбора нажмите «Импорт».
Я всегда проверяю импортируемые данные на предмет возникновения этой проблемы. Легче обнаружить проблему на этапе импорта, чем получать начальный апостроф или перепечатывать текст. Кто этого хочет?
Вы когда-нибудь пытались ввести данные, например 000123, в Excel?
Вы, вероятно, быстро заметите, что Excel автоматически удалит ведущие нули из любых чисел.
Это может сильно раздражать, если вы хотите, чтобы в ваших данных были начальные нули, и вы не знаете, как заставить Excel сохранить их.
К счастью, есть несколько способов дополнить числа нулями в начале.
В этом посте я покажу вам 9 способов добавить или оставить начальные нули в ваших числах.
Видеоруководство
Форматировать как текст
Есть возможность изменить формат диапазона на текстовый.
При этом все данные, которые вы вводите, будут рассматриваться как текстовые значения, даже если они являются числами. Это приведет к тому, что Excel сохранит начальные нули в ваших числах.
Измените формат с «Общий» на «Текст».
- Выберите диапазон ячеек, в которые вы хотите ввести ведущие нули.
- Перейдите на вкладку "Главная".
- В разделе «Числа» щелкните раскрывающийся список «Формат».
- Выберите «Текст» в параметрах формата.
Теперь, если вы попытаетесь ввести числа с ведущими нулями, они не исчезнут, поскольку вводятся как текстовые значения, а не числа.
Пользовательский формат
Вы можете добавить собственное форматирование для форматирования чисел с ведущими нулями.
Однако они будут иметь только начальные нули. Базовые данные не будут преобразованы в текст с добавленными нулями.
Добавьте собственный формат для отображения лидирующих нулей.
- Выберите диапазон ячеек, к которым вы хотите добавить ведущие нули, и откройте диалоговое окно «Формат ячеек».
- Нажмите правой кнопкой мыши и выберите "Формат ячеек".
- Используйте сочетание клавиш Ctrl + 1.
- Перейдите на вкладку "Число".
- Выберите «Пользовательский» в параметрах категории.
- Добавьте новый пользовательский формат в поле ввода "Тип". Если вы хотите, чтобы общее количество цифр, включая начальные нули, равнялось 6, добавьте 000000 в качестве пользовательского формата.
- Нажмите кнопку ОК.
После применения пользовательского формата вы заметите, что данные на самом деле не изменились.
При выборе ячейки с форматированием в строке формул по-прежнему будет отображаться исходное число. Они появляются на листе только с начальным нулевым форматом.
Это также означает, что если вы скопируете и вставите данные как значения, вы потеряете начальные нули.
Ведущий апостроф
Вы можете заставить 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 вы можете добавить новый столбец с приведенной выше формулой, чтобы создать ведущие нули.
- Перейдите на вкладку "Добавить столбец".
- Выберите «Пользовательский столбец» на ленте.
- Создайте новое имя для пользовательского столбца и добавьте приведенную выше формулу в поле формулы пользовательского столбца.
- Нажмите кнопку ОК.
Это создаст новый столбец, в каждой строке которого будет не менее 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 символов. Ячейка, которую вы читаете, может иметь любую длину. Вы можете написать следующую формулу.
Читайте также: