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社群，进行技术交流和提问，获取更多电子资料，并通过社群加入专门的微信讨论群，更方便交流。