知识库分享链接:https://t6ixa9nyl6.jiandaoyun.com/sharedoc/73pieflUSJ5Qte7oZ2Flc8
安装链接:https://jiandaoyun.com/a/5ee73cb6aeab3800060f2045
场景:有个合同表,主表录入租赁开始时间,子表单中会录入所有需要租赁的房屋,要租几间房子就提交几条记录,并且在子表单中会显示每月租金和租赁月份数,需求是想在仪表盘中显示每月应付总租金。
思路:租几个月就在子表单中显示几条记录,每个房子每个月一条记录对应租金,仪表盘对这个汇总的子表单求和就可以。
解决方案:
一、合同订单表:(除红框字段外其他都为辅助字段会自动计算)
1.租赁日期拆分:通过租期/月和开始租赁日期得出所有租期的月份的每月第一天,比如开始时间是2020-6-1,租期是2个月,那么租赁日期拆分得到的结果就是2020-6-1,2020-7-1,最多会租几个月这个公式就要判断到几。
公式示例只写到3个月:IFS(租赁明细.租期/月==1,TEXT(DATE(合同开始日期),'yyyy-MM-dd'),租赁明细.租期/月==2,CONCATENATE(TEXT(DATE(合同开始日期),'yyyy-MM-dd'),',',TEXT(DATE(YEAR(合同开始日期),MONTH(合同开始日期)+1,1),'yyyy-MM-dd')),租赁明细.租期/月==3,CONCATENATE(TEXT(DATE(合同开始日期),'yyyy-MM-dd'),',',TEXT(DATE(YEAR(合同开始日期),MONTH(合同开始日期)+1,1),'yyyy-MM-dd'),',',TEXT(DATE(YEAR(合同开始日期),MONTH(合同开始日期)+2,1),'yyyy-MM-dd')))
2.租金重复:通过repet函数,租几个月,就将租金重复几次,比如每月100,租期3个月,租金重复结果为100,100,100,
公式:REPT(CONCATENATE(租赁明细.租金元/月,','),租赁明细.租期/月)
3.日期汇总、租金汇总:将子表单里面的租赁日期拆分和租金重复汇总到主表
日期汇总:TEXT(租赁明细.租赁日期拆分)
租金汇总:CONCATENATE(租赁明细.租金重复)
4.行数:获取租赁明细中租期的总月数
公式:SUM(租赁明细.租期/月)
5.汇总.序号:通过子表单整体联动,用行数联动出对应的序号,需要建立基础表,教程:https://hc.jiandaoyun.com/doc/11259
6.汇总.租赁日期:通过split函数,用子表单的序号取出日期汇总里面每一个日期,显示在子表单中
公式:DATE(YEAR(SPLIT(日期汇总,',')[汇总.序号-1]),MONTH(SPLIT(日期汇总,',')[汇总.序号-1]),1)
7.汇总.租金/元:通过split函数,用子表单的序号取出租金汇总里面每一个租金,显示在子表单中
公式:SPLIT(租金汇总,',')[汇总.序号-1]
最终效果:实现了将租赁明细中租赁的月份每个月对应的金额都拆分显示在了汇总的子表单中
二、仪表盘
在仪表盘关联合同订单表,配置维度和指标,就可以计算出每个单位每个月应付的所有租金。 |