作者johnshyu (johnshyu)
看板Office
标题[算表] sumproduct计算时计入已结束契约金额
时间Tue Jan 31 23:04:58 2017
软体:
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://webptt.com/cn.aspx?n=bbs/Office/M.1485875101.A.C65.html
1F：→ soyoso: 请问2017/1/15~2017/4/14来看的话会计入1,2,3月亦或是2,3, 01/31 23:59
2F：→ soyoso: 4月份呢？ 01/31 23:59
3F：→ johnshyu: 如果以我的公式的话，123月会用公式1各计入1次 02/01 00:09
4F：→ johnshyu: 4月的话用公式2可以计入，但5月以後就会有重覆计入问题 02/01 00:09
5F：→ johnshyu: 公式也没办法计算破月租金的问题，这也是我的困扰之一 02/01 00:10
6F：→ soyoso: 1/15~4/14三个月，但回文1,2,3用公式各计入1次而公式2又计 02/01 00:11
7F：→ soyoso: 入1次吗？ 02/01 00:11
8F：→ soyoso: 如以回文破月举例且於1,2,3月计入的话 02/01 00:17
10F：→ johnshyu: 1/15~4/14实际是3个月，但以公式算是1~4月都存在，所以 02/01 00:26
11F：→ johnshyu: 首期及中间期数都可以用公式1抓到，末期要靠公式2 02/01 00:27
12F：→ johnshyu: 破月问题会造成首末期多算，但主要问题还是後续月份重覆 02/01 00:28
13F：→ johnshyu: 我试试你的公式先，谢谢 02/01 00:32
14F：→ johnshyu: 公式的(MIN(租期起日,L7)<=L7)不论什麽状况都至少小等於 02/01 00:39
15F：→ johnshyu: 测试起日，就变成每个月份都抓入了，但这样设是简化不少 02/01 00:40
17F：→ johnshyu: 我用你的公式联想修正公式後，变成 02/01 00:54
18F：→ johnshyu: SUMPRODUCT((DATEVALUE(租期起日)<=L7)* 02/01 00:55
19F：→ johnshyu: ((DATEVALUE(租期迄日)>=EOMONTH(L7,0))*租金)) 02/01 00:55
20F：→ johnshyu: 2个公式简化成1式就可以避免尾期计算重覆的失败了 02/01 00:56
21F：→ johnshyu: 我有设定义跟时间，这样简单很多，非常谢谢你 02/01 00:56
22F：→ johnshyu: 再来就是破月计算如果能够解决就圆满了 02/01 00:57
23F：→ johnshyu: 早上测试时，发现假设起日是2016/4/2，条件2016/4/1就 02/01 08:50
24F：→ johnshyu: 抓不到的问题(因未小於4/1日)，产生首末期非1日抓不到的 02/01 08:51
25F：→ johnshyu: 状况 02/01 08:51
27F：→ johnshyu: 真的很感谢~~!! 02/02 18:25