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

7 октября 2023 г.

Примечание редактора: в видео Брэндон Вильяроло рассказывает вам о нескольких способах подавления нулевых значений в диаграммах Excel. Для этой демонстрации он использует Microsoft Office 365. Действия аналогичны тем, которые описывает Сьюзен Харкинс в следующем руководстве.

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

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

Посетите Майкрософт

Перейти к:

Следуя за Исследование образца набора данных Удаление и форматирование нуля Диаграмма отфильтрованного набора данных Замените нули на NA() Выбор между настройками диаграммы и нулевыми значениями диаграммы Как исключить ноль в метках данных? Заключительные советы

Следуя за

Для этой демонстрации я использую Microsoft 365 Desktop в 64-разрядной системе Windows 11, но вы также можете использовать более ранние версии Excel. Excel в Интернете поддерживает большинство этих методов.

СМОТРИТЕ: Google Workspace и Microsoft 365: параллельный анализ с контрольным списком (TechRepublic Premium)

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

Изучение образца набора данных

На рисунке A показаны данные и исходные диаграммы, которые мы будем обновлять в этой статье. Круговая и однолинейная диаграммы отражают данные в столбце B для поставщика 1. Две другие диаграммы содержат три ряда данных: поставщик 1, поставщик 2 и поставщик 3. Столбец «Минимум» возвращает минимальное значение для каждого месяца, то есть апрель, май. и июль возвращают ноль для минимального значения. Эта настройка упрощает все примеры, которые мы будем рассматривать в этом руководстве.

Рисунок А

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

Круговая диаграмма

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

Рисунок Б

По умолчанию на круговой диаграмме отображается ноль.

Линейный график

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

Рисунок С

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

Гистограмма с накоплением

Excel отображает четыре стопки для месяцев без нулевого значения на гистограмме с накоплением, показанной на рисунке D. Месяцы с нулевым значением отображают только два значения, поскольку столбец «Минимум» также возвращает ноль для этих месяцев, поэтому на диаграмме фактически отображаются два нуля для каждый месяц. Читатели могут быть немного сбиты с толку тем, что они видят.

Рисунок D

На составной гистограмме отображаются нулевые значения.

Многолинейный график

Эта многолинейная диаграмма на рисунке E беспорядочна; увеличение не улучшает его читабельность. Хотя вы не можете увидеть все линии, они есть. Значения настолько близки, что некоторые линии закрывают другие, что вводит в заблуждение.

Рисунок Е

Нулевые значения на многолинейной диаграмме могут добавить хаоса.

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

Удаление и форматирование нуля

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

Ручное удаление нуля

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

Круговая диаграмма

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

Рисунок F

Удаление нулевых значений не поможет круговой диаграмме.

Гистограмма с накоплением

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

Рисунок G

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

Линейные и многолинейные диаграммы

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

Рисунок Н

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

Многолинейный график обманчив. Кажется, что серия Vendor 1 неправильная, но если щелкнуть по ней, вы увидите маркеры (рис. I). Оно есть, но скрыто другими линиями; даже удвоение его размера не улучшает его читабельность.

Рисунок I

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

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

Снятие флажка с параметров отображения рабочего листа

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

1. Откройте вкладку «Файл» и выберите «Параметры». Возможно, сначала вам придется нажать «Еще».

2. Выберите «Дополнительно» на левой панели.

3. В разделе «Параметры отображения для этого листа» выберите нужный лист в раскрывающемся меню. (Это свойство уровня листа.)

4. Снимите флажок «Показывать ноль в ячейках, имеющих нулевое значение», как показано на рисунке J.

Рисунок J

Эта опция ничего не меняет.

5. Нажмите ОК.

Нулевые значения все еще существуют — вы можете увидеть их в строке формул. Однако Excel не отобразит их; таким образом, этот метод не имеет никакого эффекта. Диаграммы рассматривают нулевые значения так, как будто они все еще здесь, потому что они есть. Excel в Интернете не разрешает доступ к этому параметру.

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

Установка пользовательского формата

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

1. Выберите диапазон данных B2:D9.

2. Нажмите кнопку запуска диалогового окна группы «Номер» (вкладка «Главная»).

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

4. В поле «Тип» введите 0,0;;; (Рисунок K) и нажмите «ОК».

Рисунок К

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

Вы заметите, что результаты аналогичны тем, которые вы видели ранее:

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

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

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

ПОСМОТРЕТЬ: Вот как вводить ведущие нули в Excel

Диаграмма отфильтрованного набора данных

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

