Как скрыть нулевые метки данных в диаграмме в 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), чтобы отображаемые серии отражали скорректированные значения.

1. Щелкните правой кнопкой мыши любую существующую метку данных на диаграмме и выберите «Формат меток данных».

   

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

   

3. Отключите другие параметры меток, такие как «Значение».

Теперь 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 Sub

2. Вернитесь к вашему листу и активируйте диаграмму, где вы хотите скрыть нулевые метки данных (щелкнув один раз на границу диаграммы).

3. Вернитесь в редактор VBA и нажмите Выполнить (или нажмите F5) для выполнения макроса. Макрос будет проходить через все серии диаграммы и автоматически скрывать любую метку со значением ноль, оставляя другие метки данных нетронутыми.

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

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

Связанные статьи:

• Скрытие нулей на оси диаграммы в Excel