年度只加大於零

帖子: 42
主題: 4
精華: 0
積分: 56
點名: 0
作業系統: Window
軟體版本: 7
閱讀權限: 20
性別: 男
註冊時間: 2014-1-10
最後登錄: 2019-5-9

2^#

發表於 2015-3-26 09:48 | 只看該作者

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

p212

帖子: 617
主題: 17
精華: 0
積分: 662
點名: 1
作業系統: WINDOWS 7
軟體版本: OFFICE 2013
閱讀權限: 50
性別: 男
來自: Pingtung
註冊時間: 2011-9-2
最後登錄: 2024-3-7

2014馬年全勤獎章 2013蛇年全勤獎章熱烈參與勳章

3^#

發表於 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))
請參考！

Leo40_2.zip (4.56 KB)

myleoyes

帖子: 463
主題: 116
精華: 0
積分: 580
點名: 0
作業系統: Vista
軟體版本: 2007
閱讀權限: 50
性別: 男
註冊時間: 2010-5-4
最後登錄: 2017-11-13

論壇贊助勳章論壇榮譽勳章

4^#

發表於 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,"")下拉
   但心想應該不需要靠補助欄就可以達到目的不是嗎?
   請前輩不吝再賜教謝謝再三!!

Le40o.gif (776.68 KB)

Leo40.gif (776.68 KB)

JBY

帖子: 161
主題: 0
精華: 0
積分: 168
點名: 0
作業系統: Vista
軟體版本: Office 2007
閱讀權限: 20
性別: 男
註冊時間: 2012-9-16
最後登錄: 2017-2-19

5^#

發表於 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

帖子: 617
主題: 17
精華: 0
積分: 662
點名: 1
作業系統: WINDOWS 7
軟體版本: OFFICE 2013
閱讀權限: 50
性別: 男
來自: Pingtung
註冊時間: 2011-9-2
最後登錄: 2024-3-7

2014馬年全勤獎章 2013蛇年全勤獎章熱烈參與勳章

6^#

發表於 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

帖子: 463
主題: 116
精華: 0
積分: 580
點名: 0
作業系統: Vista
軟體版本: 2007
閱讀權限: 50
性別: 男
註冊時間: 2010-5-4
最後登錄: 2017-11-13

論壇贊助勳章論壇榮譽勳章

7^#

發表於 2015-3-27 21:34 | 只看該作者

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

Leo40-1.rar (4.17 KB)

JBY

帖子: 161
主題: 0
精華: 0
積分: 168
點名: 0
作業系統: Vista
軟體版本: Office 2007
閱讀權限: 20
性別: 男
註冊時間: 2012-9-16
最後登錄: 2017-2-19

8^#

發表於 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

帖子: 463
主題: 116
精華: 0
積分: 580
點名: 0
作業系統: Vista
軟體版本: 2007
閱讀權限: 50
性別: 男
註冊時間: 2010-5-4
最後登錄: 2017-11-13

論壇贊助勳章論壇榮譽勳章

9^#

發表於 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

帖子: 161
主題: 0
精華: 0
積分: 168
點名: 0
作業系統: Vista
軟體版本: Office 2007
閱讀權限: 20
性別: 男
註冊時間: 2012-9-16
最後登錄: 2017-2-19

10^#

發表於 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