Давайте продемонстрируем. Начните с добавления фильтра в столбец «Поставщик 1», выполнив следующие действия:

1. Щелкните внутри диапазона данных.

2. На вкладке Данные нажмите Фильтр в группе Сортировка и фильтр. Если вы работаете с объектом «Таблица», вы можете пропустить этот шаг, поскольку фильтры уже есть.

3. Щелкните раскрывающийся список «Поставщик 1» и снимите флажок с нуля (рис. L).

Рисунок L

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

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

На рисунке M показана новая круговая диаграмма.

Рисунок М

Этот хак с фильтрацией — простое одноразовое исправление круговой диаграммы.

На рисунке N показан новый линейный график.

Рисунок N

Линейный график не является хорошим кандидатом для этого решения.

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

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

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

Замените нули на NA()

Наиболее надежным решением проблемы сокрытия нулей является замена буквальных нулевых значений функцией NA() с использованием функции Excel «Найти и заменить». Если вы регулярно обновляете данные, вы можете даже с самого начала ввести NA() для нулей, что полностью устранит проблему. Чтобы сделать это вручную, введите =NA(). Однако это не всегда практично, поэтому давайте воспользуемся функцией Excel «Замена», чтобы заменить нулевые значения в примере набора данных функцией NA():

1. Выберите набор данных. В данном случае это B2:D9.

2. Нажмите «Найти и выбрать» в группе «Редактирование» на вкладке «Главная», а затем выберите «Заменить» в раскрывающемся списке или нажмите Ctrl + H.

3. Введите 0 в поле «Найти что».

4. Введите =NA() в элементе управления «Заменить».

5. При необходимости нажмите «Параметры», чтобы отобразить дополнительные настройки.

6. Установите флажок «Сопоставить все содержимое ячейки», как показано на рисунке O.

Figure O

Обязательно проверьте эту опцию.

7. Нажмите «Заменить все», и Excel заменит нулевые значения.

8. Нажмите OK, чтобы закрыть сообщение с подтверждением.

9. Нажмите Закрыть.

На рисунке O выше показаны настройки и результаты. Если вы не выберете параметр «Сопоставить все содержимое ячейки» на шестом шаге, Excel изменит значения 40, 404 и т. д. Формулы в столбце E отображают значение ошибки, поскольку они ссылаются на ячейку, в которой отображается сообщение об ошибке.

Ни на одной из диаграмм не отображаются значения ошибок #Н/Д, но они по-прежнему отображают метку категории на оси и легенду. Столбчатая диаграмма с накоплением отображает только два ряда для месяцев, имеющих значение #Н/Д, поэтому разницы здесь нет. Единственное любопытство — это многолинейная диаграмма, показанная на рисунке P: нулевые значения, которые теперь являются значениями ошибки #Н/Д, хорошо видны.

Рисунок П

Многолинейный график показывает явные разрывы.

Предположим, вы работаете с результатами формул, которые могут возвращать ноль вместо значения ошибки, как показано на рисунке O. В этом случае вы можете использовать функцию ЕСЛИ() для возврата ошибки #Н/Д, используя следующий синтаксис:

=ЕСЛИ(формула=0,НП(),формула)

Функция MIN() возвращает минимальное значение за каждый месяц. Функция ЕСЛИ() возвращает #Н/Д, если результат равен нулю (рис. Q):

=ЕСЛИ(МИН(B2:D2)=0,NA(),МИН(B2:D2))

Рисунок Q

Используйте выражение IF(), чтобы вернуть значение ошибки NA, если формула не делает этого сама.

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

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

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

1. Выберите диаграмму.

2. Перейдите на вкладку контекстного дизайна диаграммы.

3. В группе Данные нажмите Выбрать данные.

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

Рисунок Р

Измените нулевое поведение для одной диаграммы.

5. Выберите один из вариантов (рис. S).

Рисунок S

Выберите опцию.

6. Дважды нажмите OK, чтобы вернуться к диаграмме.

Как исключить ноль в метках данных?

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

Если на диаграмме не отображается ноль или метка данных, но отображается ряд в легенде, вы можете удалить его. Просто выберите этот элемент в легенде и нажмите «Удалить». Если вы случайно удалили их все, нажмите Ctrl + Z, чтобы отменить удаление, и повторите попытку, убедившись, что выбрали только ту метку, которую хотите удалить.

Заключительные советы

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

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

Читать далее: Изучите некоторые из лучших бесплатных альтернатив Microsoft Excel.

---

Оригинал