2011年5月26日 星期四

Excel-資料庫函數(DSUM練習)

在 Excel 中取得一個部門、產品、業績的資料表,來試著練習資料庫函數中的DSUM函數。

先定義名稱,資料:儲存格A1:D28,再定義姓名、部門、產品、業績為各欄的資料。

(1) 計算部門:業務二科的業績小計

使用SUMPRODUCT函數,儲存格G2:=SUMPRODUCT(--(部門=F2),業績)

使用陣列公式,儲存格G2:{=SUM(IF(部門=F2,業績,FALSE))}

輸入完成,請按 Ctrl+Shift+Enter 鍵。

如果你使用DSUM函數,則公式會更簡單:

儲存格G2:=DSUM(資料,4,F1:F2)

(2) 計算產品:手機的業績小計

儲存格G5:=DSUM(資料,4,F4:F5)

[例] 儲存格G5:=SUMPRODUCT(--(產品=F5),業績)

[例] 儲存格G5:{=SUM(IF(產品=F5,業績,FALSE))} [陣列公式]

 

(3) 計算姓氏 :黃的業績小計

儲存格G8:=DSUM(資料,4,F7:F8)

[例] 儲存格G8:=SUMPRODUCT(--(LEFT(姓名,1)=F8),業績)

[例] 儲存格G8:{=SUM(IF(LEFT(姓名,1)=F8,業績,FALSE))}  [陣列公式]

 

(4) 計算部門:業務三科且業績>25000的業績小計

儲存格H11:=DSUM(資料,4,F10:G11)

將條件置於相同列,則會以AND函數執行,即條件為「部門=業務三科 AND 業績>25000」。

[例] 儲存格H11:=SUMPRODUCT(--(部門=F11),--(業績>25000),業績)

[例] 儲存格H11:{=SUM(IF(部門=F11,IF(業績>25000,業績,FALSE),FALSE))}  [陣列公式]

 

(5) 計算(部門:業務三科)或(產品:電腦)的業績小計

儲存格H14:=DSUM(資料,4,F13:G15)

將條件置於不同列,則會以OR函數執行,即條件為「(部門=業務三科) OR (產品=電腦)」。

[例] 儲存格H14:=SUM(IF(((部門=F14)+(產品=G15))>0,1,0)*業績)

 

(6) 計算(部門:業務三科且業績>25000)或(產品:電腦且業績>25000)的業績小計

儲存格I18:=DSUM(資料,4,F17:H19)

條件相當為「(部門=業務三科 AND 業績>25000) OR (產品=電腦 AND 業績>25000)」。

如果使用SUMPRODUCT或是陣列公式,將會太複雜,不易呈現。而使用DSUM函數,則相對簡捷。

 

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

DSUM:http://office.microsoft.com/zh-tw/excel-help/HP010342460.aspx

DSUM:將清單或資料庫的記錄欄位 () 中符合指定條件的數字予以加總。

語法:DSUM(database, field, criteria)

database:組成清單或資料庫的儲存格範圍。

field:指出函數中所使用的資料欄。

criteria:是包含指定條件的儲存格範圍。

 

練習用數據可由下表中取用(複製後,在儲存格貼上):

姓名 部門 產品 業績
郭柏辰 業務二科 手機  $ 28,529
黃韻如 業務四科 手機  $ 25,294
王國榮 業務一科 電腦  $ 23,459
湯德斌 業務三科 手機  $ 23,931
黃文杰 業務一科 螢幕  $ 17,308
簡文鼎 業務四科 電腦  $ 14,311
劉家瑋 業務二科 手機  $ 21,052
連宥媛 業務二科 印表機  $ 14,949
湯絜蘭 業務一科 手機  $ 27,113
蔡珮甄 業務一科 印表機  $ 10,077
林傑文 業務一科 電腦  $ 18,898
葉克芸 業務二科 印表機  $ 26,549
彭士豪 業務二科 印表機  $ 23,093
呂宜蓁 業務四科 手機  $ 24,660
彭筱晴 業務三科 電腦  $ 28,740
劉增偉 業務四科 手機  $ 20,762
郭嘉揚 業務二科 螢幕  $ 14,186
胡宜潔 業務一科 電腦  $ 25,883
柯佳齊 業務三科 電腦  $ 13,152
莊孟儒 業務一科 印表機  $ 10,173
邱創陽 業務一科 印表機  $ 15,229
許景崴 業務三科 螢幕  $ 18,057
林婷暄 業務二科 螢幕  $ 20,769
蔣和諠 業務一科 印表機  $ 20,352
趙昱欣 業務四科 電腦  $ 27,818
吳欣儀 業務三科 螢幕  $ 29,321
周柏任 業務二科 手機  $ 25,006
許庭姍 業務四科 印表機  $ 29,177

沒有留言:

張貼留言

好康東東