The method of calculating average value of data by PivotTable report in Excel

At the end of the exam, the Education Department asked the whole city students to sum up in the same Excel worksheet as Figure 1, and asked to find out the average score of each school, every major and every subject.

The whole city three schools, each school has six professions, each profession has five courses, each professional number is different, the total has 1000 people, the workload is huge. However, if the use of Excel PivotTable report to complete, hehe, it is much simpler.

First, create a PivotTable report

Click the menu command data → PivotTable and PivotChart reports to open the PivotTable and PivotChart Wizard dialog box.

The first step, as shown in Figure 2, is to select the Microsoft Excel data list or database and the following PivotTable form options.

The second step, as shown in Figure 3, is to enter the range of cells in which all the data is located in the selection, or click the Compress dialog button to the right of the input box to select the range of data in the worksheet with the mouse.

In the third step, select the new Sheet option in the dialog box to put the PivotTable report you created on a new worksheet, and then click the Finish button, as shown in Figure 4.

This allows you to create an empty PivotTable report with both the PivotTable toolbar and the PivotTable Field List dialog box displayed, as shown in Figure 5.

Ii. using a PivotTable report to get the results needed

According to the requirement, we should get the average scores of schools, professions and disciplines. Therefore, we should take the "school", "professional" as a row field, and the subject data as data items.

From the PivotTable Field List, drag school to the left of the PivotTable report, drag the row fields here position, and then drag the professional field to the right of the school field, and release the mouse when the mouse changes to the "I" font, as shown in Figure 6. So you get the effect shown in Figure 7. This is exactly what we want to calculate the average score of each project.

Now, all you have to do is drag the Subject field from the PivotTable Fields list to the PivotTable report, where you want to drop the data items here, to get the sum of the scores for each school, major, and discipline. As shown in Figure 8.

Now let's change the sum to the average. Select the first sum: Language in the pivot table, as in the C4 cell in the figure, and then click the Field Settings button in the PivotTable toolbar to open the PivotTable Field dialog box. Select average in the total by list in this dialog box. As shown in Figure 9. After the confirmation, all schools, the specialized language section is changed from the sum to the average.

Other subjects follow this procedure. Five courses are five times as long as this operation. At the same time, we will get the average score of each school discipline and the average score of three schools in the whole city, as shown in Figure 10.

Of course, the use of subtotals can also complete the above data analysis. However, this requires that the data be sorted first, and if we need to generate a report, we need to set up the appropriate table ourselves. With a PivotTable report, you can automatically generate the appropriate reports, eliminating the hassle of designing the tables. In addition, you can easily create additional reports if you drag the corresponding fields for row or column fields. As shown in Figure 11, you can simply drag the professional field in the Row field to column fields.