Excel公式练习100:转换非连续单元格为一维单元格区域

excelperfect

引言:本文的练习整理自chandoo.org。多一些练习,想想自己会怎么解决这个问题,看看别人又是怎样解决的,这样能够快速提高Excel公式编写水平。

本次的练习是:将非连续的单元格转换为一个一维单元格区域。

练习设置:

将字母x,1输入单元格区域F7:G7中,并将该区域命名为“x”。

将字母y,2输入单元格区域J6:K6中,并将该区域命名为“y”。

将字母z,3输入单元格区域L18:M18中,并将该区域命名为“z”。

示例数据如下图1所示。

图1

现在,要求编写一个公式,将这三个独立的引用转换为一维数组,即{“x”,1,“y”,2,“z”,3},然后将其输入到工作表的一行六列中。

不能使用辅助单元格,也不能使用VBA。

请写下你的公式。

解决方案

下面列出一系列公式,有兴趣的朋友可以按照前面文章给的方法逐个研究。

公式1数组公式。

=INDEX((x,y,z),1,{1,2,1,2,1,2},{1,1,2,2,3,3})

公式2数组公式。

=INDEX((x,y,z),1,IFERROR(1/MOD(COLUMN(OFFSET(A1,,,,COUNTA(x,y,z))),2),2),(MOD(COLUMN(OFFSET(A1,,,,COUNTA(x,y,z))),2)+COLUMN(OFFSET(A1,,,,COUNTA(x,y,z))))/2)

其中,IFERROR(1/MOD(COLUMN(OFFSET(A1,,,,COUNTA(x,y,z))),2),2)

生成数组:{1,2,1,2,1,2}

(MOD(COLUMN(OFFSET(A1,,,,COUNTA(x,y,z))),2)+COLUMN(OFFSET(A1,,,,COUNTA(x,y,z))))/2

生成数组:{1,1,2,2,3,3}

公式3数组公式。

=CELL('contents',INDIRECT('R'&SUBSTITUTE(TEXT(SMALL(CHOOSE({1;2;3},ROW(x)+COLUMN(x)%, ROW(y)+COLUMN(y)%,ROW(z)+COLUMN(z)%),COLUMN(OFFSET(A$1,,,,COUNTA((x,y,z))))), '0.00'),'.', 'C'),FALSE))

上述公式适合于3个区域都占2列的情形。然而,如果区域的长度不同呢。例如,下图2所示的数据,要转换成一维数组:{“x1”,”x2”,”x3”,”x4”,”y1”,”y2”,”z1”,”z2”}。

图2

实现公式如下。

公式4数组公式。

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

公式5数组公式。

=INDEX((x,y,z),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+(COLUMN(A1:INDEX(1:1,,COUNTA(x,y,z)))>COUNTA(x))+(COLUMN(A1:INDEX(1:1,,COUNTA(x,y,z)))>COUNTA(x,y)))

下面的公式可以生成一个一维数组,并能够被其它公式应用:

公式1

OFFSET(A$1,ROUND(MOD(SMALL(IFERROR(CHOOSE({1;2;3},1+(ROW(x)*COLUMN(x)^0)%,2+(ROW(y)*COLUMN(y)^0)%,3+(ROW(z)*COLUMN(z)^0)%),FALSE),COLUMN(OFFSET(A$1,,,,COUNTA((x,y,z))))),1)*100,0)-1,ROUND(MOD(SMALL(IFERROR(CHOOSE({1;2;3},1+COLUMN(x)%,2+COLUMN(y)%,3+COLUMN(z)%),FALSE),COLUMN(OFFSET(A$1,,,,COUNTA((x,y,z))))),1)*100,0)-1)

公式2

=INDIRECT(ADDRESS(MOD(SMALL(IFERROR(CHOOSE({1;2;3},COLUMN(x),COLUMN(y),COLUMN(z))^0*CHOOSE({1;2;3},ROW(x),ROW(y),ROW(z))+(2^14*{0;1;2}),''),ROW(OFFSET(A1,,,COUNTA(x,y,z)))),2^14),MOD(SMALL(IFERROR(CHOOSE({1;2;3},COLUMN(x),COLUMN(y),COLUMN(z))+(2^14*{0;1;2}),''),ROW(OFFSET(A1,,,COUNTA(x,y,z)))),2^14),1,1,))

还有吗?

……

太厉害了!把这些公式的运作原理搞清楚,何愁水平不进步!

欢迎在下面留言,完善本文内容,让更多的人学到更完美的知识。

欢迎到知识星球:完美Excel社群,进行技术交流和提问,获取更多电子资料,并通过社群加入专门的微信讨论群,更方便交流。

(0)

相关推荐