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

The original title: "use the PivotTable every day, these three tricks I unexpectedly know, do not be too easy to use!" "

In the era of big data, data processing and analysis are becoming more and more important.

As a very powerful tool, PivotTable can easily solve the work of summary statistics without a function and formula.

When it is faced with thousands of rows of data, it can also "do not change its face and heart", and it can be done easily!

Today, let's share three tips on PivotTable to help you improve your office efficiency!

1. Trick 1 in most data, the use of dates is essential.

For example, the sales date in the sales schedule, the invoice date and payback date in the billing and payback table, and so on.

As shown in the following picture: it is an invoice schedule.

Now I would like to count the total invoice amount each month in order to analyze the sales situation and improve the sales strategy.

Select the [A1] cell, click [insert]-[PivotTable]-[OK].

A new worksheet is generated.

Then drag the invoice date to the line area, and drag the invoice amount to the value area.

As shown below:

Select the [A4] cell in the date column, then right-click and select [combination].

As shown below:

This kind of situation, friends should often encounter, right?

They all look like dates, so why can't they be grouped? Next, I will teach you how to solve this problem quickly.

❶ if there is little data, you can reduce the column width of the cell directly in the data source. As shown below:

If it is a true date, it will be displayed as a mutton kebab because the cell width is not enough.

If it is a false date, it will not change at all.

Such as the [A5] cell in the figure above.

Select the cell to look at the edit bar, the date is preceded by a single quotation mark, indicating that it is a text date.

We can directly format this cell as normal, press [Delete] to delete, and then re-enter the correct date.

❷ if there is a lot of data, we can call up the automatic filter, and then click the drop-down button, and the true and false Monkey King will be revealed.

Look at this guy. He's the fake Monkey King. Just find it and get rid of it.

In addition to re-entry, you can also select this column and click [data]-[breakdown], bring up the sorting dialog box, and click "finish" directly.

After doing this, it will become the real date.

Finally, refresh the PivotTable and then combine it.

In this way, the monthly invoice amount is completed.

2. The picture below is a salary table for personnel.

Generate a PivotTable with this worksheet and then sort it in descending order in the PivotTable. To check the salary level.

As shown in the following figure, drag [name] and [Department] to the row area, drag [salary] to the value area, and then select the [G2] cell and click "descending" in the data tab.

No matter how it is, it is indifferent!

It's like Wukong gave this guy a magic sedentary technique!

If we get rid of the department, as long as the name, the descending order will not have the above problem.

But now it is necessary to display the name + department at the same time and sort them in descending order, so what should we do?

The methods are as follows:

❶ first drags [name] to the row area, drags [salary] to the value area, and then sorts [salary] in descending order.

❷ then drag the [department] to the line area. As shown below:

Solve the sorting problem perfectly.

3. The third trick is the PivotTable generated according to the payroll as shown in the figure below.

Now the requirement is: if you want to view the summary data of each department, you do not need to display the detail data.

Usually, we usually click the filter button next to "Department", as shown in the following figure:

However, there is no summary word in the filter menu item.

That's weird!

I don't know what the person who designed the PivotTable thinks!

Ignore it first, or solve the problem first.

There are two solutions.

Method 1:

Select the [A4] cell (of course, you can also select [A] column data other than the summary row and the total row).

Then right-click and select [expand / collapse]-[collapse entire field].

The result is as follows:

This displays the summary data for each department.

Of course, the word summary is not reflected here.

If it needs to be reflected, it can be achieved by formatting the cells.

As shown below, select [A4:A6], press [Ctrl+1] to bring up the [format Cell] dialog box, select [number]-[Custom], enter: "@ Summary", and click "OK".

The final effect is as follows:

Method 2:

Since the summary items are not displayed in the filter menu that comes with the PivotTable, we can use the table filter button to do so.

Select the [D3] cell next to the PivotTable, click "data"-"filter", and a new filter button will appear.

At this point, let's click the drop-down button in the Department cell and the summary item will appear.

The filtering effect is as follows:

4. at the end of the day, we share three useful and magical tricks in using PivotTable.

❶ solves the problem that dates cannot be grouped.

❷ solves problems that cannot be sorted.

❸ resolves issues where summary items cannot be filtered.

Although the PivotTable function is very powerful, we still need to pay attention to its rules and methods in the process of application.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Der Spiegel in Heart

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.