认识SEQUENCE函数
引言:一直想要用到最新的Office,想要试一下Excel中新的动态数组函数,但因种种原因,最终没有用上Office 365。然而,这次偶然的机会,竟然让我装上了Office 2021。太让人兴奋了,迫不及待地小试了一下,与大家分享。
这篇文章介绍SEQUENCE函数。SEQUENCE函数是新的动态数组函数中的一个,与其他函数结合使用时可以产生出人意料的结果!除了它通常的用途外,当将SEQUENCE函数和各种日期函数的组合时,可以创建动态日历。此外,它提供的转换能力令人惊讶!让我们先看看其基本语法。
SEQUENCE函数的语法为:
=SEQUENCE(rows,[columns],[start],[step])
其中,
rows:行数。
columns:可选的,列数。
start:可选的,序列的起始值,默认值是1。
step:可选的,序列的步长,默认值是1。
在工作表中输入公式:
=SEQUENCE(10)
其中,参数10定义了行数,默认起始值为1,步长为1,因此生成的结果如下图1所示。
图1
第一个参数定义了行数,也可以同时使用第二个参数添加列,例如公式:
=SEQUENCE(10,3)
生成的结果如下图2所示。
图2
注意,到序列填充的顺序是从左至右、由上至下。如果想首先填充列,则使用TRANSPOSE函数:
=TRANSPOSE(SEQUENCE(10,3))
结果如下图3所示。
图3
日期函数和SEQUENCE函数
我们可以使用SEQUENCE函数和日期函数轻松地生成日期序列,可以得到日、月、季度或年的序列。
例如,获得从今天开始10天的日期序列,使用公式:
=SEQUENCE(10,,TODAY())
结果如下图4所示。
图4
可以设置单元格格式,从而将数字序列显示为日期。
如果我们要创建显示每个月第一天的一系列日期。例如,12个月内每个月的第一天,使用公式:
=DATE(2021,SEQUENCE(12),1)
结果如下图5所示(已将单元格格式设置为日期格式)。
图5
如果想要一个类似的序列,但在今天之前的几个月内,使用公式:
=DATE(2021,SEQUENCE(1,MONTH(TODAY())),1)
结果如下图6所示(已将单元格格式设置为日期格式)。
图6
与TEXT函数组合使用,可以只显示月份:
=TEXT(DATE(2021,SEQUENCE(1,MONTH(TODAY())),1),'mmmm')
结果如下图7所示。
图7
值得一提的是,使用TODAY函数使公式具有了动态性,因此它会随着时间的推移而扩展。当十月来临的时候,将会显示十月!
另一个非常有用的技巧是对表中的值排序。让我们首先生成一个5 x 5的表,其中包含100到10000之间的随机值。使用公式:
=RANDARRAY(5,5,100,10000,TRUE)
得到如下图8所示的随机数表。
图8
现在,可以使用SEQUENCE函数对这些数值进行升序或降序排列。首先,创建长度与数值数相等的序列,示例中是25。如下图9所示。
图9
接着,添加LARGE函数:
=LARGE(B2#,SEQUENCE(COUNT(B2#)))
结果如下图10所示。
图10
再看下一个示例。
如下图11所示,原数据都在一列中,我们可以使用SEQUENCE函数将其快速转换成行列排列的表。
图11
使用公式:
=INDEX(A2:A16,SEQUENCE(5,3))
得到的效果如下图12所示。
图12
下面的示例展示将SEQUENCE函数与数据有效性结合的效果。
如下图13所示,在单元格B2中使用数据有效性定义可能的输入值。
图13
在另一个单元格中输入公式:
=SEQUENCE(B2)
使显示的数值随单元格B2中的选择而自动调整,如下图14所示。
图14
如果想要得到一个动态数据表,例如单元格B3中指定的行,6列,可使用公式:
=SEQUENCE(B2,6)
结果如下图15所示。
图15
神奇的动态数组函数!
。