Интерактивная диаграмма

Качественная визуализация большого объема информации – это почти всегда нетривиальная задача, т.к. отображение всех данных часто приводит к перегруженности диаграммы, ее запутанности и, в итоге, к неправильному восприятию и выводам.

Вот, например, данные по курсам валют за несколько месяцев:

interactive-chart1.png

Строить график по всей таблице, как легко сообразить, не лучшая идея. Красивым решением в подобной ситуации может стать создание интерактивной диаграммы, которую пользователь может сам подстраивать под себя и ситуацию. А именно:

  • двигаться по оси времени вперед-назад в будущее-прошлое
  • приближать-удалять отдельные области диаграммы для подробного изучения деталей графика
  • включать-выключать отображение отдельных валют на выбор

Выглядеть это может примерно так:

interactive-chart.gif

Нравится? Тогда поехали...

Шаг 1. Создаем дополнительную таблицу для диаграммы

В большинстве случаев для реализации интерактивности диаграммы применяется простой, но мощный прием – диаграмма строится не по исходной, а по отдельной, специально созданной таблице с формулами, которая отображает только нужные данные. В нашем случае, в эту дополнительную таблицу будут переноситься исходные данные только по тем валютам, которые пользователь выбрал с помощью флажков:

interactive-chart2.png

В Excel 2007/2010 к созданным диапазонам можно применить команду Форматировать как таблицу (Format as Table) с вкладки Главная (Home):

interactive-chart3.png

Это даст нам следующие преимущества:

  • Любые формулы в таких таблицах автоматически транслируются на весь столбец – не надо «тянуть» их вручную до конца таблицы
  • При дописывании к таблице новых строк в будущем (новых дат и курсов) – размеры таблицы увеличиваются автоматически, включая корректировку диапазонов в диаграммах, ссылках на эту таблицу в других формулах и т.д.
  • Таблица быстро получает красивое форматирование (чересстрочную заливку и т.д.)
  • Каждая таблица получает собственное имя (в нашем случае – Таблица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) и рисуем два флажка-галочки для включения-выключения каждой из валют:

interactive-chart4.png

Текст флажков можно поменять, щелкнув по ним правой кнопкой мыши и выбрав команду Изменить текст (Edit text).

interactive-chart5.png

Теперь привяжем наши флажки к любым ячейкам для определения того, включен флажок или нет (в нашем примере это две желтых ячейки в верхней части дополнительной таблицы). Для этого щелкните правой кнопкой мыши по очереди по каждому добавленному флажку и выберите команду Формат объекта (Format Control), а затем в открывшемся окне задайте Связь с ячейкой (Cell link).

Наша цель в том, чтобы каждый флажок был привязан  к соответствующей желтой ячейке над столбцом с валютой. При включении флажка в связанную ячейку будет выводиться ИСТИНА (TRUE), при выключении – ЛОЖЬ (FALSE). Это позволит, в дальнейшем, проверять с помощью формул связанные ячейки и выводить в дополнительную таблицу либо значение курса из исходной таблицы для построения графика, либо #Н/Д (#N/A), чтобы график не строился.

Шаг 3. Транслируем данные в дополнительную таблицу

Теперь заполним дополнительную таблицу формулой, которая будет транслировать исходные данные из основной таблицы, если соответствующий флажок валюты включен и связанная ячейка содержит слово ИСТИНА (TRUE):

interactive-chart6.png

Заметьте, что при использовании команды Форматировать как таблицу (Format as Table) на первом шаге, формула имеет использует имя таблицы и название колонки. В случае обычного диапазона, формула будет более привычного вида:

