ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

EH搜索     
EH云课堂-专业的职场技能充电站 Excel转在线管理系统,怎么做看这里 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
Excel不给力? 何不试试FoxTable! Excel 2016函数公式学习大典 EH云课堂直播课程免费学 打造核心竞争力的职场宝典
300集Office 2010微视频教程 Tableau-数据可视化工具 精品推荐-800套精选PPT模板,点击获取 ExcelHome出品 - VBA代码宝免费下载
你的Excel 2010实战技巧学习锦囊 欲罢不能, 过目难忘的 Office 新界面 Excel VBA经典代码实践指南
查看: 263|回复: 6

[求助] 如何利用PQ,PP计算租赁合同的各月租金

[复制链接]

TA的精华主题

TA的得分主题

发表于 2019-9-2 17:08 | 显示全部楼层 |阅读模式
1)租金/当月天数*单月租赁天数,
     例如:客户5月18日合同开始,5月份租赁天数18-31日共计14天,租金=5000/31*14, 6月份租金5000
           假设次年2月16日份退房,2月份租金=5000/28*16天
2)合同结束日期与退租日期选择较小值
3)数据源有几万条,
   求:各门店各月租金收入

拜托各位大神了

计算租赁合同的各月租金.rar

11.27 KB, 下载次数: 18

TA的精华主题

TA的得分主题

 楼主| 发表于 2019-9-2 17:14 | 显示全部楼层
刚刚接触Power BI,对于这种问题,还没有一个清晰的思路

TA的精华主题

TA的得分主题

发表于 2019-9-3 17:24 | 显示全部楼层
关注此帖,PQ应该能解决问题!
可惜自己学艺未精,不能提供解决方案。。。。。。

TA的精华主题

TA的得分主题

发表于 2019-9-3 20:29 | 显示全部楼层
表中2个合同,开始日期大,结束日期小,这房子租期怎么算?  

用powerquery,没有什么难度:
      只要将房子租期扩展到每一行(每一天),增加几列:年月列、当月日历天数、算出当月每天租金,最后透视一下就行了。

TA的精华主题

TA的得分主题

发表于 2019-9-4 07:59 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2019-9-4 18:33 | 显示全部楼层
今天卯足了劲学习PQ,加上辅助列公式 和透视表,做得如下效果,权当抛砖引玉了!

20190904150043.jpg



let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    更改的类型 = Table.TransformColumnTypes(源,{{"合同签署日期", type date}, {"合同开始时间", type date}, {"合同结束时间", type date}, {"退房时间", type date}, {"签约租金 ", type number}}),
    已添加自定义 = Table.AddColumn(更改的类型, "开始时间首天", each Date.StartOfMonth([合同开始时间])),
    已添加自定义1 = Table.AddColumn(已添加自定义, "结束时间末天", each Date.EndOfMonth([合同结束时间])),
    更改的类型1 = Table.TransformColumnTypes(已添加自定义1,{{"开始时间首天", type date}, {"结束时间末天", type date}}),
    已添加自定义2 = Table.AddColumn(更改的类型1, "月数", each Date.Month([合同结束时间])-Date.Month([合同开始时间])+(Date.Year([合同结束时间])-Date.Year([合同开始时间]))*12+1),
    已添加自定义4 = Table.AddColumn(已添加自定义2, "开始月份总天数", each Date.DaysInMonth([开始时间首天])),
    已添加自定义3 = Table.AddColumn(已添加自定义4, "月份扩展", each List.Dates([合同开始时间],[月数],#duration(30,0,0,0))),
    #"展开的“月份扩展”" = Table.ExpandListColumn(已添加自定义3, "月份扩展"),
    更改的类型2 = Table.TransformColumnTypes(#"展开的“月份扩展”",{{"月份扩展", type date}}),
    已添加条件列 = Table.AddColumn(更改的类型2, "每月到期日", each if Date.Month([合同结束时间]) = Date.Month([月份扩展]) then [合同结束时间] else  [月份扩展]),
    已添加自定义5 = Table.AddColumn(已添加条件列, "到期日调整", each if [合同结束时间] = [每月到期日] then [每月到期日] else Text.Combine({Number.ToText(Date.Year([每月到期    日])),Number.ToText(Date.Month([每月到期日])),Number.ToText(Date.DaysInMonth([每月到期日]))},"-")),
    更改的类型3 = Table.TransformColumnTypes(已添加自定义5,{{"到期日调整", type date}}),
    已添加自定义6 = Table.AddColumn(更改的类型3, "当月总天数", each Date.Day([到期日调整]))
in
    已添加自定义6

合同列表-pq做法.rar

35.52 KB, 下载次数: 2

TA的精华主题

TA的得分主题

发表于 2019-9-4 18:34 | 显示全部楼层
本帖最后由 pxy0409 于 2019-9-4 18:42 编辑

20190904183419.jpg
咱也没说啥,还要审核?先发附件吧
11.jpg

合同列表-pq做法.rar

35.52 KB, 下载次数: 14

您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

关闭

最新热点上一条 /1 下一条

关注官方微信,每天学会一个新技能

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2019-10-15 07:37 , Processed in 0.064078 second(s), 13 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2020 Wooffice Inc.

   

沪公网安备 31011702000001号 沪ICP备11019229号

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:徐怀玉律师 李志群律师

快速回复 返回顶部 返回列表