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

В некоторых отчетах в ячейках с нулевым значением предпочтительнее ставить прочерк вместо ноля, потому что по смыслу тире больше подходит, чем ноль в ячейке. Для улучшения читаемости таблиц существует несколько способов заметить 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

   For Each i In selectedRange.Cells
   If i.Value = 0 And Len(i.Value) > 0 Then
   i.Value = “-”
   End If
   Next i
   End Sub
   

5. Закройте все окна. Макрос сохранится автоматически
6. Выделите диапазон ячеек для замены 0 на тире.
7. Перейдите на вкладку «Вид» и откройте раздел «Макросы».
8. Выберите макрос ReplaceZeros и нажмите кнопку «Выполнить».

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