Отображение и скрытие нулевых значений. Не показывать 0 в excel

БЕСПЛАТНО ответим на Ваши вопросы
По лишению прав, ДТП, страховом возмещении, выезде на встречную полосу и пр. Ежедневно с 9.00 до 21.00
Москва и МО +7 (499) 938-51-97
С-Петербург и ЛО +7 (812) 467-32-86
Бесплатный звонок по России 8-800-350-23-69 доб.418

Как в Excel скрыть отображение нулей

В программе Microsoft Office Excel некоторая выводимая в ячейках информация – это итог вычисления определенных формул. Такой результат может равняться нулю и его отображение становится не нужным, особенно в тех случаях, когда в соседних ячейках результат этих формул выведен в виде текста. Избавиться от ненужных нулей можно несколькими методами. Для этого вам понадобятся:

— Персональный компьютер;

— Программа МО Excel(закажите лицензионную версию в нашем интернет-магазине).

Инструкция

  1. Для того, чтобы на рабочем листе Excel перестали отображаться нули, нужно воспользоваться специальными настройками данной программы. Если у вас установлена программа Excel 2010 – в меню программы выберите пункт «Файл», в открывшемся меню нажмите команду «Параметры». В программе Excel 2007 нужно нажать круглую иконку Office, далее найти и кликнуть по строке «Параметры Excel».

    «Параметры Excel» в Excel 2007

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

    после выполнения этих действий нажмите ОК.

  3. Также можно исключить нули не со всего листа, а с определенных ячеек. Обозначьте нужные ячейки и откройте контекстное меню, щелкнув по выделенному правой клавишей мыши. Нажмите команду «Формат ячеек» Активирование команды «Формат ячеек»

    и в левой графе, загрузившегося окна, нажмите по пункту «Все форматы».

  4. В появившемся окне, под словом «Тип» пропишите следующую команду: «0;-0;;@» (кавычки опустите). Введение кода числового формата

    В том случае, если значения в выделенных ячейках отображаются с точностью до некоторого количества знаков после запятой – во вписанной ранее команде, допишите после каждого нуля, через запятую, столько нулей, сколько должно отображаться в ячейке. Например, если запись в ячейке отображается с точностью до тысячных, то команда должна быть следующего вида: «0,000;-0,000;;@». Нажмите кнопку ОК.

  5. Еще один способ скрыть отображение нулей – это сделать их цвет аналогичный фону страницы. Для выполнения этой задачи необходимо в категории опций «Стили» выбрать кнопку «Условное форматирование», предварительно обозначив определенные ячейки. Откройте раскрывающийся перечень «Правила выделения ячеек» и щелкните команду «Равно».
  6. В открывшемся меню, в левую строку впишите «0» (без кавычек), а напротив укажите «Пользовательский формат». Загрузится новое окно, откройте вкладку «Шрифт» выберете цвет, соответствующий цвету фона вашего документа. Нажмите кнопку ОК.

Видео: Трюк Excel 7. Скрыть нулевые значения в ячейках Excel

besthard.ru

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

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

Как известно, в Excel довольно часто могут появляться нулевые значения. Чаще всего – это результат работы той или иной формулы. В общем-то в нуле как таковом нет ничего плохого. Глядя на него становится понятно, что это результат каких-то вычислений. Однако, потребность отображать нулевые значения (0) возникает не всегда. Требуют ли стандарты форматирования или собственные предпочтения отображать или скрывать нулевые значения, имеется несколько способов реализации всех этих требований.

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

В столбце С содержится формула, которая вычисляет сумму (количество, умноженное на цену).

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

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

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

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

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

Нажмите ОК.

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

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

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

Откройте окно «Формат ячеек» для чего воспользуйтесь одним из перечисленных ниже способов:

  • Вызовите контекстное меню (правая кнопка мыши) из выделенного – «Формат ячеек…»
  • На вкладке «Главная» найдите раздел «Число» — «Другие числовые форматы…»

  • На вкладке «Главная» найдите раздел «Ячейки» — «Формат»

В любом случае, в появившемся окне откройте вкладку «Число», где в разделе «Числовые форматы» выберите «(все форматы)»

В строке «Тип» вместо предлагаемого выберите или впишите вручную следующий шаблон:

Нажмите ОК.

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

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

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

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

На вкладке «Главная» нажимаем кнопку «Условное форматирование».

В выпадающем списке выбираем «Правила выделенных ячеек…» — «Равно…»

