Excel数据处理必备技能 1 --- 函数

函数是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的整个合辑，以备不时之需呀！

喜欢的可以扫一扫~