2016年计算机二级MS-Office-Excel函数公式大集合
2016 年计算机二级
MS-Office-Excel 函数公式大
集
第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 高级应用》图书在2012 年的总销售额
“降序”
=SUMPRODUCT（1*（订单明细表!E3:E262= ”《MS Office 高级应用》”）订, 单明细表!H3:H262）
（7）隆华书店在2011年第3季度（7月1日~9月30日）的总销售额
=SUMPRODUCT（1*（订单明细表!C305:C461 =”隆华书店” ）订, 单明细
表!H350:H461）
（8）隆华书店在2011年的每月平均销售额（保留 2 位小数）
=SUMPRODUCT（1*（订单明细表!C262:C636 =” 隆华书店”）,订
单明细表!H263:H636）/12 使用SUMIFS ：
（5）=SUBTOTAL（9, 订单明细表!H3:H636）
（6）=SUMIFS（订单明细表!H3:H636, 订单明细表!E3:E636, 订单明细
表!E19,订单明细表!B3:B636,">=2012-1-1", 订单明细表!B3:B636,"<=2012-12-31"）（7）=SUMIFS（订单明细表!H3:H636, 订单明细表!C3:C636, 订单明细表!C12,订单明细表!B3:B636,">=2011-7-1", 订单明细
表!B3:B636,"<=2011-9-30"）（8）=SUMIFS（订单明细表!H3:H636, 订单明细表!C3:C636, 订单
明细表!C12, 订单明细表!B3:B636,">=2011-1-1", 订单明细
表!B3:B636,"<=2011-12-31"）/12
注意：用SUNPRODUCT 函数之前先排序，尤其是涉及时间的时候。
（4）班级=LOOKUP（MID（A2,3,2）,{ ”01”, ”02”, ”03班””},{, ”班21”, ”班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" ，单击"添加"按钮，在"标签位置"下勾选"首行"复选框和"最左列"复选框，然后单击" 确定" 按钮。数据透视表筛选
单击行标签右侧的" 标签筛选"按钮，在弹出的下拉列表中选择"值筛选" ，打开级联菜单，选择"大于" ，弹出"值筛选（地区）"对话框，在第一个文本框中选择" 求和项：2010 年人口数（万人）" ，第二个文本框选择"大于" ，在第三个文本框中输入"5000" ，单击" 确定" 按钮。
在日期后添加星期
在"费用报销管理 "工作表中，选中 " 日期"数据列，单击鼠标右键，在弹出的快捷菜单 中选择"设置单元格格式 "命令，弹出"设置单元格格式 "对话框。切换至 "数字"选项卡， 在"分类" 列表框中选择 " 自定义 " 命令，在右侧的 " 示例" 组中" 类型 "列表框中输入 " yyyy" 年"m" 月"d" 日" aaaa " 。设置完毕后单击 "确定"按钮即可
(2) 是否加班 =IF(WEEKDAY(A3,2)>5, ”是” ,否””)
(3) 地区 =LEFT(C3,3)
(5) 2013 年第二季度发生在北京市的差旅费用金额总计为
=SUMPRODUCT(1*( 费用报销管理 !D74:D340= ”北京市”)费, 用报销管理 !G74:G340) =SUMIFS( 费用报销管理 !G3:G401, 费用报销管理 !D3:D401,费用报销管理 !D11,费用报 销管理 !A3:A401,">=2013-4-1", 费用报销管理 !A3:A401,"<=2013-6-30")
(6) 2013 年钱顺卓报销的火车票总计金额为
=SUMPRODUCT(1*( 费用报销管理 !B3:B401= ”钱顺卓 费用报销管理 !G3:G401) =SUMIFS( 费用报销管理 !G3:G401,费用报销管理 !F3:F401, 费用报销管理 !F10, 费用报 销管理 !B3:B401,费用报销管理 !B378)
(7) 2013 年差旅费用金额中，飞机票占所有报销费用的比例为(保留 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,费用报销管理 !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:D1”7= 行政 ”),M3:M17)
) ,1*( 费用报销管理 !F3:F401= 火车票” ，)
平均成绩和总分=SUM(D3:L3) =A VERAGE(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)=2013,MONTH( 销售订单 !$B3),0)
Office 商务办公好帮手》 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, 销售订单 !$C$3:$C$678,3)
每月图书总销量 : =SUBTOTAL(109,B4:B11) 添加汇总行 第 10 套
分列
选中 B 列单元格，单击鼠标右键，在弹出的快捷菜单中选择 单元格，将光标置于 “学号”和“名字”
之间，按 3 次空格键， 击【数据工具】组中的
“分列 ”按钮，在弹出的对话框中选择 “固定宽度 ”单选按钮，单击 “下一步”按钮，然后建立分列线。单击 “下一步 ”按钮，保持默认设置，单击 “完成”按钮。
(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, 教师基本信
“插入 ”选项。然后选中 然后选中 A 列单元A1
单
学时数: =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） 2013 年所有图书订单的销售额
=SUMIFS（表1［销售额小计］,表1［日期］, ” >=20-113-1”表, 1［日期］, ”<=20-1132-31”）《MS Office 高级应用》图书在2012年的总销
售额
=SUMIFS（表1［销售额小计］,表1［图书名称］,订单明细!D7,表1［日期］, ” >=201-12-1”,表1［日期］, ”<=201-212-31”）
隆华书店在2013年第3季度（7月1日~9月30日）的总销售额
=SUMIFS（表1［销售额小计］,表1［书店名称］,订单明细!C14,表1［日期］, ” >=201-73-1”,表1［日期］, ”<=20-193-30”）
隆华书店在2012年的每月平均销售额（保留 2 位小数）
=SUMIFS( 表1［销售额小计］,表1［书店名称］,订单明细!C14,表1［日期］, ” >=201-12-1”,表1［日期］, ”<=20-1122-31”)/12
2013年隆华书店销售额占公司全年销售总额的百分比（保留 2 位小数）
=SUMIFS（表1［销售额小计］,表1［书店名称］,订单明细!C14,表1［日期］, ” >=201-13-1”,表1［日期］, ”<=201-132-31”）/SUMIFS
（表1［销售额小计］,表1［日期］, ”>=20-113-1”表, 1［日
期］, ”<=201-132-31”）设置数字格式为百分比，保留两位小数