Якісна візуалізація великого обсягу інформації - це майже завжди нетривіальне завдання, тому відображення всіх даних часто призводить до перевантаженості діаграми, її заплутаності і, в підсумку, до неправильного сприйняття і висновків.
Ось, наприклад, дані по курсах валют за кілька місяців:
Будувати графік по всій таблиці, як легко збагнути, не найкраща ідея. Красивим рішенням у подібній ситуації може стати створення інтерактивної діаграми, яку користувач може сам підлаштовувати під себе і ситуацію. А саме:
Виглядати це може приблизно так:
Подобається? Тоді поїхали ...
У більшості випадків для реалізації інтерактивності діаграми застосовується простий, але потужний прийом - діаграма будується не за вихідною, а по окремій, спеціально створеної таблиці з формулами, яка відображає тільки потрібні дані. У нашому випадку, в цю додаткову таблицю будуть переноситися вихідні дані тільки по тим валютам, які користувач вибрав за допомогою прапорців:
В 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; B4; # Н /Д)
Зверніть увагу на часткове закріплення посилання на жовту клітинку (F$1), тому вона повинна зміщатися вправо, але не повинна - вниз, при копіюванні формули на весь діапазон.
Тепер при включенні-виключенні прапорців наша додаткова таблиця заповнюється або даними з вихідної таблиці, або штучно створеної помилкою # Н /Д, яка не дає лінії на графіку.
Тепер додамо на аркуш Excel смуги прокрутки, за допомогою яких користувач зможе легко зрушувати графік по осі часу і змінювати масштаб його збільшення.
Смугу прокрутки ( Scroll bar) беремо там же, де і прапорці - на панелі інструментів Форми ( Forms) або на вкладці Розробник (Developer):
Малюємо на аркуші в будь-якому відповідному місці одну за одною дві смуги - для зсуву за часом і масштабу:
Кожну смугу прокрутки треба пов'язати зі своєю коміркою (синя і зелена комірки на малюнку), куди буде виводитися числове значення положення повзунка. Його ми потім будемо використовувати для визначення масштабу і зсуву. Для цього клацніть правою кнопкою миші по намальованій смузі і виберіть в контекстному меню команду Формат об'єкта ( Format control) . У діалоговому вікні можна задати пов'язану комірку і мінімум-максимум, в будь-яких межах де буде гуляти повзунок:
Таким чином, після виконання всього вищевикладеного, у вас повинно бути дві смуги прокрутки, при переміщенні повзунків за якими значення в пов'язаних комірках повинні змінюватися в інтервалі від 1 до 307.
Щоб відображати на графіку дані тільки за певний інтервал часу, створимо іменований діапазон, який буде посилатися тільки на потрібні комірки в додатковій таблиці. Цей діапазон буде характеризуватися двома параметрами:
Цей іменований діапазон ми пізніше будемо використовувати як вихідні дані для побудови діаграми.
Для створення такого діапазону будемо використовувати функцію OFFSET ( OFFSET) з категорії Посилання та масиви ( Lookup and Reference) - ця функція вміє створювати посилання на діапазон заданого розміру в заданому місці аркуша і має такі аргументи:
В якості точки відліку береться якась стартова комірка, потім задається зсув щодо неї на задану кількість рядків вниз і стовпців праворуч. Останні два аргументи цієї функції - висота і ширина потрібного нам діапазону. Так, наприклад, якщо б ми хотіли мати посилання на діапазон даних з курсами за 5 днів, починаючи з 4 січня, то можна було б використовувати нашу функцію OFFSET з наступними аргументами:
=OFFSET(A3;4;1;5;2)
Хитрість у тому, що константи в цій формулі можна замінити на посилання на комірки з перемінним вмістом - в нашому випадку, на синю і зелену комірки. Зробити це можна, створивши динамічний іменований діапазон з функцією OFFSET ( OFFSET) . Для цього:
Для створення нового іменованого діапазону потрібно натиснути кнопку Створити ( Create) та ввести ім'я діапазону та посилання на комірки у вікні,.
Спочатку створимо два простих статичних іменованих діапазону з іменами, наприклад, Shift та Z oom , які будуть посилатися на синю і зелену комірки відповідно:
Тепер трохи складніше - створимо діапазон з ім'ям Euros , який буде посилатися за допомогою функції OFFSET ( OFFSET) на дані з курсів євро за вибраний відрізок часу, використовуючи щойно створені до цього діапазони Shift і Zoom і комірку E3 в якості точки відліку:
Зверніть увагу, що перед ім'ям діапазону використовується ім'я поточного аркуша - це звужує коло дії іменованого діапазону, тобто робить його доступним в межах поточного аркуша, а не всієї книги. Це необхідно нам для побудови діаграми в майбутньому. У нових версіях Excel для створення локального імені аркуша можна використовувати випадаючий список Область.
Аналогічним чином створюється іменований діапазон Dollars для даних по курсу долара:
І завершує картину діапазон Labels , який вказує на підписи до осі Х, тобто дати для обраного відрізку:
Загальна картина повинна бути приблизно наступною:
Виділимо кілька рядків у верхній частині допоміжної таблиці, наприклад діапазон E3: G10 і побудуємо по ньому діаграму типу Графік ( Line) . Для цього в Excel 2007/2010 потрібно перейти на вкладку Вставка ( Insert) і в групі Діаграма ( Chart) вибрати тип Графік ( Line) , а в більш старших версіях вибрати в меню Вставка - діаграма ( Insert - Chart) . Якщо виділити одну з ліній на створеній діаграмі, то в рядку формул буде видна функція РЯД ( SERIES) , що обслуговує виділений ряд даних:
Ця функція задає діапазони даних і підписів для виділеного ряду діаграми. Наше завдання - підмінити статичні діапазони в її аргументах на динамічні, створені нами раніше. Це можна зробити прямо в рядку формул, змінивши
= РЯД (Ліст1!$F$3; Лист1! $E$4:$E$10 ; Лист1! $F$4:$F$10 ; 1)
На
= РЯД (Ліст1!$F$3; Лист1! Labels ; Лист1! Euros ; 1)
Виконавши цю процедуру послідовно для рядів даних долара і євро, ми отримаємо те, чого прагнули - діаграма буде будуватися за динамічними діапазонами Dollars і Euros, а підписи до осі Х будуть братися з динамічного ж діапазону Labels. При зміні положення повзунків будуть змінюватися діапазони і, як наслідок, діаграма. При включенні-виключенні прапорців - відображатися тільки ті валюти, які нам потрібні.
Таким чином ми маємо повністю інтерактивну діаграму, де можемо відобразити саме той фрагмент даних, що нам потрібен для аналізу.