函数是Excel最基本,也是最主要的功能之一。Excel中的函数指的是一些系统预定义的公式,通过输入参数值即可进行函数对应功能的计算,且函数名与功能基本对应,也比较好记。在日常工作中,使用函数可以进行数据的统计、计算、处理和分析。
Excel函数共有11类,包括:数据库函数、日期与时间函数、工程函数、财务函数、信息函数、逻辑函数、查询和引用函数、数学和三角函数、统计函数、文本函数、用户自定义函数。一般来说,太多了也记不得,用到的时候查一下就好了,且掌握少数常用的函数,在日常工作中就差不多够用了。以下内容对部分常用的Excel函数进行介绍。
NO.1 日期与时间函数
1. 日期类函数
DATE(Year, Month, Day)函数用于返回一个“年月日”格式的日期,如“=DATE(2020,6,18)”的返回结果为:2019/6/18,具体的日期格式可通过单元格格式设置进行设置。
YEAR(), MONTH(), DAY()用于返回日期中的年、月、日,这与SQL中的一些函数的使用相似。
2. 时间类函数
TIME(Hour, Minute, Second)函数用于返回一个“时分秒”格式的时间,如“=TIME(12,20,50)”的返回结果为:12:20:50,具体的时间格式可通过单元格格式设置进行设置。
HOUR(), MINUTE(), SECOND()用于返回时间中的小时、分钟、秒。
3. 日期时间函数
NOW()函数用于显示当前的日期和时间,如“=NOW()”的返回结果为:2020/6/18 19:30。
4. 星期类函数
给定一个日期,如何知道它是星期几?是当年的第几周?
WEEKDAY(日期,return_type)函数用于返回此日期是星期几,其中return_type可取1,2,3。其中1表示返回1-7之间的整数,1为星期日,2为星期一,...,7为星期六;2表示返回1-7之间的整数,1为星期一,2为星期二,...,7为星期日;3表示返回0-6之间的整数,0为星期一,1为星期2,...,6为星期日,可根据具体需要设定return_type(实操发现函数提示中竟然还有更多的选择)。
WEEKNUM(日期,return_type)函数用于返回此日期是当年的第几周,其中return_type可取1或2,与WEEKDAY()函数的定义相同,1表示从星期日开始,2表示从星期一开始。
5. 日期时间计算类函数
工作中可能会遇到计算两个日期之间间隔的问题,一般相隔天数的计算可以直接使用单元格相减进行求取,相隔的月和年也可以通过一系列加减乘除实现,但公式会略显麻烦。
DATEDIF(日期1,日期2,"D")函数可用于求取时间间隔,这个函数也是很眼熟了,SQL中也见过。其中参数“D”表示求间隔天数,那么"Y"和“M”就对应求间隔年、间隔月数了,此函数多用于计算工龄、年龄等数据。
发工资时只需用到实际工作日怎么办?
NETWORKDAYS(日期1,日期2,Holidays)函数用于计算两个日期间实际的工作日天数,其中Holidays表示除周末外的其他假日,可以用存放了假日信息的连续单元格区域表示。
还有如YEARFRAC()、WORKDAY()、EDATE()、EMONTH()这些与日期计算有关的函数就不做介绍了,用到的时候查一下就OK。
NO.2 统计函数
1. 数据计算类函数
简单的数据计算可以直接通过单元格的加减乘除实现,遇到数据量大,运算复杂的,可以采用数据计算函数来解决,常用的无非就是SUM、COUNT、AVERAGE、POWER、MOD等,常见的数据计算函数如下:
2. 数据分析类函数
数据分析类函数可以用于反映数据的内在特征、数据变化以及数据之间的关系,常用的函数有MIN、MAX、MODE、QUARTILE、VAR等,常见的数据分析函数如下:
一些用于反映数据变化的指标的计算,如增长率、同比、环比等,虽无对应的函数,但可以通过编写公式进行求取。
NO.3 文本函数
1. 截取类函数
截取类函数可以用来从文本或数值中提取指定位置的字符,常见的有LEFT、RIGHT、MID。
以LEFT为例,LEFT为从左侧提取指定数量的字符,语法为“=LEFT(文本,字符数)”,如A1中存放的是“数据处理”,则“=LEFT(A1,2)”返回的是“数据”,再如A2中存放的是“3456789”,则“=LEFT(A2,2)”返回的是“34”。LEFT函数在取汉字的时候是按照字符数取的,如果想按照字节数取,需要使用LEFTB,如“=LEFTB(A1,2)”返回的结果为“数”,当“=LEFTB(A1,4)”时,才会返回“数据”。RIGHT函数与LEFT相似,只是从右侧开始取。
MID函数可从任意位置开始提取指定数量的字符,语法为“=MID(文本,起始位置,字符数)”,如想提取身份证号中的出生日期,A1中存放身份证号“370285198711012345”,“=MID(A1,7,8)”,返回结果为“19871101”。
2. 连接类函数
连接类函数用于将字符串连接在一起,最简单的方法就是直接将多个单元格进行&,也就完成了字符串的拼接,如A1中是“数据”,A2中是“处理”,A3中是“结果”,则=A1&A2&A3返回的结果为“数据处理结果”。
除了&,CONCATENATE函数也能实现字符串的连接,语法为“=CONCATENATE(text1,text2,...)”,或者“=CONCATENATE(A1,A2,A3)”。
当需要连接的单元格或文本数量过多时,使用连接符&和CONCATENATE函数都比较麻烦,此时可以选择PHONETIC函数,语法为“=PHONETIC(A1:A20)”,但此函数无法对纯数据及日文符合进行连接。
3. 转换类函数
转换类函数主要包括数值转文本、文本转数值、货币格式转化、大小写转换。
使用TEXT(数值,格式)函数可以将单元格中的数值转为指定格式,如A1中是“520.21”,使用“=TEXT(A1,"0公斤")”,返回的结果为“520.21公斤”。
使用VALUE(文本)函数可以将数值形式的文本转为数值型,也就是TEXT函数的逆过程。
货币格式转化指的是将数字改为人民币或美元格式,可以选择直接将单元格格式设置为货币或货币符号,也可以通过RMB,DOLLAR函数转为货币格式。
大小写转换就是熟知的UPPER、LOWER函数,可以将字符转为大写或小写,此外,PROPER函数可以实现将首字母转为大写。
4. 判断类函数
字符串的判断大致包括以下场景:
判断一个字符串中是否包含另一个字符串:可以使用FIND(要查找的字符串,被查找的字符串,开始查找位置)或者SEARCH函数来实现,两函数功能相同,但FIND区分大小写,SEARCH不区分大小写。
判断一个字符串中有多少个字符或字节:LEN()判断字符数,LENB()判断字节数。
判断两个字符串是否相同:EXACT(text1,text2)函数,返回值为TRUE或FALSE。
5. 替换类函数
介绍两个替换函数REPLACE(旧字符串,开始位置,要替换的字符个数,新字符串),举例:A1内容为“中国农业银行”,要求将其替换为“中国工商银行”,语法为“=REPLACE(A1,3,2,"工商")”,这样看上去貌似并没有直接使用替换功能好用哈~也可以使用“=SUBSTITUTE(A1,"农业","工商",1)”,其中1表示替换第一次出现的“农业”,可缺省,缺省时默认为1,也可设置为其他数字。
NO.4 信息函数
1. 行列信息函数
ROW函数用于返回一个引用的行号,如在单元格A1中输入“=ROW(C2)”时会返回2,即第2行;输入“=ROW(C3:D6)”时会返回第一行的行号3;输入“=ROW()”时,返回当前单元格所在的行1。此函数一般在进行特殊表格处理的时候能用到,比如隔行进行某些操作。
其复数形式ROWS函数,用于返回引用的行数,如输入“=ROW(C3:D6)”时会返回5,即共有5行数据。与此相对的COLUMN和COLUMNS函数用于返回列号,列数,用法与ROW和ROWS函数相同。
2. 逻辑判断函数
逻辑判断函数用于判断单元格内的数值类型,如是否为空,是否是数字,是否是奇数等,一般可以作为IF函数的一个判断条件(IF函数的第一个参数)使用。如“=IF(ISBLANK(A1),1,0)”,意为判断此单元格是否为空,如果为空返回1,不为空返回0。
常见的逻辑判断函数如下:
NO.5 查询和引用函数
这一部分主要介绍函数中的绝对引用和相对引用,主要会在函数的复制过程中用到。Excel函数对单元格或单元格区域的引用有三种方式:绝对引用、相对引用和混合引用。常见的就是“$”符,像是一把锁,加在字母前表示锁定列,公式移动或复制时引用的列不会变;加在数字前表示锁定行,公式移动或复制时引用的行不会变;字母和数字前都加时表示行列同时锁定,公式移动或复制时引用的行和列都不会变。
1. 绝对引用
绝对引用就是函数引用的单元或单元格区域的行和列前都加了“$”符,行和列都上锁,这样就能让公式在移动或复制时保持引用的地址不变,示例如下,B11单元格中函数为求B4:B10区域的和,使用绝对引用,函数复制到C11,D11单元格时引用地址仍为B4:B10。
2. 相对引用
相对引用就是函数引用的单元或单元格区域的行和列前都不加“$”符,行和列都不上锁,公式在移动或复制时行和列均会发生变化,示例如下,B12单元格中函数为求B4:B10区域的和,使用相对引用,函数复制到C11,D11单元格时引用地址变成了C4:C10和D4:D10。
3. 混合引用
混合引用就是行和列只锁一个,可以是$A1(绝对列相对行),也可以是A$1(相对列绝对行),公式在移动或复制时,绝对引用地址不变,相对引用地址改变。
在单元格中输入公式时,使用F4键可以实现绝对引用、相对引用、混合引用间的快速切换,具体使用哪种引用还需参照个人需求。
NO.6 其他函数
除上述分门别类的介绍一些函数外,还想介绍下常用的一些其他函数,常用到可能都不需要介绍了。
1. 解决数值舍入的函数
一些函数是专门用来对付小数形式的数据的,比如:ROUND(数据,保留小数位数),此函数采用四舍五入的原则进行小数位数的保留;ROUNDDOWN(数据,保留小数位数),舍去指定位数后的小数(向下舍入);ROUNDUP(数据,保留小数位数),舍去指定位数后的小数(向上舍入)。
简单地举个例子:
CEILING函数是另一个向上舍入的函数,使用语法为“=CEILING(数值,位数基数)”,意为将“数值”沿绝对值增大的方向上舍入,使得到的结果等于最接近“位数基数”的倍数,还是举个例子吧。
借此出个小题目,有个短租宾馆收费问题,以周为单位收费,如果客户住了9天,需要按2周进行交费,住了16天需要按照3周来交费,如何将大量的客户入住实际天数换算为周数呢?部分参照答案给你,函数自己试试吧。
FLOOR函数是另一个向下舍入的函数,使用语法为“=FLOOR(数值,位数基数)”,意为将“数值”沿绝对值减小的方向上去尾舍入,使得到的结果等于最接近“位数基数”的倍数,与CEILING函数刚好相反,此处就不举例了。
2. 用于条件计数的函数
一些函数是专门用来计数的,比如:COUNT(n1,n2,...),此函数用来统计包含数值的单元格的数量;COUNTBLANK(n1,n2,...),此函数用来统计指定区域中空白单元格的数量;而COUNTA(n1,n2,...),此函数用来统计指定区域中非空单元格的数量;COUNTIF(n1,n2,...),此函数用来对满足条件的单元格进行计数,语法为“=COUNTIF(区域1,条件1,区域2,条件2,...),注意函数中多个条件为AND关系,需同时满足。这么看来有种SQL中COUNT+CASE WHEN,COUNT(IF(,,))的感觉了。
3. 用于条件求和的函数
一些函数是专门用来求和的,最常见的就是SUM(value1,value2,...),与计数相比,SUM函数只能用于对数值形式的单元格求和。除此之外,SUMIF(条件区域,条件,求和区域)函数可根据某一条件进行求和;SUMIFS(求和区域,条件区域1,条件1,条件区域2,条件2,...)函数可根据多个条件进行求和,另有用来计数和求和的SUMPRODUCT函数和用来分类汇总的SUMTOTAL函数,此处就不举例了,自己操作去吧~
4. IF函数的应用
使用IF函数进行一些数据处理和按规则分类的问题,一般多用于与其它函数结合使用。需要注意的是,虽然IF函数能嵌套使用,以用于分类较多的情况,但其嵌套层数是有限制的,一般最多为7层,此处也不加例子来说明了。
至此,Excel比较基础的函数部分就整理介绍完毕了~ 相当于进行一个存档,之后会陆续更新新的部分,完成Excel的整个合辑,以备不时之需呀!
喜欢的可以扫一扫~