Excel Application Tutorial: Excel PivotTable report Classification for student achievement average

After the end of the final exam, the Education bureau requested the students in the whole city to be summarized in the same Excel worksheet as Figure 1, and asked to find out the school, the professional, the average score of each 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.