​ ​ 1. Enter basic information and use functions to calculate the required data

​ ​ 1. Run the WPS form to create a new blank workbook. After entering the student's name, grades and other basic information, set the format of the text and tables (Figure 1).

Figure 1

​ ​ 2. Enter the relevant formulas in the following cells to complete the calculation of relevant data:

Cell formula description

J3=SUM(C3:I3) calculates the student’s total score in each subject.

K3=AVERAGE(C3:I3) calculates the student’s average score in each subject.

L3=IF(RANK(K3,K$3:K$62,0)<16,RANK(K3,K$3:K$62,0),"") gets the class ranking of the student's average score. If it belongs to the top 15, The ranking is displayed.

​​​B64=B2 gets the row title.

C64=C2

C66{=AVERAGE(IF(RANK(C$3:C$55,C$3:C$55)<=10,C$3:C$55))}The array formula is entered in this cell. The input method is: complete the input After the content inside the curly brackets, press

C67=AVERAGE(C3:C55) calculates the average score of this subject.

​​ 3. Drag the mouse to select the J3:L3 cell, double-click the fill handle (that is the small black square) in the lower right corner of the cell area with the mouse, and use the quick input method of the WPS table to complete the calculation of relevant data.

4. Drag the mouse to select cells C64:C66, move the mouse pointer to the fill handle in the lower right corner of the cell area, press the left button and drag to the right to cell K66, still using the quick input method of the WPS table to complete the related Enter the formula (Figure 2).

Figure 2

5. Insert a blank line above the first student’s information and enter the information of a virtual student (Figure 3).

Figure 3

​ ​ 2. Use record sheets to implement data query

1. Click cell A2 and click the "Record Sheet" command under the "Data" menu (as shown in Figure 4). In the pop-up "Sheet1" dialog box, you can see the first student (virtual student) in the data table Relevant information (Figure 5).

Figure 4

Figure 5

​​ 2. Click the "Conditions" button on the right side of the dialog box, enter the corresponding student's student registration number or name information and hit Enter, you can see the score information corresponding to the student (Figure 6).

Figure 6

3. In order to respect the privacy of other students, we need to hide the score sheet: close the dialog box, move the mouse to the row header on the left side of the worksheet, drag the mouse to select rows 3-63, and click on the row header Right-click the mouse and click the "Hide" command in the pop-up menu (Figure 7) to hide the score table.

Figure 7

4. Position the cursor on cell A2 again, click the "Record Sheet" command under the "Data" menu, click the "Condition" button in the pop-up "Sheet1" dialog box, and repeat the operation just now to complete any Inquiry of student scores (Figure 8).

Figure 8

The above is what the editor said aboutI hope it will be helpful to everyone on how to use WPS forms to solve students’ scoring problems. For more exciting tutorials, please pay attention to Huajun Software Park.