Как скрыть нулевые метки данных в диаграмме в 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), чтобы отображаемые серии отражали скорректированные значения.
- Щелкните правой кнопкой мыши любую существующую метку данных на диаграмме и выберите «Формат меток данных».
- В разделе Опции меток выберите «Значение из ячеек». Появится диалоговое окно, выберите диапазон вашей вспомогательной колонки и нажмите OK.
- Отключите другие параметры меток, такие как «Значение».
Теперь 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-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек