麻辣家族討論版版 - Powered by Discuz! Board

標題: 年度只加大於零 [打印本頁]

作者: myleoyes 時間: 2015-3-25 21:53 標題: 年度只加大於零

各位前輩你們好!!
年度只加大於零不知公式為何?
請知道的前輩,不吝賜教謝謝再三!!

作者: k123456770 時間: 2015-3-26 09:48

不好意思, 無法下載附件
是否可以將範例拍照po出 or 清楚描述問題

作者: p212 時間: 2015-3-26 10:58

本帖最後由 p212 於 2015-3-26 11:08 編輯

回復 1# myleoyes
假設儲存格R1為指定「年分」之輸入欄位，且只加大於零的「金額」
1.若輸入年分為「民國」年，則儲存格Q1公式
=SUMPRODUCT((YEAR($K$4:$K$12)=$R$1+1911)*($Q$4:$Q$12>0)*($Q$4:$Q$12))
2.若輸入年分為「西元」年，則儲存格Q1公式
=SUMPRODUCT((YEAR($K$4:$K$12)=$R$1)*($Q$4:$Q$12>0)*($Q$4:$Q$12))
請參考！

作者: myleoyes 時間: 2015-3-26 21:56

回復 3# p212
k123456770前輩!請看動畫檔...
前輩!你可能誤解小弟的問題
      這樣公式無法得到答案
         =SUMPRODUCT((TEXT(K$4:K$12,"YYY")=TEXT(AY2,"YYY"))*SUMIF(Q4:Q12,">0"))
      =SUMPRODUCT((TEXT(K$4:K$12,"YYY")=TEXT(AY2,"YYY"))*SUMIF(Q4:Q12,">0"))
      所以用補助欄是可以達成需求
   AY2=DATE(YEAR(TODAY()),1,1)
   AY3=DATE(YEAR(TODAY()),12,31)
   AY4=IF(AND(K4>=AY$2,K4<=AY$3,Q4>0),Q4,"")下拉
   但心想應該不需要靠補助欄就可以達到目的不是嗎?
   請前輩不吝再賜教謝謝再三!!

作者: JBY 時間: 2015-3-26 22:35

......這樣公式無法得到答案
=SUMPRODUCT((TEXT(K$4:K$12,"YYY")=TEXT(AY2,"YYY"))*SUMIF(Q4:Q12,">0"))

=SUMPRODUCT((YEAR(K$4:K$12)=YEAR(TODAY()))*(Q4:Q12>0),Q4:Q12)

作者: p212 時間: 2015-3-27 09:57

本帖最後由 p212 於 2015-3-27 10:10 編輯

回復 4# myleoyes
很抱歉誤解了您的意思，以增加輔助儲存格方式處理。
將3#的公式內容改為5# JBY大提供的修改公式即可，所以不需另增輔助儲存格 (3#中儲存格R1指定「年分」)。
=SUMPRODUCT((YEAR($K$4:$K$12)=$R$1)*($Q$4:$Q$12>0)*($Q$4:$Q$12))
↓
=SUMPRODUCT((YEAR(K$4:K$12)=YEAR(TODAY()))*(Q4:Q12>0),Q4:Q12)
請參考！

作者: myleoyes 時間: 2015-3-27 21:34

回復 6# p212
兩位前輩!真抱歉小弟也弄錯原始檔的用意
   應該 AY4=IF(AND(K4>=AY$2,K4<=AY$3,M4>0,Q4>0),Q4,"")
   也就是說M欄與Q欄要同時大於零才加總
   再附檔請不吝再賜教謝謝再三!! 辛苦囉!

作者: JBY 時間: 2015-3-27 23:26

回復 7# myleoyes

=SUMPRODUCT((YEAR(K$4:K$12)=YEAR(TODAY()))*(M4:M12>0)*(Q4:Q12>0),Q4:Q12)

或者

=SUMPRODUCT((TEXT(K$4:K$12,"yyy")=TEXT(TODAY(),"yyy"))*(M4:M12>0)*(Q4:Q12>0),Q4:Q12)

作者: myleoyes 時間: 2015-3-28 21:39

回復 8# JBY
前輩想到囉謝謝!!另一問題請教
L1為 67 顯示為 67年次型態
K1輸入1978/2/16 顯示為 67/2/16 型態
K2=CONCATENATE(MID(YEAR(K1),1,4)-1911)
照理說 K2=L1沒錯
按分析鈕卻無法執行程式
Sub 分析()
      If [K2] <> [L1] Then MsgBox ("很抱歉系統無法分析！輸入資料為 " & [K2] & " 年次不等於來源 " & [L1] & " 年次的資料,請修改輸入資料ＯＫ！！"): End
         執行程式
End Sub
為何K2的 67非L1的 67
不知有何公式可以取代或其他方法
請不吝賜教謝謝再三!!辛苦囉!
K2不能用程式轉為值如右 [K2]=Evaluate("=CONCATENATE(MID(YEAR(K1),1,4)-1911)")

作者: JBY 時間: 2015-3-28 23:24

回復 9# myleoyes
L1: 67

K1: 1978/2/16

K2 =CONCATENATE(MID(YEAR(K1),1,4)-1911)

為何 K2 的 67 非 L1 的 67 ?

因為 K2 公式返回的 67 為文本, 而, L1 的 67 為數值

....................................................................................................................

K2, 改為 =--CONCATENATE(MID(YEAR(K1),1,4)-1911)

但是,

為什麼公式使用 CONCATENATE 和 MID 函數 ??

K2 公式, 可以直接使用 :

=YEAR(K1)-1911

作者: myleoyes 時間: 2015-3-29 17:40

回復 10# JBY
前輩!謝謝!!
用中曆來輸入出生日期比較順
所以K1是由如下程式寫入
Sub 出生日期()
   ZZ = InputBox("輸入日期", "       請輸入你的出生年月日", "67/2/16")
   If ZZ <= 0 Then Exit Sub
   a = Split(ZZ, "/")
   a(0) = a(0) + 1911
   [K1] = Join(a, "/")
   [K1].NumberFormat = "e/m/d"
End Sub
如果K2=YEAR(K1)-1911是無法顯示正確的年次
哪知K2=CONCATENATE(MID(YEAR(J1),1,4)-1911)雖可顯示正確的年次
但卻非數值頭痛耶!!

歡迎光臨麻辣家族討論版版 (http://forum.twbts.com/)