如何合并/混合单元格范围作为公式参数 [英] How merge/mix range of cell as formula parameter

问题描述

在 Excel 中，我想计算一个项目的 XIRR.
我的付款分为两个不同的范围.
但是 XIRR 函数(和其他函数)接受单个范围作为参数.
如何合并函数参数中的范围?喜欢这样的东西

In Excel I want to calculate XIRR of a projects.
My payments are in two separate ranges.
But XIRR function (and other) accept single range as parameter.
How Can I merge ranges in functions parameters? Like something as this

=XIRR(MixRanges(a1:a4,d1:d2), MixRanges(b1:b4,e1:e2))  

感谢您的建议

推荐答案

我试图不使用 UDF，但我想不出一个聪明的内置函数来使用.

I was trying not to use a UDF, but I couldn't figure out a clever built-in function to use.

将此代码添加到您的工作簿:

Add this code to your workbook:

Option Explicit

Public Function MergeRange(ParamArray rng()) As Single()
    Dim i As Long
    Dim count As Long
    Dim r As Excel.Range
    Dim s() As Single

    For i = LBound(rng) To UBound(rng)
        If (TypeOf rng(i) Is Excel.Range) Then
            For Each r In rng(i)
                ReDim Preserve s(count)
                s(count) = r.Value

                count = count + 1
            Next r
        End If
    Next i

    MergeRange = s
End Function

并像这样使用它:

=XIRR(MergeRange(A2:A6,D2:D6),MergeRange(B2:B6,E2:E6),0.1)

这将接受多个范围，获取它们的值，并将这些值作为连续数组返回.由于 XIRR 需要值和日期，因此使用 Single 数据类型应该没问题.

This will accept multiple ranges, grab their values, and return the values as a contiguous array. Since XIRR needs values and dates, using the Single data type should be ok.

这篇关于如何合并/混合单元格范围作为公式参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！