2017年6月16日 星期五

Excel-列出不重覆的清單(OFFSET,SMALL,ROW)

在 Excel 的工作表中(如下圖),如果想要根據日期清單列出不重覆的日期,該如何處理?
手動方式可以參考以下二篇文章:
如果你想要使用公式,則參考以下的做法。
Excel-列出不重覆的清單(OFFSET,SMALL,ROW)

【公式設計與解析】
本例使用一個輔助欄位C欄。
1. 計算同一個日期由第一個儲存格起算累計有幾個。
儲存格C2:=COUNTIF($A$2:A2,A2)
儲存格C2,貼至儲存格C2:C25。
2. )
儲存格F2:{=IFERROR(OFFSET($A$1,SMALL(IF($C$2:$C$25=1,
ROW($C$2:$C$25),""),ROW(1:1))-1,0),"")}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。
(1) IF($C$2:$C$25=1,ROW($C$2:$C$25),"")
在陣列公式中由C欄中找出標示為『1』的列號。
(2) SMALL(第(1)式,ROW(1:1))
利用 SMALL 函數,根據第(1)式中傳回的列號由小到大,找出第 1,2,3,… 最小者。ROW(1:1)函數,向下複製公式後,會產生 ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→…。
(3) OFFSET($A$1,第(2)式-1,0)
將第(2)式的傳回值代入 OFFSET 函數,得到對儲存格內容(一個日期)。
(4) IFERROR(第(3)式,"")
由於公式向下複製後,可能因為查詢不到對應的儲存格而產生錯誤訊息,利用 IFERROR函數將錯誤訊息轉換為空字串(顯示為空白)。

沒有留言:

張貼留言

好康東東