计算机二级Office2010Eexcel公式汇总

1、EXCEL函数公式难点大全第 1 套1第 2 套2第 3 套3第 4 套3第 5 套4第 6 套6第 7 套7第 8 套7第 9 套8第 10套9第 11套11第 12套11第 13套11第 14套12第 15套12第 16套13第 17套14第 18套14第 19套15第 20套15第 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

2、 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

3、使用 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,"

4、<=2011-9-30")（ 8） =SUMIFS(订单明细表 !H3:H636, 订单明细表 !C3:C636, 订单明细表 !C12,订单明细表 !B3:B636,">=2011-1-1",订单明细表 !B3:B636,"<=2011-12-31")/12注意：用 SUNPRODUCT函数之前先排序，尤其是涉及时间的时候。第 2 套(4) 班级=LOOKUP(MID(A2,3,2), ”01”, ”02”, ”03”, ”1 班” , ”2班” , ”3班” )第 3 套（ 1） 一季度销售额 ( 元）、二季度销售额（元）

5、=产品基本信息表 !C2*C2（ 2） 一二季度销售总量、一二季度销售总额=一季度销售情况表 !C2+' 二季度销售情况表'!C2=一季度销售情况表 !D2+' 二季度销售情况表'!D2(3) 销售额排名=RANK(D2,$D$2:$D$21,0)RANK函数排序区域绝对引用。第 4 套数据自网页导入在考生文件夹下双击打开网页" 第五次全国人口普查公报.htm" ，在工作表 " 第五次普查数据 " 中选中 A1，单击【数据】选项卡下【获取外部数据】组中的" 自网站 " 按钮，弹出 " 新建 W

6、eb查询 " 对话框，在 " 地址 " 文本框中输入网页 " 第五次全国人口普查公报 .htm" 的地址，单击右侧的 " 转到 " 按钮。单击要选择的表旁边的带方框的黑色箭头，使黑色箭头变成对号，然后单击 " 导入 " 按钮。之后会弹出 " 导入数据 "对话框，选择 " 数据的放置位置 " 为" 现有工作表 " ，在文本框中输入 "=$A$1" ，单击 "确定 " 按钮。步骤 2：按照上述方法浏览网页 &q

7、uot; 第六次全国人口普查公报 .htm" ，将其中的 "2010 年第六次全国人口普查主要数据 " 表格导入到工作表 " 第六次普查数据 " 中。合并计算双击工作表 sheet3 的表名，在编辑状态下输入 " 比较数据 " 。在该工作表的 A1 中输入 " 地区 " ，在【数据】选项卡的【数据工具】组中单击 " 合并计算 " 按钮，弹出 " 合并计算 " 对话框，设置 " 函数 " 为 " 求和 " ，在 " 引

8、用位置 " 文本框中键入第一个区域 " 第五次普查数据 !$A$1:$C$34" ，单击 " 添加 " 按钮，键入第二个区域 " 第六次普查数据 !$A$1:$C$34" ，单击 " 添加 " 按钮，在 " 标签位置 " 下勾选 " 首行 " 复选框和 " 最左列 " 复选框，然后单击 " 确定 " 按钮。数据透视表筛选单击行标签右侧的 " 标签筛选 " 按钮，在弹出的下拉列表中选择 " 值筛选

9、" ，打开级联菜单，选择 " 大于 " ，弹出 " 值筛选（地区） " 对话框，在第一个文本框中选择" 求和项： 2010 年人口数（万人） " ，第二个文本框选择 " 大于 " ，在第三个文本框中输入 "5000" ，单击 " 确定 " 按钮。第 5 套在日期后添加星期在 " 费用报销管理 " 工作表中，选中 " 日期 " 数据列，单击鼠标右键，在弹出的快捷菜单中选择 " 设置单元格格式 " 命令，弹出

10、" 设置单元格格式 " 对话框。切换至 "数字 " 选项卡，在 " 分类 " 列表框中选择 " 自定义 " 命令，在右侧的 " 示例 " 组中 " 类型 " 列表框中输入 " yyyy" 年"m"月"d" 日 " aaaa " 。设置完毕后单击 " 确定 " 按钮即可。(2) 是否加班=IF(WEEKDAY(A3,2)>5,”是” , ”否”)(3) 地区=LEFT(C3

11、,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=”钱顺卓” ), 1*(费用报

12、销管理!F3:F401= ”火车票” ) ，费用报销管理 !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(

13、费用报销管理 !G3:G401)(8)2013 年发生在周末（星期六和星期日）中的通讯补助总金额为= SUMPRODUCT(费用报销管理 !H3:H401=”是” )*( 费用报销管理 !F3:F401= ”通讯补助” ), 费用报销管理！ G3:G401)=SUMIFS(费用报销管理 !G3:G401, 费用报销管理 !H3:H401, 费用报销管理 !H3, 费用报销管理 !F3:F401, 费用报销管理 !F391)第 6 套定义名称在“平均单价”工作表中选中 B3：C7区域，单击鼠标右键，在弹出的下拉列表中选择 " 定义名称 " 命令，打开 " 新建名称

14、" 对话框。在 " 名称 " 中输入 " 商品均价 " 后单击“确定”按钮即可。销售额=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-

15、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",&q

16、uot;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

17、) 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 列单元格，单击鼠标右键，在弹出的快捷菜单中选择“插入”

18、选项。然后选中 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),"

19、;日")年龄=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

20、," 优秀 ",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:F

21、45,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，单击【开始】选项卡下【样式】组中的" 条件格式 " 按钮，选择 " 突出显示单元格规则 " 中的 " 其他规则 " ，弹

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

23、t; 班级 " ，单击 " 汇总方式 " 组中的下拉按钮选择 " 平均值 " ，在 " 选定汇总项 " 组中勾选 " 高等代数 " 、" 数学分析 " 、 " 大学英语 " 、" 大学物理 " 、"VB 程序设计 " 、 "C 语言程序设计 " 、"Matlab" 复选框，并勾选 " 每组数据分页 " 复选框。其他分类汇总方法一致第15套(4) 班级=IF(MID(A

24、3,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) 项目经

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

26、达书店 " 复选框。第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(