WPS Performance Analysis Table Table production tutorial

After each exam, the leader needs to know the students ' learning situation, such as the total score, average score, pass rate and excellent rate of each class. It would be nice if we gave the leader a comprehensive, all-inclusive form, but it's always less intuitive. If you can create a table that can be easily queried, and the results of the query can be displayed in real-time chart form, then the effect must not be generally good. Using the WPS Table tool, we can achieve this.

▲ Figure 1

Figure 1 shows a list of the original scores, including the results of eight disciplines in eight classes in the whole grade. This form is placed in the raw data worksheet. Our task is to be able to easily query the various classes of the total score, average score, pass rate, excellent rate of four items. To this end, we have the following work to do:

　　First, basic form preparation

Right-click at the Sheet2 Sheet tab and rename it to "Class item query." In the worksheet A1 cell input "query class", in A2 cell input "query item". Click B1 Cell, and then click the menu command "data → effectivity" to open the Data Validation dialog box. In the Allow Drop-down list, click on the "Sequence" option and enter "1,2,3,4,5,6,7,8" in the "source" input box below, noting that both the numbers and commas here need to be entered in the English half-width state. All others adopt the default setting.

Use the same method to set the data validity of B2 cell, only its origin is "total score, average score, pass rate, excellent rate". After this setting, we can easily select the class and query items that you want to query in the Drop-down list provided by the B1, B2 two cells.

Go back to the raw data worksheet. Create a table in the P3:x13 area as shown in Figure 2.

▲ Figure 2

Click Q3 Cell, input formula "= Class project Query!" B1 ". Click Q4 Cell, input formula "= Class project Query!" B2 ". At this point, the basic form of preparation work is completed.

　　II. Data Preparation

First, select the class and query items in the B1 and B2 cells of the class Project query worksheet, respectively. Then click on the "raw data" worksheet Q5 cell, input formula "=SUMIF ($B: $B, $Q $3,d:d)", enter after the inquiry of the class of the Language section total. In Q6 cell entry formula "=q5/countif ($B: $B, $Q $)", you can get the average score of the language section of the class.

In Q7 cell entry Formula =sumproduct (($B: $B = $Q $) * (d:d>=60))/countif ($B: $B, $Q $)

Get a pass rate.

In Q8 cell entry Formula =sumproduct (($B: $B = $Q $) * (d:d>=85))/countif ($B: $B, $Q $)

Get a good rate. Select the Q5:q8 range, drag its fill handle to the right to the X column, so that all the data for all the subjects in the class is available. We can select all the data and format it as a number with two decimal digits.

The second table is simple.

Click Q13 cell to enter the formula =vlookup ($Q $11, $P $: $X $8,column () -15,false)

Then drag its fill handle to the X13 cell to get the results you want. You can also set its number format, as shown in Figure 3.

▲ Figure 3

　　Iii. Creating a chart

With the data, it's easy to build a chart. Go back to the Class Query Project worksheet, enter the formula in a blank cell "= Final Exam" &B1& "class" &B2& "chart".

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, and in the input box to the right of name, enter "= raw data!" $Q $11 ", enter" = raw data in the input box to the right of value! $Q $: $X $ ", in" category X-axis logo " Enter the "= raw data" in the input box on the right $Q $: $X $ ", as shown in Figure 4.

▲ Figure 4

Click the "Next" button and click on the "Data Labels" tab to select the "value" option.

Click the "Finish" button to drag the prepared chart to the appropriate location on the worksheet. The resulting results are shown in Figure 5.

▲ Figure 5

Finally, we only need to select in the B1, B2 cell to query the class and query of the project, then will immediately appear in the underlying data graph, using WPS table Tools, a Student score analysis table is completed.

Form making