學不完．教不停．用不盡

在 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