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

Обновлено: 05.07.2024

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

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

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

Чтобы решить эту проблему и создать диаграмму, отображающую данные правильно, у вас может возникнуть соблазн, как это делал я в прошлом, скопировать значения данных в новый диапазон, чтобы данные были непрерывными (без пробелов). Это может занять очень много времени и сильно раздражать, особенно если объем данных велик. Excel должен облегчить нашу коллективную жизнь, поэтому я решил найти решение этой проблемы, которое требовало бы как можно меньше дополнительной ручной работы.

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

Мы рассмотрим два способа решения этой проблемы:

На рисунке ниже вы можете видеть:

Данные, которые я использую для создания диаграмм (слева)
Пустой исходный график (вверху справа)
Заполненная диаграмма (внизу справа)

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

Способ 1. Настройка того, как Excel обрабатывает скрытые и пустые ячейки

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

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

Для начала нам нужно создать диаграмму средних недельных продаж.

Мы создаем линейный график, выбирая данные в B3:B31 и D3:D31 (нажмите и удерживайте клавишу Ctrl, чтобы выбрать несмежные столбцы)
Перейдите на вкладку "Вставка", нажмите кнопку "Линия" в группе "Диаграммы" и выберите "Линия".

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

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

Топ-8 лучших бесплатных приложений для аудиокниг, которыми должен пользоваться каждый

8 лучших альтернатив Adobe Photoshop (бесплатных и платных)

8 лучших альтернатив LastPass, которые стоит попробовать

Нажмите диаграмму правой кнопкой мыши и выберите "Выбрать данные".
Нажмите "Скрытые и пустые ячейки".

Настройка того, как Excel обрабатывает пробелы в данных при создании диаграмм в Excel 2007 и Excel 2010.

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

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

Диаграмма, созданная с использованием параметра «Ноль» (слева) и параметра «Соединить точки данных с линией» (справа) при настройке того, как Excel 2007 и Excel 2010 обрабатывает пустые ячейки.

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

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

Как видно из рисунка выше, диаграмма правильно отображает данные, даже несмотря на то, что Excel использует параметр "Показать пустые ячейки как пробелы" (настройка по умолчанию для всех диаграмм, созданных в Excel 2007 и Excel 2010).

Затем мы приведем в порядок диаграмму, как сделали выше,

Приведение в порядок горизонтальной оси
Удаление легенды
Добавление заголовка (щелкните вкладку «Макет» с выбранным графиком, нажмите кнопку «Заголовок диаграммы» и выберите тип заголовка, который вы предпочитаете)

Готовый график, созданный в Excel 2007 и Excel 2010, где Excel настроен на игнорирование пробелов в наших данных.

Подведение итогов

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

В этой статье мы рассмотрели проблему, с которой я часто сталкиваюсь:

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

Некоторые данные требуют специальной обработки в Tableau. В частности:

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

Индикатор специальных значений

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

Если вы не знаете, как обращаться со значениями, вы можете оставить индикатор специальных значений. Как правило, вы должны продолжать показывать индикатор, чтобы знать, что есть данные, которые не отображаются в представлении. Однако, чтобы скрыть индикатор, щелкните его правой кнопкой мыши (удерживая клавишу Control на Mac) и выберите «Скрыть индикатор» .

Нулевые числа и даты и отрицательные значения на логарифмических осях

Нулевое значение – это пустое поле, означающее отсутствующие или неизвестные значения.

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

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

Показать данные в положении по умолчанию — показать данные в положении по умолчанию на оси. Нулевые значения по-прежнему будут включены в расчеты. Положение по умолчанию зависит от типа данных. В таблице ниже указаны значения по умолчанию.

Скрытие пустых значений

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

Примечание. См. раздел "Логические функции" для получения информации о таких функциях, как ISNULL() и IFNULL(), которые можно использовать для обработки нулевых значений в вычисляемом поле.

Неизвестные географические местоположения

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

Редактировать местоположения — исправьте местоположения, сопоставив свои данные с известными местоположениями.

Фильтровать данные. Исключите неизвестные местоположения из представления с помощью фильтра. Адреса не будут включены в расчеты.

Показать данные в положении по умолчанию — показать значения в положении по умолчанию (0, 0) на карте.

Нулевые и отрицательные значения в древовидных картах

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

Использовать абсолютные значения: используйте абсолютное значение для определения размера соответствующей области в представлении. Например, оба значения 5 и -5 отображаются одинакового размера.

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

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

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

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

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

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

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

После того как вы нажмете ОК, все нулевые значения исчезнут (хотя вы всегда можете вернуть их обратно, используя тот же фильтр).

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

Как сделать так, чтобы пустые ячейки отображались как пробелы или нули

Фильтрация нулей, как показано выше, на самом деле делает то же самое, как если бы вы скрывали ячейки, содержащие нулевые значения.

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

Чтобы получить доступ к этим параметрам, выберите диаграмму и нажмите:

Инструменты для работы с диаграммами > Дизайн > Выбрать данные > Скрытые и пустые ячейки

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

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

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

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

Примечание. Отсутствующие значения можно заменить вычисляемым полем с помощью функции ZN(). Дополнительные сведения см. в разделе «Числовые функции».

Отсутствующие значения скрыты (по умолчанию)

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

Показаны пропущенные значения

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

Чтобы отобразить отсутствующие значения в диапазоне, щелкните правой кнопкой мыши (удерживая клавишу Control на Mac) дату или заголовки корзины и выберите Показать отсутствующие значения .

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

Показать и скрыть пустые строки и столбцы

Когда вы работаете с полями, которые не являются датами или числовыми интервалами, Tableau по умолчанию скрывает отсутствующие значения. Например, вы можете показывать рабочих и количество отработанных часов в определенном месяце. Если работник вообще не работал в этом месяце, в базе данных может не быть строки для этого работника. Когда вы перетаскиваете поле Worker на полку Rows, рабочие, которые не работали, по умолчанию скрываются. Вы можете отобразить пустые строки, выбрав «Анализ» > «Макет таблицы» > «Показать пустые строки». Аналогичным образом отобразите пустые столбцы, выбрав «Анализ» > «Макет таблицы» > «Показать пустые столбцы» .

Пустые строки скрыты (по умолчанию)

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

Показаны пустые строки

Несмотря на то, что Боб не работал в январе, он указан в списке, но отметки не выставлены.

Читайте также: