WPS Table Tutorial: Transformation Formula Optimization score Book

After the completion of the test scores of students and the score of each single division and the total score is our teacher here must do the homework. In the WPS table, calculate the student total score using the SUM function, with the rank function to arrange the position, which is also known to every teacher. However, there are always some special circumstances in each exam students lack of examination. If we calculate the total score by the actual score and arrange it, it is obviously unfair and leads to an error in the ranking, as shown in Figure 1. Only the students who have been manually excluded from the special situation can make the scores and rankings clear. Therefore, there is clearly a need for optimization in such a record book.

The subject position scheduling of students with single subjects lacking examination

To solve the problem of the subject ranking of the missing test students, we need to make a judgment in the ranking: If the error is indicated as shown in Figure 1, then the word "missing test" is displayed, otherwise it will be ranked directly.

Figure 1

In this case, just enter the formula "=if (ISERROR rank (c2,c$2:c$36)," J2, Rank (c2,c$2:c$36)) in the cell, and then select the fill handle of the J2 cell to the right by dragging to the N2 cell, and then select J2: N2 the fill handle of a range of cells by dragging the formula down to line 36th (the last row of the datasheet is line 36th). The resulting results are shown in Figure 2.

Figure 2

Part of the "ISERROR (c2,c$2:c$36)" In the formula is to determine whether the result of "rank (c2,c$2:c$36)" is an error, and if so, the result of the ISERROR (rank (c2,c$2:c$36)) is true.

The total score and the ranking of the students with missing test

After the above transformation optimization, the list of records in the ranking of the division is no problem. However, the problem still exists in this book: first, the total score of all subjects is 0 points, the actual score should not be shown here; the second is that the total score of students with missing subjects should not be involved in the scheduling of the overall ranking.

such as H14 cell "0" does not show that it is not difficult. Just click on the menu command "tools → options", click the "View" tab in the Open Options dialog box, and in the "Window Options" section, deselect the 0 value check box, as shown in Figure 3.

Figure 3

As for the ranking, we still need to change the formula. Click the I2 cell and enter the formula "=if" (OR (c2= "Missing Test"), d2= "lack of test", e2= "missing test", f2= "lack of test", g2= "missing Test"), "", RANK (H2, $H $: $H $36)), drag down the fill handle of the cell down to line 36th. In this way, as long as there are "missing tests" in columns C, D, E, f or G, the corresponding cells in column I do not display anything, otherwise they will be ranked normally. The final effect after completion is shown in Figure 4.

Figure 4