Table data is not standardized? The solution is these!

As you know, Excel has powerful data processing capabilities, but the prerequisite for this powerful capability is to "comply" with the input data in Excel. However, in the actual work, many users will enter a lot of data according to their own habits, "violation", which leads to Excel can not play its powerful processing capabilities in the later operations. So, what violation data do you usually enter in Excel? How can we circumvent them and merge valid data?

1 cell merger is not standardized

For the input of some similar projects, many people often combine some cells for the beauty of the form. This is beautiful, but in the later stages of sorting and summarizing, it will bring a lot of inconvenience. For example, in the following table, the "yield" cells are merged so that when sorting, an error is displayed, prompting "the size of all merged cells needs to be the same" because Excel considers the merged cell as a "whole", but this The whole contains other cells, so it causes an error in the sorting (Figure 1).

Figure 1 Freely Merging Cells Causes Sorting to Fail

Solution: Merge cells using cross-row centering simulation

For the merging of horizontal cells, Excel provides "cross-row centering" to achieve a similar effect. Therefore, to achieve the above combined effect, you can first cancel the merger of B1, C1, then select B1, C1, right-click and select "set cell format → alignment", horizontal alignment to select "cross-column", vertical alignment to select "center." This can achieve the effect of merging cells without causing inconvenience to subsequent operations. Similarly, vertical cells are also suggested to be used less often. For example, the "quarters" mentioned above can be entered separately in cells (Figure 2).

Figure 2 Centering Effect

2. The date and currency unit input is not standardized

For the input of dates, many friends like to use a format such as "2017.5.8"; the price input is like inputting "meta" in a superfluous manner. In Excel, this type of data is not considered to be a date or number. This will also cause inconvenience in subsequent date calculations and sorting. Sales statistics, for example, cannot be summed directly. Because data such as "XX Yuan" is not considered by Excel as a result of the summation error (Figure 3).

Figure 3: Failed Sum of Failed Input

Solution: Use Find & Replace Fast Conversions

From the above input, you can see that the year, month, and day of the date are separated by ".", and the sales are followed by "meta" after each number. Therefore, we can use the find and replace for batch replacement. Take the date as an example. Click "Start → Find & Select → Replace" to find the content as "." and replace it with "-" (that is, the correct date separator in Excel). This will quickly convert the violation date into a compliance input. . Replace sales data similar (Figure 4).

Figure 4 Using alternative tools to organize data

3 character input is not standardized

Under normal circumstances, English uses half-width input, and Chinese uses full-width input. However, in daily entry operations, there are always a lot of friends who mix the English full-width, half-width, Chinese and English punctuation, so that the document will look unsightly (Figure 5).

Figure 5 Full-width English and word initials without uppercase data

Solution: ASC and PROPER functions

The ASC function can quickly convert a given cell's full-width character to a half-width character. The PROPER function can convert a word's initial letter to uppercase. Go to the Excel editing window, insert an auxiliary column B after column A, select the B2 input function "=ASC(A2)", and fill it down. Continue to insert a column of auxiliary column C, select C2, enter the function "= PROPER (B2)", fill it down (Figure 6). For Chinese and English punctuation mixed, you can copy to Word for quick replacement.

Figure 6 The result after finishing

4. The unit of quantity input is not standardized

For the sake of simplicity, in daily work, some friends often enter units and numbers when they enter data with units (such as barrels, pieces, etc.). Since these data with units are not purely digital data, sorting and summarizing are also the same. Can not be operated directly (Figure 7).

Figure 7 Irregular data for mixed units

Solution: Use "separate" specification data

As you can see, the above data contains various units, and it is now possible to use separations to separate quantities and units. Since the column separator needs a uniform separator, here we can add the "-" method to the unit as a delimiter. Click "Start → Search and Select → Replace" to find the content as "Part" and replace it with "-Part" (again, Change the other units to "-units". This will change the format of each quantity to the "quantity-unit" format. Select the data in column B, click on “Data→Separate”, and delimit the symbol to select “Custom→Other→-”. This will complete the separation of data and units and make the data become normal (Figure 8).

Figure 8 Using Disaggregated Specification Data