计算机二级MSofficeexcel中所用函数整理.doc

精品文档
一、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) 1 and(1,0) 0
例：=if(and(语文>90,数学>90,英语>90),”合格”，”不合格”)
2、or()逻辑判断函数“或”(拓展)
格式：=or(条件1，条件2……)
例：or(1,1) 1 or(1,0) 1 or(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

.