3883Excel表格資料轉置 @ 錦子老師

3883

Excel表格資料轉置

豆瓣哥：錦子老師您好，老師請問右圖有辦法依照左圖的B2:E8排列嗎？麻煩老師給予指導！

錦子老師：瓣哥，其實我們可以利用INDEX與MATCH函數整合處理。

步驟1：點取B2儲存格，輸入公式「=B3/SUM($B$3:$E$3)」後，將公式複製到C2:E2。

步驟2：點取B3儲存格，輸入公式「=COUNTIF($I$2:$I$15,B1)」後，將公式複製到C3:E3。

步驟3：點取B4儲存格，輸入公式「=IFERROR(INDEX($G$1:$G$15,SUMPRODUCT (LARGE(($I$2:$I$15=B$1)*ROW($I$2:$I$15),COUNTIF($I$2:$I$15,B$1)-ROW()+ 4))),"")」後，將公式複製到B2:E9。

搜尋I2:I15範圍中等於本欄第一列儲存格內容的列編號(($I$2:$I$15=B$1)*ROW ($I$2:$I$15))。

再統計I2:I15範圍中等於本欄第一列儲存格內容的筆數(COUNTIF($I$2:$I$15, B$1))。

由於顯示方式是要列編號由小到大顯示，故利用LARGE函數將上面算出的筆數減掉目前列編號加4(LARGE(($I$2:$I$15=B$1)*ROW($I$2:$I$15),COUNTIF($I$2:$I$15, B$1)-ROW()+ 4))抓取符合資料，以優為例，B4為是筆數2減4=-2再加4=2，抓取符合條件列編號第二大的值，B5為2-5+4=1，抓取符合條件列編號第一大的值。

再利用INDEX函數將SUMPRODUCT傳回的值對應到戈I2:I15儲存格範圍中的第幾列內容(INDEX($G$1:$G$15,SUMPRODUCT(LARGE(($I$2:$I$15=B$1)*ROW($I$2:$I$15), COUNTIF($I$2:$I$15,B$1)-ROW()+4))))。

由於並不是每一個評等都有4筆記錄，故會出現錯誤訊息，因此在公式最外圍以IFERROR函數設定若出現錯誤訊息則不填入資料(IFERROR(INDEX($G$1:$G$15, SUMPRODUCT(LARGE(($I$2:$I$15=B$1)*ROW($I$2:$I$15),COUNTIF($I$2:$I$15,B$1)-ROW()+4))),""))。