Масштабируемая диаграмма в Excel

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

Например, на графике ниже отлично видна общая тенденция изменения стоимость конкретной акции и всего пакета на отрезке с 1-ой по 500-ую сессию.  

 Но что, если на совете директоров, где Вы презентуете этот замечательный график, захотят посмотреть, что происходило в период с 40-й по 150-ую сессии? А потом с 345-й по 388-ую? Это будет фиаско. И дело не в отсутствии горизонтальной оси :) Будь она на месте, всё равно никак бы нам не помогла. Но что же делать? Строить десятки графиков под возможные запросы и показывать их по мере необходимости? Вариант для ярых мазохистов. А вот если бы можно было в несколько кликов увеличить масштаб графика и сдвигать его вправо/влево - это был бы отличный выход! На наше счастье, такая задача вполне решаема. Нужно будет обычные ряды данных на диаграмме заменить на созданные динамические диапазоны. А вот размер и положение динамического диапазона будет подбираться исходя из значений, выбранных на полосе прокрутки. Разберем методику пошагово.  

 

Построение диаграммы

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

Подробно разбирать этот процесс не будем, так как он не относится к теме статьи. Пример построения комбинированной диаграммы можно посмотреть в этом уроке. 

Добавляем и настраиваем полосы прокрутки

Увеличение масштаба и сдвиги диаграммы вправо/влево мы будем осуществлять с помощью элемента управления "Полоса прокрутки". Это удобно, красиво и вполне интерактивно. 

На вкладке "Разработчик" (если ее нет - здесь показано, как ее отобразить) выберите "Вставить" - "Элементы управления форм" - "Полоса прокрутки".

 Разместите рядом с диаграммой две горизонтальные полосы прокрутки. Можете сразу их подписать. Одна будет отвечать за выбор сессии, с которой начинается график, а вторая - за количество сессий на графике (то есть за масштаб). У нас получилось вот так.  

 Теперь нужно настроить полосы прокрутки. Кликните на первой правой кнопкой мыши и выберите "Формат объекта". Откроется окно "Формат элемента управление". Выберите вкладку "Элемент управления".  

 

• Текущее значение можно не задавать, или поставить 1;
• Минимальное значение - укажите 1. Это номер сессии, с которой допустимо начинать построение графика (а еще это количество ячеек, на которое будет сдвигаться наш динамический диапазон из исходной точки);
• Максимальное значение - зависит от количества точек данных на графике (строк в исходной таблице). У нас в таблице 500 строк, поэтому правильно будет задавать максимальное значение не больше 500 (ибо сдвиг более чем на 500 точек ничего не даст - закончатся данные). Но если впоследствии на полосе будет выбрано 500, то на графике будет всего одна точка (последняя). Это будет не слишком красиво. Условимся, что на графике может быть не менее 10 точек одновременно. Поэтому вместо 500 укажем максимальное значение 491. 
• Шаг изменения - это величина, на которую будет изменять значение полосы прокрутки при клике на стрелочки по обеим ее сторонам. Оставим 1, чтобы имелась возможность максимально точно настраивать значение;
• Шаг изменения по страницам - это величина, на которую будет изменяться значение полосы прокрутки при клике на полосе справа или слева от бегунка (так сказать, быстрая перемотка). Установим тут значение 10. 
• Связь с ячейкой. Самый важный момент. Укажите ссылку на ячейку, в которую будет выводиться число, выбранное полосой прокрутки. Впоследствии эта ячейка будет задействована в создании именованного диапазона для графика. Очень удобно будет задать этой ячейке какое-то имя, чтобы было проще к ней обращаться.  Но в данном примере мы оставим обычные ссылки. Для первой полосы связанной ячейкой укажем &F&1 (Вы, разумеется, можете указать любую). 

