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）（4）小计=单价*销量（本） （5）所有订单的总销售额 =SUM（ 订单明细表 !H3:H636） “撤销计划列 ”（6）MS Office 高级应用图书在 2012 年的总销售额“降序”=SUMPRODUCT（1*（ 订单明细表!E3:E262= ”MS Office 高级应用”）订, 单明细 表!H3:H262）（7）隆华书店在 2

2、011年第 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,订单明细

3、表 !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,&

4、quot;>=2011-1-1", 订 单 明 细 表!B3:B636,"<=2011-12-31"）/12注意：用 SUNPRODUCT 函数之前先排序，尤其是涉及时间的时候。第2套（4）班级 =LOOKUP（MID（A2,3,2）, ”01”, ”02”, ”03班”, ”班21”, ”班3”）第3套（ 1） 一季度销售额 （元）、二季度销售额（元）=产品基本信息表 !C2*C2（ 2） 一二季度销售总量、一二季度销售总额=一季度销售情况表 !C2+'二季度销售情况表 '!C2 =一季度销售情况表 !D2+'二季度销售 情况

5、表 '!D2（3）销售额排名 =RANK（D2,$D$2:$D$21,0） RANK 函数排序区域绝对引用。第4套合并计算双击工作表 sheet3的表名，在编辑状态下输入 "比较数据" 。在该工作表的 A1 中输入" 地区" ，在【数据】选项卡的【数据工具】组中单击 "合并计算"按钮，弹出 "合并计算" 对话框，设置 "函数"为" 求和" ，在"引用位置"文本框中键入第一个区域 "第五次普查数 据!$A$1:$C$34" ，单击

6、"添加"按钮，键入第二个区域 "第六次普查数据 !$A$1:$C$34" ， 单击"添加"按钮，在"标签位置"下勾选"首行"复选框和 "最左列"复选框，然后单击 " 确定" 按钮。数据透视表筛选单击行标签右侧的 " 标签筛选 "按钮，在弹出的下拉列表中选择 "值筛选 " ，打开级联菜 单，选择"大于" ，弹出"值筛选（地区）"对话框，在第一个文本框中选择 " 求和项：

7、2010 年人口数（万人） " ，第二个文本框选择 "大于" ，在第三个文本框中输入 "5000" ，单击 " 确定" 按钮。第5套在日期后添加星期在"费用报销管理 "工作表中，选中 " 日期"数据列，单击鼠标右键，在弹出的快捷菜单 中选择"设置单元格格式 "命令，弹出"设置单元格格式 "对话框。切换至 "数字"选项卡， 在"分类"列表框中选择 "自定义 "命令，在右侧的 "示

8、例"组中"类型"列表框中输入 " 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, 费用

9、报销管理 !D3:D401,费用报销管理 !D11,费用报 销管理 !A3:A401,">=2013-4-1", 费用报销管理 !A3:A401,"<=2013-6-30")(6) 2013 年钱顺卓报销的火车票总计金额为) ,1*( 费用报销管理 !F3:F401=SUMPRODUCT(1*( 费用报销管理 !B3:B401= ”钱顺卓 费用报销管理 !G3:G401) =SUMIFS( 费用报销管理 !G3:G401,费用报销管理 !F3:F401, 费用报销管理 !F10, 费用报 销管理 !B3:B401,费用报销管理 !B378)火

10、车票”，)(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:F40

11、1= ”通讯补 助 ”)费, 用报销管理！ G3:G401)=SUMIFS( 费用报销管理 !G3:G401,费用报销管理 !H3:H401, 费用报销管理 !H3,费用报销 管理 !F3:F401,费用报销管理 !F391)第6套定义名称在 “平均单价 ”工作表中选中 B3： C7 区域，单击鼠标右键，在弹出的下拉列表中选择 " 定义名称"命令，打开"新建名称 "对话框。在"名称"中输入"商品均价"后单击“确定”按 钮即可。销售额: =VLOOKUP(D4, 商品均价 ,2,0)*E4第7套(5)应交个人所得税=

12、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)第8套平

13、均成绩和总分 =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"," 法律一班 "," 法律二班 "," 法律三班 "," 法律四班

14、") 插入图表到指定单元格按住 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

15、年图书销售分析 '!$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 套“插入 ”选项。然后选中然后选中 A 列单元格，A1单分列选中 B 列单元格，单击鼠标右键，在弹出的快捷菜单中选择 单元格，将光标置于 “学号”和“名字”之间，按 3 次空格键， 击【数据工具】组中的 “

16、分列 ”按钮，在弹出的对话框中选择 “固定宽度 ”单选按钮，单击 “下一步”按钮，然后建立分列线。单击 “下一步 ”按钮，保持默认设置，单击 “完成”按钮。(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”),TOD

17、AY(), ”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," 良好&

18、quot;,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)职称 :=VLOOK

19、UP(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，单击【开始】选项卡下【样式】组中的 "条件格式"按钮，选择 "突出显 示单元格规则 "中的"其他规则" ，弹出"新建格式规则 "对话框

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

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

22、=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) 本科 生平均基本 工资 :

23、 =AVERAGEIF( 员工档案!H3:H37, ”本科”员,工档 案!K3:K37)第 17 套根据生成的数据透视表， 在透视表下方创建一个簇状柱形图， 图表中仅对博达书店一月 份的销售额小计进行比较。选中博达书店销售额小计，单击【开始】选项卡下【图表】组中的 "柱形图 "按钮，在弹 出的下拉列表中选择 " 簇状柱形图 " 命令。步骤 2：在"数据透视图 "中单击"书店名称 "右侧下三角按钮，在下拉列表中只选择 "博 达书店 " 复选框。第 18 套条件格式选择【 B3:L14 】单元格，切

24、换至【开始】选项卡，单击【样式】选项组下的【条件格 式】下拉按钮，在下拉列表中选择【突出显示单元格规则】-【大于】，在 “为大于以下值的单元格设置格式 ”文本框中输入 “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(