В появившемся окне в первой строке указываем значение, которому должна соответствовать ячейка. В нашем случае – это «0». В строке условий выбираем «Пользовательский формат…»

 

В появившемся окне «Формат ячеек» на вкладке «Шрифт» в выпадающем списке «Цвет» выбираем белый цвет и нажимаем ОК

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

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

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

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

=ЕСЛИ(D12*E12=0;"";D12*E12)

=ЕСЛИ(D12*E12=0;"";D12*E12)

Как видно из данной формулы мы сначала перемножаем значения, затем сравниваем полученный результат с 0. И если результат будет действительно равен нулю, то вместо него выводим пустую строку («»). Иначе перемножаем ячейки и выводим результат.

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

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

designcenter.org.ua

Как скрыть нули и ошибки в Excel

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

Как скрыть ошибки в Excel

Чтобы продемонстрировать как автоматически скрыть нули и ошибки в Excel, для наглядности возьмем таблицу в качестве примера, в котором необходимо скрыть нежелательные значения в столбце D.

Чтобы скрыть ОШИБКУ в ячейке:

  1. Выделите диапазон ячеек D2:D8, а потом используйте инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило».
  2. В разделе данного окна «Выберите тип правила:» воспользуйтесь опцией «Форматировать только ячейки, которые содержат:».
  3. В разделе «Измените описание правила:» из левого выпадающего списка выберите опцию «Ошибки».
  4. Нажимаем на кнопку «Формат», переходим на вкладку «Шрифт» и в разделе «Цвет:» указываем белый. На всех окнах «ОК».

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



Как скрыть нули в Excel

Пример автоматического скрытия нежелательных нулей в ячейках таблицы:

  1. Выделите диапазон ячеек D2:D8 и выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Правила выделения ячеек»-«Равно».
  2. В левом поле «Форматировать ячейки, которые РАВНЫ:» вводим значение 0.
  3. В правом выпадающем списке выбираем опцию «Пользовательский формат», переходим на вкладку «Шрифт» и в разделе «Цвет:» указываем белый.

Документ не содержит ошибочных значений и ненужных нулей. Ошибки и нули скрыты документ готов к печати.

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

exceltable.com

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

Примечание:  Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке) .

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

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

Сделайте следующее:

  1. В меню Excel выберите пункт Параметры.

  2. В разделе Разработка выберите представление   .

  3. В разделе Параметры окна снимите флажок Показывать нулевые значения.

Внимание: Этот вариант применим только к нулевым (равным 0) значениям в ячейках. Если нулевое значение в одной из выбранных ячеек изменится на ненулевое, Excel будет использовать для его отображения общий числовой формат.

Выполните следующие действия:

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

  2. На вкладке Главная в разделе номер на Формат номера во всплывающем меню нажмите кнопку другой.

  3. В диалоговом окне Формат ячеек в разделе Категория выберите пункт Другой.

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

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

Сделайте следующее:

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

  2. На вкладке Главная в поле номер на Формат номера во всплывающем меню щелкните пользовательский.

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

Задача

Действие

Отображение скрытых значений с использованием стандартного числового формата

Выберите Общий.

Отображение скрытых значений с использованием формата даты или времени

Щелкните Дата или Время, а затем выберите требуемый формат даты или времени.

Сделайте следующее:

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

  2. На вкладке " Главная " в Формат нажмите кнопку Условное форматирование.

  3. Выберите команду Создать правило.

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

  5. Во всплывающем меню Заданный текст выберите пункт Значение ячейки.

  6. Во всплывающем меню между выберите значение равно, а затем в поле рядом с оператором равно введите значение 0.

  7. Во всплывающем меню Форматировать с помощью выберите пункт пользовательский формат, а затем во всплывающем меню Цвет выберите пункт Белый.

Функция IF условие как пустую ячейку или дефис (-) в ячейке отображается нулевое значение (0). Подробнее об этом читайте в статье функции Если .

Сделайте следующее:

  1. На пустом листе в ячейках A1 и A2 введите значение 10.

  2. В ячейке A3 введите =A1-A2 и нажмите клавишу RETURN.

    Формула вернет нулевое значение.

  3. На вкладке формулы в функции нажмите кнопку Построитель формул.

  4. В списке построителя формул дважды щелкните функцию ЕСЛИ.

  5. В разделе Аргументы щелкните поле рядом с надписью значение1 и введите A3.

  6. В разделе Аргументы щелкните слово Истина рядом с надписью точно, а затем во всплывающем меню выберите оператор = (равно).

  7. В разделе Аргументы щелкните поле рядом с надписью значение2 и введите 0.

  8. В разделе Аргументы щелкните поле рядом с надписью то и выполните одно из следующих действий:

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

