Excel高手都在偷偷学的区间数字技巧,今天一次讲透

这个看似简单的需求，实际上涉及数字序列的分析、连续区间的识别、分组处理以及格式重构等多个步骤。旧的的Excel函数处理起来相当复杂，往往需要借助多个辅助列或VBA才能实现。但如果我们巧妙的运用Excel和WPS表格的新函数组合，只用单条公式就能高效的解决这个问题。

下面让我们一起来学习并体会这个高效的解决方案。

=TEXTSPLIT(A3,,",")

textsplit拆分函数将A3单元格的文本按逗号分行显示，最后返回一个数组。

结果是将"1,3,4,5,9,10,13"变成了{"1";"3";"4";"5";"9";"10";"13"}。

注意数组内元素是文本型数字。

将上一步拆分后的文本数字乘以1：

=TEXTSPLIT(A3,,",")*1

目的是转换为数值。

得到{1;3;4;5;9;10;13}。

定义变量a，存储上面得到的这个数值数组。

TEXTSPLIT(A3,,",")*1

即在let函数中，定义a后：

=LET(a,TEXTSPLIT(A3,,",")*1,可调取a参与计算式)

就可以在后续计算中调取并反复使用a。

=LET(a,TEXTSPLIT(A3,,",")*1,ROWS(a))

ROWS(a)

计算a的行数。

因为在本例中textsplit函数是设置按行拆分的，所以返回的是行数组，所以行数就是数字个数。这里就是7。

=LET(a,TEXTSPLIT(A3,,",")*1,SEQUENCE(ROWS(a)))

SEQUENCE(ROWS(a))

SEQUENCE是一个用于生成自定义序列号的函数。本例中会生成一个从1到数字个数7的序列，即{1;2;3;4;5;6;7}。

=LET(a,TEXTSPLIT(A3,,",")*1,a-SEQUENCE(ROWS(a)))

a-SEQUENCE(ROWS(a))

在let函数中定义变量b，存储这个差值数组：

=LET(a,TEXTSPLIT(A3,,",")*1,b,a-SEQUENCE(ROWS(a)),可调取a与b参与计算式)

现在就可以在let函数中同时使用a和b了。

groupby函数是Excel和WPS表格中的新函数，我们可以这样输入它的第一参数和第二参数：

GROUPBY(b,a,...)

即按b分组，并对每组中的a进行聚合运算。

理论上按b分组后，会得到b的唯一值列表，而其对应的a元素会通过某种（自定义）聚合方式归纳到一起。

那么按照哪种聚合方式对聚合值进行聚合呢？这就考验到了我们对groupby第三参数（聚合函数）的运用了。聚合函数决定按哪种方式聚合，比如是求和啊，还合并啊等等，并且还支持lambda自定义聚合函数。

GROUPBY(b,a,lambda(x,x参与的计算式))

GROUPBY(b,a,lambda(x,@x))

GROUPBY(b,a,lambda(x,IF(ROWS(x)=1,,-MAX(x))))

IF(ROWS(x)=1,,-MAX(x))

这个是IF条件判断：

如果x只有一行，即该分组只有一个数字时，则返回空，否则返回负号加上最大值。注意这里有个负号，因为后面我们会用连接符作为区间的间隔符，所以实际上是“-最大值”。

GROUPBY(b,a,LAMBDA(x,@x&IF(ROWS(x)=1,,-MAX(x))))

@x&IF(ROWS(x)=1,,-MAX(x))

将每个分组的最小值与加上“-最大值”连接起来。

注意，当分组只有一个数字时，IF返回空，所以连接后就是该数字本身；当有多个数字时，连接成“最小值-最大值”。

=LET(a,TEXTSPLIT(A3,,",")*1,b,a-SEQUENCE(ROWS(a)),GROUPBY(b,a,LAMBDA(x,@x&IF(ROWS(x)=1,,-MAX(x))),0,0))

这里groupby函数有两个0参数（第4参数与第5参数），分别代表无标头行和无总计行。然后，groupby函数会返回一个两列的数组，第一列是分组（b的唯一值），第二列是每个分组下所属a的聚合结果。

drop函数用于删除数组的列：

=LET(a,TEXTSPLIT(A3,,",")*1,b,a-SEQUENCE(ROWS(a)),DROP(GROUPBY(b,a,LAMBDA(x,@x&IF(ROWS(x)=1,,-MAX(x))),0,0),,1))

DROP(...,,1)

这里第3参数“1”表示删除第一列（即分组b列），只保留第二列的聚合结果。

=LET(a,TEXTSPLIT(A3,,",")*1,b,a-SEQUENCE(ROWS(a)),ARRAYTOTEXT(DROP(GROUPBY(b,a,LAMBDA(x,@x&IF(ROWS(x)=1,,-MAX(x))),0,0),,1)))

ARRAYTOTEXT(...)

用arraytotext函数将数组转换为逗号分隔的文本，得到最终结果：

1, 3-5, 9-10, 13

(如果您还有其它方面的问题，可后台消息框回复“提问”进行咨询)