EXCEL 2016常用知识--Excel函数

必备常用函数教学，包括逻辑函数、查找函数、文本函数、数学函数等…

1.Excel计算的两种方式

        Excel计算的两种方式：

        公式：一些运算符和数值组成的数学表达式

        函数：是Excel内部设置好的运算模块

        Excel中的公式： =1+2 =2*2 =（1+2）/3*4

        Excel的函数： 如：sum、average、count……

        实际工作中，公式和函数是一回事

2.Excel的运算符

比较运算符	含义	示例
=(等号)	等于	A1=B1
>（大于号）	大于	A1>B1
<（小于号）	小于	A1<B1
>=（大于等于号）	大于或等于	A1>=B1
<=（小于等于号）	小于或等于	A1<=B1
<>（不等号）	不等于	A1<>B1

文本运算符	含义	示例
&（与号）	将两个值连接（或串联）起来产生一个连续的文本值	“q”&“数据” 的结果为 “q数据"

3.运算顺序

公式运算符
-	负号
%	百分比
^	乘幂
* /	乘和除
+ -	加和减
&	连接符号
= <>	等于和不等于
> <	大于和小于
>= <=	大于等于和小于等于

运算符优先顺序
原则：	先乘除后加减
改变顺序：	嵌套加小括号
	嵌套中最里层小括号优先计算

运算符优先顺序
优先级	符号	运算符
1	乘幂	^
2	乘除	*  /
3	加减	+ -
4	连接符	&
5	比较运算符	= < >

4.Excel 公式与函数的书写规范

        Excel函数是不区分大小写，在结束公式编辑时，Excel会自动转换成大写；

        每个参数都要用逗号分隔； 参数中的文本要用引号引起来；

        数字前面有比较运算符也是要用双引号；

        输入英文状态下的标点符号，不能输入中文状态下的标点符号；

        函数的括号输入要成对，如果只有一对括号，可以不输入右括号。

5.Excel之逻辑函数

        一、如果该条件成立，则是真值，即结果为TRUE；   如果该条件不成立，则是假值，即结果为FALSE。

         二、在Excel四则运算与乘幂、开发运算中：        

逻辑值	数值
TRUE	1
FALSE	0

        三、在逻辑判断中：

逻辑值	数值
TRUE	所有非0数值
FALSE	0

IF函数

        IF函数有三个参数，公式如下：

                         =IF(条件判断, 结果为真返回值, 结果为假返回值)

        If嵌套函数 If函数的顺序：从左到右依次判断 判断标准要么都是大于或者都是小于

AND  OR  NOT 函数

        AND(logical1,logical2, ...) Logical1, logical2, ... 表示判断条件为真或者假，值则为 TRUE 或 FALSE。

        OR(logical1,logical2,...) OR函数的意思是指两个以上条件只要具备一个即可。

        NOT(logical) Logical 为一个可以计算出 TRUE 或 FALSE 的逻辑值或逻辑表达式。

        如果逻辑值为 FALSE，函数 NOT 返回 TRUE；如果逻辑值为 TRUE，函数 NOT 返回 FALSE。

6.Excel之统计函数

Count系列函数

函数	含义
Count	计算参数中包含数字的个数
Counta	计算参数中包含非空值的个数
countblank	计算区域中空白单元格的个数
Countif	对指定区域中符合指定条件的单元格计数
countifs	对指定区域中满足多个条件的单元格计数

SUM系列函数

函数	含义
SUM	计算区域中数字、逻辑值及数字的文本表达式之和
SUMIF	对区域内符合指定条件的值求和
SUMIFS	对区域内符合指定的多个条件的值求和

AVERAGE函数系列

函数	含义
Average	计算平均值函数
Averagea	数值的平均值(包括数字、文本和逻辑值)
averageif	某个区域内满足给定条件的所有单元格的平均值（算术平均值）
averageifs	多重条件所有单元格的平均值

MEDIAN 和 MODE 函数

        MEDIAN 函数：

        返回给定数值的中值。中值是在一组数值中居于中间的数值。 如果参数集合中包含偶数个数字，函数 MEDIAN 将返回位于中间的两个数的平均值。

        MODE函数：

        返回在某一数组或数据区域中出现频率最多的数值

RANK函数

        rank函数是排名函数。rank函数最常用的是求某一个数值在某一区域内的排名。 rank函数语法形式：rank(number,ref,[order])

        函数名后面的参数中 number 为需要求排名的那个数值或者单元格名称（单元格内必须为数字），ref 为排名的参照数值区域，order的为0和1，默认不用输入，得到的就是从大到小的排名

