Shulou(Shulou.com)11/24 Report--

Original title: "how to sort out non-standard data?" This is the most easy-to-understand answer I have ever heard! "

Hello, everyone. I am Der Spiegel in mind.

For most people in the workplace, it is common to deal with forms every day.

Often do a variety of statistical analysis reports, can also understand.

However, when making these reports, it will be more convenient to have a standard data source, both for yourself and for others.

This is not my nonsense. There are examples to prove it.

The above table was sent by a friend yesterday, and his requirement is to summarize the data recorded on the left into the table style on the right.

Such a record seems to be quite regular, with total sales = sales + 3 + sales 5.

Friends who know the function should think of using the SUMIF function.

You can simply add three SUMIF to get the result.

Complete formula:

= SUMIF) + SUMIF) of course, you can also use the method of misplaced reference, using a SUMIF or SUM function and so on.

But according to the left format to record the original data, with the increase of years, the later statistical analysis data will become more cumbersome.

What if we sort out the table on the left like this?

Is to make it look like a standard current account, and then we can use PivotTable to make a variety of reports that are statistically analyzed from different angles.

How to make the non-standard original records in the format of the above ledger? This format is what we usually call one-dimensional tabular data. )

Today, I would like to share with you some tips that have been treasured for a long time.

PS. There is no version restriction for this practice.

1. ▋ STEP01 insert new column Select column A, press and hold the [Ctrl] key, and then select column C and column E.

Then click the right mouse button-[insert] new column.

▋ STEP02 fills the month into the newly added column, select the A3:G8 data area, press [F5] to call up the [positioning condition] in the positioning dialog box, and select the [null value], then enter "= Epist1" in the edit bar, and finally press [Ctrl+Enter] to populate the month data in batches.

PS: when operating in the above picture, the active cell is in [D3]. The actual situation may be different. Friends should be flexible as appropriate.

In addition, why make an absolute reference to the line number?

Quite simply, it is to keep the line number unchanged as you fill it down. When filling to the left and right, the column number remains a relative reference.

▋ STEP03 uses the equals sign (=) to connect the data in the same column in the A9 cell, enter "= D3", then copy and drag to the right to the F9 cell, and then down until all zeros appear in the last row.

In this way, we consolidate the 3-month data into the same column.

▋ STEP04 collates and copies the data needed to enter the month in the A2 cell, filter out the value of 0 in the second row, leave the part with the data, and finally copy it to the new table.

All right, so we're done with the data.

The rest, we can easily complete the summary with the click of a mouse through the PivotTable.

Of course, the above problems can also be solved with SUMIF, but the advantage of using one-dimensional tables is that you can use PivotTable for multi-dimensional analysis.

For example:

We can also summarize the total sales by month, just drag the [month] field to the [row] area.

It is also very convenient to count monthly sales according to the percentage.

Just right-click and select [value display]-[percentage of Total].

Then you can show the proportion of monthly sales!

Compared with using functional formulas for statistics, the method of counting thoroughly is more convenient than losing it.

02. The above skills of knowledge expansion can also be used in other aspects.

For example: in many cases, there is also a common format, which is the following form of table record.

It is what we commonly call a two-dimensional table. This kind of table is only suitable for data summary, not suitable for storage as a data source.

You usually need to convert it to the following one-dimensional table.

Then how to change it?

In fact, the method of operation is similar to that introduced above, and you can almost understand it at once.

The steps are as follows:

▋ STEP01 inserts a new row first select column B, then press and hold the [Ctrl] key, then select column C and then column D, and right-click to insert the new column.

▋ STEP02 fills the month into the empty cell, select the range of B2:F7 cells, press [F5] to call up the positioning conditions in the positioning dialog box, and locate [null].

Then enter the formula "= Clear1" in the edit bar, and finally press [Ctrl+Enter] to successfully batch fill their respective months.

The remaining steps are basically the same as the above.

Enter the formula "= D2" in the cell [B8] and drag to the right and down to copy it.

Finally, copy the name of the [A] column to the space below.

At the end of the day, we introduce the technique of organizing irregular data sources into standardized one-dimensional tables.

Take advantage of a lot of small knowledge points:

❶ positioning data

❷ batch filling formula

Special usage of ❸ equal sign

It is also introduced that a variety of summary statistics can be carried out by using PivotTable.

Mastered today's data collation methods, in the future work to deal with non-standard data sources, will be handy!

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Der Spiegel, Review: Xiao Shuang, Editor: Zhu Lan

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.