В данной статье описывается, как с помощью программы Microsoft Excel обрабатывать информацию из детализации телефонных вызовов, получая на выходе векторную диаграмму, которая наглядно отражает данные телефонные вызовы во времени и по дням. Сама по себе данная диаграмма напоминает диаграмму Ганта, которая чаще всего применяется для иллюстрации плана работ по какому-либо проекту.
Диаграмма Ганта представляет собой совокупность горизонтальных полос на плоскости. Горизонтальное направление соответствует величине времени, и данная величина, в общем случае, может быть непрерывной. А в вертикальном направлении данная плоскость разбита на множество горизонтальных зон фиксированной ширины. Для классической диаграммы Ганта, отражающая график работ, каждая такая зона соответствует определённому виду работы (рис. 1). Полосы диаграммы изображаются в пределах данных зон. Полоса, изображённая в конкретной зоне, характеризует соответствующий данной зоне вид работы, а левая и правая границы полосы характеризуют соответственно время начала и конца данной работы. Следовательно, длина полосы характеризует продолжительность данной работы.
Рис. 1. Диаграмма Ганта для иллюстрации графика работ.
В случае с диаграммой телефонных звонков, описываемой в данной статье, зоны в вертикальном направлении будут характеризовать дни (сутки). При этом по горизонтали шкала времени диаграммы соответствует интервалу от 0 до 24 часов, протяжённостью в одни сутки. Каждая полоса на такой диаграмме будет соответствовать одному телефонному звонку. Левая и правая границы полосы – время начала и конца вызова, а номер зоны (по вертикали) – день, когда был произведён вызов. Диаграмма подобной конфигурации позволяет наглядно проиллюстрировать и оценить, насколько часто совершаются звонки, оценить их среднюю продолжительность, распределение по времени суток и т.д. Более того, к данной диаграмме можно добавить ещё одно свойство: цвет полосы. Раскрашивать полосы можно по разным признакам. Во-первых – по типу вызова (входящий или исходящий). Во-вторых – по телефонному номеру вызова. В первом случае достаточно двух цветов. Во втором – гораздо больше, но, как правило, достаточно не более десятка цветов на самые популярные телефонные номера, фигурирующие в вызовах чаще всего. В данной статье описывается формирование диаграммы за период в пять календарных месяцев и с учётом наличия двух мобильных операторов (двухсимочного телефона). Цвета полос на диаграмме будут выбираться по признаку «SIM1/SIM2 входящий/исходящий», то есть, потребуется четыре различных цвета.
Формирование диаграммы, в отличие от построения, предусматривает именно генерацию выходного файла с данной диаграммой. А что касается построения, то, как правило, построение диаграммы в Excel подразумевало бы соответствующую операцию именно в Excel одним из стандартных средств. Даже если такая операция и возможна (диаграмма Ганта), то она вряд ли будет удобной в отображении и масштабировании на больших объёмах входных данных. В случае с формированием файла векторного формата SVG с подобной диаграммой программа Excel применяется в качестве программного инструмента, где удобно работать с табличными данными. Вместо Excel можно было написать стороннюю отдельную программу и формировать SVG файл с помощью неё. Но Excel в данном случае я выбрал не случайно. Во-первых, в своём роде, имеется некая наглядность обработки информации, а во-вторых – специфичность выходного формата SVG.
Данный формат является форматом масштабируемой векторной графики и содержит внутри текстовые данные, форматированные по принципу XML. Это своеобразный язык разметки, содержащий определённый набор команд и параметров, характерные для рисования того или иного графического элемента. Команды, к примеру, могут быть такие: нарисовать линию, многоугольник, окружность, написать текст. А параметры – координаты углов многоугольника, цвет заливки, размер и шрифт текста и т.д. По сути, зная язык разметки SVG, можно с помощью обычного текстового редактора (Блокнот) вручную создать ту или иную картинку из разряда простейших. SVG файлы для просмотра можно открыть любым распространённым Интернет браузером.
Прежде чем приступить к формированию SVG диаграммы, необходимо не только скачать детализации вызовов с сайтов мобильных операторов, но и предварительно их обработать. Как я уже отмечал, будут рассмотрены два мобильных оператора. Один из них – «Теле2», другой – «Мегафон». Детализация звонков Теле2, которую можно скачать в личном кабинете на соответствующем сайте, представляет собой PDF документ с большой таблицей, которая поделена на страницы (рис. 2).
Рис. 2. Вид детализации вызовов «Теле2».
В случае с Мегафоном – всё практически аналогично, за исключением, что детализация представлена в XLS (Excel) файле (рис. 3).
Рис. 3. Вид детализации вызовов «Мегафон».
И ту, и другую детализацию необходимо по-разному обработать, отсеить лишнее и привести в порядок. Данный текст имеет некую «регулярность», поэтому легко подвергается автоматической обработке. Её я производил в отдельном документе с помощью функций (формул) Excel. Думаю, что не стоит останавливаться подробно на данном вопросе. В результате такой обработки получилась аккуратная большая таблица с минимально необходимыми полями: дата, время, длительность, тип звонка, номер телефона, симкарта (рис. 4). Всего получилось 2102 записи телефонных вызовов. Кстати, на рисунке 3, где изображён лист Excel с исходным текстом детализации, можно видеть наличие других листов. Данные листы я добавил как раз для реализации промежуточных этапов обработки, как продолжение исходного документа.
Рис. 4. Смешанная детализация, приведённая в порядок.
Получившуюся таблицу я скопировал в новый документ на лист «A», тут же дополнив её дополнительными полями: адрес цвета полосы, левая граница полосы (a) (в секундах от начала суток), правая граница полосы (b) (рис. 5).
Рис. 5. Дополнительные параметры на первом листе.
Данные поля легко рассчитываются при помощи формул Excel. Адрес цвета указывает на один из четырёх адресов ячеек конфигурационного листа «C», в которых он прописан в HEX-RGB формате. На данном листе указаны не только цвета, но и все дополнительные параметры SVG документа: координаты, сдвиги, масштаб и т.д. (рис. 6).
Рис. 6. Лист с параметрами.
Кроме полос на диаграмме будут отражены дополнительные данные: выделение четырёх самых частых телефонных номеров отдельной меткой на полосе, гистограмма распределения частоты телефонных вызовов во времени, а также, информация о диаграмме.
Забегая вперёд, диаграмма получилась размером 4420 на 1800 пикселей. На самом деле сложно говорить о пикселях в векторной графике, но в описании SVG формата присутствует дискретная система координат, отсчёты которой я называю пикселями. А вообще, даже исходя из аббревиатуры, данная графика масштабируемая. Как я уже писал, диаграмма будет отражать звонки за 5 месяцев, а именно – с мая по сентябрь включительно. Если посчитать, это соответствует 153 суткам. Ровно столько должно быть зон для полос на диаграмме. Заранее я определился с масштабами. По вертикали я решил отвести 10 пикселей на одну зону. При этом ширина полосы в зоне будет 8 пикселей, (с зазором в один пиксель сверху и снизу). Величиной зазора (отступа) в ячейке B8 листа «C» можно регулировать ширину полос в зоне. Масштаб по горизонтали можно выбрать, в принципе, любой, однако имеет место быть практическая наглядность диаграммы, приемлемое соотношение её сторон и вместимость. В итоге я решил взять 3 пикселя за протяжённость одной минуты, или иными словами, 20 секунд на пиксель.
Итого, активная область диаграммы имеет следующие размеры. По горизонтали: 24*60*3=4320; по вертикали: 153*10=1530. Слева на диаграмме напротив каждой зоны должно быть написано её название. Названия зон полностью соответствуют датам. Для этой цели я решил отвести место шириной 100 пикселей. Вверху над диаграммой желательно (для удобства) написать метки времени, хотя бы часы. А внизу под диаграммой будет располагаться гистограмма, про которую я писал выше, а также дополнительная информация. Для этих целей я отвёл 270 пикселей, округлив высоту всей диаграммы до 1800. Кроме всего сказанного, на диаграмме я решил отразить светлые горизонтальные линии между зонами (днями), чуть более тёмными – между неделями, а чёрными – между месяцами. Кроме горизонтальных линий присутствуют также и вертикальные, расставленные через каждый час – для границ часов. И ещё одна немаловажная деталь. На левой границе каждой изображаемой цветной полосы будет изображаться чёрная метка её начала в виде квадратной открывающейся скобки. Это нужно для того, чтобы предотвратить слияние двух полос, которые могут соответствовать подряд идущим телефонным вызовам.
Основная обработка информации происходит на листе «B» (рис. 7). Там можно видеть кучу «лишних» промежуточных столбов, значения ячеек которых можно было рассчитать «в уме» или сразу же учесть в окончательной формуле. Это касается координат углов каждой полосы. Однако мне это всё показалось очень громоздким, что на первых порах можно было запутаться.
Рис. 7. Лист с основными расчётами.
В колонке «A» извлекается номер дня (зоны) из даты вызова. В колонке «B» – время вызова в секундах от начала суток. Это такое же значение, что и в колонке «I» листа «C». В колонке «C» – округлённая в большую сторону продолжительность вызова в минутах. Здесь стоит сделать оговорку, для чего введена такая «неточность». Казалось бы, нужно брать продолжительность вызова с точностью до 20 сек, то есть до одного пикселя диаграммы (исходя из принятого масштаба). Однако очевидно, что очень короткие полосы шириной в 1-2 пикселя будут плохо отображаться на диаграмме. Поэтому минимальная длина полосы будет соответствовать хотя бы как минимум трём пикселям. И вообще, длина любой полосы будет кратна трём. За счёт округления длительности вызова в большую сторону (с точностью до минуты) диаграмма будет слегка «переполненной», по сравнению с реальной ситуацией, однако это переполнение весьма незначительное. В колонке «D» с помощью формулы «ДВССЫЛ» извлекается значение цвета из параметров (Лист «C») по рассчитанному адресу на листе «A». Далее – рассчитываются координаты углов полосы. Как я уже писал, там много лишних промежуточных расчётов, но я не стал переделывать. В колонке «U» вычисляется наличие выделения и цвет окантовки полосы, если телефонный номер текущего вызова совпадает с одним из четырёх приведённых телефонных номеров для выделения (на листе «C»). Забыл выше написать о том, что в выделяемом вызове на соответствующую полосу накладывается не только метка, но и серый цвет окантовки полосы (этот цвет также можно поменять на листе с параметрами). В обычном случае полоса не имеет окантовки. Наконец, в следующих трёх колонках происходит окончательное формирование текста на языке разметки SVG графики. В данной статье я не буду рассматривать описание и синтаксис данного языка. На самом деле в этом нет ничего сложного, я разобрался за несколько минут. В столбце «V» формируется код, рисующий полосу с окантовкой.
Пример:
В столбце «W» – код для левого края полосы.
Пример:
В столбце «X» – код для отображения текста метки (цифра 1, 2, 3 или 4) только для тех вызовов, где она нужна. Данная избирательность выполняется с помощью формулы «ЕСЛИ(U2<>«none»;…;…)».
Пример текста «3»:
На рисунке 8 представлен скриншот этих трёх столбцов в очень маленьком масштабе, так как иначе продемонстрировать практически невозможно из-за громоздкости текста. Также видно, насколько громоздкой получается запись формулы «СЦЕПИТЬ» со всеми её аргументами.
Рис. 8. Столбцы с результатами основных расчётов.
На листе «Надписи» происходит формирование надписей над диаграммой (метки часов) и слева от диаграммы (даты) (рис. 9). В формулах прописаны параметры шрифтов: размер, стиль, цвет шрифта и окантовки. Основной акцент расчёта – автозаполнение ячеек по датам и часам, расчёт координат положения текста через равномерный шаг.
Рис. 9. Лист, формирующий надписи.
На листе «Границы» формируются все вспомогательные линии диаграммы, служащие границами зон (дат) и часов. На рисунке 10 показан скриншот, где видно формирование горизонтальных линий по зонам. В первых двух столбцах – номер зоны (начиная с нуля) и её относительная вертикальная координата. В третьем столбце формируется код для SVG, рисующий линии. Здесь в формировании кода применяется не только привычная формула «СЦЕПИТЬ», но и две формулы «ЕСЛИ», вложенные одна в другую. Это необходимо для реализации рисования линии трёх различных цветов, в зависимости от ситуации. Как писалось выше, чёрные линии отделяет месяцы, серые – недели, а светло-серые – дни. Последние два цвета задаются на листе «C» в ячейках B17 и C17. В аргументах формулы «ЕСЛИ» присутствуют формулы «ДЕНЬ» и «ОСТАТ». Первая формула распознаёт число из даты, заданной в виде целого числа, которое получено путём смещения значений номера зоны (из первого столбца) на заранее подобранную константу 42491.
В частности, производится проверка на равенство числа из даты с единицей, распознавая тем самым начало нового месяца. Формула «ОСТАТ» применяется для распознавания начала новой недели (классический алгоритм). Во втором аргументе данной формулы стоит число 7, так как в неделе 7 дней. В частности, производится сравнение остатка от деления со значением 1. Этим значением (от 0 до 6) можно регулировать смещение дней недели на диаграмме, и оно подобрано таким образом, чтобы было соответствие реальному календарю. После формирования горизонтальных линий формируются 25 вертикальных линий уже более простым способом (23 линии на каждый час и ещё две граничные).
Рис. 10. Лист, формирующий границы.
На листе «Мелочи» (рис. 11) прописаны формирования дополнительной информации о свойствах диаграммы. В столбцах «B» и «C» прописаны координаты смещения для каждого элемента.
Рис. 11. Лист, формирующий дополнительную информацию.
На вкладке «Занятость» формируется гистограмма распределения плотности звонков во времени (рис. 12). Она представляет собой набор вертикальных линий различных длин, плотно стоящих рядом друг с другом и расположенных прямо под диаграммой. Число таких линий соответствует числу элементов времени (по 20 сек.), а именно 24*60*3=4320.
Рис. 12. Лист, формирующий гистограмму плотности вызовов.
Длина линии (высота столбика гистограммы) в точности соответствует сумме «занятых» элементов времени по всем 153 дням. То есть, если на текущий элемент времени в текущем дне приходится телефонный вызов, то он учитывается в гистограмме. Такой числовой массив я рассчитывал с помощью отдельной простейшей программы на Си. С помощью ячеек Excel такой расчёт сделать невозможно из-за многомерности операций. Можно было воспользоваться VBA, поместив туда соответствующий программный код, но на тот момент времени я этим средством вообще не владел. Код программы для расчёта массива значений гистограммы приведён ниже.
Входными данными программы служит текстовый файл «ab.txt». В данный файл скопированы два столбца из листа «A» значений секунд начала и конца каждого вызова (об этом я уже писал выше, см. рис. 5). Рассчитанные значения массива выводятся в выходной файл «Out.txt». Алгоритм расчёта простой, поэтому описывать его не стоит. Данные из выходного файла копируются в столбец «D» на листе «Занятость». Первые три столбца – условные обозначения элементов интервалов времени и их номер. Столбец «E» – те же значение гистограммы, но масштабированные в 5 раз с округлением до целой части. Это сделано для удобного размещения гистограммы, наглядности и исключения громоздкости. Кроме того, каждое значение смещено на единицу. Это нужно для псевдо прорисовки горизонтальной оси. Даже если значение гистограммы нулевое (что характерно для ночного времени суток), один пиксель гистограммы всё равно будет изображён. Таким образом, будет прорисована ось абсцисс.
Наконец, на листе «Результат» объединены все сформированные коды SVG по каждому листу документа в определённом порядке (надписи и границы – в первую очередь). Данное объединение я произвёл при помощи обычного копирования столбцов вручную (рис. 13). При необходимости можно написать в VBA функцию автоматического экспорта SVG файла, пробегаясь по результирующим столбцам всех листов. В самой первой строке сформирован заголовок файла. В нём указаны, прежде всего, ширина и высота картинки. Самая последняя строка, дописанная вручную, закрывает документ, точнее главный блок svg. Всего получилось около 6800 строк.
Рис. 13. Лист с объединением результатов.
Затем нужно скопировать всё содержимое этого листа в текстовый редактор (я пользовался программой «AkelPad») и сохранить документ в файл с расширением svg в кодировке UTF-8. После этого, при отсутствии ошибок, файл открывается в Интернет браузере для просмотра. На рисунках ниже приведены виды различных участков получившейся картинки с различными масштабами.
Рис. 14. Общий вид получившейся диаграммы в Chrome.
Рис. 15. Левый верхний угол диаграммы (виды различных границ и названия зон).
Рис. 16. Полосы диаграммы с метками.
Рис. 17. Полосы диаграммы и гистограмма под ними.
Рис. 18. Дополнительная информация на диаграмме.
Рис. 19. Полосы диаграммы и метки часов над ними.
Диаграмма Ганта представляет собой совокупность горизонтальных полос на плоскости. Горизонтальное направление соответствует величине времени, и данная величина, в общем случае, может быть непрерывной. А в вертикальном направлении данная плоскость разбита на множество горизонтальных зон фиксированной ширины. Для классической диаграммы Ганта, отражающая график работ, каждая такая зона соответствует определённому виду работы (рис. 1). Полосы диаграммы изображаются в пределах данных зон. Полоса, изображённая в конкретной зоне, характеризует соответствующий данной зоне вид работы, а левая и правая границы полосы характеризуют соответственно время начала и конца данной работы. Следовательно, длина полосы характеризует продолжительность данной работы.
Рис. 1. Диаграмма Ганта для иллюстрации графика работ.
В случае с диаграммой телефонных звонков, описываемой в данной статье, зоны в вертикальном направлении будут характеризовать дни (сутки). При этом по горизонтали шкала времени диаграммы соответствует интервалу от 0 до 24 часов, протяжённостью в одни сутки. Каждая полоса на такой диаграмме будет соответствовать одному телефонному звонку. Левая и правая границы полосы – время начала и конца вызова, а номер зоны (по вертикали) – день, когда был произведён вызов. Диаграмма подобной конфигурации позволяет наглядно проиллюстрировать и оценить, насколько часто совершаются звонки, оценить их среднюю продолжительность, распределение по времени суток и т.д. Более того, к данной диаграмме можно добавить ещё одно свойство: цвет полосы. Раскрашивать полосы можно по разным признакам. Во-первых – по типу вызова (входящий или исходящий). Во-вторых – по телефонному номеру вызова. В первом случае достаточно двух цветов. Во втором – гораздо больше, но, как правило, достаточно не более десятка цветов на самые популярные телефонные номера, фигурирующие в вызовах чаще всего. В данной статье описывается формирование диаграммы за период в пять календарных месяцев и с учётом наличия двух мобильных операторов (двухсимочного телефона). Цвета полос на диаграмме будут выбираться по признаку «SIM1/SIM2 входящий/исходящий», то есть, потребуется четыре различных цвета.
Формирование диаграммы, в отличие от построения, предусматривает именно генерацию выходного файла с данной диаграммой. А что касается построения, то, как правило, построение диаграммы в Excel подразумевало бы соответствующую операцию именно в Excel одним из стандартных средств. Даже если такая операция и возможна (диаграмма Ганта), то она вряд ли будет удобной в отображении и масштабировании на больших объёмах входных данных. В случае с формированием файла векторного формата SVG с подобной диаграммой программа Excel применяется в качестве программного инструмента, где удобно работать с табличными данными. Вместо Excel можно было написать стороннюю отдельную программу и формировать SVG файл с помощью неё. Но Excel в данном случае я выбрал не случайно. Во-первых, в своём роде, имеется некая наглядность обработки информации, а во-вторых – специфичность выходного формата SVG.
Данный формат является форматом масштабируемой векторной графики и содержит внутри текстовые данные, форматированные по принципу XML. Это своеобразный язык разметки, содержащий определённый набор команд и параметров, характерные для рисования того или иного графического элемента. Команды, к примеру, могут быть такие: нарисовать линию, многоугольник, окружность, написать текст. А параметры – координаты углов многоугольника, цвет заливки, размер и шрифт текста и т.д. По сути, зная язык разметки SVG, можно с помощью обычного текстового редактора (Блокнот) вручную создать ту или иную картинку из разряда простейших. SVG файлы для просмотра можно открыть любым распространённым Интернет браузером.
Прежде чем приступить к формированию SVG диаграммы, необходимо не только скачать детализации вызовов с сайтов мобильных операторов, но и предварительно их обработать. Как я уже отмечал, будут рассмотрены два мобильных оператора. Один из них – «Теле2», другой – «Мегафон». Детализация звонков Теле2, которую можно скачать в личном кабинете на соответствующем сайте, представляет собой PDF документ с большой таблицей, которая поделена на страницы (рис. 2).
Рис. 2. Вид детализации вызовов «Теле2».
В случае с Мегафоном – всё практически аналогично, за исключением, что детализация представлена в XLS (Excel) файле (рис. 3).
Рис. 3. Вид детализации вызовов «Мегафон».
И ту, и другую детализацию необходимо по-разному обработать, отсеить лишнее и привести в порядок. Данный текст имеет некую «регулярность», поэтому легко подвергается автоматической обработке. Её я производил в отдельном документе с помощью функций (формул) Excel. Думаю, что не стоит останавливаться подробно на данном вопросе. В результате такой обработки получилась аккуратная большая таблица с минимально необходимыми полями: дата, время, длительность, тип звонка, номер телефона, симкарта (рис. 4). Всего получилось 2102 записи телефонных вызовов. Кстати, на рисунке 3, где изображён лист Excel с исходным текстом детализации, можно видеть наличие других листов. Данные листы я добавил как раз для реализации промежуточных этапов обработки, как продолжение исходного документа.
Рис. 4. Смешанная детализация, приведённая в порядок.
Получившуюся таблицу я скопировал в новый документ на лист «A», тут же дополнив её дополнительными полями: адрес цвета полосы, левая граница полосы (a) (в секундах от начала суток), правая граница полосы (b) (рис. 5).
Рис. 5. Дополнительные параметры на первом листе.
Данные поля легко рассчитываются при помощи формул Excel. Адрес цвета указывает на один из четырёх адресов ячеек конфигурационного листа «C», в которых он прописан в HEX-RGB формате. На данном листе указаны не только цвета, но и все дополнительные параметры SVG документа: координаты, сдвиги, масштаб и т.д. (рис. 6).
Рис. 6. Лист с параметрами.
Кроме полос на диаграмме будут отражены дополнительные данные: выделение четырёх самых частых телефонных номеров отдельной меткой на полосе, гистограмма распределения частоты телефонных вызовов во времени, а также, информация о диаграмме.
Забегая вперёд, диаграмма получилась размером 4420 на 1800 пикселей. На самом деле сложно говорить о пикселях в векторной графике, но в описании SVG формата присутствует дискретная система координат, отсчёты которой я называю пикселями. А вообще, даже исходя из аббревиатуры, данная графика масштабируемая. Как я уже писал, диаграмма будет отражать звонки за 5 месяцев, а именно – с мая по сентябрь включительно. Если посчитать, это соответствует 153 суткам. Ровно столько должно быть зон для полос на диаграмме. Заранее я определился с масштабами. По вертикали я решил отвести 10 пикселей на одну зону. При этом ширина полосы в зоне будет 8 пикселей, (с зазором в один пиксель сверху и снизу). Величиной зазора (отступа) в ячейке B8 листа «C» можно регулировать ширину полос в зоне. Масштаб по горизонтали можно выбрать, в принципе, любой, однако имеет место быть практическая наглядность диаграммы, приемлемое соотношение её сторон и вместимость. В итоге я решил взять 3 пикселя за протяжённость одной минуты, или иными словами, 20 секунд на пиксель.
Итого, активная область диаграммы имеет следующие размеры. По горизонтали: 24*60*3=4320; по вертикали: 153*10=1530. Слева на диаграмме напротив каждой зоны должно быть написано её название. Названия зон полностью соответствуют датам. Для этой цели я решил отвести место шириной 100 пикселей. Вверху над диаграммой желательно (для удобства) написать метки времени, хотя бы часы. А внизу под диаграммой будет располагаться гистограмма, про которую я писал выше, а также дополнительная информация. Для этих целей я отвёл 270 пикселей, округлив высоту всей диаграммы до 1800. Кроме всего сказанного, на диаграмме я решил отразить светлые горизонтальные линии между зонами (днями), чуть более тёмными – между неделями, а чёрными – между месяцами. Кроме горизонтальных линий присутствуют также и вертикальные, расставленные через каждый час – для границ часов. И ещё одна немаловажная деталь. На левой границе каждой изображаемой цветной полосы будет изображаться чёрная метка её начала в виде квадратной открывающейся скобки. Это нужно для того, чтобы предотвратить слияние двух полос, которые могут соответствовать подряд идущим телефонным вызовам.
Основная обработка информации происходит на листе «B» (рис. 7). Там можно видеть кучу «лишних» промежуточных столбов, значения ячеек которых можно было рассчитать «в уме» или сразу же учесть в окончательной формуле. Это касается координат углов каждой полосы. Однако мне это всё показалось очень громоздким, что на первых порах можно было запутаться.
Рис. 7. Лист с основными расчётами.
В колонке «A» извлекается номер дня (зоны) из даты вызова. В колонке «B» – время вызова в секундах от начала суток. Это такое же значение, что и в колонке «I» листа «C». В колонке «C» – округлённая в большую сторону продолжительность вызова в минутах. Здесь стоит сделать оговорку, для чего введена такая «неточность». Казалось бы, нужно брать продолжительность вызова с точностью до 20 сек, то есть до одного пикселя диаграммы (исходя из принятого масштаба). Однако очевидно, что очень короткие полосы шириной в 1-2 пикселя будут плохо отображаться на диаграмме. Поэтому минимальная длина полосы будет соответствовать хотя бы как минимум трём пикселям. И вообще, длина любой полосы будет кратна трём. За счёт округления длительности вызова в большую сторону (с точностью до минуты) диаграмма будет слегка «переполненной», по сравнению с реальной ситуацией, однако это переполнение весьма незначительное. В колонке «D» с помощью формулы «ДВССЫЛ» извлекается значение цвета из параметров (Лист «C») по рассчитанному адресу на листе «A». Далее – рассчитываются координаты углов полосы. Как я уже писал, там много лишних промежуточных расчётов, но я не стал переделывать. В колонке «U» вычисляется наличие выделения и цвет окантовки полосы, если телефонный номер текущего вызова совпадает с одним из четырёх приведённых телефонных номеров для выделения (на листе «C»). Забыл выше написать о том, что в выделяемом вызове на соответствующую полосу накладывается не только метка, но и серый цвет окантовки полосы (этот цвет также можно поменять на листе с параметрами). В обычном случае полоса не имеет окантовки. Наконец, в следующих трёх колонках происходит окончательное формирование текста на языке разметки SVG графики. В данной статье я не буду рассматривать описание и синтаксис данного языка. На самом деле в этом нет ничего сложного, я разобрался за несколько минут. В столбце «V» формируется код, рисующий полосу с окантовкой.
Пример:
<path fill="#FF5050" stroke="#808080" d="M1598,51L1598,59L1601,59L1601,51L1598,51" style="stroke-width: 1px;" stroke-width="1" stroke-dasharray="0"></path>.
В столбце «W» – код для левого края полосы.
Пример:
<path fill="none" stroke="black" d="M1599,52L1598,52L1598,58L1599,58" style="stroke-width: 1px;" stroke-width="1" stroke-dasharray="0"></path>.
В столбце «X» – код для отображения текста метки (цифра 1, 2, 3 или 4) только для тех вызовов, где она нужна. Данная избирательность выполняется с помощью формулы «ЕСЛИ(U2<>«none»;…;…)».
Пример текста «3»:
<text x="1601" y="58" style="text-anchor: middle; font-family: times; font-weight: bolder; font-size: 8px;" stroke="none" fill="black"><tspan>3</tspan></text>.
На рисунке 8 представлен скриншот этих трёх столбцов в очень маленьком масштабе, так как иначе продемонстрировать практически невозможно из-за громоздкости текста. Также видно, насколько громоздкой получается запись формулы «СЦЕПИТЬ» со всеми её аргументами.
Рис. 8. Столбцы с результатами основных расчётов.
На листе «Надписи» происходит формирование надписей над диаграммой (метки часов) и слева от диаграммы (даты) (рис. 9). В формулах прописаны параметры шрифтов: размер, стиль, цвет шрифта и окантовки. Основной акцент расчёта – автозаполнение ячеек по датам и часам, расчёт координат положения текста через равномерный шаг.
Рис. 9. Лист, формирующий надписи.
На листе «Границы» формируются все вспомогательные линии диаграммы, служащие границами зон (дат) и часов. На рисунке 10 показан скриншот, где видно формирование горизонтальных линий по зонам. В первых двух столбцах – номер зоны (начиная с нуля) и её относительная вертикальная координата. В третьем столбце формируется код для SVG, рисующий линии. Здесь в формировании кода применяется не только привычная формула «СЦЕПИТЬ», но и две формулы «ЕСЛИ», вложенные одна в другую. Это необходимо для реализации рисования линии трёх различных цветов, в зависимости от ситуации. Как писалось выше, чёрные линии отделяет месяцы, серые – недели, а светло-серые – дни. Последние два цвета задаются на листе «C» в ячейках B17 и C17. В аргументах формулы «ЕСЛИ» присутствуют формулы «ДЕНЬ» и «ОСТАТ». Первая формула распознаёт число из даты, заданной в виде целого числа, которое получено путём смещения значений номера зоны (из первого столбца) на заранее подобранную константу 42491.
В частности, производится проверка на равенство числа из даты с единицей, распознавая тем самым начало нового месяца. Формула «ОСТАТ» применяется для распознавания начала новой недели (классический алгоритм). Во втором аргументе данной формулы стоит число 7, так как в неделе 7 дней. В частности, производится сравнение остатка от деления со значением 1. Этим значением (от 0 до 6) можно регулировать смещение дней недели на диаграмме, и оно подобрано таким образом, чтобы было соответствие реальному календарю. После формирования горизонтальных линий формируются 25 вертикальных линий уже более простым способом (23 линии на каждый час и ещё две граничные).
Рис. 10. Лист, формирующий границы.
На листе «Мелочи» (рис. 11) прописаны формирования дополнительной информации о свойствах диаграммы. В столбцах «B» и «C» прописаны координаты смещения для каждого элемента.
Рис. 11. Лист, формирующий дополнительную информацию.
На вкладке «Занятость» формируется гистограмма распределения плотности звонков во времени (рис. 12). Она представляет собой набор вертикальных линий различных длин, плотно стоящих рядом друг с другом и расположенных прямо под диаграммой. Число таких линий соответствует числу элементов времени (по 20 сек.), а именно 24*60*3=4320.
Рис. 12. Лист, формирующий гистограмму плотности вызовов.
Длина линии (высота столбика гистограммы) в точности соответствует сумме «занятых» элементов времени по всем 153 дням. То есть, если на текущий элемент времени в текущем дне приходится телефонный вызов, то он учитывается в гистограмме. Такой числовой массив я рассчитывал с помощью отдельной простейшей программы на Си. С помощью ячеек Excel такой расчёт сделать невозможно из-за многомерности операций. Можно было воспользоваться VBA, поместив туда соответствующий программный код, но на тот момент времени я этим средством вообще не владел. Код программы для расчёта массива значений гистограммы приведён ниже.
#include <stdio.h>
#include <windows.h>
int main(){
int a,b,n,c,k;
int q[4320];
for(n=0;n<4320;n++){
q[n]=0;
}
FILE *f,*f1;
f=fopen("ab.txt","r");
f1=fopen("Out.txt","w");
for(c=0;c<2102;c++){
fscanf(f,"%i\t%i\n",&a,&b);
for(k=a;k<b;k++){
q[k/20]+=1;
}
}
for(n=0;n<4320;n++){
fprintf(f1,"%i\n",q[n]);
}
fclose(f);
fclose(f1);
system("PAUSE");
return 0;
}
Входными данными программы служит текстовый файл «ab.txt». В данный файл скопированы два столбца из листа «A» значений секунд начала и конца каждого вызова (об этом я уже писал выше, см. рис. 5). Рассчитанные значения массива выводятся в выходной файл «Out.txt». Алгоритм расчёта простой, поэтому описывать его не стоит. Данные из выходного файла копируются в столбец «D» на листе «Занятость». Первые три столбца – условные обозначения элементов интервалов времени и их номер. Столбец «E» – те же значение гистограммы, но масштабированные в 5 раз с округлением до целой части. Это сделано для удобного размещения гистограммы, наглядности и исключения громоздкости. Кроме того, каждое значение смещено на единицу. Это нужно для псевдо прорисовки горизонтальной оси. Даже если значение гистограммы нулевое (что характерно для ночного времени суток), один пиксель гистограммы всё равно будет изображён. Таким образом, будет прорисована ось абсцисс.
Наконец, на листе «Результат» объединены все сформированные коды SVG по каждому листу документа в определённом порядке (надписи и границы – в первую очередь). Данное объединение я произвёл при помощи обычного копирования столбцов вручную (рис. 13). При необходимости можно написать в VBA функцию автоматического экспорта SVG файла, пробегаясь по результирующим столбцам всех листов. В самой первой строке сформирован заголовок файла. В нём указаны, прежде всего, ширина и высота картинки. Самая последняя строка, дописанная вручную, закрывает документ, точнее главный блок svg. Всего получилось около 6800 строк.
Рис. 13. Лист с объединением результатов.
Затем нужно скопировать всё содержимое этого листа в текстовый редактор (я пользовался программой «AkelPad») и сохранить документ в файл с расширением svg в кодировке UTF-8. После этого, при отсутствии ошибок, файл открывается в Интернет браузере для просмотра. На рисунках ниже приведены виды различных участков получившейся картинки с различными масштабами.
Рис. 14. Общий вид получившейся диаграммы в Chrome.
Рис. 15. Левый верхний угол диаграммы (виды различных границ и названия зон).
Рис. 16. Полосы диаграммы с метками.
Рис. 17. Полосы диаграммы и гистограмма под ними.
Рис. 18. Дополнительная информация на диаграмме.
Рис. 19. Полосы диаграммы и метки часов над ними.
