Automatic calculation of mobile package charges with Excel 2007

Today, I am prepared to compare the mobile phone package rates, this comparison, in my current China line Chang listen to D card, the original global pass local packages, as well as I feel more favorable dynamic zone music package between, covering the local mobile company's three major brands. Because the various packages long-distance and roaming charges are basically consistent, so long distance and roaming. In order to make the fee closer to the real situation, the general need to check the last 6 months of the bill, to find out the total monthly costs, and then compared with other packages. For the sake of convenience, take one months as an example.

The first step is to get a detailed list of charges

Enter the mobile online business hall, open their own details of the bill, the specific process can be consulted 10086. Select all items on the detail list and copy them to Notepad and save them as txt files. Be careful not to copy directly into the Excel2007, otherwise you cannot do this directly because Excel adds a format character to the phone bill. This step should be especially careful not to change any data format, otherwise the spreadsheet will not be able to import data.

The second step is to import Excel

After opening Excel 2007, in the "open" option to find the TXT file just saved, the system enters the Text Import Wizard 1. Note that in the "Please select the file type that is most appropriate", choose "separator symbol." Then click Next, select "Space" in "separator", other values are default, and click Finish. After the import is complete, be sure to see if the header and the data correspond, and then import the changes if the error occurs.

The third step of the single processing

Take the main call as the keyword, the basic fee for the secondary keyword, delete the main call free call. Because these packages are long-distance and roaming fees are similar, in order to simplify the calculation, respectively, using the call location and long-distance charges for the keyword, use the same method, the data are also deleted. In addition, because of the basic message charges, we first calculate the number of text messages, =countif (h2:h290.) Short message "), article 42. Then delete the SMS data.

The following is the beginning of the calculation of the cost of three packages:

Calculate the local call fee for the "D" package. In e column last input =sum (e2:e290), oh, my call fee calculated out, 24.8 yuan.

Calculates the cost of the global pass local package. Because the local is called free, so first delete all the called orders. Use the call type to sort, and delete it.

Next, calculate the number of call charges. The principle of billing is less than 60 seconds to calculate to 60 seconds. Add a column of J to the table. =ceiling (d2/60.1), and then make a column copy of the formula.

Find the number of calls. In the L61 input =sum (j2:j65), oh, the number of calls out, 94 times, the main call cost is 18.8 yuan.

Calculate the Motion zone music package. Busy time for the Monday to Friday 9:00-12:00,18:00-2l Point, the rest for leisure.

Call time for the day of the week. Enter =weekday Left (C2.6). 2), to get the number between 1-7, corresponds to Monday to Sunday.

Then process the call time. Adds a row of L,=hour (right C2.8). 5), get the number format of the call time.

When the calculation is busy. Add a line of M, and enter =and (or (l64>=9.l64<=12), and (l64>=18.l64<=21), or (k64=1.k64=2.k64=3.k64=4.k64=5)).

Calculate the phone bill. =0.3*SUMIF (m2:m65,true,j2:j65) +0.1*sumif (M2:m65. FALSE,J2:J65), the cost is 14.6 yuan.