Вводимые символы

В виде пустых ячеек

""

В виде тире

"-"

Установите флажок рядом с еще и введите Считат A3, p нажмитеклавишу ВВОД.

Если формула в ячейке A3 возвращает нулевое значение, Excel отображает значение, указанное в кавычках в качестве аргумента то (также называемого аргументом значение_если_истина).

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

Сделайте следующее:

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

  2. На вкладке Сводная таблица в разделе Данные нажмите кнопку Параметры.

  3. На вкладке Отображение выполните одно или несколько из следующих действий:

Задача

Необходимые действия

Отображение определенного значения вместо кода ошибки

Установите флажок значения ошибок как, а затем в поле введите значение, которое будет отображаться вместо ошибки с кодом.

Чтобы ошибки отображались как пустые ячейки, оставьте поле пустым.

Отображение определенного значения вместо пустой ячейки

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

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

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

У вас есть вопрос об определенной функции?

Задать вопрос на форуме сообщества, посвященного Excel

Помогите нам улучшить Excel

У вас есть предложения по улучшению следующей версии Excel? Если да, ознакомьтесь с темами на форуме Excel User Voice.

См. также

support.office.com

Как сделать данные в Excel невидимыми

08.11.2012 Григорий Цапко Полезные советы

В некоторых случаях необходимо сделать данные, в каких либо ячейках невидимыми, не удаляя их. Например, промежуточные результаты вычислений, нулевые значения, значение параметра ИСТИНА или ЛОЖЬ и т.д.

Для этого можно воспользоваться, как минимум, двумя способами:

Во-первых, самое простое, установить формат шрифта в ячейке такой же, как и цвет фона, то есть, в самом распространенном случае – покрасить в белый цвет.

Во-вторых, можно изменить тип данных, сделав их невидимыми. Для этого выделяем необходимый для скрытия диапазон ячеек с данными, кликаем правой кнопкой мыши и в появившемся контекстном меню выбираем Формат ячеек → вкладка Число → в поле Числовые форматы: выбираем (все форматы). Справа в поле Тип: устанавливаем три знака точки с запятой «;;;» (без кавычек) и нажимаем ОК.

В приведенном ниже примере показаны два типа данных: «Основной» — видимый, и «;;;» — невидимый, а также сумма чисел, одинаковая в обоих диапазонах.

Для увеличения нажмите на рисунке

Чтобы сделать данные в ячейках видимыми, нужно проделать аналогичную операцию. Снова выделить необходимые ячейки и установить Тип: Основной, или просто  удалить «;;;» из этого поля.

Эти способы применимы в основном к тем ячейкам, данные в которых должны быть скрыты постоянно. Однако, такое случается не всегда.

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

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

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

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

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

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

Для увеличения нажмите на рисунке

 Как видим, второй вариант воспринимается гораздо легче.

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

Во-первых, мы можем использовать формулу на основе функции ЕСЛИ.

Так, например, если у нас, изначально, величина выручки в ячейке В40 получается, как произведение количества проданного товара из ячейки В5 на цену товара из ячейки В25 (В40=В5*В25), то формула для скрытия нулевого значения выручки в ячейке В40 будет выглядеть следующим образом:

=ЕСЛИ(В5=0;””;В5*В25)

В том случае, если в этом периоде не было продаж и значение В5=0, возможное нулевое значение выручки скрывается при помощи двух кавычек.

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

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

В Excel2010 это можно сделать следующим образом:

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

На вкладке Главная → в группе Стили → выбираем команду Условное форматирование → в раскрывающемся списке выбираем Правила выделения ячеек → Равно…

 

Для увеличения нажмите на рисунке

 Далее выполняем следующую последовательность действий:

В поле Форматировать ячейки, которые РАВНЫ: устанавливаем значение 0 (1). В Раскрывающемся списке (2) выбираем Пользовательский формат… и в открывшемся диалоговом окне Формат ячеек на вкладке Шрифт в раскрывающемся списке Цвет: (3) меняем Цвет темы «Авто» на «Белый, Фон 1» (4). Нажимаем ОК (5) и ОК (6).

 

Для увеличения нажмите на рисунке

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

