MS 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 . Использовать автофильтр с документами произвольной формы практически невозможно.
На сегодня все. Надеюсь, что материал этой статьи поможет вам быстро справиться с проблемой отображения нулевых значений в ваших документах. Файл с примерами из этой статьи вы можете скачать на сайте редакции. До новых встреч и удачной работы!