Excel 公式通过查找第一个非零单元格，然后查找与第一个非零单元格对应的日期来计算 IRR

Question

寻找一个公式，我可以将其输入到 B 列中，以计算数据集的 IRR。由于 IRR 公式为 =XIRR(Values,Dates,guess[])，我正在寻找一种方法来使其自动化，其中公式中的单元格引用从第一个非零单元格（对于值）开始，然后从日期中相同的对应单元格开始。

感谢您提供的任何帮助。我在下面的链接中有一个示例图片，正如您所看到的，起始单元格引用会有所不同，因为第一个非零单元格也会发生变化，但我需要找到一种方法来自动引用它，并为整个 B 列提供统一的公式。

Answer 1

我有一种方法可以做到这一点，即混合使用 INDIRECT、ADDRESS 和 MATCH，对于第 2 行，它看起来像这样：

=XIRR(INDIRECT(ADDRESS(ROW(),COLUMN()+MATCH(TRUE,D2:O2<>0,0))&":O"&ROW()),INDIRECT(ADDRESS(1,COLUMN()+MATCH(TRUE,D2:O2<>0,0))&":O1"))

我会试着为您分解一下。首先，我们需要找出第一列非零值，我们可以很容易地用以下方法做到这一点：

MATCH(TRUE,D2:O2<>0,0)

这将返回一个数字，我们可以在其他地方使用它。我们需要做的是使用这个数字来定义 XIRR 使用的范围。我发现最简单的方法是使用 ADDRESS，如下所示：

ADDRESS(ROW(),COLUMN()+MATCH(TRUE,D2:O2<>0,0))

我必须COLUMN()先添加进去，这样我们才能知道现在的位置，以及要偏移多少。这只给了我们范围的前半部分，即 G2。所以我们还需要添加:和地址的末尾部分，所以我们再添加一点（请注意，这里要结束的 O 列是硬编码的），并将其全部放入包装器中INDIRECT。

INDIRECT(ADDRESS(ROW(),COLUMN()+MATCH(TRUE,D2:O2<>0,0))&":O"&ROW())

这是一种半动态获取 的方法G2:O2。然后有一个类似的公式用于获取 ，G1:O1但现在更多的是硬编码在

INDIRECT(ADDRESS(1,COLUMN()+MATCH(TRUE,D2:O2<>0,0))&":O1")

将 XIRR 中的两个 INDIRECT 结合起来就可以了。

此版本的公式仅在您的数据以 O 列结束、要查找非零值的数据始终从公式后的列开始、并且日期位于第 1 行时才有效。所有这些问题都可以克服并使公式完全动态化，但这会使其变得更加复杂。

Answer 2

OFFSET由于您的标签中没有显示版本约束，因此以下内容应该可以工作，并且由于它不使用或函数，因此是非易失性的INDIRECT。

相反，我们使用INDEX和XMATCH返回相关的日期和值数组。

使用该LET函数可以得到更容易理解的公式。

B2: =LET(
    arrDt, $D$1:$O$1,
    arrVal, $D2:$O2,
    startCol, XMATCH(
        TRUE,
        arrVal <> 0
    ),
    dts, INDEX(
        arrDt,
        SEQUENCE(
            ,
            COLUMNS(arrDt) -
                startCol + 1,
            startCol
        )
    ),
    vals, INDEX(
        arrVal,
        SEQUENCE(
            ,
            COLUMNS(arrDt) -
                startCol + 1,
            startCol
        )
    ),
    XIRR(vals, dts)
)

并向下填充。