=ЕСЛИ(F$1;B4;#Н/Д)

Обратите внимание  на частичное закрепление ссылки на желтую ячейку (F$1), т.к. она должна смещаться вправо, но не должна – вниз, при копировании формулы на весь диапазон.

Теперь при включении-выключении флажков наша дополнительная таблица заполняется либо данными из исходной таблицы, либо искусственно созданной ошибкой #Н/Д, которая не дает линии на графике.

Шаг 4. Создаем полосы прокрутки для оси времени и масштабирования

Теперь добавим на лист Excel полосы прокрутки, с помощью которых пользователь сможет легко сдвигать график по оси времени и менять масштаб его увеличения.

Полосу прокрутки (Scroll bar) берем там же, где и флажки – на панели инструментов Формы (Forms) или на вкладке Разработчик (Developer):

interactive-chart7.png

Рисуем на листе в любом подходящем месте одну за другой две полосы – для сдвига по времени и масштаба:

interactive-chart8.png

Каждую полосу прокрутки надо связать со своей ячейкой (синяя и зеленая ячейки на рисунке), куда будет выводиться числовое значение положения ползунка. Его мы потом будем использовать для определения масштаба и сдвига. Для этого щелкните правой кнопкой мыши по нарисованной полосе и выберите в контекстном меню команду Формат объекта (Format control). В открывшемся окне можно задать связанную ячейку и минимум-максимум, в пределах которых будет гулять ползунок:

interactive-chart9.png

Таким образом, после выполнения всего вышеизложенного, у вас должно быть две полосы прокрутки, при перемещении ползунков по которым значения в связанных ячейках должны меняться в интервале от 1 до 307.

Шаг 5. Создаем динамический именованный диапазон

Чтобы отображать на графике данные только за определенный интервал времени, создадим именованный диапазон, который будет ссылаться только на нужные ячейки в дополнительной таблице. Этот диапазон будет характеризоваться двумя параметрами:

  • Отступом от начала таблицы вниз на заданное количество строк, т.е. отступом по временной шкале прошлое-будущее (синяя ячейка)
  • Количеством ячеек по высоте, т.е. масштабом (зеленая ячейка)

Этот именованный диапазон мы позже будем использовать как исходные данные для построения диаграммы.

Для создания такого диапазона будем использовать функцию СМЕЩ (OFFSET) из категории Ссылки и массивы (Lookup and Reference) - эта функция умеет создавать ссылку на диапазон заданного размера в заданном месте листа и имеет следующие аргументы:

interactive-chart19.png

В качестве точки отсчета берется некая стартовая ячейка, затем задается смещение относительно нее на заданное количество строк вниз и столбцов вправо. Последние два аргумента этой функции – высота и ширина нужного нам диапазона. Так, например, если бы мы хотели иметь ссылку на диапазон данных с курсами за 5 дней, начиная с 4 января, то можно было бы использовать нашу функцию СМЕЩ со следующими аргументами:

=СМЕЩ(A3;4;1;5;2)

interactive-chart10.png

Хитрость в том, что константы в этой формуле можно заменить на ссылки на ячейки с переменным содержимым – в нашем случае, на синюю и зеленую ячейки. Сделать это можно, создав динамический именованный диапазон с функцией СМЕЩ (OFFSET). Для этого:

  • В Excel 2007/2010 нажмите кнопку Диспетчер имен (Name Manager) на вкладке Формулы (Formulas)
  • В Excel 2003 и старше – выберите в меню Вставка – Имя – Присвоить (Insert – Name – Define)

Для создания нового именованного диапазона нужно нажать кнопку Создать (Create) и ввести имя диапазона и ссылку на ячейки в открывшемся окне.

Сначала создадим два простых статических именованных диапазона с именами, например, Shift и Zoom, которые будут ссылаться на синюю и зеленую ячейки соответственно:    

interactive-chart11.png interactive-chart12.png

Теперь чуть сложнее – создадим диапазон с именем Euros, который будет ссылаться с помощью функции СМЕЩ (OFFSET) на данные по курсам евро за выбранный отрезок времени, используя только что созданные до этого диапазоны Shift и Zoom и ячейку E3 в качестве точки отсчета:

interactive-chart13.png

Обратите внимание, что перед именем диапазона используется имя текущего листа – это сужает круг действия именованного диапазона, т.е. делает его доступным в пределах текущего листа, а не всей книги. Это необходимо нам для построения диаграммы в будущем. В новых версиях Excel для создания локального имени листа можно использовать выпадающий список Область.

Аналогичным образом создается именованный диапазон Dollars для данных по курсу доллара:

interactive-chart14.png

И завершает картину диапазон Labels, указывающий на подписи к оси Х, т.е. даты для выбранного отрезка:

interactive-chart15.png

Общая получившаяся картина должна быть примерно следующей:

interactive-chart16.png

Шаг 6. Строим диаграмму

Выделим несколько строк в верхней части вспомогательной таблицы, например диапазон E3:G10 и построим по нему диаграмму типа График (Line). Для этого в Excel 2007/2010 нужно перейти на вкладку Вставка (Insert) и в группе Диаграмма (Chart) выбрать тип График (Line), а в более старших версиях выбрать в меню Вставка – Диаграмма (Insert – Chart). Если выделить одну из линий на созданной диаграмме, то в строке формул будет видна функция РЯД (SERIES), обслуживающая выделенный ряд данных:

interactive-chart18.png

Эта функция задает диапазоны данных и подписей для выделенного ряда диаграммы. Наша задача – подменить статические диапазоны в ее аргументах на динамические, созданные нами ранее. Это можно сделать прямо в строке формул, изменив

=РЯД(Лист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  2  
03.01.2016 13:17:38
Николай спасибо за помощь. Но после 6 шага в графике исчезли линии.
23.01.2016 08:50:29
Как вариант замены таких танцев с бубном можно предложить использовать надстройку Power View (в некоторых версиях офиса есть сразу; ее только надо включить в COM-надстройках). Там подобные диаграммы с полосой прокрутки и прочими плюшками создаются в два клика. Но конечно, как я уже сказал, эта надстройка есть лишь в некоторых версиях MSO, поэтому такой вариант не всем подойдет, особенно, если экселевский файл надо отправлять другому пользователю.
08.02.2016 10:35:49
Добрый день!
Столкнулась с такой проблемой: мои данные для графика расположены не вертикально, а горизонтально (и транспонировать их нет возможности).
Соответственно мой диапазон "Labels" будет выглядеть таким образом:
=СМЕЩ(Динамика!$C$5;0;Shift;1;Zoom) 
а строка с данными для графика (они расположены через 1 строку), так:
 =СМЕЩ(Динамика!$C$5;2;Shift;1;Zoom) 
Но, когда я подставляю именованные диапазоны
=РЯД(Динамика!$A$7:$B$7;Динамика!$D$5:$OB$5;Динамика!$D$7:$OB$7;1) 

=РЯД(Динамика!$A$7:$B$7;Динамика!Labels;Динамика!PayPrice;1) 
у меня высвечивается ошибка:
Некоторые формулы на этом листе содержат недопустимые ссылки.
Убедитесь, что ссылки на ячейки, имена диапазонов, определенные имена и связи с другими книгами в формулах указаны верно.
Подскажите, как можно это исправить, или в чем моя ошибка.
Спасибо.
24.03.2016 14:28:14
Здравствуйте, Николай
у меня проблема с "ряд" в области данных, меняются на стандартные, и полосы прокрутки перестают работать. В вашем примере так же, после нескольких манипуляций прокрутка перестает работать. Было ли найдено решение данной проблемы?  
14.04.2016 16:32:59
Тоже была такая проблема, мне помогло преобразование таблицы, по которой строится график, в обычный диапазон.
Добрый день, Николай,

Я сделала все так, как вы описали - диаграмма построилась. Спасибо!
Но возникла необходимость один линейный график изменить на гистограмму и создать к ней вспомогательную ось.
Вот только, когда я отжимаю галочку, которая отвечает за данные по которым строится гистограмма, на диаграмме остаются подписи данных (не сами цифры, а надпись Н/Д). В случае с линейными графиками такого не происходит (они исчезают полностью при отжиме галочки).
Вы не могли бы подсказать, как можно это исправить?
06.06.2016 14:51:36
Добрый день! А возможно ли на графике выделить определенный период (например неделю),другим цветом...я делаю это вручную,но при перемещении ползунка,эта выделенная неделя на графике перемещается на другие дни...подскажите пожалуйста,возможно ли чтоб выделенный период не перемещался!?
16.08.2016 14:59:14
Добрый день! Подскажите, если надо сделать подписями оси ОХ два столбца, как будет выглядеть Labels?
25.11.2016 00:06:34
Добрый день. Подскажите как выровнять по левому краю именованный столбец в гистограмме в накоплением, к примеру фамилии.
25.11.2016 08:22:53
Николай, здравствуйте, подскажите, пожалуйста, что не так? Все сделали как вы описали сверху, но не получается последний шаг 6:

" подменить статические диапазоны в ее аргументах на динамические, созданные нами ранее. Это можно сделать прямо в строке формул, изменив
=РЯД(Лист1!$F$3;Лист1!$E$4:$E$10;Лист1!$F$4:$F$10;1)
на
=РЯД(Лист1!$F$3;Лист1!Labels;Лист1!Euros;1)"


При вводе Euros вместо стат.диапазона и нажатия Enter формула не активируется.

Спасибо
04.02.2017 13:48:56
Здравствуйте, Николай! Спасибо такую нужную инструкцию. Один момент: в моём случае диаграмма комбинированная (одна "с областями" и несколько "точечных" графиков, для которых я добавил (по аналогии с "Labels") именованный диапазон "Номер", значения которого целые числа по порядку от единицы). Если это имеет значение, то данных десятки тысяч. Проблема состоит в том, что при прокрутке в сторону "будущего" точечный график и диаграмма "с областями" не совпадают, происходит сдвижка, когда они должны быть "точка в точку". С приближением-удалением такой проблемы нет. Если в полосе прокрутки "Shift" значение 0 (в начале), то все точки лежат правильно (точечный график располагается по верхним границам диаграммы "с областями". Пожалуйста, помогите советом. В чём здесь может быть проблема..
11.08.2017 10:51:25
Добрый день.
Подскажите пожалуйста, как сделать интерактивную диаграмму только для 5 валют, как я понимаю для каждой валюты должно быть два столбца со значениям истина и ложь или это делается немного проще?
11.08.2017 11:55:50
Почему два? В примере выше такого нет. Для каждой валюты нужен один столбец - в нем будет или значение курса или ошибка #Н/Д. Так что пяти столбцов хватит + 1 столбец с датами.
11.08.2017 13:09:58
Спасибо.
04.10.2017 15:47:30
Николай, здравствуйте! Как Вы делаете видео-ролики в начале статьи, которые демонстрируют функционал Приема в Excel?
05.10.2017 09:57:23
В смысле - как? Руками, голосом :)
Если вы имеете ввиду программу для записи видео с экрана, то их вагон. Я использую Camtasia.
05.10.2017 10:09:07
Да, имела ввиду программу. Спасибо
27.10.2017 14:44:38
Добрый день! Может я задам глупый вопрос, возможно полосе задать значение для процентных показателей, которые в большинстве своем меньше 1%? Спасибо.
26.04.2018 21:47:05
Приветствую!
Очень полезная стать. Благодаря ей удалось реализовал давнюю задумку, благодарю автора!
Но есть еще один момент который хотелось бы реализовать. У меня длинная таблица с данными по изменению одного параметра, все здорово выводится и масштабируется, НО хочется помимо самой динамики видеть некие интервалы этих изменений. На скриншоте ниже вертикальными красными линиями нарисовал те самые интервалы. Как можно реализовать это? Заранее благодарю за помощь!
   



 
17.01.2019 15:46:49
Николай, скажите пожалуйста. Возможно ли вывести график вместе с ползунками и чекбоксами на отдельный лист ???
15.04.2019 09:45:34
Добрый день.
У меня проблема. Дошел до шага 6. Диаграмму построил. Когда в строке формул меняю диапазоны на "Labels" и "Received" (это у меня вместо "Euro") нажимаю Enter или галочку у строки формул... и далее ничего не происходит.... Выделенный диапазон таблицы, указывающий ранее на исходные данные для диаграммы, исчезает, выделение с диаграммы не снимается, (в строке формул мигает курсор) никакая ячейка в поле листа не выбирается, выходит из этого состояния только при нажатии клавиши Esc, при этом формула в строке формул приобретает первоначальный вид.
15.04.2019 12:08:55
Андрей Кузнецов
15.04.2019 09:45:34


Добрый день.
У меня проблема. Дошел до шага 6. Диаграмму построил. Когда в строке формул меняю диапазоны на "Labels" и "Received" (это у меня вместо "Euro") нажимаю Enter или галочку у строки формул... и далее ничего не происходит.... Выделенный диапазон таблицы, указывающий ранее на исходные данные для диаграммы, исчезает, выделение с диаграммы не снимается, (в строке формул мигает курсор) никакая ячейка в поле листа не выбирается, выходит из этого состояния только при нажатии клавиши Esc, при этом формула в строке формул приобретает первоначальный вид.
Увидел такой же вопрос в комментариях, но ответа на него не увидел.
08.11.2019 21:26:56
О! почти то что хотел, но вот как бы сделать именно как в яндексе на валютах?
03.05.2020 10:28:54
Здравствуйте!
Нужно сделать динамическое изменение границ осей (прыгающий график).
Как Excel делает автоматически - не устраивает (от 0 до максимума + запас). Надо чтобы от корки до корки.
Если график изменился и например по оси Y значения установились в диапазоне [20; 30] то и ось должна стать [20; 30], а не [0; 35] как делает Excel-Автомат.
Динамический расчет нужных максимумов и минимумов я вывел в отдельные ячейки. Но вот как эти ячейки диаграмме втюхать?
Формат оси -> Вертикальная ось значений -> Параметры оси -> Границы - либо Excel-автомат, либо статические значения.
Не варианта чтобы брать значение из ячейки.
Формулу не кушает.
(Excel 2016)
Так понимаю что без макроса никак...
Пытался записать макрос - увидеть что делает Excel для изменения границ. Но что-то не клеится это направление
ActiveSheet.ChartObjects("Диаграмма 4" ).Axes(xlValue).MinimumScale = Cells(9, 14).Value    'какая-то ошибка
хочет сначала
ActiveSheet.ChartObjects("Диаграмма 4" ).Activate
ActiveChart.Axes(xlValue).Select
А это каждый раз переключает фокус с активной ячейки на диаграмму.
И как сделать макрос чтобы он срабатывал ТОЛЬКО при изменении диаграммы?
17.05.2020 10:41:27
ау? есть кто живой?
14.10.2020 22:17:04
Всем по привету!
Вопрос по диаграмме! Есть таблица, в ней есть строки с датой и ценами, и столбцы с наименованием продукта. Можно ли сделать диаграмму, чтобы в ней отражались данные только по одному столбцу (допустим морковка) и две линии или столбца или еще чего то по дате, причем дата должна быть первое и пятнадцатое число месяца и каждый год отдельно, т.е. предполагается две линии (или столбца) 19 и 20 год с точками 1 и 15 число месяца?
23.03.2021 08:31:44
Николай, привет! Меня заинтересовала твоя статья. Все изложено подробно и по факту. Спасибо!
Но у меня появились некоторые вопросы которые не дают мне покоя. Можно ли реализовать подобное с сводными диаграммами у которых данные взяты с PP (Power Pivot) или с PQ (Power Query)? Буду рад вашим ответам!!!
:)
19.07.2021 06:53:59
Прием с использованием #Н/Д для второй таблицы означает, что вторая таблица должна быть такого же размера, как и первая?

Для меня это критично, потому что первая таблица исходных данных может быть размером до 800 000 строк. Современные компьютеры и такие массивы перелопачивают, но все равно тормозят прилично. Во второй таблице как раз и нужно производить вычисления над исходными данными, и даже если заменить вычисление на #Н/Д, то Excel все равно будет пытаться сначала сосчитать каждую ячейку, пока доберется до ЕСЛИ и поставит #Н/Д .

А можно вторую таблицу строить только для нужного кол-ва данных?  Вот это и было бы - вырезать нужный  массив данных. Как бы изменять размер второй таблицы под заданные условия? Для меня условия - это номера двух строк, начало и конец для второй таблицы.
04.10.2021 15:43:10
Николай, Здравствуй.
Не могу нарадоваться вашей диаграммой.
Помогите пожалуйста решить проблемку с диаграммами.
Мне необходимо в одном эксель файле, но на нескольких листах (3-5) использовать эти диаграммы с разными значениями.
На одном первом листе диаграммы меняются, на других нет.
Можно решить эту проблему? Если да,пожалуйста, подскажите как.
:(
Николай умер...
:oops:
Страницы: 1  2  
Наверх