Excel公式练习99：二维数组转换成一维数组

excelperfect

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

本次的练习是：如下图1所示，2行6列的数据，实际上是一个二维数组：

{1,2,3,”a”,”b”,”c”;4,5,6,”d”,”e”,”f”}

图1:2行6列数据，将该数据区域命名为“a”

现在，要将这个二维数组动态转换为一维数组，然后其他公式可以直接使用该数组，而无需在某个区域内输入。因此，你的公式应该返回的结果是：

{1,2,3,”a”,”b”,”c”,4,5,6,”d”,”e”,”f”}

不应该使用任何辅助单元格、中间公式或者VBA。

写下你的公式。

解决方案

下面列出一系列公式，有兴趣的朋友可以逐个研究。

公式1：

=OFFSET(a,TRUNC((COLUMN(OFFSET(A1,,,,COLUMNS(a)*ROWS(a)))-1)/COLUMNS(a)),MOD(COLUMN(OFFSET(A1,,,,COLUMNS(a)*ROWS(a)))-1,COLUMNS(a)),ROWS(a),COLUMNS(a))

公式2：

=OFFSET(a,INT(COLUMN(OFFSET(A1,,,,COUNTA(a/0)))/(COLUMNS(a)+1)),MOD(COLUMN(OFFSET(A1,,,,COUNTA(a/0)))-1,COLUMNS(a)),1,1)

公式3：

=IF(T(OFFSET(a,INT(COLUMN(OFFSET(A1,,,,COUNTA(a/0)))/(COLUMNS(a)+1)),MOD(COLUMN(OFFSET(A1,,,,COUNTA(a/0)))-1,COLUMNS(a)),1,1))='',N(OFFSET(a,INT(COLUMN(OFFSET(A1,,,,COUNTA(a/0)))/(COLUMNS(a)+1)),MOD(COLUMN(OFFSET(A1,,,,COUNTA(a/0)))-1,COLUMNS(a)),1,1)),T(OFFSET(a,INT(COLUMN(OFFSET(A1,,,,COUNTA(a/0)))/(COLUMNS(a)+1)),MOD(COLUMN(OFFSET(A1,,,,COUNTA(a/0)))-1,COLUMNS(a)),1,1)))

公式4：

=OFFSET(a,INT(COLUMN(OFFSET(A1,,,,COUNTA(-a)))/(COLUMNS(a)+1)),MOD(COLUMN(OFFSET(A1,,,,COUNTA(-a)))-1,COLUMNS(a)),1,1)

公式5：

=OFFSET(a,INT(COLUMN(OFFSET(A1,,,,COUNTA(-a)))/COLUMNS(a)-1/COLUMNS(a)),MOD(COLUMN(OFFSET(A1,,,,COUNTA(-a)))-1,COLUMNS(a)),1,1)

公式6：

=OFFSET(a,MOD(ROW(OFFSET(A1,,,COUNTA(-a)))-1,ROWS(a)),INT(ROW(OFFSET(A1,,,COUNTA(a)))/ROWS(a)-1/ROWS(a)),1,1)

公式7：

=INDEX(A1:F2,N(IF(1,1+(INT((ROW(INDEX(A:A,1):INDEX(A:A,ROWS(A1:F2)*COLUMNS(A1:F2)))-1)/COLUMNS(A1:F2))))),N(IF(1,1+(MOD((ROW(INDEX(A:A,1):INDEX(A:A,ROWS(A1:F2)*COLUMNS(A1:F2)))-1),COLUMNS(A1:F2))))))

可以通过F9键或者公式求值功能，加深对上述公式的理解。

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

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