Quick statistics and analysis of achievement distribution with Excel

In this article, we describe how to use Excel to quickly statistical results and analyze the results of the distribution of methods and procedures.

　　Preparing data

Enter the necessary data, the worksheet is named "Total Table", with the political score (out of 100) in column D, for example, in cell E1~i1, below 60 (poor), 60 points and above (pass), 60-79 (Medium), 80-89 (good), 90 above (excellent).

Then enter the following formula in cell E2~i2: E2:=if (d2<60,1,0), F2:=if (d2>=60,1,0), G2:=if (and (d2>=60,d2<=79), 1,0), H2:=if ( and (d2>=80,d2& lt;=89), 1,0), I2:=if (d2>=90,1,0), select the cell range e2~i2, and then fill it down to the area e264~i264.

　　Calculate basic data

The so-called basic data, is the actual test number, the highest score, the lowest score and the average score (take politics as an example). Click a cell in the data range to select the menu data → PivotTable and PivotChart report. pops up the PivotTable and PivotChart Wizard-3 Step 1 dialog box and click Next to create a blank PivotTable report with the worksheet named "Basic Analysis." Click anywhere within the empty table range, display the PivotTable Field List dialog box, drag the class fields to drag row Fields here, and drag the political field to drag the data field here (Figure 1).)

Right-click in B4 cell sum: Politics, select field Settings from the pop-up menu, and in the PivotTable Field dialog box that pops up, select Totals as Count. And then the sum: Political 2 Summary mode selected as the "maximum", "Sum: Political 3" Summary way selected as "minimum", "Sum: Political 4" is selected as the summary of the average. Finally, drag the data field to the Total field (Figure 2).

　　Distribution of statistical results

From 30 points ~100, every 5 minutes statistics each class number of grades (take politics as an example). And then create a blank PivotTable report, named fractional segment, drag the class field to drag the column field here, drag the political field to drag the row field here, and drag the political field to drag the data field to the here section. "

Right-click in B4 cell sum: Politics, select field Settings from the pop-up menu, and in the PivotTable Field dialog box that pops up, select Totals as Count. Select a score, click the right mouse button, select "Group and display Detail" → "combination" in the pop-up menu, enter "30" in the Start in box of the pop-up dialog box, enter "100" in the "Terminate in" edit box, and then set step to 5, and click OK (Figure 3).