比较：RANK/RANKEQ和RANKAVG的区别

LARGE和SMALL函数

        LARGE函数用于返回数据集中的第K个最大值。

        语法：large(array,k) 参数：array为需要找到第K个最大值的数组或数字型数据区域。 K为返回的数据在数组或数据区域里的位置（从大到小）

        SMALL函数用于返回数据集中的第K个最小值。

7.Excel之查找函数

VLOOKUP函数

该函数的语法规则如下：

                        VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

参数	简单说明	输入数据类型
lookup_value	要查找的值	数值、引用或文本字符串
table_array	要查找的区域	数据表区域
col_index_num	返回数据在查找区域的第几列数	正整数
range_lookup	模糊匹配/精确匹配	TRUE（或不填）/FALSE

HLOOKUP函数

该函数的语法规则如下：

                HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

lookup_value	要查找的值	数值、引用或文本字符串
table_array	要查找的区域	数据表区域
row_index_num	返回数据在区域的第几行数	正整数
range_lookup	模糊匹配/精确匹配	TRUE /FALSE（或不填）

MATCH函数

        MATCH函数含义：返回指定数值在指定数组区域中的位置 语法：MATCH(lookup_value, lookup_array, match_type) lookup_value：需要在数据表（lookup_array）中查找的值。可以为数值（数字、文本或逻辑值）或对数字、文本或逻辑值的单元格引用。可以包含通配符、星号 (*) 和问号 (?)。星号可以匹配任何字符序列；问号可以匹配单个字符。 lookup_array：可能包含有所要查找数值的连续的单元格区域，区域必须是某一行或某一列，即必须为一维数据，引用的查找区域是一维数组。 match_type：表示查询的指定方式，用数字-1、0或者1表示，match_type省略相当于match_type为1的情况。 为1时，查找小于或等于lookup_value的最大数值在lookup_array中的位置，lookup_array必须按升序排列： 为0时，查找等于lookup_value的第一个数值，lookup_array按任意顺序排列： 为-1时，查找大于或等于lookup_value的最小数值在lookup_array中的位置，lookup_array必须按降序排列。利用MATCH函数查找功能时，当查找条件存在时，MATCH函数结果为具体位置（数值），否则显示#N/A错误。 注：当所查找对象在指定区域未发现匹配对象时将报错！

index函数

        index函数的公式格式是=index(array,row_num,column_num) ，

         其中array表示我们要引用的区域， row_num表示要引用的行数，column_num表示要引用的列数， 最终的结果就是引用出区域内行列交叉处的内容。

LOOKUP函数

        函数 LOOKUP 有两种语法形式：向量和数组。

         LOOKUP函数的语法如下：

                 LOOKUP（lookup_value，lookup_vector，result_vector）

                 LOOKUP（lookup_value，array）

        函数 LOOKUP 的向量形式是在单行区域或单列区域（向量）中查找数值，然后返回第二个单行区域或单列区域中相同位置的数值； 函数 LOOKUP 的数组形式在数组的第一行或第一列查找指定的数值，然后返回数组的最后一行或最后一列中相同位置的数值。

注：向量形式LOOKUP_vector 的数值必须按升序排序，否则，函数 LOOKUP 不能返回正确的结果。        

          数组形式:array的数值首例必须按升序排列，且行数要大于列数。

8.Excel之引用函数

INDIRECT函数

        indirect函数的语法格式

                                         =INDIRECT(ref_text,[a1])

[a1]可以省略， Indirect返回的结果是通过一个文本值所指定的引用

9.Excel之数学函数

ROUND函数

        Round函数返回一个数值，该数值是按照指定的小数位数进行四舍五入运算的结果。 ROUND函数的公式 round(number,digits) number,要四舍五入的数，digits是要小数点后保留的位数    

MOD函数    

        mod函数是一个用来求余数函数，返回两数相除的余数。mod函数在Excel中一般不单独使用，经常和其他函数组合起来使用。

                mod函数的公式

                                 =mod(number,divisor) =mod（被除数，除数）

RAND和RANDBETWEEN函数

        Exce函数之随机函数RAND用于产生0~1之间的随机数

        RAND公式： Rand（） RANDBETWEEN（bottom,top） Bottom参数：

        RANDBETWEEN将返回的最小整数。 Top参数： RANDBETWEEN将返回的最大整数。

        RAND和RANDBETWEEN,每次计算工作表时都将返回一个新的数值。

