wps/excel函数技巧:reduce双循环解决数据分组问题

如图，A列为源数据，如何将数据进行分组，要求每组不多于100。公式如下：

=DROP(REDUCE(SEQUENCE(1,10,0,0),A2:A6,LAMBDA(x,y,

#以10个0的一列为初始数据，y值对A列每一个数据进行引用

VSTACK(x,DROP(REDUCE(0,B1:K1,LAMBDA(m,n,

#对10个小组进行一组一组的调用，这里也直接可以使用数列

LET(

i,SUM(CHOOSECOLS(x,XMATCH(n,B1:K1))), #对外部循环的累积器按列调用并求和

j,SUM(m), #调用内循环的累积器并求和

h,IFS(i>=100,"",(i<=100)*(j

#分三个条件确定向累加器追加的数值，如果这一列数字大于100，空值；小于100，且 所在行的和小于所在列的和，则取100-i,y-j两个数的最小值；其它情况，空值。

HSTACK(m,h)))),,1)))),1) #横向堆变量n形成一行

公式思路：本公式最有难度的部分是累积器的追加与调用，因为数据是随着函数变化的，所以再书写过程中，一处错误就可能导致整个公式出错。对于复杂的公式很难进行调试，这里我有两条建议哈：

1、多使用变量，尽量不要一次成型，这样很难一部分一部分的找错误；

2、多使用iferror,也就是对错误值进行修正，不要将错误带到下一个环节中导致整体报错。其实这个公式是经过简化的，简化以前是这个样子滴：

=DROP(REDUCE(SEQUENCE(1,10,0,0),A2:A6,LAMBDA(x,y,VSTACK(x,DROP(REDUCE("",B1:K1,LAMBDA(m,n,LET(i,IFERROR(SUM(CHOOSECOLS(x,XMATCH(n,B1:K1))),0),j,SUM(TAKE(m,-1)),h,IFS(i>=100,"",(i<=100)*(j<=y),MIN(100-i,y-j),1,""),r,IFERROR(HSTACK(m,IFERROR(h,0)),0),r))),,1)))),1)

里面使用了很多iferror,也使用了很多变量，甚至是最终的表达式都先写成r变量，然后再调用r,这样的好处是可以方便的对每个变量进行调用，找出错误和需要改进的地方，越是复杂的公式越有用哟。

本例的核心是三个条件的使用，领会以后也可以写出拖拉式的半自动公式，大家可以试试哈。