☰

给定多个开始日期，如何为相对日期值分配计数？

tag-icon • tag-icon microsoft-excel charts
给定多个开始日期，如何为相对日期值分配计数？

我正在尝试在 Excel 中创建前后图表，在零月排列多个里程碑日期，并为里程碑月之前和之后的月份分配相对月份数。

我的电子表格大约有 5,000 行，如下所示：

+------------+-------------+---------+---------+---------+---------+---------+---------+
|  UserID    |MilestoneDate| 2014-01 | 2014-02 | 2014-03 | 2014-04 | 2014-05 | 2014-06 |
+------------+-------------+---------+---------+---------+---------+---------+---------+
|    7       | 2014-01-02  | 232     | 22      | 19      | 77      | 11      | 89      |
+------------+-------------+---------+---------+---------+---------+---------+---------+
|    89      | 2014-04-02  | 345     | 45      | 564     | 13      | 122     | 77      |
+------------+-------------+---------+---------+---------+---------+---------+---------+
|    678     | 2014-03-11  | 55      | 14      | 17      | 222     | 109     | 111     |
+------------+-------------+---------+---------+---------+---------+---------+---------+
|    897     | 2014-03-07  | 234     | 56      | 201     | 19      | 55      | 78      |
+------------+-------------+---------+---------+---------+---------+---------+---------+
|    789     | 2014-02-22  | 331     | 33      | 67      | 108     | 111     | 56      |
+------------+-------------+---------+---------+---------+---------+---------+---------+
|    989     | 2014-01-09  | 12      | 89      | 97      | 125     | 323     | 99      |
+------------+-------------+---------+---------+---------+---------+---------+---------+

MilestoneDate = 用户下载应用程序的日期，月份列代表用户每月访问网站的次数。

最终，我想生成一个折线图，X 轴上为相对月份 #，所有 MilestoneDate 月份均设置为零。MilestoneDate 之前的访问应计入相应的负月份（例如，用户 89 的 3 月活动应发生在“-1”月份，而他的 2 月活动应发生在“-2”月份）。

我不知道如何在 Excel 中实现这一点（以编程方式创建一个带有偏移值的新表 - 如何实现？）。我甚至不知道这种图表叫什么，这让我很难通过 Google 来寻求帮助。

有什么帮助吗？

答案1

看看这是否符合要求。

需要为图表源创建一个表。在此表中，用户按与原始数据源相同的顺序列出。检查每个用户的开始日期，并在 A16 及以下使用以下公式计算偏移量

=MONTH(B5)-MONTH(MonthZero)

第 15 行从 -5 到 6 的标签是手动输入的。公式从 C16 开始填充表格，向下和向内复制

=IF(IFERROR(OFFSET($C5:$H5,0,$A16+C$15,1,1),NA())=$A5,NA(),IF(IFERROR(OFFSET($C5:$H5,0,$A16+C$15,1,1),NA())=$B5,NA(),IFERROR(OFFSET($C5:$H5,0,$A16+C$15,1,1),NA())))

该公式抓取用户的原始数据，并使用计算出的偏移量将其相对于加入月份进行定位。使用两个嵌套的 IF 来避免用户名及其加入日期出现在表中，而 IFError 则确保仅返回有效的偏移量。

然后创建一个以第 14 行和第 15 行作为 X 轴并以用户 ID 作为系列的折线图。

在此处输入图片描述

我已经分享了用于创建屏幕截图的文件在我的 OneDrive 上。

笔记：不应尝试使用 5000 个用户使用 5000 个系列的此类数据可视化。您需要以某种方式聚合数据。

编辑：这是另一个版本，使用了不同的公式。我创建了另一张表，将原始数据排列在 A 至 H 列中，将图表源并排排列在 J 列及以下列中。

Offset 函数不稳定，会导致速度变慢。现在，此索引函数位于单元格 L5 中，并复制到单元格 L5 和单元格 L6 中，这样更短、更快，并且结果相同。

=IFERROR(IF($J5+L$4>=0,INDEX($C5:$H5,($J5+L$4)+1),NA()),NA())

在此处输入图片描述

您可以在 OneDrive 文件的第 2 表上找到它。

相关内容