2666如何將資料轉置
周雅芳 :「請問錦子老師,我有一個表格想由現時的效果變成理想的效果,不懂 Excel 有沒有功能可以做到。」
![](https://assets.matters.news/embed/e3d4f897-a604-4b03-8427-f0abd71b70b8.png)
錦子老師:「其實這並不是那麼難,作法是:
點取E3儲存格輸入公式:
=IFERROR(OFFSET($A$1,SUMPRODUCT(SMALL(($B$2:$C$5=E$2)*ROW($B$2:$C$5),COUNT($B$2:$C$5)-COUNTIF($B$2:$C$5,E$2)+ROW()-2))-1,,,),"")
再將公式複製到E3:I5儲存格。
![](https://assets.matters.news/embed/40bac19b-8881-4fc5-9d12-395ed41c68bb.png)
【公式說明】
首先我們要設定條件公式,由於是要抓取數字對應的英文項目,故輸入($B$2:$C$5=E$2)抓取對應第二列數字的資料,再傳回所在列ROW($B$2:$C$5)。
由於是用Small函數,會將不符合的資料當0,故要先統計資料個數COUNT($B$2:$C$5),再統指定範圍中符合的個數COUNTIF($B$2:$C$5,E$2),二者相減後,再加上目前的列數減2 ROW()-2傳回其位在第幾列。
透過SUMPRODUCT函數傳回詃數值SUMPRODUCT(SMALL(($B$2:$C$5=E$2)*ROW ($B$2:$C$5),COUNT($B$2:$C$5)-COUNTIF($B$2:$C$5,E$2)+ROW()-2))。
然後透過OFFSET函數,指定從A1儲存格向下移動傳回的列數減1的儲存格內容OFFSET ($A$1,SUMPRODUCT(SMALL(($B$2:$C$5=E$2)*ROW($B$2:$C$5),COUNT($B$2:$C$5)-COUNTIF ($B$2:$C$5,E$2)+ROW()-2))-1,,,)。
如果沒有資料可傳回時會出現錯誤訊息,故利用IFERROR函數使其不填入資料=IFERROR (OFFSET($A$1,SUMPRODUCT(SMALL(($B$2:$C$5=E$2)*ROW($B$2:$C$5),COUNT($B$2:$C$5)-COUNTIF($B$2:$C$5,E$2)+ROW()-2))-1,,,),"")。
以上就是今天主要學習的知識點,希望對大家有所幫助~~有什麼問題歡迎留言,我會儘量及時的給大家答覆~~