2022年2022年计算机二级OfficeEexcel公式汇总 3.pdf

学习必备欢迎下载EXCEL函数公式难点大全第 1 套. 2第 2 套. 3第 3 套. 3第 4 套. 3第 5 套. 4第 6 套. 6第 7 套. 7第 8 套. 7第 9 套. 8第 10 套. 9第 11 套. 11第 12 套. 11第 13 套. 12第 14 套. 12第 15 套. 13第 16 套. 13第 17 套. 14第 18 套. 14第 19 套. 15第 20 套. 15名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 18 页 - - - - - - - - - 学习必备欢迎下载第 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) 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 18 页 - - - - - - - - - 学习必备欢迎下载(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,名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 18 页 - - - - - - - - - 学习必备欢迎下载费 用 报 销 管 理 !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 区域，单击鼠标右键，在弹出的下拉列表中选择定义名称 命令，打开 新建名称 对话框。在 名称中输入 商品均价 后单击 “ 确定 ” 按钮即可。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页，共 18 页 - - - - - - - - - 学习必备欢迎下载销售额=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) 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 8 页，共 18 页 - - - - - - - - - 学习必备欢迎下载年级排名=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) 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 9 页，共 18 页 - - - - - - - - - 学习必备欢迎下载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, ” 男” ,” 女” ) 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 10 页，共 18 页 - - - - - - - - - 学习必备欢迎下载出生日期=-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,及格 ,不及格 ) 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 11 页，共 18 页 - - - - - - - - - 学习必备欢迎下载英语及其他 : =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, ” 借” ,” 贷” ) 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 12 页，共 18 页 - - - - - - - - - 学习必备欢迎下载第 13 套(3)销量=VLOOKUP(A4, 销量信息 ,3,FALSE) 第 14 套条件格式选中 F2：G14，单击【开始】选项卡下【样式】组中的条件格式 按钮，选择突出显示单元格规则中的其他规则 ，弹出新建格式规则 对话框，在 编辑规则说明 选项下设置单元格值小于 80。然后单击 格式按钮，弹出 设置单元格格式 对话框，在填充选项卡下选择 红色，单击 确定按钮。分类汇总选中 C15，单击【数据】选项卡下【分级显示】组中的分类汇总 按钮，弹出分类汇总 对话框，单击 分类字段 组中的下拉按钮选择 班级， 单击 汇总方式 组中的下拉按钮选择平均值，在 选定汇总项 组中勾选 高等代数 、数学分析 、大学英语、大学物理 、VB 程序设计 、C语言程序设计 、Matlab复选框，并勾选 每组数据分页 复选框。其他分类汇总方法一致名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 13 页，共 18 页 - - - - - - - - - 学习必备欢迎下载第 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) 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 14 页，共 18 页 - - - - - - - - - 学习必备欢迎下载(6)项目经理的基本工资总额=员工档案 !K3+员工档案 !K7 (7)本科生平均基本工资=AVERAGEIF( 员工档案 !H3:H37,” 本科” ,员工档案 !K3:K37) 第 17 套根据生成的数据透视表，在透视表下方创建一个簇状柱形图，图表中仅对博达书店一月份的销售额小计进行比较。选中博达书店销售额小计，单击【开始】选项卡下【图表】组中的 柱形图 按钮，在弹出的下拉列表中选择簇状柱形图 命令。步骤 2：在数据透视图 中单击 书店名称 右侧下三角按钮，在下拉列表中只选择博达书店 复选框。第 18 套条件格式选择【 B3:L14】单元格，切换至【开始】选项卡，单击【样式】选项组下的【条件格式】下拉按钮，在下拉列表中选择【突出显示单元格规则】-【大于】，在 “ 为大于以下值的单元格设置格式 ” 文本框中输入 “1000”，使用默认设置 “ 浅红填充色深红色文本” ，单击 “ 确定 ” 按钮。步骤 2：选择【M3:M14 】单元格，切换至【开始】选项卡，单击【样式】选项组下的【条件格式】下拉按钮，在弹出的下拉名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 15 页，共 18 页 - - - - - - - - - 学习必备欢迎下载列表中选择【突出显示单元格规则】-【大于】，在 “ 为大于以下值的单元格设置格式” 文本框中输入 “=$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) 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 16 页，共 18 页 - - - - - - - - - 学习必备欢迎下载销售额小计注：销售量超过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” ) 设置数字格式为百分比，保留两位小数名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 18 页，共 18 页 - - - - - - - - -