365最新版函数基本用法介绍（一楼更新视频下载链接）

shaowu459

关于TEXTSPLIT还有一个常见的小问题，如下图，字符串先按找分号拆分，然后用TAKE取出第一个值来，再按-拆分，并没有返回包含ABC三个元素的数组：




用公式审核工具会注意到最外层TEXTSPLIT拆分的是一个数组，而拆分数组只能得到第一个分隔符之前的内容：



解决方法让其变成单值即可，在使用其他函数时，如果觉得逻辑上合理，但为什么不按期望的返回，可以考虑下这个原因。

把INDEX后两个参数写全，提取第一行第一列的值：



如果是文本，也可以用CONCAT聚合一下，如果是数字，可以用SUM等函数聚合一下变成单值：

cinlo

shaowu459 发表于 2022-5-27 20:54
我觉得365函数优点就在于通用性强，逻辑清晰，可阅读性强啊

是的，新函数就是简洁明了，可读性、可维护性强。还有很重要的一点：效率超高！
老函数要实现同样的功能，会拐N个弯，老是把头绕得晕乎乎的，容易头秃！

shaowu459

第15个函数：MAP函数。

用官方文件中的说法，MAP函数可以对参数数组中的每个元素映射到一个新值，生成一个新的数组。该函数语法如下：

