☰

Excel 2010：求和及返回日期的公式

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function microsoft-excel-2007
Excel 2010：求和及返回日期的公式

我有一本工作簿，里面有一张汇总表和一张数据集表，其中有一个链接表，其中包含 700k+ 条投资财务数据记录。（在我的具体案例中，是房地产；但这与跟踪股票和股息的情况相同。）汇总表每项资产（投资）占一行，并显示每项资产最初注入的金额（即投入或投资），预期货币产出（或投资回报）​​在某个时候将等于原始注入/投资金额。数据集显示每项资产的每日产量（即货币产出），每项资产占一行按日期（即许多每个属性有 100 行，并且无法编辑。需要注意的是，PropName 列不可靠（不准确/不一致），因此应使用 PropID 来关联两个工作表。

我希望在汇总表的“100% 回收日期”列中有一个公式，该公式将告诉我总（累计）产出达到或超过投入的 100% 的日期（即收回投资的日期）。我假设我需要一个公式来执行以下操作，尽管我可能错了：

  1. 将每个资产的所有生产（投资回报）​​数据汇总，从最旧到最新（数据集已按此方式排序），

    直到达到原始投入/投资的 100%（显示在汇总表的总投入栏中），

  2. 然后返回达到 100% 恢复率的日期（位于数据集表中的字段中），

  3. 如果未达到 100% 恢复，则返回“TBD”。

例如，对于物业 0764（“物业 1”），所需的 100%RecoveryDate 应为 6/7/2013（来自数据集表的第六个数据行），因为 1.667000055+ 5.000999928 +1.667000055 + 3.334000111 + 5.000999928（来自第一个六数据集表的行）= 16.670000077，大于 13（属性 0764 的总输入）。

欢迎并非常感谢任何建设性的反馈！

汇总表样本：

PropID   PropName   TotalInput   CurrentOutput  100%RecoveryDate  ProgressToRecovery
 0764     Prop 1          13         71,820                              189%
 0736     Prop 2      30,711        134,746                              439%
 1680     Prop 3      25,014         52,887                              211%
 4078     Prop 4      29,494         36,705                              124%
 5226     Prop 5      43,983         41,438                               94%
 6427     Prop 6      28,786         50,855                              177%
 6683     Prop 7      19,231         60,501                              315%
 6739     Prop 8      28,350         48,229                              170%
 9153     Prop 9      37,888         28,125                               74%
 8020     Prop 10     31,429         41,094                              131%

数据集样本：

PropID   PropName     Date          Output
 0764     Prop 1    6/1/2013      1.667000055
 0764     Prop 1    6/3/2013      5.000999928
 0764     Prop 1    6/7/2013      1.667000055
 0764     Prop 1    6/10/2013     3.334000111
 0736     Prop 2    6/19/2013   361
 0764     Prop 1    6/19/2013     5.000999928
 0764     Prop 1    6/22/2013     6.668000221
 0764     Prop 1    7/12/2013     3.334000111
 1680     Prop 3    7/17/2013   389
 0764     Prop 1    7/23/2013    10.00200081
 0736     Prop 2    8/2/2013    236
 4078     Prop 4    8/22/2013   236
 0764     Prop 1    8/25/2013     6.668000221
 0764     Prop 1    8/30/2013     3.334000111
 0764     Prop 1    8/31/2013     5.000999928
 0764     Prop 1    9/11/2013     1.667000055
 6427     Prop 6    9/15/2013  1018
 1680     Prop 3    9/16/2013   389
 0764     Prop 1    9/20/2013     6.668000221
 0764     Prop 1    9/22/2013    10.00200081
 0764     Prop 1    9/23/2013    10.00200081
 5226     Prop 5    9/23/2013   125

答案1

创建一个虚拟表。将其链接到数据集表，如下所示：

  • 单击单元格A1，然后

    • 类型='Data Set'!A1，或
    • 键入=，单击“数据集”选项卡，然后单击A1该工作表上的 单元格

    然后键入Enter或单击公式栏左侧的复选标记。

  • 单击名称框（公式栏左侧），然后键入一个涵盖数据集表中所有行的范围，包括（至少）“PropID”、“日期”和“输出”列。（这些是示例中的列A、C和D，并且您说您有 700K+ 行，因此您可以键入A1:D999999。）
  • 键入Enter。举例来说： Excel 体操插图
  • 单击公式栏（应该显示='Data Set'!A1）并输入Ctrl+ Enter。

然后，

  • 进入=IF(SUMIFS($D$2:$D2,$A$2:$A2,$A2)>VLOOKUP($A2, Summary!A:C, 3,FALSE), ROW(), "")单元G2格并输入Enter。
  • 进入=MIN(IF(($A$2:$A$999998=$A2), ($G$2:$G$999998), 999999))单元格H2并输入Ctrl+ Shift+ Enter，从而使其成为“数组公式”。
  • 将它们拖至第 999999 行。

    不幸的是，我不知道如何让名称框技巧适用于数组公式；您可能只需要手动拖动它。

  • 转到摘要表。
  • 在单元格E2（第一个“100%RecoveryDate”）中输入：=IF(ISERROR(VLOOKUP(A2,Dummy!A:H,8,FALSE)), "No Data!", IF(VLOOKUP(A2,Dummy!A:H,8,FALSE)=999999, "TBD", INDEX(Dummy!C:C,VLOOKUP(A2,Dummy!A:H,8,FALSE))))。
  • 将单元格格式化为日期。
  • 如果您愿意的话，将其置于中心。
  • 然后将其拖至摘要表的最后一行数据。
笔记：如果您的数据集达到 1000000（一百万）行，则需要修改上述内容。我希望这些地方很明显。

相关内容