2011年6月20日 星期一

Excel-圖書借閱資料統計與分析(SUMPRODUCT,進階篩選)

在 Excel 中取得某個月份的圖書借閱資料(如下圖,資料為虛擬),根據這些資料來做一些統計與分析的練習。

先選取所有資料範圍,按一下 Ctrl+Shift+F3 鍵,選取「勾選頂端列」選項。定義「日期、登錄號、書名、班級、借書證號」等名稱。並且定義全部資料的儲存格範圍的名稱為「資料」。

(1) 計算各班及各天借閱次數

各班借閱次數

儲存格H2:=SUMPRODUCT(--(班級=G2)),複製儲存格H2,貼在儲存格H2:H16。

各天借閱次數

儲存格H19:=SUMPRODUCT(--(日期=G19)),複製儲存格H19,貼在儲存格H19:H49。

(2) 使用「篩選」功能

在 Excel 的自動篩選中提供了「等於、不等於、大於或等於、小於、小於或等於、開始於、結束於、包含、不包含」等條件設定,並且可以使用兩個條件的邏輯 AND 和 OR 運算,並且可以使用「*、?」等萬用字元。

<A> 找出書名含有「傳說」的借閱記錄

篩選條件:包含「傳說」和篩選條件:等於「*傳說*」的篩選結果是一樣的。

也可以使用進階篩選來做到:

如果你想要挑選指定日期和班級的篩選資料,可以使用進階篩選,並且將兩個條件寫在同一列,即可執行邏輯AND運算的篩選。

以下的例子為篩選「(日期=05-06-200 AND 班級=三年六班) OR (日期=05-09-200 AND 班級=三年四班)」的資料。

將條件寫在同一列執行的是邏輯AND的運算,寫在不同列執行的是邏輯OR的運算。

相關函數說明,請參閱微軟網站:

SUMPRODUCThttp://office.microsoft.com/zh-tw/excel-help/HP010342935.aspx

SUMPRODUCT:傳回各陣列中所有對應元素乘積的總和。

語法:SUMPRODUCT(array1, [array2], [array3], ...)

array1:要求對應元素乘積和的第一個陣列引數。

array2, array3,...:要求對應元素乘積和的第 2 個到第 255 個陣列引數。

註:各陣列必須有相同的維度(相同的列數,相同的欄數)。否則會傳回錯誤值#VALUE!。並且會將所有非數值資料的陣列元素當成0來處理。

沒有留言:

張貼留言

好康東東