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

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

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

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. При изменении положения ползунков будут меняться диапазоны и, как следствие, диаграмма. При включении-выключении флажков – отображаться только те валюты, которые нам нужны.

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

Ссылки по теме

 


22.02.2013 12:28:09
Ник, привет. Слушай, зверски клевая вещь. Один нюанс: когда при помощи флагов убираю из области данных диагрммы колонки с данными, то после включения их обратно формулы ("ряд" в области данных) меняются на стандартные, и полосы прокрутки перестают работать. Как их (формулы) закрепить, чтобы включение выключение флагов не меняло формулы в диаграмме? ME 2010.
Заранее благодарю.
23.02.2013 12:43:13
Василий, видимо вы убираете ссылку на диапазон с данными полностью (тогда настройки сбрасываются), а я заменяю данные в ненужном ряду ошибкой #н/д (тогда ряд просто не виден на диаграмме, но фактически в ней остается и настройки не слетают). Скачайте пример - посмотрите как у меня реализовано.
28.02.2013 10:03:15
Приветствую, Коль. Благодарю за твое время. Я все сделал, как у тебя написано здесь. И при выключеном флаге столбец заполняется #Н/Д. Но после включения флажка обратно вместо измененной вручную формулы "ряд" с сылкой на именованные диапазоны задается обычная область данных с адресами ячеек. Не понимаю почему так...
01.03.2013 15:39:04
Василий, киньте мне свой файл в почту - посмотрю, ок?
20.06.2013 14:20:27
Николай, у меня такая же проблема как у Василия. С чем это связано, Вы выяснили?
06.06.2014 16:23:28
Аналогично - Диаграмму хлебом не корми - дай настройкам "слететь на базовые" :):):)
09.06.2014 10:23:25
Вставил вспомогательную формулу: чтобы Динамический диапазон был всегда на 1 ед меньше Максимальной длине талблицы.
Короче, нужен "умный" динамический диапазон - который не дает слететь настройкам в Графике!!!
У меня такая же проблема, насколько я понимаю.
Я не стал реализовывать действия с галочками, мне важно приближение и выделение дат. При максимальном удалении и показывании всех дат диапазон меняется на обычную область ячеек и полосы перестают работать...
01.03.2013 12:52:19
Николай,
А как сделать диаграмму следующего вида: нужны три полосы прокрутки параметров (например, от 1 до 100), которые можно было бы перетягивать как в Вашем примере и два слолбика диаграммы, которые зависят от полос прокрутки. Как в фотошопе, там где можно каналы сдвигать или фиксировать один канал в каком-то положении, а результат кривой (в моем случае двух финальных показателей) зависит от положения всех трех ползунков. Надеюсь, понятно объяснил, что хочу получить.
Вот ссылка с рисунком того, что хотелось бы
http://yadi.sk/d/ylc1D-Fb2yb32
03.03.2013 09:01:29
А чем ваш вариант отличается принципиально от этого примера? Только 2-3 полосы прокрутки, а не одну надо нарисовать. Привязать их к ячейкам. На эти ячейки завязать ваши формулы для расчета итоговых значений. По итоговым значениям построить диаграмму.
21.09.2013 21:51:47
Здравствуйте, Николай. Хотелось бы увидеть от Вас пример построения графика Точки Безубыточности по такой же схеме. Т.е. при разной прибыли/выручке смещается дата Точки Безубыточности. С такими же ползунками.

Или просто пример построения Точки Безубыточности с разными кривыми при разных прибылях/выручках

А также график срока окупаемости проекта при разных прибылях/вырчуках

