Excel нулевые значения не показывать на графике

Excel нулевые значения не показывать на графике

Excel работает за вас

Excel works!

Thanks for Visiting

Нулевые значения на графике

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

Как сделать такой график сплошным или понять какая динамика этого графика?

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

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

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

В нижней части окна выбора найдите кнопку Скрытые и пустые ячейки.

После нажатия откроется меню выбора варианта скрытия пустых и нулевых ячеек.

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

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

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

Отображать данные в скрытых ячейках

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

Показать динамику графика. Тренд графика

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

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

Линия тренда показывает какая динамика показателей преобладает (на понижение/повышение не меняется), даже если не нулевые значения на графике присутствуют. Рекомендую!

Пожалуйста пишите ваши комментарии и вопросы!

Иногда в ряде данных отсутствуют некоторые значения. Например, если данные собираются вручную, то можно элементарно забыть провести измерение. 🙂 Рассмотрим ситуацию на примере измерения температуры в Москве (измерения делаются через каждые 3 часа). Статистика за период 25 августа – 2 сентября 2011 представлена на рис. 1.

Рис. 1. Данные о температуре в Москве (по оси абсцисс указаны дата и время измерения)

Скачать заметку в формате Word, примеры в формате Excel

Предположим, что некоторые измерения не были сделаны (рис. 2).

Рис 2. Данные с пропусками

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

Рис. 3. Выбрать данные

В открывшемся окне «Выбор источника данных» нажмите «Скрытые и пустые ячейки» (рис. 4).

Рис. 4. Скрытые и пустые ячейки

В открывшемся окне «Настройка скрытых и пустых ячеек» (рис. 5) выберите одну из опций:

  • Пустые значения (по умолчанию); диаграмма примет вид, как на рис. 3.
  • Нулевые значения; вид диаграммы представлен на рис. 6; такая интерпретация пропущенных данных верна, если действительно пустое значение соответствует нулю; например, если график отражает продажи одного артикула, а дни, когда продаж не было, система не выводит вовсе, то, строя впоследствии график с отражением всех календарных (рабочих) дней, необходимо показать на графике нули в пропущенные дни.
  • Линию; вид диаграммы представлен на рис.7; такое отражение пропущенных данных верно, если значение не проставлено (не измерено) по ошибке, и следовало бы каким-либо образом интерпретировать пропущенные данные.
Читайте также:  Honor nova 3 обзор

Рис. 5. Выбор метода показа пропущенных данных

Рис. 6. Замена пропущенных данных на нулевые значения

Обратите внимание, что пропущенную точку Excel не ставит, а просто соединяет точки соседствующие с пропущенной.

Еще два слова об опции «Показывать данные в скрытых строках и столбцах» (рис. 5). По умолчанию эта опция выключена, и вот что произойдет, если мы скроем, например, часть данных за 27 августа (рис. 8).

Рис. 8. Скрытие части данных на листе приводит к тому, что они не отражаются на диаграмме

Если же опцию «Показывать данные в скрытых строках и столбцах» включить, то данные в листе будут скрыты, а на диаграмме показаны (рис. 9).

Рис. 9. Скрытые данные отражаются на диаграмме

Комментарии: 6 комментариев

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

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

Искренне благодарю Вас за оперативный ответ! Это то, что хотелось!

В этом курсе:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читайте также:  Huawei mobile драйвер на 4g модем

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читайте также:  Diablo 2 широкоформатное разрешение

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Ссылка на основную публикацию
Adblock detector