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

Have you ever met such a collapsing boss?

For example, here is a piece of data with thousands of lines.

▲ only shows part of the data. The boss asks you to count the sales of each commodity and the sales of each shipping area later.

In fact, these requirements are testing your mastery of Excel.

Students who know how to Excel can be easily done with PivotTable. Look at this, ↓↓↓.

❶ counts the sales of each commodity:

❷ statistics of sales in each shipping region:

If you often want to summarize and analyze the data, you might as well learn the PivotTable report.

01. PivotTable introduction before we learn, we need to know what a PivotTable is and when to use it.

In my opinion, PivotTable can improve the efficiency of report generation.

It can quickly summarize, analyze, browse and display data, and show the original data in multi-dimensions.

In the PivotTable report, you can also filter, sort, make charts, and so on.

But the most important thing is to summarize the data in groups, which can be achieved by simply dragging a few fields. See figure ↓↓↓ below.

Doesn't it look very simple!

For example, the above requirements are summarized by product name and shipping area, which can be easily achieved through the PivotTable report.

Want to study? Then let's move on together.

02, insert PivotTable PivotTable insertion steps are also very simple.

👉 procedure:

❶ selects any cell in the original data, and then click the insert tab-[PivotTable]

❷ chooses to place the PivotTable report on [new worksheet] or somewhere in [existing worksheet]

❸, click [OK].

In this way, the PivotTable report is generated, doesn't it look very simple!

By the way, here I choose to generate the PivotTable report in the new worksheet, and the final effect is as follows:

Next, we just drag the fields to summarize the data.

When you see this, you must want to ask, what is the field and how to drag it?

Don't worry, let's move on.

03, PivotTable structure PivotTable is mainly divided into three structures, report results display area, field list, field setting area.

❶ report results display area: to put it simply, where the final results are displayed

❷ field list: the column headings of the original data are displayed here, and each column header represents an entire column of data corresponding to it.

❸ field setting area: drag the fields you want to display from the "field list" to automatically summarize the data and display it in the "report results display area".

In the Field Settings area, you can see that there are four areas: filter, column, row, and value.

What exactly do they do? we'll talk about it later.

After introducing these basic concepts, let's take a look at what the effect is after the operation.

04. The use of PivotTable according to the initial example, we need to sort out "transaction quantity" and "paid-in amount" by commodity name.

Don't blink, just for three seconds.

Click the left mouse button on "commodity name", drag into the "line", the same operation, "transaction quantity" and "paid-in amount" are dragged into the "value".

How is it? is it very convenient!

"Row", or "row label", is to arrange the data in the cell on each row, as shown in the following figure:

The data in the Commodity name column, after the duplicate values are deleted by default, are all arranged in each row.

Similarly, column labels are all arranged in each column after the duplicate values are deleted.

"value" is the data summary area, which shows the calculation of "transaction quantity" and "paid-in amount".

05. PivotTable layout changes compared with ordinary tables, PivotTable not only can summarize data, but also has one feature: layout changes are more flexible.

Drag the ❶ tag at will to display the data as needed.

❷ categorized summary: you can summarize the data by field.

Click the "Design" tab-"Classification Summary", and select above, below or not to display each set of data.

❸ totals: used to display the totals of each row / column of data.

Click the [Design] tab-[totals], and select whether to show totals for rows, columns, rows and columns or not.

❹ blank lines: insert blank lines after each set of data to make data viewing more intuitive.

Click the "Design" tab-"blank line", and select insert blank line or delete blank line after each item.

❺ converts the PivotTable report into a table format. It doesn't matter if you don't understand this sentence. Take a look at the procedure and you will understand.

Click the [Design] tab-[report layout]-[display in tabular form]-[report layout]-[repeat all project tags].

In this way, the PivotTable report can be displayed in a tabular layout.

How about, see here, do you think the PivotTable is particularly flexible?

Of course, it is necessary to choose the display mode according to the actual situation.

06. After the PivotTable report beautifies the data, it still doesn't look very beautiful.

So we also need to put a "new dress" on the PivotTable to make it look new.

It is also easy to beautify the PivotTable. Click the Design tab and select a style in the PivotTable style.

Put on new clothes, the PivotTable will be more beautiful!

Note: in front of us, we have seen the powerful summary function of PivotTable, but we should also note that we must avoid the following minefields when using it!

❶ title missing: title cannot be empty.

Duplicate ❷ headings: column labels cannot be duplicated.

After you insert a PivotTable report, each column of data is compressed into a field label, and drag the field to change the layout of the PivotTable report.

If the two fields are the same, the PivotTable report will be messed up and you can't tell which corresponds to which column.

❸ blank row: if there are blank rows in the PivotTable, click "data"-"insert"-"PivotTable" instead of automatically selecting all data regions.

Of course, we can manually change the table range and insert the PivotTable report.

So, have you learned the simple and practical PivotTable?

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Chang Xiaoan 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.