Отчёт по биллингу офисной АТС с помощью VBA и сводных таблиц Excel

Оставлен Adm

Статья даёт ответы на следующие вопросы:

  • Как подготовить сводный отчёт в Excel по данным биллинга офисной АТС?
  • Как осуществлять преобразование данных Excel средствами VBA?

Постановка задачи

Необходимо обработать «сырую» информацию биллинга офисной АТС, построив сводный отчёт по количеству выговоренных минут при внешних вызовах по группам внутренних абонентов (перечень групп и номера внутренних абонентов этих групп прилагаются) с делением вызовов на типы: городские, федеральные, мобильные, короткие, междугородние.

Исходные данные

Таблица Microsoft Excel с "сырыми" необработанными данными биллинга АТС следующего формата.

Таблица Microsoft Excel с необработанными данными биллинга АТС

Данные по группам внутренних абонентов с перечислением входящих в них номеров:
group1 (1008, 1546, 1548, 1552), group2 (1019, 1028, 1029, 1100, 1223, 1311).

Перед началом работы необходимо убедиться, что заголовки колонок отображаются как числа, а не как буквы. Для того, чтобы отобразить числовые заголовки колонок, необходимо выполнить следующие действия: меню Файл - Параметры - Формулы - Работа с формулами - установить флаг "Стиль ссылок R1C1".

Обрабатываемые показатели

Extension – внутренний номер сотрудника.

cNumber – номер, на который сотрудник осуществлял вызов.

cDuration – длительность вызова.

Алгоритм решения задачи

