复制 Google 表格 {array1;array2} 函数在 Microsoft Excel 中作为用户定义函数 [英] Replicate Google Sheets {array1; array2} function in Microsoft Excel as user defined function

问题描述

在 Google 表格中，我可以使用 ={range1;range2;...} 表示法将多个范围附加到一个作为公式的一部分.在 Excel 中，此功能似乎无法作为公式使用.我想创建一个用户定义的函数来允许我在 Excel 中附加范围.

理想情况下，该函数应具有与 Google 表格版本类似的简单性.我附上了一个

然后可以在公式中使用它:

=FILTER(vStack(A:C,F:H),vStack(A:A,F:F)=苹果")

In Google Sheets, I can use the ={range1;range2;...} notation to append multiple ranges into one as part of a formula. In Excel, it seems this functionality isn't available as a formula. I would like to create a user defined function to allow me to append ranges in Excel.

Ideally, the function would have a similar simplicity to the Google Sheets version. I've attached a link to a public Google Sheets document with a basic use case, in case it's helpful.

I've tried Tom's answer but it gives an error (see example photo).

Thanks!

解决方案

Here is a general UDF to stack ranges of the same number of columns:

Function vStack(ParamArray rng() As Variant) As Variant
    If TypeName(rng(1)) <> "Range" Then Exit Function
    Dim otarr() As Variant
    ReDim otarr(1 To 100000, 1 To rng(1).Columns.Count)
    
    Dim z As Long
    z = 1
    
    Dim i As Long
    For i = LBound(rng) To UBound(rng)
        If TypeName(rng(i)) <> "Range" Then Exit Function
        If i > LBound(rng) Then
            If rng(i).Columns.Count <> rng(i - 1).Columns.Count Then Exit Function
        End If
        Dim rngarr As Variant
        rngarr = Intersect(rng(i), rng(i).Parent.UsedRange)
        Dim j As Long
        For j = LBound(rngarr, 1) To UBound(rngarr, 1)
            Dim k As Long
            For k = LBound(rngarr, 2) To UBound(rngarr, 2)
                otarr(z, k) = rngarr(j, k)
            Next k
            z = z + 1
        Next j
    Next i
    
    Dim nArray() As Variant
    ReDim nArray(1 To z - 1, 1 To UBound(otarr, 2))
    
    For i = 1 To z - 1
        For j = 1 To UBound(otarr, 2)
            nArray(i, j) = otarr(i, j)
        Next j
    Next i
    
    vStack = nArray

End Function

One note, I limit the initial array to 100,000 rows. If this is not enough you can up that to what ever you want, but also think, "Am I treating Excel as a database?". If the answer is yes, it is time to make the switch to an actual referential database.

Then one can use it in a formula:

=FILTER(vStack(A:C,F:H),vStack(A:A,F:F)="Apples")

这篇关于复制 Google 表格 {array1;array2} 函数在 Microsoft Excel 中作为用户定义函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！