Excel小众函数详解，包括各种奇形怪状的冷门函数（excel 经典函数）

 

本文主要介绍一些小众函数，包括各种奇形怪状的函数

let,switch,search,upper,lower,unique,xmatch,getpivotdata,formulatext,isformula,fieldvalue,areas,address,valuetotext,value,t,rmb,rept,numbervalue,even,odd,exact,arraytotext,sequence,sqrt,power,pi,abs,fact,exp,ln,log,type,sign,product

=LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...])

LET英文让的意思，函数会向计算结果分配名称，这样就可存储中间计算、值或定义公式中的名称。

但是这些名称仅可在 LET函数范围内使用。

这个函数与编程中的变量类似，LET 是通过 Excel 的本机公式语法实现的

若要在 Excel 中使用 LET 函数，需定义名称/关联值对，再定义一个使用所有这些项的计算。

必须至少定义一个名称/值对（变量），LET 最多支持 126 个对。

例：=LET(x,5,x+1) 让变量x等于5，返回变量x+1的值

第一个参数是要分配的第一个变量名称，必须以字母开头，不能是公式的输出，也不能与范围语法冲突。本例用x

第二个参数是第一个变量名称的值，本例赋值为5，这个参数可以用公式进行动态引用

第三个参数是计算公式或者第二个变量，如果第三个参数作为最后一个参数，则是计算公式，本例返回的是计算公式x+1 = 5+1

第三个参数当然还可以作为第二个变量的名称，如下：我们定义第二个变量名称y，并赋值为2，最后一个参数是计算公式，即x+y = 5+2

问题1：根据总成绩返回分数评级（成绩大于等于700为非常优秀，成绩大于等于600为优秀，成绩大于等于500为普通，成绩小于500为一般）

这个问题有一般方法是加个辅助列，用VLOOKUP先把每人的成绩匹配出来，然后再用IFS判断，这里面如果不用辅助列的话，把VLOOKUP公式作为参数写里面会很累赘

=IFS(XLOOKUP(L2,A:A,I:I)>=700,"非常优秀",XLOOKUP(L2,A:A,I:I)>=600,"优秀

",XLOOKUP(L2,A:A,I:I)>=500,"普通",XLOOKUP(L2,A:A,I:I)<500,"一般")

这时我们可以用LET函数：此时相当于我们把总成绩的查询结果赋值给变量a，然后用逻辑函数对变量a的值进行判断

=LET(a,XLOOKUP(L2,A:A,I:I),IFS(a>=700,"非常优秀",a=600,"优秀",a>=500,"普通",a<500,"一般"))

SWITCH(表达式, value1, result1, [default 或 value2, result2],…[default 或 value3, result3])

switch英文是转换的意思，SWITCH 函数根据值列表计算一个值（称为表达式），并返回与第一个匹配值对应的结果。 如果不匹配，则可能返回可选默认值

这个函数和CHOOSE函数类似，CHOOSE是按序号进行返回对应的值，这个是按匹配返回对应的值

第一个参数是要进行比较的值，也可以是要转换的值，比如把日期转为星期的格式，再进行匹配

第二个参数是条件值，如果第一个参数的值等于第二个参数，则返第三个参数

第三个参数是返回值

还可以进行多条件判断，如果查不到，我们还可以加一个如果没有匹配到的返回值，如果不指定此项没有匹配结果时，则会返回#N/A值

问题2：通过籍贯进行匹配返回，如果是陕西省、山东省返回北方，如果是湖南省返回南方：

=SWITCH(XLOOKUP(L2,A:A,D:D),"陕西省","北方","湖南省","南方","山东省","北方")

SEARCH(find_text,within_text,[start_num])search是查找的意思，这个函数与FIND函数类似

第一个参数是要查找的文本

第二个参数是要在其中搜索第一个参数的值的文本

第三个参数是从第几个字符开始查找

注意：SEARCH函数不区分大小写。如果要执行区分大小写的搜索，FIND函数区分大小写

可以在第一个参数中使用通配符问号 (?) 和星号 (*) ，问号匹配任意单个字符；星号匹配任意一串字符。 如果要查找实际的问号或星号，请在字符前键入波形符 (~)

如果找不到 find_text 值，则返回#VALUE！ 错误值。

如果省略了 start_num 参数，则假设其值为 1。

总结起来一句话：SEARCH函数功能与FIND函数一样，不同在于SEARCH支持通配符查找并且不区分大小写，FIND函数不支持通配符且区分大小写

问题3：查找技能名称里第三个字是剑字的技能：一个问号代表任一一个字符

=SEARCH("??剑?",E2)

UPPER(参数文本)将文本转换为大写字母

LOWER(参数文本)将文本转换为小写字母

PROPER(参数文本),proper单词有适当的意思，就是将文本字符串的首字母以及文字中任何非字母字符之后的任何其他字母转换成大写，将其余字母转换为小写。

TRANSPOSE(array)：transpose英文是转置的意思，也就是行列互换位置：相当于复制粘贴里面选择性粘贴的转置：

=TRANSPOSE(A1:D2)

问题4：将姓名分数评级区域进行转置显示：

=TRANSPOSE(L1:M6)

UNIQUE函数返回列表或范围中的一系列唯一值，unique英文单词是独一无二的意思：

问题5：返回部门里面的唯一值列表，也就是说相同的门派只返回第一个出现的值：

=UNIQUE(H2:H20)

=XMATCH (lookup_value、lookup_array、[match_mode]、[search_mode]) xmatch函数是match函数的升级版，类似xlookup相比于vlookup函数一样

前三个参数和match函数一样

只是多了一个第四个参数[search_mode]

指定搜索类型：

1 - 在默认搜索 (搜索)

-1 - 搜索倒序搜索 (搜索) 。

2 - 执行依赖于按升lookup_array排序的二进制 搜索 。 如果未排序，将返回无效结果。

2 - 执行依赖于 lookup_array 按降序排序的二进制搜索。 如果未排序，将返回无效结果。

问题6：倒序查找姓名序号，也就是从最下面往上查找人名，然后返回第一个查到的姓名的位置：

如下：=XMATCH(M2,A:A,0,-1)

GETPIVOTDATA，get是获取，pivot是枢的意思，我们可以把pivot理解为是透视表的意思就行，data是数据，该函数返回数据透视表中的可见数据，例;我们将成绩表进行透视，取部门、籍贯、总成绩三个字段，在E1单元格直接等于用鼠标点总计那个单元格,得到如下公式;

=GETPIVOTDATA("总成绩",$A$1)

第一个参数是要取值的数据区域，第二个参数按参数指南上的意思应该是透视表名称，不过我们发现这个参数都是透视表的最左上角单元格绝对引用，

如果想看华山派的总成绩，则将A列华山派进行折叠，后等于C6单元格即可

=GETPIVOTDATA("总成绩",$A$1,"部门","华山派")

此时公式参数，多了1个筛选条件，即部门

如果再往下钻：选陕西省

=GETPIVOTDATA("总成绩",$A$1,"部门","华山派","籍贯","陕西省")

注意这个公式不能下拉，如果想在右边引用一列相同数据，可以选择复制粘贴纯数值的格式或者用单元格引用的形式：如下在E1单元格输入 =C1向下即可

FORMULATEXT(reference) formula是公式的意思，此函数以字符串的形式返回公式

=FORMULATEXT(M2)

这个函数可以理解为选择公式功能 - 显示公式的结果

此时M单元格都变成公式文本格式显示了

=ISFORMULA(M2)判断是否为公式，返回逻辑值

可以使用FIELDVALUE函数从股票或地理位置数据类型等链接数据类型中检索字段数据。

=AREAS((L1:M6,M1:M6))返回参数里面的区域个数（相连部分视为一个区域)

ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])根据指定行号和列号获得工作表中的某个单元格的地址=ADDRESS(1,1)

