2015年9月8日 星期二

Excel-判斷最近90天資料是否重覆出現(SUMPRODUCT,COUNTIF)

有網友詢問在 Excel 試算表中有如下圖的資料清單,如何判斷某個代號在最近 90 天(三個月)曾經出現過並且標示,該如何處理?
如下圖中,由 2015/4/1 起每天會有一筆或是多筆資料(以代號表示),希望能在輸入一個代號後,即能顯示這代號於最近三個月曾經出現過了。

【公式設計與解析】
儲存格C2:
=IF(SUMPRODUCT(((A2-$A$2:A2)<=90)*COUNTIF(B2,$B$2:B2))>1,"重覆","")
複製儲存格C2,往下各列貼上。因為每天會輸入新的資料,所以公式每天向下複製。
以 儲存格C162 為例,其公式為:
=IF(SUMPRODUCT(((A162-$A$2:A162)<=90)*COUNTIF(B162,$B$2:B162))
>1,"重覆","")
A162-$A$2:A162)<=90:判斷儲存格A2:A162範圍中和儲存格A162的日期相差是否在90天以內,傳回 TRUE/FALSE 陣列。
COUNTIF(B162,$B$2:B162):判斷在儲存格B2:B162範圍中和儲存格B162的代號相同者的個數,傳回 1 或是大於 1 的陣列。
在 SUMPRODUCT 函數中將以上二式相乘,其中 TRUE/FALSE 陣列在運算過程中會轉換為 1/0 陣列,最後運算的傳回值如果傳回 1,表示沒有重覆,如果傳回值大於 1,則表示重覆一次(或以上)。

【延伸學習】
如果使用陣列公式,可以改寫公式:
儲存格C2:
{=IF(SUM(((A2-$A$2:A2)<=90)*COUNTIF(B2,$B$2:B2))>1,"重覆","")}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上{}。
複製儲存格C2,往下各列貼上。

沒有留言:

張貼留言

好康東東