如何生成含FALSE值字段及对应Loan ID的动态数组？公式报错求助

需要创建Excel公式或VBA宏，生成如下动态数组：针对第2行中值为FALSE的每个字段，返回该字段列内值为FALSE的对应Loan ID。测试的公式返回#CALC!错误，寻求可行方案。

数据集 #

期望数组输出 #

已测试错误公式 #

返回#CALC!错误：

=LET(
    data, C5:H14,
    headers, C4:H4,
    active, C3:H3,
    loans, B5:B14,

    filteredCols, FILTER(SEQUENCE(1, COLUMNS(data)), active=TRUE),

    results,
    REDUCE("", filteredCols,
        LAMBDA(acc,col,
            VSTACK(acc,
                FILTER(
                    CHOOSE({1,2}, INDEX(headers, col), loans),
                    INDEX(data,,col)=FALSE
                )
            )
        )
    ),

    DROP(results,1)
)

公式解决方案（修正版） #

原公式核心问题：

1. 错误引用了判断字段的行：应该用第2行（C2:G2）判断需要处理的字段，而非第3行表头；
2. 数据范围与筛选逻辑不匹配：原data范围偏移，且筛选列的条件写反。

修正后的公式：

=LET(
    data, C4:G13,  ' 字段数据区域（排除表头）
    headers, C3:G3, ' 字段表头
    target_cols, C2:G2, ' 判断需处理字段的行（值为FALSE的字段）
    loans, B4:B13, ' Loan ID列

    ' 筛选出需要处理的字段列序号
    filtered_cols, FILTER(SEQUENCE(COLUMNS(data)), target_cols=FALSE),

    ' 遍历目标列，提取符合条件的字段名和Loan ID
    results, REDUCE("", filtered_cols,
        LAMBDA(acc, col,
            VSTACK(acc,
                FILTER(
                    HSTACK(INDEX(headers, col), loans),
                    INDEX(data,, col)=FALSE
                )
            )
        )
    ),

    ' 移除初始空行并添加表头
    VSTACK({"Field", "Loan ID"}, DROP(results, 1))
)

公式说明 #

• 调整了data、target_cols、loans的引用范围，匹配实际数据位置；
• 用HSTACK替代CHOOSE，更简洁地组合字段名与Loan ID；
• 最后通过VSTACK添加表头，直接生成完整结果数组。

VBA宏解决方案 #

若需用VBA实现，运行以下宏后，结果会输出到当前工作表I1单元格开始的区域：

Sub GenerateLoanIDReport()
    Dim ws As Worksheet
    Dim dataRange As Range, headerRange As Range, targetColsRange As Range, loanIDRange As Range
    Dim targetCols As Variant, data As Variant, headers As Variant, loans As Variant
    Dim resultArr() As String
    Dim i As Long, j As Long, rowCount As Long

    Set ws = ActiveSheet
    ' 设置各区域范围，可根据实际表格调整
    Set headerRange = ws.Range("C3:G3")
    Set targetColsRange = ws.Range("C2:G2")
    Set dataRange = ws.Range("C4:G13")
    Set loanIDRange = ws.Range("B4:B13")

    ' 读取数据到数组，提升运行效率
    headers = headerRange.Value
    targetCols = targetColsRange.Value
    data = dataRange.Value
    loans = loanIDRange.Value

    rowCount = 0
    ' 先统计符合条件的记录行数
    For j = 1 To UBound(targetCols, 2)
        If targetCols(1, j) = False Then
            For i = 1 To UBound(data, 1)
                If data(i, j) = False Then
                    rowCount = rowCount + 1
                End If
            Next i
        End If
    Next j

    ' 初始化结果数组
    ReDim resultArr(1 To rowCount + 1, 1 To 2)
    resultArr(1, 1) = "Field"
    resultArr(1, 2) = "Loan ID"

    rowCount = 1
    ' 填充结果数组
    For j = 1 To UBound(targetCols, 2)
        If targetCols(1, j) = False Then
            For i = 1 To UBound(data, 1)
                If data(i, j) = False Then
                    rowCount = rowCount + 1
                    resultArr(rowCount, 1) = headers(1, j)
                    resultArr(rowCount, 2) = loans(i, 1)
                End If
            Next i
        End If
    Next j

    ' 输出结果到I1开始的区域
    ws.Range("I1").Resize(UBound(resultArr, 1), UBound(resultArr, 2)).Value = resultArr
End Sub

VBA说明 #

• 先统计符合条件的记录数，初始化对应大小的数组；
• 遍历每个目标字段列，收集符合条件的字段名与Loan ID；
• 结果输出到工作表I列开始位置，可根据需求修改输出区域。

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