Excel2016 How to use the rank function to achieve ranking within a group

the ranking of data in a single worksheet is fine, but how do you do it without a worksheet merge if you rank data among multiple worksheets?

A small group held a long jump, hundred meters, put three games, divided into 3 groups, the group should rank and have awards, the group also has to carry out 3 groups of full ranking, also has awards. The results of the 3 groups were grouped into one, two and three groups of 3 tables.

Group ranking in their respective tables, easy to achieve. However, the overall ranking of the cross group will involve 3 tables of data, not by a single table can be completed. So, in the absence of a table, how to do the full ranking? If someone has a change in performance, how to achieve the group or the full score of the linkage?

Rank function to achieve group ranking

Because each team's results are exclusive of one worksheet, using the Rank function in a worksheet makes it easy to sort the data. Take an example of calculating the rank of a group of worksheets. First select the worksheet with the name "one group", click on "Zhao a" in the "group position" cell G2; then, in the formula bar, type "=rank (F2, $F $: $F $)"; Next, drag the fill handle to fill the data.

The same is true in the class rankings on the other two worksheets.

Range reference Implementation ranking expansion

If you want to traverse 3 tables for comprehensive data ranking, you need to make a cross table reference to the data range. First of all, select the name is "one group" worksheet, click "Zhao a" in the "full position" cell H2; then in the formula bar, enter the =rank (F2, group: three groups!) $F $: $F $) "; Next, drag the fill handle for data padding.

The same is true for the full ranking of other worksheets. Among them, "'" and ":" are the symbols in the English state.

Note: About the scope of the data reference is $f$2: $F $, in practical applications, it is not possible to have the same number of members in each group, so we want to refer to the range of the worksheet with the most records in each group's performance worksheet. Because it is a test, in this example, in the "one group" worksheet, where 8 conditions are placed, the other two groups put in fewer records, so the data reference range of one of the largest group of 8 is the base, that is $f$2: $F $. For input tips on "one group: three groups": You can hold down the SHIFT key and use the mouse to select the worksheet you want.

Implementation of data modification and result linkage

After the above process is done, when you change the results of a member in any worksheet, the group position and the full rank of the members will automatically change accordingly.

Look at it! In the table, only one of the results of the change, two ranking value has changed.

It is more time-saving and labor-saving to use the method as above to get the full rank of each group of data and then to paste the data into each group's score table. Let's try it!