讀者問到:在下圖左的資料清單中有四個字元,分別被標示多個數字,該數字是其位置的順序。如何將其轉換為下圖右依其位置填入字元?

下圖左之中的所有數字因代表位置,所以均不會重複。

Excel-在資料清單中依位置數字填入對應位置(SUMPRODUCT,OFFSET)

【公式設計與解析】

儲存格G1:

=OFFSET($A$1,SUMPRODUCT(($B$1:$D$4=F1)*ROW($B$1:$D$4))-1,0)

(1) ROW($B$1:$D$4)

SUMPRODUCT 函數中,傳回儲存格B1:D4中每一個儲存格的列號。

(2) SUMPRODUCT(($B$1:$D$4=F1)*ROW($B$1:$D$4))

($B$1:$D$4=F1)*ROW($B$1:$D$4):在 SUMPRODUCT 函數中的『*』運算會將判斷式傳回值 TRUE/FALSE 轉換為 1/0,相當執行邏輯 AND 運算。最後會傳回符合條件的儲存格之列號。

(3) OFFSET($A$1,第(2)式-1,0)

將第(2)式傳回的列號置於 OFFSET 函數即可找到對應的內容。

創作者介紹
創作者 vincent 的頭像
vincent

學不完.教不停.用不盡

vincent 發表在 痞客邦 留言(3) 人氣()