錦子老師

周雅芳：「請問錦子老師，我有一個表格想由現時的效果變成理想的效果，不懂Excel有沒有功能可以做到。」

錦子老師：「其實這並不是那麼難，作法是：

點取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儲存格。

【公式說明】

首先我們要設定條件公式，由於是要抓取數字對應的英文項目，故輸入($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,,,),"")。

以上就是今天主要學習的知識點，希望對大家有所幫助~~有什麼問題歡迎留言，我會儘量及時的給大家答覆~~

部落格相關範例