Зміст
- Як налаштувати Excel 2007 та Excel 2010 на ігнорування порожніх комірок під час створення діаграми чи графіку
- Спосіб 1: Налаштуйте, як Excel обробляє приховані та порожні клітини
- Спосіб 2: Використовуйте формулу для перетворення порожніх клітинок у # N / A
- Підведенню
Роббі здебільшого пише про Skyrim, але також іноді проливає світло на дивацтва таких програм Microsoft, як Excel та Outlook.
Як налаштувати Excel 2007 та Excel 2010 на ігнорування порожніх комірок під час створення діаграми чи графіку
Це проблема, з якою я стикався сам, де я хотів створити діаграму, коли більшість комірок у діапазоні, які ви хочете скласти, є порожніми (містить порожні клітинки).
Для того, щоб вирішити цю проблему і створити діаграму, яка правильно відображає дані, у вас може виникнути спокуса, як і раніше, скопіювати значення даних у новий діапазон, щоб дані були суміжними (без прогалин). Це може зайняти дуже багато часу і дуже дратувати, особливо якщо обсяг даних великий. Excel повинен полегшити наше колективне життя, тому я вирішив шукати рішення цієї проблеми, яке передбачало б якомога менше додаткової ручної роботи.
У моєму випадку мені було цікаво скласти графік середньотижневих показників кількості щоденних продажів, які здійснила моя команда. Коли я створив діаграму, вибравши діапазон даних, я отримав лише діаграму без даних.
Ми розглянемо два способи вирішення цієї проблеми:
- Як змінити спосіб роботи діаграми з порожніми або порожніми клітинками
- Як використовувати формулу для зміни вмісту порожніх комірок на # N / A (Excel автоматично ігнорує комірки з # N / A під час створення діаграми)
На малюнку нижче ви можете побачити:
- Дані, які я використовую для створення діаграм (зліва)
- Пуста початкова діаграма (угорі праворуч)
- Заповнений графік (праворуч унизу)
Спосіб 1: Налаштуйте, як Excel обробляє приховані та порожні клітини
Перший метод, який ми будемо використовувати для створення діаграми, яка правильно відображає дані, - це змінити спосіб, як Excel 2007 та Excel 2010 обробляють порожні клітинки на графіках.
За замовчуванням на діаграмі, коли Excel бачить порожню комірку, він створює пробіл. Коли у вас діапазон даних із більшою кількістю порожніх комірок, ніж комірки з даними, це означає, що Excel ефективно створює графік, заповнений прогалинами.
Для початку нам потрібно створити діаграму середньотижневих продажів.
- Ми створюємо лінійний графік, вибираючи дані в B3: B31 і D3: D31 (клацніть і утримуйте Ctrl клавіша для вибору стовпців, які не сусідні)
- Клацніть на Вставити і виберіть Лінія кнопку в Діаграми групу та виберіть Лінія
Тепер, коли ми створили графік, нам потрібно змінити спосіб, як Excel обробляє порожні клітинки. Зробити це:
- Клацніть правою кнопкою миші діаграму та виберіть Виберіть Дані
- Клацніть Приховані та порожні клітини
- Прогалини - це налаштування за замовчуванням, як ми обговорювали раніше
- Нуль у нашому прикладі дані будуть позначені як значення, нуль, нуль, нуль, нуль, нуль, нуль, значення
- З'єднайте точки даних лінією робить саме так, як сказано, з'єднує точки даних між собою
На малюнку нижче показаний наш графік, створений за допомогою Нуль (ліворуч) та З'єднайте точки даних лінією (праворуч). Прогалини ми вже бачили з порожньою діаграмою вище.
- Я також впорядкував горизонтальну вісь клацання правою кнопкою миші це і виділення Формат осі
- Під Параметри осі вкладку, я змінив Основна одиниця до Виправлено 7 днів
- Я змінив Позиційна вісь до На позначках так, щоб він показував повну дату
Спосіб 2: Використовуйте формулу для перетворення порожніх клітинок у # N / A
Другим методом забезпечення правильної діаграми даних Excel, що містять велику кількість прогалин або порожніх комірок, є використання формули. Для цього ми скористаємось тим фактом, що коли Excel бачить у клітинці # N / A, він не включає його в діаграму. Формула, яку ми будемо використовувати в Е3:
= IF (D3 = "", # N / A, D3)
ЯКЩО функція розглядає вміст комірки D3 та ЯКЩО D3 дорівнює “” (іншими словами порожня комірка), тоді він змінює поточну комірку на # N / A. Якщо Excel знайде щось у комірці, він просто скопіює комірку. Наприклад, він скопіює вміст D3 в E3.
У мене є стаття, яка обговорює ЯКЩО функція більш детально. Я також охоплюю використання IFERROR для придушення очікуваних помилок та використання ЯКЩО з логічними функціями І, АБО і НЕ. Його можна знайти тут:
- Далі я копіюю формулу в стовпець E.
- Потім я створюю діаграму точно так, як це було зроблено вище, використовуючи дати у стовпці B та результати формул у стовпці E
Як видно з малюнка вище, діаграма правильно відображає дані, навіть якщо Excel використовує Показати порожні клітинки як Прогалини (налаштування за замовчуванням для всіх діаграм, створених у програмах Excel 2007 та Excel 2010).
Потім ми привели в порядок діаграму, як це було зроблено вище,
- Приведення в порядок горизонтальної осі
- Видалення Легенда
- Додавання a Заголовок (клацніть на Макет на вибраній графіці виберіть Назва діаграми і виберіть потрібний тип заголовка)
Підведенню
Часто дані надходять до вас із електронних таблиць, над якими ви не маєте контролю, і вони не завжди відформатовані саме так, як ви хотіли б. Замість того, щоб повторно редагувати дані відповідно до того, що ви хочете зробити, завжди набагато краще і швидше, щоб Excel вирішив будь-які проблеми форматування даних і дав результати, які ви хочете, без істотного ручного редагування з вашого боку.
У цій статті ми розглянули проблему, з якою я часто стикаюся:
Як створити діаграми, використовуючи дані, що містять велику кількість порожніх комірок. Ми розглянули два способи вирішення цієї проблеми:
- Зміна способу обробки Excel з порожніми клітинками на самій діаграмі
- Використовуючи ЯКЩО оператори для зміни порожніх комірок на # N / A (Excel 2007 та Excel 2010 ігнорують комірки, що містять # N / A під час створення діаграм)
Використовуючи ЯКЩО твердження у формулі є більш гнучким, ніж зміна способу роботи Excel з порожніми клітинками на діаграмі, оскільки ви можете попросити його змінити будь-що на # N / A, а не лише порожні клітинки. Це означає, що якщо ви отримуєте електронні таблиці з небажаними даними або даними, які ви не хочете, ви також можете отримати Excel, щоб ігнорувати його для цілей створення діаграми з даних.
Я сподіваюся, що ця стаття виявилася Вам корисною та інформативною. Будь ласка, не соромтеся залишати будь-які коментарі, які ви можете мати, нижче та велика подяка за прочитане!
Ця стаття є точною та вірною, наскільки відомо автору. Вміст призначений лише для інформаційних чи розважальних цілей і не замінює особистих порад чи професійних порад у ділових, фінансових, юридичних чи технічних питаннях.
