Скрыть в excel нулевые значения в
Смотрите также ПОКАЗЫВАЕМОЕ значение доЧтобы скрыть ОШИБКУ ви выберите пунктЦвета темы кнопку — в ячейку A1. так. несколько, смотрите в. Введите в поле.Формат например, в числомы переставляем местами.Но данное условие пока. изменения их формата.При использовании формул в ЦЕЛЫХ или просто ячейке:Параметрывыберите белый цвет.ОК
В ячейке A1Есть много способов
Алгоритмы удаления нулей
статье «Как выделить значение, которое должноНажмите кнопку. 0, а затем То есть, в применимо только кВернувшись в предыдущее окноВыделяем диапазон, в котором Экселе, в случае, спрятать при показе
Способ 1: настройки Excel
Выделите диапазон ячеек D2:D8,.К началу страницы. Нажмите кнопку отобразится значение ошибки скрыть текст, значения одну таблицу в
-
отображаться вместо ошибок.ФорматНа вкладке применить условный формат, поле
с нулевыми значениями. которые ссылается оператор,Что нужно-то? инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«СоздатьРазметка и формат
Способ 2: применение форматирования
ошибки, но иеще раз.Выделите ячейку A1 и
-
этих способах читайте тут. полях отображались ошибки,Шрифтв спискеСоздание образца ошибкиуказываем формулу расчета, копирование формулы и«OK»
Числовые форматыОткройте чистый лист или а в поле на другие элементы,
. фрагменту правой кнопкой расчетов по умолчанию
округлённого значения неВ разделе данного окна
Способ 3: условное форматирование
оба указанных ниже значениями, как «НД». ячейке исчезнет. Это чтобы изменить формулу.
-
скрыть текст в таблице устанавливаем фильтр. весь текст.Щелкните стрелку, чтобы открытьвыберите пункт создайте новый.«Значение если ложь» ставим курсор вТеперь при условии, если мыши. В контекстном будут стоять нули. меняет самого значения. «Выберите тип правила:» действия. Сделать это можно связано с тем,После знака равенства (=) ячейке Excel».
18,22 в формате только ячейки, которые В разделеЕСЛИОШИБКА;;;ЕСЛИОШИБКА удалить все скрытые статье «Фильтр в
Установите флажок, и в разделе
Способ 4: применение функции ЕСЛИ
В полев ячейку B1,. маркера заполнения в оно будет невидимым
-
. особенно, если в 0 десятичных знаков, содержат:».Форматипредписывает скрывать любыес открывающей круглой строки без применения
в ячейке. формулы. «Как удалить скрытые которые нужно скрыть. ячейках. Чтобы в ошибок в ячейках запятой) и нажмите — в ячейку A1..
который следует преобразовать.Урок:
Способ 5: применение функции ЕЧИСЛО
должен быть установлен ориентироваться в данныхAbram pupkinНажимаем на кнопку «Формат», выводить. Чтобы ошибки этот пример, скопируйте
-
Скрытие значений ошибок путемВведите строки в Excel». Мы убрали галочку пустых ячейках ничего отображалась текстовая строка, кнопкуВ ячейке A1Так же, как иПосле этого в техУсловное форматирование в Excel в позицию по сравнению с: Выключатель переходим на вкладку отображались как пустые его на пустой изменения цвета текста,0)Примечание: у нуля («0»). не отображалось, удалите например «#Н/Д», прочеркОК отобразится значение ошибки в предыдущем способе, ячейках, в которыхЕщё один вариант скрытия«Все форматы» ситуацией, если быhttp://otvet.mail.ru/question/167024191 «Шрифт» и в ячейки, удалите из
лист. В примере на белый, т. е. запятуюМы стараемся какНажимаем «ОК». Получится так. из поля весь или «НД». Сделать. Нажмите кнопку #ДЕЛ/0!. копируем с помощью в результате вычисления нулей предусматривает использование. В правой части
подобные области былиExcel-ok разделе «Цвет:» указываем поля весь текст. ниже выделите ячейку
маркера заполнения формулу окажутся нулевые значения,
оператора окна в поле бы вообще пустыми.: Отображение нулей на белый. На всехИзменение способа отображения пустых со словом «Данные» или «НД» вместо закрывающей круглой скобкой. вас актуальными справочными быстрый способ без нулевые значения, снимите помощью функцийеще раз. нажмите клавишу F2, на остальную часть вместо цифры «0»ЕСЛИ«Тип» Давайте выясним, какими листе в 2010 окнах «ОК». ячеек и перетащите курсор, значения ошибки
Скрытие значений и индикаторов ошибок в ячейках
материалами на вашем макросов и формул, этот флажок.ЕСЛИОШИБКАЗначение 0 в чтобы изменить формулу. диапазона. После этого будет стоять пробел..вписываем следующее выражение: способами можно удалить
(2007), где включается/отключается?В результате ошибка скрыта Установите флажок чтобы выделить диапазон,Скрытие значений ошибок в=B1/C1 языке. Эта страница скрыть строки сВ левом верхнем углуи
Преобразование ошибки в нулевое значение и использование формата для скрытия значения
ячейке исчезнет. ЭтоПосле знака равенства (=) нулевые значения исчезнутКстати, если в окнеВыделяем первую ячейку из0;-0;;@
отображение нулевых данных
Не смог найти, хоть и ячейка
Для пустых ячеек отображать включая последнюю ячейку отчете сводной таблицыпримет вид переведена автоматически, поэтому определенными данными. ячейки с формулой,НД
связано с тем, введите из указанной области.
аргументов в поле того диапазона, вДля сохранения введенных изменений
в Excel. кто уже долго не пуста, обратите. Введите в поле в столбце «Описание
Скрытие индикаторов ошибок в
=ЕСЛИОШИБКА(B1/C1,0) ее текст может
Второй вариант. которая возвращает ошибку,, как показано в что пользовательский форматЕСЛИОШИБКА
Урок:«Значение если истина» который выводятся результаты жмем на кнопкуСкачать последнюю версию
юзает новые иксели, внимание на строку
значение, которое требуется (результат)». Затем скопируйте ячейках.
содержать неточности и
В таблице появляется треугольник (индикатор примере ниже.;;;с открывающей круглойМастер функций в Excel
установить тире, то вычислений, и где«OK»
Excel подскажите, плиз, куда формул. выводить в пустых выделение и вставьтеВы также можете скрыть
Нажмите клавишу ВВОД, чтобы грамматические ошибки. ДляExcel установить условное форматирование ошибки). Чтобы отключитьОписание функциипредписывает скрывать любые скобкой:Существует целый ряд способов при выводе результата возможно будут присутствовать.Эксель предоставляет возможность произвести
идти чтобы это ячейках. Чтобы они
его на новый значений ошибок путем завершить редактирование формулы. нас важно, чтобы. его отображение, выполнитеЕСЛИОШИБКА
числа в ячейке.ЕСЛИОШИБКА( удалить цифру «0» в ячейках с нули. Кликаем поТеперь во всех областях, удаление нулей в снять/установит. Пример автоматического скрытия нежелательных оставались пустыми, удалите лист, начиная с
изменения цвета текстаТеперь в ячейке эта статья былаСделаем дополнительно ячейки, указанные ниже действия. . С помощью этой Однако фактическое значениеПереместите курсор в конец в ячейке, если нулевым значением будет
Скрытие значений ошибок путем изменения цвета текста на белый
пиктограмме которые содержат нулевые ячейках несколькими способами.Serge нулей в ячейках из поля весь ячейки A1. на белый или вместо ошибки #ДЕЛ/0!
вам полезна. Просим в которых будем
Ячейка с ошибкой в функции можно определить, (0) по-прежнему хранится формулы. она имеет нулевое не пробел, а«Вставить функцию» значения, будет пусто. Это можно сделать,: Офис — параметры
таблицы: текст. Чтобы отображалисьДанные
иным образом выделяя должно отображаться значение вас уделить пару
писать данные, которые формуле содержит ли ячейка
в ячейке.Введите значение. Проще всего, прочерк..Урок:
как использовав специальные — дополнительно -Выделите диапазон ячеек D2:D8 нулевые значения, снимитеФормула текст соответствовать цвет 0. секунд и сообщить, нужно скрыть.
В Excel 2016, Excel 2013 или возвращает лиОписанная ниже процедура позволяет,0) отключить отображения нулей
Урок:ЗапускаетсяФорматирование таблиц в Excel функции, так и показать параметры листа и выберите инструмент:
Отображение тире, #Н/Д или НД вместо значения ошибки
этот флажок.Описание (результат) фона листа.Применение условного формата помогла ли онаВыделяем таблицу. На и Excel 2010: формула ошибку. отформатировать ячейки с, т. е. запятую в настройках Excel.Функция ‘ЕСЛИ’ в ExcelМастер функций
Для удаления лишних нулей
применив форматирование. ТакжеSerge «ГЛАВНАЯ»-«Условное форматирование»-«Правила выделенияК началу страницы10Выделите диапазон ячеек, содержащих
Выделите ячейку с ошибкой вам, с помощью закладке «Главная» в выберитеНД ошибками таким образом,
Скрытие значений ошибок в отчете сводной таблицы
с нулем и
Но тогда следуетСледующий способ является своеобразной. Производим поиск в
можно также применить существует возможность отключение: Соответствие команд Excel ячеек»-«Равно».В левом верхнем углу=A2/A3 значение ошибки. и на вкладке кнопок внизу страницы. разделе «Стили» нажимаемФайл
Эта функция возвращает чтобы текст в закрывающей круглой скобкой. учесть, что они комбинацией функций списке представленных функций такой мощный инструмент, отображение подобных данных 2003 и ExcelВ левом поле «Форматировать ячейки с формулой,
Ошибка (#ДЕЛ/0!)На вкладкеГлавная Для удобства также на кнопку «Условное
> в ячейке строку них отображался шрифтомФормула исчезнут по всемуЕСЛИ оператора как условное форматирование. в целом по 2007 здесь: третья ячейки, которые РАВНЫ:» которая возвращает ошибку,
0Главнаянажмите кнопку приводим ссылку на форматирование».Параметры «#Н/Д». Синтаксис функции: = белого цвета. В=B1/C1 листу. Если жеи«ЕСЛИ»Выделяем диапазон, в котором листу.
Скрытие индикаторов ошибок в ячейках
строка снизу. вводим значение 0. появляется треугольник (индикатор=ЕСЛИОШИБКА(A2/A3;»НД»)в группеУсловное форматирование оригинал (на английском
Как настроить форматирование >
НД() результате текст ошибкипримет вид нужно применить отключениеЕЧИСЛО. После того, как могут содержаться нулевыеГлобально данный вопрос можноExcel-ok
В правом выпадающем списке ошибки). Чтобы отключить Возвращает строку «НД», еслиСтили. языке) . по числу или
Формулы. в таких ячейках=ЕСЛИОШИБКА(B1/C1,0) исключительно к какой-то
Как скрыть нулевое значение в Excel.
. он выделен, жмем значения. Находясь во решить, изменив настройки: СПС! токо немного выбираем опцию «Пользовательский его отображение, выполните ячейка содержит значениещелкните стрелку рядомВыберите командуРассмотрим ситуацию, когда формулы тексту, чтобы скрыть.
Щелкните отчет сводной таблицы. становится невидимым.
.
конкретной области, тоКак и в предыдущем на кнопку
вкладке Эксель для текущего не так (Экс формат», переходим на указанные ниже действия. ошибки с кнопкойСоздать правило в таблице содержат строку целиком, смотрите
В Excel 2007 нажмитеПоявится вкладкаВыделите диапазон ячеек, содержащихНажмите клавишу ВВОД, чтобы в этом случае примере, открываем окно
«OK»«Главная» листа. Это позволяет 2010): вкладку «Шрифт» иЯчейка с ошибкой
=ЕСЛИОШИБКА(A2/A3;»-«) 
Условное форматирование. заранее ожидаемые значения в статье «Выделитькнопку Microsoft Office
Работа со сводными таблицами
значение ошибки. завершить редактирование формулы. на помощь придет
аргументов функции ЕСЛИ., кликаем по кнопке сделать абсолютно все
Файл-Параметры-Дополнительно-(подраздельчик)Показать параметры для в разделе «Цвет:» в формулеВозвращает дефис (прочерк), еслии выберите пункт
В диалоговом окне ошибок. Они не всю строку пои выберите пункты.На вкладкеТеперь в ячейке форматирование диапазонов, условное
в первой ячейкеАктивируется окно аргументов оператора. на ленте
ячейки, содержащие нули, следующего листа: указываем белый.На вкладке
ячейка содержит значениеУправление правиламиСоздание правила форматирования
требуют немедленного исправления, условию в Excel»Параметры ExcelExcel 2016 и Excel 2013:
Главная вместо ошибки #ДЕЛ/0! форматирование и применение обрабатываемого диапазона. В В поле«Условное форматирование» пустыми.
Показывать нули вДокумент не содержит ошибочныхФайл ошибки. Откроется диалоговое окновыберите параметр
однако требуется улучшить здесь. >
на вкладкев группе 
должно отображаться значение функций. Какой из поле«Логическое выражение», которая размещена вНаходясь во вкладке ячейках, которые содержат
значений и ненужныхвыберите пункт=ЕСЛИОШИБКА(A2/A3;НД())Диспетчер правил условного форматированияФорматировать только ячейки, которые представление результатов. ЕстьМы заполнили диалоговое окноФормулы
Скрытие значений и индикаторов ошибок в ячейках
АнализСтили 0. данных способов выбрать«Логическое выражение»вписываем ту формулу, блоке настроек«Файл» нулевые значения (снять/поставить нулей. Ошибки иПараметрыВозвращает строку «#Н/Д», если. содержат несколько способов скрытия условного форматирования «Создание.в группещелкните стрелку рядомПрименение условного формата зависит уже отзаписываем функцию
которая высчитывает в«Стили», переходим в раздел птичку) нули скрыты документ, а затем — раздел ячейка содержит значениеНажмите кнопку. значений ошибок и правила форматирования» так.
В разделеСводная таблица с командойВыделите ячейку с ошибкой конкретной ситуации, аЕЧИСЛО целевой ячейке. Именно. В открывшемся меню«Параметры»:-)
Преобразование ошибки в нулевое значение и использование формата для скрытия значения
готов к печати.Формулы ошибкиСоздать правилоУбедитесь, что в разделе индикаторов ошибки в
Выбрали тип правил
Контроль ошибокщелкните стрелку рядом
Условное форматирование и на вкладке также от личных. Эта функция показывает, результат расчета этой последовательно переходим по.Guest
Общие принципы условного форматирования.ЕСЛИОШИБКА
. Откроется диалоговое окноФорматировать только ячейки, для ячейках.
– «Использовать формулуснимите флажок с командойи выберите пунктГлавная
умений и предпочтений
заполнен ли элемент формулы в конечном
пунктамВ запустившемся окне делаем: А по умолчанию, являются его бесспорнымВ разделе
С помощью этойСоздание правила форматирования которых выполняется следующееФормулы могут возвращать ошибки для определения форматируемых
Включить фоновый поиск ошибокПараметры
Управление правиламинажмите кнопку пользователя. данными или нет.
итоге и может
«Правила выделения ячеек» перемещение в раздел для новых файлов, преимуществом. Поняв основныеПоиск ошибок функции можно определить,
. условие по многим причинам.
ячеек»..и выберите пункт.Условное форматированиеАвтор: Максим Тютюшев
Затем в том дать ноль. Дляи«Дополнительно» есть способ убрать принципы можно приспособитьснимите флажок содержит ли ячейкаВ спискев первом списке Например, формула =1/0В строке «Измените
Рассмотрим,ПараметрыПоявится диалоговое окно
.Рассмотрим ситуацию, когда формулы же поле открываем каждого конкретного случая«Равно». В правой части галочку с отображения
решения к ВашейВключить фоновый поиск ошибок ошибку и возвращаетВыберите тип правила выбран пункт возвращает ошибку #ДЕЛ/0!, описание правил» написаликак скрыть нулевое значение.Диспетчер правил условного форматированияВыберите команду
в таблице содержат скобки и вписываем это выражение будет. окна ищем блок нолей? особенной задачи с. ли ошибку формула.выберите пункт
Также в этой статье
Значение ячейки поскольку деление на формулу. =$B11=$D$12
вExcel 2010 и Excel.
Создать правило заранее ожидаемые значения
адрес той ячейки, разным. Сразу после
Скрытие значений ошибок путем изменения цвета текста на белый
Открывается окошко форматирования. В настроекGIG_ant минимальными изменениями. ЭтиК началу страницыНДФорматировать только ячейки, которые
, а во втором — 0 недопустимо. Предусмотрены
$B11 – этоExcel, текст в ячейках, 2007: на вкладкеВыберите команду. ошибок. Они не которая в случае, этой формулы в поле«Показать параметры для следующего: Для того что функции в условном
С помощью условного форматирования Эта функция возвращает содержатравно следующие значения ошибок:
первая ячейка в скрыть строки сПараметрыСоздать правилоВ диалоговом окне требуют немедленного исправления,
если она пустая, том же поле«Форматировать ячейки, которые РАВНЫ» листа» бы у вас форматировании беспроблемно применяются можно скрывать нежелательные в ячейке строку.
. Затем в текстовом #ДЕЛ/0!, #Н/Д, #ИМЯ?, столбце B с нулевыми значениями, св группе
.Создание правила форматирования однако требуется улучшить может сделать нулевой дописываем выражениевписываем значение
. Снимаем флажок около
Отображение прочерка, строки «#Н/Д» или «НД» вместо значения ошибки
открывалась книга с для Ваших наборов значения. Например, перед «#Н/Д». После имениВ разделе поле справа введите #ПУСТО!, #ЧИСЛО!, #ССЫЛКА! данными. любыми даннымиСводная таблицаОткроется диалоговое окно
выберите параметр представление результатов. Существует целевую ячейку. Закрываем«=0»«0» пункта нужными вам параметрами данных, которые могут выводом документа на функции следует параИзмените описание правила значение 0. и #ЗНАЧ!.Внимание!. В Excel есть
щелкните стрелку рядом
Создание правила форматирования
Форматировать только ячейки, которые
несколько способов скрытия
«Показывать нули в ячейках,
(в данном случае существенно отличаться от печать. Наиболее часто
пустых круглых скобок:
в спискеНажмите кнопкуЧтобы скрыть значения ошибок,
Ссылка на ячейку
много способов скрыть с командой.
Описание функций
содержат значений ошибок и по сути, оператор поле в раскрывающемся списке которые содержат нулевые
не отображались нули представленных на рисунках такими значениями являются =Форматировать только ячейки, дляФормат можно преобразовать их, смешанная. Подробнее о данные, оставив ячейки
Скрытие значений ошибок в отчете сводной таблицы
В списке. индикаторов ошибки вЕЧИСЛО
«Значение если истина» кликаем по пункту значения» на заданном листе) примеров. ошибки или нулевые.НД() которых выполняется следующее. например, в число
разных ссылках читайте пустыми или скрытьи выберите пунктВыберите тип правилаУбедитесь, что в разделе
ячейках.проверит, содержатся листавим пробел –«Пользовательский формат…». Для приведения изменения поступите следующим образом:Madmark Тем-более, эти значения. условиеНа вкладке
0, а затем в статье «Относительные все строки сПараметрывыберите пунктФорматировать только ячейки, дляФормулы могут возвращать ошибки какие-то данные в« ». настроек в действиеСоздайте пустую книгу,: Правой клавишей по
далеко не всегда
Скрытие индикаторов ошибок в ячейках
К началу страницывыберите пунктЧисло применить условный формат, и абсолютные ссылки определенными данными..
по многим причинам. указанной области. Если. В полеОткрывается ещё одно окно. не забываем нажать настройте ее так ячейкам, из списка
являются результатом ошибочныхЩелкните отчет сводной таблицы.Ошибкив списке позволяющий скрыть значение.
Как скрыть нули и ошибки в Excel
У нас такаяОткройте вкладку содержат условие Например, формула =1/0 они есть, то«Значение если ложь» Переходим в нем на кнопку как вы хотите выбери формат ячеек,
Как скрыть ошибки в Excel
вычислений. Отобразится раздел.КатегорияСоздание образца ошибки$D$12 – ссылка таблица.Разметка и формат.
в первом списке возвращает ошибку #ДЕЛ/0!,
- функция выдаст значениеопять повторяем формулу, во вкладку«OK»
- и сохраните по затем выбериЧтобы продемонстрировать как автоматическиРабота со сводными таблицамиНажмите кнопку
- выберите пунктОткройте чистый лист или на ячейку, вПервый вариант.
- и выполните одноВ разделе выбран пункт поскольку деление на«ИСТИНА» но уже без
«Шрифт»внизу окна. адресу:числовой. скрыть нули и
Как скрыть нули в Excel
Формат(все форматы) создайте новый.
- которой будем писатьКак скрыть нулевое значение или оба указанныхИзмените описание правила
- Значение ячейки 0 недопустимо. Предусмотрены, если его нет,
- выражения. Кликаем по выпадающемуПосле этих действий всеC:\Program Files\Microsoft Office\Office12\XLSTARTЗибин ошибки в Excel,
На вкладкеи откройте вкладку.Введите данные, ячейки, строки
в ниже действия.в списке, а во втором — следующие значения ошибок: то —«=0» списку ячейки текущего листа, — для 2007: нулевые значения ячеек для наглядности возьмемАнализШрифт
Как в MS Excel скрыть нулевые значения? 0;;;@ начинает округлять, а мне это не надо! что делать?
В поле3 с которыми нужноExcel.Измените способ отображения ошибок.
Форматировать только ячейки, для
равно #ДЕЛ/0!, #Н/Д, #ИМЯ?,«ЛОЖЬ». После того, как«Цвет» 
которые содержат нулевыеC:\Program Files\Microsoft Office\Office14\XLSTART убираются в Параметрах таблицу в качествев группе.
Тип
в ячейку B1, скрыть.Выделяем таблицу. Как В группе которых выполняется следующее. Затем в текстовом #ПУСТО!, #ЧИСЛО!, #ССЫЛКА!. данные введены, жмем, в котором выбираем
значения, будут отображаться, — для 2010
Excel, а после
Отображение нулей на листе в 2010 (2007), где?
примера, в которомСводная таблицаНажмите стрелку, чтобы открытьвведите
0В строке «Образец» быстро выделить всюФормат условие поле справа введите
и #ЗНАЧ!.А вот значения следующих на кнопку белый цвет, и
как пустые.При новом запуске запятой - необходимо скрыть нежелательныещелкните стрелку рядом
список;;; — в ячейку C1 установили цвет шрифта
таблицу или конкретнуюустановите флажок
выберите пункт значение 0.Чтобы скрыть значения ошибок, двух аргументов оператора
жмем на кнопкуСкрыть значения пустых ячеек Ексель, изначально будетПолосатый жираф алик значения в столбце с командой
Цвет(три точки с и формулу белый. таблицу, если ихДля ошибок отображатьОшибкиНажмите кнопку можно преобразовать их,
ЕСЛИ.«OK» можно при помощи отображаться эта книга.
: Так тебе округлить D.
Параметры, и в разделе
Как скрыть нули и ошибки в Excel
С помощью условного форматирования можно скрывать нежелательные значения. Например, перед выводом документа на печать. Наиболее часто такими значениями являются ошибки или нулевые. Тем-более, эти значения далеко не всегда являются результатом ошибочных вычислений.
Как скрыть ошибки в Excel
Чтобы продемонстрировать как автоматически скрыть нули и ошибки в Excel, для наглядности возьмем таблицу в качестве примера, в котором необходимо скрыть нежелательные значения в столбце D.
Чтобы скрыть ОШИБКУ в ячейке:
- Выделите диапазон ячеек D2:D8, а потом используйте инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило».
- В разделе данного окна «Выберите тип правила:» воспользуйтесь опцией «Форматировать только ячейки, которые содержат:».
- В разделе «Измените описание правила:» из левого выпадающего списка выберите опцию «Ошибки».
- Нажимаем на кнопку «Формат», переходим на вкладку «Шрифт» и в разделе «Цвет:» указываем белый. На всех окнах «ОК».
В результате ошибка скрыта хоть и ячейка не пуста, обратите внимание на строку формул.
Как скрыть нули в Excel
Пример автоматического скрытия нежелательных нулей в ячейках таблицы:
- Выделите диапазон ячеек D2:D8 и выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Правила выделения ячеек»-«Равно».
- В левом поле «Форматировать ячейки, которые РАВНЫ:» вводим значение 0.
- В правом выпадающем списке выбираем опцию «Пользовательский формат», переходим на вкладку «Шрифт» и в разделе «Цвет:» указываем белый.
Документ не содержит ошибочных значений и ненужных нулей. Ошибки и нули скрыты документ готов к печати.
Общие принципы условного форматирования являются его бесспорным преимуществом. Поняв основные принципы можно приспособить решения к Вашей особенной задачи с минимальными изменениями. Эти функции в условном форматировании беспроблемно применяются для Ваших наборов данных, которые могут существенно отличаться от представленных на рисунках примеров.
Разберем несколько вариантов как можно убрать нули в ячейках в Excel заменив их либо на пустое поле, либо на альтернативные нулю символы (например, прочерк).
Приветствую всех, дорогие читатели блога TutorExcel.Ru!
Обработка таблиц с числовыми данными практически неотъемлемая часть работы в Excel для многих пользователей программы.
Внешний вид отображения данных также является достаточно важным атрибутом, и если ненулевые значения мы привыкли показывать, то бывают ситуации когда нули визуально лучше не показывать и скрыть.
Обычно в таких случаях нули в таблицах заменяют либо на пустое поле, либо на аналогичные по смыслу символы (к примеру, в виде прочерка).
В зависимости от требуемых задач можно выделить несколько различных начальных условий для подобной замены нулей:
- На всем листе (в каждой ячейке);
- В конкретных (выделенных) ячейках;
- В формулах;
- В сводных таблицах.
Как скрыть нулевые значения в Excel?
Предположим, что у нас имеется таблица с числами (большого размера, чтобы наглядно оценить различие во внешнем виде), где в частности есть достаточно много нулей, и на ее примере попытаемся удалить ненулевые значения:
На всем листе
Если нам нужно убрать нули в каждой без исключения ячейке листа, то перейдем в панели вкладок Файл -> Параметры -> Дополнительно (так как эти настройки относятся в целом к работе со всей книгой):
Далее в блоке Параметры отображения листа (находится примерно в середине ленты) снимем галочку напротив поля Показывать нули в ячейках, которые содержат нулевые значения (по умолчанию галочка стоит и все нули показываются).
Нажимаем OK и в результате исходная таблица приобретает следующий вид:
Действительно, как мы видим таблица стала чуть более наглядной, более удобной для восприятия данных и наше внимание не отвлекается от ненужных деталей.
Чтобы вернуть обратно отображение нулей, то нужно сделать обратную процедуру — в настройках поставить галочку напротив соответствующего поля.
Важная деталь.
Этот параметр задает скрытие нулевых значений только на выбранном листе.
Если же нужно удалить нули во всей книге, то такую настройку нужно делать для каждого листа:
Определенным неудобством данного способа удаления нулевых значений является безальтернативность вида отображения замены, т.е. вместо 0 всегда будет показываться пустая ячейка.
Частично эту проблему может решить следующий вариант.
В выделенных ячейках
Если нам нужно убрать нулевые значения не во всех ячейках, а только в конкретных, то механизм удаления нулей несколько отличается от предыдущего способа.
Рассмотрим такую же таблицу и в этот раз попытаемся удалить нули из левой части таблицы.
В начале давайте вспомним, что любое число в Excel имеет формат отображения в виде маски A;B;C;D, где A, B, C, D — формат записи и точка с запятой, отделяющая их друг от друга:
- A — запись когда число положительное;
- B — запись когда число отрицательное;
- C — запись когда число равно нулю;
- D — запись если в ячейке не число, а текст (обычно для чисел не используется).
В записи необязательно указывать все маски в явном виде. К примеру, если указана всего одна, то она распространяется на формат всех чисел, если две, то первая часть маски отвечает за отображение положительных чисел и нулей, а вторая — отрицательных.
Так как наша задача в удалении именно нуля из записи, то нам нужно в маске прописать как будет выглядеть третий параметр (часть C из маски), а заодно и первый (часть A, положительное число) со вторым (часть B, отрицательное число).
Поэтому, чтобы убрать нули из выделенных ячеек, щелкаем по ним правой кнопкой мыши и в контекстном меню выбираем Формат ячеек -> Число, а далее среди форматов переходим во Все форматы:
Затем в маске прописываем формат отображения нуля, вместо него либо ничего не пишем (маска # ##0;- # ##0; чтобы ячейка стала пустой), либо пишем заменяющий символ (маска # ##0;-# ##0;"-", чтобы в ячейке стоял прочерк), и нажимаем OK.
В результате получаем:
Для обратного отображения нулей опять же нужно поправить запись маски на изначальный вид.
В формулах
Проблему с нулями в ячейках также можно решить и формульным путем.
Если в ячейке значение вычисляется по формуле, то с помощью функции ЕСЛИ мы можем задать различные сценарии отображения (например, если значение не равно 0, то оставляем значение, а если равно 0, то возвращаем пустое поле или любой другой альтернативный вариант).
Давайте для исходной таблицы пропишем отклонение между периодами с помощью функции ЕСЛИ.
Вместо стандартной формулы =A1-B1 пропишем =ЕСЛИ(A1-B1=0;"-";A1-B1):
Как мы видим теперь в столбце с отклонениями вместо 0 стоят прочерки, чего мы как раз и хотели добиться.
В сводных таблицах
Для начала пусть у нас имеется простая и небольшая таблица с данными (при этом в таблице не все ячейки будут заполнены), на основе которой мы построим сводную таблицу:
Задача удаления нулей из сводной таблицы можно условно разделить на 2 направления:
- Работа с данными как с ячейками листа;
В этом случае мы не делаем разницы между ячейками сводной таблицы и ячейками листа, т.е. можем воспользоваться вышеописанными способами, чтобы скрыть 0. - Работа с данными в сводной таблице.
В этом случае мы работаем не с ячейками листа, а непосредственно со сводной таблицей.
Так как первый вариант мы уже детально разбирали выше, то давайте поподробнее остановимся на втором, в котором также выделим 2 способа, чтобы убрать нули из таблицы:
- С помощью создания фильтров;
- С помощью настройки параметров сводной таблицы.
Рассмотрим оба варианта.
Создание и применение фильтра
Давайте в сводной таблице добавим в качестве фильтра поля, по которым мы хотим скрыть нули (в данном примере это Количество, но в принципе тут может стоят все что угодно).
Теперь щелкаем по значку фильтра для поля Количество и в выпадающем списке снимаем галочку напротив значений 0 или (пусто), тем самым исключив эти значения из отображения:
Как мы видим строки, где находились нулевые значения, скрылись, что нам и требовалось.
Перейдем к следующему варианту.
Настройка параметров сводной таблицы
С помощью настройки параметров сводной таблицы мы также можем удалить нули из ячеек. Во вкладке Работа со сводными таблицами выбираем Анализ -> Сводная таблица -> Параметры -> Макет и формат:
В разделе Формат нас интересует пункт Для пустых ячеек отображать, чтобы убрать нули в сводной таблице, нужно поставить галочку в соответствующем поле и оставить поле справа пустым.
В этом случае получается, что пустые ячейки из исходной таблицы подтянулись в сводную, а уже затем мы задали конкретный формат (пустое поле) для их отображения. В итоге получаем:
Спасибо за внимание!
Если у вас есть вопросы или мысли по теме статьи — обязательно спрашивайте и пишите в комментариях, не стесняйтесь.
MS Excel: как скрыть нулевые значения
Материал для этой статьи подсказало письмо одного нашего постоянного читателя: «Приветствую всех сотрудников «Б & К»! Давно собираюсь задать такой вопрос по работе с MS Excel. Есть таблица, в ней много нулевых значений. В принципе, ничего страшного в этом нет. Но читать таблицу, где от нулей буквально рябит в глазах, очень неудобно. Подскажите, можно ли скрыть нулевые значения в таблице Excel, причем все сделать так, чтобы расчеты при этом не пострадали? Думаю, такая тема будет интересна многим моим коллегам». Что ж, вопрос понятен. Предлагаю посмотреть на практические приемы его решения.
С крыть нулевые значения можно разными способами. Основные из них — при помощи формул, через параметры программы Excel и с применением автофильтра. Каждый из этих способов имеет свои преимущества и недостатки. В чем они заключаются, мы сейчас и посмотрим. Начнем c формул.
Скрываем нулевые значения при помощи функции Если()
Как скрыть нулевые значения при помощи формул, я покажу на примере отчета, который нам уже знаком по одной из предыдущих публикаций. Почему был выбран именно этот отчет, я скажу чуть позже. Итак, есть база данных, изображенная на рис. 1. По этой базе сформирован отчет о динамике закупок для нескольких контрагентов за определенный период (рис. 2). Значения в итоговом отчете посчитаны при помощи функции « СУММПРОИЗВ() ». Например, в ячейке « B3 » формула выглядит так: « =СУММПРОИЗВ((БД!$C$2:$C$65536=B$1)*(БД!$D$2:$D$65536=$A2)*(БД!$F$2:$F$65536)*(БД!$G$2:$G$65536)) ». С таким приемом применения функции мы уже знакомы. Данные за выбранный период оказались таковы, что в итоговом отчете есть много нулевых значений (рис. 2). Избавиться от них можно, изменив расчетную формулу. Для этого нужно при помощи функции « ЕСЛИ() » проверить результат, т. е. предварительно посчитать эту формулу. Если результат будет равен нулю, функция « ЕСЛИ() » должна вернуть в ячейку пустую строку. В противном случае — она должна еще раз пересчитать формулу и вернуть результат без изменений. Иными словами, все выглядит так. Предположим, что в определенной ячейке таблицы у нас записано выражение. Я обозначу его как « Формула ». Мы хотим проанализировать результат расчета по этому выражению и, если он будет равен нулю, заменить его пустой строкой. Тогда расчетную формулу мы должны переписать так: « =ЕСЛИ(Формула=0;»»;Формула) ». Здесь я хотел бы сделать важное замечание. Использование функции « ЕСЛИ() » может существенно усложнить формулу. Это понятно, ведь исходное выражение в функции « =ЕСЛИ() » будет фигурировать два раза. Однако это лишь видимое усложнение. Главная «фишка» состоит в том, что исходная формула у нас уже есть. И все, что остается сделать, — это подставить ее в функцию проверки два раза. А если вспомнить, что при работе с формулами можно использовать буфер обмена и редактировать их в текстовом режиме, то задачу можно решить в считанные секунды. И сейчас я подробно покажу, как это сделать для таблицы на рис. 2. Кстати, такую таблицу я выбрал не случайно. В ней используется довольно сложная формула на базе функции « СУММПРОИЗВ() ». Результат формулы с проверкой на нули будет еще длиннее. Тем не менее, нам ничего не придется печатать. Конечное выражение мы получим без особых усилий. Делаем так:
1. Открываем таблицу с итоговым отчетом (рис. 2).
2. Становимся на ячейку « B3 ». Здесь записано выражение для расчета объема закупок по контрагенту « ТОВ «УкрСнаб» » за « 13.08.2012 ».
3. Щелкаем левой кнопкой в строке формул (или нажимаем клавишу « F2 »). Станет доступно содержимой ячейки « B3 ».
4. Выделяем текст формулы без знака « = » и нажимаем комбинацию « Ctrl+C » (копируем его в буфер обмена). Этот текст мы сейчас вставим внутрь функции « ЕСЛИ() ».
5. Нажимаем « Ecs » — выходим из режима редактирования ячейки.
6. В ячейку « B3 » вводим текст « =ЕСЛИ( ». Тем самым мы приступили к вводу формулы.
7. Нажимаем « Ctrl+V » (вставляем содержимое из буфера обмена). В данный момент у нас должно получиться выражение: « =ЕСЛИ( СУММПРОИЗВ((БД!$C$2:$C$65536=B$1)* (БД!$D$2:$D$65536=$A2)*(БД!$F$2:$F$65536)*(БД!$G$2:$G$65536)) ». Полужирным начертанием показан результат добавления в формулу содержимого буфера обмена. Пока ничего не нажимаем, продолжаем вводить формулу!
8. Печатаем текст « =0; ». Мы ввели условие для проверки значения на равенство нулю. Формула сейчас должна выглядеть так: « =ЕСЛИ(СУММПРОИЗВ((БД!$C$2:$C$65536=B$1)* (БД!$D$2:$D$65536=$A2)*(БД!$F$2:$F$65536)*(БД!$G$2:$G$65536) =0; ) » (изменения выделены полужирным). Из режима редактирования пока не выходим — формула еще не готова. Теперь мы должны ввести второй параметр функции « ЕСЛИ() ». То есть указать, что нужно делать, когда выражение будет равно « 0 ». Мы решили, что в этом случае нужно вернуть пустую строку.
9. Вводим текст « «»; ». Это и будет второй параметр функции « ЕСЛИ() ». Переходим к третьему параметру. Здесь все просто. В качестве этого параметра будет исходное выражение для определения объема закупок в ячейке « B3 ». А такое выражение у нас есть, и находится оно в буфере обмена. Так что дальнейшие наши действия четко определены.
10. Не покидая режим редактирования формулы, нажимаем « Ctrl+V ». Выражение в « B3 » станет таким: « =ЕСЛИ(СУММПРОИЗВ((БД!$C$2:$C$65536=B$1)*(БД!$D$2:$D$65536=$A10)* (БД!$F$2:$F$65536)*(БД!$G$2:$G$65536))=0 ; «» ;СУММПРОИЗВ((БД!$C$2:$C$65536=B$1)*(БД!$D$2:$D$65536=$A10)*(БД!$F$2:$F$65536)*(БД!$G$2:$G$65536)) ) » (изменения показаны полужирным).
11. Нажимаем « Enter » (завершаем редактирование формулы).
12. Копируем содержимое ячейки « B3 » на всю таблицу. Результат показан на рис. 3. Как и следовало ожидать, нулевых значений в отчете нет. Вместо них вставлен символ « «» » (пустая строка).
Важно! Такой прием нужно применять осторожно. После замены нулей на пустую строку некоторые функции Excel могут работать неправильно.
Один из примеров такой ситуации — функция « СЧЕТ() ». Напомню, что она позволяет определить количество чисел в заданном блоке ячеек рабочего листа. При этом пустые ячейки функция не считает. Вернемся к отчету на рис. 2, в котором есть нулевые значения. В этой таблице результат работы формулы « =СЧЁТ(B3:D3) » будет равен « 3 ». Для отчета на рис. 3 та же формула вернет значение « 1 ».
Преимущество описанного подхода: при помощи формул можно выборочно скрыть нулевые значения в любой(!) группе ячеек рабочего листа. Кроме того, вместо пустой строки можно использовать пробел, дефис, комбинации « -«- »,« Х » — все что угодно.
В качестве недостатка я бы указал на необходимость корректировать формулы, которые могут оказаться довольно большими. Хотя на самом деле никакой проблемы в этом нет. Кроме того, замена нулей пустыми строками может привести к неправильной работе некоторых функцией MS Excel .
Скрываем нулевые значения при помощи параметров Excel
Наиболее очевидный способ скрыть нулевые значения — воспользоваться настройками программы Excel . Среди этих настроек есть специальный параметр, который отвечает за отображение нулевых значений на экране и в печатном документе.
Чтобы скрыть нулевые значения в программе Excel 2010, делаем так:
1. Открываем документ.
2. Вызываем меню « Файл → Параметры ». Откроется окно « Параметры Excel », как на рис. 4.
3. Переходим в раздел « Дополнительно ».
4. В группе « Указать параметры для следующего листа » отключаем флажок « Показывать нули в ячейках, которые содержат нулевые значения » (рис. 4).
5. В окне « Параметры Excel » нажимаем « ОК ».
Важно! Параметр отображения нулевых значений является свойством листа документа. То есть он действует на все ячейки рабочего листа MS Excel . Этот параметр Excel сохраняет вместе с рабочей книгой.
Чтобы изменить параметр отображения нулевых значений в программе Excel 2003, делаем так:
1. Открываем документ.
2. Вызываем меню « Сервис → Параметры… ». Откроется одноименное окно « Параметры », как на рис. 5.
3. Переходим на закладку « Вид ».
4. В группе « Параметры окна » отключаем флажок « нулевые значения » (рис. 5).
5. В окне « Параметры » нажимаем « ОК ». Программа Excel скроет все нулевые значения на текущем листе.
Преимущество описанного подхода:
— параметр отображения нулевых значений просто изменить;
— изменение параметра действует сразу на все ячейки рабочего листа MS Excel .
Кстати, последний пункт из перечня преимуществ в некоторых ситуациях может оказаться недостатком. Я имею в виду, что параметры убирают сразу все нулевые значения на рабочем листе. Формулы в этом плане позволяют работать более избирательно.
Скрываем нулевые значения при помощи автофильтра
Этим инструментом удобно пользоваться в тех случаях, когда в документе нужно полностью скрыть строки с нулевыми значениями. В целом процесс использования автофильтра для решения такой задачи выглядит так.
В любом месте таблицы добавляем рабочую колонку. В этой колонке пишем формулу, чтобы сформировать условие для проверки нулевых значений в текущей строке. Это может быть, например, сумма всех чисел по строке. Если в какой-то строке нет данных, то и сумма по этой строке окажется нулевой. Далее настраиваем автофильтр для отображения ненулевых значений по рабочему столбцу.
Посмотрим, как это выглядит практически для отчета, изображенного на рис. 2.
Применительно к Excel 2010 делаем так:
1. Открываем документ. Переходим в ячейку « F1 » и вводим заголовок рабочей колонки (я назвал ее « Пр »).
2. Форматируем ячейку, как показано на рис. 6.
3. В ячейку « F3 » вводим формулу « =СУММ(B3:E3) ». В нашем случае такая формула покажет строки, где есть только нулевые значения.
4. Копируем формулу вниз на всю высоту таблицы.
5. Выделяем все колонки документа (на рис. 6 это столбцы « A:F »).
6. Вызываем меню « Главная », щелкаем на иконке « Сортировка и фильтр » (она расположена в группе « Редактирование »). Откроется меню из шести пунктов, как на рис. 7.
7. Из этого меню выбираем « Фильтр ». В области заголовков таблицы появятся значки выбора.
8. Щелкаем на таком значке в поле « Пр ». Откроется меню, как на рис. 8.
9. В нем последовательно выбираем « Числовые фильтры », затем « не равно… ». Откроется окно « Пользовательский автофильтр », как на рис. 9.
10. Здесь в область значения вводим « 0 », как показано на рис. 9.
11. В окне « Пользовательский автофильтр » нажимаем « ОК ». Excel скроет строки документа, полностью состоящие из нулевых значений.
В программе Excel 2003 описанная процедура выглядит так:
1. Открываем документ.
2. Выделяем все столбцы документа (на рис. 6 — это колонки « A:F »).
3. Вызываем меню « Данные → Фильтр → Автофильтр ». В области заголовков таблицы появятся значки выбора.
4. Щелкаем на значке в поле « Пр ». Откроется меню, как на рис. 10.
5. Из этого меню выбираем вариант « (Условие…) ». Откроется окно « Пользовательский автофильтр », как на рис. 9.
6. Щелкаем на значке выпадающего списка с названием « Пр ».
7. Из предложенных вариантов выбираем « не равно » (рис. 10).
8. В область значения вводим « 0 », как показано на рис. 9.
9. В окне « Пользовательский автофильтр » нажимаем « ОК ». Строки с нулевыми значениями станут на экране не видны.
Преимущества работы с автофильтром: можно скрыть целые строки документа, где стоят только нулевые значения.
Недостаток применения автофильтра:
— нельзя выборочно скрывать нули в произвольной группе ячеек таблицы;
— автофильтр можно применить только к регулярным таблицам, которые являются базами данных в формате Excel . Использовать автофильтр с документами произвольной формы практически невозможно.
На сегодня все. Надеюсь, что материал этой статьи поможет вам быстро справиться с проблемой отображения нулевых значений в ваших документах. Файл с примерами из этой статьи вы можете скачать на сайте редакции. До новых встреч и удачной работы!
