Use Excel to Do data description: rank and percent

After the mid-term examination of a class, only published results in accordance with the requirements, but students and parents asked to know the rankings. In order to publish results rankings, students can be found through the results of their rankings, and at the same time the score is ranked in the class percentage (that is, the student is ranked in the top "X" students).

Sorting is the basic operation of Excel, Excel data Analysis, "rank and percentage ranking" can make this work simplified, direct output report.

Note: This feature requires Excel extensions, and if your Excel does not have data analysis installed, select Tools-add-ins to load the analysis database on the installation CD. After successful loading, you can see the data analysis option in the Tools Drop-down menu.

Operation Steps

1. Open the original data table, make the original data of this example no special requirements, as long as the row or column to meet the same attribute value.

2. Select "Tools"-"Data Analysis"-"descriptive statistics", the Property settings box appears, and then select;

Input range: Select the data area, if there is a data label, pay attention to the check at the same time "logo in the first line";

Grouping method: Indicates whether the data in the input area is considered by row or column, choose according to the original data format;

The output area can select this table, a new worksheet group, or a new workbook.

3. Click "OK" to see the generated report.

As you can see, a new table of four columns is generated, where "point" refers to the ordinal number of the original data, which is also a useful sequence in this case; Score "is the sort of data series;" Ranked "statistical method for taking the same position with duplicate data;" Percentages are sorted in descending order, and you need to make a little adjustment to get the true percent rank.

4. In the next column in the Percent column, enter percentile rank, enter the formula in the first cell =1-g3 (corresponds to ' percentile rank '), and return. Select the cell, and drag down until the fill is complete. This achieves the goal of showing percent rankings.

The completed report instance is shown in the following illustration.