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

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

shaowu459

第17个函数：REDUCE函数

REDUCE函数是这一系列里最后一个函数，是一个功能非常强大的循环函数，和Power Quqery里的List.Accumulate很相似。这个函数既可以循环返回单值，也可以将每步循环的结果显示出来，在实际应用中，叠加数组、变换表格样式等场景下应用非常多。思路上能用循环每个元素进行运算然后将结果堆叠起来解决的问题，差不多都可以考虑REDUCE函数。

REDUCE函数的语法如下：


=REDUCE([initial_value], array, lambda(accumulator, value))，第一参数为初始值，可以是单值也可以是数组；第二参数是要循环的数组，也可以只是用来控制循环次数的数组，REDUCE函数循环的次数由第二参数数组的元素数量来决定；第三参数是LAMBDA函数，确定第二参数需要执行的运算，并将运算结果形成下一步运算的初始值。

一般我自己习惯写成：REDUCE(初始值，循环的数组，LAMBDA(x,y,运算公式))这种方式，x代表每次循环时的初始值，每次循环后更新（值不一定更新，可以判断保留上步运算值），y代表循环数组中的每个元素。当然，你也可以使用其他数字分别代表初始值和循环数组的元素，比如说：REDUCE(初始值，循环的数组，LAMBDA(s,c,运算公式))等。但是如果涉及嵌套时，例如LAMBDA的运算公式里再涉及REDUCE函数、MAP函数等时，变量名称就别用同样的了，因为在REDUCE函数的LAMBDA函数体里面，s,c或x,y已经有特定含义了。

下面用一些简单例子来说明REDUCE函数用法：

下面的公式可以求A5:A6单元格区域数据的平方和：

1. =REDUCE(0,A5:C6,LAMBDA(x,y,x+y^2))

复制代码

x的初始值是0，循环A5:A6单元格每个元素时，初始值x+y^2形成第二次循环时的初始值x。

就这个例子来说，目前帮助文件中的写法是不严谨，甚至可以说是错误的，帮助文件中是如此写的：

1. =REDUCE(, A1:C2, LAMBDA(a,b,a+b^2))

复制代码

公式中省略了REDUCE函数的第一参数，之所以正确只是因为第一参数1的平方值仍是1。当REDUCE函数第一参数省略时，REDUCE函数在运算时会将第二参数的第一个值保留下来，不参与LAMBDA函数体里的运算。我们来看下面的说明。

我们将第二参数数组第一个元素改成7，按帮助文件的省略第一参数写法，最终函数返回结果是97：

1. =REDUCE(,A16:C17,LAMBDA(x,y,x+y^2))

复制代码

从右侧的运算过程可以看出，由于省略了第一参数，因此第一次循环时7并没有参与平方运算，而是直接保留了下来，作为下一次循环的x，所以结果是97。

实际上，初始值写0就能得到正常运算结果139：

1. =REDUCE(0,A25:C26,LAMBDA(x,y,x+y^2))

复制代码

第一参数不省略，循环第一个元素7时，7也参与了平方运算生成49，作为下一步循环的初始值x。

还有一个常见的例子是循环将一个字符串中的多个关键字替换成空，该功能可以使用以下函数完成：

1. =REDUCE(A36,A39:A41,LAMBDA(x,y,SUBSTITUTE(x,y,)))

复制代码

REDUCE函数在循环A39:A41单元格每个元素时，都将当前循环的初始值x中的对应关键字用SUBSTITUTE函数替换成空。第一次循环时将“Excel”替换成空，第二次循环时的x已经是将“Excel”替换成空后的字符串了，再继续替换“人”为空。

除了替换成空之外，将替换关键词列表中的关键词替换成其他值：

1. =REDUCE(A46,A49:A51,LAMBDA(x,y,SUBSTITUTE(x,y,VLOOKUP(y,A49:B51,2,))))

复制代码

上图中，A48:B51单元格区域时关键词和需要替换的值列表，A46单元格为初始字符串。在SUBSTITUTE函数的第三参数使用VLOOKUP(y,A49:B51,2,)返回对应B列的内容。

REDUCE函数第二参数为单元格区域时，LAMBDA函数循环元素时会保留单元格属性，可以使用ROW函数等提取行号，但是多层嵌套时可能失去该特性。还有使用INDEX函数等处理后也可能失去该特性：



如果只是&y，则不会有问题：



只是用offset，返回的还是个区域range，则可以使用row等函数：



index提取后，也是变成了普通数组，不是单元格引用了：



只用offset没有问题，y都是单元格引用：



再来看一个逆转字符串的例子：

1. =REDUCE("",MID(A55,SEQUENCE(LEN(A55)),1),LAMBDA(x,y,y&x))

复制代码

用MID函数将A55单元格字符串按每个字符拆开，然后每次循环时都将这个字符放在x的左面连接起来。

下面的公式可以在每个大写字母前面加一个空格：

1. =REDUCE(A61,CHAR(ROW($66:$91)),LAMBDA(x,y,SUBSTITUTE(x,y," "&y)))

复制代码

第二参数生成每个大写字母，然后SUBSTITUTE循环替代，替代时在y前面加上一个空格。例如循环到字母D，则将D替换成“ D”。

REDUCE函数的第二参数，可以只用来控制循环次数，实际不使用对应的值运算：



第二参数一个8个元素，所以REDUCE函数循环8次。如果你想循环足够多的次数还要节省字母，可以用类似=REDUCE(0,1:1,LAMBDA(x,y,x+1))

下面的公式，可以将字符串中连续的A或B最终替换成单个A或B：

1. =REDUCE(A79,K79:K81&{"A","B"},LAMBDA(x,y,SUBSTITUTE(x,y&y,y)))

复制代码

其中第二参数部分K79:K81&{"A","B"}引用K79:K81这样的空单元格区域（足够多以控制足够的次数）形成下面的数组：



REDUCE函数循环这个数字，分别将A替换3次，B替换3次，每次替换时都把两个A替换成一个A，也即将y&y替换成y。

（下楼待续）。