目录一. LET函数1.1 简介1.2 基本用法1.3 实战1.3.1 案例11.3.2 案例2二. LAMBDA函数2.1 简介2.2 基本用法2.3 共享自定义公式给其他用户2.4 实战2.4.1 LAMBDA 配合 LET 使用2.4.2 字符串处理三. MAP函数3.1 简介3.2 基本用法3.3 MAP 和 BYROW 的区别四. FILTER4.1 简介4.2 根据多条件进行筛选4.3 实战4.3.1 FILTER 配合 LET 使用4.3.2 模糊包含4.3.3 过滤出5的倍数一. LET函数1.1 简介在 LET 出现之前Excel 很多复杂公式都会重复计算很难读很难维护性能差而 LET 可以像编程语言一样定义变量给变量命名重复使用中间结果让公式更清晰当有下面的情况发生的时候应该考虑使用LET一个表达式重复出现公式太长看不懂想提高性能有多个中间步骤LET 的基本语法LET(变量名1,值1,变量名2,值2,...最终返回值)1.2 基本用法定义变量使用let之前A1B1被计算了2次(A1B1)*2(A1B1)*3使用let之后A1B1的计算结果被放到了x变量中A1B1只需要计算1次就可以了。LET(x,A1B1,x*2x*3)中间结果调试下面是想要得到的效果最后想要获取y的值LET(x,A1A2,y,x*2,y)可以先将终将变量x先输出得到中间定义变量的值便于debugLET(x,A1A2,y,x*2,x)1.3 实战1.3.1 案例1假设A1单元格中有如下内容现在要求对该内容进行如下操作拆分去重排序再拼接apple,banana,apple,orange,banana不使用LET的话可读性较差TEXTJOIN(,,TRUE,SORT(UNIQUE(TEXTSPLIT(A1,,))))使用了LET之后可读性更高LET(arr,TEXTSPLIT(A1,,),uniqueArr,UNIQUE(arr),sortedArr,SORT(uniqueArr),TEXTJOIN(,,TRUE,sortedArr))1.3.2 案例2LET(name,TEXTBEFORE(A2:A3,,),scores,--TEXTSPLIT(TEXTAFTER(A2:A3,,),,),total,BYROW(scores,LAMBDA(r,SUM(r))),avg,BYROW(scores,LAMBDA(r,AVERAGE(r))),id,SEQUENCE(ROWS(name)),VSTACK({序号,姓名,总分,平均分},HSTACK(id,name,total,avg)))二. LAMBDA函数2.1 简介基本语法结构LAMBDA(参数1,参数2,...,计算公式)LAMBDA相当于其他编程语言中的匿名函数核心作用是在 Excel 中自定义函数。需要注意的是LAMBDA本身本身不会执行必须要被调用之后才会执行。只是定义了一个LAMBDA函数并没有调用显示计算错误定义了LAMBDA函数并且调用LAMBDA函数支持多个参数2.2 基本用法LAMBDA函数的最重要的点就是把复杂公式封装起来LAMBDA(score,IF(score90,A,IF(score80,B,IF(score60,C,D))))【公式】选项卡找到【名称管理器】然后就将自定义的LAMBDA函数粘贴到【引用位置】处最后再给LAMBDA函数取一个名字就完成了自定义函数的构建然后就可以使用自定义函数了2.3 共享自定义公式给其他用户使用LAMBDA函数自定义的公式只会在当前Excel中起作用。如果同时定义了很多自定义函数其他人想要使用很多自定义函数的话就要手动一个个导入很耗费时间。解决办法新建一个Excel文件然后通过【名称管理器】定义若干个自定义函数将Excel文件另存为.xlam格式将该文件共享给其他用户其他用户将.xlam格式的文件存放到C:\Users\用户名\AppData\Roaming\Microsoft\AddIns文件夹中然后如下图所示通过Excel的【加载项】找到对应的.xlam文件名然后激活即可激活之后当前用户的所有的Excel就都可以使用自定义的公式了2.4 实战2.4.1 LAMBDA 配合 LET 使用语法规则LAMBDA(参数,LET(变量1,值1,变量2,值2,...最终返回值))根据成绩计算该成绩对应的等级// ▶版本1LAMBDA(score,LET(grade,CHOOSE(1(score60)(score80)(score90),D,C,B,A),grade))// ▶版本2LAMBDA(score,LET(isA,score90,isB,score80,isC,score60,grade,CHOOSE(1isCisBisA,D,C,B,A),grade))计算添加消费税和折扣之后的商品价格的函数LAMBDA(price,tax,LET(subtotal,price*tax,discount,subtotal*0.9,ROUND(discount,2)))2.4.2 字符串处理假设text的内容为banana, apple,apple,orange被LAMBDA处理之后就会变为apple,banana,orangeLAMBDA(text,LET(arr,TEXTSPLIT(text,,),clean,TRIM(arr),uniqueArr,UNIQUE(clean),sortedArr,SORT(uniqueArr),TEXTJOIN(,,TRUE,sortedArr)))三. MAP函数3.1 简介MAP函数是Excel新增的动态数组函数之一主要用来对数组中的每个元素执行一次自定义计算和下面语言的map用法有点像JavaScript 的Array.map()Python 的map()Java Stream 的mapPowerShell 的ForEach-ObjectExcel的MAP函数通常会搭配下面的函数一起使用LAMBDALETBYROWBYCOL基本语法array1 第一个数组array2 第二个数组可选LAMBDA 对每个元素执行的逻辑a,b对应数组中的当前元素MAP(array1,[array2],...,LAMBDA(a,b,...,计算逻辑))3.2 基本用法A1到B5单元格区域内的每个单元格的数据都乘2MAP(A1:B5,LAMBDA(x,x*2))MAP还支持多个数组一起处理两个数组相加MAP(A1:A5,B1:B5,LAMBDA(a,b,ab))3.3 MAP 和 BYROW 的区别MAP是逐元素处理会对所指定区域内的每一个单元格都进行处理。MAP(A1:C3,LAMBDA(x,x*2))BYROW逐行处理会对指定区域内的每一整行都进行处理。BYROW(A1:C3,LAMBDA(r,SUM(r)))四. FILTER4.1 简介FILTER函数的作用是根据条件从数据区域中筛选出符合条件的数据。array要筛选的数据区域include筛选条件TRUE/FALSE[if_empty]没有结果时返回什么FILTER(array,include,[if_empty])筛选出分数 90 的人FILTER(A2:B5,B2:B590)4.2 根据多条件进行筛选*代表 和分数 90 且 姓名是李四FILTER(A2:B5,(B2:B590)*(A2:A5李四))代表 或分数 90 或 分数 60FILTER(A2:B5,(B2:B590)(B2:B560))当没有符合条件的数据时会报错因此一般建议添加第三个参数4.3 实战4.3.1FILTER配合LET使用FILTER配合LET使用可以提高公式的可读性避免重复计算性能更好LET(data,A2:B5,score,B2:B5,FILTER(data,score90))4.3.2 模糊包含查找包含東的数据SEARCH找位置ISNUMBER判断是否找到LET(data,A1:A4,FILTER(data,ISNUMBER(SEARCH(東,data))))4.3.3 过滤出5的倍数LET(data,A1:A6,FILTER(data,MOD(data,5)0))