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 次空格键， 击【数据工具】组中的“分列 ”按钮，在弹出的对话框中选择 “固定宽度 ”单选按钮，单击 “下一步”按钮，然后建立分列线。