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)  计算所有参数的乘积