Excel 创建公式实现 在选中单元格中查询满足条件的值，返回第二列的内容拼接为数组

excel 添加开发工具

调用

结果

创建函数 逗号分隔

SplicingRange(Arr As Range, n As Integer)
参数
Arr ： 选中单元格；n ： 需要查询的值，选中单元格中的第一例
返回值： 取第一列 = n 的，拼接为字符串用逗号分隔

 
Function SplicingRange(Arr As Range, n As Integer)
    Dim x As Integer
     For i = 1 To Arr.Rows.Count
        If Arr.Cells(i, 1) = n Then
            If SplicingRange <> "" Then
               SplicingRange = SplicingRange & "," & Arr.Cells(i, 2)
            Else
                SplicingRange = Arr.Cells(i, 2)
            End If
        End If
     Next
End Function
 
 

创建函数 拼接

修改 返回 数组

 Function SplicingRange(Arr As Range, n As Integer)
    Dim x As Integer
     For i = 1 To Arr.Rows.Count
        If Arr.Cells(i, 1) = n Then
            If SplicingRange <> "" Then
               SplicingRange = SplicingRange & ",""" & Arr.Cells(i, 2) & """"
            Else
                SplicingRange = "{""" & Arr.Cells(i, 2) & """"
            End If
        End If
     Next
     SplicingRange = SplicingRange & "}"
     
End Function
 

结果