2011年4月19日 星期二

Excel-計算儲存格範圍中的不重覆個數

在 Excel 中取得一個圖書館圖書借閱的摘要表,其中登錄號代表一本書,借書證號代表一個人。而這個資料表已經先以「借書證號」遞增排序好了。

現在,想要根據這個資料表,要來求得「借閱人次」。其中相同借閱人的借閱日期如果是同一天,則只能算一次。為了說明方便,則於下圖列出「起始列、終止列」的號碼(列號)。

由於這個資料表已經排序完成,假設資料範圍:A2:C2000,要先找出某個借書證號的資料區間。

(1) 計算起始列

儲存格F2:={MIN(IF($C$2:$C$2000=E2,ROW($C$2:$C$2000),""))}

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

(2) 計算終止列

儲存格G2:={MAX(IF($C$2:$C$2000=E2,ROW($C$2:$C$2000),""))}

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

詳細說明參考:Excel-範圍內的最大值和最小值

http://isvincent.blogspot.com/2011/04/excel_19.html

(3) 計算計閱本數
儲存格H2:=COUNTA(OFFSET($A$1,F2-1,,G2-F2+1,))

將起始列和終止列代入,利用COUNTA函數來計算有文字內容的個數。

(4) 計算借閱人次

儲存格I2:=SUMPRODUCT(1/COUNTIF(OFFSET($A$1,F2-1,,G2-F2+1,),OFFSET($A$1,F2-1,,G2-F2+1,)))
詳細說明參考:Excel-計算不重覆的數值個數
http://isvincent.blogspot.com/2010/03/excel.html

沒有留言:

張貼留言

好康東東