计算机二级Office2010Eexcel公式汇总
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
第
根据生成的数据透视表，在透视表下方创建一个簇状柱形图，图表中仅对博达书店一月份的销售额小计进行比较。
选中博达书店销售额小计，单击【开始】选项卡下【图表】组中的"柱形图"按钮，在弹出的下拉列表中选择"簇状柱形图"命令。
步骤2：在"数据透视图"中单击"书店名称"右侧下三角按钮，在下拉列表中只选择"博达书店"复选框。
(8)
=SUMPRODUCT(1*(D3:D17=”行政”),I3:I17)
管理部门实发工资
=SUMPRODUCT(1*(D3:D17=”行政”),M3:M17)
第
平均成绩和总分
=SUM(D3:L3)
=AVERAGE(D3:L3)
年级排名
=RANK(M3,M$3:M$102,0)
学号的第三位为专业代码、第四位代表班级序号
=SUMIF(授课信息表!$D$3:$D$72,E3,授课信息表!$F$3:$F$72)
第
(3)
=IF(H2=0,”平”,IF(H2>0,”借”,”贷”))
第
(3)
=VLOOKUP(A4,销量信息,3,FALSE)
第
条件格式
选中F2：G14，单击【开始】选项卡下【样式】组中的"条件格式"按钮，选择"突出显示单元格规则"中的"其他规则"，弹出"新建格式规则"对话框，在"编辑规则说明"选项下设置单元格值小于80。然后单击"格式"按钮，弹出"设置单元格格式"对话框，在"填充"选项卡下选择"红色"，单击"确定"按钮。
第
条件格式
选择【B3:L14】单元格，切换至【开始】选项卡，单击【样式】选项组下的【条件格式】下拉按钮，在下拉列表中选择【突出显示单元格规则】-【大于】，在“为大于以下值的单元格设置格式”文本框中输入“1000”，使用默认设置“浅红填充色深红色文本”，单击“确定”按钮。
(6)2013
=SUMPRODUCT(1*(费用报销管理!B3:B401=”钱顺卓”), 1*(费用报销管理!F3:F401=”火车票”)，
费用报销管理!G3:G401)
=SUMIFS(费用报销管理!G3:G401,费用报销管理!F3:F401,费用报销管理!F10,费用报销管理!B3:B401,费用报销管理!B378)
=IF(YEAR(销售订单!$B3)=2013,MONTH(销售订单!$B3),0)
《
==SUMIFS(销售订单!$H$3:$H$678,销售订单!$E$3:$E$678,'2013年图书销售分析'!$A4,销售订单!$C$3:$C$678,1)
《
==SUMIFS(销售订单!$H$3:$H$678,销售订单!$E$3:$E$678,'2013年图书销售分析'!$A4,销售订单!$C$3:$C$678,2)
分类汇总
选中C15，单击【数据】选项卡下【分级显示】组中的"分类汇总"按钮，弹出"分类汇总"对话框，单击"分类字段"组中的下拉按钮选择"班级"，单击"汇总方式"组中的下拉按钮选择"平均值"，在"选定汇总项"组中勾选"高等代数"、"数学分析"、"大学英语"、"大学物理"、"VB程序设计"、"C语言程序设计"、"Matlab"复选框，并勾选"每组数据分页"复选框。
(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”)
（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”},{”1班”,”2班”,”3班”})
其他分类汇总方法一致
第
(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)&”日”
=INT((TODAY()-E2)/365)
(4)
=VLOOKUP(A2,初三学生档案!$A$1:$B$56,2,FALSE)，其余方法一样
=SUM(C2*30%)+(D2*30%)+(E2*40%)
="第"&RANK(F2,$F$2:$F$45)&"名"
数学，语文：=IF(F2>=102,"优秀",IF(F2>=84,"良好",IF(F2>=72,"及格",IF(F2>72,"及格","不及格"))))
Hale Waihona Puke Baidu(7)
=SUMPRODUCT(1*(订单明细表!C305:C461 =”隆华书店”),订单明细表!H350:H461)
(8)
=SUMPRODUCT(1*(订单明细表!C262:C636 =”隆华书店”),订单明细表!H263:H636)/12
使用SUMIFS：
（5）=SUBTOTAL(9,订单明细表!H3:H636)
(3)
=INT((TODAY()-I3)/365)
(4)
=AVERAGEIF(员工档案!H3:H37,”本科”,员工档案!K3:K37)
(5)
=SUM(员工档案!M3:M37)
(6)
=员工档案!K3+员工档案!K7
(7)
=AVERAGEIF(员工档案!H3:H37,”本科”,员工档案!K3:K37)
《
==SUMIFS(销售订单!$H$3:$H$678,销售订单!$E$3:$E$678,'2013年图书销售分析'!$A4,销售订单!$C$3:$C$678,3)
每月图书总销量
=SUBTOTAL(109,B4:B11)添加汇总行
第
分列
选中B列单元格，单击鼠标右键，在弹出的快捷菜单中选择“插入”选项。然后选中A1单元格，将光标置于“学号”和“名字”之间，按3次空格键，然后选中A列单元格，单击【数据工具】组中的“分列”按钮，在弹出的对话框中选择“固定宽度”单选按钮，单击“下一步”按钮，然后建立分列线。单击“下一步”按钮，保持默认设置，单击“完成”按钮。
步骤2：按照上述方法浏览网页"第六次全国人口普查公报.htm"，将其中的"2010年第六次全国人口普查主要数据"表格导入到工作表"第六次普查数据"中。
合并计算
双击工作表sheet3的表名，在编辑状态下输入"比较数据"。在该工作表的A1中输入"地区"，在【数据】选项卡的【数据工具】组中单击"合并计算"按钮，弹出"合并计算"对话框，设置"函数"为"求和"，在"引用位置"文本框中键入第一个区域"第五次普查数据!$A$1:$C$34"，单击"添加"按钮，键入第二个区域"第六次普查数据!$A$1:$C$34"，单击"添加"按钮，在"标签位置"下勾选"首行"复选框和"最左列"复选框，然后单击"确定"按钮。
数据透视表筛选
单击行标签右侧的"标签筛选"按钮，在弹出的下拉列表中选择"值筛选"，打开级联菜单，选择"大于"，弹出"值筛选（地区）"对话框，在第一个文本框中选择"求和项：2010年人口数（万人）"，第二个文本框选择"大于"，在第三个文本框中输入"5000"，单击"确定"按钮。
第
在日期后添加星期
在"费用报销管理"工作表中，选中"日期"数据列，单击鼠标右键，在弹出的快捷菜单中选择"设置单元格格式"命令，弹出"设置单元格格式"对话框。切换至"数字"选项卡，在"分类"列表框中选择"自定义"命令，在右侧的"示例"组中"类型"列表框中输入" yyyy"年"m"月"d"日" aaaa "。设置完毕后单击"确定"按钮即可。
(7)2013
=SUMPRODUCT(1*(费用报销管理!F3:F401=”飞机票”),费用报销管理!G3:G401)/SUM(费用报销管理!G3:G401)
=SUMIFS(费用报销管理!G3:G401,费用报销管理!F3:F401,费用报销管理!F3)/SUM(费用报销管理!G3:G401)
(8)2013
(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")
EXCEL
第
(2)
=VLOOKUP(D3,编号对照!$A$3:$C$19,2,FALSE)
(3)
=VLOOKUP(D3,编号对照!$A$3:$C$19,3,FALSE)
(4)
=[@单价]*[@销量（本）]
(5)
=SUM(订单明细表!H3:H636)
“撤销计划列”
(6)
“降序”
=SUMPRODUCT(1*(订单明细表!E3:E262=”《MS Office高级应用》”),订单明细表!H3:H262)
=”法律”&TEXT(MID(B3,3,2),”[DBNum1]”)&”班”
=LOOKUP(MID(B3,3,2),{"01","02","03","04"},{"法律一班","法律二班","法律三班","法律四班"})
插入图表到指定单元格
按住AIT键，带鼠标指针出现斜向上箭头是开始拖动
第
插入一列
“插入”在C3中输入
= SUMPRODUCT((费用报销管理!H3:H401=”是”)*(费用报销管理!F3:F401=”通讯补助”),费用报销管理！G3:G401)
=SUMIFS(费用报销管理!G3:G401,费用报销管理!H3:H401,费用报销管理!H3,费用报销管理!F3:F401,费用报销管理!F391)
第
定义名称
在“平均单价”工作表中选中B3：C7区域，单击鼠标右键，在弹出的下拉列表中选择"定义名称"命令，打开"新建名称"对话框。在"名称"中输入"商品均价"后单击“确定”按钮即可。
销售额
=VLOOKUP(D4,商品均价,2,0)*E4
第
(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)
英语及其他: =IF(F3>=90,"优秀",IF(F3>=75,"良好",IF(F3>=60,"及格","不及格")))
=VLOOKUP(A3,初三学生档案!$A$2:$B$56,2,FALSE)
=VLOOKUP(A3,语文!A2:F45,6,FALSE)
第
(3)
==VLOOKUP(E12,教师基本信息!$D$3:$E$22,2,FALSE)
（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")
第
（1）
=产品基本信息表!C2*C2
（2）
=一季度销售情况表!C2+'二季度销售情况表'!C2
=一季度销售情况表!D2+'二季度销售情况表'!D2
(3)
=RANK(D2,$D$2:$D$21,0)RANK函数排序区域绝对引用。
第
数据自网页导入
在考生文件夹下双击打开网页"第五次全国人口普查公报.htm"，在工作表"第五次普查数据"中选中A1，单击【数据】选项卡下【获取外部数据】组中的"自网站"按钮，弹出"新建Web查询"对话框，在"地址"文本框中输入网页"第五次全国人口普查公报.htm"的地址，单击右侧的"转到"按钮。单击要选择的表旁边的带方框的黑色箭头，使黑色箭头变成对号，然后单击"导入"按钮。之后会弹出"导入数据"对话框，选择"数据的放置位置"为"现有工作表"，在文本框中输入"=$A$1"，单击"确定"按钮。
根据生成的数据透视表，在透视表下方创建一个簇状柱形图，图表中仅对博达书店一月份的销售额小计进行比较。
选中博达书店销售额小计，单击【开始】选项卡下【图表】组中的"柱形图"按钮，在弹出的下拉列表中选择"簇状柱形图"命令。
步骤2：在"数据透视图"中单击"书店名称"右侧下三角按钮，在下拉列表中只选择"博达书店"复选框。
(8)
=SUMPRODUCT(1*(D3:D17=”行政”),I3:I17)
管理部门实发工资
=SUMPRODUCT(1*(D3:D17=”行政”),M3:M17)
第
平均成绩和总分
=SUM(D3:L3)
=AVERAGE(D3:L3)
年级排名
=RANK(M3,M$3:M$102,0)
学号的第三位为专业代码、第四位代表班级序号
=SUMIF(授课信息表!$D$3:$D$72,E3,授课信息表!$F$3:$F$72)
第
(3)
=IF(H2=0,”平”,IF(H2>0,”借”,”贷”))
第
(3)
=VLOOKUP(A4,销量信息,3,FALSE)
第
条件格式
选中F2：G14，单击【开始】选项卡下【样式】组中的"条件格式"按钮，选择"突出显示单元格规则"中的"其他规则"，弹出"新建格式规则"对话框，在"编辑规则说明"选项下设置单元格值小于80。然后单击"格式"按钮，弹出"设置单元格格式"对话框，在"填充"选项卡下选择"红色"，单击"确定"按钮。
第
条件格式
选择【B3:L14】单元格，切换至【开始】选项卡，单击【样式】选项组下的【条件格式】下拉按钮，在下拉列表中选择【突出显示单元格规则】-【大于】，在“为大于以下值的单元格设置格式”文本框中输入“1000”，使用默认设置“浅红填充色深红色文本”，单击“确定”按钮。
(6)2013
=SUMPRODUCT(1*(费用报销管理!B3:B401=”钱顺卓”), 1*(费用报销管理!F3:F401=”火车票”)，
费用报销管理!G3:G401)
=SUMIFS(费用报销管理!G3:G401,费用报销管理!F3:F401,费用报销管理!F10,费用报销管理!B3:B401,费用报销管理!B378)
=IF(YEAR(销售订单!$B3)=2013,MONTH(销售订单!$B3),0)
《
==SUMIFS(销售订单!$H$3:$H$678,销售订单!$E$3:$E$678,'2013年图书销售分析'!$A4,销售订单!$C$3:$C$678,1)
《
==SUMIFS(销售订单!$H$3:$H$678,销售订单!$E$3:$E$678,'2013年图书销售分析'!$A4,销售订单!$C$3:$C$678,2)
分类汇总
选中C15，单击【数据】选项卡下【分级显示】组中的"分类汇总"按钮，弹出"分类汇总"对话框，单击"分类字段"组中的下拉按钮选择"班级"，单击"汇总方式"组中的下拉按钮选择"平均值"，在"选定汇总项"组中勾选"高等代数"、"数学分析"、"大学英语"、"大学物理"、"VB程序设计"、"C语言程序设计"、"Matlab"复选框，并勾选"每组数据分页"复选框。
(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”)
（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”},{”1班”,”2班”,”3班”})
其他分类汇总方法一致
第
(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)&”日”
=INT((TODAY()-E2)/365)
(4)
=VLOOKUP(A2,初三学生档案!$A$1:$B$56,2,FALSE)，其余方法一样
=SUM(C2*30%)+(D2*30%)+(E2*40%)
="第"&RANK(F2,$F$2:$F$45)&"名"
数学，语文：=IF(F2>=102,"优秀",IF(F2>=84,"良好",IF(F2>=72,"及格",IF(F2>72,"及格","不及格"))))
Hale Waihona Puke Baidu(7)
=SUMPRODUCT(1*(订单明细表!C305:C461 =”隆华书店”),订单明细表!H350:H461)
(8)
=SUMPRODUCT(1*(订单明细表!C262:C636 =”隆华书店”),订单明细表!H263:H636)/12
使用SUMIFS：
（5）=SUBTOTAL(9,订单明细表!H3:H636)
(3)
=INT((TODAY()-I3)/365)
(4)
=AVERAGEIF(员工档案!H3:H37,”本科”,员工档案!K3:K37)
(5)
=SUM(员工档案!M3:M37)
(6)
=员工档案!K3+员工档案!K7
(7)
=AVERAGEIF(员工档案!H3:H37,”本科”,员工档案!K3:K37)
《
==SUMIFS(销售订单!$H$3:$H$678,销售订单!$E$3:$E$678,'2013年图书销售分析'!$A4,销售订单!$C$3:$C$678,3)
每月图书总销量
=SUBTOTAL(109,B4:B11)添加汇总行
第
分列
选中B列单元格，单击鼠标右键，在弹出的快捷菜单中选择“插入”选项。然后选中A1单元格，将光标置于“学号”和“名字”之间，按3次空格键，然后选中A列单元格，单击【数据工具】组中的“分列”按钮，在弹出的对话框中选择“固定宽度”单选按钮，单击“下一步”按钮，然后建立分列线。单击“下一步”按钮，保持默认设置，单击“完成”按钮。
步骤2：按照上述方法浏览网页"第六次全国人口普查公报.htm"，将其中的"2010年第六次全国人口普查主要数据"表格导入到工作表"第六次普查数据"中。
合并计算
双击工作表sheet3的表名，在编辑状态下输入"比较数据"。在该工作表的A1中输入"地区"，在【数据】选项卡的【数据工具】组中单击"合并计算"按钮，弹出"合并计算"对话框，设置"函数"为"求和"，在"引用位置"文本框中键入第一个区域"第五次普查数据!$A$1:$C$34"，单击"添加"按钮，键入第二个区域"第六次普查数据!$A$1:$C$34"，单击"添加"按钮，在"标签位置"下勾选"首行"复选框和"最左列"复选框，然后单击"确定"按钮。
数据透视表筛选
单击行标签右侧的"标签筛选"按钮，在弹出的下拉列表中选择"值筛选"，打开级联菜单，选择"大于"，弹出"值筛选（地区）"对话框，在第一个文本框中选择"求和项：2010年人口数（万人）"，第二个文本框选择"大于"，在第三个文本框中输入"5000"，单击"确定"按钮。
第
在日期后添加星期
在"费用报销管理"工作表中，选中"日期"数据列，单击鼠标右键，在弹出的快捷菜单中选择"设置单元格格式"命令，弹出"设置单元格格式"对话框。切换至"数字"选项卡，在"分类"列表框中选择"自定义"命令，在右侧的"示例"组中"类型"列表框中输入" yyyy"年"m"月"d"日" aaaa "。设置完毕后单击"确定"按钮即可。
(7)2013
=SUMPRODUCT(1*(费用报销管理!F3:F401=”飞机票”),费用报销管理!G3:G401)/SUM(费用报销管理!G3:G401)
=SUMIFS(费用报销管理!G3:G401,费用报销管理!F3:F401,费用报销管理!F3)/SUM(费用报销管理!G3:G401)
(8)2013
(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")
EXCEL
第
(2)
=VLOOKUP(D3,编号对照!$A$3:$C$19,2,FALSE)
(3)
=VLOOKUP(D3,编号对照!$A$3:$C$19,3,FALSE)
(4)
=[@单价]*[@销量（本）]
(5)
=SUM(订单明细表!H3:H636)
“撤销计划列”
(6)
“降序”
=SUMPRODUCT(1*(订单明细表!E3:E262=”《MS Office高级应用》”),订单明细表!H3:H262)
=”法律”&TEXT(MID(B3,3,2),”[DBNum1]”)&”班”
=LOOKUP(MID(B3,3,2),{"01","02","03","04"},{"法律一班","法律二班","法律三班","法律四班"})
插入图表到指定单元格
按住AIT键，带鼠标指针出现斜向上箭头是开始拖动
第
插入一列
“插入”在C3中输入
= SUMPRODUCT((费用报销管理!H3:H401=”是”)*(费用报销管理!F3:F401=”通讯补助”),费用报销管理！G3:G401)
=SUMIFS(费用报销管理!G3:G401,费用报销管理!H3:H401,费用报销管理!H3,费用报销管理!F3:F401,费用报销管理!F391)
第
定义名称
在“平均单价”工作表中选中B3：C7区域，单击鼠标右键，在弹出的下拉列表中选择"定义名称"命令，打开"新建名称"对话框。在"名称"中输入"商品均价"后单击“确定”按钮即可。
销售额
=VLOOKUP(D4,商品均价,2,0)*E4
第
(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)
英语及其他: =IF(F3>=90,"优秀",IF(F3>=75,"良好",IF(F3>=60,"及格","不及格")))
=VLOOKUP(A3,初三学生档案!$A$2:$B$56,2,FALSE)
=VLOOKUP(A3,语文!A2:F45,6,FALSE)
第
(3)
==VLOOKUP(E12,教师基本信息!$D$3:$E$22,2,FALSE)
（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")
第
（1）
=产品基本信息表!C2*C2
（2）
=一季度销售情况表!C2+'二季度销售情况表'!C2
=一季度销售情况表!D2+'二季度销售情况表'!D2
(3)
=RANK(D2,$D$2:$D$21,0)RANK函数排序区域绝对引用。
第
数据自网页导入
在考生文件夹下双击打开网页"第五次全国人口普查公报.htm"，在工作表"第五次普查数据"中选中A1，单击【数据】选项卡下【获取外部数据】组中的"自网站"按钮，弹出"新建Web查询"对话框，在"地址"文本框中输入网页"第五次全国人口普查公报.htm"的地址，单击右侧的"转到"按钮。单击要选择的表旁边的带方框的黑色箭头，使黑色箭头变成对号，然后单击"导入"按钮。之后会弹出"导入数据"对话框，选择"数据的放置位置"为"现有工作表"，在文本框中输入"=$A$1"，单击"确定"按钮。