2016年3月22日 星期二

Excel-資料表轉換(INDEX,COLUMN,SMALL)

同仁遇到這樣的問題:在下圖左,是一個 Excel 的資料表,如果要將這個資料表轉換成下圖右的樣式,該如何處理?
Excel-資料表轉換(INDEX,COLUMN,SMALL)
面對這樣的問題,只要判斷儲存格內是否為『V』,如果成立,則印出欄標題,如果不成立,則印出空白。
儲存格J2:=IF(B2="V",B$1,"")
複製儲存格J2,貼至其他每個儲存格。
特別提醒:在 Excel 的判斷式中,雖然是使用『B2="V"』,但是儲存格B2不管輸入『V』或是『v』,結果都是成立的。
如果你想要轉換為下圖右的格式,又該如何處理?
Excel-資料表轉換(INDEX,COLUMN,SMALL)
儲存格R2:{=IFERROR(INDEX($B$1:$G$1,1,SMALL(IF($B2:$G2="V",COLUMN
($B2:$G2),999),COLUMN(A:A))-1),"")}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 自動輸入「{}」。
複製儲存格R2,貼至其他每個儲存格。
(1) IF($B2:$G2="V",COLUMN($B2:$G2),999)
在陣列公式中,判斷儲存格B2:G2中是否為『V或v』,傳回對應的儲存格B2:G2的欄號(2~7),否則傳回999。在此使用 999,只是一個隨機很大的數值。
(2) SMALL(第(1)式,COLUMN(A:A))
在陣列公式中,當公式往右複製時,會根據第(1)式所得的欄號利用 SMALL 函數,逐欄取出由最小到最大的欄號。
(3) INDEX($B$1:$G$1,1,第(2)式-1)
根據第(2)式傳回由小到大的欄號,藉助 INDEX 函數以查表方式傳回儲存格B1:G1的儲存格內容(甲~己)。
(4) IFERROR(第(3)式,"")
根據第(3)式傳回值,如果是傳回錯誤訊息者,再以 IFERROR 函數將其顯示為空白。

沒有留言:

張貼留言

好康東東