excel表格分班用函数怎么做

1.学生分数如何用Excel编制自动分班

学校在对新生进行分班时，一般主要考虑入学成绩（总分）和男女人数的匀称搭配。

这里以1000人容量为例，具体介绍用Excel编制自动分班软件的方法。1.打开Excel工作簿。

2.在首行各单元格中，从左到右依次输入“男分”、“男余”、“男次”、“男”、“女分”、“女余”、“女次”、“女”、“班次”、“姓名”、“总分”、“性别”和“班数”。3.在“男”列的D2单元格中输入公式“=IF(L2="男"，K2,0)”，向下拖动复制到D1001；在“女”列的H2单元格中输入公式“=IF(L2="女"，K2,0)”，向下拖动复制到H1001。

注意：在输入公式时，除汉字而外，所有字符一律要在英文输入状态下输入，且不要加进空格。4.在“男余”列的B2单元格中输入公式“=IF(D2=0,"",MOD(C2,2*M$2)）”，向下拖动复制到B1001；在“女余”列的F2单元格中输入公式“=IF(H2=0,"",MOD(G2,2*M$2)）”，向下拖动复制到F1001。

5.在“男分”列的A2单元格中输入公式“=IF(D2=0,0,IF(B2=0,M$2,IF(B2<=M$2,M$2+1-B2,B2-M$2)））”，向下拖动复制到A1001；在“女分”列的E2单元格中输入公式“=IF(H2=0,0,IF(F2=0,1,IF(F2<=M$2,F2,2*M$2+1-F2)））”，向下拖动复制到E1001。 6.在“班次”列的I2单元格中输入公式“=A2+E2”，向下拖动复制到I1001。

在M2中输入“6”（默认的年级总班数，使用时可任意设定）。7.在“女次”列的G2、G3单元格中分别输入公式“=RANK(H2,H$2:H$1001)”和“=RANK(H3, H$2:H$1001)+COUNTIF(H$2:H2,H3)”，将G3向下拖动复制到G1001；在“男次”列的C2、C3单元格中分别输入公式“=RANK(D2,D$2:D$1001)”和“=RANK(D3, D$2:D$1001)+COUNTIF(D$2:D2,D3)”，将C3向下拖动复制到C1001。

8.选定A1:H1，点击〖格式〗→〖列〗→〖隐藏〗。再点击〖工具〗→〖选项〗→〖重新计算〗→〖人工重算〗→〖确定〗。

9.选定I2:I1001，点击〖格式〗→〖单元格〗→〖保护〗→选中“锁定”、“隐藏”（前面出现“√”）→〖确定〗；选定J2:M1001，点击〖格式〗→〖单元格〗→〖保护〗→不选中“锁定”、“隐藏”（去掉前面的“√”）→〖确定〗。再点击〖工具〗→〖保护〗→〖保护工作表〗→输入密码→〖确定〗；再点击〖工具〗→〖保护〗→〖保护工作簿〗→输入密码→〖确定〗。

10.点击〖文件〗→〖保存〗→输入文件名“自动分班”，将“保存类型”选为“模板”→〖保存〗。退出。

这样，一个自动分班软件就编制成功了！使用方法如下：①启动Excel，点击〖文件〗→〖新建〗→〖常用〗→选择“自动分班”→〖确定〗。②将姓名、总分、性别的有关信息输入到相应位置。

注意：“性别”项中“男”、“女”前后不得有空格和其它任何字符。在M2单元格中输入年级总班数（否则按6个班进行自动分班）。

③按F9键，即完成自动分班工作。④将I1:L1001复制到新的工作簿上，就可按班排序并编辑（如调整个别特殊学生的班次等）和打印输出。

分班完成后，可以将本次Excel表格上传到YCF帮助学生查询。

2.如何巧用Excel完成文理分

新建一个工作簿，工作簿含四个工作表，用来放四次考试的成绩，分别命名为：I、II、III、IIII。

成绩表I、II、III、IIII的结构相同。 新建一个工作表，命名为tongji，用来做成绩分析统计。

说明：1。 “选科”列根据学生选的科目填写，用“w、h、s、z、l、d”分别代表选物理、化学、生物、政治、历史、地理。

2。 语文、数学、英语下的I、II、III、IIII分别放四次考试相应的科目成绩，这些数据可以通过整列复制过来得到，也可以用跨表引用，如第一次考试的语文成绩：在单元格D4中输入“=I!C3”，然后拖动复制即可。

其余类推。 3。

X科下的I、II、III、IIII分别放四次考试中学生选考科目的成绩，利用Excel的条件判断和跨表引用实现，在单元格P4中输入“=IF(C4="w",I!G3,IF(C4="h",I!H3,IF(C4="s",I!I3,IF(C4="l",I!J3,IF(C4="z",I!K3,IF(C4="d",I!J3)）））））”，这句话的意思为：如果选考物理（C4="w"）则显示工作表I的G3单元格的内容，否则如果选考化学，则显示表I的H3的内容，其余类推。 4。

文（理）综合按上文提及的综合科成绩取法（三科平均分）放相应的分数，在单元格T4中输入“=IF(C4="z",I!F3+I!I3+I!J3,IF(C4="w",I!G3+I!H3+I!K3,IF(C4="h",I!H3+I!G3+I!K3,IF(C4="l",I!I3+I!F3+I!J3,IF(C4="s",I!K3+I!G3+I!H3,IF(C4="d",I!I3+I!F3+I!J3))))))/3”，这句话的意思为：如果选考政治（C4=“Z”），则显示工作表I的F3（政治）、I3（历史）、J3（地理）的平均数，其余类推。 5。

总分下的I、II、III、IIII分别用来计算相应的总分。如在X4中输入“=E4+H4+L4+P4+T4”，其余类推。

6。 总均分则用来计算总分下的I、II、III、IIII平均分。

建表至此完成，通过该表可以获得四次考试中按学生选考科目得出的总分成绩，并且可以分析波动情况。 此外，在考虑选科时，学生大多数会从本身兴趣出发选择，但也有学生可能并不十分了解自己的兴趣，而是希望选择一门自己特长的科目。

在此基础上，我们也可以建立一个选科指引表。该表将学生四次考试中所有的选考科目的总分都计算出来。

学生根据该表可以找出本人特长的科目。 新建一个工作表，表的名字为zhiyin，物理下的I、II、III、IIII分别放四次考试的总分，总分的计算方法是：语文+数学+英语+选考科目+文理综合，文理综合的计算与上同，平均分放四次成绩的平均数，排名则取平均分的名次。

例如，在单元格C3中填入“=I!C3+I!D3+I!E3+I!G3+(I!G3+I!H3+I!K3)/3”，在G3中输入“=AVERAGE(C3:F3)”，在H3中输入“=RANK(G3,G:G,0)”（注：RANK是排名函数，具体参看Excel的帮助），其余类推。 利用这个表我们可以得出学生选科的指引。

比如1号学生，他选择“物理、化学、政治、历史、地理、生物”的总分平均的排名为：2 3、2 3、3 4、2 3、30、22，从这个排名来看，该生选择理科更有优势。