Чтобы вернуть исходное форматирование, удаляем созданное правило из выделенных ячеек: Вкладка Главная  → группа Стили → команда Условное форматирование → Удалить правила → Удалить правила из выделенных ячеек.

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

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

 

Для увеличения нажмите на рисунке

 

excel-training.ru

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 могут работать неправильно.

buhgalter.com.ua

Скрытие значений и индикаторов ошибок в ячейках

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

Формулы могут возвращать ошибки по многим причинам. Например, формула =1/0 возвращает ошибку #ДЕЛ/0!, поскольку деление на 0 недопустимо. Предусмотрены следующие значения ошибок: #ДЕЛ/0!, #Н/Д, #ИМЯ?, #ПУСТО!, #ЧИСЛО!, #ССЫЛКА! и #ЗНАЧ!.

Преобразование ошибки в нулевое значение и использование формата для скрытия значения

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

Создание образца ошибки

  1. Откройте чистый лист или создайте новый.

  2. Введите 3 в ячейку B1, 0 — в ячейку C1 и формулу =B1/C1 — в ячейку A1.В ячейке A1 отобразится значение ошибки #ДЕЛ/0!.

  3. Выделите ячейку A1 и нажмите клавишу F2, чтобы изменить формулу.

  4. После знака равенства (=) введите ЕСЛИОШИБКА с открывающей круглой скобкой:ЕСЛИОШИБКА(

  5. Переместите курсор в конец формулы.

  6. Введите ,0), т. е. запятую с нулем и закрывающей круглой скобкой. Формула =B1/C1 примет вид =ЕСЛИОШИБКА(B1/C1,0).

  7. Нажмите клавишу ВВОД, чтобы завершить редактирование формулы. Теперь в ячейке вместо ошибки #ДЕЛ/0! должно отображаться значение 0.

Применение условного формата

  1. Выделите ячейку с ошибкой и на вкладке Главная нажмите кнопку Условное форматирование.

  2. Выберите команду Создать правило.

  3. В диалоговом окне Создание правила форматирования выберите параметр Форматировать только ячейки, которые содержат.

  4. Убедитесь, что в разделе Форматировать только ячейки, для которых выполняется следующее условие в первом списке выбран пункт Значение ячейки, а во втором — равно. Затем в текстовом поле справа введите значение 0.

  5. Нажмите кнопку Формат.

  6. На вкладке Число в списке Числовые форматы выберите пункт (все форматы).

  7. В поле Тип введите ;;; (три точки с запятой) и нажмите кнопку ОК. Нажмите кнопку ОК еще раз. Значение 0 в ячейке исчезнет. Это связано с тем, что пользовательский формат ;;; предписывает скрывать любые числа в ячейке. Однако фактическое значение (0) по-прежнему хранится в ячейке.

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

  1. Выделите диапазон ячеек, содержащих значение ошибки.

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

  3. Выберите команду Создать правило. Откроется диалоговое окно Создание правила форматирования.

  4. В списке Выберите тип правила выберите пункт Форматировать только ячейки, которые содержат.

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

  6. Нажмите кнопку Формат и откройте вкладку Шрифт.

  7. Щелкните стрелку, чтобы открыть список Цвет, и в разделе Цвета темы выберите белый цвет.

Возможны ситуации, когда необходимо, чтобы вместо значений ошибок в ячейках отображалась текстовая строка, например "#Н/Д", прочерк или "НД". Сделать это можно с помощью функций ЕСЛИОШИБКА и НД, как показано в примере ниже.

Описание функции

ЕСЛИОШИБКА   . С помощью этой функции можно определить, содержит ли ячейка или возвращает ли формула ошибку.

НД    Эта функция возвращает в ячейке строку "#Н/Д". Синтаксис функции: =НД().

  1. Щелкните отчет сводной таблицы. Появится вкладка Работа со сводными таблицами.

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

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

  3. Откройте вкладку Разметка и формат и выполните одно или оба указанных ниже действия.

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

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

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

Ячейка с ошибкой в формуле

  1. В Excel 2016, Excel 2013 и Excel 2010: выберите Файл > Параметры >Формулы.

    В Excel 2007 нажмите кнопку Microsoft Office  и выберите пункты Параметры Excel > Формулы.

  2. В разделе Контроль ошибок снимите флажок Включить фоновый поиск ошибок.

См. также

Функция ЕСЛИОШИБКА

Функция НД

support.office.com



О сайте

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