如何使用跨多個作業表的值構建匯總表？-有解無憂

我正在嘗試將作業簿中一組其他作業表中的單元格中的資料提取到匯總表中。用例是作業簿用戶創建和修改一系列類似于表單的詳細資訊作業表，其中包含多個單元格，其中包含名稱和年齡等值以及摘要不需要的更詳細資訊。

到目前為止，我研究了以下內容：

3D 參考
- 這看起來很有用，但是因為這個作業簿的用戶將隨著時間的推移添加新的詳細資訊表，我要么必須創建空的保護表并告訴他們不要亂用這些，要么硬編碼第一個和最后一個詳細表的名稱和再次，告訴他們不要惹那些。
- 無論如何，您似乎不能直接使用 3D 參考作為公式的值。=Joe:John!A2給出一個值錯誤而不是回傳三行。
UDF 回傳范圍
- 我在這里遇到的問題是如何實際回傳值。
- 我不能只遍歷作業表并構建一個 Union(Result, CurrentRange) ，因為該行會引發一個 VB 錯誤，該錯誤不會在除錯器中中斷，從而導致 Excel 值錯誤。
- 我嘗試將每個值存盤在 Collection 中，然后將其轉換為陣列，其中第一個暗淡是行數，第二個暗淡是 1 列。當我嘗試設定函式回傳范圍的 .Value 時，再次出現上述 VB 錯誤。
我還沒有嘗試讓函式（或在這種情況下是子？）通過跟蹤行數并逐個單元格地移動匯總作業表來直接寫入值。這感覺真的很hacky，所以我希望這不是建議的解決方案。

我希望能夠只傳遞一組單元格參考并獲得一個表格，但我很樂意為摘要中的每一列使用單獨的公式來指定要整理的值。

我并不是要求提供一個可行的解決方案，但我遇到了很多死胡同和令人沮喪的錯誤，我會對一些可靠的線索感到非常高興。謝謝！

以下是作業簿結構的一些模型。

作業表 1 名為摘要：

	一個	乙
1	姓名	顏色
2	喬	紅色的
3	簡	藍色的
4	約翰	黑色的

我上面提到的 UDF 嘗試是在匯總表的單元格 B2 中嘗試使用以下公式：=GetSummaryData("$D$4")

以下是三個示例詳細資訊表：

喬

	一個	乙	C	D
1	細節
2
3	姓名	年齡	性別	最喜歡的顏色
4	喬	42	米	紅色的
5
6	傳
7	Lorem Ipsum

簡

	一個	乙	C	D
1	細節
2
3	姓名	年齡	性別	最喜歡的顏色
4	簡	18	F	藍色的
5
6	傳
7	Lorem Ipsum

約翰

	一個	乙	C	D
1	細節
2
3	姓名	年齡	性別	最喜歡的顏色
4	喬	99	米	黑色的
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

uj5u.com熱心網友回復：

如果您有 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

轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/487651.html

標籤：数组擅长 vba 用户定义函数

上一篇：使用vba連接到API

下一篇：嘗試從ExcelVBA的表單復選框中的用戶選擇中列印多張作業表