WPS Table Tutorial: Using the ET form to solve the multiple conditions of performance statistics

In the ET table, the scores, averages and rank of the subjects in the class score book are easy tasks. But how do you do it if you want to do something similar for a class that is mixed with multiple classes?

Figure 1

Fig. 1 shows the indicative score books for different specialties in different schools. We need to calculate the total score and average score of each major subject in each school, and to prioritize the students in their respective schools. This seemingly arduous task, using the Sumproduct function correctly in the ET table, is not so difficult to accomplish. We can only do this:

One, multiple conditions sum

For example, we need to calculate the total score of all students in the mechanical and electrical major of the industrial vocational College. Analysis table can be seen: the name of the school in the B2:b16 cell area, the professional name in the C2:C16 region, language results are distributed in the D2:d16 region. In the appropriate cell input formula "=sumproduct" (b2:b16= "Industrial Professional") * (c2:c16= "electromechanical"), D2:d16) ", after the return can get the result" 228. You see that? The first two parentheses in the formula are the conditions that need to be met, and "D2:d16" represents the area that needs to be summed. If you have more conditions, then put parentheses in front of it. Note that the middle bracket is connected with "*".

As to the total score of each subject in our schools, we have to design the form shown in Figure 2 to solve it.

Figure 2

First build a blank form, as shown in Figure 2 to enter the school name and professional name. In N2 cell input formula "=sumproduct ($B $ $B $16= $L 3) * ($C $ $C $16= $M 3), d$2:d$16)", select the fill handle for the cell, drag the formula to the right and down to the entire table, Then the total score of each discipline in each school is there. Not too hard, huh?

Since the professional setting of the schools is not the same, the total score of some professions is calculated as "0". If you want to pursue perfection, then we can use the IF function, when the result is "0" does not display any content, so that the results can be shown as much as possible. The formula is "=if (($B $: $B $16= $L 3) * ($C $: $C $16= $M 3), d$2:d$16) =0," ", Sumproduct ($B $: $B $16= $L 3) * ($C $: $C $16=$ M3), d$2:d$16) ".

Two, the average score of multiple conditions

The sum and the average score is only one step away, that is, the need to count the number of each school's professional. With it, the average score is much simpler. This can also be done using the Sumproduct function.

Figure 3

The N18 cell input formula for the table of figures shown in Figure 3 is =sumproduct (($B $ $B $16= $L) * ($C $: $C $16= $M) * (d$2:d$16<> ")", and then copy the formula to the right.

The average score is divided by the number. Therefore, if the number is "0", then the average time-sharing will have problems. Therefore, in order to get average time-sharing, we also need to make a judgment with the IF function. To borrow the previous two forms, then the language average of the mechanical and electrical specialty of the industrial vocational college can be written as "=if (ISERROR (n3/n18)," ", N3/n18)", and the remaining copy formula can be as shown in Figure 4.

Figure 4

The IsError (N3/N18) action in the formula determines whether the result of the "n3/n18" error occurs.

If you directly do not use the number of statistics table directly to find the average, then the formula can be written as "=if (($B $: $B $16= $L 3) * ($C $: $C $16= $M 3) * (d$2:d$16<>" ") = 0," ", Sumproduct ($B $: $B $16= $L 3) * ($C $: $C $16= $M 3), d$2:d$16)/sumproduct ($B $: $B $16= $L 3) * ($C $: $C $16= $M 3) * (d$2:d$16 <> "))".

Third, under the multiple conditions of the ranking

In the case of not destroying the original data table, for each student scheduled in the school in the professional position, looks very difficult, but with sumproduct is not the same. Just imagine, with the help of the Sumproduct function, we can easily count the number of people who meet multiple criteria. Then the industrial professional electrical and mechanical major total score of "616" students ranking, is not the school for "industrial professional", professional "electromechanical", Total score ">616" of the number plus 1!

With this idea, the formula is well written? J2 cell formula is "=sumproduct ($B $ $B $16=b2) * ($C $: $C $16=c2) * ($I $: $I $16>i2)) +1", after you finish, copy the formula down. The result is shown in Figure 5.

Figure 5