MAP(数组1,数组2,数组3……,lambda(x,y,z……)，lambda之前的参数可以是一个也可以是多个，如果映射多个数组的话，数组大小应该一样，否则会返回错误值或返回值中包含错误。其实简单理解，可以认为MAP函数循环对数组中的每个值进行在特定的运算，返回一个单值，每个返回的单值形成一个新的数组。下面用示例来说明MAP函数基本用法。LAMBDA里面的x、y、z等分别对应数组1、数组2、数组3中的每个元素。

下面的公式使用MAP函数对A1:C3这个数组中的每个元素进行平方运算，x就代表A1:C3中的每个元素，运算完毕后的值重新组成3行3列的数组，可以里成运算后的平方数替代了原位置的数据。以A1的1为例，经过平方运算后返回值为1，生成新数组最左上角的元素1；以A2的2为例，经过平方运算后返回值为4，生成新数组第1行第2列的元素，后面以此类推。

1. =MAP(A1:C3,LAMBDA(x,x^2))

复制代码

MAP函数对每个数组元素映射完毕后生成的值只能是单值，不能是数组，否则会出现错误。例如，希望循环A5:A7每个单元格，并且取每个单元格及右侧两个单元格形成一个3行数组，会返回错误：



如果用其他函数将数组聚合一下返回单值，是可以的。例如用TEXTJOIN函数将每行的3个元素合并在一起：



通过上面的一个例子可以看出，MAP函数引用的数组如果是单元格区域，那么在循环的时候，每个元素还是一个单元格区域，也就是可以用于SUMIF函数、OFFSET函数等函数的ref或range，ROW（循环的元素）也可以返回单元格所在的行。这个特性也是经常被使用的一个特性。下面举例来说明：

以下公式能筛选出A列对应的每个名称B列最小值所在的行（MAP函数和FILTER函数也是好搭档，经常合作）：

1. =FILTER(A14:B25,MAP(B14:B25,LAMBDA(x,MIN(FILTER(B14:B25,A14:A25=OFFSET(x,,-1)))=x)))

复制代码

MAP函数第一参数数组是B14:B25单元格区域，因此MAP函数将循环对区域中的每个元素进行运算和判断。以B19单元格为例，当循环到B19单元格时：



MAP函数映射运算完毕后，返回TRUE和FALSE的数组，TRUE即B列数值是A列名称对应值中的最小值。最后用FILTER函数筛选出结果为TRUE的即可。

MAP函数映射的数组可以是多个，例如要筛选出“名称”=A并且数值>5的数据，可以用以下公式：

1. =FILTER(A29:B39,MAP(A29:A39,B29:B39,LAMBDA(x,y,AND(x="A",y>5))))

复制代码

上面的公式中x代表A29:A39的每个元素，y代表B29:B39的每个元素。以第一行为例，循环的值为A29和B29，AND(x="A",y>5)也即AND(A29="A",B29>5)，结果返回TRUE，后面以此类推。映射完成和返回一个TRUE和FALSE的数组，最后用FILTER函数筛选TRUE对应行即可。

以下公式可以求A41:E44每个单元格及其右侧两个单元格的合计值：

1. =MAP(A41:E44,LAMBDA(x,SUM(OFFSET(x,,,,3))))

复制代码

循环的第一个元素是A41单元格，LAMBDA里的x代表A41,OFFSET(x,,,,3)也即OFFSET(A41,,,,3)=A41:C41单元格区域，然后用SUM函数求和返回一个单值48。后面的值以此类推。


以下公式实现的功能是将左侧表格中每行的最大值替换成max，最小值替换成min，其余所有值都显示mid。

1. =VSTACK(A53:D53,MAP(A54:D56,LAMBDA(x,LET(t,INDEX(A54:D56,ROW(x)-53,),IFS(x=MIN(t),"min",x=MAX(t),"max",1,"mid")))))

复制代码

以B54单元为例，当循环到B54单元格时，ROW(B54)-53返回1，INDEX(A54:D56,ROW(x)-53,)返回A54:D54的数组，用LET函数定义为t方便后续调用。IFS(x=MIN(t),"min",x=MAX(t),"max",1,"mid")这部分分别判断B54单元格的值是否等于t数组中的最小值或最大值，如果是则分别返回max或min，如果都不是则返回mid。

下面的公式先用UNIQUE函数提取出A60:A70单元格区域的不重复值，然后MAP函数针对每个不重复值去用SUMIF函数求对应数值合计，最后用HSTACK函数将不重复值和映射生成的数组合并在一起。

1. =LET(s,UNIQUE(A60:A70),HSTACK(s,MAP(UNIQUE(A60:A70),LAMBDA(x,SUMIF(A60:A70,x,B60:B70)))))

复制代码

下图是根据论坛一个实例改编，要求返回每个客户最近3次（从下往上数，包含当前行）的购物金额合计。

1. =MAP(B75:B102,LAMBDA(x,SUM(TAKE(FILTER(C75:x,B75:x=x),-3))))

复制代码

简单归纳一下，MAP函数就是对参数数组中的每个元素进行循环操作，MAP函数参数中的每个值，还可以用于其他函数，例如VLOOKUP等，通过查询返回值重新组成数组。如果MAP函数引用参数数组是单元格区域，在LAMBDA函数运算时每个元素x还带着单元格属性，可以用ROW函数返回行号、COLUMN函数返回列号、可用于OFFSET函数偏移引用等。

tchh666

老师。我看这两段公式结果都一样。会有区别吗？？

shaowu459

第16个函数：SCAN函数

SCAN函数也是一个循环的函数，在循环过程中可以把每步生成的结果存储下来，类似Power Query中的List.Generate函数。函数语法如下：

=SCAN ([initial_value], array, lambda(accumulator, value))，第一参数为初始值，可以是个数组或者单值，在LAMBDA里对应x（我个人习惯用x和y）；第二参数为要循环的数组，这个参数中的每个元素对应LAMBDA函数里的y，这个数组中的值可以不被LAMBDA计算使用，可以只用于控制循环次数，也即第二参数有多少个元素，SCAN函数就循环多少次；第三参数是LAMBDA(x,y，计算公式)，其中x和y分别对应第一和第二参数，但计算公式中可以使用x、y，也可以不使用。LAMBDA函数计算出来的值会作为新的x参与下次运算。

下面用一些简单的示例来说明SCAN函数的基本用法。

下面的公式可以求A2:A7单元格从上到下的累计数。

1. =SCAN(0,A2:A7,LAMBDA(x,y,x+y))

复制代码

SCAN函数的初始值设置为0，因为A2:A7单元格是6个元素，因此SCAN函数循环6次。

循环第1次的时候，初始值x=0，y=1（A2单元格的值），LAMBDA函数计算x+y=0+1=1，因此C2单元格返回1，作为下次循环的x。
循环第2次的时候，初始值x=1，y=2（A3单元格的值），LAMBDA函数计算x+y=1+2=3，因此C3单元格返回3，作为下次循环的x。
循环第3次的时候，初始值x=3，y=3（A4单元格的值），LAMBDA函数计算x+y=3+3=6，因此C4单元格返回6，作为下次循环的x。
以此类推。

如果省略SCAN函数的第一参数，那么第二参数的第一个元素不会参与LAMBDA函数里的运算，直接留下来作为最终数组的第一个元素：

因为省略了第一参数，因此第二参数的2直接留了下来：



这个例子更明显一些，如果循环时LAMBDA函数对第一个参数2进行运算，应该返回(1+2)^2=9，但实际仍然返回了2，也即将第二参数第一个元素直接保留了下来，没有参加任何运算。




SCAN函数的第二参数也可以是多行多列数组，循环的时候是按先行后列来执行的，第二参数可以是空的，数组元素数量只控制循环次数，运算可以不引用第二参数：



上面的图中，初始值为0，每循环一次x+1，和第二参数y没关系，第二参数只控制循环8次。另外，观察生成的结果结果1、2、3……7、8可以看出，SCAN函数对第二参数循环时按先行后列的方式进行的。

下图左侧是家庭信息统计表，每家户主在第一行，要求给家庭从上到下编号：

1. =SCAN(0,A26:A37,LAMBDA(x,y,x+(y="户主")))

复制代码

SCAN初始值设置定为0，循环A26:A37的值，第一次循环到A26时，A26是户主，所以x+(y="户主")=0+TRUE=1，循环到A27时，是这个家庭的成员，不是户主，因此x+(y="户主")=1+FALSE=1，也即这个家庭的编号都是1。直到A29单元格再次是“户主”，x会变成2，后面以此类推。

上面的例子也是论坛的实例简化改变，生成了家庭编号，就可以进一步筛选某个家庭的全部成员、合并一个家庭全部成员的姓名到一个单元格等等。

下面的公式将A42:A56单元格区域中的空白单元格填充为上面单元格的内容：

1. =SCAN(0,A42:A56,LAMBDA(x,y,IF(y="",x,y)))

复制代码

核心思路是：当y是空单元格时就取SCAN函数存储的x值，y不是空单元格时，就将x的值更新成当前循环的y。由于第一个循环的元素肯定不是空单元格，因此SCAN函数第一参数设置成什么都无所谓，也可以省略。



省略第一参数的情况常用于保留列标题，因为列标题无需参与任何运算，所以可以直接保留下来，不用最后用VSTAK函数来拼接了。

下图B列有一些单价是空的，要求从右侧单价表查找值进行填充。

1. =SCAN(,A61:B66,LAMBDA(x,y,IF(y="",VLOOKUP(x,E61:F66,2,),y)))

复制代码

因为SCAN函数循环第二参数的时候是按先行后列，所以循环到空单元格之前必然循环到空单元格左侧的“型号”，因此，LAMBDA函数里使用当y不是空时，就将值存储下来当成新x，如果是空的时候，就去价格表里使用VLOOKUP函数存储的x，这时x必然是空单元格左侧的型号。

SCAN函数第二参数是单元格区域时，循环每个元素时，每个元素仍然保留单元格的属性，包含行列信息，是个ref或range。



举例说明上面题目的意思，1和8差值是7，1和8所在单元格存在4个间隔，因此7/4=1.75，要求1下面是1+1.75=2.75，再下面填充2.75+1.75=4.5，以此类推，均匀增加至8。LAMBDA部分计算时，充分使用y是单元格引用的特性，构造出单元格区域判断当前空行距离下面的数值有几行，然后使用OFFSET函数以y为基准偏移引用。

从上面的一些例子可以看出，SCAN函数每次计算完毕返回的都是单值，但也可以通过一些文本合并或用整数+数字/10^4这种方式存储两个或多个信息，例如：

1. =SCAN("1-1",ROW(1:9),LAMBDA(x,y,LET(s,TEXTBEFORE(x,"-")*2,s&"-"&LEN(s))))

复制代码

上面的例子中，初始值x是“1-1”，代表数字1的长度是1，然后第二参数为9个元素，因此循环9次。每次循环时提取x字符串横岗左面的数字，然后做乘以2的运算，运算完毕后再将数字和数字的长度连接起来形成新的x字符串。例如，第一次循环时，提取初始值x横杠左面的1，然后乘以2得到2，使用LEN函数得到长度是1，最后将2&“-”&LEN函数产生的1连接起来形成字符串"2-1"作为新的x。后面以此类推，也即一个字符串可以存储两个信息。使用数字的就不举例了。

最后说一下，SCAN函数的第一参数初始值x可以是一个数字，但是在运算的时候要聚合一下，例如下面的例子中初始值x是{1,0,0}，LAMBDA函数运算时用SUM函数将初始值x求和得到一个单值结果，然后再乘以y仍然得到单值结果返回，公式正常运行。

1. =SCAN({1,0,0},ROW(1:9),LAMBDA(x,y,SUM(x)*y))

复制代码

shaowu459

tchh666 发表于 2022-6-1 23:01
老师。我看这两段公式结果都一样。会有区别吗？？

没有区别。我应该是先正常写的公式，写UNIQUE函数获得不重复名称直接当做MAP参数和HSTACK，最后整理的时候才用LET定义的，改了一个，忘记改后面了。

SCAN函数再补充一个例子吧，填充多列合并单元格的，和一列的其实是一样的：

1. =TRANSPOSE(SCAN(,TRANSPOSE(A1:D25),LAMBDA(x,y,IF(y="",x,y))))

复制代码

如果依赖单元格：

lgcmeli

tchh666 发表于 2022-6-1 23:01
老师。我看这两段公式结果都一样。会有区别吗？？

可否化简，=LET(u,UNIQUE(A8:A19),
d,SUMIF(A8:A19,u,B8:B19),
HSTACK(u,d))

shaowu459

lgcmeli 发表于 2022-6-2 13:29
可否化简，=LET(u,UNIQUE(A8:A19),
d,SUMIF(A8:A19,u,B8:B19),
HSTACK(u,d))

可以。这个例子是用来说MAP函数使用的，用于介绍可以用其他函数生成一个数组，然后用MAP去逐个循环这个数组元素。简化后就没有MAP函数了，可以作为HSTACK函数的例子

kuangben8

超人老师的函数分层写法，感觉就像CAD里的LISP语言编程一样。感谢分享。

tchh666

又捣鼓出来个公式，。差点写晕了。。

1. =LET(B,B8:B241,C,C8:C241,D,D8:D241,E,E8:E241,U,UNIQUE(B8:C241),S,SORTBY(U,SUMIFS(E,B,TAKE(U,,1),C,TAKE(U,,-1)),-1),_T1,TAKE(S,,1),_T2,TAKE(S,,-1),HSTACK(S,MAP(_T1,_T2,LAMBDA(X,Y,LOOKUP(1,0/(B=X)/(C=Y),D))),SUMIFS(E,B,_T1,C,_T2)))
   

复制代码

原求助贴地址 ：https://club.excelhome.net/forum ... ;page=1#pid10996757