Алгоритм включает в себя несколько этапов.
  1. Обработать имеющуюся плоскую таблицу Excel:
    1. Добавить в конец таблицы колонки: Тип (тип вызываемого номера) - № 11; Группа (группа внутреннего абонента) - № 12; Длительность (минут) - № 13.
    2. Разработать в составе рабочей книги Excel VBA макрос обработки данных и заполнения созданных колонок.
    3. Запустить макрос на выполнение (Лента функций – Вкладка Вид – Макросы – Макросы – выбрать созданный макрос – Выполнить.
  2. Построить в составе рабочей книги Excel сводную таблицу на основе результирующих данных.

Разработка макроса

Создадим новый макрос в рабочей книге Excel. Лента функций – Вид – Макросы – Запись макроса – Лента функций – Вид Макросы – Остановить запись.

Откроем код процедуры созданного макроса в редакторе Microsoft Visual Basic for Applications. Лента функций – Вид – Макросы – Макросы – выбрать созданный макрос – Изменить.

Окно редактора Microsoft Visual Basic for Applications с кодом вновь созданного макроса

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

    ' определение строковых массивов для хранения информации о составе групп абонентов
    ' в каждую группу включен набор коротких внутренних номеров абонентов
    Dim group1(4) As String
    group1(1) = "1008"
    group1(2) = "1546"
    group1(3) = "1548"
    group1(4) = "1552"
        
    Dim group2(6) As String
    group2(1) = "1019"
    group2(2) = "1028"
    group2(3) = "1029"
    group2(4) = "1100"
    group2(5) = "1223"
    group2(6) = "1311"

Определим переменные для счётчиков циклов (при последующем обходе таблицы) и для хранения временных данных. Инициализируем начальное значение счётчика цикла по перебору строк таблицы с данными.

' счетчики циклов
Dim i As Long
Dim j As Integer
    
' переменная для операций со строковыми значениями
Dim str As String
   
' переменные для разбора значения длительности вызова на часы, минуты, секунды
Dim hh As Integer
Dim mm As Integer
Dim ss As Integer
' переменная для вычисления итоговой длительности вызова в минутах
Dim tm As Long 
' инициализация начального значения счётчика цикла
i = 2

Реализуем перебор в цикле всех строк таблицы с данными.

Do While Not (IsEmpty(ActiveSheet.Cells(i, 1).Value))
' тело цикла

i = i + 1
Loop

Для i-ой строки данных осуществим проверку на принадлежность внутреннего номера абонента к одной из групп абонентов. Добавим в тело главного цикла следующие строки.

 For j = 0 To UBound(group1)
        If (Cells(i, 3).Value = group1(j)) Then
            ActiveSheet.Cells(i, 12).Value = "group1"
        End If
       Next j
       
       For j = 0 To UBound(group2)
        If (Cells(i, 3).Value = group2(j)) Then
            ActiveSheet.Cells(i, 12).Value = "group2"
        End If
 Next j

Для строки данных рассчитаем длительность вызова. Округлим секунды в большую сторону до 1 минуты. Добавим следующие строки в тело главного цикла.

       hh = Int(Left(ActiveSheet.Cells(i, 8), 1))
       mm = Int(Mid(ActiveSheet.Cells(i, 8), 3, 2))
       ss = Int(Right(ActiveSheet.Cells(i, 8), 2))
       tm = mm + hh * 60
       If (ss > 0) Then
        tm = tm + 1
       End If
       ActiveSheet.Cells(i, 13).Value = tm

Определим тип вызываемого номера. Добавим следующие строки в тело главного цикла.

  If (Len(ActiveSheet.Cells(i, 10)) < 7) Then
            ActiveSheet.Cells(i, 11).Value = "short"
       Else
            ' определяем начальный символ
            str = Left(ActiveSheet.Cells(i, 10), 1)
            If (Left(ActiveSheet.Cells(i, 10), 4) = "8499" Or Left(ActiveSheet.Cells(i, 10), 4) = "8495" Or Left(ActiveSheet.Cells(i, 10), 7) = "8-(495)" Or Left(ActiveSheet.Cells(i, 10), 7) = "8-(499)") Then
                ActiveSheet.Cells(i, 11).Value = "city"
            ElseIf (Left(ActiveSheet.Cells(i, 10), 7) = "8-(800)") Or (Left(ActiveSheet.Cells(i, 10), 4) = "8800") Then
                ActiveSheet.Cells(i, 11).Value = "federal"
            ElseIf (Left(ActiveSheet.Cells(i, 10), 4) = "8-(9") Or (Left(ActiveSheet.Cells(i, 10), 2) = "89") Then
                ActiveSheet.Cells(i, 11).Value = "mobile"
            Else
                ActiveSheet.Cells(i, 11).Value = "roaming"
            End If
End If

Итоговый код процедуры макроса

Sub Макрос1()
'
' Макрос1 Макрос
'
    ' определение строковых массивов для хранения информации о составе групп абонентов
    ' в каждую группу включен набор коротких внутренних номеров абонентов
    Dim group1(4) As String
    group1(1) = "1008"
    group1(2) = "1546"
    group1(3) = "1548"
    group1(4) = "1552"
         
    Dim group2(6) As String
    group2(1) = "1019"
    group2(2) = "1028"
    group2(3) = "1029"
    group2(4) = "1100"
    group2(5) = "1223"
    group2(6) = "1311"

    ' счетчики циклов
    Dim i As Long
    Dim j As Integer
    
    ' переменная для операций со строковыми значениями
    Dim str As String
   
    ' переменные для разбора значения длительности вызова на часы, минуты, секунды
    Dim hh As Integer
    Dim mm As Integer
    Dim ss As Integer
    ' переменная для вычисления итоговой длительности вызова в минутах
    Dim tm As Long
    
    i = 2
    Do While Not (IsEmpty(ActiveSheet.Cells(i, 1).Value))
       
       For j = 0 To UBound(group1)
        If (Cells(i, 3).Value = group1(j)) Then
            ActiveSheet.Cells(i, 12).Value = "group1"
        End If
       Next j
       
       For j = 0 To UBound(group2)
        If (Cells(i, 3).Value = group2(j)) Then
            ActiveSheet.Cells(i, 12).Value = "group2"
        End If
       Next j
    
       ' переводим длительность в минуты
       hh = Int(Left(ActiveSheet.Cells(i, 8), 1))
       mm = Int(Mid(ActiveSheet.Cells(i, 8), 3, 2))
       ss = Int(Right(ActiveSheet.Cells(i, 8), 2))
       tm = mm + hh * 60
       If (ss > 0) Then
        tm = tm + 1
       End If
       ActiveSheet.Cells(i, 13).Value = tm
         
       If (Len(ActiveSheet.Cells(i, 10)) < 7) Then
            ActiveSheet.Cells(i, 11).Value = "short"
       Else
            ' определяем начальный символ
            str = Left(ActiveSheet.Cells(i, 10), 1)
            If (Left(ActiveSheet.Cells(i, 10), 4) = "8499" Or Left(ActiveSheet.Cells(i, 10), 4) = "8495" Or Left(ActiveSheet.Cells(i, 10), 7) = "8-(495)" Or Left(ActiveSheet.Cells(i, 10), 7) = "8-(499)") Then
                ActiveSheet.Cells(i, 11).Value = "city"
            ElseIf (Left(ActiveSheet.Cells(i, 10), 7) = "8-(800)") Or (Left(ActiveSheet.Cells(i, 10), 4) = "8800") Then
                ActiveSheet.Cells(i, 11).Value = "federal"
            ElseIf (Left(ActiveSheet.Cells(i, 10), 4) = "8-(9") Or (Left(ActiveSheet.Cells(i, 10), 2) = "89") Then
                ActiveSheet.Cells(i, 11).Value = "mobile"
            Else
                ActiveSheet.Cells(i, 11).Value = "roaming"
            End If
       End If
       i = i + 1
    Loop
    'MsgBox i
End Sub

Построение сводной таблицы

Выделить результирующую таблицу (достаточно выделить область со всеми строками данных, включающую только созданные колонки Тип, Группа, Длительность).

Нажать на панели функций Вставка – Сводная таблица – Ok.

Результирующая таблица работы макроса по обработке данных биллинга АТС

На новом листе будет создана сводная таблица, настроим её.

Перетащить из списка полей сводной таблицы поле Группа в область Названия строк, поле Тип в область Названия столбцов, поле Длительность в область Значения.

Результирующий отчёт в виде сводной таблицы по данным биллинга АТС

Возможные пути оптимизации VBA макроса

Вынести заданные в коде макроса числовые значения позиций колонок (например, ActiveSheet.Cells(i, 8)) в настройки, определив соответствующие переменные в начале кода макроса.

Определять значения переменных с индексами обрабатываемых колонками по заголовкам колонок.

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

Оптимизировать алгоритм определения типов вызываемых номеров.

Формировать сводную таблицу программно с помощью VBA макроса.

Тэги: