Как заменить ноль на прочерк в excel

Как заменить ноль на прочерк в excel

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

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

Скрытие и отображение всех нулевых значений на листе

Выберите Файл > Параметры > Дополнительно.

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

Чтобы отображать в ячейках нулевые значения (0), установите флажок Показывать нули в ячейках, которые содержат нулевые значения.

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

Скрытие нулевых значений в выделенных ячейках

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

Выделите ячейки, содержащие нулевые значения (0), которые требуется скрыть.

Вы можете нажать клавиши CTRL+1 или на вкладке Главная щелкнуть Формат > Формат ячеек.

Щелкните Число > Все форматы.

В поле Тип введите выражение 0;-0;;@ и нажмите кнопку ОК.

Отображение скрытых значений.

Выделите ячейки со скрытыми нулевыми значениями.

Вы можете нажать клавиши CTRL+1 или на вкладке Главная щелкнуть Формат > Формат ячеек.

Для применения числового формата, определенного по умолчанию, выберите Число > Общий и нажмите кнопку ОК.

Скрытие нулевых значений, возвращенных формулой

Выделите ячейку, содержащую нулевое (0) значение.

На вкладке Главная щелкните стрелку рядом с кнопкой Условное форматирование и выберите «Правила выделения ячеек» > «Равно».

В левом поле введите 0.

В правом поле выберите Пользовательский формат.

В поле Формат ячейки откройте вкладку Шрифт.

В списке Цвет выберите белый цвет и нажмите кнопку ОК.

Отображение нулей в виде пробелов или тире

Для решения этой задачи воспользуйтесь функцией ЕСЛИ.

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

Вот как читать формулу. Если результат вычисления (A2-A3) равен «0», ничего не отображается, в том числе и «0» (это указывается двойными кавычками «»). В противном случае отображается результат вычисления A2-A3. Если вам нужно не оставлять ячейки пустыми, но отображать не «0», а что-то другое, между двойными кавычками вставьте дефис «-» или другой символ.

Скрытие нулевых значений в отчете сводной таблицы

Выберите отчет сводной таблицы.

На вкладке Анализ в группе Сводная таблица щелкните стрелку рядом с командой Параметры и выберите пункт Параметры.

Перейдите на вкладку Разметка и формат, а затем выполните следующие действия.

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

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

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

Отображение и скрытие всех нулевых значений на листе

Выберите Файл > Параметры > Дополнительно.

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

Чтобы отображать в ячейках нулевые значения (0), установите флажок Показывать нули в ячейках, которые содержат нулевые значения.

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

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

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

Выделите ячейки, содержащие нулевые значения (0), которые требуется скрыть.

Вы можете нажать клавиши CTRL+1 или на вкладке Главная щелкнуть Формат > Формат ячеек.

В списке Категория выберите элемент Пользовательский.

В поле Тип введите 0;-0;;@

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

Чтобы снова отобразить скрытые значения, выделите ячейки, а затем нажмите клавиши CTRL+1 или на вкладке Главная в группе Ячейки наведите указатель мыши на элемент Формат и выберите Формат ячеек. Чтобы применить числовой формат по умолчанию, в списке Категория выберите Общий. Чтобы снова отобразить дату и время, выберите подходящий формат даты и времени на вкладке Число.

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

Выделите ячейку, содержащую нулевое (0) значение.

На вкладке Главная в группе Стили щелкните стрелку рядом с элементом Условное форматирование, наведите указатель на элемент Правила выделения ячеек и выберите вариант Равно.

В левом поле введите 0.

В правом поле выберите Пользовательский формат.

В диалоговом окне Формат ячеек откройте вкладку Шрифт.

В поле Цвет выберите белый цвет.

Использование формулы для отображения нулей в виде пробелов или тире

Для выполнения этой задачи используйте функцию ЕСЛИ.

Чтобы этот пример проще было понять, скопируйте его на пустой лист.

