2023年计算机二级MSOfficeExcel函数公式大集合.docx

第1套
(2)图书名称  =VLOOKUP(D3,编号对照!$A$3:$C$19,2,FALSE) 
(3)单价  =VLOOKUP(D3,编号对照!$A$3:$C$19,3,FALSE) 
(4)小计  =[@单价]*[@销量（本）] 
(5)所有订单总销售额  =SUM(订单明细表!H3:H636) “撤销筹划列” 
(6) 《MS Office高档应用》图书在总销售额 
“降序” 
=SUMPRODUCT(1*(订单明细表!E3:E262=”《MS Office高档应用》”),订单明细表!H3:H262)
(7)隆华书店在第3季度（7月1日~9月30日）总销售额 
=SUMPRODUCT(1*(订单明细表!C305:C461 =”隆华书店”),订单明细表!H350:H461) 
(8)隆华书店在每月平均销售额（保留2位小数） 
=SUMPRODUCT(1*(订单明细表!C262:C636 =”隆华书店”),订单明细表!H263:H636)/12 使用SUMIFS： 
（5）=SUBTOTAL(9,订单明细表!H3:H636) 
（6）=SUMIFS(订单明细表!H3:H636,订单明细表!E3:E636,订单
明细表!E19,订单明细表!B3:B636,">=-1-1",订单明细表!B3:B636,"<=-12-31") 
（7）=SUMIFS(订单明细表!H3:H636,订单明细表!C3:C636,订单
明细表!C12,订单明细表!B3:B636,">=-7-1",订单明细表!B3:B636,"<=-9-30") 
（8）=SUMIFS(订单明细表!H3:H636,订单明细表!C3:C636,订单
明细表!C12,订单明细表!B3:B636,">=-1-1",订单明细表!B3:B636,"<=-12-31")/12 
注意：用SUNPRODUCT函数之前先排序，尤其是波及时间时候。
第2套
(4)班级  =LOOKUP(MID(A2,3,2),{”01”,”02”,”03”},{”1班”,”2班”,”3班”}) 
第3套
（1） 一季度销售额(元）、二季度销售额（元）  =产品基本信息表!C2*C2 
（2） 一二季度销售总量、一二季度销售总额 
=一季度销售状况表!C2+'二季度销售状况表 '!C2 =一季度销售状况表!D2+'二季度销售状况表 '!D2 
(3)销售额排名  =RANK(D2,$D$2:$D$21,0)   RANK函数排序区域绝对引用。 
第4套 
合并计算 
双击工作表sheet3表名，在编辑状态下输入"比较数据"。在该工作表A1中输入"地区"，在【数据】选项卡【数据工具】组中单击"合并计算"按钮，弹出"合并计算"对话框，设置"函数"为"求和"，在"引用位置"文本框中键入第一种区域"第五次普查数据!$A$1:$C$34"，单击"添加"按钮，键入第二个区域"第六次普查数据!$A$1:$C$34"，单击"添加"按钮，在"标签位置"下勾选"首行"复选框和"最左列"复选框，然后单击"确定"按钮。 
数据透视表筛选 
单击行标签右侧"标签筛选"按钮，在弹出下拉列表中选用"值筛选"，打开级联菜单，选用"不不不小于"，弹出"值筛选（地区）"对话框，在第一种文本框中选用"求和项：人口数（万人）"，第二个文本框选用"不不不小于"，在第三个文本框中输入"5000"，单击"确定"按钮。 
第5套
在日期后添加星期 
在"费用报销管理"工作表中，选中"日期"数据列，单击鼠标右键，在弹出快捷菜单中选用"设置单元格格式"命令，弹出"设置单元格格式"对话框。切换至"数字"选项卡，在"分类"列表框中选用"自定义"命令，在右侧"示例"组中"类型"列表框中输入" yyyy"年"m"月"d"日" aaaa "。设置完毕后单击"确定"按钮即可。 
(2)与否加班  =IF(WEEKDAY(A3,2)>5,”是”,”否”) 
(3)地区  =LEFT(C3,3) 
(5)第二季度发生在北京市差旅费用金额总计为 
=SUMPRODUCT(1*(费用报销管理!D74:D340=”北京市”),费用报销管理!G74:G340) 
=SUMIFS(费用报销管理!G3:G401,费用报销管理!D3:D401,费用报销管理!D11,费用报销管理!A3:A401,">=-4-1",费用报销管理!A3:A401,"<=-6-30") 
(6)钱顺卓报销火车票总计金额为 
=SUMPRODUCT(1*(费用报销管理!B3:B401=”钱顺卓”), 1*(费用报销管理!F3:F401=”火车票”)， 
费用报销管理!G3:G401) 
=SUMIFS(费用报销管理!G3:G401,费用报销管理!F3:F401，费用报销管理!F10,费用报销管理!B3:B401,费用报销管理!B378) 
 (7)差旅费用金额中，飞机票占所有报销费用比例为（保留2位小数） 
=SUMPRODUCT(1*(费用报销管理!F3:F401=”飞机票”),费用报销管理!G3:G401)/SUM(费用报销管理!G3:G401) 
=SUMIFS(费用报销管理!G3:G401,费用报销管理!F3:F401,费用报销管理!F3)/SUM(费用报销管理!G3:G401) 
 (8)发生在周末（星期六和星期日）中通讯补助总金额为 
= SUMPRODUCT((费用报销管理!H3:H401=”是”)*(费用报销管理!F3:F401=”通讯补助”),费用报销管理！G3:G401) 
=SUMIFS(费用报销管理!G3:G401,费用报销管理!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*0.2-555,IF(K3<=35000,K3*0.25-1005,IF(K3<=55000,K3*0.3-2755,IF(K3<=80000,K3*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) =AVERAGE(D3:L3)
年级排名   =RANK(M3,M$3:M$102,0) 
学号第三位为专业代码、第四位代表班级序号   =”法律”&TEXT(MID(B3,3,2),”[DBNum1]”)&”班”     
=LOOKUP(MID(B3,3,2),{"01","02","03","04"},{"法律一班","法律二班","法律三班","法律四班"}) 插入图表到指定单元格 
按住AIT键，带鼠标指针出现斜向上箭头是开始拖动 
第9套
插入一列 
“插入”在C3中输入 
  =IF(YEAR(销售订单!$B3)=,MONTH(销售订单!$B3),0) 
《Office商务办公好帮手》 1月（销售） 
==SUMIFS(销售订单!$H$3:$H$678,销售订单!$E$3:$E$678,'图书销售分析'!$A4,销售订单!$C$3:$C$678,1)
《Office商务办公好帮手》 2月（销售） 
==SUMIFS(销售订单!$H$3:$H$678,销售订单!$E$3:$E$678,'图书销售分析'!$A4,销售订单!$C$3:$C$678,2) 
《Office商务办公好帮手》 3月（销售） 
==SUMIFS(销售订单!$H$3:$H$678,销售订单!$E$3:$E$678,'图书销售分析'!$A4,销售订单!$C$3:$C$678,3) 
每月图书总销量：  =SUBTOTAL(109,B4:B11)添加汇总行 
第10套
分列 
选中B列单元格，单击鼠标右键，在弹出快捷菜单中选用“插入”选项。然后选中A1单元格，将光标置于“学号”和“名字”之间，按3次空格键，然后选中A列单元格，单击【数据工具】组中“分列”按钮，在弹出对话框中选用“固定宽度”单项选择按钮，单击“下一步”按钮，然后建立分列线。单击“下一步”按钮，保持默认设置，单击“完毕”按钮。 
(3)性别    =IF(MOD(MID(C2,17,1),2)=1,”男”,”女”) 
出生日期   =--TEXT(MID(C2,7,8),”0-00-00”) 
=CONCATENATE(MID(C8,7,4),"年",MID(C8,11,2),"月
",MID(C8,13,2),"日") 
年龄:    =DATEDIF(--TEXT(MID(C2,7,8),”0-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名”形式填入“班级名次”列中 
  ="第"&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,"及格","不及格"))) 
姓名：   =VLOOKUP(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，单击【开始】选项卡下【样式】组中"条件格式"按钮，选用"突出显示单元格规则"中"其她规则"，弹出"新建格式规则"对话框，在"编辑规则阐明"选项下设置单元格值不不小于80。然后单击"格式"按钮，弹出"设置单元格格式"对话框，在"填充"选项卡下选用"红色"，单击"确定"按钮。 
分类汇总 
选中C15，单击【数据】选项卡下【分级显示】组中"分类汇总"按钮，弹出"分类汇总"对话框，单击"分类字段"组中下拉按钮选用"班级"，单击"汇总方式"组中下拉按钮选用"平均值"，在"选定汇总项"组中勾选"高等代数"、"数学分析"、"大学英语"、"大学物理"、"VB程序设计"、"C语言程序设计"、"Matlab"复选框，并勾选"每组数据分页"复选框。 
其她分类汇总措施一致
第15套
(4)班级 ：  =IF(MID(A3,4,2)=”01”,”1班”,IF(MID(A3,4,2)=”02”,”2班”,”3班”)) 
(5)姓名 ：   =VLOOKUP(A3,学号对照!$A$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 
(7)本科生平均基本工资 ：  =AVERAGEIF(员工档案!H3:H37,”本科”,员工档案!K3:K37) 
第17套
根据生成数据透视表，在透视表下方创立一种簇状柱形图，图表中仅对博达书店一月份销售额小计进行比较。 
选中博达书店销售额小计，单击【开始】选项卡下【图表】组中"柱形图"按钮，在弹出下拉列表中选用"簇状柱形图"命令。 
环节2：在"数据透视图"中单击"书店名称"右侧下三角按钮，在下拉列表中只选用"博达书店"复选框。 
第18套
条件格式 
选用【B3:L14】单元格，切换至【开始】选项卡，单击【样式】选项组下【条件格式】下拉按钮，在下拉列表中选用【突出显示单元格规则】-【不不不小于】，在“为不不不小于如下值单元格设置格式”文本框中输入“1000”，使用默认设置“浅红填充色深红色文本”，单击“确定”按钮。 
环节2：选用【M3:M14】单元格，切换至【开始】选项卡，单击【样式】选项组下【条件格式】下拉按钮，在弹出下拉列表中选用【突出显示单元格规则】-【不不不小于】，在“为不不不小于如下值单元格设置格式”文本框中输入“=$M$15*110%”，设置颜色为“黄填充色深黄色文本”，单击“确定”按钮。 
季度 ： =”第”&INT(1+(MONTH(A3)-1)/3)&”季度” 
第19套
停放时间 ： =DATEDIF(F2,H2,”YD”)*24+(I2-G2) 
收费金额 ： =E2*ROUNDUP(((HOUR(J2)*60+MINUTE(J2))/15),0) 
拟收费金额 ： =E2*ROUNDDOWN(((HOUR(J2)*60+MINUTE(J2))/15),0) 
差值：  =K2-L2 
第20套
单价 ：   =VLOOKUP([@图书名称],表2,2,0) 
销售额小计注：销售量超过40本（含），按9.3折销售 
  =IF([@销量(本)]>=40,[@单价]*[@销量(本)]*0.93, [@单价]*[@销量(本)]) 
所属区域 ： =VLOOKUP(MID([@发货地址],1,3),表3,2,0)  
 所有图书订单销售额 
  =SUMIFS(表1[销售额小计],表1[日期],”>=-1-1”,表1[日期],”<=-12-31”)  
《MS Office高档应用》图书在总销售额 
  =SUMIFS(表1[销售额小计],表1[图书名称],订单明细!D7,表1[日期],”>=-1-1”, 表1[日期], ”<=-12-31”) 
隆华书店在第3季度（7月1日~9月30日）总销售额 
  =SUMIFS(表1[销售额小计],表1[书店名称],订单明细!C14,表1[日期],”>=-7-1”, 表1[日期],”<=-9-30”) 
隆华书店在每月平均销售额（保留2位小数） 
  =SUMIFS(表1[销售额小计],表1[书店名称],订单明细!C14,表1[日期],”>=-1-1”, 表1[日期],”<=-12-31”)/12 
隆华书店销售额占企业整年销售总额比例（保留2位小数） 
  =SUMIFS(表1[销售额小计],表1[书店名称],订单明细!C14,表1[日期],”>=-1-1”, 表1[日期],”<=-12-31”)/SUMIFS(表1[销售额小计],表1[日期],”>=-1-1”,表1[日期],”<=-12-31”)  设置数字格式为比例，保留两位小数