Excel函数

False代表0

自定义万元单位

0!,0,
0!,0,万元

Exact比较两字符串是否相同

用途：Exact函数用于检测两个字符串是否完全相同。

语法：Exact(text1,text2)

注意：不区分文本型和数值型数字

vlookup多条件查询

VLOOKUP(F6&G6,IF({1,0},C:C&D:D,E:E),2,0)：是将A:A&B:B和C:C和成两列数据。第一列是复合数据列A:A&B:B。第二列是查询结果列F:F

注意：所有使用了数组的公式，不能直接回车，需要使用Ctrl+Shift+Enter，否则会出错。

 IF 和 Choose能呈现相同结果

HYPERLINK超链接

=HYPERLINK(link_location, friendly_name)

# link_location 链接
# friendly_name 命名

IF函数（AND/OR）

#=IF(判断条件, 真值, 假值)
#=IF(AND(条件1, 条件2), 真值, 假值)  两个条件都满足返回真值
=if(AND(B2>=80,C2>=23),200,0)

#=IF(OR(条件1, 条件2), 真值, 假值)   只要满足一个条件就返回真值
=if(OR(B2>=80,C2>=23),200,0)

IF函数嵌套

=IF(B2>=90,"优秀",IF(B2>=80,"良好",IF(B2>=60,"及格","不及格")))

COUNTIF 单条件计数

# =COUNTIF(区域, 条件)  可接收array和range
=COUNTIF(A1:A3,">=10")

根据日期填充序号

=COUNTIF($B$2:B2,B2)   # F4绝对引用

COUNTIFS 多条件计数

# =COUNTIFS(区域1, 条件1, [区域2, 条件2], ...)
=COUNTIFS(E:E,">2000",B:B,"绿康")

SUMIF 单条件求和

# =SUMIF(条件判断区域, 判断条件, 求和区域)
=SUMIF(B:B,"绿康",E:E)

SUMPRODUCT数组对位相乘，并返回乘积之和

SUMPRODUCT(array1, [array2], [array3], ...)

=SUMPRODUCT(A21:A24,B21:B24)  # 同行相乘，结果相加

 取整

=INT(A2)

 ROUND四舍五入

=ROUND(A28,0)  # 取一位
=ROUND(A28,1)  # 取二位

 RANK求该数值在此列排名第几

RANK(number,ref,[order])

• Number     必需。 要找到其排位的数字。

• Ref     必需。 数字列表的数组，对数字列表的引用。 Ref 中的非数字值会被忽略。

• Order     可选。 一个指定数字排位方式的数字。0为降序、1位升序

=RANK(F2,F:F,0)

替换文本字符串

replace 指定替换某个位置字符串

格式：replace（文本，开始字符，字符长度，替换的新字符串）参数二、三是数值格式

substitute 替换指定字符

格式：substitute（文本，字符串，要替换的新字符串，替换第几个字符串）参数4：可选

len+substitute求人数

实际字符数：=LEN(F2)

去除符号的字符数： =LEN(SUBSTITUTE(F2,"，",""))+1  #当最后字符无符号需+1

实际人数: =LEN(F2)-LEN(SUBSTITUTE(F2,"，",""))+1

office365新增函数

一、Unique函数。

功能：从一个指定的范围或数组中返回唯一值。

语法结构：=Unique(单元格区域或数组,[比较方式],[返回范围])。

解读：

1、参数“比较方式”共有2个值，分别为TRUE或FALSE，当此值为TRUE时，则比较列彼此并返回唯一列；当为FALSE或省略时，将行彼此比较并返回唯一行。

2、参数“返回范围”共有2个值，分别为TRUE或FALSE，当此值为TRUE时，则返回指定数据范围或数组中恰好发生一次的所有非重复列或行；当为FALSE或省略时，将返回指定数据范围或数组中所有不同的行或列。

应用案例：

1、目的：返回“学历”列中的唯一值。

方法：

在目标单元格中输入公式：=UNIQUE(F3:F12)。

解读：

