excel函數問題

Question

　　　　A　　　B　　　　C　　　　D　　　　E
-----------------------------------------------------------------------------------
1　｜日期　 摘要　　　收入　　　支出　　　 結餘
2　｜11/24　跨行轉帳　3,400　　　 　0　　 　3,400
3　｜11/24　ATM現金　　　0　 　2,000　　　1,400
4　｜11/27　現金　　　4,000　　　　 0　　　 5,400
5 ｜11/28　現金　　　 0　 　2,000　　　3,400
6　｜12/04　ATM現金　 　0　　 2,000　　　1,400

將所有ATM現金欄位的支出加總,函數使用SUMIF($B:$B,"ATM現金",$D:$D),
1.假設我要加入日期限制,僅限制在11月份的資料,那函數該如何寫呢
2.假設我除了日期限制我還要同時計算出"ATM現金"以及"現金"
兩個支出欄位的加總呢
感謝回答

Answer 1

提供簡單易懂的公式:
MONTH(A2:A6)=11　：A1:A6有等於11(月份)
AND
RIGHT($B$2:$B$6,2)="現金"　：$B$2:$B$6後兩字元為"現金"
的$D$2:$D$6的加總
=SUMPRODUCT((MONTH($A$2:$A$6)=11)*(RIGHT($B$2:$B$6,2)="現金")*($D$2:$D$6))
也可寫成:
=SUMPRODUCT((MONTH($A$2:$A$6)=11)*(($B$2:$B$6="ATM現金")+($B$2:$B$6="現金"))*($D$2:$D$6))

2007-01-30 12:38:07 補充：
如要計算同樣條件的收入與支出是否平衡或加總:
=SUMPRODUCT((MONTH($A$2:$A$6)=11)*(($B$2:$B$6="ATM現金")＋($B$2:$B$6="現金"))*($C$2:$C$6－$D$2:$D$6))

Answer 2

1. 假設加入日期限制,僅限制在11月份的資料, 那函數改用 SUMPRODUCT公式如下:


11月之ATM現金支出合計:
=SUMPRODUCT((MONTH($A$2:$A$6)=11)*($B$2:$B$6="ATM現金"),$D$2:$D$6)

11月之現金支出合計:
=SUMPRODUCT((MONTH($A$2:$A$6)=11)*($B$2:$B$6="現金"),$D$2:$D$6)

2.假設除了日期限制我還要同時計算出"ATM現金"以及"現金"
兩個支出欄位的加總

兩個公式相加即可。如下:
=SUMPRODUCT((MONTH($A$2:$A$6)=11)*($B$2:$B$6="ATM現金"),$D$2:$D$6)+SUMPRODUCT((MONTH($A$2:$A$6)=11)*($B$2:$B$6="現金"),$D$2:$D$6)



2007-02-01 09:03:48 補充：
Applerot 又展現簡化之公式. 多學到兩招了! 佩服囉!

Answer 3

1.請使用陣列公式
{=SUM(IF(TEXT($A$2:$A$999,"MM")="11",IF($B$2:$B$999="ATM現金",$D$2:$D$999)))}
外圍的大括弧請按住CTRL+SHIFT後,再按ENTER即會出現
(如此,公式會自動計算)

2.公式請改為
{=SUM(IF(TEXT($A$2:$A$999,"MM")="11",IF($B$2:$B$999="ATM現金",$D$2:$D$999)))+SUM(IF(TEXT($A$2:$A$999,"MM")="11",IF($B$2:$B$999="現金",$D$2:$D$999)))}
一樣是陣列公式的輸入方式

Answer 4

11月ATM現金支出，公式為
=SUMPRODUCT(--(MONTH($A$2:$A$6)=11),--($B$2:$B$6="ATM現金"),$D$2:$D$6)

11月現金支出，公式為
=SUMPRODUCT(--(MONTH($A$2:$A$6)=11),--($B$2:$B$6="現金"),$D$2:$D$6)

如果要兩個支出欄位加總，直接將上面兩個公式相加即可。

有關多重欄位查詢及 SUMPRODUCT() 用法，請參考網頁
[EXCEL] 多重條件查表法

Answer 5

請在F2輸入以下公式
=AND(MONTH(A2)=11,B2="ATM現金")
並將公式往下複製
再輸入
=SUMIF(F2:F6,TRUE,D2:D6)
就可得到問題1 的答案

同理
請在G2輸入以下公式
=AND(MONTH(A2)=11,OR(B2="ATM現金",B2="現金"))
並將公式往下複製
再輸入
=SUMIF(G2:G6,TRUE,D2:D6)
就可得到問題2的答案

若您還是不懂
請留下聯絡資料 我把Excel email to U