计算机二级Office2010Eexcel公式汇总

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列单元格，单击鼠标右键，在弹出的快捷菜单中选择“插入”选项。然后选中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(F2>72,"及格","不及格"))))

英语及其他: =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(H2>0,”借”,”贷”))

第13套

(3)销量

=VLOOKUP(A4,销量信息,3,FALSE)

条件格式

选中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)

(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)

根据生成的数据透视表，在透视表下方创建一个簇状柱

形图，图表中仅对博达书店一月份的销售额小计进行比较。

选中博达书店销售额小计，单击【开始】选项卡下【图表】组中的"柱形图"按钮，在弹出的下拉列表中选择"簇状柱形图"命令。

步骤2：在"数据透视图"中单击"书店名称"右侧下三角按钮，在下拉列表中只选择"博达书店"复选框。

第18套

条件格式

选择【B3:L14】单元格，切换至【开始】选项卡，单击【样式】选项组下的【条件格式】下拉按钮，在下拉列表中选择【突出显示单元格规则】-【大于】，在“为大于以下值的单元格设置格式”文本框中输入“1000”，使用默认设置“浅红填充色深红色文本”，单击“确定”按钮。

步骤2：选择【M3:M14】单元格，切换至【开始】选项卡，单击【样式】选项组下的【条件格式】下拉按钮，在弹出的下拉列表中选择【突出显示单元格规则】-【大于】，在“为大于以下值的单元格设置格式”文本框中输入“=$M$15*110%”，设置颜色为“黄填充色深黄色文本”，单击“确定”按钮。

季度

=”第”&INT(1+(MONTH(A3)-1)/3)&”季度”

停放时间

=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)

2013年所有图书订单的销售额

=SUMIFS(表1[销售额小计],表1[日期],”>=2013-1-1”,表1[日期],”<=2013-12-31”)

《MS Office高级应用》图书在2012年的总销售额

=SUMIFS(表1[销售额小计],表1[图书名称],订单明细!D7,表1[日期],”>=2012-1-1”, 表1[日期], ”<=2012-12-31”)

隆华书店在2013年第3季度（7月1日~9月30日）的总销售额

=SUMIFS(表1[销售额小计],表1[书店名称],订单明细!C14,表1[日期],”>=2013-7-1”, 表1[日期],”<=2013-9-30”)

隆华书店在2012年的每月平均销售额（保留2位小数）=SUMIFS(表1[销售额小计],表1[书店名称],订单明细!C14,表1[日期],”>=2012-1-1”, 表1[日期],”<=2012-12-31”)/12

2013年隆华书店销售额占公司全年销售总额的百分比（保留2位小数）

=SUMIFS(表1[销售额小计],表1[书店名称],订单明细!C14,表1[日期],” >=2013-1-1”, 表1[日期],”<=2013-12-31”)/SUMIFS(表1[销售额小计],表1[日期],”>=2013-1-1”,表1[日期],”<=2013-12-31”) 设置数字格式为百分比，保留两位小数