|
PowerQuery解决方案,左边的调整列表可扩展,更改中间的开始日期,右键点击右边的表格,选择刷新就可以了,方便快捷- let
- Source = Table.TransformColumnTypes(Excel.CurrentWorkbook(){[Name="RateList"]}[Content],{{"调整时间",type date},{"利率",type number}}),
- Custom1 = [s=Date.From(Excel.CurrentWorkbook(){[Name="StartDate"]}[Content][开始日期]{0}),
- a=Date.From(DateTime.LocalNow()),
- b=List.Select(List.Transform({1..120},each Date.AddYears(s,_)),each _<a),
- c=List.Select(Source[调整时间],each _>s),
- d=List.Distinct(List.Sort({s}&b&c&{a})),
- e=#table(type table[开始时间 = date,结束时间 = date],List.Zip({List.RemoveLastN(d),List.Skip(d)}))
- ][e],
- Custom2 = Table.AddColumn(Custom1,"天数",each [a=List.Select(List.Transform({1..1200},(x)=>Date.AddMonths([开始时间],x)),(y)=>y<=[结束时间]),
- b=List.Count(a),
- c=b*30+Duration.Days([结束时间]-(if b=0 then [开始时间] else List.Last(a)))][c]),
- Custom3 = Table.AddColumn(Custom2,"利率",each List.Last(Table.SelectRows(Source,(x)=>x[调整时间]<=[开始时间])[利率]))
- in
- Custom3
复制代码
|
|