In our work or study, when we use Excel for data statistics, we often use all kinds of Excel-related functions, such as sum, sumif, countif, **erage, **erageif and so on.
In fact, there is a simpler and more convenient operation in Excel, which is pivot table. All you have to do is drag what you want to count, and the data will be displayed.
1. Reference path method:
Setting method: 1. Click the menu bar: Insert - Pivot Table, you can create a new or insert a pivot table area on the current page;
2. Drag the name field to the row label or column label, and the data field needs to be dragged to the numeric value. Through the operation of pivot table, we can perform all kinds of summing, counting, averaging and various proportional calculations.
2. Case introduction.
Scenario 1: Find the total sales volume of each department in the personnel details.
Operation method: 1. Click the menu bar: Insert - PivotTable to insert the pivot table area on the current page;
2. Drag the department field to the row label, and the sales field to the value area. This brings out the default summation of the data.
Scenario 2: Find the average sales volume for each department.
Operation method: 1. Click the menu bar: Insert - PivotTable to insert the pivot table area on the current page;
2. Drag the department field to the row label, and the sales field to the value area.
3. Click the small inverted triangle on the right side of the sales in the numeric field, set the value field, and select the displayed value as the average value.
Scenario 3: Find the number of people in each department.
Operation method: 1. Click the menu bar: Insert - PivotTable to insert the pivot table area on the current page;
2. Drag the department field to the row label, and the name field to the numeric area; If you move a non-numeric label, the number is counted by default.
Scenario 4: Find the proportion of the number of people in each department.
Operation method: 1. Click the menu bar: Insert - PivotTable to insert the pivot table area on the current page;
2. Drag the department field to the row label, and the name field to the numeric area;
3. Click the small inverted triangle to the right of the name in the numeric field, and set the value field - value display method - the percentage of the total sum.
Scenario 5: Find the proportion of sales by department.
Operation method: 1. Click the menu bar: Insert - PivotTable to insert the pivot table area on the current page;
2. Drag the department field to the row label, and the sales field to the value area;
3. Click the small inverted triangle on the right side of the sales in the numeric field, and set the value field - value display method - the percentage of the total sum.
Note: 1. In the process of making a pivot table, there can be no blank cells in the header, that is, the header row, so that the pivot table will not be dialyzable;
2. In order to ensure the accuracy of the data, the data format of each column must be consistent.
Related Articles: 12 kinds of Excel operation skills, newcomers and old people should learn to improve work efficiency.
What should I do if the computer suddenly loses power and shuts down, **What should I do if it is not saved? One trick to teach you to recover data quickly.
Excel commonly uses 8 conditional statistical functions to solve 80% of daily work, and say goodbye to overtime and tables.