Удаление нулевых значений в Microsoft Excel. Отображение и скрытие нулевых значений Как в экселе удалить нули
Вам когда-нибудь приходилось импортировать или вводить в Excel данные, содержащие начальные нули (например, 00123) или большие числа (например, 1234 5678 9087 6543)? Это могут быть номера социального страхования, телефонные номера, номера кредитных карт, коды продуктов, номера счетов или почтовые индексы. Excel автоматически удаляет начальные нули и преобразует большие числа в экспоненциальное представление (например, 1,23E+15), чтобы их можно было использовать в формулах и математических операциях. В этой статье объясняется, как сохранить данные в исходном формате, который Excel обрабатывает как текст.
Преобразование чисел в текст при импорте текстовых данных
Для форматирования отдельных столбцов в виде текста при импорте данных в Excel используйте функцию Получить и преобразовать (Power Query)
В Excel 2010 и Excel 2013 импортировать текстовые файлы и преобразовывать числа в текст можно двумя способами. Рекомендуется использовать Power Query (для этого нужно скачать надстройку Power Query). Если надстройку Power Query скачать не удается, можно воспользоваться мастером импорта текста . В этом случае импортируется текстовый файл, однако данные проходят те же этапы преобразования, что и при импорте из других источников, таких как XML, Интернет, JSON и т. д.
Применение пользовательского формата для сохранения начальных нулей
Если эту задачу необходимо решить в пределах книги, поскольку она не используется другими программами в качестве источника данных, для сохранения начальных нулей можно использовать пользовательский или специальный формат. Этот способ подходит для цифровых кодов, содержащих менее 16 цифр. Цифровые коды также можно отформатировать с использованием дефисов или других знаков препинания. Например, чтобы сделать телефонные номера более удобными для чтения, можно добавить дефисы между международным кодом, кодом области, префиксом и остальными цифрами.
Инструкции
Применение функции ТЕКСТ для форматирования
Для преобразования данных в нужный формат можно использовать расположенный рядом с ними пустой столбец и функцию ТЕКСТ .
Округление номеров кредитных карт
Максимальная точность чисел в Excel составляет 15 значащих цифр. Это означает, что в любом числе, состоящем из 16 и более цифр, например в номере кредитной карты, все цифры, следующие после 15-й, округляются до нуля. Если цифровые коды состоят из 16 и более цифр, необходимо использовать текстовый формат. Для этого выполните одно из указанных ниже действий.
Форматирование столбца в виде текста
Выделите диапазон данных и нажмите клавиши CTRL+1 , чтобы открыть диалоговое окно Формат > Ячейки . На вкладке Число выберите формат Текстовый .
Примечание: Этот способ не позволяет изменить числа, которые были введены ранее. Он действует только на числа, введенные после применения формата.
Использование апострофа
Перед числом можно ввести апостроф (" ), и Excel будет обрабатывать его как текст.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community , попросить помощи в сообществе Answers community , а также предложить новую функцию или улучшение на веб-сайте
Вы можете сами настроить отображение нулевых значений в ячейке или использовать в таблице набор стандартов форматирования, которые требуют скрывать нулевые значения. Отображать и скрывать нулевые значения можно разными способами.
Скрытие и отображение всех нулевых значений на листе
-
Показывать нули в ячейках, которые содержат нулевые значения .
Выберите Файл > Параметры > Дополнительно .
В группе
Скрытие нулевых значений в выделенных ячейках
Эти действия скрывают нулевые значения в выделенных ячейках с помощью числового формата. Скрытые значения отображаются только в строке формул и не выводятся на печать. Если значение в одной из этих ячеек изменится на ненулевое, оно будет отображаться в ячейке, а формат значения будет похож на общий числовой формат.
Отображение скрытых значений .
Выделите ячейки со скрытыми нулевыми значениями.
Вы можете нажать клавиши CTRL+1 или на вкладке Главная щелкнуть Формат > Формат ячеек .
Для применения числового формата, определенного по умолчанию, выберите Число > Общий и нажмите кнопку ОК .
Скрытие нулевых значений, возвращенных формулой
На вкладке Главная Условное форматирование и выберите "Правила выделения ячеек" > "Равно".
В левом поле введите 0 .
В правом поле выберите Пользовательский формат .
В поле Формат ячейки откройте вкладку Шрифт .
В списке Цвет выберите белый цвет и нажмите кнопку ОК .
Отображение нулей в виде пробелов или тире
Для решения этой задачи воспользуйтесь функцией ЕСЛИ .
Если ячейка содержит нулевые значения, для возврата пустой ячейки используйте формулу, например такую:
ЕСЛИ(A2-A3=0;"";A2-A3)
Вот как читать формулу. Если результат вычисления (A2-A3) равен "0", ничего не отображается, в том числе и "0" (это указывается двойными кавычками ""). В противном случае отображается результат вычисления A2-A3. Если вам нужно не оставлять ячейки пустыми, но отображать не "0", а что-то другое, между двойными кавычками вставьте дефис "-" или другой символ.
Изменение отображения ошибок В поле Формат установите флажок Для ошибок отображать
Изменение отображения пустых ячеек Установите флажок Для пустых ячеек отображать
Выберите отчет сводной таблицы.
На вкладке Анализ в группе Сводная таблица Параметры и выберите пункт Параметры .
Перейдите на вкладку Разметка и формат
Потребность отображать нулевые значения (0) на листах возникает не всегда. Требуют ли стандарты форматирования или собственные предпочтения отображать или скрывать нулевые значения, имеется несколько способов реализации всех этих требований.
Чтобы отображать в ячейках нулевые значения (0), установите флажок Показывать нули в ячейках, которые содержат нулевые значения .
Чтобы отображать нулевые значения в виде пустых ячеек, снимите флажок Показывать нули в ячейках, которые содержат нулевые значения .
Выберите Файл > Параметры > Дополнительно .
В группе Показать параметры для следующего листа выберите лист, после чего выполните одно из указанных ниже действий.
Вы можете нажать клавиши CTRL+1 или на вкладке Главная щелкнуть Формат > Формат ячеек .
В поле Тип введите 0;-0;;@
Примечания:
Скрытые значения отображаются только в строке формул или в ячейке, если вы редактируете ее содержимое. Эти значения не печатаются.
Чтобы снова отобразить скрытые значения, выделите ячейки, а затем нажмите клавиши CTRL+1 или на вкладке Главная в группе Ячейки наведите указатель мыши на элемент Формат и выберите Формат ячеек . Чтобы применить числовой формат по умолчанию, в списке Категория выберите Общий . Чтобы снова отобразить дату и время, выберите подходящий формат даты и времени на вкладке Число .
Выделите ячейку, содержащую нулевое (0) значение.
На вкладке Главная в группе Стили щелкните стрелку рядом с элементом Условное форматирование , наведите указатель на элемент Правила выделения ячеек и выберите вариант Равно .
В левом поле введите 0 .
В правом поле выберите Пользовательский формат .
В диалоговом окне Формат ячеек откройте вкладку Шрифт .
В поле Цвет выберите белый цвет.
Для выполнения этой задачи используйте функцию ЕСЛИ .
Пример
Функция ЕСЛИ .
Скрытие нулевых значений в отчете сводной таблицы
На вкладке Параметры в группе Параметры сводной таблицы щелкните стрелку рядом с командой Параметры и выберите пункт Параметры .
Перейдите на вкладку Разметка и формат , а затем выполните следующие действия.
В поле Формат установите флажок Для ошибок отображать . Введите в поле значение, которое нужно выводить вместо ошибок. Для отображения ошибок в виде пустых ячеек удалите из поля весь текст.
Установите флажок Для пустых ячеек отображать . Введите в поле значение, которое нужно выводить в пустых ячейках. Чтобы они оставались пустыми, удалите из поля весь текст. Чтобы отображались нулевые значения, снимите этот флажок.
Потребность отображать нулевые значения (0) на листах возникает не всегда. Требуют ли стандарты форматирования или собственные предпочтения отображать или скрывать нулевые значения, имеется несколько способов реализации всех этих требований.
Отображение и скрытие всех нулевых значений на листе
Скрытие нулевых значений в выделенных ячейках с помощью числового формата
Эти действия позволяют скрыть нулевые значения в выделенных ячейках. Если значение в одной из ячеек станет ненулевым, его формат будет аналогичен общему числовому формату.
Выделите ячейки, содержащие нулевые значения (0), которые требуется скрыть.
Вы можете нажать клавиши CTRL+1 или на вкладке Главная в группе Ячейки щелкнуть Формат > Формат ячеек .
В поле Тип введите 0;-0;;@
Примечания:
Скрытие нулевых значений, возвращенных формулой, с помощью условного форматирования
Выделите ячейку, содержащую нулевое (0) значение.
На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выберите "Правила выделения ячеек" > "Равно".
В левом поле введите 0 .
В правом поле выберите Пользовательский формат .
В диалоговом окне Формат ячеек откройте вкладку Шрифт .
В поле Цвет выберите белый цвет.
Использование формулы для отображения нулей в виде пробелов или тире
Для выполнения этой задачи используйте функцию ЕСЛИ.
Пример
Чтобы этот пример проще было понять, скопируйте его на пустой лист.
Копирование примера
Выделите пример, приведенный в этой статье.
Важно: Не выделяйте заголовки строк или столбцов.
Выделение примера в справке
Нажмите клавиши CTRL+C.
В Excel создайте пустую книгу или лист.
Выделите на листе ячейку A1 и нажмите клавиши CTRL+V.
Важно: Чтобы пример правильно работал, его нужно вставить в ячейку A1.
Чтобы переключиться между просмотром результатов и просмотром формул, возвращающих эти результаты, нажмите клавиши CTRL+` (знак ударения) или на вкладке Формулы в группе "Зависимости формул" нажмите кнопку Показать формулы .
Скопировав пример на пустой лист, вы можете настроить его так, как вам нужно.
Дополнительные сведения об использовании этой функции см. в статье Функция ЕСЛИ .
Скрытие нулевых значений в отчете сводной таблицы
Щелкните отчет сводной таблицы.
На вкладке Параметры в группе Параметры сводной таблицы щелкните стрелку рядом с командой Параметры и выберите пункт Параметры .
Перейдите на вкладку Разметка и формат , а затем выполните следующие действия.
Изменение способа отображения ошибок. В поле Формат установите флажок Для ошибок отображать . Введите в поле значение, которое нужно выводить вместо ошибок. Для отображения ошибок в виде пустых ячеек удалите из поля весь текст.
Изменение способа отображения пустых ячеек. Установите флажок Для пустых ячеек отображать . Введите в поле значение, которое нужно выводить в пустых ячейках. Чтобы они оставались пустыми, удалите из поля весь текст. Чтобы отображались нулевые значения, снимите этот флажок.
Значения, отображаемые в ячейках электронных таблиц приложения Microsoft Office Excel, часто формируются в результате выполнения записанных в них формул. Результатом вычислений может быть и нулевое значение, показывать которое в ячейке нежелательно. Нули не улучшают общего восприятия результатов, особенно если в других ячейках колонки формулы выводят текстовые, а не числовые значения. Решить эту задачу можно несколькими способами.
Вам понадобится
- Табличный редактор Microsoft Office Excel 2007 или 2010.
Инструкция
Как поставить ноль в экселе?При составлении отчетов в Excel бывает необходимость в пустых ячейках проставить нули или наоборот требуют убрать нулевые значения. Как говориться, «хозяин – барин» или «любой каприз за ваши деньги». Но оказывается, у многих это вызывает большое затруднение. Все зависит от настройки программы. Она либо проставляет нули, где надо и не надо, либо вовсе их не ставит. Поэтому сегодня и рассмотрим эти два вопроса.
Убираем или ставим нули в Microsoft Excel 2003
Для этого заходим в меню Сервис, и выбираем в самом низу Параметры .
Откроется одноименное окно Параметры .
На вкладке Вид в панели Параметры окна , в среднем столбце устанавливаем галочку на параметр Нулевые значения . Теперь все ваши нулевые значения в ячейках будут отображаться.
Таким же образом можно убрать нули в экселе . Для этого достаточно снять галочку у этого параметра, и нули не будут отображаться.
Как поставить или убрать нули в Microsoft Excel 2007
Щелкните мышкой по разноцветному значку программы в верхнем левом углу.
В открывшемся меню в самом низу щелкните мышкой по кнопке Параметры Excel .
В открывшемся окне Параметры Excel перейдите на вкладку Дополнительно , и в большом окне справа найдите , и поставьте галочку напротив записи – .
в Microsoft Excel 2010
Все так же, как в Excel 2007, только сначала открываем меню Файл , переходим на Параметры , а потом – Дополнительно – Показать параметры для следующего листа – уберите, или поставьте галочку на пункте – Показывать нули в ячейках, которые содержат нулевые значения .
При использовании формул в Экселе, в случае, если ячейки, на которые ссылается оператор, пусты, в области расчетов по умолчанию будут стоять нули. Эстетически это не очень красиво выглядит, особенно, если в таблице очень много подобных диапазонов с нулевыми значениями. Да и пользователю труднее ориентироваться в данных по сравнению с ситуацией, если бы подобные области были бы вообще пустыми. Давайте выясним, какими способами можно удалить отображение нулевых данных в Excel.
Эксель предоставляет возможность произвести удаление нулей в ячейках несколькими способами. Это можно сделать, как использовав специальные функции, так и применив форматирование. Также существует возможность отключение отображение подобных данных в целом по листу.
Способ 1: настройки Excel
Глобально данный вопрос можно решить, изменив настройки Эксель для текущего листа. Это позволяет сделать абсолютно все ячейки, содержащие нули, пустыми.
После этих действий все ячейки текущего листа, которые содержат нулевые значения, будут отображаться, как пустые.
Способ 2: применение форматирования
Скрыть значения пустых ячеек можно при помощи изменения их формата.
Теперь во всех областях, которые содержат нулевые значения, будет пусто.
Способ 3: условное форматирование
Для удаления лишних нулей можно также применить такой мощный инструмент, как условное форматирование.
Теперь при условии, если значение в ячейке будет нулевым, то оно будет невидимым для пользователя, так как цвет его шрифта сольется с цветом фона.
Способ 4: применение функции ЕСЛИ
Ещё один вариант скрытия нулей предусматривает использование оператора ЕСЛИ .
Кстати, если в окне аргументов в поле «Значение если истина» установить тире, то при выводе результата в ячейках с нулевым значением будет не пробел, а прочерк.
Способ 5: применение функции ЕЧИСЛО
Следующий способ является своеобразной комбинацией функций ЕСЛИ и ЕЧИСЛО .
Существует целый ряд способов удалить цифру «0» в ячейке, если она имеет нулевое значение. Проще всего, отключить отображения нулей в настройках Excel. Но тогда следует учесть, что они исчезнут по всему листу. Если же нужно применить отключение исключительно к какой-то конкретной области, то в этом случае на помощь придет форматирование диапазонов, условное форматирование и применение функций. Какой из данных способов выбрать зависит уже от конкретной ситуации, а также от личных умений и предпочтений пользователя.
