2015年12月13日 星期日

Excel-略過空白儲存格將資料依序向左集中(OFFSET,COLUMN,PHONETIC)

其中提到了「取出每列有數值最左(右)欄的內容及對應的欄位」,有網友想要進一步,想要略過空白儲存格將資料依序向左集中。
如下圖,每一列中有部分儲存格是『空白』儲存格,現在要把非空白的儲存格向左依序集中,該如何處理?
Excel-略過空白儲存格將資料依序向左集中(OFFSET,SMALL,COLUMN,PHONETIC)

【公式設計與解析】
儲存格A13:{=OFFSET($A1,0,SMALL(IF(($A1:$M1<>""),COLUMN($A1:$M1),
999),COLUMN(A:A))-1)}
這是陣列公式,輸入完成按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」。
SMALL(IF(($A1:$M1<>""),COLUMN($A1:$M1),999):在陣列公式中,判斷儲存格A1:M1的內容是否不為空白,如果成立則傳回欄的編號,如果不成立則傳回999。(999參數只是隨意假設一個很大的數)
再透過 OFFSET 函數,以儲存格A1為起點,運用上式的結果來查詢相對應的欄內容。
複製儲存格A13,貼至儲存格A13:M21。
觀察下圖,其中空白儲存格會傳回『0』。
Excel-略過空白儲存格將資料依序向左集中(OFFSET,SMALL,COLUMN,PHONETIC)
如果要將儲存格中的 0,顯示為空白,則修改公式:
儲存格A13:{=IF(SMALL(IF(($A1:$M1<>""),COLUMN($A1:$M1),999),
COLUMN(A:A))=999,"",OFFSET($A1,0,SMALL(IF(($A1:$M1<>""),COLUMN
($A1:$M1),999),COLUMN(A:A))-1))}

【延伸練習】
如果你的儲存格內容中不包含公式,且每一個儲存格內容最多只 1 個字,則改用下列更簡單的公式。
儲存格A13:=MID(PHONETIC($A1:$M1),COLUMN(C:C),1)
使用 PHONETIC 函數將多個儲存格內容串接在一起,如果儲存格內容包含公式,則這個儲存格會被視為空白。
複製儲存格A13,貼至儲存格A13:M21。

沒有留言:

張貼留言

檢視其他文章

好康東東