Создаем «спарклайны» в Excel 2003
Уважаемая редакция! Недавно я увидел распечатку таблицы, сделанной в программе Excel. В конце каждой строки этой таблицы была специальная ячейка, внутри которой располагалась маленькая встроенная гистограмма из нескольких значений. Подскажите, как создать такой документ? Заранее благодарю.
В. Соколов, г. Харьков
Отвечает Николай КАРПЕНКО, канд. техн. наук, доцент кафедры прикладной математики и информационных технологий Харьковской национальной академии городского хозяйства
Скорее всего, речь идет о таблице, выполненной в программе Excel 2010. Одной из многочисленных «фишек» этой программы являются так называемые «спарклайны» (от англ. sparklines — миниатюрные графики), которые могут быть вставлены в любую ячейку таблицы для графической иллюстрации ее значений. Конечно, реализовать в полном объеме «спарклайны» в Excel 2003 тяжело. Для этого понадобится весьма внушительная программа, причем не одна, а программирование не наша задача. И все же можно предложить компромиссное решение — создать маленькую пользовательскую функцию для формирования мини-гистограммы. Если не делать сложных настроек в этой функции, на ее создание уйдет от силы пара минут. А результат работы будет похож на внешний вид графика в стиле «спарклайн» Excel 2010. Приступим.
В качестве исходных данных я выбрал таблицу «
Продажи», изображенную на рис. 1. В ней собраны данные по объемам продаж четырех моделей компьютеров за I — IV кварталы 2009 года. Наша задача — в ячейках «G3», «G4», «G5», «G6» построить миниатюрные гистограммы, которые покажут динамику продаж каждого наименования ТМЦ по кварталам. Делаем так:
1) вызываем «
Сервис → Макрос → Редактор Visual Basic» или нажимаем «Alt+F11». Появится окно редактора VBA;2) в левом верхнем углу окна редактора находим окошко с заголовком «
Project»;3) в этом окне подсвечиваем строку с надписью «
VBAProject(Продажи.xls)» (рис. 2 на с. 33);
4) вызываем меню «
Insert → Module». В ветке «VBAProject(Продажи.xls)» появится строчка «Modules», а в ней — элемент «Module1» (рис. 2);5) делаем двойной щелчок левой кнопкой мыши на строке «
Module1»;6) справа в окне печатаем такой текст:
Function SpLines(Rng As Range) As String
Const MaxSym = 10
For Each i In Rng
oStr=oStr&WorksheetFunction.Rept("|",i/WorksheetFunction.Max(Rng)*MaxSym)&Chr(10)
Next i
SpLines = Mid(oStr, 1, Len(oStr) - 1)
End Function
7) закрываем окно редактора VBA, возвращаемся к таблице «
Продажи».Важно!
Функция «SpLines()» не работает с отрицательными числами в блоке исходных данных.Мы создали пользовательскую функцию с именем «SpLines». Подробности ее работы нам не нужны. Важнее понять, что она делает. В качестве исходного параметра функция получает диапазон значений «Rng». Второй параметр функции задан в виде константы «MaxSym» — это максимальное количество символов в столбике гистограммы. Функция просматривает все ячейки в диапазоне «Rng». Для блока «B3:E3» нашей таблицы это будут числа «50», «20», «70», «30». Затем функция вычисляет коэффициент масштабирования как отношение текущего значения в ячейке к максимальному числу в диапазоне «Rng». Этот коэффициент она умножает на максимальную высоту гистограммы «MaxSym». Полученное значение — это высота столбика гистограммы для текущей ячейки. Например, в ячейке «B3» записан объем реализации «50». Максимальное число в блоке «B3:E3» (рис. 1) равно «70». Константа «MaxSym» равна «10». Тогда высота столбика в гистограмме будет 50/70*10, т. е. приблизительно 7 единиц. В результате для каждого числа из диапазона «Rng» функция формирует последовательность символов «|», их количество будет равно высоте соответствующего столбика. Например, для значения «50» получится строка из семи символов «|», для значения «70» — из десяти и т. д. Эти строки функция сцепляет между собой, разделяя каждую последовательность символом разрыва строки (в программе это Chr(10)). В результате мы получим строку, где каждый столбик гистограммы будет представлен группой символов «|», а между отдельными группами будет стоять разделитель «010». Например, для чисел «50», «20», «70», «30» строка будет выглядеть так: « | | | | | | | | | | | | | | | | | | | | | | | ». Остается показать этот текст на экране, разместив его по вертикали. И тут есть одно «но» — осуществить такую операцию в программе не так просто, как это может показаться. Но мы применим одну хитрость — воспользуемся для решения задачи параметрами форматирования ячеек. Чтобы воспользоваться нашей функцией, сделаем так:
1) становимся в ячейку «G3»;
2) вызываем меню «Вставка → Функция…»;
3) в категории «Определенные пользователем» находим функцию «SpLines», дважды щелкаем на ней левой кнопкой мыши. Появится окно «Аргументы функции» с единственным параметром «Rng» (рис. 3);
4) ставим курсор в окошко «Rng», обводим блок «B3:E3» на рабочем листе «Продажи»;
5) нажимаем «ОК»;
6) щелкаем правой кнопкой на ячейке «G3», из контекстного меню выбираем «Формат ячеек…». Появится одноименное окно «Формат ячеек»;
7) переходим на закладку «Выравнивание»;
8) включаем флажок «Переносить по словам», параметр «Ориентация» ставим в положение «90 градусов»;
9) в окне «Формат ячеек…» нажимаем «ОК»;
10) копируем содержимое «G3» на всю высоту таблицы. Окончательный результат наших усилий показан на рис. 4. На мой взгляд, очень похоже на график в стиле «спарклайн». Удачной работы!
Жду ваших вопросов, замечаний и предложений на
bk@id.factor.ua , nictomkar@rambler.ru или на форуме редакции www.bk.factor.ua/forum .
