《计算机二级MS office excel中所用函数整理》由会员分享,可在线阅读,更多相关《计算机二级MS office excel中所用函数整理(11页珍藏版)》请在人人文库网上搜索。
1、一、If条件判断格式:=if(条件,真,假)语文:60=if(成绩=60,”及格”,”不及格”)90= 优秀、80=良好60=合格59=90,“优秀”,if(成绩=80,”良好”,if(成绩=60,合格,不合格)例:第7套:=IF(K3=6,是,否)统计第二季度发生在北京市的差旅费用总金额。第二季度(4,5,6) 北京市 差旅费用74-340=sumif(地区74-340,“北京”,差旅费用列)=SUMIF(费用报销管理!D74:D340,费用报销管理!D11,费用报销管理!G74:G340)员工钱顺卓报销的火车票费用总额。钱顺卓 火车票 差旅费用=SUMIFs(差旅费用列。
2、,报销人列,钱顺卓,类别列,火车票)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。
3、)十四、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)。
4、/365)十五、averageif()条件平均值格式:averageif(条件区域,值,平均值区域)女 语文 平均=averageif(性别,女,语文)十六、averageifs()多条件平平均值格式:averageifs(平均值区域,条件区域1,值1,条件区域2,值2.)3班 女 语文 平均=averageifs(语文,性别,女,班级,3班)例:第4套 导入网页中数据 第4套 2方法:数据获取外部数据:自网站地址:输入网页地址如:D:.html转到选择表:箭头变对勾导入确定 合并计算表格 第4套 4方法数据数据工具:合并计算函数:求和引用位置:sheet1:A1:C34 添加sheet2:A。
5、1:C34 添加勾选首行和最左列确定例:第19套十七、提取日期和时间函数Year() month() day()hour() minute() 十八、向上取整函数Roundup()格式:=roundup(数值,小数位数)例:=roundup(3.2,0).42、=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(。
6、)删除不可见字符二十、len()测字符串长度,lenb()测字符字节数例:=len(“中国人hello”).8=lenb(“中国人hello”).11二十一、统计函数 计数COUNT() : 只对包含数的单元格计数统计数值的个数格式:=count(区域) 计数COUNTA() :可以对包含任何类型的单元格计数格式:=counta(区域) 条件计数COUNTIF() COUNTIFS()格式:=countif(条件区域,条件) 格式:=countifs(条件区域,条件,条件区域2,条件2) 二十二、large()求第几名的成绩 Large函数 格式: =Large(统计区域,名次)例 :=lar。
7、ge(a1:a11,1)=large(a1:a11,4)例: =large($L$2:$L$19,1;2;3;4)可求出1-4名的成绩使用时注意:选中4行,完成后,同时按下shift+ctrl+enter(回车),可出结果二十三、逻辑判断函数:and ,orif1、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套。
8、:第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()同格式1Sum()同格式2注意:所有数组公式,输入完成后,按下 shift+ctrl+enter键 来结束输入。二十五、判断计算结果错误函数iferror 二十六、。
9、查找指定字符位置函数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,一班,二班,三班)=L。
10、OOKUP(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天。例:=in。
11、t(days360(“1989-4-10”,”-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(区。
12、域,行号,列号) 如: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(选择单元格 可省) 可以返回所选单元格的列号,省略参数时返回公式所在。
13、的列号。格式:=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。