![按月统计活跃时长的发生情况并绘制成图表](https://linux22.com/image/1613084/%E6%8C%89%E6%9C%88%E7%BB%9F%E8%AE%A1%E6%B4%BB%E8%B7%83%E6%97%B6%E9%95%BF%E7%9A%84%E5%8F%91%E7%94%9F%E6%83%85%E5%86%B5%E5%B9%B6%E7%BB%98%E5%88%B6%E6%88%90%E5%9B%BE%E8%A1%A8.png)
假设我现在有一个电子表格,其中保存着 CrossFit 健身房会员的日期和会员期限。
Date Started Date Ended Member Name
11/27/2019 12/19/2019 Aaron
10/21/2019 11/12/2019 Laura
10/21/2019 11/13/2019 Walt
10/9/2019 11/13/2019 Thomas
9/23/2019 10/23/2019 Kinsey
9/17/2019 10/1/2019 Ashley
8/29/2019 11/15/2019 Chris
8/27/2019 9/10/2019 Kevin
我需要创建一个数据透视图/表格或图形,统计按月拥有活跃会员资格的会员的出现次数。
例如,Chris 将从 8 月到 11 月被视为活跃会员。因此,他将被列入 8 月、9 月、10 月和 11 月。
另一个例子是,Ashley 从 9 月和 10 月开始都是活跃会员。因此,她将被纳入 9 月和 10 月。
到目前为止,我只能按成员的开始日期来计算成员的出现次数。有什么想法可以解决这个问题吗?
答案1
您可以设置一个表格,然后绘制图表。
你可以用公式来实现
- 第一列是代表每个月第一天的日期列表
- 第二列是返回该月计数的公式。
例如
日期栏:
First Month: I2: =EOMONTH(MIN(Table1[Date Started]),-1)+1
Subsequent Months: =IFERROR(IF(EOMONTH(MAX(Table1[Date Ended]),0)>EDATE(I3,1),EDATE(I3,1),""),"")
计数列:
=SUMPRODUCT((I3>Table1[Date Started]-DAY(Table1[Date Started]))*(I3<DATE(YEAR(Table1[Date Ended]),MONTH(Table1[Date Ended])+1,0)))
您还可以使用Power Query,这在您更新、扩展或收缩数据表时可以带来一些优势。
PQ 适用于 Excel 2010 及更高版本。在 Excel 2016+ 中,您可以在表格中选择单元格后从Data
选项卡 -->访问此功能。Get&Transform
Table/Range
除了输入自定义列公式之外,所有步骤都可以从 UI 中完成,将下面的 M 代码粘贴到高级编辑器中(可能需要编辑Source Table Name
)将重现 UI 步骤供您检查,以便了解正在执行的操作。
算法
- 添加自定义列,创建列表按月从开始到结束的日期
- 提炼列表中的值以分号分隔
- 分裂将列分成行(使用分号分隔符)
- 拆分成行也可以有效地取消列透视
- 转换该列到每月的第一天
- 团体日期列中的 Operation = Count
M 代码
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date Started", type date}, {"Date Ended", type date}, {"Name", type text}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type", "Months", each List.Transform({0..Date.Month([Date Ended])-Date.Month([Date Started])+(Date.Year([Date Ended])-Date.Year([Date Started]))*12}, (x) => Date.AddMonths([Date Started],x))),
#"Extracted Values" = Table.TransformColumns(#"Added Custom1", {"Months", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Extracted Values", {{"Months", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Months"),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Months", type date}}),
#"Calculated Start of Month" = Table.TransformColumns(#"Changed Type1",{{"Months", Date.StartOfMonth, type date}}),
#"Grouped Rows" = Table.Group(#"Calculated Start of Month", {"Months"}, {{"Count", each Table.RowCount(_), type number}}),
#"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Months", Order.Ascending}})
in
#"Sorted Rows"