CONVERT函数

CONVERT函数公式：

        CONVERT(number,from_unit,to_unit)

        number： 以 from_units 为单位的需要进行转换的数值。

         from_unit：数值 number 的单位。

        to_unit：为结果的单位。

10.Excel之日期函数

TODAY和NOW函数

        Today函数表示返回当前日期。 公式=today（）。

        NOW函数，用于返回电脑设置的当前日期和时间的序列号。

        NOW语法：NOW()该函数不需要参数 公式：=NOW()

提取日期和时间函数

        公式=Year()

        公式=month()

        公式=day()

        公式=hour()

        公式=minute()

        公式=second()

        Date函数

        Time函数

WEEKDAY函数

        WEEKDAY函数是Excel函数。 返回值为一个数字，表示星期几。

        常使用WEEKDAY函数计算某天为星期几。

        公式:WEEKDAY(serial_number，return_type)

        serial_number 是要返回日期数的日期

        return_type为确定返回值类型的数字

DATEDIF函数

        DATEDIF函数是一个excel中隐藏的、但功能非常强大的日期函数，主要用于计算两个日期之间的天数、月数或年数。

                DATEDIF(start_date,end_date,unit)

                参数1：start_date，表示起始日期

                参数2：end_date，表示结束日期

                参数3：unit为所需信息的返回时间单位代码。

          各代码含义如下：

                 "y"返回时间段中的整年数

                "m”返回时间段中的整月数

                 "d"返回时间段中的天数

                "md”参数1和2的天数之差，

                忽略年和月 "ym“参数1和2的月数之差，

                忽略年和日 "yd”参数1和2的天数之差,  忽略年。按照月、日计算天数

11.Excel之文本函数

FIND函数

        FIND(find_text,within_text,start_num)

        Find_text 是要查找的字符串。

         Within_text 是包含要查找关键字的单元格。就是说要在这个单元格内查找关键字

         Start_num 指定开始进行查找的字符数。

         如果 within_text 中没有 find_text，则 FIND返回错误值 #VALUE!。 FINE函数是区分大小写

SEARCH函数

        search函数的公式

        =search（find_text，within_text，start_num）

        =search（要查找的字符，字符所在的文本，从第几个字符开始查找）

        第三个参数可以省略（默认从第一个开始查找）。

        Search函数是不区分大小写。

TRIM函数和CLEAN函数

        Trim() 函数：去掉首尾空格

        Clean()函数：删除其中含有的当前操作系统无法打印的字符。

LEFT和RIGHT函数

        left函数公式： LEFT(text, num_chars)

        其中text表示我们要截取的那个内容num_chars表示截取的字符的数量

        right函数语法： right(text,num_chars)

        其中text表示要截取的字符串 num_chars表示截取的字符的数量

MID函数

        MID字符串函数，作用是从一个字符串中截取出指定数量的字符

         MID函数公式： MID(text, start_num, num_chars)

        text被截取的字符 start_num从左起第几位开始截取（用数字表达）

         num_chars从左起向右截取的长度是多少（用数字表达）

LEN函数

        Len函数公式： Len(string | varname)

        LEN函数是一种函数，功能是返回文本串的字符数。

CONCATENATE函数

        CONCATENATE函数公式： CONCATENATE(text1, [text2], ...)

        Text1必需要连接的第一个文本项。

         Text2, ...可选。其他文本项，最多为 255 项。项与项之间必须用逗号隔开。 注释您也可以使用连接符号 (&) 计算运算符代替CONCATENATE函数来连接文本项。

TEXT函数

        text函数公式 =text(value,format_text）Value 为数字值。

        Format_text 为设置单元格格式中自己所要选用的文本格式。

12.Excel之信息函数

IS系列函数

        is函数是一个逻辑函数，可以用来判断一些特定的内容

         Istext判断单元格是否是文本

         Isnumber判断单元格是否为数值

         Istext和isnumber的判断的结果相反

IFERROR函数

        在excel中使用iferror函数可以用来判断某些内容的正确与否，正确则返回正确结果，错误则返回需要显示的信息 IFERROR函数语法： IFERROR(value, value_if_error) 表示判断value的正确性，如果value正确则返回正确结果，否则返回value_if_error。 其中value的错误格式有#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME?、#NULL等。