此用法是Unique函数的最基础的用法，也是最常用的功能。省略了后面2个参数，默认按照“行”区域和所有不同行的非重复值提取。

2、目的：返回仅出现一次的学历。

方法：

在目标单元格中输入公式：=UNIQUE(F3:F12,,1)。

解读：

比较示例1和示例2的结果，发现公式只是多了一个参数，但其结果却相差很大，所以在一定要清楚当Unique函数的第3个参数为TRUE时，返回的值时指定区域中仅出现1次的唯一值。

二、Filter函数。

功能：筛选区域或数组。

语法结构：=Filter(返回结果的数组或单元格区域,筛选条件,[所包含数组中的所有值都为空时的返回值])。

应用案例：

1、单条件查询。

目的：快速筛选本部门的员工姓名。

方法：

在单元格中输入公式：=FILTER(C3:C12,B3:B12=K3,"")。

2、多条件查询。

目的：根据部门和员工的婚姻情况查询对应的月薪情况。

 方法：

在目标单元格中输入公式：=FILTER(H3:H12,(B3:B12=K3)*(F3:F12=L3),"")。

3、逆向查询（单条件）。

目的：查询已婚或未婚的人员姓名。

 方法：在目标单元格中输入公式：=FILTER(C3:C12,F3:F12=K3,"")。

4、逆向查询（多条件）。

目的：查询符合指定婚姻和学历的人员姓名，如果没有符合条件的值，返回“无相关人员信息”。

 方法：

在目标单元格中输入公式：=FILTER(C3:C12,(F3:F12=K3)*(G3:G12=L3),"无相关人员信息")。

5、返回符合条件的所有列。

目的：查询已婚或未婚人员的所有信息。

 方法：

在目标单元格中输入公式：=FILTER(B3:H12,F3:F12=K3,"")。

6、条件计数。

目的：计算本部门中学历的种类。

方法：

在目标单元格中输入公式：=COUNTA(UNIQUE(FILTER(G3:G12,B3:B12=K3,"")))。

解读：

如果要直接计算部门的个数，相信大家现在也有了相应的思路。以上图为例，公式为：=COUNTA(UNIQUE(B3:B12))。

7、提取符合条件的不重复值。

目的：提取本部门中员工的学历种类。

方法：

在目标单元格中输入公式：=UNIQUE(FILTER(G3:G12,B3:B12=K3,""))。

8、动态提取相应字段。

目的：提取部门中的员工姓名。

 方法：

在目标单元格中输入公式：=FILTER(C3:C15,B3:B15=K3,"")。

解读：

动态扩展数据时只需修改相应的条件即可。

三、Sort函数。

功能：对范围或数组进行排序。

语法结构：=Sort(数组或数据区域,[主要关键字的行数或列数],[排序模式],[排序方式])。

解读：

1、Sort的参数共有4个，其中必备参数只有1个。就是数组或数据区域。

2、第2个参数“主要关键字的行数或列数”就是排序以数据范围中此行或此列为主要关键字进行排序。

3、第3个参数“排序模式”有2个值，分别为：1或-1，即按升序或降序排序，默认情况下为按升序排序。

4、第4个参数“排序方式”有2个值，分别为：TRUE或FALSE，TRUE表示按列排序，FALSE表示按行排序，默认情况下为FALSE。

应用案例：

1、以“月薪”为主要关键字进行降序排序。

 

 方法：

在目标单元格中输入公式：=SORT(B3:H12,7,-1,0)。

2、以“月薪”为主要关键字进行升序排序。

 方法：

在目标单元格中输入公式：=SORT(B3:H12,7,1,0)。

3、以“月薪”为主要关键字降序排序，“年龄”为次要关键字升序排序。

 方法：

在目标单元格中输入公式：=SORT(B3:H12,{7,3},{-1,1})。

解读：

从上例中可以看出，Sort函数的参数用数组也是可以执行的，意思就是以第7位为主要关键字降序排序，以第3列为次要关键字升序排序。

三、XLOOKUP

功能：在指定的范围或数组中搜索匹配项，并通过第二个范围或数组返回相应的项。

