Practical Excel skills sharing: Let's talk about how to enter data in a standardized way?

Even many Excel veterans will encounter situations where the tables are not standardized, resulting in writing very complex and brain-burning formulas when doing data statistics, or even the final formula result is wrong, but the reason cannot be found. , which also reduces work efficiency. It can be seen that before we make a table, it is very necessary to understand the data specifications of some tables. Sharpening the knife will not make mistakes in chopping firewood. Let’s take a look together~

Excel has its own set of rules for processing data. The standard specification is that one cell records one attribute. Today we will learn how to enter data in a standardized way.

1. One cell and one attribute

In the two tables shown below, we need to sum the received quantities.

The data in column E all have units, and the data has also become text attributes. You need to divide the data into columns or replace the units with empty values ​​before you can sum, and the data in column K The data are standardized values ​​and can be summed directly.

If you want to sum column E directly, enter the formula in cell E12: =SUM(--LEFT(E2:E11,LEN(E2:E11)-1)), Press shift ctrl enter to end.

This is an array formula, which is not easy for function novices to understand and remember.

And summing column K couldn’t be simpler. Select the data in column K, click Formula-AutoSum-Sum, and you will get the result immediately.

Second, avoid using merged cells

Merge cells, only those that need to be printed Forms do not require further calculation and statistical summary, such as recruitment forms, etc.

In the source data table, the use of merged cells is prohibited. A standardized data source table should have all cells filled in, one record after another, and each row of data should be complete and neatly structured.

Column A in the above picture contains merged cells, which looks simple on the surface. Many problems will be encountered when processing data. Similarly, solving these problems also requires some skills. can be solved.

1. Formula

For example, to summarize the quantity received by the design department, use the formula: =SUMIF(A2:A4,A2,E2:E4) , the result is: 8. It is obviously incorrect, because after merging the cells, only the first cell A2 has data, and the rest of the cells are blank.

2. Filter

When filtering, you can only get one record, such as filtering the number of receipts from the "Design Department" .

3. Sorting

If you need to sort by sales volume, the following error dialog box will appear.

Third, the title is not placed on the worksheet

The title of the Excel document can be Displayed in the workbook and worksheet names, as shown in the right image below.

The Excel header row is used to store the attributes of each column of data, such as the "age", "position", and "sales" fields, which are filtered and sorted Field basis.

The title "Employee Sales Statistics Table" shown on the left side of the picture above is nothing more than telling everyone what kind of table it is, and it does not have any other functions.

So, you can occupy the first row of the worksheet without a title. Just mark it in the worksheet and workbook names as shown on the right.

Fourth, do not use blank rows and columns to separate data

As shown in the figure below, lines 6 and 11 are separated by two blank rows data. If you need to filter all the data below, you must first select the area A1:E13, and then filter. If there is no blank row in the middle, there is no need to select the area. Just place the mouse on any cell with data and filter directly.

If there is no blank line in the middle, place the mouse on any cell with data and press ctrl A to select all the data. However, if there is a partition, it will not work. .

Not only when filtering, but also when writing formulas. For data sources, it is important to maintain continuity between data.

If you really need to separate the data, you can thicken the cell borders, change the cell fill color, etc.

Fifth, do not add redundant total rows

A standard data source table should not have the total rows in the figure below . It is not advisable to add up the data while entering the data.

The data source may be added or deleted at any time, so the total will also change accordingly.

The correct approach is to enter the data first and then total it. The source data table is a table, and the summary table is another worksheet or other area. The summary can be completed using functions, pivot tables, etc.

Sixth, try to use one worksheet for the same type of data

Do not use the same worksheet for filtering, sorting, citing and summarizing. The difficulty increases a lot, which is very unfavorable for operation.

As shown in the figure below, all data is placed in one worksheet, and it is easy to filter and summarize by month.

Although long formulas of dozens or hundreds of characters or a piece of VBA code can complete multi-table merging, the prerequisite still depends on how scattered the data sources are. Therefore, for practical application, there is no need to set up obstacles and problems for yourself.

Seventh, record an attribute in the same cell

As shown below, all the numbers in the same cell in column A are added together.

To solve this problem, I used a very long array formula:

=SUM(TEXT(LEFT(TEXT(MID(A1&" a",ROW($A$1:$B$5),COLUMN(Sheet2!A:E)),),COLUMN(Sheet2!A:E)-1),"0;;0;!0")*ISERR (-MID(A1,ROW($A$1:$B$5)-1,1)))

End with three keys and get the result.

If the above data source is standardized, as shown in the figure below, data with different attributes are recorded in different cells. The sum becomes very, very simple, =SUM(D2:F2), and get the result.

Especially when you are new to Excel and have not yet mastered some skills, try to standardize the data source as much as possible. Although it is a small problem, it can also save a lot of work. Time~

Relevant learning recommendations: excel tutorial

The above is the detailed content of Practical Excel skills sharing: Let's talk about how to enter data in a standardized way?. For more information, please follow other related articles on the PHP Chinese website!