excel 2007-数据透视表字符串逗号分隔

Question

我有一个数据源，我需要在汇总表中显示它们。

请参阅附件中的输出表，并告诉我如何制作

请查看样本附件

Answer 1

一种方法是使用用户定义的函数使逗号分隔列表

Function MergeCOO(rId As Range, r1 As Range, r2 As Range) As String
    Dim vIdx As Variant
    Dim vCOO As Variant
    Dim Id As Variant
    Dim i As Long, str As String
    Dim c As New Collection
    Id = rId.Value
    vIdx = r1
    vCOO = r2
    On Error Resume Next
    For i = LBound(vIdx, 1) To UBound(vIdx, 1)
        If vIdx(i, 1) = Id Then
            c.Add vCOO(i, 1), vCOO(i, 1)
            If Err.Number <> 0 Then Err.Clear
        End If
    Next
    For i = 0 To c.Count
        str = str & c.Item(i) & ","
    Next
    MergeCOO = Left(str, Len(str) - 1)
End Function

将公式添加到工作表Source 中的L列

=MergeCOO([@[HS Code]],[HS Code],[COO])

然后，您可以将这个额外的列添加到您的报告中，因为您认为