Как скрыть нулевые значения меток данных на диаграмме в Excel?
При создании диаграмм в Excel добавление меток данных помогает наглядно пояснить точки данных и предоставляет зрителям прямой доступ к их значениям. Однако если данные на диаграмме содержат нули, Excel зачастую отображает их в виде меток — это может вызвать путаницу или снизить визуальную привлекательность диаграммы. Во многих бизнес-, академических и отчётных сценариях предпочтительнее скрывать такие нулевые метки, чтобы на диаграмме отображались только действительно значимые значения.
К счастью, Excel предлагает несколько практичных способов скрыть нули в метках данных — каждый идеально подходит для своих задач и рабочих процессов. В этом руководстве вы найдёте популярные методы: от встроенного форматирования и формул до автоматизации с помощью VBA. Далее — пошаговые инструкции и полезные советы, которые помогут отображать на диаграммах только значимые данные.
- Скрыть нули Метки данных на диаграмме с помощью пользовательского числового формата
- Формула Excel — скрытие нулей с помощью формулы ЕСЛИ в Исходные данные
- Код VBA — автоматическое скрытие нулей Метки данных с помощью кода
Скрытие нулей Метки данных на диаграмме с помощью пользовательского числового формата
Если вы хотите скрыть нули в метках данных на диаграмме, не изменяя исходные данные, один из самых быстрых способов — применить к меткам данных пользовательский числовой формат. Этот метод особенно полезен, когда нужно сохранить исходные данные (включая нули), но при этом не отображать нулевые значения на диаграмме.
1. Сначала добавьте метки данных на диаграмму по мере необходимости. Затем щелкните правой кнопкой мыши любую из меток данных и выберите в контекстном меню Формат метки данных. См. снимок экрана:
2. В диалоговом окне Формат Метки данных слева выберите Число. Затем в списке Категория выберите Пользовательный. Введите пользовательский код формата #«» в поле Код формата и нажмите Добавить, чтобы сохранить его в список типов. См. снимок экрана:
Примечание: в Excel 2013 или более поздней версии после щелчка правой кнопкой мыши по любой метке данных и выбора Формат метки данных разверните раздел Число на панели форматирования, выберите Пользовательский, введите #«» в качестве кода формата и нажмите Добавить.
3. Нажмите Закрыть, чтобы закрыть диалоговое окно. Теперь на диаграмме будут скрыты все метки данных со значением ноль — останутся только ненулевые значения.
Совет: чтобы восстановить отображение нулей в метках данных, вернитесь в диалоговое окно Формат метки данных, выберите Число → Пользовательский и укажите стандартный числовой формат, например #,##0;-#,##0, из списка «Список типов».
Это решение особенно эффективно, когда требуется быстро визуально исправить отображение, и оно работает для большинства диаграмм на основе числовых данных (например, гистограмм, линейчатых, линейных и т. д.). Однако если ваши Исходный диапазон регулярно обновляются с помощью формул или меняющихся Нулевые значения, рекомендуется рассмотреть решения на основе формул или автоматизированные методы, описанные ниже.
Внимание: Пользовательское числовое форматирование визуально скрывает нули на диаграмме, но фактическое значение остаётся нулевым в фоновых данных и в исходных данных.
Формула Excel — скрытие нулей на диаграмме с помощью формулы ЕСЛИ в Исходные данные
Другой практичный способ предотвратить отображение нулей Метки данных на диаграмме Excel — изменить ваши Исходные данные с помощью формулы ЕСЛИ. Этот метод заменяет Нулевые значения в источнике диаграммы Диапазон данных на пустые ячейки, в результате чего механизм построения диаграмм Excel не отображает и не подписывает эти точки. Такой подход особенно полезен, когда диаграмма ссылается на динамический Диапазон данных или формулы, и вы хотите контролировать, какие данные отображаются, без дополнительных шагов форматирования.
Применимые сценарии: Используйте это решение, если вы можете управлять исходными данными (или создать вспомогательный столбец для диаграммы) и хотите полностью исключить нулевые значения из меток или самой серии диаграммы.
Преимущества: Простота и эффективность — гарантирует, что нули не отображаются ни в точках данных диаграммы, ни в её метках.
Недостатки: Требует либо изменения существующих данных, либо добавления вспомогательного столбца, если вы не хотите изменять исходный набор данных.
Чтобы реализовать это решение:
1. В новом вспомогательном столбце или в существующем диапазоне данных (например, если исходные значения находятся в столбце B, начиная с ячейки)B2), введите следующую формулу в соответствующую ячейку вспомогательного столбца (например, в ячейку C2):
=IF(A1=0,"",A1)Эта формула проверяет ячейку C2: если значение равно нулю, она возвращает пустую ячейку; в противном случае — исходное значение.
2. Нажмите Enter, чтобы подтвердить формулу, а затем при необходимости скопируйте её вниз по столбцу рядом с исходными данными: выделите ячейку с формулой и перетащите маркер заполнения или воспользуйтесь сочетаниями клавиш Ctrl+C / Ctrl+V.
3. Обновите диапазон данных диаграммы, чтобы она ссылалась на новый вспомогательный столбец (например, столбец)C), — тогда отображаемые серии будут отражать скорректированные значения.
- Щелкните правой кнопкой мыши по любой существующей метке данных на диаграмме и выберите «Формат метки данных».
- В разделе «Параметры меток» выберите «Значения из ячеек» — откроется диалоговое окно. Укажите диапазон вспомогательного столбца и нажмите «ОК».
- Снимите флажки с других параметров меток, например «Значение».
Теперь Excel не будет отображать Метки данных для Нулевые значения, поскольку ячейки в источнике диаграммы действительно пусты (а не содержат ноль). Напоминаем: убедитесь, что пустые ячейки не интерпретируются как нули в настройках диаграммы (например, для линейных диаграмм или Диаграмма рассеяния проверьте параметр «Настройки скрытых и пустых ячеек» через Выбрать данные → Скрытые и Пустые ячейки).
Напоминание об ошибках: Если в вашем столбце с формулами есть ошибки, например #ЗНАЧ!, такие точки могут быть пропущены или отображаться на диаграмме с метками ошибок — убедитесь, что ваша формула корректно работает для всех строк.
Код VBA — автоматическое скрытие нулей Метки данных на диаграмме
Для больших наборов данных, часто обновляемых диаграмм или регулярной отчетности использование VBA обеспечивает удобный и эффективный способ автоматически скрывать или удалять нули Метки данных с диаграммы Excel. Решение на основе VBA подходит, когда вы хотите автоматизировать процесс или обрабатывать сразу несколько диаграмм без ручного форматирования.
Применимые сценарии: Этот подход идеально подходит пользователям, знакомым с запуском макросов, а также для решения сложных и повторяющихся задач построения диаграмм в нескольких рабочих книгах Excel.
Преимущества: Автоматически скрывает нули в метках данных, экономя время и снижая риск ошибок при ручной настройке. Работает даже при изменении данных диаграммы или создании панелей мониторинга с частыми обновлениями.
Недостатки: Требует включения макросов и базового понимания процедур VBA. Изменения, внесённые с помощью VBA, возможно, придётся обновлять, если данные или серии диаграмм изменятся после выполнения макроса.
Как использовать это решение на основе VBA:
1. На вкладке Разработчик в Excel выберите Visual Basic, чтобы открыть редактор VBA. В окне VBA выберите Вставка > Модуль и вставьте следующий код в созданный модуль:
Sub HideZeroDataLabels()
'Updated by extendoffice 2025/7/11
Dim cht As Chart
Dim s As Series
Dim pt As Point
Dim xTitleId As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set cht = Application.ActiveChart
If cht Is Nothing Then
MsgBox "Please activate the chart from which you want to hide zero data labels.", vbExclamation, xTitleId
Exit Sub
End If
For Each s In cht.SeriesCollection
For Each pt In s.Points
If pt.HasDataLabel Then
If pt.DataLabel.Text = "0" Or pt.DataLabel.Text = "0%" Then
pt.DataLabel.Delete
End If
End If
Next pt
Next s
End Sub2. Вернитесь на лист и активируйте диаграмму, на которой нужно скрыть нулевые метки данных (щёлкнув один раз по её границе).
3. Вернитесь в редактор VBA и нажмите кнопку 
Выполнить(или нажмите клавишу)F5), чтобы запустить макрос. Макрос пройдёт по всем сериям диаграммы и автоматически скроет любые метки со значением «ноль», оставив остальные метки данных без изменений.
Практические советы: Если ваша диаграмма содержит более одного ряда данных, макрос обработает каждый из них по отдельности. А для удобного многократного использования вы можете назначить макрос пользовательской кнопке.
Напоминание об ошибке: Убедитесь, что вы включили макросы перед запуском кода и что диаграмма, которую требуется обработать, в данный момент активна — в противном случае макрос выведет предупреждение.
См. также:
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек