Живая диаграмма

Работа средней руки аналитика - это вечный цейтнот. Он разрывается между огромным количеством дел, которые, если их не автоматизировать, будут убивать ваше рабочее время со страшной эффективностью, и между самим процессом автоматизации, который тоже занимает массу времени (и требует покоя!), но без которого точно хана. Что? Это про вас? Тогда почаще заходите на мой сайт, потому что здесь уже много статей, которые сильно сэкономят вам время, а в будущем их будет ещё больше.

Если вы серьёзно занимаетесь аналитикой, то точно знаете, что визуализировать большие объёмы данных очень непросто. Кому-то надо увидать общую картину за весь период наблюдений, кто-то заинтересуется конкретным периодом времени. Если вы начнёте работать по заявкам многочисленных заказчиков, то количество вариантов графиков быстро начнёт расти и вы столь же быстро в них потонете и запутаетесь, не говоря уж о том, что это всё отнимает время и силы. Не лучше ли сразу делать такие диаграммы, которые на корню отсекают целые направления атак на вас со стороны ваших заказчиков? В частности, сегодня мы поговорим про диаграммы, которые пользователь может с лёгкостью необычайной адаптировать под свои изменчивые потребности.

Тестовые данные

В качестве хорошего примера я взял на сайте ЦБ статистику по курсам доллара США, евро и британского фунта с 2005 года по настоящее время. С 01.01.2005 по 18.02.2015 ЦБ 2511 раз устанавливал официальный курс рубля.

Цели

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

1. Наша диаграмма сможет менять так называемый уровень дискретности. Что это такое? Это количество информации в 1 точке графика. Мы можем в 1 точке графика выводить 1 день, а можем, например, выводить 5 дней. Какое же значение я должен использовать? Очень просто - среднюю величину курса валюты за каждые 5 дней. Дискретность в этом случае будет равна 5. Таким образом, она может меняться от 1 до, собственно, 2511, но тогда у меня будет на графике всего одна точка, поэтому я ограничил величину дискретности так, чтобы на графике было хотя бы три точки, то есть 2511/3=837. Мы разрешим пользователю менять дискретность от 1 до 837.

2. Поскольку наш массив данных в соответствии с уровнем дискретности будет разбит на N порций (например, при дискретности 5, этих порций будет 502), то мы разрешим пользователю самостоятельно выбирать, начиная с какой именно порции будет строиться диаграмма. Будем называть это начальная порция.

3. Ну и ещё один параметр, связанный с предыдущим, - ширина окна в порциях, по которому строится диаграмма. Например, при дискретности 5, я хочу посмотреть график, начиная с порции 100 (то есть с 30.12.2006) и при этом ширина окна будет 200 порций (до 30.12.2010).

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

Вот как выглядит конечный результат:

Демонстрация

Скачать файл

Как мы этого добились:

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

2. На листе Calc мы будем готовить данные, на которые уже непосредственно будут ссылаться ряды диаграмм. В частности, вы там можете видеть умную табличку tblWork.

   

   Столбцы таблицы предназначены для следующего:

   • Строка - ряд цифр от 1 до 2511 (размер таблицы tblCUR). Этот столбец можно было бы вычислять формулой, но я от них отказался для увеличения скорости пересчета

   • Порция - номер порции (как вы помните, количество порций у нас может меняться, так как это зависит от уровня дискретности)

   • Начало порции - номер строки таблицы tblCUR, в которой начинается текущая порция

   • Конец порции - соответственно последняя строка текущей порции

   • Дата - дата, извлеченная из tblCUR из первой строки соответствующей порции

   • EUR / GBP / USD - расчёт средней величины по порции данных соответствующей валюты.

3. Слева от tblWork располагаются ряд вспомогательных таблиц для выполнения некоторых необходимых вычислений.

   • Ячейка C3 (именованный диапазон rngPortionSize) хранит текущий уровень дискретности данных. Справа располагаются ячейки, хранящие минимальную (ИД rngPortionSizeMin) и максимальную (ИД rngPortionSizeMax) границы изменения уровня дискретности.

   • Ячейка C4 (ИД rngStartPortion) хранит начальное положение окна диаграммы. Справа аналогично хранятся ИД rngStartPortionMin и rngStartPortionMax

   • Ячейка C5 (ИД rngWindowWidth) - ширина окна диаграммы. Плюс правее ИД rngWindowWidthMin и rngWindowWidthMax.

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