Думаю, всем это было бы очень интересно
28.03.2013 08:46:57
Николай, реально зверская вещь. Ну ооочень понравилось - особенно для накопления и быстрого доступа к данным в любое время за любой период. Попробовала воспроизвести сама, всё работает нормально, кроме ползунка "ближе-дальше". Почему-то в определённых местах он "зависает". Помогите разобраться пожалуйста.
11.04.2013 10:52:20
Ну, не видя вашего файла, ничего сказать не могу. Может компьютер тормозит, может еще что-то. Киньте в почту - посмотрю.
08.04.2013 17:45:50
Николай, спасибо огромное. Замечательная вещь. Один вопрос только: когда убираю флажок с Euro на диаграмме линия отображающая эту валюту также исчезает, только на ее место сверху сразу становится линия Dollar. Подскажите пожалуйста как сделать, чтобы при вкл/выкл. флажка валют линии диаграмм оставались на своих местах, как это показано в вашем примере (т.е. Евро всегда вверху, а Доллар внизу).
Спасибо заранее.
11.04.2013 10:51:25
Это у вас просто вертикальная шкала автоматически подстраивается под новые данные. Щелкните по ней правой - Формат оси и задайте фиксированное максимальное значение "с запасом". Тогда график прыгать не будет.
12.04.2013 11:54:02
Спасибо.
25.04.2013 12:42:40
Николай, Спасибо огромное за такой инструмент. При повторении урока - все получилось, но при попытке реализовать динамическую диаграмму со своей структурой данных столкнулся со сложностью. Сложность возникла на финальном этапе - редактировании аргументов функции диаграммы. После того, как я подставляю в функцию в качестве аргумента для диапазона данных именованный диапазон - я не могу подтвердить внесенные изменения. Именованный диапазон в аргумент функции, отвечающий за подписи оси Х подставляется отлично. Подскажите пожалуйста, в чем может быть ошибка. Если необходимо - я могу выслать файл с проблемой. Заранее Спасибо!
30.04.2013 14:06:12
Миколо, дякую за нові можливості у Exel, спробував поданий метод зі своїми даними і стикнувся з проблемою, словами описати її досить складно, чи можна надіслати Вам файл для перегляду. Буду вдячний.
15.05.2013 15:02:17
По-украински не разумею, уж простите :)
15.05.2013 21:02:27
Николай, вот уже месяц пользуюсь этими диаграммами, никак не нарадуюсь - так здорово держать все данные в одном файле (на одном листе!) и при необходимости буквально в один клик выдавать данные за любой промежуток времени. Сегодня попробовала вставить интерактивную диаграмму в презентацию - почему-то не получается - вставляется сама диаграмма без флажков. Пробовала выделять элементы управления, группировать и так - кучей копировать в презентацию - тоже никак, почему-то при переносе в Power Point саму объединённую фигуру не показывает (т.е. нажимаешь мышкой - понятно, что она там есть, но её не видно). Есть способ вставки интерактивной диаграммы в презентацию? Помогите пожалуйста
16.05.2013 09:52:08
Со 100% сохранением функциональности это перенести в Power Point не получится. Можно скопировать в Excel, а в ПП вставить как картинку со связью (Главная - Вставка - Специальная вставка - Связать - Рисунок). Тогда при двойном щелчке по картинке на слайде будет переход к исходному Excel-файлу. Можно также попробовать вставить в режиме "Объект Лист Microsoft Excel" - тоже частично функционал должен сохраниться.
17.05.2013 09:40:48
Спрошу сразу ещё вот о чём. Я никак не могу найти на форуме ответ на ещё один интересующий вопрос.
Необходимо организовать Web-публикацию такой интерактивной диаграммы. Может подскажете как может быть реализована такая задача? Статическая диаграмма публикуется - это проще паренной репы. А вот чтоб кнопочки нажимались, чтоб диапазон и масштаб менялись... :(
17.05.2013 12:07:58
Не думаю, что это возможно без применения веб-программирования (JavaScript или подобного).
02.06.2013 22:20:00
Добрый вечер, Николай! Хочу предложить способ реализации Вашей идеи, но без посредства дополнительной таблицы. Но, правда, нужно создать дополнительный четвертый столбец в таблице, который будет заполняться значениями #Н/Д.
Динамический диапазон Labels по такой же формуле, как в статье, но только уже со сдвигом относительно заголовка поля с подписями категорий в исходной таблице. Диапазон Dollars будет базироваться на такой формуле:

=СМЕЩ(Лист1!Labels;0;3-Ячейка с логическим значением доллар*1).

«Ячейка с лог. значением» в формуле, эта та, которая связана с соответствующим флажком, который отвечает за вывод или скрытие графика в долларах. Если в этой ячейке ИСТИНА, сдвиг по столбцам будет равен 2 (колонка с курсом доллара). Если флажок снят, мы получаем значение ЛОЖЬ, оно умножается на 1, дает ноль, и в итоге формула выносит диапазон на третий столбец, как раз тот, который у нас всегда заполнен ошибками. Аналогично диапазон Euros:

=СМЕЩ(Лист1!Labels;0;3-Ячейка с логическим значением евро*2),

тут тоже, или диапазон данных попадет в первую колонку со значениями и выдаст нам график по евро, либо попадет в колонку с ошибками и не выдаст нам график.
Не знаю как в других версиях, но в excel 2010 и наличие служебной колонки не нужно, достаточно пустого столбца. Если диапазон, на который ссылается ряд данных, пуст, на графике линий не создается. Если используется динамическая таблица, то вместо значения #Н/Д можно использовать =НД(), тогда и копировать ошибку не придется.
16.02.2016 20:28:52
Можно и без дополнительных столбцов (в Excel 2013 работает):
Dollars
 =ЕСЛИ(Лист1!$G$1=ЛОЖЬ;{#Н/Д};СМЕЩ(Лист1!$A$3;Shift;2;Zoom;1))
Euros
=ЕСЛИ(Лист1!$F$1=ЛОЖЬ;{#Н/Д};СМЕЩ(Лист1!$A$3;Shift;1;Zoom;1))
06.08.2013 15:19:09
Здравствуйте, Николай! Подскажите, а можно реализовать то же самое, только с учетом времени, а не только даты. Тоесть, когда временной формат имеет значение не "дд.мм.гггг", а "дд.мм.гггг чч.мм.сс" или "дд.мм.гггг" "чч.мм.сс". Чтобы на графике было видно изменение значения на протяжении дня.  Заранее Вам спасибо. file1.xls
Роман, позвольте я вам помогу с этим вопросом. Для того, чтобы на графике отображался нужный вм формат времени, необходимо в таблице с исходными данными задать требуемый формат. График автоматически подтягивает формат из таблицы
09.09.2013 23:26:40
Здравствуйте, Николай! Замечательная вещь, то о чем всегда думал, но реализовать полностью "одной кнопочкой" не получалось! Однако есть один момент с легендой. Я сделал пример, но с бо!льшим объемом валют. Как бы сделать так, чтобы и в легенде диаграммы отображалость то количество "галок", которое я выбрал (если 1 валюту, то только ее, если 2, то 2, а не все запрограммированные виды валют)? Огромное спасибо!!!
Артем, попробуйте скачать пример по ссылке в начале страницы, у меня сначала тоже сильно все глючило. Комментариев которые даются в пошаговой инструкции не достаточно для простого пользователя, но методом сравнения, проб и ошибок, все в итоге получилось.
Я сделал свой график с 5 отобранными акциями.
18.12.2013 11:01:45
Николай спасибо большое для ваши полезные советы! Я часто посещаю и изучаю много. Будьте здоров!
С наступающим!
18.12.2013 14:05:51
Спасибо! И вам того же! :)
29.12.2013 01:41:10
Очень хорошая идея, вещь полезная. Но вот что предлагаю подумать: можно ли реализовать механизм расчета доходности движения за период zoom? Т.е. начало периода, определяемое Shift будет базой и равно 1.
04.01.2014 11:22:20
Долго въезжал в смысл фразы "доходность движения за период". Так и не въехал :)  Имеется ввиду вычисление динамики изменения в процентах на начало и конец периода zoom?
07.01.2014 18:34:51
Извините. Я решил использовать интерактивную диаграмму для демонстрации доходности портфеля ценных бумаг за период zoom при этом начало этого периода равно 0% а доходность следующих отметок на графике будет равна их реальность доходности от начала минус доходность начала периода zoom. Например, начало формирования портфеля январь 2013, февраль +2%, март +4%,... июнь -5%:..сентябрь +2%.. декабрь 6%.  Итого, доходность за весь период +6%,. но если переместить график на июнь, то доходность на начало июня равно 0%, к сентябрю +7% (2-(5%)),к декабрю +11%. Соответственно, рисунок графика с июня будет растущим. Сама доходность с начала ведения портфеля у меня есть,её вычислять не надо.
11.01.2014 13:12:28
А можно вопрос не по теме?
Как сделать эффект разрыва листа, с оборванными краями, как на первой картинке в статье?
11.01.2014 16:27:48
Спасибо, понятно
07.03.2014 21:03:04
Здравствуйте, Николай! У меня такой вопрос, а как сделать, чтоб значение на вертикальных осях менялись автоматически с обычных чисел на проценты в случае изменения данных. Я видел, что это возможно, но у меня не получается.;)
15.07.2014 17:37:02
Щелкнуть правой кнопкой по оси - Формат оси - Число - галка Связь с источником.
Тогда при изменении формата ячеек с исходными данными будет автоматом меняться шкала оси.
19.03.2014 17:10:46
Здравствуйте Николай! Интересует вопрос: если есть две таблицы с данными по курсам валют, например в разных отделениях (5-6 отделений), те же ЕВРО и ДОЛЛ (отдельно таблица по ЕВРО с номерами отделений в качестве заголовков столбцов, такая же ДОЛЛ), и мне нужно что бы можно было выбирать из выпадающего списка нужное отделение, и соответствующие значения курсов подставлялись в таблицу для построения диаграммы? Как задать ссылку на нужный столбец в формулу  =ЕСЛИ(F$1;  ?  ;#Н/Д)
03.06.2014 17:06:37
Николай, здравствуйте! Подскажите можно ли использовать формулу СМЕЩ для указания диапазона данных всей диаграммы, а не одном ряде?
09.06.2014 12:05:19
Проблема в том, что для диаграммы нужна функция РЯД, а для нее каждый ряд с данными задается отдельно.
30.06.2014 11:57:01
Николай, здравствуйте
Не подскажите в чем может быть причина следующего:
в общем все сделал как у вас описано выше и все работает, за одним маленьким исключением, я создавал не для двух валют диаграмму, а для трех. С самого начала планировал для трех валют все, в итоге доллар и евро работают, а на курс рубля не получается. Когда делаю последнее действие
=РЯД(Лист1!$F$3;Лист1!$E$4:$E$10;Лист1!$F$4:$F$10;1)
на
=РЯД(Лист1!$F$3;Лист1!Labels;Лист1!Euros;1),  
только наименование вместо euros => RUB, но я просто не могу ввести формулу. Нажимаю энтер, но формула не меняется, вам знакома такая ситуация?
спасибо
14.07.2014 15:16:40
Здравствуйте, Николай! Спасибо за инструмент. Всё работает. Вот только при попытке загнать данный инструмент под свои задачи столкнулся с необходимостью сделать выпадающий список. Те по вашему примеру это было бы не две валюты, а скажем десять. Тогда Check Box становится не очень удобен и хочется сделать выпадающий список на графике с возможностью мультивыбора. Это возможно реализовать стандартными методами Excel или надо макрос писать?
Спасибо.
15.07.2014 17:35:19
Да нет, зачем макросы? Создаете выпадающий список и в формулах в столбцах В и С с помощью ЕСЛИ проверяете выбранное значение. Если выбрана текущая валюта, то отображаете ее величину, если нет - НД(). Очень похоже, в общем и целом, на работу с чекбоксом, только проще.
17.07.2014 12:34:20
Спасибо, большое.
29.07.2014 13:13:21
Всем привет,
Николай, добавил галочку для добавления в ленту Разработчика. Но кнопка "Вставить" неактивна.
Пакет установки полный, Excell 2007.
В чем проблема?
Спасибо.
28.08.2014 05:40:41
Доброго времени суток!

Очень понравилась сама идея что такое вообще можно сделать в Excel, спасибо огромное! Подскажите а возможно объединить "Прошлое Будущее" и "Ближе Дальше" (сделать 1 шкалу периодом, при ее  изменении и будет уже меняться и сама диаграмма и ее маштаб)?

Спасибо!
21.10.2014 15:29:59
Здравствуйте, Николай!
Скажите пожалуйста, возможно ли реализовать данный инструмент со шкалой наименований? Т. е. передвигаться не по шкале времени, а по шкале наименований объектов?
02.11.2014 00:12:24
Подскажите, можно ли как-то воспользоваться этим методом, если диаграмма Биржевая?
С линией проблем нет, но в случае биржевой диаграммы попытка заменить в РЯДе на именованный диапазон не приводит к результату. Заранее спасибо за помощь.
03.11.2014 18:15:24
Николай, добрый день!
Пример потрясающий, спасибо за описание - все работает!
Скажите, неужели нет никакой возможности/инструмента/программы для создания динамических диаграмм в PPT? я видела такие графики на презентациях, все работало в обычном power pointe, но не могу понять как это создано чтобы повторить.
Заранее спасибо!
16.02.2015 21:59:43
Николай, ваша статья помогла понять, что в описании ряда перед именованным диапазоном надо обязательно писать имя листа. Это, блин, не очевидно, а я делал первый раз. Спасибо.
21.02.2015 19:31:53
Также написал статью на эту тему. Чуть-чуть более сложная реализация интерактивной диаграммы (добавил ещё одно измерение). Если кому-то интересно, то можно взглянуть тут. А Николаю спасибо за отличную статью, она, как следует из комментария выше, помогла мне кое в чём разобраться.
25.02.2015 00:17:16
Спасибо! Остался один вопрос как сделать тек чтобы не только график не отображался, но и легенда. (знаю что в 2013 есть удобный фильтр а вот в 2010 нет :( )
16.07.2015 17:54:18
Николай, добрый день.
Всё сделал как у Вас написано, но при попытке заменить формулу "=ряд" выдаёт ошибку "формула на этом листе содержит одну или несколько недопустимых ссылок. проверьте правильность указания пути..." что делать. может ошибка где. приложил бы файл, но как?
19.07.2015 10:54:04
Сюда не приложить. Шлите мне в почту или выложите на форум лучше.
28.07.2015 09:08:47
я отправил Вам таблицу в письме, но ничего пока не получил, прошла неделя
20.08.2015 11:05:12
Николай, добрый день!
В первую очередь- спасибо за отличную идею и подробную инструкцию!
У меня вместо валюты- менеджеры (графики по прибыли, обороту, количеству отгрузок), и, поскольку их достаточно много, вопрос: можно ли каким-то образом добавить еще галочку: выделить все. ( например, мне нужны данные только по 1 менеджеру, я долго-долго снимаю галочки со всех остальных, а затем также долго их обратно проставляю). Заранее спасибо!
01.10.2015 12:29:22
Чекбоксы ActiveX рисуйте. И кнопку нарисуйте. ну и в обработчике нажатия

CheckBox1.Value = 1
CheckBox2.Value = 1
CheckBox3.Value = 1
...
CheckBox100500.Value = 1

"встроенные" чекбоксы убогие до ужаса.
01.10.2015 14:03:59
спасибо!:) попробую....
01.10.2015 09:38:42
благодарю за полезность!! но есть проблемка одна. у вас даты идут подряд (1, 2, 3, 4). а если они идут НЕ подряд (11, 13, 17, 19), и некоторые даты еще не заполнены (23, 27, 29), как поступить? если включить радиокнопку "Ось текста" в Формат оси - Параметры оси, то он оставляет только нужные даты, НО другие (23, 27, 29) отображает как 0.
02.10.2015 10:09:09
Выделить диаграмму, на вкладке Конструктор кнопка Исходные данные, потом Скрытые и пустые ячейки - Линия. Тогда разрывы данных должен, по-идее, соединять, а не отображать нулями.
15.10.2015 14:00:56
Здравствуйте! Поскажите, есть ли возможность ограничить интервал построения?
Дело в том, что в моем случае данные добавляются в таблицу каждый день, и так до конца года. Т.е. листая ScrollBox мы уходим в "чистую" область (например в декабрь 15). Возможно ли ограничить как -то постоение текущим днем?
30.12.2015 10:38:18
Добрый день и всех с наступающим!
Просьба знатоков помочь с графиком. Сделал все как указанно выше. Все работало но при сохранении и открытии файла слетало. Как то восстанавливал (давно в файл не залезал не помню точно что делал). Это не беда я может потом разберусь, что не так но сейчас не могу восстановить функционал вообще. Не дает сменить для первого графика диапазон в формуле на именованный диапазон "Consumers". Другие дает в формулу вставить, а этот нет. Я его уже удалял его и заново создавал. Тоже самое - при внесении его в формулу не реагирует на ввод. Что ему не нравиться?
Файл тут - http://my-files.ru/0bkems или скину по почте.
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
Да, имела ввиду программу. Спасибо