Excel中的数据聚合

Question

假设比尔盖茨( Bill )拥有一只基金，而该基金又拥有微软股票。在金融市场上，这是非常普遍的，这可能会更进一步(x拥有y，拥有z，拥有.)。问题是，如何递归地从最后一个节点检索数据到第一个节点？也就是说，如何知道微软比尔盖茨拥有多少股份？在SQL中，您可以使用JOIN，然后尝试在它之后进行一些黑客攻击，但即使这样，它也只能表明Bill拥有一只基金。最后一个连接，即票据基金拥有微软股票的一只基金不会连接(它会显示y属于x，但这个信息不会连接到y属于z)。那么，在excel中解决这个问题的最佳方法是什么？我完全接受类似这样的建议：“最好将其转换为csv，然后尝试这个建议的SQL命令”。下面是一个更好地理解问题的图表。

​

为了更好地理解，这里有一个图表：

​

Answer 1

这是一个由你的图表启发的疯狂的想法。在VBA中创建以下看似毫无意义的函数：

Function Owns(Owner As String, ParamArray assets() As Variant) As String
    Owns = Owner
End Function

​

​

(显然-只输入A2-A6中的公式- A8-A12中的案文只是我的评注)。要获得这些信息，循环遍历A列--通过扫描到C列查看他们拥有什么--如果C列中的字符串也在A列中--将所有A列(但不包括C列)引用该资产作为参数到Owns()。这适用于A栏中的前三个条目，如果资产仅在C列中--将该行的引用放在C列中。这适用于A中的最后两个条目。

你为什么要那样做？好-选择包含比尔盖茨的单元格，转到公式选项卡，并反复调用Trace Precedents。你应该看到：

​

​

输入法: VBA对象有一个方法.Precedents()，它将这些单元格作为Range对象。C列中的先例单元格是先例树的叶子。还有一种方法.DirectPrecedents()，它可以用于逐级处理树级。在这种情况下，它是否有用，我真的不知道，但它似乎是为您的数据获得一个树式结构的好方法，可以在VBA中进行操作。

编辑:跟踪受抚养人可以让你从T键回到比尔盖茨。有两个与此相对应的VBA方法。此外，还有一些方法，如.ShowPrecedents，可以用来为箭头动画，以允许用户查看受抚养人的流。我还试验了一种方法(在C中的某些单元格中使用第二个虚拟函数)，该方法将A列中的单元直接链接到C列中的单元格，而C列中的单元格又以ShowPrecedents显示数据前后流的方式与A列中的单元格相连--但我认为这会给树增加多余的水平。

我编写了一个宏，它接受一个电子表格的设置，就像原来的一样，并添加了虚拟公式。要使用它，你需要

1)创建两个命名范围。" owner“是所有者名称的列(在这个玩具示例中是A2:A6)和" asset”，后者是资产列的对应部分(这里是C2:C6)。

2)包含对Microsoft脚本运行时( VBA编辑器中的工具/引用)的引用，以便可以使用字典。

为了完整起见，我再次添加了“所有者”的代码以及一个名为"restore“的子程序，它将用显示的字符串(应该是原始字符串)替换添加的公式：

函数拥有(所有者作为字符串，ParamArray资产()作为变体)作为字符串所有者=所有者结束函数

Sub tag()
    Dim Owner As Range, Asset As Range
    Dim OwnerAddress As New Dictionary
    Dim OwnerFormula As New Dictionary
    Dim OwnerRange As Range
    Dim ocell As Range, acell As Range
    Dim owner_name As Variant, asset_name As String
    Dim formula As String
    Dim shift As Long

    Set Owner = Range("Owner")
    Set Asset = Range("Asset")
    shift = Asset.Column - Owner.Column '2 now, but user might move columns around

    For Each ocell In Owner
        owner_name = LCase(Trim(ocell.Value))
        If OwnerAddress.Exists(owner_name) Then
            OwnerAddress(owner_name) = OwnerAddress(owner_name) & ", " & ocell.Address
        Else
            OwnerAddress(owner_name) = ocell.Address
        End If
    Next ocell

    For Each owner_name In OwnerAddress.Keys
        Set OwnerRange = Range(OwnerAddress(owner_name)) 'e.g. all ocells containing "Bill's Fund"
        formula = "=Owns(" & """" & OwnerRange.Cells(1).Value & """"
        For Each ocell In OwnerRange
            Set acell = ocell.Offset(0, shift)
            asset_name = LCase(Trim(acell.Value))
            If OwnerAddress.Exists(asset_name) Then
                formula = formula & "," & OwnerAddress(asset_name)
            Else
                formula = formula & "," & acell.Address
            End If
        Next ocell
        formula = formula & ")"
        OwnerFormula.Add owner_name, formula
    Next owner_name

    For Each ocell In Owner
        ocell.formula = OwnerFormula(LCase(Trim(ocell.Value)))
    Next ocell
End Sub