4. А вот на чём хотелось бы остановиться, так это на расчёте среднего значения курса валюты (на примере EUR) для порции данных:

   

   В начале вы видите проверку (функция ЕСЛИ или IF для английской версии) на истинность ИД rngEnabledEUR. Для каждой валюты создан такой ИД и связан с соответствующей галочкой, управляющей видимостью графика валюты на диаграмме. Если rngEnabledEUR ложно, то вместо курса валюты в ячейку подставится константа #Н/Д (#N/A), а по таким вводным линия диаграммы просто исчезнет с экрана (правда, продолжая числиться в легенде). Если ИД rngEnabledEUR истинна, то вычисляется второе условие по ЕСЛИ, где проверяется, что [@Порция]>0, - то есть, что текущая строка является корректной порцией данных. Далее вычисляется средняя величина по динамическому диапазону, который весьма красиво вычисляется функциями ИНДЕКС (INDEX). Подробнее об этом способе можно прочесть тут.

5. Теперь, когда мы сформировали в столбцах Дата, EUR, GBP, USD таблицы tblWork данные для построения диаграммы, мы видим, что соответствующие массивы будут иметь переменное количество строк. Как мы уже обсудили выше, у нас целых 3 параметра, которыми пользователь может оперировать по своему усмотрению, которые меняют и количество порций данных вообще, и стартовую порцию, и количество порций из имеющихся, выводимое на график. Разумеется, это опять задача для динамических именованных диапазонов.

   На примере seriesEUR давайте смотреть, как устроены такие ИД:

   

   Опять мы видим, что диапазон формируется при помощи двух функций ИНДЕКС и двоеточия между ними. Поскольку во вспомогательной таблице у нас расчитан уже параметр rngStartPortion, который указывает нам на номер порции данных, с которой нам необходимо начинать построение, то с его помощью мы через ПОИСКПОЗ (MATCH) находим в столбце tblWork[Порция] нужную ячейку, то есть получаем верхнюю границу ИД seriesEUR. Нижняя граница вычисляется похоже - к уже найденной позиции начала мы прибавляем ширину окна из rngWindowWidth.

6. Создаём лист Chart. На нём создаём пустую диаграмму типа График и через меню Выбрать данные... добавляем в неё наши именованные диапазоны, начинающиеся с приставки series. Их 4 штуки: seriesDate - для оси Х, а остальные три - для рядов данных с курсами валют.

   

   Вот тут внимание - прямо курсу лежат грабли, присыпанные сеном! При указании именованных диапазонов в окне Изменение ряда есть один важный нюанс. Excel отказывается воспринимать ввод диапазона в виде =seriesEUR, а хочет либо =Calc!seriesEUR, либо, как на экране с именем файла. С моей точки зрения это не очевидно для пользователя, такие нюансы приходится запоминать. Причём, вы можете через F3 даже вызвать диалог Вставка имени, но и он тоже вставляет имя, не учитывая этого нюанса.

   

7. Осталось накидать на лист 3 ActiveX полосы прокрутки, 3 чек-бокса и 6 элементов типа подпись. Встроенные элементы управления формой я не люблю - слишком они тупые, сколько раз не пытался их применять, каждый раз натыкаюсь на какое-нибудь ограничение и опять возвращаюсь к ActiveX.

   Тут главное связать наши полосы прокрутки и чек-боксы с соответствующими ячейками на листе Calc. За это отвечает свойство LinkedCell.

   

8. Ну и самый неприятный этап - это прописать VBA код на события, которые генерируют наши элементы управления. Почему неприятный? Потому что во-первых, мне кажется, нет ничего более муторного, чем программирование пользовательского интерфейса, а во-вторых, наши полосы прокрутки могут генерировать такие комбинации дискретности, начальной позиции и ширины окна, которые будут несовместимы друг с другом. Например, если дискретность сделать 5, то у вас количество доступных порций данных сразу сократится в 5 раз (если до этого было 1), а если ваше окно диаграммы при этом стояло хотя бы в середине имевшихся порций, то начальная позиция будет указывать на несуществующие порции данных и произойдёт сбой.

   В связи с этим я придумал один трюк: я свои элементы управления привязал не к ячейкам, которые напрямую управляют параметрами ИД series*, а к копиям этих ячеек. Диапазон B14:E17 у меня функционально дублирует диапазон B2:E5. В B14:E17 я контролирую через формулы, чтобы текущие параметры диаграммы находились в пределах допустимых коридоров (смотри формулы в F15:F17 и результирующий статус в F18). И в случае, если в результате действий пользователя F18 (ИД rngStatus) становится в ЛОЖЬ, то я такие изменения пользователя игнорирую.

   Короче, Склифасовский! Если вам не жизненно важны все эти скучные нюансы, то скопируйте их у меня один-в-один :) Вот код для листа Chart:

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