Интерактивная диаграмма
Качественная визуализация большого объема информации – это почти всегда нетривиальная задача, т.к. отображение всех данных часто приводит к перегруженности диаграммы, ее запутанности и, в итоге, к неправильному восприятию и выводам.
Вот, например, данные по курсам валют за несколько месяцев:
Строить график по всей таблице, как легко сообразить, не лучшая идея. Красивым решением в подобной ситуации может стать создание интерактивной диаграммы, которую пользователь может сам подстраивать под себя и ситуацию. А именно:
- двигаться по оси времени вперед-назад в будущее-прошлое
- приближать-удалять отдельные области диаграммы для подробного изучения деталей графика
- включать-выключать отображение отдельных валют на выбор
Выглядеть это может примерно так:
Нравится? Тогда поехали...
Шаг 1. Создаем дополнительную таблицу для диаграммы
В большинстве случаев для реализации интерактивности диаграммы применяется простой, но мощный прием – диаграмма строится не по исходной, а по отдельной, специально созданной таблице с формулами, которая отображает только нужные данные. В нашем случае, в эту дополнительную таблицу будут переноситься исходные данные только по тем валютам, которые пользователь выбрал с помощью флажков:
В Excel 2007/2010 к созданным диапазонам можно применить команду Форматировать как таблицу (Format as Table) с вкладки Главная (Home):
Это даст нам следующие преимущества:
- Любые формулы в таких таблицах автоматически транслируются на весь столбец – не надо «тянуть» их вручную до конца таблицы
- При дописывании к таблице новых строк в будущем (новых дат и курсов) – размеры таблицы увеличиваются автоматически, включая корректировку диапазонов в диаграммах, ссылках на эту таблицу в других формулах и т.д.
- Таблица быстро получает красивое форматирование (чересстрочную заливку и т.д.)
- Каждая таблица получает собственное имя (в нашем случае – Таблица1 и Таблица2), которое можно затем использовать в формулах.
Подробнее про преимущества использования подобных Таблиц можно почитать тут.
Шаг 2. Добавляем флажки (checkboxes) для валют
В Excel 2007/2010 для этого необходимо отобразить вкладку Разработчик (Developer), а в Excel 2003 и более старших версиях – панель инструментов Формы (Forms). Для этого:
- В Excel 2003: выберите в меню Вид – Панели инструментов – Формы (View – Toolbars – Forms)
- В Excel 2007: нажать кнопку Офис – Параметры Excel – Отобразить вкладку Разработчик на ленте (Office Button – Excel options – Show Developer Tab in the Ribbon)
- В Excel 2010: Файл – Параметры – Настройка ленты – включить флаг Разрабочик (File – Options – Customize Ribbon – Developer)
На появившейся панели инструментов или вкладке Разработчик (Developer) в раскрывающемся списке Вставить (Insert) выбираем инструмент Флажок (Checkbox) и рисуем два флажка-галочки для включения-выключения каждой из валют:
Текст флажков можно поменять, щелкнув по ним правой кнопкой мыши и выбрав команду Изменить текст (Edit text).
Теперь привяжем наши флажки к любым ячейкам для определения того, включен флажок или нет (в нашем примере это две желтых ячейки в верхней части дополнительной таблицы). Для этого щелкните правой кнопкой мыши по очереди по каждому добавленному флажку и выберите команду Формат объекта (Format Control), а затем в открывшемся окне задайте Связь с ячейкой (Cell link).
Наша цель в том, чтобы каждый флажок был привязан к соответствующей желтой ячейке над столбцом с валютой. При включении флажка в связанную ячейку будет выводиться ИСТИНА (TRUE), при выключении – ЛОЖЬ (FALSE). Это позволит, в дальнейшем, проверять с помощью формул связанные ячейки и выводить в дополнительную таблицу либо значение курса из исходной таблицы для построения графика, либо #Н/Д (#N/A), чтобы график не строился.
Шаг 3. Транслируем данные в дополнительную таблицу
Теперь заполним дополнительную таблицу формулой, которая будет транслировать исходные данные из основной таблицы, если соответствующий флажок валюты включен и связанная ячейка содержит слово ИСТИНА (TRUE):
Заметьте, что при использовании команды Форматировать как таблицу (Format as Table) на первом шаге, формула имеет использует имя таблицы и название колонки. В случае обычного диапазона, формула будет более привычного вида:
=ЕСЛИ(F$1;B4;#Н/Д)
Обратите внимание на частичное закрепление ссылки на желтую ячейку (F$1), т.к. она должна смещаться вправо, но не должна – вниз, при копировании формулы на весь диапазон.
Теперь при включении-выключении флажков наша дополнительная таблица заполняется либо данными из исходной таблицы, либо искусственно созданной ошибкой #Н/Д, которая не дает линии на графике.
Шаг 4. Создаем полосы прокрутки для оси времени и масштабирования
Теперь добавим на лист Excel полосы прокрутки, с помощью которых пользователь сможет легко сдвигать график по оси времени и менять масштаб его увеличения.
Полосу прокрутки (Scroll bar) берем там же, где и флажки – на панели инструментов Формы (Forms) или на вкладке Разработчик (Developer):
Рисуем на листе в любом подходящем месте одну за другой две полосы – для сдвига по времени и масштаба:
Каждую полосу прокрутки надо связать со своей ячейкой (синяя и зеленая ячейки на рисунке), куда будет выводиться числовое значение положения ползунка. Его мы потом будем использовать для определения масштаба и сдвига. Для этого щелкните правой кнопкой мыши по нарисованной полосе и выберите в контекстном меню команду Формат объекта (Format control). В открывшемся окне можно задать связанную ячейку и минимум-максимум, в пределах которых будет гулять ползунок:
Таким образом, после выполнения всего вышеизложенного, у вас должно быть две полосы прокрутки, при перемещении ползунков по которым значения в связанных ячейках должны меняться в интервале от 1 до 307.
Шаг 5. Создаем динамический именованный диапазон
Чтобы отображать на графике данные только за определенный интервал времени, создадим именованный диапазон, который будет ссылаться только на нужные ячейки в дополнительной таблице. Этот диапазон будет характеризоваться двумя параметрами:
- Отступом от начала таблицы вниз на заданное количество строк, т.е. отступом по временной шкале прошлое-будущее (синяя ячейка)
- Количеством ячеек по высоте, т.е. масштабом (зеленая ячейка)
Этот именованный диапазон мы позже будем использовать как исходные данные для построения диаграммы.
Для создания такого диапазона будем использовать функцию СМЕЩ (OFFSET) из категории Ссылки и массивы (Lookup and Reference) - эта функция умеет создавать ссылку на диапазон заданного размера в заданном месте листа и имеет следующие аргументы:
В качестве точки отсчета берется некая стартовая ячейка, затем задается смещение относительно нее на заданное количество строк вниз и столбцов вправо. Последние два аргумента этой функции – высота и ширина нужного нам диапазона. Так, например, если бы мы хотели иметь ссылку на диапазон данных с курсами за 5 дней, начиная с 4 января, то можно было бы использовать нашу функцию СМЕЩ со следующими аргументами:
=СМЕЩ(A3;4;1;5;2)
Хитрость в том, что константы в этой формуле можно заменить на ссылки на ячейки с переменным содержимым – в нашем случае, на синюю и зеленую ячейки. Сделать это можно, создав динамический именованный диапазон с функцией СМЕЩ (OFFSET). Для этого:
- В Excel 2007/2010 нажмите кнопку Диспетчер имен (Name Manager) на вкладке Формулы (Formulas)
- В Excel 2003 и старше – выберите в меню Вставка – Имя – Присвоить (Insert – Name – Define)
Для создания нового именованного диапазона нужно нажать кнопку Создать (Create) и ввести имя диапазона и ссылку на ячейки в открывшемся окне.
Сначала создадим два простых статических именованных диапазона с именами, например, Shift и Zoom, которые будут ссылаться на синюю и зеленую ячейки соответственно:
Теперь чуть сложнее – создадим диапазон с именем Euros, который будет ссылаться с помощью функции СМЕЩ (OFFSET) на данные по курсам евро за выбранный отрезок времени, используя только что созданные до этого диапазоны Shift и Zoom и ячейку E3 в качестве точки отсчета:
Обратите внимание, что перед именем диапазона используется имя текущего листа – это сужает круг действия именованного диапазона, т.е. делает его доступным в пределах текущего листа, а не всей книги. Это необходимо нам для построения диаграммы в будущем. В новых версиях Excel для создания локального имени листа можно использовать выпадающий список Область.
Аналогичным образом создается именованный диапазон Dollars для данных по курсу доллара:
И завершает картину диапазон Labels, указывающий на подписи к оси Х, т.е. даты для выбранного отрезка:
Общая получившаяся картина должна быть примерно следующей:
Шаг 6. Строим диаграмму
Выделим несколько строк в верхней части вспомогательной таблицы, например диапазон 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. При изменении положения ползунков будут меняться диапазоны и, как следствие, диаграмма. При включении-выключении флажков – отображаться только те валюты, которые нам нужны.
Таким образом мы имеем полностью интерактивную диаграмму, где можем отобразить именно тот фрагмент данных, что нам нужен для анализа.
Ссылки по теме
Заранее благодарю.
Короче, нужен "умный" динамический диапазон - который не дает слететь настройкам в Графике!!!
Я не стал реализовывать действия с галочками, мне важно приближение и выделение дат. При максимальном удалении и показывании всех дат диапазон меняется на обычную область ячеек и полосы перестают работать...
А как сделать диаграмму следующего вида: нужны три полосы прокрутки параметров (например, от 1 до 100), которые можно было бы перетягивать как в Вашем примере и два слолбика диаграммы, которые зависят от полос прокрутки. Как в фотошопе, там где можно каналы сдвигать или фиксировать один канал в каком-то положении, а результат кривой (в моем случае двух финальных показателей) зависит от положения всех трех ползунков. Надеюсь, понятно объяснил, что хочу получить.
Вот ссылка с рисунком того, что хотелось бы
Или просто пример построения Точки Безубыточности с разными кривыми при разных прибылях/выручках
А также график срока окупаемости проекта при разных прибылях/вырчуках
Думаю, всем это было бы очень интересно
Спасибо заранее.
Необходимо организовать Web-публикацию такой интерактивной диаграммы. Может подскажете как может быть реализована такая задача? Статическая диаграмма публикуется - это проще паренной репы. А вот чтоб кнопочки нажимались, чтоб диапазон и масштаб менялись...
Динамический диапазон Labels по такой же формуле, как в статье, но только уже со сдвигом относительно заголовка поля с подписями категорий в исходной таблице. Диапазон Dollars будет базироваться на такой формуле:
=СМЕЩ(Лист1!Labels;0;3-Ячейка с логическим значением доллар*1).
«Ячейка с лог. значением» в формуле, эта та, которая связана с соответствующим флажком, который отвечает за вывод или скрытие графика в долларах. Если в этой ячейке ИСТИНА, сдвиг по столбцам будет равен 2 (колонка с курсом доллара). Если флажок снят, мы получаем значение ЛОЖЬ, оно умножается на 1, дает ноль, и в итоге формула выносит диапазон на третий столбец, как раз тот, который у нас всегда заполнен ошибками. Аналогично диапазон Euros:
=СМЕЩ(Лист1!Labels;0;3-Ячейка с логическим значением евро*2),
тут тоже, или диапазон данных попадет в первую колонку со значениями и выдаст нам график по евро, либо попадет в колонку с ошибками и не выдаст нам график.
Не знаю как в других версиях, но в excel 2010 и наличие служебной колонки не нужно, достаточно пустого столбца. Если диапазон, на который ссылается ряд данных, пуст, на графике линий не создается. Если используется динамическая таблица, то вместо значения #Н/Д можно использовать =НД(), тогда и копировать ошибку не придется.
Dollars
=ЕСЛИ(Лист1!$G$1=ЛОЖЬ;{#Н/Д};СМЕЩ(Лист1!$A$3;Shift;2;Zoom;1))=ЕСЛИ(Лист1!$F$1=ЛОЖЬ;{#Н/Д};СМЕЩ(Лист1!$A$3;Shift;1;Zoom;1))Я сделал свой график с 5 отобранными акциями.
С наступающим!
Как сделать эффект разрыва листа, с оборванными краями, как на первой картинке в статье?
Тогда при изменении формата ячеек с исходными данными будет автоматом меняться шкала оси.
Не подскажите в чем может быть причина следующего:
в общем все сделал как у вас описано выше и все работает, за одним маленьким исключением, я создавал не для двух валют диаграмму, а для трех. С самого начала планировал для трех валют все, в итоге доллар и евро работают, а на курс рубля не получается. Когда делаю последнее действие
=РЯД(Лист1!$F$3;Лист1!$E$4:$E$10;Лист1!$F$4:$F$10;1)
на
=РЯД(Лист1!$F$3;Лист1!Labels;Лист1!Euros;1),
только наименование вместо euros => RUB, но я просто не могу ввести формулу. Нажимаю энтер, но формула не меняется, вам знакома такая ситуация?
спасибо
Спасибо.
Николай, добавил галочку для добавления в ленту Разработчика. Но кнопка "Вставить" неактивна.
Пакет установки полный, Excell 2007.
В чем проблема?
Спасибо.
Очень понравилась сама идея что такое вообще можно сделать в Excel, спасибо огромное! Подскажите а возможно объединить "Прошлое Будущее" и "Ближе Дальше" (сделать 1 шкалу периодом, при ее изменении и будет уже меняться и сама диаграмма и ее маштаб)?
Спасибо!
Скажите пожалуйста, возможно ли реализовать данный инструмент со шкалой наименований? Т. е. передвигаться не по шкале времени, а по шкале наименований объектов?
С линией проблем нет, но в случае биржевой диаграммы попытка заменить в РЯДе на именованный диапазон не приводит к результату. Заранее спасибо за помощь.
Пример потрясающий, спасибо за описание - все работает!
Скажите, неужели нет никакой возможности/инструмента/программы для создания динамических диаграмм в PPT? я видела такие графики на презентациях, все работало в обычном power pointe, но не могу понять как это создано чтобы повторить.
Заранее спасибо!
Всё сделал как у Вас написано, но при попытке заменить формулу "=ряд" выдаёт ошибку "формула на этом листе содержит одну или несколько недопустимых ссылок. проверьте правильность указания пути..." что делать. может ошибка где. приложил бы файл, но как?
В первую очередь- спасибо за отличную идею и подробную инструкцию!
У меня вместо валюты- менеджеры (графики по прибыли, обороту, количеству отгрузок), и, поскольку их достаточно много, вопрос: можно ли каким-то образом добавить еще галочку: выделить все. ( например, мне нужны данные только по 1 менеджеру, я долго-долго снимаю галочки со всех остальных, а затем также долго их обратно проставляю). Заранее спасибо!
CheckBox1.Value = 1
CheckBox2.Value = 1
CheckBox3.Value = 1
...
CheckBox100500.Value = 1
"встроенные" чекбоксы убогие до ужаса.
Дело в том, что в моем случае данные добавляются в таблицу каждый день, и так до конца года. Т.е. листая ScrollBox мы уходим в "чистую" область (например в декабрь 15). Возможно ли ограничить как -то постоение текущим днем?
Просьба знатоков помочь с графиком. Сделал все как указанно выше. Все работало но при сохранении и открытии файла слетало. Как то восстанавливал (давно в файл не залезал не помню точно что делал). Это не беда я может потом разберусь, что не так но сейчас не могу восстановить функционал вообще. Не дает сменить для первого графика диапазон в формуле на именованный диапазон "Consumers". Другие дает в формулу вставить, а этот нет. Я его уже удалял его и заново создавал. Тоже самое - при внесении его в формулу не реагирует на ввод. Что ему не нравиться?
Файл - или скину по почте.