excelperfect

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

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

圖1

圖2

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

圖3

然后，利用降維技術(shù)（該技術(shù)詳見(jiàn)《Excel公式技巧24：Excel公式中的降維技術(shù)》），將這個(gè)二維數(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ù)的原理展示過(guò)程。同樣，可以將單元格區(qū)域B2:E6轉(zhuǎn)換為單列數(shù)據(jù)。

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

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

名稱(chēng)：Pos

引用位置：=Sheet1!A2:A6

名稱(chēng)：Data

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

名稱(chēng)：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))))))

名稱(chēng)：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))))))

所有定義的名稱(chēng)如下圖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所示。

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

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

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