[算表] sumproduct計算時計入已結束契約金額

[johnshyu (johnshyu)]

軟體:
excel

版本:
2010

公式設定是要將數個跨年度的契約按存在月份統計於該月份的租金總額

目前設了兩個公式分別是：
=SUMPRODUCT((DATE(YEAR(租期起日),MONTH(租期起日),1)<=DATE(2016,1,1))*(DATE(YEAR(租期迄日),MONTH(租期迄日
),1)>=DATE(2016,1,31))*租金*(標的類型=$A$2))

+SUMPRODUCT((DATE(YEAR(租期起日),MONTH(租期起日),DAY(租期起日))<DATE(2016,1,1))*(DATE(YEAR(租期迄日),MONTH(租
期迄日),DAY(租期迄日))<=DATE(2016,1,31))*租金*(標的類型=$A$2))

第一個公式是測式契約起迄日的月份第一天是不是屬於在測試月份的起迄日
但是在契約尾期卻沒辦理計入。所以加入第二個公式做計算
將契約起日當天跟迄日當日去計算有沒有在測試月份當日內，但卻發生在契約
結束的後續月份，公式仍然成立，就會一直計入該契約數，造成公式失效
希望能夠有改善尾期抓取之公式，謝謝大家!!

2012/08/22      2021/10/21
2015/05/08      2017/05/07
2016/04/01      2017/03/31
2015/11/01      2016/03/31
上面是契約起迄日，在測試2016/3/31時都還正確，測4/30時，3/31結束的契約仍會計入


--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 61.231.184.24
※ 文章網址: https://www.ptt.cc/bbs/Office/M.1485875101.A.C65.html
→ soyoso: 請問2017/1/15~2017/4/14來看的話會計入1,2,3月亦或是2,3,
→ soyoso: 4月份呢？
→ johnshyu: 如果以我的公式的話，123月會用公式1各計入1次
→ johnshyu: 4月的話用公式2可以計入，但5月以後就會有重覆計入問題
→ johnshyu: 公式也沒辦法計算破月租金的問題，這也是我的困擾之一
→ soyoso: 1/15~4/14三個月，但回文1,2,3用公式各計入1次而公式2又計
→ soyoso: 入1次嗎？
→ soyoso: 如以回文破月舉例且於1,2,3月計入的話
→ soyoso: https://i.imgur.com/bYzk6S2 是否類似這樣
→ johnshyu: 1/15~4/14實際是3個月，但以公式算是1~4月都存在，所以
→ johnshyu: 首期及中間期數都可以用公式1抓到，末期要靠公式2
→ johnshyu: 破月問題會造成首末期多算，但主要問題還是後續月份重覆
→ johnshyu: 我試試你的公式先，謝謝
→ johnshyu: 公式的(MIN(租期起日,L7)<=L7)不論什麼狀況都至少小等於
→ johnshyu: 測試起日，就變成每個月份都抓入了，但這樣設是簡化不少
→ soyoso: 如1~4月都存在的話 https://i.imgur.com/MMw0kbJ
→ johnshyu: 我用你的公式聯想修正公式後，變成
→ johnshyu: SUMPRODUCT((DATEVALUE(租期起日)<=L7)*
→ johnshyu: ((DATEVALUE(租期迄日)>=EOMONTH(L7,0))*租金))
→ johnshyu: 2個公式簡化成1式就可以避免尾期計算重覆的失敗了
→ johnshyu: 我有設定義跟時間，這樣簡單很多，非常謝謝你
→ johnshyu: 再來就是破月計算如果能夠解決就圓滿了
→ johnshyu: 早上測試時，發現假設起日是2016/4/2，條件2016/4/1就
→ johnshyu: 抓不到的問題(因未小於4/1日)，產生首末期非1日抓不到的
→ johnshyu: 狀況
→ soyoso: https://i.imgur.com/dFUKz5u 類似這樣
→ johnshyu: 真的很感謝~~!!