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 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函数之前先排序，尤其是涉及时间的时候。
第2套(4)班级=LOOKUP(MID(A2,3,2),{”01”,”02”,”03”},{”1班”,”2班”,”3班”})第3套（1）一季度销售额(元）、二季度销售额（元）=产品基本信息表!C2*C2（2）一二季度销售总量、一二季度销售总额=一季度销售情况表!C2+'二季度销售情况表'!C2=一季度销售情况表!D2+'二季度销售情况表'!D2(3)销售额排名=RANK(D2,$D$2:$D$21,0) RANK函数排序区域绝对引用。
第4套数据自网页导入在考生文件夹下双击打开网页"第五次全国人口普查公报.htm"，在工作表"第五次普查数据"中选中A1，单击【数据】选项卡下【获取外部数据】组中的"自网站"按钮，弹出"新建Web查询"对话框，在"地址"文本框中输入网页"第五次全国人口普查公报.htm"的地址，单击右侧的"转到"按钮。
单击要选择的表旁边的带方框的黑色箭头，使黑色箭头变成对号，然后单击"导入"按钮。
之后会弹出"导入数据"对话框，选择"数据的放置位置"为"现有工作表"，在文本框中输入"=$A$1"，单击"确定"按钮。
步骤2：按照上述方法浏览网页"第六次全国人口普查公报.htm"，将其中的"2010年第六次全国人口普查主要数据"表格导入到工作表"第六次普查数据"中。
合并计算双击工作表sheet3的表名，在编辑状态下输入"比较数据"。
在该工作表的A1中输入"地区"，在【数据】选项卡的【数据工具】组中单击"合并计算"按钮，弹出"合并计算"对话框，设置"函数"为"求和"，在"引用位置"文本框中键入第一个区域"第五次普查数据!$A$1:$C$34"，单击"添加"按钮，键入第二个区域"第六次普查数据!$A$1:$C$34"，单击"添加"按钮，在"标签位置"下勾选"首行"复选框和"最左列"复选框，然后单击"确定"按钮。
数据透视表筛选单击行标签右侧的"标签筛选"按钮，在弹出的下拉列表中选择"值筛选"，打开级联菜单，选择"大于"，弹出"值筛选（地区）"对话框，在第一个文本框中选择"求和项：2010年人口数（万人）"，第二个文本框选择"大于"，在第三个文本框中输入"5000"，单击"确定"按钮。
第5套在日期后添加星期在"费用报销管理"工作表中，选中"日期"数据列，单击鼠标右键，在弹出的快捷菜单中选择"设置单元格格式"命令，弹出"设置单元格格式"对话框。
切换至"数字"选项卡，在"分类"列表框中选择"自定义"命令，在右侧的"示例"组中"类型"列表框中输入" 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=”钱顺卓”), 1*(费用报销管理!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(费用报销管理!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: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,'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列单元格，单击鼠标右键，在弹出的快捷菜单中选择“插入”选项。