Анализ детализации мобильной связи (Билайн)

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

Думаю, у каждого в жизни бывает момент, когда после внесения очередной порции денег за мобильную связь, ты говоришь себе - не пора ли уже сменить, наконец, тариф на более выгодный? Выбор, однако, не прост. У каждого из операторов "большой тройки" десятки активных тарифов: лимитные, безлимитные, пред- и постоплатные, с абонентской платой и без. И к каждому из них можно добавить дополнительные услуги в виде пакетов SMS и интернет-траффика, подключения "любимых номеров", внутрисетевого и международного роуминга и т.д.

Чтобы подобрать "правильный" тариф, нужно сначала оценить свою текущую статистику, т.е. определить в среднем за месяц:

  • сколько минут входящих и исходящих звонков вы совершаете
  • на телефоны каких операторов вы чаще всего звоните
  • есть ли номера, на которые вы звоните заметно чаще других (их можно сделать "любимыми")
  • какую долю составляют звонки во внутрисетевом и международном роуминге
  • сколько Мб мобильного интернет-траффика вы тратите

Все мобильные операторы на сегодняшний день легко могут сделать вам подробную детализацию расходов на связь за любой период. Однако, информативность и наглядность таких детализаций оставляют желать лучшего (мягко говоря). И вот тут нам может очень пригодиться Microsoft Excel. Всего несколько несложных действий и парочка формул - и вы точно будете знать - сколько и на что вы тратите.

Все дальнейшие действия будут показаны на примере "Билайна". Для других операторов картина аналогичная, но может отличаться деталями. Телефоны во всех скриншотах и видео изменены, любые совпадения - случайны :)

Шаг 1. Выгружаем детализацию

Идем на www.beeline.ru в Личный Кабинет - Финансовая информация и просим создать детализацию за последний, например, месяц. У Билайна, кстати, недавно обновился дизайн сайта и Личного Кабинета - все стало в разы удобнее и нагляднее. Тема Лебедев, хоть и пафосный товарищ, но дело знает.

beeline1.png

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

beeline2.png

С ходу видно несколько проблем, которые помешают последующему анализу этой выгрузки в Excel:

  • непонятная многострочная шапка (Excel понимает только однострочные, без пустых и объединенных ячеек)
  • длительность звонка и объем потраченного интернет-траффика смешаны в одном столбце и этот столбец содержит не время в формате Excel, а текст, т.е. нельзя посчитать суммарное количество минут входящих и исходящих звонков и суммарный траффик
  • аналогично, три последних столбца с данными по балансу на самом деле содержат не числа, а текст (стоит текстовый формат ячеек, т.е. нельзя посчитать суммарные расходы)

Для окончательного превращения КАМАЗа в истребитель придется-таки "после сборки доработать напильником".

Шаг 2. Доработка детализации

Во-первых, убираем все лишние строки в верхней части листа, оставляя однострочную шапку:

beeline3.png

Во-вторых, превратим псевдочисла в трех последних колонках в нормальные числа, с которыми можно работать. Для этого выделяем все данные в столбцах с балансом и жмем на всплывающий значок с восклицательным знаком - Преобразовать в число (Convert to number):

beeline4.png

В-третьих, добавим справа от таблицы столбец с формулой

=ЕСЛИОШИБКА(ВРЕМЗНАЧ(D2);0)

=IFERROR(TIMEVALUE(D2);0)

которая будет превращать длительность разговора в текстовом виде из столбца D в нормальный формат времени, который понимает Excel. В случае возникновения ошибки преобразования (например, когда в ячейке вместо времени стоит объем траффика), формула будет выдавать ноль.

В-четвертых, выдернем в еще один дополнительный столбец из номера абонента три цифры кода мобильного оператора или города-области с помощью формулы:

=ЕСЛИ(C2="Internet'";0;ПСТР(C2;2;3))

=IF(C2="Internet";0;MID(C2;2;3))

