2014年3月16日 星期日

Excel-將一欄篩選為多欄後依順序列出清單(陣列公式)

一直有網友有這種需求:在 Excel 的資料表,想要將一欄中的資料篩選為多欄後依順序列出(參考下圖),該如何處理呢?

Excel 其實提供了多種的工具可以手動來完成這個工作,但是,因為資料會變動,所以還是以公式來處理比較好。

【準備工作】

選取儲存格A1:C26,按下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:人員、假別、時數。

【輸入公式】

(1) 儲存格E2

{=IFERROR(OFFSET($B$1,SMALL(IF(人員="甲",ROW(人員),10^10),ROW(1:1))-1,,,),"")}

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

IF(人員="甲",ROW(人員),10^10):取得人員為「甲」的「列號陣列」,如果不為「甲」者,給予一個很大的數,本例為 10 的 10 次方。

SMALL(IF(人員="甲",ROW(人員),10^10),ROW(1:1)):利用上式取得的列號陣列,在 SMALL 函數中取得第 1 小值(ROW(1:1)=1)的列號,如果該公式向下複製時,即可取得第 2 小、第 3 小、…的列號。

將上式取得的列號,帶入 OFFSET 函數以取得對照的儲存格內容(即為「假別」)。

再以 IFFERROR 函數將查不到資料而傳回錯誤訊息的儲存格顯示為空白。

(2) 儲存格F2

{=IFERROR(OFFSET($C$1,SMALL(IF(人員="甲",ROW(人員),10^10),ROW(1:1))-1,,,),"")}

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

仿 (1) 儲存格E2的原理,只需將起始儲存格B1改為儲存格C1即可。如此,可以取得「時數」的內容。

複製儲存格E2:F2,往下各列貼上。

試著自行練習產生G2:J2的公式,只要將上述公式中的「甲」改為「乙」、「丙」即可。

沒有留言:

張貼留言

好康東東