# Quick statistics and analysis of achievement distribution with Excel

Source: Internet
Author: User
Tags count

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).

Related Keywords:

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

## A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

• #### Sales Support

1 on 1 presale consultation

• #### After-Sales Support

24/7 Technical Support 6 Free Tickets per Quarter Faster Response

• Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.