Створюємо «спарклайни» в 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);
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 .
