as shown below table summarizes the results after the various professional student achievement have been put on a work table, the format shown in Figure 1. In order to do performance analysis should be done two rankings: one discharge every student majors in the same city in the ranking; second is discharged each student in the school's ranking in this profession; two rank to Total as the basis.

Figure 1

the work done previously, each had to the data by specialty, were screened out by the school copy to different worksheet, and then in a different worksheet using RANK function to sort. The city more than 1000 students in three schools, each school has a 7-8 professional, so this work is time-consuming and laborious screening copy, copy the complete screen but also in more than 10 ranking worksheet work, very troublesome. But this time, the job done extraordinarily well, only 10 minutes to complete all the work. Because of this we use the SUMPRODUCT function to complete the conditional rankings work. Realization process is as follows:

one, ready to work Total

selected where the H2: H1032 range of cells, click the function of area "formula" tab, "the name of the definition of" functional group "definition name" button in the pop-up "New name" dialog "name "input box, enter the name of the definition of this region" zongfen ". At this point, the dialog box below the "reference position" after the input box has automatically enter our selected range of cells "= counterparts! $ H $ 2: $ H $ 1032", shown in Figure 2.

Figure 2 $ show_page $

by the same method, selected schools where the range of cells I2: I1032, professional range of cells where J2: J1032, respectively, which specify the name of the "xuexiao" and "zhuanye".

completed, even if this preparation is over.

Second, schedule ranking

enter a title in the K1 cells, "according to the professional ranks." Click the K2 cell, enter the formula "= SUMPRODUCT ((zhuanye = $ J2) * ($ H2

enter a title cell in L1, "the professional school rankings." Click to L2 cells, enter the formula "= SUMPRODUCT ((zhuanye = $ J2) * ($ H2
Figure 3

If you are experiencing similar problems, such as summary results of the parallel classes in a worksheet, and we need students in class rank, then one might wish to follow back, huh, huh, the effect, really Who is using who knows ah.