计算机二级OfficeEexcel公式汇总.docx
《计算机二级OfficeEexcel公式汇总.docx》由会员分享，可在线阅读，更多相关《计算机二级OfficeEexcel公式汇总.docx（12页珍藏版）》请在冰点文库上搜索。
计算机二级OfficeEexcel公式汇总
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）《MSOffice高级应用》图书在20XX年的总销售额
“降序”
=SUMPRODUCT（1*（订单明细表!
E3:
E262=”《MSOffice高级应用》”）,订单明细表!
H3:
H262）
（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,"<=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"，将其中的"20XX年第六次全国人口普查主要数据"表格导入到工作表"第六次普查数据"中。
合并计算
双击工作表sheet3的表名，在编辑状态下输入"比较数据"。
在该工作表的A1中输入"地区"，在【数据】选项卡的【数据工具】组中单击"合并计算"按钮，弹出"合并计算"对话框，设置"函数"为"求和"，在"引用位置"文本框中键入第一个区域"第五次普查数据!
$A$1:
$C$34"，单击"添加"按钮，键入第二个区域"第六次普查数据!
$A$1:
$C$34"，单击"添加"按钮，在"标签位置"下勾选"首行"复选框和"最左列"复选框，然后单击"确定"按钮。
数据透视表筛选
单击行标签右侧的"标签筛选"按钮，在弹出的下拉列表中选择"值筛选"，打开级联菜单，选择"大于"，弹出"值筛选（地区）"对话框，在第一个文本框中选择"求和项：
20XX年人口数（万人）"，第二个文本框选择"大于"，在第三个文本框中输入"5000"，单击"确定"按钮。
第5套
在日期后添加星期
在"费用报销管理"工作表中，选中"日期"数据列，单击鼠标右键，在弹出的快捷菜单中选择"设置单元格格式"命令，弹出"设置单元格格式"对话框。
切换至"数字"选项卡，在"分类"列表框中选择"自定义"命令，在右侧的"示例"组中"类型"列表框中输入"yyyy"年"m"月"d"日"aaaa"。
设置完毕后单击"确定"按钮即可。
（2）是否加班
=IF（WEEKDAY（A3,2）>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,费用报销管理!
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区域，单击鼠标右键，在弹出的下拉列表中选择"定义名称"命令，打开"新建名称"对话框。
在"名称"中输入"商品均价"后单击“确定”按钮即可。
销售额
=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,'20XX年图书销售分析'!
$A4,销售订单!
$C$3:
$C$678,1）
《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,”男”,”女”）
出生日期
=--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）
第14套
条件格式
选中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）
第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）
（6）项目经理的基本工资总额
=员工档案!
K3+员工档案!
K7
（7）本科生平均基本工资
=AVERAGEIF（员工档案!
H3:
H37,”本科”,员工档案!
K3:
K37）
第17套
根据生成的数据透视表，在透视表下方创建一个簇状柱形图，图表中仅对博达书店一月份的销售额小计进行比较。
选中博达书店销售额小计，单击【开始】选项卡下【图表】组中的"柱形图"按钮，在弹出的下拉列表中选择"簇状柱形图"命令。
步骤2：
在"数据透视图"中单击"书店名称"右侧下三角按钮，在下拉列表中只选择"博达书店"复选框。
第18套
条件格式
选择【B3:
L14】单元格，切换至【开始】选项卡，单击【样式】选项组下的【条件格式】下拉按钮，在下拉列表中选择【突出显示单元格规则】-【大于】，在“为大于以下值的单元格设置格式”文本框中输入“1000”，使用默认设置“浅红填充色深红色文本”，单击“确定”按钮。
步骤2：
选择【M3:
M14】单元格，切换至【开始】选项卡，单击【样式】选项组下的【条件格式】下拉按钮，在弹出的下拉列表中选择【突出显示单元格规则】-【大于】，在“为大于以下值的单元格设置格式”文本框中输入“=$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）
销售额小计注：
销售量超过40本（含），按9.3折销售
=IF（[@销量（本）]>=40,[@单价]*[@销量（本）]*0.93,[@单价]*[@销量（本）]）
所属区域
=VLOOKUP（MID（[@发货地址],1,3）,表3,2,0）
20XX年所有图书订单的销售额
=SUMIFS（表1[销售额小计],表1[日期],”>=2013-1-1”,表1[日期],”<=2013-12-31”）
《MSOffice高级应用》图书在20XX年的总销售额
=SUMIFS（表1[销售额小计],表1[图书名称],订单明细!
D7,表1[日期],”>=2012-1-1”,表1[日期],”<=2012-12-31”）
隆华书店在20XX年第3季度（7月1日~9月30日）的总销售额
=SUMIFS（表1[销售额小计],表1[书店名称],订单明细!
C14,表1[日期],”>=2013-7-1”,表1[日期],”<=2013-9-30”）
隆华书店在20XX年的每月平均销售额（保留2位小数）
=SUMIFS（表1[销售额小计],表1[书店名称],订单明细!
C14,表1[日期],”>=2012-1-1”,表1[日期],”<=2012-12-31”）/12
20XX年隆华书店销售额占公司全年销售总额的百分比（保留2位小数）
=SUMIFS（表1[销售额小计],表1[书店名称],订单明细!
C14,表1[日期],”>=2013-1-1”,表1[日期],”<=2013-12-31”）/SUMIFS（表1[销售额小计],表1[日期],”>=2013-1-1”,表1[日期],”<=2013-12-31”）设置数字格式为百分比，保留两位小数
升级会员 