☰

根据日期在 Excel 中填充值

tag-icon • tag-icon microsoft-excel microsoft-excel-2010
根据日期在 Excel 中填充值

我在 Excel 2010 中遇到了一个难题。我有一个包含以下变量的 ID 列表：发行日期、累计值、到期日。每个 ID 都有不同的发行日期和到期日，但它们都在 2007 年 1 月 2 日至 2011 年 12 月 23 日之间的范围内（分析期）。下面是一个简短的示例：

该 ID 有 3 个不同的值：一个表示 2007 年 1 月 2 日（值：1000）；另一个表示 2007 年 5 月 5 日（值：1500），最后一个表示 2007 年 12 月 4 日（值：2700）。我希望此 ID 在其活动期间具有重复的累积值。

因此，在这种情况下，在 2007 年 1 月 2 日至 2007 年 5 月 4 日期间，价值将为 (1000)；然后在 2007 年 5 月 5 日至 2007 年 12 月 3 日期间，价值将为 1500；最后，在 2007 年 12 月 4 日至到期日（比如说 2010 年 3 月 3 日）期间，价值将保持在 2700。

这对我来说是一个艰巨的挑战，因为我有 355 个不同的 ID。有些 ID 具有唯一值，这意味着它们的累计值将被复制，直到到期日。但是，其他 ID 具有不同的累计值（在不同的日期），应该正确排列。

最终输出将是 2007 年 1 月 2 日至 2011 年 12 月 23 日之间的一组连续日期（以列为单位），然后是相应分配的 355 行数据。如果需要更清楚地了解请求，我可以发送一个 Excel 中的简短示例。

答案1

为此，您需要在创建矩阵之前对数据进行一些调整。

  1. ID按issue_date顺序或优先级对数据进行排序（从 A 到 Z）。

  2. 添加一列end_date。 的值end_date将是该记录的到期日和该特定 ID 的下一个发行日期前一天中较早的日期。假设ID在 A 列中为，issue_date在 B 列中为，maturity_date在 D 列中为，则输入以下公式作为end_date第一条记录的。向下填充。

    =TEXT(IF(A3=A2,MIN(B3-1,D2),D2),"mmm d, yyyy")

    在此处输入图片描述

  3. 在另一张表上，创建矩阵的框架。通过插入第一个日期然后一直填充到结束日期来创建列标签。您可以通过对表中 ID 上的唯一记录进行高级过滤来获取行标签。

  4. 在矩阵的左上角单元格（本例中为 B3）中，输入以下公式。“Raw”是包含原始数据的工作表的名称。

    =SUMPRODUCT(Raw!$C$2:$C$23,--(Raw!$A$2:$A$23=Matrix!$A3),--(DATEVALUE(Raw!$B$2:$B$23)<=Matrix!B$2),--(DATEVALUE(Raw!$E$2:$E$23)>=Matrix!B$2))

    向上和向下填充以填充矩阵。这将返回该日期的 ID 值，如果该日期没有可用数据，则返回 0。

    在此处输入图片描述

SUMPRODUCT()是 Excel 中最不直观但最有用的函数之一。基本上，它的作用是将每个数组参数中的相应值相乘，然后对这些乘积求和。这里的数组是表中的累积值以及 0 和 1 的数组，它们表示是否满足条件。因此，只有满足所有条件的值才会有非零乘积。由于只有一条记录应该满足所有条件，因此总和就是该值。

我在这里使用日期作为文本，但这些公式也适用于 Excel 中格式化为日期的日期。在这种情况下，TEXT()和DATEVALUE()位有点多余，但一切都应该一样有效。

相关内容