如何使用几个工作表中的值构建摘要表？

Question

我正在尝试将工作簿中其他工作表中的单元格中的数据吸引到摘要表中。用例是，工作簿用户创建并修改了一系列详细信息工作表，这些工作表与几个单元格形式形式，其中包含诸如名称和年龄的值以及更多的详细信息，而摘要则不需要的详细信息。

到目前为止，我已经研究了以下内容：

• 3D参考
  • 这似乎很有用，但是由于本工作簿的用户会随着时间的推移添加新的细节表，所以我要么必须创建空的警卫表，并告诉他们不要与之混乱，或者硬代码是第一个和最后一个名称细节表，再次告诉他们不要与那些混乱。
  • 无论如何，似乎您可以直接使用3D引用作为公式的值。 = Joe：John！a2给出一个值错误，而不是返回三行。
• UDF返回范围
  • 我遇到的问题是如何实际返回值。
  • 我不能仅仅迭代纸张并建立一个联合（结果，电流），因为该行抛出了不会在调试器中破坏导致Excel值错误的VB错误。
  • 我尝试将每个值存储在集合中，然后将其转换为一个数组，第一个昏暗的行是行的数量，第二个昏暗为1列。当我尝试设置函数返回范围的.value时，同样，VB错误如上所述。
• 我尚未尝试使用该函数（在这种情况下为sub？）直接通过跟踪行的数量并通过单元格中的摘要工作表，直接编写值。这感觉真的很刺耳，所以我希望这不是建议的解决方案。

我很想能够通过一组单元格参考并获取表格，但是我很乐意使用摘要中的每一列使用单独的公式来指定整理的值。

我并不是要提供一个工作解决方案，但是我遇到了很多死胡同和令人沮丧的错误，我会对一些可靠的潜在客户感到非常满意。谢谢你！

这是工作簿结构的一些模型。

表1命名为摘要：

	A	B	C	D
1	名称	颜色
2	Joe	Red
3	Jane	Blue
4	John	Black

上面提到的UDF的尝试是试图在摘要表上使用以下公式：= getsummarydata（“ $ d $ 4”）

，这里有三个示例详细信息：

乔

	A	b	c	d
1	细节
2
3	名称	年龄	性别	最爱颜色
4	乔	42	m	红色
5
6	传记
7	lorem ipsum

jane

	a	b	c	d
1	详细信息
2
3 3个	名称	年龄	性别	最爱颜色
4	Jane	18	F	蓝色
5
6	传记
7	Lorem Ipsum

John

	A	B	C	D
1	细节
2
3	名称	年龄	性别	最爱的颜色
4	Joe	99	m	Black
5
6	传记
7	Lorem Ipsum

终于，这是我上次的一些非功能性VBA代码试图：

Option Explicit
Function PersonaSheetsData(RangeRef As String) As Range
    Dim result As Range, thisRange As Range, currSheet As Worksheet, col As New Collection
    For Each currSheet In ActiveWorkbook.Worksheets
        ' Guard to only pull data from Details worksheets
        If currSheet.Cells(1, 1) = "Details" Then
            Set thisRange = currSheet.Range(RangeRef)
            col.Add thisRange.Value
        End If
    Next currSheet
    Set result = ActiveCell
    Set result = result.Resize(col.Count, 1)
    Dim arr As Variant
    arr = collectionToArray(col)
    result.Value = arr
    Set PersonaSheetsData = result
End Function

Function collectionToArray(c As Collection) As Variant
    Dim a() As Variant: ReDim a(0 To c.Count - 1, 0 To 0)
    Dim i As Integer
    For i = 1 To c.Count
        a(i - 1, 0) = c.Item(i)
    Next
    collectionToArray = a
End Function

Answer 1

如果您有“自动溢流阵列公式”版本的Excel，这对我有用

Function PersonaSheetsData(RangeRef As String)
    Dim result As Range, thisRange As Range, currSheet As Worksheet, col As New Collection
    For Each currSheet In ActiveWorkbook.Worksheets
        ' Guard to only pull data from Details worksheets
        If currSheet.Cells(1, 1) = "Details" Then
            col.Add currSheet.Range(RangeRef).Value
        End If
    Next currSheet
    PersonaSheetsData = collectionToArray(col)
End Function

Function collectionToArray(c As Collection) As Variant
    Dim a() As Variant, i As Integer
    ReDim a(1 To c.Count, 1 To 1) '1-based feels more natural here...
    For i = 1 To c.Count
        a(i, 1) = c.Item(i)
    Next
    collectionToArray = a
End Function