This is a Power Query based approach...
我从这开始:
然后,我通过单击“添加列”选项卡和“自定义列”按钮添加自定义列,并完成弹出窗口,如下所示:
...然后单击“确定” .
然后我通过选择它然后单击Transform选项卡然后单击Data Type和Date来更改该新列的类型 .
然后我添加了另一个自定义列,完成弹出窗口,如下所示:
然后我添加了另一个自定义列,完成弹出窗口,如下所示:
然后我添加了另一个自定义列,完成弹出窗口,如下所示:
然后我通过单击列顶部的
并展开到新行来扩展我添加的最后一列 .
然后我添加了一个最终的自定义列,完成弹出窗口,如下所示:
最后,我按Event,DaysLost,Started和Year列进行分组,并通过单击Transform选项卡和Group By按钮并完成弹出窗口来总结DaysLostForYear列:
我最终得到了这个:
你可能想要一个不同的分组,但这应该让你接近 . 它显示了与每次伤害总天数相关的年份中丢失了多少天 . 例如,第一次伤害持续30天,于2016年12月25日开始:其中7天发生在2016年,23起发生在2017年 . 第二次伤害是588天,从2012年8月6日开始:148 2012年为365天,2013年为365天,2014年为75天 .
请注意,我将开始日期视为丢失的一天 . 还要注意我占闰年 .
我希望这有帮助 .
这是查询代码:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Event", type text}, {"DaysLost", Int64.Type}, {"Started", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Ended", each Date.AddDays([Started],[DaysLost]-1)),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Ended", type date}}),
#"Added Custom3" = Table.AddColumn(#"Changed Type1", "DaysYearStarted", each Number.From(Date.From(Text.From(Date.Year([Started]))&"/12/31")-[Started])+1),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "DaysYearEnded", each Number.From([Ended]-Date.From(Text.From(Date.Year([Ended])-1)&"/12/31"))),
#"Added Custom5" = Table.AddColumn(#"Added Custom4", "Year", each List.Numbers(Date.Year([Started]),Date.Year([Ended])-Date.Year([Started])+1)),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom5", "Year"),
#"Added Custom1" = Table.AddColumn(#"Expanded Custom", "DaysLostForYear", each if [Year]=Date.Year([Started]) then [DaysYearStarted] else
if [Year]=Date.Year([Ended]) then [DaysYearEnded] else
if Date.IsLeapYear([Year]) then 366 else 365),
#"Grouped Rows" = Table.Group(#"Added Custom1", {"Event", "DaysLost", "Started", "Year"}, {{"DaysLostForYear", each List.Sum([DaysLostForYear]), type number}})
in
#"Grouped Rows"