Описание (результат)

Второе число вычитается из первого (0).

Возвращает пустую ячейку, если значение равно нулю

Возвращает дефис (-), если значение равно нулю

Дополнительные сведения об использовании этой функции см. в статье Функция ЕСЛИ.

Скрытие нулевых значений в отчете сводной таблицы

Щелкните отчет сводной таблицы.

На вкладке Параметры в группе Параметры сводной таблицы щелкните стрелку рядом с командой Параметры и выберите пункт Параметры.

Перейдите на вкладку Разметка и формат, а затем выполните следующие действия.

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

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

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

Отображение и скрытие всех нулевых значений на листе

Нажмите кнопку Microsoft Office , Excel параметры, а затем выберите категорию Дополнительные параметры.

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

Чтобы отображать в ячейках нулевые значения (0), установите флажок Показывать нули в ячейках, которые содержат нулевые значения.

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

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

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

Выделите ячейки, содержащие нулевые значения (0), которые требуется скрыть.

Вы можете нажать клавиши CTRL+1 или на вкладке Главная в группе Ячейки щелкнуть Формат > Формат ячеек.

В списке Категория выберите элемент Пользовательский.

В поле Тип введите 0;-0;;@

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

Чтобы снова отобразить скрытые значения, выделите ячейки, а затем на вкладке Главная в группе Ячейки наведите указатель мыши на элемент Формат и выберите Формат ячеек. Чтобы применить числовой формат по умолчанию, в списке Категория выберите Общий. Чтобы снова отобразить дату и время, выберите подходящий формат даты и времени на вкладке Число.

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

Выделите ячейку, содержащую нулевое (0) значение.

На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выберите «Правила выделения ячеек» > «Равно».

В левом поле введите 0.

В правом поле выберите Пользовательский формат.

В диалоговом окне Формат ячеек откройте вкладку Шрифт.

В поле Цвет выберите белый цвет.

Использование формулы для отображения нулей в виде пробелов или тире

Для выполнения этой задачи используйте функцию ЕСЛИ.

Чтобы этот пример проще было понять, скопируйте его на пустой лист.

Копирование примера

Выделите пример, приведенный в этой статье.

Важно: Не выделяйте заголовки строк или столбцов.

Выделение примера в справке

Нажмите клавиши CTRL+C.

В Excel создайте пустую книгу или лист.

Выделите на листе ячейку A1 и нажмите клавиши CTRL+V.

Важно: Чтобы пример правильно работал, его нужно вставить в ячейку A1.

