☰

在 Excel 中检索指定日期之前的行

tag-icon • tag-icon microsoft-excel
在 Excel 中检索指定日期之前的行

原始问题：

假设我有一张Table 1如下所示的表格：

1

Table 2我怎样才能自动填充下表？

2

第二个表格应仅显示表格 1 中的值（如果这些值在 6 月 30 日可用）。例如，A应仅显示值 10,000，而不是 5,000。

更新的问题（以便更清楚地理解）：

编辑表

基本上，我怎样才能将公式放入单元格中，C16以便C27它们像上图一样填充？单元格C16必须C27能够检索第一个表中截至单元格的日期的最新 A、B、C、DE16值E27。

例如：单元格 C25 应返回 15,000，因为这是 9 月 30 日的最新可用值。它不应返回 20,000，因为该值仅在 9 月 30 日之后可用。

非常感谢大家的帮助！

答案1

在 Excel 中有一些更简单的方法可以做到这一点。但是，我使用 LO Calc，一些在 Excel 中有效的简单公式在 Calc 中不起作用。我不得不使用一种我可以验证的方法。所以这里有一个在 Calc 中也有效的解决方案。

此解决方案使用辅助列（您在屏幕截图中的 F 列中看到的列），如果您不想看到它，可以将其隐藏。

截屏

我 2016 年 9 月的输出与您的不匹配。花了几分钟才意识到这是因为您的示例中没有包含 9 月的 C 和 D 数据。我的结果是正确的。

公式

让我们从辅助列开始。表 2 基于日历季度内的名称。辅助列为您提供了这些信息。F2 中的公式：

=CEILING(MONTH(E2)/3)&A2

第一部分计算日期属于哪个季度。使用 将该名称与其连接起来&。

这引出了问题的核心。C16 中的公式是：

=IFERROR(INDEX(C$2:C$12,SUMPRODUCT(MAX((F$2:F$12=F16)*ROW(F$2:F$12)))-1),"")

怎么运行的

我将从内部解释该公式并加以实施。

  • SUMPRODUCT(MAX((F$2:F$12=F16)*ROW(F$2:F$12)))

    SUMPRODUCT 标识包含与名称和季度匹配的辅助列中最后一个条目的行。

  • INDEX(C$2:C$12,last_matching_row-1)

    INDEX 使用最后一个值的行从 C 列中的数据中选择该值。但是，位置是相对于其查找的范围指定的，而不是工作表中的实际行，因此会-1对此进行调整。

  • =IFERROR(formula,"")

    公式的主要部分是当您有数据时计算结果。如果没有数据，它会返回错误。因此，公式被包裹在 IFERROR 中，如果没有数据，它会生成一个空单元格。

答案2

我能够使用以下公式获得期望的结果：

=SUMIFS(B1:B6,C1:C6, "<="&TEXT(K1,"dd-mm-yyy"), A1:A6,A1)

列B是表 A 中的值列。

列C是表 A 中的日期列。

“<="&TEXT(K1,"dd-mm-yyy") 表示 <=2016 年 6 月 30 日（可能有更好的方法来做到这一点）。

A 列是表 A 中的名称列。

这SUMIFS 函数需要超过 1 个标准 - 因此我们对列中的值求和B，其中列中的日期C小于或等于单元格中的日期K1，并且列中的名称与A中的名称相匹配A1。

抱歉，我的答案布局不太好，我对此还很陌生 - 我希望这会有所帮助。

编辑：根据问题中添加的更新信息，我的答案不起作用。我的答案对值进行了求和，但问题仅要求最新的值。

相关内容