Якісна візуалізація великого обсягу інформації - це майже завжди нетривіальне завдання, тому відображення всіх даних часто призводить до перевантаженості діаграми, її заплутаності і, в підсумку, до неправильного сприйняття і висновків.
Ось, наприклад, дані по курсах валют за кілька місяців:
Будувати графік по всій таблиці, як легко збагнути, не найкраща ідея. Красивим рішенням у подібній ситуації може стати створення інтерактивної діаграми, яку користувач може сам підлаштовувати під себе і ситуацію. А саме:
Виглядати це може приблизно так:
Подобається? Тоді поїхали ...
У більшості випадків для реалізації інтерактивності діаграми застосовується простий, але потужний прийом - діаграма будується не за вихідною, а по окремій, спеціально створеної таблиці з формулами, яка відображає тільки потрібні дані. У нашому випадку, в цю додаткову таблицю будуть переноситися вихідні дані тільки по тим валютам, які користувач вибрав за допомогою прапорців:
В Excel 2007/2010 до створених діапазонам можна застосувати команду Форматувати як таблицю (Format as Table) з вкладки Головна (Home):
Це дасть нам наступні переваги:
В Excel 2007/2010 для цього необхідно відобразити вкладку Розробник (Developer), а в Excel 2003 і більш старших версіях - панель інструментів Форми (Forms). Для цього:
На що з'явилася панелі інструментів або вкладці Розробник (Developer) в списку, що розкривається Вставити (Insert) вибираємо інструмент Прапорець (Checkbox) і малюємо два прапорці-галочки для включення-виключення кожної з валют:
Текст прапорців можна поміняти, клацнувши по ним правою кнопкою миші і вибравши команду Змінити текст (Edit text).
Тепер прив'яжемо наші прапорці до будь комірок для визначення того, чи включений прапорець чи ні (у нашому прикладі це дві жовті комірки у верхній частині додаткової таблиці). Для цього клацніть правою кнопкою миші по черзі по кожному доданому прапорцю і виберіть команду Формат об'єкта (Format Control), а потім у вікні задайте Зв'язок з коміркою (Cell link).
Наша мета в тому, щоб кожен прапорець був прив'язаний до відповідної жовтої клітинки над колонкою з валютою. При включенні прапорця в пов'язану комірку буде виводитися ІСТИНА (TRUE), при вимиканні - БРЕХНЯ (FALSE). Це дозволить, надалі, перевіряти за допомогою формул пов'язані комірки і виводити в додаткову таблицю або значення курсу з вихідної таблиці для побудови графіка, або #Н/Д (#N/A), щоб графік не будувався.
Тепер заповнимо додаткову таблицю формулою, яка буде транслювати вихідні дані з основної таблиці, якщо відповідний прапорець валюти включений і пов'язана комірка містить слово ІСТИНА (TRUE):
Зауважте, що при використанні команди Форматувати як таблицю (Format as Table) на першому кроці, формула має використовує ім'я таблиці і назву колонки. У разі звичайного діапазону, формула буде більш звичного вигляду:
Зверніть увагу на часткове закріплення посилання на жовту клітинку (F$1), тому вона повинна зміщатися вправо, але не повинна - вниз, при копіюванні формули на весь діапазон.
Тепер при включенні-виключенні прапорців наша додаткова таблиця заповнюється або даними з вихідної таблиці, або штучно створеної помилкою # Н /Д, яка не дає лінії на графіку.
Тепер додамо на аркуш Excel смуги прокрутки, за допомогою яких користувач зможе легко зрушувати графік по осі часу і змінювати масштаб його збільшення.
Смугу прокрутки (Scroll bar) беремо там же, де і прапорці - на панелі інструментів Форми (Forms) або на вкладці Розробник (Developer):
Малюємо на аркуші в будь-якому відповідному місці одну за одною дві смуги - для зсуву за часом і масштабу:
Кожну смугу прокрутки треба пов'язати зі своєю коміркою (синя і зелена комірки на малюнку), куди буде виводитися числове значення положення повзунка. Його ми потім будемо використовувати для визначення масштабу і зсуву. Для цього клацніть правою кнопкою миші по намальованій смузі і виберіть в контекстному меню команду Формат об'єкта (Format control). У діалоговому вікні можна задати пов'язану комірку і мінімум-максимум, в будь-яких межах де буде гуляти повзунок:
Таким чином, після виконання всього вищевикладеного, у вас повинно бути дві смуги прокрутки, при переміщенні повзунків за якими значення в пов'язаних комірках повинні змінюватися в інтервалі від 1 до 307.
Щоб відображати на графіку дані тільки за певний інтервал часу, створимо іменований діапазон, який буде посилатися тільки на потрібні комірки в додатковій таблиці. Цей діапазон буде характеризуватися двома параметрами:
Цей іменований діапазон ми пізніше будемо використовувати як вихідні дані для побудови діаграми.
Для створення такого діапазону будемо використовувати функцію
В якості точки відліку береться якась стартова комірка, потім задається зсув щодо неї на задану кількість рядків вниз і стовпців праворуч. Останні два аргументи цієї функції - висота і ширина потрібного нам діапазону. Так, наприклад, якщо б ми хотіли мати посилання на діапазон даних з курсами за 5 днів, починаючи з 4 січня, то можна було б використовувати нашу функцію
Хитрість у тому, що константи в цій формулі можна замінити на посилання на комірки з перемінним вмістом - в нашому випадку, на синю і зелену комірки. Зробити це можна, створивши динамічний іменований діапазон з функцією
Для створення нового іменованого діапазону потрібно натиснути кнопку Створити (Create) та ввести ім'я діапазону та посилання на комірки у вікні,.
Спочатку створимо два простих статичних іменованих діапазону з іменами, наприклад,
Тепер трохи складніше - створимо діапазон з ім'ям
Зверніть увагу, що перед ім'ям діапазону використовується ім'я поточного аркуша - це звужує коло дії іменованого діапазону, тобто робить його доступним в межах поточного аркуша, а не всієї книги. Це необхідно нам для побудови діаграми в майбутньому. У нових версіях Excel для створення локального імені аркуша можна використовувати випадаючий список Область.
Аналогічним чином створюється іменований діапазон
І завершує картину діапазон
Загальна картина повинна бути приблизно наступною:
Виділимо кілька рядків у верхній частині допоміжної таблиці, наприклад діапазон E3:G10 і побудуємо по ньому діаграму типу Графік (Line). Для цього в Excel 2007/2010 потрібно перейти на вкладку Вставка (Insert) і в групі Діаграма (Chart) вибрати тип Графік (Line), а в більш старших версіях вибрати в меню Вставка - діаграма (Insert - Chart). Якщо виділити одну з ліній на створеній діаграмі, то в рядку формул буде видна функція
Ця функція задає діапазони даних і підписів для виділеного ряду діаграми. Наше завдання - підмінити статичні діапазони в її аргументах на динамічні, створені нами раніше. Це можна зробити прямо в рядку формул, змінивши:
на:
Виконавши цю процедуру послідовно для рядів даних долара і євро, ми отримаємо те, чого прагнули - діаграма буде будуватися за динамічними діапазонами
Таким чином ми маємо повністю інтерактивну діаграму, де можемо відобразити саме той фрагмент даних, що нам потрібен для аналізу.