Качественная визуализация большого объема информации – это почти всегда нетривиальная задача, поэтому отображение всех данных часто приводит к перегруженности диаграммы, ее запутанности и, в итоге, к неправильному восприятию и выводам.
Вот, например, данные по курсам валют за несколько месяцев:
Строить график по всей таблице, как легко понять, не самая лучшая идея. Прекрасным решением в схожей ситуации может стать создание интерактивной диаграммы, которую юзер может сам подстраивать под себя и ситуацию. А именно:
Выглядеть это может примерно так:
Нравится? Тогда уехали...
В большинстве случаев для реализации интерактивности диаграммы применяется простой, но мощный прием – диаграмма строится не по исходной, а по отдельной, специально созданной таблице с формулами, отображающей только нужные данные. В нашем случае, в эту дополнительную таблицу будут переноситься исходные данные только по тем валютам, которые пользователь выбрал с помощью флажков:
В 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) . Если выделить одну из линий на созданной диаграмме, то в строке формул будет видна функция
Эта функция задает диапазоны данных и подписей выделенного ряда диаграммы. Наша задача – подменить статические диапазоны в ее аргументах на динамические, созданные нами раньше. Это можно сделать прямо в строке формул, изменив:
на:
Выполнив эту процедуру последовательно для рядов данных доллара и евро, мы получим то, к чему стремились – диаграмма будет строиться по динамическим диапазонам.
Таким образом, мы имеем полностью интерактивную диаграмму, где можем отразить именно тот фрагмент данных, который нам нужен для анализа.