excelperfect

在《Excel公式技巧106：將表數(shù)據(jù)轉(zhuǎn)換成列數(shù)據(jù)》中，詳細(xì)解析了一位網(wǎng)友問我的問題的解答過程。然而，事情并沒有完。上次提供的示例數(shù)據(jù)太完美了，所以實現(xiàn)起來相對簡單。在上次的解答之后，該名網(wǎng)友又提出了一個比較棘手的問題。

如下圖1和圖2所示，需要將工作表Sheet1中的數(shù)據(jù)轉(zhuǎn)換成工作表Sheet2中的數(shù)據(jù)。

圖1

圖2

由于在單元格區(qū)域B2:E6中每行的數(shù)據(jù)不一，這給編寫公式帶來了難度。我的思路是，對于工作表Sheet1中列A的數(shù)據(jù)，根據(jù)同一行在單元格區(qū)域B2:E6中數(shù)據(jù)的數(shù)量，計算出共有多少個數(shù)據(jù)要重復(fù)，如下圖3所示，這是一個二維數(shù)組。

圖3

然后，利用降維技術(shù)（該技術(shù)詳見《Excel公式技巧24：Excel公式中的降維技術(shù)》），將這個二維數(shù)組變成一維數(shù)組，公式如下：

=INDEX(IF((B2:E6<>''),A2:A6,''),N(IF(1,1+(INT((ROW(INDEX(A:A,1):INDEX(A:A,ROWS(B2:E6)*COLUMNS(B2:E6)))-1)/COLUMNS(B2:E6))))),N(IF(1,1+(MOD((ROW(INDEX(A:A,1):INDEX(A:A,ROWS(B2:E6)*COLUMNS(B2:E6)))-1),COLUMNS(B2:E6))))))

結(jié)果如下圖4所示。

圖4

去掉其中的空單元格，使其成為連續(xù)包含數(shù)據(jù)的單元格，使用公式：

=IFERROR(INDEX(INDEX(IF((B2:E6<>''),A2:A6,''),N(IF(1,1+(INT((ROW(INDEX(A:A,1):INDEX(A:A,ROWS(B2:E6)*COLUMNS(B2:E6)))-1)/COLUMNS(B2:E6))))),N(IF(1,1+(MOD((ROW(INDEX(A:A,1):INDEX(A:A,ROWS(B2:E6)*COLUMNS(B2:E6)))-1),COLUMNS(B2:E6)))))),SMALL(IF(INDEX(IF((B2:E6<>''),A2:A6,''),N(IF(1,1+(INT((ROW(INDEX(A:A,1):INDEX(A:A,ROWS(B2:E6)*COLUMNS(B2:E6)))-1)/COLUMNS(B2:E6))))),N(IF(1,1+(MOD((ROW(INDEX(A:A,1):INDEX(A:A,ROWS(B2:E6)*COLUMNS(B2:E6)))-1),COLUMNS(B2:E6))))))<>'',ROW(A1:A20)),ROW(A1:A20))),'')

結(jié)果如下圖5所示。

圖5

這就是將矩形塊數(shù)據(jù)轉(zhuǎn)換成單列數(shù)據(jù)的原理展示過程。同樣，可以將單元格區(qū)域B2:E6轉(zhuǎn)換為單列數(shù)據(jù)。

咋一看，可能被這么復(fù)雜的公式嚇倒了。其實，公式里面有很多部分都是重復(fù)的，我們可以使用名稱來將公式進(jìn)行簡化。

單擊功能區(qū)“公式”選項卡中的“定義名稱”來創(chuàng)建名稱。

名稱：Pos

引用位置：=Sheet1!A2:A6

名稱：Data

引用位置：=Sheet1!$B$2:$E$6

名稱：midArr

引用位置：

=INDEX(IF((Data<>''),Pos,''),N(IF(1,1+(INT((ROW(INDEX(A:A,1):

INDEX(A:A,ROWS(Data)*COLUMNS(Data)))-1)/COLUMNS(Data))))),

N(IF(1,1+(MOD((ROW(INDEX(A:A,1):INDEX(A:A,ROWS(Data)*

COLUMNS(Data)))-1),COLUMNS(Data))))))

名稱：midArr2

引用位置：

=INDEX(Data,N(IF(1,1+(INT((ROW(INDEX(A:A,1):INDEX(A:A,

ROWS(Data)*COLUMNS(Data)))-1)/COLUMNS(Data))))),

N(IF(1,1+(MOD((ROW(INDEX(A:A,1):INDEX(A:A,ROWS(Data)*

COLUMNS(Data)))-1),COLUMNS(Data))))))

所有定義的名稱如下圖6所示。

圖6

這樣，在上圖2所示的工作表Sheet2中，選擇單元格區(qū)域A2:A21，輸入數(shù)組公式：

=IFERROR(INDEX(midArr,SMALL(IF(midArr<>'',ROW(A1:A20)),ROW(A1:A20))),'')

選擇單元格區(qū)域B2:B21，輸入數(shù)組公式：

=IFERROR(INDEX(midArr2,SMALL(IF(midArr2<>0,ROW(A1:A20)),ROW(A1:A20))),'')

結(jié)果如上圖2所示。

此時，當(dāng)你更新工作表Sheet1單元格區(qū)域B2:E6中的數(shù)據(jù)時，工作表Sheet2會自動更新。

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

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