小伙伴们好啊,今天再和大家分享几个Excel 2021版本中的常用公式,点滴积累,也能提高效率。
1、自动增减的序号
如下图,在A2单元格输入以下公式,可以生成随着数据增加而变化的序号。
=SEQUENCE(COUNTA(B:B)-1)
COUNTA(B:B)-1部分,计算B列非空单元格的个数。减去1,得到不包含标题行在内的实际记录数。
SEQUENCE函数用于生成指定行列的序列号。本例中,生成序号的行数由COUNTA(B:B)-1的结果来指定。也就是B列有多少行数据,SEQUENCE函数就生成对应行数的序号。
2、提取销售总和大于200的记录
如下图,希望从左侧的数据表中,提取出1~4月总销量大于200的记录。
G2单元格输入以下公式:
=FILTER(A2:E8,MMULT(B2:E8,1^ROW(1:4))>200)
MMULT(B2:E8,1^ROW(1:4))部分,先计算出每一行的总和,结果为:
{234;202;247;172;248;125;117}
接下来再使用FILTER函数,根据MMULT计算出的结果,在
A2:E8单元格区域中
提取出大于200的记录。
3、提取最高销量大于70的记录
如下图所示,希望从左侧数据表中,提取出销售最大值大于70的全部记录。
G2单元格输入以下公式:
=FILTER(A2:E8,SUBTOTAL(4,OFFSET(B1:E1,ROW(1:7),))>70)
OFFSET函数以B1:E1单元格区域为基点,依次向下偏移1~7行,得到B2:E2、B3:E3……B8:E8单元格区域的多维引用。
再使用 SUBTOTAL函数,计算出每一行的最大值,结果为:
{57;79;79;64;79;42;75}
接下来再使用FILTER函数,根据SUBTOTAL计算出的结果,在A2:E8单元格区域中提取出大于70的记录。
好了,今天的内容就是这些吧,祝各位一天好心情~~
图文制作:祝洪忠