Use of Excel

Use of Excel

We can see a piece of news about the top 20 surnames in the country. Specific data is provided in the middle, but it is estimated that the data is copied from the word. If the format is not correct, we can use Excel to process the data.

The processing result is as follows:

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M00/37/08/wKiom1Or7D-Am7OrAAKI9xiLDw4440.jpg "style =" float: none; "Title =" Excel split function.jpg "alt =" wKiom1Or7D-Am7OrAAKI9xiLDw4440.jpg "/>

I. Function Processing Method

Cell	Description
Column	Raw Data
B-E Columns	Separated Data
B2	= Mid (A2, 1, 2)
C2	= Mid ($ A2, find ("", $ A2, 1) + 1)
D2	= Value (mid ($ A2, find ("", $ A2, 1) + 2, 4 ))
E2	= Value (mid ($ A2, find ("", $ A2, 5) + ))

1. Cell B2 uses the mid function to intercept characters directly, but because 1-9 is a single digit and 10-20 is a double digit, the length of the third parameter is 2, A space is intercepted after numbers 1-9, which is irrelevant. If you think that there are more than spaces in the way, you can nest a value function to form the value (mid (A2,) form and remove the spaces automatically.

2. the mid function of the C2 cell. the start position of the second parameter uses the find function to search for the first space position in the original data, and then add 1. For example, the Wang surname is mid (A2, (3, 1). One character is truncated starting from the third character. The surname Wu is mid (A11,) and one character is truncated from the fourth character, this solves the problem that the starting point of one-digit and two-digit truncation is different.

3. D2 uses the value function because the following data is used for calculation. Even numbers intercepted by the mid function are treated as characters and cannot be computed. Therefore, value must be used for conversion.

4. E2 needs to find the second space position, so the start point of the find function is changed to 5, which can be changed to 5, 6, 7, or 8.

Ii. Partitioning Method

Step 1:

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M02/37/07/wKioL1Or7BHhW-rqAAOE1eRTs8c966.jpg "style =" float: none; "Title =" Excel split rows 1.jpg" alt = "wKioL1Or7BHhW-rqAAOE1eRTs8c966.jpg"/>

After column A is selected, click the next button, select the separator, and click Next.

Step 2:

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M01/37/08/wKiom1Or7EDiBRZpAAE1rxsRfiY304.jpg "style =" float: none; "Title =" Excel split rows 2.jpg" alt = "wkiom1or7edibrzpaae1rxsrfiy304.jpg"/>

Select "space" as the separator and "Data preview" to see the results after the breakdown. The second column is ineffective and needs to be further processed.

Step 3:

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M00/37/07/wKioL1Or7BLDyrWzAAF7mYQVLXk498.jpg "style =" float: none; "Title =" Excel split rows 3.jpg" alt = "wkiol1or7bldyrwzaaf7myqvlxk498.jpg"/>

Select the columns to be formatted in "Data preview", and then select the desired format in "column data format". Generally, the "regular" format is used, if necessary, click the "advanced" button for further settings. Click Finish.

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M00/37/07/wKioL1Or7BKylrcyAAEinlqfHRM264.jpg "style =" float: none; "Title =" Excel split rows 4.jpg" alt = "wkiol1or7bkylrcyaaeinl1_hrm264.jpg"/>

The effect is as follows: Column B needs to be split.

Column B must be split into two columns. Therefore, a blank column is inserted after Column B, otherwise, the split data will overwrite the original "Population Percentage" data in Column C.

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M02/37/08/wKiom1Or7EGhb0nZAAPcu2CYSCE853.jpg "style =" float: none; "Title =" Excel split rows 5.jpg" alt = "wkiom1or7eghb0nzaapcu2cysce853.jpg"/>

Select column B first and then Column B. Select "fixed width" in step 1, and drag the column line in step 2 to the appropriate position. This is the back of the surname and Chinese character, click "Next" and then set the data format.

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M01/37/07/wKioL1Or7BDjVhR-AAEso9D4f3Q518.jpg "style =" float: none; "Title =" Excel split rows 6.jpg" alt = "wKioL1Or7BDjVhR-AAEso9D4f3Q518.jpg"/>

The final effect is shown in.

This method is suitable for those who are not familiar with Excel functions.

This article from the "Breeze flip book" blog, please be sure to keep this source http://lioncn.blog.51cto.com/1557898/1431296