计算机二级MS office excel中所用函数整理.docx

计算机二级MS office excel中所用函数整理.docx

《计算机二级MS office excel中所用函数整理.docx》由会员分享，可在线阅读，更多相关《计算机二级MS office excel中所用函数整理.docx（13页珍藏版）》请在冰豆网上搜索。

计算机二级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