Если в ячейке слово "Internet", то функция ЕСЛИ выдаст ноль. В противном случае текстовая функция ПСТР выдернет из номера три цифры, начиная со второй.

В-пятых, добавим столбец, куда будет выводится название оператора или города, куда мы звонили. Для этого придется использовать небольшой самодельный справочник по кодам на отдельном листе вот такого вида:

beeline5.png

Чтобы подтянуть из него названия операторов и городов по кодам, придется использовать функцию ВПР (VLOOKUP) в отдельном столбце:

=ЕСЛИОШИБКА(ВПР(J2;Лист5!A:B;2;0);0)

=IFERROR(VLOOKUP(J2;Справочник!A:B;2;0);0)

Функция ЕСЛИОШИБКА (IFERROR) нужна, чтобы перехватить ошибку #Н/Д и заменить ее на ноль.

В итоге, после обработки наша таблица должна выглядеть примерно так:

beeline6.png

С подготовительной частью все, можем переходить к отчетам.

Шаг 3. Создание отчетов

Для отчетов проще и удобнее всего будет использовать один из самых мощных и красивых инструментов Microsoft Excel - сводные таблицы. Ставим активную ячейку в нашу таблицу с данными и идем на вкладку Вставка - Сводная таблица (Insert - Pivot Table). В следующем окне жмем ОК и формируем отчет сводной таблицы, перетаскивая названия столбцов (поля) в одну из четырех областей отчета (названия строк, названия столбцов, значения или фильтр отчета):

beeline7.png

Отчет 1. Общие расходы по типу

Забрасываем мышью поле Сервис в область Строки (Row Labels), а поле Изменение баланса в область Значения (Values). Получаем суммарные расходы по типам:

beeline8.png

Из такого отчета понятно сколько денег тратится в общем и на что. На моем тарифном плане входящие и мобильный интернет бесплатны, но у вас картина может быть совсем другой (и весьма неожиданной, кстати). Также по такому отчету хорошо видны расходы на всякие непонятные услуги, которые вы когда-то подключили и - забыли. И возможно найдутся те, которые вы точно не подключали, но они у вас почему-то есть (сюрприз!) Выглядеть они будут, скорее всего, как непонятные аббревиатуры, которые надо обязательно гуглить и проверять. Я недавно нашел у себя парочку таких, кушающих по нескольку рублей в день и успешно их отключил.

Отчет 2. Тайминг входящих и исходящих звонков

Забрасываем в сводной таблице поля Сервис и Место в область Строки, а поля Изменение баланса и Время в область Значения. Щелкаем правой кнопкой мыши по значениям поля Время и выбираем Итоги по - Сумма (Summarize Values By - Sum). Также для столбца с временем можно задать пользовательский формат [мм]:сс, при котором минуты не обнуляются при превышении 60, а накапливаются. Получим:

beeline9.png

Для пущей наглядности дополнительно можно:

  • Отсортировать таблицу, т.е. встать в любую ячейку столбца С и выбрать Данные - Сортировать по возрастанию/убыванию (Data - Sort Ascending/Descending)
  • Применить к числам гистограммы на вкладке Главная - Условное форматирование - Гистограммы (Home - Conditional Formatting - Data Bars).
  • Отфильтровать лишние сервисы и услуги с помощью фильтра в А3.

Из такого отчета ясно видно кто и откуда звонил нам и куда звонили мы (и сколько на это потратили). У меня, повторюсь, входящие бесплатны, но у вас картина будет другая и, возможно, приведет вас к мысли о смене тарифа или даже оператора. Если у вас много звонков по стране или за ее пределами, то стоит подумать о подключении услуг более дешевого внутреннего или международного роуминга.

Ссылки по теме

 



22.12.2018 14:42:28
Подскажите а как добавить формулу общее количество по набираемуму номеру или этому же номеру но входящему(звонки,смс)?
Наверх