The annual entrance examination has begun, the enrollment of the calf in order to summarize the county entrance examination enrollment situation and busy, for a while "screening", A Moment "sort", make a sweat, two eyes straight. Seeing him like this, I suggested to him the use of WPS table function automatic Rollup, and hand-set the method of the function to tell him.
I. Establishment of registration lists
In the county uniform Entrance Examination Application form to enter student registration information, if manages, directly to the township reported data copied to the table can be. (as shown in figure I)
according to the requirements of the county, fill in high school students, in the high school column filled with "1", Post-Secondary fill "2", Kindergarten Teachers Fill "3", Secondary School fill "4", the Grade Fill "5".
Second, create a summary table
Make the summary form in the WPS form according to the requirements of County manages. (as shown in Figure II)
Three, set up statistical functions
Enter formulas in B2 cells =countif the Register! $I: I, "1") ", to count the number of students enrolled in high school;" Enter the array function in the C2 cell =sumproduct (report roster!$ I:i=1) * (Register! $D: d= "female") "statistics of the number of female students enrolled in high school; in C2 cell, enter =b2-sumproduct (newspaper register! $I: I=1) * (Register! $G: g=" Han "))" Statistics the number of minority students in high school; Enter the function "=sumproduct" in the E2 (newspaper list!) $I: I=1) * (newspaper Register!) $H: h= "fresh") "Statistics the number of students enrolled in high school; Enter the =sumproduct in the F2 cell ( Report to the Register! $I: I=1) * (newspaper Register!) $J: j=2) "Statistics The number of students who report high school to five-year college; in G2, enter the =sumproduct (report to the Register! $I: I=1) * (newspaper Register!) $K: k=3)" Statistics on the number of secondary school students enrolled in kindergarten In the H2, enter the =sumproduct (report to the Register!). $I: I=1) * (newspaper Register!) $L: l=4) "Statistics the number of secondary school students in high schools; Enter the" =sumproduct in I2 "(Report list! $I: I=1) * (newspaper Register!) $M: m=5))" Count the number of students in the high school and newspaper. So far, candidates for the high school category of students statistics out.
In accordance with the provisions of the county, candidates for high school, can be reported down to college, kindergarten, secondary, vocational, college, can be reported down to the kindergarten teachers, secondary schools, grades, and so on. In accordance with this rule, we will take a look at the formula set for this category of tertiary education.
Enter the =sumproduct in B3 cell (report roster!). $I: I= "") * (newspaper Register!) $J: j=2) "Statistics of the number of students enrolled in college, the meaning of this function is: when the" Report roster "worksheet in the S column of a cell information is null, and J column corresponding cell has information , the student will be judged to be an applicant for college, and the number of students returning to the college in B3.
In the same vein, we enter "=sumproduct" in cell B4 (register! $I: i= ") * (Register!) J:j= "") * (Newspaper Register! $K: k=3)) "Statistics on the number of students enrolled in kindergarten teachers. And so on, can be in B5, B6 input Statistics Secondary School and the number of candidates for the function, and the high school class function format in the remaining cells to establish statistics of the number of girls, minority numbers and other information functions.
After the function was created, I reminded the calf where the array function must use the Ctrl+shift+eNter "combination to produce the correct result. The calf can't wait to copy the student information from one school class to the "report roster" worksheet, and then click on the "Totals" worksheet to test it, haha, all the statistical results are correctly displayed. (Figure three)
Examination register. xls
