双字典实现文本聚合，维度转换，再送3种解法！

直接来看案例，这也算是一种典型的案例了，如果总成绩是数值，那么可以直接使用数据透视表，几秒搞定，但是如果是文本，或者混合的，传统的透视表就无法处理了！

▲ 案例来源：EH论坛

那么我们要如何处理呢？其实我一共写了4种:

> 函数法：TEXTJOIN+FILTER

> PowerPivot:CONCATENATEX

> PowerPivot:透视列+Text.Combine

> VBA:双字典棋盘法！

本篇主要讲讲VBA的处理方法，其他方法见文末~

处理思路：

1、使用两个字典，分别记录行和列

2、我们按顺序把行列内容分别添加到字典，如果不存在就序号+1，存入，如果存在就取出存在的序号，这样我们就可以得到行列不重复的内容，且快速定位到每个人每个科目在的位置

3、结果是一个二维数组，但是我们不知道大小，但是有一点可以肯定，不会超过数据源的行数，所以我们可以声明两个维度都和数据源一样大

4、考虑一个人一个科目可能有多次成绩，我们需要判断对应的结果是否存在，如果存在就使用逗号拼接！

最后把brr写入表中即可！

部分代码截图:

完整源码如下：

'功能：一维转二维，文本聚合
'作者：E精精
'日期：20220129
'-----------------------------------------------------------
Sub 一维转二维()
    Dim dicRow As Object
    Dim dicCol As Object
    Set dicRow = CreateObject('scripting.dictionary')
    Set dicCol = CreateObject('scripting.dictionary')
    
    Dim arr '数据源数据
    Dim brr()
    Dim r As Long, c As Long, i As Long
    Dim n As Long, m As Long
    
    arr = Sheets('原始成绩').Range('A1').CurrentRegion.Value
    ReDim brr(1 To UBound(arr), 1 To UBound(arr))
    m = 1: n = 1
    For i = 2 To UBound(arr)
        '如果不存在
        If Not dicRow.exists(arr(i, 1)) Then
            '序号+1，写入字典
            m = m + 1
            dicRow(arr(i, 1)) = m
        End If
        r = dicRow(arr(i, 1))
        brr(r, 1) = arr(i, 1)
        
        If Not dicCol.exists(arr(i, 2)) Then
            n = n + 1
            dicCol(arr(i, 2)) = n
        End If
        c = dicCol(arr(i, 2))
        brr(1, c) = arr(i, 2)
        
        '判断同一人统一科目是否已有数据
        If CStr(brr(r, c)) <> '' Then
            brr(r, c) = brr(r, c) & ',' & arr(i, 3)
        Else
            brr(r, c) = arr(i, 3)
        End If
    Next
  
    With Sheet3
        .Range('F8').Resize(m, n) = brr
    End With
End Sub

OK！VBA方法就到这里！其他方法我们也分享给大家，就不一一再详解了！

▍函数法：

=TEXTJOIN(',',,IFERROR(FILTER(原始成绩!$C$1:$C$300,(原始成绩!$A$1:$A$300=函数!$A2)*(原始成绩!$B$1:$B$300=函数!B$1)),''))

▍PowerPivot：CONCATENATEX函数解法

=CONCATENATEX(DISTINCT('表1'[总成绩]),'表1'[总成绩],',')

▍PowerQuery：透视列解法

= Table.Pivot(更改的类型, List.Distinct(更改的类型[课程名称]), '课程名称', '总成绩',  each Text.Combine(_,','))

怎么样？不管什么问题，只要你Excel学的还可以，一般至少也是可以写个三两种解法的！