上一篇文章：Excel-取出每列有數值最左(右)欄的內容及對應的欄位(INDEX,COLUMN)
其中提到了「取出每列有數值最左(右)欄的內容及對應的欄位」，有網友想要進一步，想要略過空白儲存格將資料依序向左集中。
如下圖，每一列中有部分儲存格是『空白』儲存格，現在要把非空白的儲存格向左依序集中，該如何處理？
【公式設計與解析】
儲存格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』。
如果要將儲存格中的 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。
留言列表