计算机二级MSOfficeEcel函数公式大集合Word格式文档下载.docx

计算机二级MSOfficeEcel函数公式大集合Word格式文档下载.docx

《计算机二级MSOfficeEcel函数公式大集合Word格式文档下载.docx》由会员分享，可在线阅读，更多相关《计算机二级MSOfficeEcel函数公式大集合Word格式文档下载.docx（9页珍藏版）》请在冰豆网上搜索。

=2011-7-1"

=2011-9-30"

（8）=SUMIFS（订单明细表!

=2011-1-1"

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

合并计算

双击工作表sheet3的表名，在编辑状态下输入"

比较数据"

。

在该工作表的A1中输入"

地区"

，在【数据】选项卡的【数据工具】组中单击"

合并计算"

按钮，弹出"

对话框，设置"

函数"

为"

求和"

，在"

引用位置"

文本框中键入第一个区域"

第五次普查数据!

$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"

费用报销管理!

=2013-6-30"

（6）2013年钱顺卓报销的火车票总计金额为

B401=”钱顺卓”）,1*（费用报销管理!

F3:

F401=”火车票”），

费用报销管理!

G401）

F401,费用报销管理!

F10,费用报销管理!

B401,费用报销管理!

B378）

（7）2013年差旅费用金额中，飞机票占所有报销费用的比例为（保留2位小数）

F401=”飞机票”）,费用报销管理!

G401）/SUM（费用报销管理!

F401,费用报销管理!

F3）/SUM（费用报销管理!

（8）2013年发生在周末（星期六和星期日）中的通讯补助总金额为

=SUMPRODUCT（（费用报销管理!

H401=”是”）*（费用报销管理!

F401=”通讯补助”）,费用报销管理！

H401,费用报销管理!

H3,费用报销管理!

F391）

第6套

定义名称

在“平均单价”工作表中选中B3：

C7区域，单击鼠标右键，在弹出的下拉列表中选择"

定义名称"

命令，打开"

新建名称"

名称"

中输入"

商品均价"

后单击“确定”按钮即可。

销售额:

=VLOOKUP（D4,商品均价,2,0）*E4

第7套

（5）应交个人所得税

=ROUND（IF（K3<

=1500,K3*,IF（K3<

=4500,K3*,IF（K3<

=9000,K3*,IF（K3<

=35000,K3*,IF（K3<

=55000,K3*,IF（K3<

=80000,K3*,IF（K3>

80000,K3*））））））,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月（销售）

$C$678,2）

《Office商务办公好帮手》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,初三学生档案!

$B$56,2,FALSE），其余方法一样

按照三个比例计算学期成绩=SUM（C2*30%）+（D2*30%）+（E2*40%）

按成绩由高到低的顺序排名，并按“第n名”的形式填入“班级名次”列中

="

第"

&

RANK（F2,$F$2:

$F$45）&

名"

“期末总评”

数学，语文：

=IF（F2>

=102,"

优秀"

IF（F2>

=84,"

良好"

=72,"

及格"

72,"

不及格"

））））

英语及其他:

=IF（F3>

=90,"

IF（F3>

=75,"

=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$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,学号对照!

$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（员工档案!

H37,”本科”,员工档案!

K3:

K37）

（5）所有人的基础工资:

=SUM（员工档案!

M3:

M37）

（6）项目经理的基本工资总额:

=员工档案!

K3+员工档案!

K7

（7）本科生平均基本工资:

第17套

根据生成的数据透视表，在透视表下方创建一个簇状柱形图，图表中仅对博达书店一月份的销售额小计进行比较。

选中博达书店销售额小计，单击【开始】选项卡下【图表】组中的"

柱形图"

簇状柱形图"

命令。

步骤2：

数据透视图"

中单击"

书店名称"

右侧下三角按钮，在下拉列表中只选择"

博达书店"

第18套

选择【B3:

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

选择【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本（含），按折销售

=IF（[@销量（本）]>

=40,[@单价]*[@销量（本）]*,[@单价]*[@销量（本）]）

所属区域:

=VLOOKUP（MID（[@发货地址],1,3）,表3,2,0）

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

=SUMIFS（表1[销售额小计],表1[日期],”>

=2013-1-1”,表1[日期],”<

=2013-12-31”）

《MSOffice高级应用》图书在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位小数）

=2012-12-31”）/12

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

=2013-12-31”）/SUMIFS（表1[销售额小计],表1[日期],”>

=2013-12-31”）设置数字格式为百分比，保留两位小数