=ADDRESS(1,1,1,1,"花名册")

valuetotext()函数可以将其他格式的值转为文本格式，如下：

=VALUE(O2) 将表示数字的文本字符串转换为数字,与上面的函数用法正好相反

T()函数表示如果是文本值，则返回值，如果不是文本值，则返回为空(注意虽然返回值为空，但是该单元格并不是空，用ISBLANK函数可以判断里面是有内容的）

=RMB(L2,2)将数字格式转为带货币符号的文本

REPT(text, number_times)rept是单词repeat的缩写，是重复的意思，函如其名表示将文本重复一定次数，使用REPT 来在单元格中填充文本字符串的大量实例

第一个参数是要重复的文本

第二个参数Number_times表示用于指定文本重复次数的正数

例：输入=REPT("我喜欢你",ROW())，下拉单元格会形成一个向下的三角形

NUMBERVALUE(Text, [Decimal_separator], [Group_separator ])

将以文本格式显示的数值返回为以数值格式，这个函数主要可以解决带千分位的数值文本，例如跨境电商里面亚马逊欧洲站数据导出后的原始文本，

第一个参数是要转换为数字的文本。

第二个参数是小数分隔符Decimal_separator可选。 用于分隔结果的整数和小数部分的字符。

这个函数会将第一个文本参数中的空格（即使位于参数中间）也将被忽略。

例如，“ 3 000 ”将返回3000

第三个参建Group_separator是用于分隔数字分组的字符，例如千分位，百万分位符号

=NUMBERVALUE(A2,",")

可以看到除了第三个数字无法转换，其他的都完成，第三个需要先把不是负号的横杠替换掉，再进行转换

或者这个问题，可以全部用文本替换的形式，利用power query里面的功能，进行三次替换，最后再转为数字格式，这种方法相对比较方便，一劳永逸，不用每次都要进行好几次替换

EXACT函数，判断两个文本是否相等，这个函数直接用=号就可以实现

ARRAYTOTEXT 函数返回任意指定区域内的文本值的数组，也就是将一个矩阵区域转为一个单元格的数组

ARRAYTOTEXT(array, [format])

第一个参数是待转换区域，

第二个参数是指定格式：

0 默认。 易于阅读的简明格式。 返回的文本将与应用了常规格式的单元格中呈现的文本相同。

1 包含转义字符和行定界符的严格格式。 生成一条可在输入编辑栏时被解析的字符串， 将返回的字符串（布尔值、数字和错误除外）封装在引号中。

例=ARRAYTOTEXT(成绩单,1)，将整个成绩表转换到一个单元格里的数组格式

=PI()圆周率函数，返回pai的值

=SQRT(25)平方根函数，sqrt是单词square root的缩写

=POWER(2,10)幂函数，power英文有幂、乘方的意思

可以用power函数进行求多次方根：例：求1024的十次方根：

=POWER(1024,1/10)

=FACT(5)阶乘函数

=EXP(1)返回自然对数函数的底数e的N次方

=LN(A1)以自然对数底数e的对数

TYPE(value)返回数值的类型

1代表数值

2代表文本

4代表逻辑值

16代表误差值

64代表数组

128代表复合数据

=SIGN(A1)返回参数的正负值，正数返回1，负数返回-1，0返回0

=LOG(4,2)返回对数函数的值（以2为底数，求4的对数）第二个参数忽略则默认为10

=PRODUCT(A1:A5)计算所有参数的乘积