2014年5月28日 星期三

Excel-將欄位中的資料重組為資料為欄位的表格(陣列公式)

前一篇文章:提到將欄位中的資料重組為資料為欄位的表格,網友還想要知道左邊表格如何轉換為右邊表格。(參考下圖)

方法應有很多,例舉使用陣列公式的做法:

儲存格H2:

{=OFFSET($A$1,0,SMALL(IF($B2:$E2="彈班",COLUMN($A:$D),99),COLUMN(A1)))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

IF($B2:$E2="彈班",COLUMN($B:$E),99):找出儲存格B2:E2中含有「彈班」的儲存格,對應至欄A:D,可傳回 1,2,3,4。若不含「彈班」者,傳回 99。(99的用意是要給一個較大的數值)

將上式置入 SMALL 函數,再利用 COLUMN(A1)=1 來取出第 1 小的值,當向右複製公式時會產生 COLUMN(B1)=2、COLUMN(C1)=3、COLUMN(D1)=4。

最後藉由 OFFSET 函數,根據儲存格A1的相對位址得到對應的「甲、乙、丙、丁」。

試試將 OFFSET 函數改為 INDIRECTADDRESS 函數:

儲存格H2:{=INDIRECT(ADDRESS(1,SMALL(IF($B2:$E2="彈班",COLUMN($B:$E),99),COLUMN(A1))))}

沒有留言:

張貼留言

檢視其他文章

好康東東