Анализ детализации мобильной связи (Билайн)
Постановка задачи
Думаю, у каждого в жизни бывает момент, когда после внесения очередной порции денег за мобильную связь, ты говоришь себе - не пора ли уже сменить, наконец, тариф на более выгодный? Выбор, однако, не прост. У каждого из операторов "большой тройки" десятки активных тарифов: лимитные, безлимитные, пред- и постоплатные, с абонентской платой и без. И к каждому из них можно добавить дополнительные услуги в виде пакетов SMS и интернет-траффика, подключения "любимых номеров", внутрисетевого и международного роуминга и т.д.
Чтобы подобрать "правильный" тариф, нужно сначала оценить свою текущую статистику, т.е. определить в среднем за месяц:
- сколько минут входящих и исходящих звонков вы совершаете
- на телефоны каких операторов вы чаще всего звоните
- есть ли номера, на которые вы звоните заметно чаще других (их можно сделать "любимыми")
- какую долю составляют звонки во внутрисетевом и международном роуминге
- сколько Мб мобильного интернет-траффика вы тратите
Все мобильные операторы на сегодняшний день легко могут сделать вам подробную детализацию расходов на связь за любой период. Однако, информативность и наглядность таких детализаций оставляют желать лучшего (мягко говоря). И вот тут нам может очень пригодиться Microsoft Excel. Всего несколько несложных действий и парочка формул - и вы точно будете знать - сколько и на что вы тратите.
Все дальнейшие действия будут показаны на примере "Билайна". Для других операторов картина аналогичная, но может отличаться деталями. Телефоны во всех скриншотах и видео изменены, любые совпадения - случайны :)
Шаг 1. Выгружаем детализацию
Идем на www.beeline.ru в Личный Кабинет - Финансовая информация и просим создать детализацию за последний, например, месяц. У Билайна, кстати, недавно обновился дизайн сайта и Личного Кабинета - все стало в разы удобнее и нагляднее. Тема Лебедев, хоть и пафосный товарищ, но дело знает.
Скачиваем детализацию (или она приходит вам по почте) и открываем ее в Microsoft Excel. И вот сюда, как видно, нормальные люди с прямыми руками еще не добрались:
С ходу видно несколько проблем, которые помешают последующему анализу этой выгрузки в Excel:
- непонятная многострочная шапка (Excel понимает только однострочные, без пустых и объединенных ячеек)
- длительность звонка и объем потраченного интернет-траффика смешаны в одном столбце и этот столбец содержит не время в формате Excel, а текст, т.е. нельзя посчитать суммарное количество минут входящих и исходящих звонков и суммарный траффик
- аналогично, три последних столбца с данными по балансу на самом деле содержат не числа, а текст (стоит текстовый формат ячеек, т.е. нельзя посчитать суммарные расходы)
Для окончательного превращения КАМАЗа в истребитель придется-таки "после сборки доработать напильником".
Шаг 2. Доработка детализации
Во-первых, убираем все лишние строки в верхней части листа, оставляя однострочную шапку:
Во-вторых, превратим псевдочисла в трех последних колонках в нормальные числа, с которыми можно работать. Для этого выделяем все данные в столбцах с балансом и жмем на всплывающий значок с восклицательным знаком - Преобразовать в число (Convert to number):
В-третьих, добавим справа от таблицы столбец с формулой
=ЕСЛИОШИБКА(ВРЕМЗНАЧ(D2);0)
=IFERROR(TIMEVALUE(D2);0)
которая будет превращать длительность разговора в текстовом виде из столбца D в нормальный формат времени, который понимает Excel. В случае возникновения ошибки преобразования (например, когда в ячейке вместо времени стоит объем траффика), формула будет выдавать ноль.
В-четвертых, выдернем в еще один дополнительный столбец из номера абонента три цифры кода мобильного оператора или города-области с помощью формулы:
=ЕСЛИ(C2="Internet'";0;ПСТР(C2;2;3))
=IF(C2="Internet";0;MID(C2;2;3))
Если в ячейке слово "Internet", то функция ЕСЛИ выдаст ноль. В противном случае текстовая функция ПСТР выдернет из номера три цифры, начиная со второй.
В-пятых, добавим столбец, куда будет выводится название оператора или города, куда мы звонили. Для этого придется использовать небольшой самодельный справочник по кодам на отдельном листе вот такого вида:
Чтобы подтянуть из него названия операторов и городов по кодам, придется использовать функцию ВПР (VLOOKUP) в отдельном столбце:
=ЕСЛИОШИБКА(ВПР(J2;Лист5!A:B;2;0);0)
=IFERROR(VLOOKUP(J2;Справочник!A:B;2;0);0)
Функция ЕСЛИОШИБКА (IFERROR) нужна, чтобы перехватить ошибку #Н/Д и заменить ее на ноль.
В итоге, после обработки наша таблица должна выглядеть примерно так:
С подготовительной частью все, можем переходить к отчетам.
Шаг 3. Создание отчетов
Для отчетов проще и удобнее всего будет использовать один из самых мощных и красивых инструментов Microsoft Excel - сводные таблицы. Ставим активную ячейку в нашу таблицу с данными и идем на вкладку Вставка - Сводная таблица (Insert - Pivot Table). В следующем окне жмем ОК и формируем отчет сводной таблицы, перетаскивая названия столбцов (поля) в одну из четырех областей отчета (названия строк, названия столбцов, значения или фильтр отчета):
Отчет 1. Общие расходы по типу
Забрасываем мышью поле Сервис в область Строки (Row Labels), а поле Изменение баланса в область Значения (Values). Получаем суммарные расходы по типам:
Из такого отчета понятно сколько денег тратится в общем и на что. На моем тарифном плане входящие и мобильный интернет бесплатны, но у вас картина может быть совсем другой (и весьма неожиданной, кстати). Также по такому отчету хорошо видны расходы на всякие непонятные услуги, которые вы когда-то подключили и - забыли. И возможно найдутся те, которые вы точно не подключали, но они у вас почему-то есть (сюрприз!) Выглядеть они будут, скорее всего, как непонятные аббревиатуры, которые надо обязательно гуглить и проверять. Я недавно нашел у себя парочку таких, кушающих по нескольку рублей в день и успешно их отключил.
Отчет 2. Тайминг входящих и исходящих звонков
Забрасываем в сводной таблице поля Сервис и Место в область Строки, а поля Изменение баланса и Время в область Значения. Щелкаем правой кнопкой мыши по значениям поля Время и выбираем Итоги по - Сумма (Summarize Values By - Sum). Также для столбца с временем можно задать пользовательский формат [мм]:сс, при котором минуты не обнуляются при превышении 60, а накапливаются. Получим:
Для пущей наглядности дополнительно можно:
- Отсортировать таблицу, т.е. встать в любую ячейку столбца С и выбрать Данные - Сортировать по возрастанию/убыванию (Data - Sort Ascending/Descending)
- Применить к числам гистограммы на вкладке Главная - Условное форматирование - Гистограммы (Home - Conditional Formatting - Data Bars).
- Отфильтровать лишние сервисы и услуги с помощью фильтра в А3.
Из такого отчета ясно видно кто и откуда звонил нам и куда звонили мы (и сколько на это потратили). У меня, повторюсь, входящие бесплатны, но у вас картина будет другая и, возможно, приведет вас к мысли о смене тарифа или даже оператора. Если у вас много звонков по стране или за ее пределами, то стоит подумать о подключении услуг более дешевого внутреннего или международного роуминга.
Ссылки по теме
- Что такое сводные таблицы, как их создавать и настраивать
- Как использовать функцию ВПР (VLOOKUP) для подтягивания данных из одной таблицы в другую
- Что такое пользовательские форматы данных и как создавать свои форматы, которых нет в Excel
