Умовне форматування в діаграмі excel, exceltip
На аркуші Excel умовне форматування досить легко реалізувати. Дана вбудована можливість знаходиться на вкладці Основне стрічки Excel. Умовне форматування для діаграм - це зовсім інша історія.
Люди досить часто запитують мене, як застосувати умовне форматування до діаграм, тобто щоб при зміні значень в осередках, змінювалися кольори стовпців (маркерів, точок ...) діаграми. Існує два підходи до вирішення проблеми. Перший, за допомогою макросу, який відстежує зміни на аркуші і вносить необхідні корективи в окремі елементи діаграми. Другий працює без використання макросів, і сьогодні ми його розглянемо.
Діаграма без форматування
Нижче наведено простий приклад даних для побудови діаграми з умовним форматуванням ...
... які побудують просту неотформатированним гистограмму ...
... або просту лінійчату діаграму
Нам необхідно, щоб в залежності від значень ряду даних, окремі елементи діаграми зафарбовувати в певний колір.
За винятком деяких простих вбудованих форматів, умовне форматування робочого листа вимагає наявність формули для визначення того, які клітини будуть відформатовані. Таким же чином ми поступимо для визначення форматування в діаграмах.
Ми замінимо оригінальний графік лінії або гістограми декількома рядами даних, по одному для кожного услвиях. Так як наші дані знаходяться в діапазоні від 0 до 5,07, ми створимо ряд для діапазонів 0-0,5; 0,5-1,5; 1,5-3; 3-4,5 і 4,5-6.
Діаграма з умовним форматуванням
Нижче показані дані для діаграми з умовним форматуванням. Діапазон умов форматування знаходиться в рядках 1 і 2, формули для заголовка знаходяться в діапазоні C3: G3. Наприклад, формула, яка перебуває в осередку С3, виглядає наступним чином:
Формула для осередку С4:
Дана формула відображає значення колонки B, якщо воно лежить в діапазоні від 4,5 до 6, в іншому випадку, повертається порожня клітинка. Діапазон C4: G13 заповнений цією формулою.
Під час виділення діаграми, ми побачимо джерело даних для графіка
Нам необхідно змінити джерело даних, прибравши колонку B і додавши колонки C: G. Це робиться просто, шляхом перетягування і зміни розмірів виділеної області.
Тепер наша діаграма має 5 наборів кольорових стовпців, по одному кольору на кожен ряд даних. Це не зовсім вірно, але так як у нас кластерний тип діаграми, ми бачимо певний ряд даних з 4-ма порожніми значеннями.
Ситуацію легко виправити, призначивши 100% -ве перекриття одного з стовпців. Це дозволить перекрити порожній стовпчик видимим.
Лінійчата діаграма з умовним форматуванням
Відмінністю побудови лінійчатої діаграми від гістограми буде формула, яка визначає потрапляння значення по осі Y в діапазон умов і повертає помилку # Н / Д, якщо умова не дотримується. Формула в діапазоні C4: G13 буде виглядати наступним чином:
Нам необхідно буде розширити джерело даних, залишивши при цьому колонку B, як лінію з'єднує всі точки, і додавши колонки C: G, як окремо відформатовані ряди даних.
Тепер діаграма складається з маркерів і відрізків п'яти кольорів, по одному на кожен ряд даних.
Трохи підправимо формат нашої діаграми. Видалимо маркери оригінального ряду даних (сірий), видалимо кольорові відрізки і визначимо інші маркери для додаткових рядів даних. Також видалимо непотрібні елементи легенди (для сірого графіка), для цього необхідно клацнути по легенді, потім по зайвому елементу і натиснути клавішу Delete.
Гнучкість умовного форматування
Цей простий приклад форматування визначається формулами, заснований на значеннях шкали Y. Немає ніяких складнощів у визначенні форматування на основі значень за шкалою X або Y, або навіть значень, що знаходяться в іншому стовпці, які навіть не відображені. Як і при роботі з традиційним умовним форматуванням, обмеженням стане тільки ваша можливість будувати формули.