Як використовувати формули в діаграмах excel приклади

Діаграми дозволяють нам комфортно сприймати інформацію. Excel володіє широкими можливостями для створення діаграм і графіків. А якщо додати до діаграм формули, то тоді з'являється додаткова можливість для створення динамічних звітів і презентацій.

Розглянемо, як застосовувати формули і умовне форматування в діаграмах Excel.

Приклади формул в діаграмах

Побудуємо на основі ряду даних простий графік з маркерами:

Якщо клацнути по будь-якій точці графіка, то в рядку формул з'явиться функція РЯД. Саме з її допомогою генеруються ряди даних всіх діаграм. Ця функція застосовується тільки для визначення значень точок на графіках. Просто використовувати її на робочому аркуші неможливо.

Аргументи функції РЯД:

Аргументи функції РЯД можна знайти і змінити в діалоговому вікні «Вибрати дані»:

Виділимо елемент легенди «y» і клацнемо по кнопці змінити. В поле «Ім'я ряду» міститься аргумент функції «Ім'я»:

Назва ряду даних - «y». Його можна міняти.

В поле «Значення» - аргумент значень ряду даних.

Так як наш графік побудований на основі одного ряду даних, то порядок дорівнює одиниці. Даний аргумент відбивається в списку «Елементи легенди».

Аргументи функції РЯД допускають застосування іменованих діапазонів. Якщо скористатися цією можливістю, то можна створити динамічну діаграму, швидко перемикатися між даними одного ряду.

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

Створення динамічних діаграм

Відкриваємо вкладку «Формули» - натискаємо кнопку «Диспетчер імен».

У діалоговому вікні тиснемо «Створити». Відкриється вікно «Створення імені». В поле «Ім'я» вводимо ім'я діапазону. В поле «Діапазон» - формулу для посилання на дані в першому стовпці (= зміщений (Лист1! $ A $ 1; 1; 0; СЧЁТЗ (Лист1! $ A $ 1: $ A $ 20) -1; 1)).

Щоб заголовок ряду даних не включався в іменований діапазон, за аргументами функції СЧЕТЗ ставимо «-1». Як діапазону можна вказувати весь стовпець А - Excel швидко визначає порожні клітинки. У прикладі ми поставили лише перші 20 осередків.

Створюємо іменований діапазон для другого стовпця. За таким же принципом.

Тепер поміняємо посилання на ряд даних в графіку іменами динамічних діапазонів. Викликаємо діалогове вікно «Вибір джерела даних». Виділяємо елемент легенди і натискаємо «Змінити». Міняємо посилання в поле «Значення» на ім'я діапазону.

Далі тиснемо «Змінити підписи горизонтальній осі». Задаємо для діапазону призначеної ім'я.

Графік залишається колишнім. Але якщо ми додамо в наявну таблицю нові дані, вони тут же потраплять на діаграму.

При роботі з величезним масивом даних іноді потрібно створити діаграму тільки на основі певної кількості останніх значень в ряду. Щоб формула вибирала тільки їх, при формуванні динамічного іменованого діапазону прописуємо наступне: = зміщений (Лист1! $ A $ 1; СЧЁТЗ (Лист1! $ A $ 1: $ A $ 1000) -40; 0; 40; 1). За таким же принципом - для стовпчика В.

Скільки б даних ми не додавали в вихідну таблицю, на графіку буде показано тільки останні 40 значень.

Умовне форматування в діаграмі

Даний інструмент досить просто використовується для звичайних даних. Для діаграм в Excel застосувати вбудоване умовне форматування неможливо. Потрібно йти іншим шляхом.

Навіщо це? Для поліпшення сприйняття інформації. При зміні значень у вихідних осередках автоматично буде змінюватися кольорове виконання діаграми.

Виконати умовне форматування в діаграмах можна за допомогою макросів і формул. Розглянемо другий спосіб.

На підставі тих самих вихідних даних складемо гистограмму:

Так виглядає діаграма без форматування. Потрібно зробити наступним чином: окремі стовпчики повинні зафарбовувати в певний колір залежно від значення.

Для умовного форматування потрібно формула, яка визначає відформатовані осередки.

Для кожного умови створимо окремий ряд даних. Значення в початковій таблиці знаходяться в діапазоні від 0,06 до 5,7. Створимо ряд для періодів 0-0,6; 0,6-1,6; 1,6-3; 3-4,6; 4,6-6.

Сформуємо дані для гістограми з умовним форматуванням. Діапазон умов внесемо в рядки 1 і 2. Заголовки - в рядок 3. Формули для заголовків:

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

Джерело даних для гістограми - стовпці А і В. Потрібно виключити колонку В і додати новостворений діапазон З: F.

Тепер стовпчики діаграми пофарбовані в різні кольори в залежності від значення.