☰

如何将具有多个值的列转换为具有多列的单行

tag-icon • tag-icon microsoft-excel
如何将具有多个值的列转换为具有多列的单行

我的 excel 文件有 213,262 条记录 - 大约 43k 项索赔。每项索赔的多个 CPT 代码列在单个列中。我需要将每项索赔转换为一行多列，每个 CPT 代码一列。每项索赔的代码数量各不相同，但我只想看到最多 30 个 CPT 代码。
现在，我的数据如下所示：

Item #    CPT
123456789 11111
123456789 22222
123456789 33333
123456789 44444

我需要的是：

Item #    CPT1  CPT2  CPT3  CPT4  
123456789 11111 22222 33333 44444

任何帮助或建议都将不胜感激！提前致谢！

答案1

这项任务并不像乍一看那么困难。

解决方案有几种不同的方法。例如，正如评论中提到的（我完全同意这个观点），您可以将表数据传输到数据库，然后使用 SQL 指令将其转换为所需的格式。

由于您的数据已经在 Excel 工作表中，因此您可以使用内置的 Excel 机制。例如，使用函数UNIQUE()构建所有项目并使用TRANSPOSE(FILTER())获取持续刺激每个值。

或者你可以使用不太复杂的 VBA 代码：只需收集所有可用的项目中的键Dictionary并创建一个持续刺激 Collection为每个数据。之后，将累积的数据转换为矩形二维数组。代码可以是这样的：

Function getPivot(aData As Range, nCountCRT As Long) As Variant
Dim aAllItems As Object
Dim aCRTs As Collection
Dim aRow As Range
Dim sItem As Variant, sCRT As Variant
Dim i As Long, j As Long
Dim aResult() As Variant
Rem Collect data from range aData:
    Set aAllItems = CreateObject("Scripting.Dictionary")
    For Each aRow In aData.Rows
        sItem = aRow.Cells(1).Value
        sCRT = aRow.Cells(2).Value
        If aAllItems.Exists(sItem) Then
            Set aCRTs = aAllItems(sItem)
            aCRTs.Add sCRT
            Set aAllItems(sItem) = aCRTs
        Else
            Set aCRTs = New Collection
            aCRTs.Add sCRT
            aAllItems.Add sItem, aCRTs
        End If
    Next aRow
Rem Repack data to array:
    ReDim aResult(1 To aAllItems.Count, 0 To nCountCRT)
    i = 0
    For Each sItem In aAllItems.keys
        i = i + 1
        aResult(i, 0) = sItem
        j = 0
        For Each sCRT In aAllItems(sItem)
            j = j + 1
            If j > nCountCRT Then Exit For
            aResult(i, j) = sCRT
        Next sCRT
    Next sItem
Rem Set first row as Table Header
    For i = nCountCRT To 1 Step -1
        aResult(1, i) = aResult(1, 1) & i
    Next i
    getPivot = aResult
End Function

使用此功能，您可以轻松地在工作表中获取包含所需结果的新工作表。假设您的所有数据都在活动工作表上，并从第一行的第一个单元格（单元格 A1）开始。然后此过程将完成工作：

Sub createPivot()
Dim a As Variant
Dim sheet As Worksheet
    a = getPivot(ActiveSheet.UsedRange.Resize(, 2), 30)
    Set sheet = ActiveWorkbook.Sheets.Add(After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count))
    sheet.Range(Cells(1), Cells(UBound(a, 1), UBound(a, 2) + 1)).Value = a
End Sub

解决您的问题的另一种方法是使用位于“数据”选项卡上的 PowerQuery 工具。此工具专门用于数据转换任务。要掌握它，您需要观看几个视频在YouTube上，阅读函数描述语言参考书，并在论坛上寻找有关使用该工具的建议。

一旦您掌握了正确的技能，只需单击几下即可解决这些问题。

将源数据转换为名为SourceTable（Ctrl+T）。

在“数据”选项卡上选择“从表”命令，单击工具栏上的必要按钮，获取如下代码

let
    Source = Excel.CurrentWorkbook(){[Name="SourceTable"]}[Content],
    SetCPTtypeAsText = Table.TransformColumnTypes(Source,{{"CPT", type text}}),
    GroupItems = Table.Group(SetCPTtypeAsText, {"Item #"}, {{"GroupByItems", each _, type table}}),
    ConvertGroupToString = Table.AddColumn(GroupItems, "CPT", each Text.Combine(List.FirstN(List.Distinct(Table.Column([GroupByItems], "CPT")),30), "#(tab)")),
    SplitStringToColumns = Table.SplitColumn(ConvertGroupToString,"CPT",Splitter.SplitTextByDelimiter("#(tab)"),{"CPT.1", "CPT.2", "CPT.3", "CPT.4", "CPT.5", "CPT.6", "CPT.7", "CPT.8", "CPT.9", "CPT.10", "CPT.11", "CPT.12", "CPT.13", "CPT.14", "CPT.15", "CPT.16", "CPT.17", "CPT.18", "CPT.19", "CPT.20", "CPT.21", "CPT.22", "CPT.23", "CPT.24", "CPT.25", "CPT.26", "CPT.27", "CPT.28", "CPT.29", "CPT.30"}),
    RemoveTemporaryColumn = Table.RemoveColumns(SplitStringToColumns,{"GroupByItems"})
in
    RemoveTemporaryColumn

此脚本比上面的 VBA 宏略短，运行速度快一两秒，但产生的结果相同。但创建这样的场景需要一定的技能

希望这对您有帮助

相关内容