完整计算机二级MS office excel中所用函数整理.docx
《完整计算机二级MS office excel中所用函数整理.docx》由会员分享，可在线阅读，更多相关《完整计算机二级MS office excel中所用函数整理.docx（12页珍藏版）》请在冰豆网上搜索。
完整计算机二级MSofficeexcel中所用函数整理
一、If条件判断
格式：
=if（条件，真，假）
语文：
60
=if（成绩〉=60,”及格”，”不及格”）
90〉=优秀、
80〉=良好
60〉=合格
59〈=不合格
格式：
=if（条件，真，if（条件，真,if（条件，真，假）））
格式：
=if（条件，if（条件,if（条件，真，假），假），假）
=if（成绩>=90,“优秀"，if（成绩>=80,"良好”，if（成绩>=60,合格，不合格）））
例：
第7套：
=IF（K3〈=1500，K3*3%,
IF（K3〈=4500,K3*10%—105，
IF（K3〈=9000,K3*20％-555，
IF（K3〈=35000，K3*25％-1005，
IF（K3〈=55000,K3*30%—2755,
IF（K3<=80000,K3＊35%—5505，K3＊45％—13505））））））
分类汇总
1、先排序
2、数据——分类汇总
二、求和函数sum（）
格式：
=sum（区域）
三、求平均值函数average（）
格式:
=average（区域）
四、最大最小值函数：
max，min
格式:
=max（区域）
格式：
=min（区域）
五、排名函数rank（）
格式:
=rank（排位数值，范围$，0）
例：
=RANK（J2，$J＄2：
$J$19,0）
六、左截取函数left（）
=left（原字符，长度）
=left（“河北省邯郸市”,3）
七、中间截取mid
Mid（原字符，起始位置，长度）
=mid（“河北省邯郸市”，4,3）
例：
第2，3套
公式，sum,average,rank,left
八、垂直查询Vlookup
格式：
=Vlookup（查找目标，范围$,列号,方式0）
九、sumif条件求和
格式：
=Sumif（条件区域，条件值，求和区域）
例：
性别:
女数学总成绩
=sumif（性别列,“女”，数学列）
十、sumifs多条件求和
格式：
=Sumifs（和区域，条件1，值1,条件2,值2………。
）
班级：
3班性别：
女数学总成绩
=sumifs（数学列,班级列，3班，性别列，女）
相关题库：
第1套
十一、星期函数Weekday
格式：
=Weekday（日期,数字2）
数字：
1：
星期天到星期六（1-7）西方
2：
星期一到星期天（1—7）
3:
星期一到星期天（0-6）
例:
第5套（if，left，vlookup,sumif，sumifs，weekday）
=IF（weekday（A3,2）〉=6,”是”,”否”）
统计2013年第二季度发生在北京市的差旅费用总金额。
第二季度（4,5，6）北京市差旅费用
74—340
=sumif（地区74-340，“北京"，差旅费用列）
=SUMIF（费用报销管理！
D74：
D340，费用报销管理!
D11，费用报销管理！
G74:
G340）
2013年员工钱顺卓报销的火车票费用总额。
钱顺卓火车票差旅费用
=SUMIFs（差旅费用列，报销人列，钱顺卓，类别列，火车票）
7、=sumif（类别列,飞机，差旅费用列）/sum（差旅费用列）
8、周末（是否：
是）类别:
通讯差旅费用列
=sumifs（差旅费用列,是否,是,类别，通讯）
例：
第17套（if，mod，mid,text，today,int）
十二、mod求余函数
格式:
=Mod（10，2）………………0求余数
mid
性别:
=IF（MOD（MID（C2,17,1）,2）=1,"男"，"女”）
出生：
=MID（C2,7，4）&”年"＆MID（C2，11，2）＆”月"＆MID（C2,13，2）＆"日”
十三、Text（）转换
格式：
=text（数值,文本形式）
=TEXT（MID（C3，7,8）,”0000-00—00”）
十四、Today（）当前日期，Int（）向下取整
=int（2。
6）
年龄：
int（（today（）—出生日期）/365）
工龄：
int（（today（）—入职日期）/365）
=INT（（TODAY（）—［＠出生日期］）/365）
姓名:
=VLOOKUP（A2，初三学生档案!
＄A$2：
＄B＄56,2，0）
名次:
="第”＆RANK（F2，＄F$2:
＄F$45,0）＆”名"
总评:
语文=IF（F2>=102，”优秀”,IF（F2〉=84,"良好”,IF（F2〉=74，”合格","不合格”）））
………。
例:
第9套
2、=TEXT（MID（F3，7，8），"0000年00月00日"）
3、int（（today（）—入职日期）/365）
=INT（（TODAY（）-I3）/365）
十五、averageif（）条件平均值
格式：
averageif（条件区域,值，平均值区域）
女语文平均
=averageif（性别，女，语文）
十六、averageifs（）多条件平平均值
格式：
averageifs（平均值区域，条件区域1，值1，条件区域2,值2………。
）
3班女语文平均
=averageifs（语文,性别，女，班级,3班）
例：
第4套
•导入网页中数据第4套2
方法：
数据——获取外部数据：
自网站
地址：
输入网页地址如:
D：
\.。
..html--转到
选择表:
箭头变对勾——导入——确定
•合并计算表格第4套4
方法
数据——数据工具：
合并计算
函数:
求和
引用位置：
sheet1：
A1：
C34添加
sheet2:
A1：
C34添加
勾选首行和最左列——确定
例:
第19套
十七、提取日期和时间函数
Year（）month（）day（）
hour（）minute（）
十八、向上取整函数
Roundup（）
格式：
=roundup（数值，小数位数）
例：
=roundup（3.2，0）…….4
2、=VLOOKUP（C2，收费标准!
＄A＄3：
＄B$5，2，0）
停放时间：
=（H2-F2）*24+（I2—G2）
收费金额：
=ROUNDUP（（HOUR（J2）＊60+MINUTE（J2））/15，0）＊E2
拟收费金额：
=INT（（HOUR（J2）*60+MINUTE（J2））/15）＊E2
新题库33套：
十九、trim（）删除空格函数，clean（）删除不可见字符
二十、len（）测字符串长度,lenb（）测字符字节数
例：
=len（“中国人hello”）…….8
=lenb（“中国人hello”）…。
.11
二十一、统计函数
•计数COUNT（）:
只对包含数的单元格计数统计数值的个数
格式:
=count（区域）
•计数COUNTA（）：
可以对包含任何类型的单元格计数
格式：
=counta（区域）
•条件计数COUNTIF（）COUNTIFS（）
格式：
=countif（条件区域，条件）
格式：
=countifs（条件区域,条件,条件区域2，条件2）
二十二、large（）求第几名的成绩
•Large函数
•格式：
=Large（统计区域，名次）
例:
=large（a1:
a11,1）
=large（a1:
a11,4）
例：
=large（＄L$2:
$L＄19，{1；2;3;4｝）
可求出1-4名的成绩
使用时注意：
选中4行，完成后，同时按下shift+ctrl+enter（回车），可出结果
二十三、逻辑判断函数：
and，or
if
1、and（）逻辑判断函数“与"常用与if条件
格式:
=and（条件1,条件2……）
and（1,1）1and（1,0）0
例：
=if（and（语文〉90,数学〉90，英语〉90），"合格”,”不合格”）
2、or（）逻辑判断函数“或”（拓展）
格式：
=or（条件1，条件2……）
例：
or（1,1）1or（1,0）1or（0，0）0
第24套:
第27套：
二十四、数组公式三个
根据条件求最大值：
Max（）
格式1:
=max（if（（条件1）*（条件2），求最大值区域））
格式2:
=max（（条件1）＊（条件2）＊求最大值区域）
3班女语文高成绩
=max（（班级=“3班”）*（性别=”女”）＊语文）——ctrl+shift+enter
例：
=MAX（IF（（D2:
D19="3班"）＊（C2:
C19=”女"）,E2：
E19））
=MAX（（D2：
D19=”3班"）*（C2:
C19=”女"）*E2:
E19）
Min（）—-同格式1
Sum（）——同格式2
注意：
所有数组公式，输入完成后，
按下shift+ctrl+enter键来结束输入。
二十五、判断计算结果错误函数iferror
二十六、查找指定字符位置函数find
格式:
=iferror（要判断的公式，显示结果）
例：
iferror（A2/B2，”错误"）
格式：
=find（要查找的字符，要进行搜索的字符串，起始位置1可省）
例：
a1：
”河北省教育考试院"
=find（“省”,a1）3
第13套
二十七、向量型查找函数LOOKUP:
把数（或本文）与一行或一列的数据依次进行匹配，匹配成功后，把对应的数值查找出来.
格式:
lookup（）
=LOOKUP（查找的值，查找范围，返回值范围）
例：
填充班级列（学号第3、4位代表学生所在的班级：
01:
1班……）mid
=LOOKUP（mid（A2,3，2）,{”01”，”02”,”03”},｛”一班”，"二班"，"三班”}）
=LOOKUP（MID（A2,3，2），Sheet2!
＄A$1：
$A＄3，Sheet2!
＄B＄1:
＄B$3）
二十八、row（）获取当前行
=mod（row（），2）=0-————-—-—偶数行
Mod（5，2）….。
…….1
二十九、Days360（）函数：
一年按360天的算法计算出两个日期之间相差的天数。
利用int可算出年龄
格式：
=days360（开始日期，结束日期）
方法：
false/省略，按美国方法计算，ture,按欧洲方法计算
美国：
如果起始日期是一个月的31天，则将这一天视为同一个月份的第30天;如果终止日期是一个月的第31天则视为下一月的第1天。
欧洲：
无论起始和终止是一个月的31天，都视为同一个月份的第30天.
例：
=int（days360（“1989—4—10”,”2018-11-19”）/360）…….。
29
三十、 Choose函数：
选择,就是从参数列表中选择一个并返回这个参数的值.
格式：
=choose（索引值，值1，值2，值3……）
索引值为1：
返回值1
索引值为2：
返回值2
……以次累推
例：
三十一、Offset函数：
以指定的（单元格或区域）为参照系，通过给定偏移量得到新的引用。
格式：
=offset（参照单元格，行偏移量，列偏移量，返回几行，返回几列）
例：
参照A1偏移2行
例：
参照A1偏移1行1列，返回2行2列shift+ctrl+enter（回车）
三十二、查找数值index（）查找位置match（）
•格式：
=index（区域，行号，列号）
如:
a1:
a3为｛68，96，90｝
则=index（a1：
a3，1，1）68
•格式：
=match（数值，区域，查询方式）
查询方式：
1小于或等于
-1大于或等于
0等于
例:
a1：
a5为[12，15，17，20,22］
=match（15。
5，a1:
a5,1）2
按顺序查找a1：
a5区域内查找小于或等于15。
5的值,找出其中最大数的位置
三十三、sqrt（）求平方根
=sqrt（9）
第30套
三十四、column（）用于返回单元格的列号，columns（）返回列数
题库中新增二相关函数（34，35，36）
格式：
=column（选择单元格可省）
可以返回所选单元格的列号，省略参数时返回公式所在的列号。
格式：
=columns（区域）返回所选区域的列数
例：
例：
例：
=COLUMNS（D3：
F3）…。
3返回选中了几列
三十五、sumproduct（）返回区域乘积的和
1、格式：
=sumproduct（区域1，区域2……）
区域中的值必须为数值类型，如果为文本数型则结果为0
例:
2、如果数值是文本类型还必须计算出数据,可以用以下格式
格式：
=sumproduct（区域1*区域2＊……）
例：
3、也可用于多条件求和
格式：
=sumproduct（条件1＊条件2*…,求和数据区域）
例:
三班女生语文成绩
=sumproduct（性别=”女"*班级=”3班”，语文成绩列）
三十六：
celing（）将数据向上舍入到指的基数
格式:
=celing（要舍入的数，指定的基数）
例：
=celing（4.12，0。
5）…4.5
=CEILING（1.61，0.5）…2
升级会员 