2018年9月二级MS Excel题库公式

2024年1月23日发(作者：沃尔沃s80二手车为什么这么便宜)

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.在\"总体情况表工作表B3.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列相应单元格中。

=(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”，”法律一班

IF(MID(B3,3,2)=”02”,”法律二班”，IF(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)

=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

第8套

8.2 在“停车收费记录”表中，涉及金额的单元格格式均设置为保留2位的数值类型。依据“收费标准”表，利用公式将收费标准对应的金额填入“停车收费记录”表中的“收费标准”列；利用出场日期、时间与进场日期、时间的关系，计算“停放时间”列，单元格格式为时间类型的“XX时XX分”。

E2=VLOOKUP(C2,standard,2,FALSE)

J2=(H2-F2)*24+(I2-G2)

8.3 依据停放时间和收费标准，计算当前收费金额并填入“收费金额”列；计算拟采用的收费政策的预计收费金额并填入“拟收费金额”列；计算拟调整后的收费与当前收费之间的差值并填入“差值”列。

K2=ROUNDUP((HOUR(J2)*60+MINUTE(J2))/15,0)*E2

L2=INT((HOUR(J2)*60+MINUTE(J2))/15)*E2

M2=K2-L2

8.4 将“停车收费记录”表中的内容套用表格格式“表样式中等深浅12”，并添加汇总行，最后三列“收费金额”、“拟收费金额”和“差值”汇总值均为求和。

K551=SUBTOTAL(109,[收费金额])

8.5 在“收费金额”列中，将单次停车收费达到100元的单元格突出显示为黄底红字的货币类型。

无

8.6 建名为“数据透视分析”的表，在该表中创建3个数据透视表，起始位置分别为A3、A11、A19单元格。第一个透视表的行标签为“车型”，列标签为“进场日期”，求和项为“收费金额”，可以提供当前的每天收费情况；第二个透视表的行标签为“车型”，列标签为“进场日期”，求和项为“拟收费金额”，可以提供调整收费政策后的每天收费情况；第三个透视表行标签为“车型”，列标签为“进场日期”，求和项为“差值”，可以提供收费政策调整后每天的收费变化情况。

无

第9套

9.4.通过函数计算每个月的总支出、各个类别月均支出、每月平均总支出；并按每个月总支出升序对工作表进行排序。

总支出：=SUM(C3:M3)

月均支出：=AVERAGE(C3:C14)

9.6.在“年月”与“服装服饰”列之间插入新列“季度”，数据根据月份由函数生成，例如：1至3月对应“1季度”、4至6月对应“2季度”??

=INT(1+(MONTH(A3)-1)/3)&\"季度\"

第10套

10.3在“订单明细”工作表的“单价”列中，利用VLOOKUP公式计算并填写相对应图书的单价金额。图书名称与图书单价的相对应关系了参考工作表“图书单价”。

=VLOOKUP([@图书名称],表2,2,0)

10.4如果每订单的图书销量超过40本(含40本)，则按照图书单价的9.3折进行销售;否则按照图书单价的原价进行销售。按照此规则，计算并填写“订单明细”工作表中每笔订单的“销售额小计”，保留两位数。要求该工作表中的金额以显示精度参与后续的统计计算。

=IF([@销量（本）]>=40,[@单价]*[@销量（本）]*0.93,[@单价]*[@销量（本）])

10.5.根据“订单明细”工作表的“发货地址”列信息，并参考“城市对照”工作表中省市与销售区域的对应关系，计算并填写“订单明细”工作表中每笔订单的“所属区域”。

=VLOOKUP(MID([@发货地址],1,3),表3,2,0)

10.6.根据“订单明细”工作表中的销售记录，分别创建名为“北区”、“南区”、“西区”和“东区”的工作表，这4个工作表中分别统计本销售区域各类图书的累计销售金额，统计格式请参考“Excel素材.xIsx\"文件中的“统计样例”工作表。将这4个工作表中的金额设置为带千分位的、保留两位小数的数值格式。

=GETPIVOTDATA(\"销售额小计\",北区!$A$1,\"图书名称\",\"《Access数据库程序设计》\",\"所属区域\",\"北区\")

=GETPIVOTDATA(\"销售额小计\",南区!$A$1,\"图书名称\",\"《Acces数据库程序设计》\",\"所属区域\",\"南区\")

=GETPIVOTDATA(\"销售额小计\",西区!$A$1,\"图书名称\",\"《Acce数据库程序设计》\",\"所属区域\",\"西区\")

=GETPIVOTDATA(\"销售额小计\",东区!$A$1,\"图书名称\",\"

《Access数据库程序设计》\",\"所属区域\",\"东区\")

10.7.在“统计报告”工作表中，分别根据“统计项目”列的描述，计算并填写所对应的“统计数据”单元

=SUMIFS(表1[销售额小计],表1[日期],\">=2013-1-1\",表1[日期],\"<=2013-12-31\")

=SUMIFS(表1[销售额小计],表1[图书名称],订单明细!D7,表1[日期],\">=2012-1-1\",表1[日期],\"<=2012-12-31\")

=SUMIFS(表1[销售额小计],表1[书店名称],订单明细!C14,表1[日期],\">=2013-7-1\",表1[日期],\"<=2013-9-30\")

=SUMIFS(表1[销售额小计],表1[书店名称],订单明细!C14,表1[日期],\">=2012-1-1\",表1[日期],\"<=2012-12-31\")/12

=SUMIFS(表1[销售额小计],表1[书店名称],订单明细!C14,表1[日期],\">=2013-1-1\",表1[日

期],\"<=2013-12-31\")/SUMIFS(表1[销售额小计],表1[日

期],\">=2013-1-1\",表1[日期],\"<=2013-12-31\")

第11套

11.3.利用sum和average函数计算每一个学生的总分及平均成绩。

=SUM(D2:J2)

=AVERAGE(D2:J2)

11.4. 学号第 3、4 位代表学生所在的班级，例如：\"120105\"代表12级1班5号。请通过函数提取每个学生所在的班级并按下列对应关系填写在\"班级\"列中：

=MID(A2,4,1)&\"班\"

11.6. 通过分类汇总功能求出每个班各科的平均成绩，并将每组结果分页显示。

=SUBTOTAL(1,D2:D7)

第12套

12.1. 分别在\"一季度销售情况表\"、\"二季度销售情况表\"工作表内,计算 \"一季度销售额\"列和\"二季度销售额\"列内容，均为数值型，保留小数点后0位。

=C2*产品基本信息表!C2

12.2. 在 \"产品销售汇总图表\"内,计算\"一二季度销售总量\"和\"一二季度销售总额\"列内容，数值型，保留小数点后0位；在不改变原有数据顺序的情况下，按一二季度销售总额给出销售额排名。

=一季度销售情况表!C2+\'二季度销售情况表 \'!C2

=一季度销售情况表!D2+\'二季度销售情况表 \'!D2

=RANK(D2,$D$2:$D$21,0)

第13套

13.5参考考生文件夹下的\"工资薪金所得税率.xlsx\"，利用IF函数计算 \"应交个人所得税\"列。 (提示：应交个人所得税=应纳税所得额*对应税率-对应速算扣除数)

= IF(K3<=1500,K3*3/100,IF(K3<=4500,K3*10/100-105,IF(K3<=9000,K3*20/100-555,IF(K3<=35000,K3*25%-1005,IF(K3<=5500

0,K3*30%-2755,IF(K3<=80000,K3*35%-5505,IF(K3>80000,K3*45%-13505)))))))

13.6 利用公式计算\"实发工资\"列，公式为：实发工资=应付工资合计-扣除社保-应交个人所得税。

=I3-J3-L3

第14套

14.4.将工作表“平均单价”中的区域b3：c7定义名称为“商品均价”。运用公式计算工作表“销售情况”中f列的销售情况，要求在公式中通过vlookup函数自动在工作表“平均单价”中查找相关商品的单价，并在公式中引用所定义的名称“商品均价”。

=VLOOKUP(D4,商品均价,2,0)*E4

第15套

15.2. 根据图书编号，请在\"订单明细表\"工作表的\"图书名称\"列中，使用VLOOKUP函数完成图书名称的自动填充。\"图书名称\"和\"图书编号\"的对应关系在\"编号对照\"工作表中。

=VLOOKUP([@图书编号],表2[#全部],2,0)

15.3. 根据图书编号，请在\"订单明细表\"工作表的\"单价\"列中，使用VLOOKUP函数完成图书单价的自动填充。\"单价\"和\"图书编号\"的对应关系在\"编号对照\"工作表中。

=VLOOKUP([@图书编号],表2[#全部],3,0)

15.4. 在\"订单明细表\"工作表的\"小计\"列中，计算每笔订单的销售额。

=[@单价]*[@销量（本）]

15.5. 根据\"订单明细表\"工作表中的销售数据，统计所有订单的总销售金额，并将其填写在\"统计报告\"工作表的B3单元格中。

=SUM(表3[小计])

15.6. 根据\"订单明细表\"工作表中的销售数据，统计《MS Office高级应用》图书在2012年的总销售额，并将其填写在\"统计报告\"工作表的B4单元格中。

=SUMIFS(表3[小计],表3[图书名称],\"《MS Office高级应用》\",表3[日期],\">=2012-1-1\",表3[日期],\"<=2012-12-31\")

15.7. 根据\"订单明细表\"工作表中的销售数据，统计隆华书店在2011年第3季度的总销售额，

并将其填写在\"统计报告\"工作表的B5单元格中。

=SUMIFS(表3[小计],表3[书店名称],\"隆华书店\",表3[日期],\">=2011-7-1\",表3[日期],\"<=2011-9-30\")

15.8. 根据\"订单明细表\"工作表中的销售数据，统计隆华书店在2011年的每月平均销售额（保留2位小数），并将其填写在\"统计报告\"工作表的B6单元格中。

=SUMIFS(表3[小计],表3[书店名称],\"隆华书店\",表3[日期],\">=2011-1-1\",表3[日期],\"<=2011-12-31\")/12

第16套

16.2．如果“日期”列中的日期为星期六或星期日，则是“是否加班”列的单元格中显示“是”，否则显示“否”（必须使用公式）。

=If(WEEKDAY(A3,2)>5,”是”,”否”)

16.3．使用公式统计每个活动地点所在的省份或直辖市，并将其填写在“地区”列所对应的单元格中，例如“北京市”、“浙江省”

=LEFT(C3,3)

16.4．依据“费用类别编号”列内容，使用VLOOKUP函数，生成“费用类别”列内容，对照关系参考“费用类别”工作表。

=VLOOKUP(E3,费用别类!$A$3:$B$12,2,FALSE)

16.5．在“差旅成本分析报告”工作表B3单元格中，统计2013年第二季度发生在北京市的差旅费用总金额。

=SUMIFS(费用报销管理!G3:G401,费用报销管理!A3:A401,”>=2013-04-01”,费用报销管理!A3:A401,”<=2013-06-30”,费用报销管理！D3:D401,”北京市”)

16.6．在“差旅成本分析报告”工作表B4单元格中，统计2013年员工钱顺卓报销的火车票费用总额。

=SUMIFS(费用报销管理!G3:G401,费用报销管理!B3:B401,”钱顺卓”,费用报销管理!F3:F401,”火车票”)

16.7在“差旅成本分析报告”工作表B5单元格中，统计2013年差旅费用中，飞机票费用占所有报销费用的比例，并保留2位小数。

=SUMIF(费用报销管理!F3:F401,”飞机票”,费用报销管理!G3:G401)/SUM(费用报销管理!G3:G401)

16.8在“差旅成本分析报告”工作表B6单元格中，统计2013年发生在周末（星期六或星期日）的通讯补助总金额。

=SUMIFS(费用报销管理!G3:G401,费用报销管理!H3:H401,”是”,费用报销管理!F3:F401,”通讯补助”)

第17套

17.2 在“销售业绩表”工作表的“个人销售总计”列中，通过公式计算每名销售人员1月~6月的销售总和。

=SUM(D3:I3)

17.3 依据“个人销售总计”列的统计数据，在“销售业绩表”工作表的“销售排名”列中通过公式计算销售排行榜，个人销售总计排名第-一的，显示“第1名”;个人销售总计排名

第二的，显示“第2名”;以此类推。

=\"第\"&([@个人销售总计][个人销售总计])&\"名\"

17.4 在“按月统计”工作表中，利用公式计算1月~6月的销售达标率，即销售额大于60000元的人数所占比例，并填写在“销售达标率”行中。要求以百分比格式显示计算数据，并保

留2位小数。

=COUNTIF(表1[一月份].\">60000\")/COUNT(表1[-月份])

17.5 在“按月统计”工作表中，分别通过公式计算各月排名第1、第2和第3的销售业绩，并填写在“销售第一名业绩”、“销售第二名业绩”和“销售第三名业绩”所对应的单元格中。要求使用人民币会计专用数据格式，并保留2位小数.

=LARGE(表1[一月份],1)

=LARGE(表1[一月份],2)

E5 : =LARGE(表1[四月份],3)

=LARGE(表1[一月份],3)

E6: =LARGE(表1[四月份],4)

第18套

18.5. 在“销售记录”工作表的E4:E891中，应用函数输入C列（类型）所对应的产品价格

=VLOOKUP(C4,价格表!$B$2:$C$5,2,0)

18.6. 在“销售记录”工作表的F4:F891中，计算每笔订单记录的金额，应用货币格式，计算规则：金额=价格*数量*（1-折扣百分比），折扣百分比由订单中的订货数量和产品类型决定

=D4*E4*(1-VLOOKUP(C4，折扣表!$B$9:$F$11,IF(D4<1000,2,IF(D4<1500,3,IF(D4<2000,4,5)))))

18.7. 将“销售记录”工作表的单元格区域A3:F891中所有记录居中对齐，并将发生在周六或周日的销售记录的单元格的填充颜色设置为黄色

=OR(WEEKDAY($B4,2)=6,WEEKDAY($B4,2)=7)

第19套

17.4. 在工作表“员工基础档案”中，利用公式及函数依次输入每个学生的性别“男”或“女”，出生日期“××××年××月××日”，每位员工截止2015年9月30日的年龄、工龄工资、基本月工资。其中：

① 身份证号的倒数第2位用于判断性别，奇数为男性，偶数为女性；

② 身份证号的第7~14位代表出生年月日；

③ 年龄需要按周岁计算，满1年才计1岁，每月按30天、一年按360天计算；

④ 工龄工资的计算方法：本公司工龄达到或超过30年的每满一年每月增加50元、不足10年的每满一年每月增加20元、工龄不满1年的没有工龄工资，其他为每满一年每月增加30元。

⑤ 基本月工资=签约月工资+月工龄工资

=IF(MOD(MID(E2,17,1),2)=1,\"男\",\"女\")

=MID(E2,7,4)&\"年\"&MID(E2,11,2)&\"月\"&MID(E2,13,2)&\"日\"

=IF(K2<1,0,IF(K2<10,K2*20,IF(K2<30,K2*30,K2*50)))

=M2+L2

19.5. 参照工作表“员工基础档案”中的信息，在工作表“年终奖金”中输入与工号对应的员工姓名、部门、月基本工资；按照年基本工资总额的15%计算每个员工的年终应发奖金。

=VLOOKUP(A5,档案,14,0)

=D6*12*0.15

19.6. 在工作表“年终奖金”中，根据工作表“个人所得税税率”中的对应关系计算每个员工年终奖金应交的个人所得税、实发奖金，并填入G列和H列。年终奖金目前的计税方法是：

① 年终奖金的月应税所得额=全部年终奖金÷12

② 根据步骤①计算得出的月应税所得额在个人所得税税率表中找到对应的税率

③ 年终奖金应交个税=全部年终奖金×月应税所得额的对应税率-对应速算扣

除数

④ 实发奖金=应发奖金-应交个税

=E6/12

=IF(F6<=1500,E6*0.03,IF(F6<=4500,E6*0.1-105,IF(F6<=9000,E6*0.2-555,IF(F6<=35000,E6*0.25-1005,IF(F6<=55000,E6*0.3-2755,IF(F6<=80000,E6*0.35-5505,E6*0.45-13505))))))

=E6-G6

19.7. 根据工作表“年终奖金”中的数据，在“12月工资表”中依次输入每个员工的“应发年终奖金”、“奖金个税”，并计算员工的“实发工资奖金”总额。（实发工资奖金=应发工资奖金合计-扣除社保-工资个税-奖金个税）

=VLOOKUP(A5,年终奖金!$A$4:$H$71,5,0)

=VLOOKUP(A5,年终奖金!$A$4:$H$71,7,0)

=H6-I6-K6-L6

19.8. 基于工作表“12月工资表”中的数据，从工作表“工资条”的A2单元格开始依次为每位员工生成样例所示的工资条，要求每张工资条占用两行、内外均加框线，第1行为工号、姓名、部门等列标题，第2行为相应工资奖金及个税金额，两张工资条之间空一行以便剪裁、该空行行高统一设为40默认单位，自动调整列宽到最合适大小，字号不得小于10磅。

=CHOOSE(MOD(ROW(),3)+1,OFFSET(\'12月工资表\'!E$3,ROW()/3,),\"\",\'12月工资表\'!E$3)

第20套

20.5. 在数据区域的最右侧增加“完成情况”列，在该列中按以下规则、运用公式和函数填写统计结果：当左侧三项“是否填报”、“是否审核”、“是否通知客户”全部为“是”时显示“完成”，否则为“未完成”，将所有“未完成”的单元格以标准红色文体突出显示。

=IF(AND(F7=\"是\",G7=\"是\",H7=\"是\"),\"完成\",\"未完成\")

20.6. 在“完成情况”列的右侧增加“报告奖金”列，按照下列要求对每个报告的员工奖金数进行统计计算（以元为单位）。另外当完成情况为“完成”时，每个报告多加30元奖金，未完成时没有额外奖金：

报告收费金额（元） 奖金（元/每个报告）

小于等于1000 100

大于1000小于等于2800 报告收费金额的8%

大于2800 报告收费金额的10%

=IF(I3=\"完成\",IF(D3<=1000,100,IF(D3<=2800,D3*8%,D3*10%))+30,IF(D3<=1000,100,IF(D3<=2800,D3*8%,D3*10%)))

20.9. 在工作表“员工个人情况统计”中，对每位员工的报告完成情况及奖金数进行计算统计并依次填入相应的单元格。

=COUNTIF(表1[责任人],[@姓名])

=COUNTIFS(表1[责任人],[@姓名],表1[报告修改次数],0)

=COUNTIFS(表1[责任人],[@姓名],表1[报告修改次数],1)

=COUNTIFS(表1[责任人],[@姓名],表1[报告修改次数],2)

=COUNTIFS(表1[责任人],[@姓名],表1[报告修改次数],3)

=COUNTIFS(表1[责任人],[@姓名],表1[报告修改次数],4)

=SUMIF(表1[责任人],[@姓名],表1[报告奖金])

=SUBTOTAL(109,B3:B7)

第21套

21.6 在“咨询商品编码”与“预购类型”之间插入新列，列标题为“商品单价”，利用公式，将工作表“商品单价”中对应的价格填入该列。

=VLOOKUP([@咨询商品编码],商品单价!$A$2:$B$7,2,0)

21.7 在“成交数量”与“销售经理”之间插入新列，新列标题为“成交金额”，根据“成交数量”和“商品单价”，利用公式计算并填入“成交金额”。

=[@成交数量]*[@商品单价]

21.9打开“月统计表”工作表，利用公式计算每位销售经理每月的成交金额，并填入对应位置，同时计算“总和”列，“总计”行。

张乐一月 =SUMIFS(表1[成交金额],表1[销售经理],A3,表1[咨

询日期],\">=2014-1-1\",表1[咨询日期],\"<=2014-1-31\")

张乐二月 =SUMIFS(表1[成交金额],表1[销售经理],A3,表1[咨询日期],\">=2014-2-1\",表1[咨询日期],\"<=2014-2-28\")

张乐三月 =SUMIFS(表1[成交金额],表1[销售经理],A3,表1[咨询日期],\">=2014-3-1\",表1[咨询日期],\"<=2014-3-31\")

总和 =SUM(B3:D3)

总计 =SUM(B3:B5)

第22套

22.3④在 “部门代码”中填入相应的部门代码，其中准考证后的前三位为部门代码。

=VLOOKUP(MID(B4,5,2),LEFT(行政区划代码!B$4:B$38,2),MID(行政区划代码! B$4:B$38,4,LEN,(行政区划代码! B$4:B$38)-3))

=LEFT(B4,3)

⑤准考证号第4位代表考试类别，按照下列计分规则计算每个人的总成绩：

准考证号的第4位

1

2

考试类别

A类

B类

计分方法

笔试面试各占50%

面试占60%、面试占40%

=IF(MID(B4,4,1)=”1”,J4*0.5+K4*0.5,J4*0.6+K4*0.4)

22.5②将各项数据填入相应单元格，其中统计男女人数时应使用函数并应用已定义的名称，最低笔试分数线按部门统计。

=COUNTIFS(部门代码,B5,性别,”女”)

=COUNTIFS(部门代码,B5,性别,”男”)

=D5+E5

=D5/F5

=MIN(IF(名单!F$4:F$1777=统计分析!B5,名单!J$4:J$1777))

③对工作表“统计分析”设置条件格式，令其只有在单元格非空时才会自动以某一浅色填充偶数行、且自动添加上下边框线。

=MOD(ROW(),2)=0

第23套

在“性别”和“部门代码”之间插入一个空列，列标题为“地区”。自左向右准考证5、6位为地区代码，根据工作表“行政区划代码”的对应关系，在地区列填入地区名称。

=LOOKUP(MID(B4,5,2),LEFT(行政区划代码!B$4:B$38,2),MID(行政区划代码!B$4:B$38,4,LEN(行政区划代码!B$4:B$38)-3))

在“部门代码”列中填入相应的部门代码，其准考证前三位为部门代码。

=LEFT(B4,3)

准考证号第4位代表考试类别，按照下列计分规则计算下列总成绩

准考证号第4位

1

2

考试类别

A类

B类

计分方法

面试笔试各占50%

笔试占60%面试占40%

=IF(MID(B4,4,1)=\"1\",J4*0.5+K4*0.5,J4*0.6+K4*0.4)

正确的准考证号为12位文本，面试分数的范围为0—100之间的整数（含本数），试检测这两列数据的有效性，当输入错误时给出提示信息“超出范围请重新输入！”，以标准红色文本标出存在的错误数据。

=(len(b4<>12))

将各项数据填入相应的单元格，其中统计男女人数时应使用函数并应用已定义的名称，最低笔试分数线按部门统计

=COUNTIFS(名单!F$4:F$1777,B5,名单!D$4:D$1777,\"女\")

=COUNTIFS(名单!F$4:F$1777,B5,名单!D$4:D$1777,\"男\")

=D5+E5

=D5/F5

=MIN(IF(名单!F$4:F$1777=统计分析!B5,名单!J$4:J$1777))

对工作表“统计分析”设置条件格式，令其只有在单元格非空时才会自动以某一浅色填充偶数行、且自动添加上下边框线。

=mod(row(),2=0)

图标标题与数据上方第1行的标题内容一致并可同步变化

=统计分析!$B$1

第24套

24.2.在“成本分析”工作表的单元格区域F3:F15，使用公式计算不同订货量下的年订货成本，公式为“年订货成本=（年需求量/订货量）×单次订货成本”，计算结果应用货币格式并保留整数。

=$C$2/E3*$C$3

24.3.在“成本分析”工作表的单元格区域G3:G15，使用公式计算不同订货量下的年存储成本，公式为“年存储成本=单位年存储成本×订货量×0.5”，计算结果应用货币格式并保留整数。

=$C$4*E3*0.5

24.4.在“成本分析”工作表的单元格区域H3:H15，使用公式计算不同订货量下的年总成本，公式为“年总成本=年订货成本+年储存成本”，计算结果应用货币格式并保留整数。

=F3+G3

24.7.在工作表“经济订货批量分析”的C5单元格计算经济订货批量的值，公式为：

，计算结果保留整数。

=SQRT(2*C2*C3/C4)”

24.8.在工作表“经济订货批量分析”的单元格区域B7:M27创建模拟运算表，模拟不同的年需求量和单位年储存成本所对应的不同经济订货批量；其中C7:M7为年需求量可能的变化值，B8:B27为单位年

储存成本可能的变化值，模拟运算的结果保留整数。

=SQRT(2*C2*C3/C4)”

第25套

25.2 在“主要城市降水量”工作表中，将A列数据中城市名称的汉语拼音删除，并在城市名后面添加文本“市”，如“北京市”；

=LEFT(A2,LENB(A2)-LEN(A2))&\"市\"

25.5 在单元格区域N2:N32中计算各城市全年的合计降水量，对其应用实心填充的数据条条件格式，并且不显示数值本身；

=SUM(降水量统计[@[1月]:[12月]])

25.6 在单元格区域O2:O32中，根据“合计降水量”列中的数值进行降序排名；

=(N2,$N$2:$N$32,0)

说明：RANK.和函数功能相同。

25.8在S3单元格中建立公式，使用Index函数和Match函数，根据R3单元格中的城市名称和S2单元格中的月份名称，查询对应的降水量；

=INDEX(降水量统计[[城市（毫米）]:[12月]],MATCH(R3,降水量统计[城市（毫米）],0),MATCH(S2,降水量统计[[#标题],[城市（毫米）]:[12月]],0))

第26套

26.3. 在“订单明细”工作表中，完成下列任务：

① 根据B列中的产品代码，在C列、D列和E列填入相应的产品名称、产品类别和产品单价（对应信息可在“产品信息”工作表中查找）。

=VLOOKUP(B2,产品信息,2,0)

=VLOOKUP(B2,产品信息,3,0)

=VLOOKUP(B2,产品信息,4,0)

② 设置G列单元格格式，折扣为0的单元格显示“-”，折扣大于0的单元格显示为百

分比格式，并保留0位小数（如15%）。

③ 在H列中计算每订单的销售金额，公式为“金额=单价×数量×(1-折扣)”，设置E列和H列单元格为货币格式，保留2位小数。

=E2*F2*(1-G2)

26.4. 在“订单信息”工作表中，完成下列任务：

① 根据B列中的客户代码，在E列和F列填入相应的发货地区和发货城市（提示：需首先清除B列中的空格和不可见字符），对应信息可在“客户信息”工作表中查找。

=TRIM(CLEAN(B2))

=VLOOKUP(B2,客户信息,6,0)

=VLOOKUP(B2,客户信息,5,0)

② 在G列计算每订单的订单金额，该信息可在“订单明细”工作表中查找（注意：一个订单可能包含多个产品），计算结果设置为货币格式，保留2位小数。

=SUMIF(订单明细!$A$2:$A$907,订单信息!A2,订单明细!$H$2:$H$907)

5. 在“产品类别分析”工作表中，完成下列任务：

① 在B2:B9单元格区域计算每类产品的销售总额，设置单元格格式为货币格式，保留2位小数；并按照销售额对表格数据降序排序。

=SUMIF(订单明细!$D$2:$D$907,A2,订单明细!$H$2:$H$907)

7. 在“客户信息”工作表中，根据每个客户的销售总额计算其所对应的客户等级（不要改变当前数据的排序），等级评定标准可参考“客户等级”工作表；使用条件格式，将客户等级为1级～5级的记录所在单元格填充颜色设置为“红色”，字体颜色设置为“白色,背景1”。

=IFERROR(CHOOSE(INT(SUMIF(订单信息！$B$2：$B$324,客户信息！A2,订单信息！$G$2:$G$342)/5000)+1

=IF(VALUE(LEFT($G2,FIND(“级”$G2)-1))<=5,TRUE,FALSE)

=VLOOKUP(SUMIF(订单信息!$B$2:$B$342,A61,订单信息!$G$2:$G$342),客户等级!$A$2:$B$11,2,1)

第27套

27.2.按照下列要求对素材“ExceLxsx\"中的4个工作表“第1周”~“第4周”中的原始数据进行完善:

②分别计算每个工作表中4个销售部的周销量合计值，并填入G列。

=SUM(C2:F2)

⑧分别计算每个工作表中的周销售总额，并填入H列。

=B2*G2

⑤将4个工作表中的数据以求和方式合并到新工作表“月销售合计”中，合并数据自工作表“月销售合计”的A1单元格开始填列。

A1:H106

27.3.按照下列要求对新工作表“月销售合计”中的数据进行调整、完善:

⑧删除B列中的合并单价，根据合并后的月销量及月销售总额重新计算平均单价。

=H2/G2

27. 5.根据工作表“品种目录”中的数据，在工作表“月销售合计”的B列中为每个菜品填入相应的“类别”，如果某一菜品不属于“品种目录”的任何一一个类别，则填入文本“其他”。

“=IFERROR(VLOOKUP(A2,品种目录!SBS2:$DS918.3,0).\"其他\")”

计算机二级excel公式总结

1、求和函数： SUM =SUM ( A1:A5 , 5 ) 等于 60

2、条件求和函数：SUMIF = SUMIF ( A2 : A6 , “ 01 ” , F2 : F6 )

3、求平均值函数： AVERAGE =AVERAGE(A1:A5, 5) 等于 10

4、 最大（小）值函数： MAX（ MIN） = MAX(A1:A5) 等于 27

5、统计数值型数据个数函数： COUNT = COUNT (A1:A5) 等于 3

6、条件计数函数： COUNT IF =COUNTIF (A3:A6, \"??p* \") 等于 3

7、统计非空白单元格个数函数： COUNTA

8、 查找函数： VLOOKUP =VLOOKUP (1,A2:C10,3,TRUE)

9、排位函数： RANK ( 排序数 , 范围, 排位方式) =RANK( A3 , A2:A6 , 1 )

10、 当前日期函数： TODAY（）功能：返回计算机系统的当前日期。

11、求出给定“日期”的年份。： YEAR=YEAR(\"2006/04/24\") 等于 2006

12、当前日期和时间函数： NOW（）功能：返回计算机系统的当前日期和当前时间。

13、IF（条件X，值1，值2） =IF（D3>=80 ，“优良”,IF(D3>=60,“中”，“不及格”)

14、逻辑“与”函数： AND 条件： 80 <= C2 < 90, 则表示成 AND（ C2 >= 80 ，C2 <

90）

15、逻辑“或”函数： OR 成绩大于等于80 或者 成绩小于90, OR（ C2 >= 80 ，C2 <

90 ）

16、 左边截取函数： LEFT（X，n） = LEFT (“陈列室”, 1 ) 结果为 “陈”。

17、右边截取函数： RIGHT（X ，n）

18、求余数函数: MOD(X1,X2)，返回X1/X2的余数,结果的正负号与除数(X2)相同

19、四舍五入函数: ROUND( X , n )

20、中间截取函数： MID（X ，m ，n） = MID ( \"Fluid Flow\" , 1 , 5 ) 等于 \"Fluid\"

21、求字串长度函数： LEN（X） = LEN ( “abcdefg”) 结果为7 。

其他考试公式总结：

1、 =SUMPRODUCT(1*(E3:E30=”《认命》”),H3:H30)

=SUMPRODUCT(1*(C350:C461=”隆华书店”),H350:H461),季度需要先排序

=SUMPRODUCT(1*(D3:D17=\"销售\"),M3:M17)

2、 RANK(D2,$D$2:$D$21,0)，0是降序。 =\"第\"&RANK(F2,$F$2:$F$45)&\"名\" ，第几名。

3、 LOOKUP(MID(A2,3,2),{\"01\",\"02\",\"03\"},{\"1班\",\"2班\",\"3班\"})\"，从第三个字符起，取两个。

IF(MID(A3,4,2)=\"01\",\"1班\",IF(MID(A3,4,2)=\"02\",\"2班\",\"3班\"))

4、 VLOOKUP(A3,学号对照!$A$3:$B$20,2,FALSE)

5、 =MID(F3,7,4)&\"年\"&MID(F3,11,2)&\"月\"&MID(F3,13,2)&\"日\"

6、 INT((TODAY()-I3)/365)

7、 \"=AVERAGEIF(员工档案!H3:H37,\"本科\"，员工档案!K3:K37)\"

8、 =ROUND(IF(K3<=1500,K3*3/100,IF(K3<=4500,K3*10/100-105,IF(K3<=9000,K3*20/100-555,IF(K3<=35000,K3*25%-1005,IF(K3<=5500,K3*30%-2755,IF(K3<=80000,K3*35%-5505,IF(K3>80000,K3*45%-13505))))))),2) 。应交个人所得税。

“=IF(F2>=102,\"优秀\",良好\",IF(F2>=72,\"及格\",IF(F2>72,\"及格\",\"不及格\"))))”

9、 SUBTOTAL(109,B4:B11)”. 109=sum, 对AL31-AL34求和.

10、 =IF(MOD(MID(C2,17,1),2)=1,\"男\",\"女\")”输入男女生性别。

11、 TEXT(MID(C2,7,8),\"0-00-00\")”出生年月日。

12、 =DATEDIF(--TEXT(MID(C2,7,8),\"0-00-00\"),TODAY(),\"y\")，年龄计算。