Excel公式技巧：根据不同区域大小生成相应数字

excelperfect

如下图1所示，单元格区域F3:I3命名为“x”，有4个数据；单元格区域D2:E2命名为“y”，有2个数据；单元格区域“K1:L1”命名为“z”，有2个数据。

图1

现在，想要使用公式生成数组{1,2,3,4,1,2,1,2}或者{1,1,1,1,2,2,3,3}，如何实现？

1.可以使用数组公式：

=COLUMN(A1:INDEX(1:1,,COUNTA(x,y,z)))-COUNTA(x)*(COLUMN(A1:INDEX(1:1,,COUNTA(x,y,z)))>COUNTA(x))-COUNTA(y)*(COLUMN(A1:INDEX(1:1,,COUNTA(x,y,z)))>COUNTA(x,y))

生成数组：

{1,2,3,4,1,2,1,2}

该公式的解析过程如下：

={1,2,3,4,5,6,7,8}-4×({1,2,3,4,5,6,7,8}>4)-2×({1,2,3,4,5,6,7,8}>6)

={1,2,3,4,5,6,7,8}-4×({FALSE,FALSE,FALSE,FALSE,TRUE,TRUE,TRUE,TRUE})-2×({FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,TRUE,TRUE})

={1,2,3,4,5,6,7,8}-{0,0,0,0,4,4,4,4}-{0,0,0,0,0,0,2,2}

={1,2,3,4,1,2,1,2}

2.可以使用数组公式：

=1+(COLUMN(A1:INDEX(1:1,,COUNTA(x,y,z)))>COUNTA(x))+(COLUMN(A1:INDEX(1:1,,COUNTA(x,y,z)))>COUNTA(x,y))

生成数组：

{1,1,1,1,2,2,3,3}

该公式的解析过程如下：

=1+{1,2,3,4,5,6,7,8}>4+{1,2,3,4,5,6,7,8}>6

=1+{FALSE,FALSE,FALSE,FALSE,TRUE,TRUE,TRUE,TRUE}+{FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,TRUE,TRUE}

={1,1,1,1,2,2,3,3}

这样，在找到各个不同区域相应的数字后，可以用在INDEX函数中，来获取相应的数值。

仔细想一想，获取这些数字似乎是有一定的规律的，可以依此类推。