Вот так выглядят итоговые настройки: 

 Теперь настроим вторую полосу. Она, если помните, отвечает за количество точек (масштаб) диаграммы. Для нее укажем минимальным значением - 10 (мы условились, что на графике может быть не менее 10 точек). Максимальное значение укажем 500 (по количеству строк в таблице). Шаги изменения также зададим как 1 и 10. А свяжем всё это с ячейкой &G&1. 

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

 Перед тем как приступить к созданию динамических диапазонов, применим еще один небольшой трюк. Если мы выберем на первой полосе значение 491 (максимальное), а на второй - например, 50, то получим очень некрасивый график. Он будет начинаться с 491-й сессии и содержать 50 точек, в то время как наши данные заканчиваются на 500-й строке. Оставшиеся 40 точек будут просто пустыми и график будет кривой и непрезентабельный. Обойдем это следующей хитростью. В ячейку H1 введем формулу =МИН(G1;501-F1). Это формула будет всегда отображать в ячейке меньшее из значений: либо количество точек по второму ползунку, либо количество оставшихся до конца таблицы точек. Именно на эту ячейку мы будем ссылать при указании высоты именованного диапазона.
 

Создание динамических именованных диапазонов

Ключевая идея всего трюка - создание именованных диапазонов, размеры которых зависят от значений в ячейках, связанных с полосой прокрутки. Для каждого ряда нужно будет создать свой динамический диапазон. У нас таких рядов будет два: отдельная акция и стоимость всего пакета. Также нужно будет создать отдельный динамический диапазон для подписей данных, если Вы планируете их использовать на диаграмме.

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

=СМЕЩ(Лист1!$A$1;Лист1!$F$1;1;Лист1!$H$1;1)

Чтобы Вы поняли, что она делает - покажем наши исходные данные. Они выглядят так:   

Лист1!$A$1 - стартовая ячейка (шапка первого столбца с подписями данных);

Лист1!$F$1 - смещение по строкам - величина, выбранная первым ползунком;

1 - смещение по столбцам (нужно попасть из столбца A в столбец с Курсом акции "ФинИнт);

Лист1!$H$1 - высота диапазона  - величина, выбранная вторым ползунком и скорректированная нашей формулой;

1 - ширина диапазона (всегда равна единице, так как для одного ряда диаграммы можно указать только один столбец данных). 

На рисунке выше зеленым цветом выделен диапазон, построенный из 4-ой строки и масштабом в 12 точек данных (если функция СМЕЩ все же непонятна, то почитайте вот эту статью - мы подробно ее разбираем). 

Итак, первый создаваемый именованный диапазон должен выглядеть так: 

Аналогично создадим еще два - для второго ряда и для подписей данных. Мы назовём их "Пакет" и "Подписи". При создании формулы отличие будет только в третьем аргументе функции СМЕЩ (смещение по столбцам). Для второго ряда укажите 2 (чтобы попасть из столбца А в столбец С), а для подписей данных - 0 (они и так расположены в столбце А).

Получиться должно примерно вот это:

 

Перенос динамических диапазонов на диаграмму

Мы соединили полосы прокрутки и динамические диапазоны в единое целое. Осталось подключить к ним созданную в начале диаграмму. Кликните на график с единственной акцией один раз и в строке формул появится функция РЯД.  

 Первый ее аргумент - это название ряда. Его можно не трогать. Нам нужны второй и третий аргументы. Второй отвечает за подписи данных, а третий - за сами значения. Нам нужно заменить статичные ссылки на созданные диапазоны. Замените Лист1!$A$2:$A$501 на Лист1!Подписи, а Лист1!$B$2:$B$501 на Лист1!Акция и нажмите Enter.  

Функция РЯД в строке формул заменит название листа на название файла (так как мы создавали имена уровня книги), а график тут же перестроится в зависимости от того, что выбрано в данный момент Вашими полосами прокрутки. Проделайте ту же операцию для второго ряда (указав его имя в третьем аргументе). В принципе, всё готово. Осталось навести немного красоты. 

Введите в любую пустую ячейку (мы вводили в I1) формулу:

=СЦЕПИТЬ("Динамика курса с ";F1;" по ";F1+H1-1;" сессию")

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

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

Кстати, чтобы скрытые исходные данные отображались на диаграмме, сделайте следующее. Выделите диаграмму, выберите "Конструктор" - "Выбрать данные" - "Скрытые и пустые ячейки" и поставьте галочку "Показывать данные в скрытых строках и столбцах". 

Получим вот такую прекрасно масштабируемую диаграмму, с которой не стыдно явиться на любое совещание.   

Файл-пример, как всегда, можно найти на нашем канале. 

Поддержать наш проект и его дальнейшее развитие можно вот здесь.

Ваши вопросы по статье можете задавать через нашего бота обратной связи в Telegram: @ExEvFeedbackBot

С уважением, команда tDots.ru