Excel如何解析数组？自定义VBA数组函数作为公式参数失效求解

1. Excel数组存储问题 #

• Excel中的数组是独立的元素集合，不会默认以逗号/分号拼接的字符串形式存储，只有调用JOIN函数显式拼接时才会生成格式化字符串。
• 你举例的一维字符串数组Arr = Array("A","B","C")用默认JOIN处理后会生成"A,B,C"，默认分隔符为逗号，也可以自定义分隔符。如果是二维数组无法直接用JOIN拼接。

2. VBA自定义函数test调用问题 #

• 直接调用=test(A2)不能正常匹配你的使用需求，原代码存在两个核心问题：
  • 数组声明为String类型，会把B列的日期转为字符串，后续日期计算会失效
  • VBA默认返回一维横向数组，和Excel工作表的纵向单元格区域结构不匹配
• 调整后的代码如下：

Function test(Ref As String) As Variant
    Dim creationdetableau() As Variant
    Dim cpt As Integer
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("CashFlows")
    cpt = 0
    
    For i = 1 To 35500
        If Ref = ws.Cells(i, "A").Value Then
            ReDim Preserve creationdetableau(cpt)
            creationdetableau(cpt) = ws.Cells(i, "B").Value
            cpt = cpt + 1
        End If
    Next i
    
    ' 无匹配值时返回错误值避免异常
    If cpt = 0 Then
        test = CVErr(xlErrNA)
        Exit Function
    End If
    
    ' 转置为纵向数组，匹配工作表区域结构
    test = Application.Transpose(creationdetableau)
End Function

• 调整后使用规则：
  • Excel 365/2021等支持动态数组的版本，输入=test(A2)按回车即可自动溢出所有匹配的日期值
  • 旧版Excel需要选中和返回元素数量一致的纵向连续单元格，输入公式后按Ctrl+Shift+Enter数组确认即可输出所有值

3. CfDur公式适配动态数组问题 #

公式无法运行的核心原因是原test函数返回的结构/格式不符合CfDur的参数要求，按以下步骤解决即可：

1. 先使用上文调整后的test函数，确保返回的是纵向的、原生日期格式的动态数组
2. 如果你使用的Excel版本支持动态数组传参，调整后的公式=CfDur(E2;P2;test(A2);CashFlows!J2:J100)可以直接运行
3. 如果版本不支持数组传参，可通过两种方式兼容：
   • 新建命名区域：打开名称管理器，新建名称为动态日期，引用位置填写=test(你的工作表名!$A$2)，将公式修改为=CfDur(E2;P2;动态日期;CashFlows!J2:J100)即可
   • 新增辅助列：将=test(A2)的返回值溢出到空白辅助列，把辅助列的对应区域作为参数传入CfDur即可

内容的提问来源于stack exchange，提问作者TourEiffel