▲Picture 1
Figure 1 shows a certain original score sheet, including the scores of eight classes and eight subjects in the whole grade. This table is placed in the "Raw Data" worksheet. Our task is to easily query the four items of total score, average score, passing rate, and excellence rate of each class and subject. To this end, we have the following work to do:
1. Basic form preparation
Right-click on the Sheet2 worksheet label and rename it "Class Project Query". Enter "Query Class" in cell A1 of the worksheet, and enter "Query Project" in cell A2. Click cell B1, and then click the menu command "Data → Validation" to open the "Data Validation" dialog box. Click the "Sequence" option in the "Allow" drop-down list, and enter "1,2,3,4,5,6,7,8" in the "Source" input box below. Pay attention to the numbers and commas here. All must be entered in half-width English. The default settings are used for everything else.
Use the same method to set the data validity of cell B2, except that its source is "total score, average score, passing rate, excellent rate". After such settings, we can easily select the class and item to be queried from the drop-down lists provided in cells B1 and B2.
Return to the "Original Data" worksheet. Create a table as shown in Figure 2 in the P3:X13 area.
▲Picture 2
Click on cell Q3 and enter the formula "=Class Project Query!B1". Click on cell Q4 and enter the formula "=Class Project Query!B2". At this point, the basic form preparation work is completed.
2. Data preparation
First, select the class and query item to be queried in cells B1 and B2 of the "Class Project Query" worksheet. Then click on cell Q5 in the "Original Data" worksheet, enter the formula "=SUMIF($B:$B,$Q$3,D:D)", and press Enter to get the total score of the Chinese subject of the queried class. . Enter the formula "=Q5/COUNTIF($B:$B,$Q$3)" in cell Q6 to get the average score of the class's Chinese subject.
Enter the formula =SUMPRODUCT(($B:$B=$Q$3)*(D:D>=60))/COUNTIF($B:$B,$Q$3) in cell Q7
Get a passing rate.
Enter the formula =SUMPRODUCT(($B:$B=$Q$3)*(D:D>=85))/COUNTIF($B:$B,$Q$3) in cell Q8
Get an excellent rate. Select the Q5:Q8 cell range and drag its fill handle to the right to column X, so that all data for all subjects in the class is available. We can select all data and format it as a number with two decimal places.
As for the second form, it is simple.
Click on cell Q13 and enter the formula =VLOOKUP($Q$11,$P$5:$X$8,COLUMN()-15,FALSE)
Then drag its fill handle to cell X13 to get the desired result. Its digital format can also be set, as shown in Figure 3.
▲Picture 3
3. Create a chart
Once you have the data, creating charts is easy. Return to the "Class Query Project" worksheet and enter the formula "="Final Exam"&B1&"Class"&B2&"Chart"" in a blank cell.
Click the menu command "Insert → Chart" to open the "Chart Type" dialog box. Select the "Clustered Column Chart" type, click the "Next" button, click the "Series" tab, enter "=Original Data!$Q$11" in the input box to the right of "Name", and enter "=Original Data!$Q$11" to the right of "Value" Enter "=Original data!$Q$13:$X$13" in the input box on the right side, and enter "=Original data!$Q$12:$X$12" in the input box on the right side of "Category X-axis flag", as shown in the figure 4 shown.
▲Picture 4
Click the "Next" button, click the "Data Flags" tab, and select the "Value" checkbox.
Click the "Finish" button and drag the prepared chart to the appropriate location in the worksheet. The results obtained are shown in Figure 5.
▲Picture 5
Finally, we only need to select the classes and items to be queried in cells B1 and B2, and then the relevant data charts will appear below in real time. Using the WPS table tool, a student performance analysis table is completed. .
The above is what the editor said aboutI hope this will be helpful to everyone. Please pay attention to Huajun Software Park for more exciting tutorials.
