2018年9月二级MS Excel题库公式大全
第1套
1.3. 在工作表“初三”中，利用公式及函数依次输入每个学生的性别“男”或“女”、出生日期“××××年××月××日”和年龄。其中：身份证号的倒数第2位用于判断性别，奇数为男性，偶数为女性；身份证号的第7~14位代表出生年月日；
=IF(MOD(MID(C2,17,1),2)=1,"男","女")
=MID(C2,7,4)&"年"&MID(C2,11,2)&"月"&MID(C2,13,2)&"日" 1.4. 参考工作表“初三”，在工作表“语文”中输入与学号对应的“姓名”；按照平时、期中、期末成绩各占30%、30%、40%的比例计算每个学生的“学期成绩”并填入相应单元格中；按成绩由高到低的顺序统计每个学生的“学期成绩”排名并按“第n名”的形式填入“班级名次”列中；按照下列条件填写“期末总评”：
=VLOOKUP(A26，档案[[学号]:[姓名]],2,0)
=C2*30%+D2*30%+E2*40%
="第"&RANK(F2,$F$2:$F$45)&"名"
1.6. 分别将各科的“学期成绩”引入到工作表“期末总成绩”的相应列中，在工作表“期末总成绩”中依次引入姓名、计算各科的平均分、每个学生的总分，并按成绩由高到底的顺序统计每个学生的总分排名、并以1、2、3……形式标识名次，最后将所有成绩的数字格式设为数值、保留两位小数。
=VLOOKUP(A3，档案[[学号]:[姓名]],2,0)
=VLOOKUP(A3，语文!$A$2:$F$45,6,0)
=VLOOKUP(A3，英语!$A$2:$F$45,6,0)（物理化学品德等）
=AVERAGE(C3:C46)
=RANK(J3,$J$3:$J$46,0)
第2套
2.4. .在“法一”、“法二”、“法三”、“法四"工作表中，利用公式分别计算"总分”、平均分”、班内排名列的值和最后一行平均分的值
=SUM(C3:K3)
=AVERAGE(C3:K3)
=RANK(L3,$L$3:$L$27,0)
=AVERAGE(C3:C27)
2.6.在"总体情况表工作表B
3.J6单元格区域内，计算填充各班级每门课程的平均成绩；并计算"总分”、平均分”、“总平均分”、“排名
=SUM(B3:J3)
=AVERAGE(B3:J3)
=RANK(K3,$K$3:$K$56,0)
=AVERAGE(B3:B6)
第3套
3.2.参照“产品基本信息表”所列，运用公式或函数分别在工作表“一季度销售情况表”、“二季度销售情况表”中，填入各型号产品对应的单价，并计算各月销售额填入F列中。
=VLOOKUP(B2，产品基本信息表!$B$2:$C$21,2,0)
=D2*E2
3.3在“产品销售汇总表”中，分别计算各型号产品的一、二季度销量、销售额及合计数，填入相应列中。
=SUMIF(一季度销售情况表!$B$2:$B$44,B2，一季度销售情况表!$D$2:$D$44)
=SUMIF(一季度销售情况表!$B$2:$B$44,B2，一季度销售情况表!$F$2:$F$44)
=C2+E2
=D2+F2
3.4. 在“产品销售汇总表”中，在不改变原有数据顺序的情况下，按一二季度销售总额从高到低给出销售
额排名，填入I列相应单元格中。
=RANK.EQ(H2,$H$2:$H$21,0)
第4套
4.5.  在合并后的工作表“比较数据”中的数据区域最右边依次增加“人口增长数”和“比重变化”两列，计算这两列的值，并设置合适的格式。其中：人口增长数=2010年人口数-2000年人口数；比变化=2010年比重-2000年比重。
=B2-D2
=C2-E2
4.7.  在工作薄“统计指标.xlsx”的工作表“统计数据”中相应的单元格内填入统计结果。
=SUM(表1[2000年人口数（万人）])
=SUM(表2[2010年人口数（万人）])
=SUM(表3[人口增长数])
（插入sum函数，选中第五次普查数据2000年人口数（万人）数据B2:B34，点击确定，依次同上）
第5套
5.3. 在“2012级法律”工作表中，利用公式分别计算“总分”、“平均分”、“年级排名”列
的值。
=SUM(D3:L3)
=AVERAGE(D3:L3) 或 =AVERAGE(M3/9)
=RANK(M3,M$3:M$102,0)
5.4.  在“2012级法律”工作表中，利用公式、根据学生的学号、将其他班级的名称填入“班级”列，规则为：学号的第三位为专业代码、第四位代表班级序号，即01为“法律一班”，02为“法律二班”，03为“法律三班”，04为“法律四班”。
=IF(MID(B3,3,2)=”01”，”法律一班
I F(MID(B3,3,2)=”02”,”法律二班”，
I F(MID(B3,3,2)=”03”,”法律三班”，”法律四班”)))
第6套
6.1.在“销售订单”工作表的“图书编号”列中，使用VLOOKUP函数填充所对应“图书名称”的“图书编号”，“图书名称”和“图书编号”的对照关系请参考“图书编目表”工作表。
=VLOOKUP([@图书名称]，表3,2,0)
6.3、在“2013年图书销售分析”工作表中，统计2013年各类图书在每月的销售量，并将统计结果填充在所对应的单元格中。为该表添加汇总行，在汇总行单元格中分别计算每月图书的总销量。
每月销售量：  =SUMIFS(表1[销量（本）]，表1[图书名称],A4，表1[日期],">=2013-1-1"，表1[日期],"<2013-2-1")
汇总每月图书的销售量    =SUM(B4:B11)
第7套
7.2 利用“成绩单”、“小分统计”和“分值表”工作表中的数据，完成“按班级汇总”和“按学校汇总”工作表中相应空白列的数值计算。具体提示如下：
⑴“考试学生数”列必须利用公式计算，“平均分”列由“成绩单”工作表数据计算得出；按班级汇总：
=COUNTIFS(成绩单!$A$2:$A$950，按班级汇总!$A2，成绩单!$B$2:$B$950，按班级汇总!$B2)
=AVERAGEIFS(成绩单!$D$2:$D$950，成绩单!$A$2:$A$950，按班级汇总!$A2，成绩单!$B$2:$B$950，按班级汇总!$B2)
=COUNTIF(成绩单!$A$2:$A$950，按学校汇总!$A2)
mid函数提取年月日=AVERAGEIFS(成绩单!$D$2:$D$950，成绩单!$A$2:$A$950，按学校汇总!$A2)
⑶“小分统计”工作表中包含了各班级每一道小题的平均得分，通过其可计算出各班级的“客观题平均分”和“主观题平均分”。（备注：由于系统生成每题平均得分时已经进行了四舍五入操作，因此通过其计算“客观题平均分”和“主观题平均分”之和时，可能与根据“成绩单”工作表的计算结果存在一定误差）；
=SUM(小分统计!$C2:$AP2)
=SUM(小分统计!$AQ2:$AZ2)
⑷利用公式计算“按学校汇总”工作表中的“客观题平均分”和“主观题平均分”，计算方法为：每个学校的所有班级相应平均分乘以对应班级人数，相加后再除以该校的总考生数；
=SUM((按班级汇总!$A$2:$A$33=按学校汇总!$A2)*(按班级汇总!$C$2:$C$33)*(按班级汇总!$G$2:$G$33))/$B2
=SUM((按班级汇总!$A$2:$A$33=按学校汇总!$A2)*(按班级汇总!$C$2:$C$33)*(按班级汇总!$H$2:$H$33))/$B2
⑸计算“按学校汇总”工作表中的每题得分率，即：每个学校所有学生在该题上的得分之和除以该校总考生数，再除以该题的分值。
=SUM((小分统计!$A$2:$A$33=$A2)*小分统计!C$2:C$33*按班级汇总!$C$2:$C$33)/$B2/分值表!B$3

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。