Contents
Formulation of the problem
I think that everyone in life has a moment when, after paying another portion of money for mobile communications, you say to yourself – isn’t it time to finally change the tariff to a more profitable one? The choice, however, is not easy. Each of the Big Three operators has dozens of active tariffs: limit, unlimited, pre- and post-paid, with and without a monthly fee. And to each of them you can add additional services in the form of SMS packages and Internet traffic, connection of “favorite numbers”, intranet and international roaming, etc.
To choose the “right” tariff, you must first evaluate your current statistics, i.e. determine the average per month:
- how many minutes of incoming and outgoing calls do you make
- Which mobile operators do you call most often?
- are there numbers that you call noticeably more often than others (you can make them “favorite”)
- What is the share of calls in intranet and international roaming
- how many MB of mobile Internet traffic do you spend
All mobile operators today can easily make you a detailed breakdown of communication costs for any period. However, the information content and visibility of such details leave much to be desired (to put it mildly). This is where Microsoft Excel can come in very handy. Just a few simple steps and a couple of formulas – and you will know exactly how much and what you spend.
All further actions will be shown on the example of Beeline. For other operators, the picture is similar, but may differ in details. Phones in all screenshots and videos have been changed, any matches are random 🙂
Step 1. Uploading the details
Go to www.beeline.ru Personal Account – Financial Information and ask you to create a detail for the last, for example, month. Beeline, by the way, recently updated the design of the site and the Personal Account – everything has become much more convenient and clearer. Subject Lebedev, although a pretentious comrade, knows his business.
Download the detail (or it comes to you by mail) and open it in Microsoft Excel. And here, apparently, normal people with straight arms have not yet reached:
You can see several problems right off the bat that will prevent the subsequent analysis of this upload in Excel:
- incomprehensible multi-line header (Excel understands only single-line, without empty and merged cells)
- the duration of the call and the amount of Internet traffic spent are mixed in one column, and this column does not contain time in Excel format, but text, i.e. it is impossible to calculate the total number of minutes of incoming and outgoing calls and total traffic
- similarly, the last three columns with balance data actually do not contain numbers, but text (there is a text format for the cells, i.e. you cannot calculate the total expenses)
For the final transformation of KAMAZ into a fighter, it will be necessary to “finish it with a file after assembly”.
Step 2: Refine Detailing
At first, remove all extra lines at the top of the sheet, leaving a one-line header:
Secondly, turn the pseudo-numbers in the last three columns into normal numbers that you can work with. To do this, select all the data in the columns with a balance and click on the pop-up icon with an exclamation mark – Convert to number:
Thirdly, add a column with the formula to the right of the table
=IFERROR(TIMEVALUE(D2),0)
=IFERROR(TIMEVALUE(D2);0)
which will convert the duration of the conversation in text form from column D to the normal time format that Excel understands. In case of a conversion error (for example, when the cell contains the amount of traffic instead of the time), the formula will return zero.
Fourthly, we will pull out into another additional column from the subscriber number three digits of the code of the mobile operator or city-region using the formula:
=ЕСЛИ(C2=»Internet’»;0;ПСТР(C2;2;3))
=IF(C2=»Internet»;0;MID(C2;2;3))
If the cell contains the word “Internet”, then the IF function will return zero. Otherwise, the MID text function will pull out three digits from the number, starting from the second.
Fifthly, add a column where the name of the operator or the city where we called will be displayed. To do this, you will have to use a small home-made guide to codes on a separate sheet like this:
To pull up the names of operators and cities from it by codes, you will have to use the VLOOKUP function in a separate column:
=ЕСЛИОШИБКА(ВПР(J2;Лист5!A:B;2;0);0)
=IFERROR(VLOOKUP(J2;Lookup!A:B;2;0);0)
Function ESLIOSHIBKA (IFERROR) needed to catch the #N/A error and replace it with zero.
As a result, after processing, our table should look something like this:
With the preparatory part, everything, we can move on to the reports.
Step 3. Reporting
For reports, it will be easiest and most convenient to use one of the most powerful and beautiful tools in Microsoft Excel – pivot tables. We put the active cell in our data table and go to the tab Insert – Pivot Table (Insert – Pivot Table). In the next window, click OK and form a pivot table report by dragging the column names (fields) into one of the four report areas (row names, column names, values or report filter):
Statement 1. Total Expenses by Type
Throwing the field with the mouse Service to the area Row Labels, and the field Change Balance to the area Values. We get total expenses by types:
From such a report, it is clear how much money is spent in general and on what. On my tariff plan, incoming and mobile Internet are free, but your picture may be completely different (and quite unexpected, by the way). Also, according to such a report, the costs of all sorts of incomprehensible services that you once connected and forgot are clearly visible. And perhaps there are those that you definitely didn’t connect, but for some reason you have them (surprise!) They will most likely look like incomprehensible abbreviations that you must google and check. I recently found a couple of these, eating several rubles a day, and successfully turned them off.
Report 2. Timing of incoming and outgoing calls
Throwing fields in the pivot table Service и Place to the area Rows, and the fields Change of balance и Time to the area The values. Right click on field values Time and choose Totals by – Sum (Summarize Values By — Sum). Also, for the time column, you can set a custom format [mm]:ss, in which minutes are not reset when exceeding 60, but are accumulated. We get:
For greater clarity, you can additionally:
- Sort the table, i.e. stand in any cell of column C and select Data – Sort in ascending / descending order (Data – Sort Ascending / Descending)
- Apply to histogram numbers on tab Home – Conditional Formatting – Data Bars.
- Filter out unnecessary services using the filter in A3.
Such a report clearly shows who and where called us and where we called (and how much we spent on it). I, I repeat, incoming calls are free, but your picture will be different and may lead you to think about changing the tariff or even the operator. If you have a lot of calls within the country or abroad, then you should consider connecting to cheaper domestic or international roaming services.
- What are pivot tables, how to create and customize them
- How to use the VLOOKUP function to pull data from one table to another
- What are custom data formats and how to create your own formats that are not in Excel