语法结构：=Xlookup(查询值,查询值范围,返回值范围,[未查询到值时的返回值],[匹配模式],[搜索模式])。

1、逆向/正向查询。

目的：根据“员工姓名”查询员工所属“部门”。

=XLOOKUP(L3,D3:D12,B3:B12)。

2、多列查询。

目的：根据员工姓名返回“年龄”、“性别”、“婚姻”、“学历”、“月薪”等列。

=XLOOKUP(L3,D3:D12,E3:I12)
=XLOOKUP(L3,D3:D12,E3:I12,"无此记录")  # 如果查询不到对应的值，则返回“无此记录”。

3、模糊查询。

目的：根据员工“月薪”和“扣缴比例”查询员工的扣缴比例。

=XLOOKUP(I3,L$3:L$8,M$3:M$8,,-1)

4、多条件查询。

目的：根据“部门”和“职位”查询对应的员工。

=XLOOKUP(M3&N3,B3:B12&C3:C12,E3:E12)

5、查询最新结果

目的：查询“产品”的最新“批发价”。

=XLOOKUP(I3,B3:B12,C3:C12,,,-1)

解读：

结合数据表，要查询“产品”的最新“批发价”，其实就是从数据表的最后一行逆向向第一行查询，结合Xlookup的特点，只需将第6个参数的值设置为-1即可。

四、WRAPROWS将一行或一列数据转换为多列数据

语法结构：=WRAPROWS（数据区域，转换列数，数据不足要填充的值）

五、WRAPCOLS将一行或者一列数据转换为多行数据

语法结构：=WRAPCOLS（数据区域，转换行数，数据不足要填充的值）

一行转多行

一列转多行

六、VSTACK数据垂直汇总

功能：按垂直顺序追加数组，相当于数据汇总

语法：=VSTACK（数组1，数组2，数组3）

与之类似的还有HSTACK，它是根据行进行数据追加

 七、TOCOL多列数据转换为一列数据

功能：将多列数据转换为一列数据，只需设置一个参数即可

语法：TOCOL（数组，[要忽略的数据类型，扫描模式一般省略即可]）

八、TOROW多行数据转换为一行数据

语法：TOROW（数组）

九、TOCOL多列数据转换为一列数据

十、矩阵转一行

=OFFSET($A1,(COLUMN(A:A)-1)/5,MOD(COLUMN(A:A)-1,5),)

十一、TEXTSPLIT字符串拆分为行或列

语法：=TEXTSPLIT（文本，列分隔符，行分隔符）

拆分到列

=TEXTSPLIT(A2,",")

 拆分到行（第二个参数用逗号占位）

=TEXTSPLIT(A2,,",")

十二、SUBTOTAL筛选后的数据填充序号

语法:SUBTOTAL（函数，引用函数的区域）

=SUBTOTAL(103,$B$5:B5)

十三、计算两个日期之间相差月数DATEDIF

=DATEDIF（大日期，小日期，“m”）

计算身份证周岁年龄

=DATEDIF(TEXT(MID(身份证单元格,7,8),"0000-00-00"),TODAY(),"y")

十四、取小数mod

MOD函数，为返回两数相除的余数，除数输入1，即可取出小数部分，具体操作：在D4单元格输入公式：= MOD(B4,1)，然后选中D4单元，双击右下角填充柄向下填充公式，演示如下图：

十五、复制到word或者PPT中并样式不变

不要直接CTRL+C然后CTRL+V——因为字号、宽度等样式会改变。

选中表格，鼠标移动到边框外，鼠标出现+，按住Ctrl键将表格直接拖动到word中即可。

十六、复制到word或者PPT中同时可以同步修改

选中表格CTRL+C，然后在word中按CTRL+ALT+V进行选择性粘贴，在对话框中选择“粘贴链接”与“Microsoft Excel工作表对象即可。

十七、快速填充序号

纯数字序列：ctrl+拖拉

文本+数字 ：直接拖拉

十八、矩阵填充序号

填充序列

十九、矩阵文本数字转换为数值

二十、拆分单元格多行内容

二十一、合并多行内容到一个单元格中