计算机二级OfficeEexcel公式汇总资格考试计算机等级考试_资格考试-计算机等级考试.pdf
EXCEL函数公式难点大全 第 1 套.2 第 2 套.4 第 3 套.4 第 4 套.4 第 5 套.5 第 6 套.7 第 7 套.8 第 8 套.8 第 9 套.9 第 10 套.10 第 11 套.12 第 12 套.12 第 13 套.13 第 14 套.13 第 15 套.14 第 16 套.14 第 17 套.15 第 18 套.15 第 19 套.16 第 20 套.16 第 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高级应用图书在 20XX年的总销售额“降序”=SUMPRODUCT(1*(订单明细表!E3:E262=”MS Office 高级应用”),订单明细表!H3:H262)音符读着这句话似乎有很多事都滑过了最后却定格在了那件事上它就像一缕温暖的阳光拂过我的心田那是一个炽热炎炎的日子里太阳火辣辣的烤着大地晒得人汗珠往下直滚刺得人眼睛都睁不开一股热浪扑面而来让人气也喘不过来大起来黑板上一行行秀丽的字都变成了一个个张牙舞爪的怪物前面的同学也变成了荡漾的波浪看来我中暑了豆大的汗珠直往下掉脑袋疼得象要爆裂似的该怎么办呢就在这时坐在我后面的贾甜发现了我的异常立即站起来说老师侯翔中暑该怎么回行就在这时贾甜又自告奋勇老师我陪侯翔回家吧老师投来赞许的目光爽快的答应了而我听到后心中涌上了一股浓浓的甜蜜天气比在学校更热了大地烤的都快冒烟了一路上贾甜用瘦文章来源网络整理仅供参考学习小的身躯扶 (7)隆华书店在 20XX年第 3 季度（7 月 1 日9月 30日）的总销售额=SUMPRODUCT(1*(订单明细表!C305:C461=”隆华书店”),订单明细表!H350:H461)(8)隆华书店在 20XX年的每月平均销售额（保留 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,=2011-7-1,订 单 明 细表!B3:B636,=2011-1-1,订 单 明 细表!B3:B636,5,”是”,”否”)(3)地区=LEFT(C3,3)(5)20XX年第二季度发生在北京市的差旅费用金额总计为=SUMPRODUCT(1*(费用报销管理!D74:D340=”北京市”),费用报销管理!G74:G340)=SUMIFS(费用报销管理!G3:G401,费用报销管理!D3:D401,费用报销管理!D11,费用报销管理!A3:A401,=2013-4-1,费用报销管理!A3:A401,=2013-6-30)(6)20XX 年钱顺卓报销的火车票总计金额为=SUMPRODUCT(1*(费用报销管理!B3:B401=”钱顺卓”),1*(费用报销管理!F3:F401=”火车票”)，费用报销管理!G3:G401)=SUMIFS(费用报销管理!G3:G401,费用报销管理!F3:F401,音符读着这句话似乎有很多事都滑过了最后却定格在了那件事上它就像一缕温暖的阳光拂过我的心田那是一个炽热炎炎的日子里太阳火辣辣的烤着大地晒得人汗珠往下直滚刺得人眼睛都睁不开一股热浪扑面而来让人气也喘不过来大起来黑板上一行行秀丽的字都变成了一个个张牙舞爪的怪物前面的同学也变成了荡漾的波浪看来我中暑了豆大的汗珠直往下掉脑袋疼得象要爆裂似的该怎么办呢就在这时坐在我后面的贾甜发现了我的异常立即站起来说老师侯翔中暑该怎么回行就在这时贾甜又自告奋勇老师我陪侯翔回家吧老师投来赞许的目光爽快的答应了而我听到后心中涌上了一股浓浓的甜蜜天气比在学校更热了大地烤的都快冒烟了一路上贾甜用瘦文章来源网络整理仅供参考学习小的身躯扶 费用报销管理!F10,费用报销管理!B3:B401,费用报销管理!B378)(7)20XX年差旅费用金额中，飞机票占所有报销费用的比例为（保留 2位小数）=SUMPRODUCT(1*(费用报销管理!F3:F401=”飞机票”),费用报销管理!G3:G401)/SUM(费用报销管理!G3:G401)=SUMIFS(费用报销管理!G3:G401,费用报销管理!F3:F401,费用报销管理!F3)/SUM(费用报销管理!G3:G401)(8)20XX年发生在周末（星期六和星期日）中的通讯补助总金额为=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(K380000,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)=2013,MONTH(销售订单!$B3),0)Office商务办公好帮手 1月（销售）=SUMIFS(销售订单!$H$3:$H$678,销售订单!$E$3:$E$678,20XX 年 图 书 销 售 分 析!$A4,销 售 订单!$C$3:$C$678,1)音符读着这句话似乎有很多事都滑过了最后却定格在了那件事上它就像一缕温暖的阳光拂过我的心田那是一个炽热炎炎的日子里太阳火辣辣的烤着大地晒得人汗珠往下直滚刺得人眼睛都睁不开一股热浪扑面而来让人气也喘不过来大起来黑板上一行行秀丽的字都变成了一个个张牙舞爪的怪物前面的同学也变成了荡漾的波浪看来我中暑了豆大的汗珠直往下掉脑袋疼得象要爆裂似的该怎么办呢就在这时坐在我后面的贾甜发现了我的异常立即站起来说老师侯翔中暑该怎么回行就在这时贾甜又自告奋勇老师我陪侯翔回家吧老师投来赞许的目光爽快的答应了而我听到后心中涌上了一股浓浓的甜蜜天气比在学校更热了大地烤的都快冒烟了一路上贾甜用瘦文章来源网络整理仅供参考学习小的身躯扶 Office商务办公好帮手 2月（销售）=SUMIFS(销售订单!$H$3:$H$678,销售订单!$E$3:$E$678,20XX 年 图 书 销 售 分 析!$A4,销 售 订单!$C$3:$C$678,2)Office商务办公好帮手 3月（销售）=SUMIFS(销售订单!$H$3:$H$678,销售订单!$E$3:$E$678,20XX 年 图 书 销 售 分 析!$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(F272,及格,不及格)音符读着这句话似乎有很多事都滑过了最后却定格在了那件事上它就像一缕温暖的阳光拂过我的心田那是一个炽热炎炎的日子里太阳火辣辣的烤着大地晒得人汗珠往下直滚刺得人眼睛都睁不开一股热浪扑面而来让人气也喘不过来大起来黑板上一行行秀丽的字都变成了一个个张牙舞爪的怪物前面的同学也变成了荡漾的波浪看来我中暑了豆大的汗珠直往下掉脑袋疼得象要爆裂似的该怎么办呢就在这时坐在我后面的贾甜发现了我的异常立即站起来说老师侯翔中暑该怎么回行就在这时贾甜又自告奋勇老师我陪侯翔回家吧老师投来赞许的目光爽快的答应了而我听到后心中涌上了一股浓浓的甜蜜天气比在学校更热了大地烤的都快冒烟了一路上贾甜用瘦文章来源网络整理仅供参考学习小的身躯扶 英语及其他:=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(H20,”借”,”贷”)音符读着这句话似乎有很多事都滑过了最后却定格在了那件事上它就像一缕温暖的阳光拂过我的心田那是一个炽热炎炎的日子里太阳火辣辣的烤着大地晒得人汗珠往下直滚刺得人眼睛都睁不开一股热浪扑面而来让人气也喘不过来大起来黑板上一行行秀丽的字都变成了一个个张牙舞爪的怪物前面的同学也变成了荡漾的波浪看来我中暑了豆大的汗珠直往下掉脑袋疼得象要爆裂似的该怎么办呢就在这时坐在我后面的贾甜发现了我的异常立即站起来说老师侯翔中暑该怎么回行就在这时贾甜又自告奋勇老师我陪侯翔回家吧老师投来赞许的目光爽快的答应了而我听到后心中涌上了一股浓浓的甜蜜天气比在学校更热了大地烤的都快冒烟了一路上贾甜用瘦文章来源网络整理仅供参考学习小的身躯扶 第 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)20XX年所有图书订单的销售额 =SUMIFS(表 1销售额小计,表 1日期,”=2013-1-1”,表 1日期,”=2012-1-1”,表 1日期,”=2013-7-1”,表 1日期,”=2012-1-1”,表 1日期,”=2013-1-1”,表 1日期,”=2013-1-1”,表1日期,”=2013-12-31”)设置数字格式为百分比，保留两位小数 音符读着这句话似乎有很多事都滑过了最后却定格在了那件事上它就像一缕温暖的阳光拂过我的心田那是一个炽热炎炎的日子里太阳火辣辣的烤着大地晒得人汗珠往下直滚刺得人眼睛都睁不开一股热浪扑面而来让人气也喘不过来大起来黑板上一行行秀丽的字都变成了一个个张牙舞爪的怪物前面的同学也变成了荡漾的波浪看来我中暑了豆大的汗珠直往下掉脑袋疼得象要爆裂似的该怎么办呢就在这时坐在我后面的贾甜发现了我的异常立即站起来说老师侯翔中暑该怎么回行就在这时贾甜又自告奋勇老师我陪侯翔回家吧老师投来赞许的目光爽快的答应了而我听到后心中涌上了一股浓浓的甜蜜天气比在学校更热了大地烤的都快冒烟了一路上贾甜用瘦文章来源网络整理仅供参考学习小的身躯扶
