Содержание
- Как настроить Excel 2007 и Excel 2010 для игнорирования пустых ячеек при создании диаграммы или графика
- Метод 1: настройка того, как Excel обрабатывает скрытые и пустые ячейки
- Метод 2: используйте формулу для преобразования пустых ячеек в # N / A
- Заключение
Робби в основном пишет о Skyrim, но иногда проливает свет на странности приложений Microsoft, таких как Excel и Outlook.
Как настроить Excel 2007 и Excel 2010 для игнорирования пустых ячеек при создании диаграммы или графика
Это проблема, с которой я столкнулся сам, когда я хотел создать диаграмму, когда большинство ячеек в диапазоне, который вы хотите построить, являются пустыми (содержат пустые ячейки).
Чтобы решить эту проблему и создать диаграмму, которая правильно отображает данные, у вас может возникнуть соблазн, как я делал в прошлом, скопировать значения данных в новый диапазон, чтобы данные были непрерывными (без пробелов). Это может занять очень много времени и очень раздражать, особенно если объем данных велик. Предполагается, что Excel упростит нашу коллективную жизнь, поэтому я решил найти решение этой проблемы, предполагающее как можно меньше дополнительной ручной работы.
В моем случае мне было интересно составить график средненедельного количества ежедневных продаж, которые совершала моя команда. Когда я создал диаграмму, выбрав диапазон данных, все, что у меня было, это диаграмма без данных.
Мы рассмотрим два метода решения этой проблемы:
- Как изменить способ работы диаграммы с пустыми или пустыми ячейками
- Как использовать формулу для изменения содержимого пустых ячеек на # Н / Д (Excel автоматически игнорирует ячейки с # Н / Д при создании диаграммы)
На рисунке ниже вы можете увидеть:
- Данные, которые я использую для создания диаграмм (слева)
- Пустая начальная таблица (вверху справа)
- Заполненная диаграмма (внизу справа)
Метод 1: настройка того, как Excel обрабатывает скрытые и пустые ячейки
Первый метод, который мы собираемся использовать для создания диаграммы, которая правильно отображает данные, - это изменить способ обработки в Excel 2007 и Excel 2010 пустых ячеек на графиках.
По умолчанию на диаграмме, когда Excel видит пустую ячейку, создается пробел. Если у вас есть диапазон данных с большим количеством пустых ячеек, чем ячеек с данными, это означает, что Excel эффективно создает график, полный пробелов.
Для начала нам нужно создать график средних продаж за неделю.
- Мы создаем линейный график, выбирая данные в B3: B31 и D3: D31 (нажмите и удерживайте Ctrl клавиша для выбора несмежных столбцов)
- Щелкните значок Вставлять вкладку и выберите Линия кнопка в Диаграммы группа и выберите Линия
Теперь, когда у нас есть график, нам нужно изменить способ обработки пустых ячеек в Excel. Сделать это:
- Щелкните правой кнопкой мыши диаграмму и выберите Выбрать данные
- Нажмите Скрытые и пустые ячейки
- Пробелы это настройка по умолчанию, как мы обсуждали ранее
- Нуль в нашем примере данные будут отображаться как значение, ноль, ноль, ноль, ноль, ноль, ноль, значение
- Соедините точки данных линией делает именно то, что сказано, соединяет точки данных вместе
На рисунке ниже показан наш график, созданный с использованием Нуль (слева) и Соедините точки данных линией (верно). Пробелы мы уже видели с пустой диаграммой выше.
- Я также привел в порядок горизонтальную ось. щелчок правой кнопкой мыши это и выбрав Ось формата
- Под Параметры оси вкладку, я изменил Основная единица к Фиксированные 7 дней
- Я изменил Ось положения к На отметках чтобы он показывал полную дату
Метод 2: используйте формулу для преобразования пустых ячеек в # N / A
Второй способ убедиться, что данные диаграмм Excel содержат большое количество пробелов или пустых ячеек правильно, - это использовать формулу. Для этого мы воспользуемся тем фактом, что, когда Excel видит # N / A в ячейке, он не включает ее в диаграмму. Формула, которую мы будем использовать в E3:
= ЕСЛИ (D3 = "", # N / A, D3)
В ЕСЛИ функция просматривает содержимое ячейки D3 и ЕСЛИ D3 равно «» (другими словами, пустая ячейка), тогда он изменяет текущую ячейку на # N / A. Если Excel что-то обнаружит в ячейке, он просто скопирует ячейку. Например, он скопирует содержимое D3 в E3.
У меня есть статья, в которой обсуждается ЕСЛИ функции более подробно. Я также рассказываю об использовании ЕСЛИ ОШИБКА для подавления ожидаемых ошибок и использования ЕСЛИ с логическими функциями И, ИЛИ а также НЕТ. Это можно найти здесь:
- Затем я копирую формулу в столбец E.
- Затем я создаю диаграмму точно так же, как мы делали выше, используя даты в столбце B и результаты формул в столбце E.
Как видно из рисунка выше, диаграмма правильно отображает данные, даже если Excel использует Показать пустые ячейки в виде Пробелы (настройка по умолчанию для всех диаграмм, созданных в Excel 2007 и Excel 2010).
Затем мы приводили в порядок диаграмму, как делали выше,
- Убираем горизонтальную ось
- Удаление Легенда
- Добавление Заголовок (щелкните значок Макет на вкладке с выбранным графиком выберите Заголовок диаграммы кнопку и выберите нужный тип заголовка)
Заключение
Часто данные поступают к вам из электронных таблиц, которые вы не контролируете, и не всегда отформатированы именно так, как вам хотелось бы. Вместо того, чтобы повторно редактировать данные, чтобы они соответствовали тому, что вы хотите, всегда намного лучше и быстрее, чтобы Excel работал с любыми проблемами форматирования, которые могут иметь данные, и давать желаемые результаты без значительного ручного редактирования с вашей стороны.
В этой статье мы рассмотрели проблему, с которой я часто сталкиваюсь:
Как создавать диаграммы с использованием данных, содержащих большое количество пустых ячеек. Мы рассмотрели два метода решения этой проблемы:
- Изменение способа обработки пустых ячеек в самой диаграмме в Excel
- С использованием ЕСЛИ операторы для изменения пустых ячеек на # N / A (Excel 2007 и Excel 2010 игнорируют ячейки, содержащие # N / A, при создании диаграмм)
С использованием ЕСЛИ операторы в формуле более гибкие, чем изменение способа Excel работать с пустыми ячейками в диаграмме, поскольку вы можете попросить его изменить что-либо на # N / A, а не только пустые ячейки. Это означает, что если вы получаете электронные таблицы с нежелательными данными или данными, которые вам не нужны, вы также можете заставить Excel игнорировать их при создании диаграммы на основе данных.
Я очень надеюсь, что вы нашли эту статью полезной и информативной. Пожалуйста, не стесняйтесь оставлять любые комментарии, которые у вас могут быть ниже, и большое спасибо за чтение!
Эта статья точна и правдива, насколько известно автору. Контент предназначен только для информационных или развлекательных целей и не заменяет личного или профессионального совета по деловым, финансовым, юридическим или техническим вопросам.
