☰

按月统计活跃时长的发生情况并绘制成图表

tag-icon • tag-icon microsoft-excel date countif range pivot-chart
按月统计活跃时长的发生情况并绘制成图表

假设我现在有一个电子表格，其中保存着 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&TransformTable/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"

相关内容