2016年计算机二级MS-Office-Excel函数公式大集合

1、2016 年计算机二纺 MS-Office-Excel 函数公式大集 合第1套(2)图书名称=VLOOKUP(D3,编号对照！$A$3:$C$19,2,FALSE)(3)单价=VLOOKUP(D3,编号对照！$A$3:$C$19,3,FALSE)小计=单价口销量(本)(5)所有订单的总销售额=SUM(订单明细表！H3:H636)撤销计划列”(6)MS Office高级应用图书在2012年的总销售额降序”=SUMPRODUCT(1*(订单明细表!E3:E262= "MS Office 高级应用”)订单明细 表!H3:H262)(7)隆华书店在2011年第3季度(7月1日9月30日)的总

2、销售额=SUMPRODUCT(1*(订单明细表！C305c461 = "隆华书店”订单明细表！H350:H461)(8)隆华书店在2011年的每月平均销售额(保留2位小数)=SUMPRODUCT(1*( 订单明细表！C262c636 ="隆华书店”)，订单明细 表!H263:H636)/12 使用 SUMIFS :(5) =SUBTOTAL(9,订单明细表!H3:H636)(6) =SUMIFS(订单明细表！H3:H636，订单明细表！E3:E636，订单明细表!E19,订单明细表 旧3:B636,”>=2012-1-1",订单明细表 旧3:B636,“&l

3、t;=2012-12-31") (7) =SUMIFS(订单明细表！H3:H636，订单明细表！C3c636，订单明细表!C12,订单明细表 旧3:B636,”>=2011-7-1",订单明细表 旧3:B636,“<=2011-9-30”)(8) =SUMIFS(订单明细表！H3:H636，订单明细表！C3c636，订单明细表 ！C12,订单明细表旧3:B636,”>=2011-1-1", 订单明细 表!B3:B636,“<=2011-12-31")/12注意：用SUNPRODUCT函数之前先排序，尤其是涉及时间的时候。第2套(4

4、)班级=LOOKUP(MID(A2,3,2), “ 01“，“ 02“，" 038),“班 1“，“班" )第3套(1) 一季度销售额(元)、二季度销售额(元)=产品基本信息表！C2*C2(2) 一二季度销售总量、一二季度销售总额二一季度销售情况表！C2+'二季度销售情况表!C2二一季度销售情况表！D2+'二季度销售 情况表!D2(3)销售额排名=RANK(D2,$D$2:$D$21,0) RANK 函数排序区域绝对引用。第4套合并计算双击工作表sheet3的表名，在编辑状态下输入"比较数据”。在该工作表的A1中输入” 地区”，在【数据】选项卡的【

5、数据工具】组中单击 “合并计算”按钮，弹出“合并计算” 对话框，设置"函数"为"求和"，在"引用位置"文本框中键入第一个区域"第五次普查数 据!$A$1:$C$34",单击"添加"按钮，键入第二个区域"第六次普查数据！$A$1:$C$34", 单击"添加"按钮，在"标签位置"下勾选"首行"复选框和"最左列”复选框，然后单击" 确定"按钮。数据透视表筛选单击行标签右侧的"标签筛选

6、"按钮，在弹出的下拉列表中选择"值筛选"，打开级联菜 单，选择"大于"，弹出"值筛选(地区)”对话框，在第一个文本框中选择"求和项：2010 年人口数(万人)”，第二个文本框选择“大于"，在第三个文本框中输入"5000”,单击 "确定"按钮。在日期后添加星期在"费用报销管理”工作表中，选中“日期”数据列，单击鼠标右键，在弹出的快捷菜单 中选择”设置单元格格式”命令，弹出”设置单元格格式”对话框。切换至“数字”选项卡, 在“分类"列表框中选择“自定义"命令，

7、在右侧的"示例"组中"类型"列表框中输入"yyyy"年"m"月"d"日"aaaa "。设置完毕后单击"确定"按钮即可。（2）是否加班=IF（WEEKDAY（A3,2）>5, “ 是“，否”）地区 =LEFT（C3,3）（5）2013年第二季度发生在北京市的差旅费用金额总计为=SUMPRODUCT（1*（费用报销管理！D74:D340="北京市”庚用报销管理！G74:G340）=SUMIFS（费用报销管理！G3:G401,费用报销管理！D3:

8、D401,费用报销管理！D11,费用报销管理!A3:A401,">=2013-4-T',费用报销管理！A3:A401,”<=2013-6-30"）（6）2013年钱顺卓报销的火车票总计金额为=SUMPRODUCT（1*（费用报销管理 旧3:B401= "钱顺卓”）1*（费用报销管理！F3:F401= "火车票”）费用报销管理!G3:G401）=SUMIFS（费用报销管理！G3:G401,费用报销管理！F3:F401,费用报销管理！F10,费用报销管理旧3:B401,费用报销管理 旧378）（7）2013年差旅费用金额中，飞机票占所有报

9、销费用的比例为（保留 2位小数）=SUMPRODUCT（1*（费用报销管理！F3:F401= ”飞机票”），费用报销管理!G3:G401）/SUM（费用报销管理!G3:G401）=SUMIFS（费用报销管理！G3:G401,费用报销管理！F3:F401,费用报销管理！F3）/SUM（费用报销管理!G3:G401）（8）2013年发生在周末（星期六和星期日）中的通讯补助总金额为=SUMPRODUCT（费用报销管理！H3:H401= "是“）*费用报销管理！F3:F401= "通讯补助”添用报销管理！ G3:G401）=SUMIFS（费用报销管理！G3:G401,费用报销管理！

10、H3:H401,费用报销管理！H3,费用报销管理!F3:F401,费用报销管理!F391）第6套定义名称在 平均单价”工作表中选中B3: C7区域，单击鼠标右键，在弹出的下拉列表中选择 " 定义名称“命令，打开"新建名称”对话框。在"名称"中输入"商品均价”后单击 确定”按 钮即可。销售额：=VLOOKUP（D4,商品均价,2,0）*E4第7套(5)应交个人所得税=ROUND(IF(K3<=1500,K3*0.03,IF(K3<=4500,K3*0.1-105,IF(K3<=9000,K3*02555,IF (K3<=3

11、5000K3*0.25-1005,IF(K3<=55000K3*0.3-2755,IF(K3<=80000K3*0.35-5505,IF( K3>80000,K3*0.45-13505),2)(8)管理部门应付工资合计=SUMPRODUCT(1*(D3:D17=行政”),I3:I17)管理部门实发工资=SUMPRODUCT(1*(D3:D17=行政" ),M3:M17)第8套平均成绩和总分 =SUM(D3:L3) =A VERAGE(D3:L3)年级排名 =RANK(M3,M$3:M$102,0)学号的第三位为专业代码、第四位代表班级序号="法律” &am

12、p;TEXT(MID(B3,3,2), ” DBNuml )&"班”=LOOKUP(MID(B3,3,2),"01","02","03","04","法律一班","法律二班","法律三班","法律四班")插入图表到指定单元格按住AIT键，带鼠标指针出现斜向上箭头是开始拖动第9套插入一列插入”在C3中输入=IF(YEAR(销售订单!$B3)=2013,MONTH(销售订单!$B3),0)« Office商务办公

13、好帮手1月(销售)=SUMIFS(销售订单！$H$3:$H$678，销售订单！$E$3:$E$678,'2013 年图书销售分析'!$A4, 销售订单!$C$3:$C$678,1)Office商务办公好帮手2月(销售)=SUMIFS(销售订单！$H$3:$H$678，销售订单！$E$3:$E$678,'2013 年图书销售分析'!$A4, 销售订单!$C$3:$C$678,2)Office商务办公好帮手3月(销售)=SUMIFS(销售订单！$H$3:$H$678，销售订单！$E$3:$E$678,'2013 年图书销售分析'!$A4, 销售订单!

14、$C$3:$C$678,3)每月图书总销量：=SUBTOTAL(109,B4:B11)添加汇总行第10套分列选中B列单元格，单击鼠标右键，在弹出的快捷菜单中选择 单元格，将光标置于 学号”和 名字”之间，按3次空格键, 击【数据工具】组中的 分列”按钮，在弹出的对话框中选择插入”选项。然后选中 然后选中A列单元格,A1单同定宽度”单选按钮，单击下一步”按钮，然后建立分列线。单击 下一步"按钮，保持默认设置，单击 完成"按钮(3)性别 =IF(MOD(MID(C2,17,1),2)=1,男“，女”)出生日期 =-TEXT(MID(C2,7,8), ” 小00-00”)年”,M

15、ID(C8,11,2),“月=CONCATENATE(MID(C8,7,4),“”,MID(C8,13,2),"日") 年龄：=DATEDIF(- TEXT(MID(C2,7,8), ” 小00-00"),TODAY(), " y" )=INT(TODAY()-E2)/365) (4)语文姓名=VLOOKUP(A2,初三学生档案！$A$1:$B$56,2,FALSE),其余方法一样按照三个比例计算学期成绩=SUM(C2*30%)+(D2*30%)+(E2*40%)按成绩由高到低的顺序排名，并按第n名”的形式填入 班级名次”列中="第&

16、quot;&RANK(F2,$F$2:$F$45)&"名"期末总评”数学，语文：=IF(F2>=102,"优秀",IF(F2>=84,"良好",IF(F2>=72,"及格",IF(F2>72,"及 格","不及格")英语及其他：=IF(F3>=90,"优秀",IF(F3>=75,"良好",IF(F3>=60,"及格"，"不及格")姓名：=V

17、LOOKUP(A3,初三学生档案！$A$2:$B$56,2,FALSE)语文成绩：=VLOOKUP(A3,语文!A2:F45,6,FALSE)第11套(3)职称：=VLOOKUP(E12,教师基本信息！$D$3:$E$22,2,FALSE) 学时数：=SUMIF(授课信息表！$D$3:$D$72,E3,授课信息表！$F$3:$F$72)第12套(3) =IF(H2=0，“平"，IF(H2>0,借“，贷”)第13套(3)销量：=VLOOKUP(A4,销量信息,3,FALSE)第14套条件格式选中F2: G14,单击【开始】选项卡下【样式】组中的“条件格式"按钮，选择”突

18、出显 示单元格规则”中的"其他规则"，弹出"新建格式规则”对话框，在"编辑规则说明"选项 下设置单元格值小于 80。然后单击"格式"按钮，弹出”设置单元格格式”对话框，在" 填充”选项卡下选择"红色"，单击"确定"按钮。分类汇总选中C15,单击【数据】选项卡下【分级显示】组中的 “分类汇总”按钮，弹出”分类汇 总”对话框，单击"分类字段"组中的下拉按钮选择"班级",单击"汇总方式"组中的下拉 按钮选择"平均

19、值"，在"选定汇总项"组中勾选"高等代数"、"数学分析"、"大学英语"、 "大学物理"、"VB程序设计"、"C语言程序设计"、"Matlab"复选框，并勾选"每组数 据分页"复选框。其他分类汇总方法一致第15套(4)班级:=IF(MID(A3,4,2)= " 01" 班1,IF(MID(A3,4,2)=" 02班：”,了班”)(5)姓名：=VLOOKUP(A3,学号对照！$A$

20、3:$B$20,2,FALSE)第16套(2)出生日期:=MID(F3,7,4)& ”年" &MID(F3,11,2)& 月" &MID(F3,13,2)& 旧”(3)工龄:=INT(TODAY()-I3)/365)(4)工龄工资：=AVERAGEIF(员工档案！H3:H37, “本科”员工档案！K3:K37)(5)所有人的基础工资：=SUM(员工档案！M3:M37)(6)项目经理的基本工资总额：=员工档案！K3+员工档案！K7本科生平均基本工资： =AVERAGEIF(员工档案！H3:H37, “本科”员工档 案!K3:K37)第1

21、7套根据生成的数据透视表，在透视表下方创建一个簇状柱形图，图表中仅对博达书店一月 份的销售额小计进行比较。选中博达书店销售额小计，单击【开始】选项卡下【图表】组中的“柱形图”按钮，在弹出的下拉列表中选择"簇状柱形图”命令。步骤2:在“数据透视图“中单击"书店名称“右侧下三角按钮，在下拉列表中只选择“博 达书店”复选框。第18套条件格式选择【B3:L14】单元格，切换至【开始】选项卡，单击【样式】选项组下的【条件格式】下拉按钮，在下拉列表中选择【突出显示单元格规则】-【大于】，在为大于以下值的单元格设置格式”文本框中输入“1000；使用默认设置 浅红填充色深红色文本”， 单击

22、确定”按钮。步骤2:选择【M3:M14 单元格，切换至【开始】选项卡，单击【样式】选项组下的【条件格式】下拉按钮，在弹出的下拉列表中选择【突出显示单元格规则】-【大于】，在为大于以下值的单元格设置格式”文本框中输入“二$M$15*110% ,设置颜色为黄填 充色深黄色文本”，单击 确定”按钮。季度：="第" &INT(1+(MONTH(A3) -1)/3)& “季度”第19套停放时间：=DATEDIF(F2,H2," YDD )*24+(I2 -G2)收费金额:=E2*ROUNDUP(HOUR(J2)*60+MINUTE(J2)/15),0)拟收费金额:=E2*ROUNDDOWN(HOUR(J2)*60+MI