2016年3月23日 星期三

Excel-依據欄位中空白的儲存格列出資料清單(OFFSET,SMALL,ROW)

如下圖,網友想要在 Excel 的資料清單中依據日期欄位中的內容,以第一個空白者(未填日期)為第一個預備人選,由上而下把空白者列為優先名單,該如何處理?
Excel-依據欄位中空白的儲存格列出資料清單(OFFSET,SMALL,ROW)

【公式設計與解析】
選取儲存格C1:C17,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期。
儲存格F2:{=OFFSET($A$1,SMALL(IF(日期="",ROW(日期),999),ROW(1:1))-1,0)}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。
(1) IF(日期="",ROW(日期),999)
在陣列公式中,找出日期欄位中是空白者,並傳回日期的列號,否則傳回『999』(這只是一個隨機很大的數字)。
(2) SMALL(IF(日期="",ROW(日期),999),ROW(1:1))-1
將第(1)式的傳回值代入 SMALL 函數中,依序得到列號最小到最大的結果。
當公式往下複製時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→ ... 。
(3) OFFSET($A$1,SMALL(IF(日期="",ROW(日期),999),ROW(1:1))-1,0)
將第(2)式傳回的列號代入 OFFSET 函數,可傳回以儲存格A1為起點對應的儲存格內容。
同理:
儲存格G2:{=OFFSET($B$1,SMALL(IF(日期="",ROW(日期),999),ROW(1:1))-1,0)}
在下圖中,如果在儲存格C5中輸入資料,則預備人選的清單(下圖右)內容,也會隨之對應改變。
Excel-依據欄位中空白的儲存格列出資料清單(OFFSET,SMALL,ROW)

沒有留言:

張貼留言

好康東東