excelperfect

引言：本文的練習(xí)整理自chandoo.org。多一些練習(xí)，想想自己怎么解決問題，看看別人又是怎樣解決的，能夠快速提高Excel公式編寫水平。

本次的練習(xí)是：如下圖1所示，2行6列的數(shù)據(jù)，實際上是一個二維數(shù)組：

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

圖1:2行6列數(shù)據(jù)，將該數(shù)據(jù)區(qū)域命名為“a”

現(xiàn)在，要將這個二維數(shù)組動態(tài)轉(zhuǎn)換為一維數(shù)組，然后其他公式可以直接使用該數(shù)組，而無需在某個區(qū)域內(nèi)輸入。因此，你的公式應(yīng)該返回的結(jié)果是：

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

不應(yīng)該使用任何輔助單元格、中間公式或者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鍵或者公式求值功能，加深對上述公式的理解。

歡迎在下面留言，完善本文內(nèi)容，讓更多的人學(xué)到更完美的知識。

歡迎到知識星球：完美Excel社群，進(jìn)行技術(shù)交流和提問，獲取更多電子資料，并通過社群加入專門的微信討論群，更方便交流。