Чтобы переключиться между просмотром результатов и просмотром формул, возвращающих эти результаты, нажмите клавиши CTRL+` (знак ударения) или на вкладке Формулы в группе «Зависимости формул» нажмите кнопку Показать формулы.

Скопировав пример на пустой лист, вы можете настроить его так, как вам нужно.

Как в экселе вместо нуля поставить прочерк?

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

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

Второй шаг. Выделим ячейки «В2» и «В3», после нужно нажать на правую кнопку мыши, чтобы на экране появилось специальное меню, в котором выберем строчку «Формат ячеек».

Третий шаг. В появившейся таблице, выбираем в списке возможных форматов ячеек строку «все форматы», там создаем следующую комбинацию символов: # ##0,00;-# ##0,00;"-". После чего можно нажать на кнопку «ОК».

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

Как заменить ноль на прочерк в excel

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

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

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

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

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

2. В Формат ячеек диалоговое окно под Число вкладку нажмите На заказ из Категория список и введите 0; -0; -; @ в Тип текстовое окно. Смотрите скриншот:

3, Затем нажмите OK, все нули заменены тире в исходном диапазоне. Смотрите скриншот:

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

С крутым Выбрать определенные ячейки особенность Kutools for Excel, вы можете легко выбрать все ячейки, равные заданному числу в Excel, например, все ячейки, равные 0. После выбора этих нулевых ячеек вы можете массово преобразовать их в тире с помощью клавиш Ctrl + Enter.

Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная пробная версия 30 -день, кредитная карта не требуется! Get It Now

Преобразование нулей в тире с функцией замены

Вы также можете применить функцию «Заменить», чтобы полностью преобразовать все нули в тире.

1. Нажмите Главная > Найти и выбрать > Замените для включения диалогового окна «Найти и заменить».

Заметки:
(1) Чтобы преобразовать нули в тире в определенном выделении, сначала выберите диапазон, а затем включите диалоговое окно «Найти и заменить»;
(2) Вы также можете нажать Ctrl + H вместе, чтобы открыть диалоговое окно «Найти и заменить».

2. В диалоговом окне «Найти и заменить» сделайте следующее:
(1) Тип 0 в Найти то, что коробка;
(2) Введите тире – в Заменить коробка;
(3) Нажмите Опции >> кнопку, чтобы развернуть дополнительные параметры замены, и проверьте Соответствие всему содержимому ячейки вариант;

3, Нажмите Заменить все кнопку.

4. Затем появляется диалоговое окно, в котором показано, сколько замен было выполнено. Щелкните значок OK чтобы закрыть его.

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

Преобразование нулей в тире с помощью Kutools for Excel

Если вы установили Kutools for Excel, Его Выбрать определенные ячейки Функция может помочь вам сначала выбрать все ячейки с нулями, а затем сразу преобразовать все нули в тире.

Kutools for Excel— Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 60-дневная пробная версия, кредитная карта не требуется! Get It Now

1. Выберите диапазон, в котором вы хотите преобразовать нули в тире, и нажмите Kutools > Выберите > Выбрать определенные ячейки, см. снимок экрана:

2. В Выбрать определенные ячейки диалоговое окно, нажмите Ячейка вариант, выберите Равно из Конкретный тип выпадающий список, введите 0 в текстовое поле и щелкните Ok кнопка. Смотрите скриншот:

3. Теперь все нули выделены. Затем введите символ — тире в строке формул и нажмите Ctrl + Enter ключи вместе. Затем все нули были преобразованы в тире, см. Снимок экрана:

Демонстрация: преобразование нулей в тире в Excel

Kutools for Excel включает более 300 удобных инструментов для Excel, которые можно бесплатно попробовать без ограничений в 30 дней. Бесплатная пробная версия сейчас! Купить!

Статьи по теме:

Преобразовать ноль в пустой в выбранном диапазоне в Excel
Используйте функцию «Найти и заменить», VBA и отличный сторонний инструмент, чтобы преобразовать нули в пустые при выделении.

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

Скрыть ноль на оси диаграммы в Excel
Как правило, ось диаграммы в большинстве случаев начинается с 0. Однако иногда 0 не требуется, и вы можете скрыть его от оси диаграммы.

Удалите все нули после десятичной дроби в Excel
При использовании функции увеличения десятичного числа для массового изменения десятичных разрядов нескольких ячеек к целым числам будут добавлены нули. Теперь в этой статье представлены два решения для удаления этих нулей после десятичной точки.

Лучшие инструменты для офисной работы

Превратите часы в минуты с Kutools for Excel!

Готовы ускорить свои задачи в Excel? Используйте силу Kutools for Excel — ваш лучший инструмент для экономии времени. Оптимизируйте сложные задачи и скользите по своим данным, как профессионал. Испытайте Excel с молниеносной скоростью!

Зачем тебе Kutools for Excel

���� Более 300 мощных функций: Kutools содержит более 300 расширенных функций, упрощающих вашу работу в более чем 1500 сценариях.

�� Превосходная обработка данных: объединяйте ячейки, удаляйте дубликаты и выполняйте расширенные преобразования данных — и все это без особых усилий!

���� Эффективные пакетные операции: Зачем прилагать дополнительные усилия, если можно работать с умом? С легкостью импортируйте, экспортируйте, комбинируйте и корректируйте данные.

�� Настраиваемые диаграммы и отчеты: доступ к множеству дополнительных диаграмм и создание информативных отчетов, рассказывающих историю.

���� Мощная панель навигации: Получите преимущество благодаря надежному диспетчеру столбцов, диспетчеру рабочих листов и пользовательскому избранному.

. Семь типов раскрывающихся списков: Упростите ввод данных с помощью раскрывающихся списков различных функций и типов.

�� Удобный: простой инструмент для начинающих и мощный инструмент для экспертов.

Как в таблице сделать прочерк вместо ноля

В некоторых отчетах в ячейках с нулевым значением предпочтительнее ставить прочерк вместо ноля, потому что по смыслу тире больше подходит, чем ноль в ячейке. Для улучшения читаемости таблиц существует несколько способов заметить 0 на (-).
Изменением формата чисел
При помощи пользовательского формата
При помощи функции ЕСЛИ
При помощи функции ТЕКСТ
Использование функции «Найти и Заменить»
При помощи VBA

Изменением формата чисел

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

1. Выделите столбец, в котором нужно произвести замену.
2. Перейдите на вкладку «Главная».
3. В блоке «Число» вызовите контекстное меню.
4. Выберите формат «Финансовый», установите нужное количество знаков после запятой и откажитесь от обозначения валюты в конце числа, если это необходимо.
5. Нажмите «Ок».

В итоге нули отобразились в виде прочерка.

При помощи пользовательского формата

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

1. Выделите диапазон ячеек, в котором будут производиться изменения.
2. Перейдите на вкладку «Главная».
3. В блоке «Число» нажмите кнопку со стрелкой для вызова меню.
4. Выберите вид формата «Все форматы».
5. В поле «Тип» введите сочетание #,##0;-#,##0;-
6. Нажмите «ОК».

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

При помощи функции ЕСЛИ

Функция ЕСЛИ будет проверять каждую ячейку на нулевое значение, и есть это выражение верно, ставить знак тире.

1. Установите курсор в первую ячейку диапазона.
2. Напишите формулу проверки на ноль в виде: =ЕСЛИ(C3=0;”-“;C3)
3. Проверьте правильность исполнения формулы.
4. Скопируйте формулу на последующие ячейки столбца.

Обратите внимание, что ячейки с тире будут иметь текстовое значение.

При помощи функции ТЕКСТ

Функция ТЕКСТ также отображает ноль как прочерк.

1. Создайте пустой столбец рядом с тем столбцом, где нужно заменить ноль на прочерк.
2. В первой ячейке этого столбца напишите формулу вида = ТЕКСТ( C3;”#,##0;-#,##0;-“)
3. Скопируйте формулу в остальные ячейки столбца, потянув вниз за правый нижний угол ячейки.

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

Использование функции «Найти и Заменить»

Это простейший способ заменить ноль на прочерк.

1. Выделите диапазон, в котором нужно произвести замену.
2. Перейдите в раздел «Найти и заменить» в меню Excel либо нажав сочетание клавиш Ctrl+H
3. В поле «Найти» введите 0, в поле «Заменить» введите – .
4. Отметьте опцию «Ячейка целиком», чтобы 0 в составе чисел не был замен на прочерк.
5. Нажмите кнопку «Заменить все».
6. Закройте окно «Найти и Заменить.»

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

При помощи VBA

VBA – отличный способ изящно решать многие задачи Excel. Создадим макрос, который будет автоматически заменять нули на тире.

1. Перейдите на вкладку «Разработчик». Если ее нет в меню нажмите сочетание клавиш Alt+F11.
2. В появившемся окне прейдите на вкладку «Insert» .
3. Выберите раздел «Мodule».
4. Наберите следующий код:
   Sub ReplaceZeros()
   Dim selectedRange As Range
   Set selectedRange = Application.Selection

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