☰

转置符合特定条件的行/列的数据

tag-icon • tag-icon microsoft-excel microsoft-excel-2013 pivot-table spreadsheet
转置符合特定条件的行/列的数据

我进行了一项调查，要求分数和一个优先事项对于每个问题。

响应数据将每个组件列在单独的行中，以便将分数和优先级视为不同的问题，而不是作为相同的问题，像这样：

前

目前，如果问题 ID 为 xx1，则为分数，但如果 ID 是 xx2，则为优先事项。

我需要它被呈现，以便分数和优先事项在每个问题的列中列出为值，如下所示：

后

有什么想法可以到达那里吗？

最终，我试图将其变成一个可旋转的平面表。但现在如果我尝试旋转它，我无法将分数和优先级分开。它将它们视为单独的问题，而不是同一问题的两个不同分数，这应该是这样的。

答案1

类似这样的方法可以工作，具体取决于你的数据

Option Explicit
Sub ScorePriority()
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Dim i As Long

    For i = lastRow To 2 Step -1
        If Cells(i, 2) = Cells(i - 1, 2) And _
        Cells(i, 3) = Cells(i - 1, 3) And _
        Left(Cells(i, 4), 4) = Left(Cells(i - 1, 4), 4) Then
            Cells(i - 1, 6) = Cells(i, 5)
            Cells(i).EntireRow.Delete shift:=xlUp
        End If
    Next

End Sub

答案2

解决方案：我使用了与@gtwebb 建议的类似的多步骤方法，但不太优雅。

  1. 用于Text-to-Columns分离问题 ID 的最后一位小数（这才是关键）。最后一位小数告诉我它是 1=分数还是 2=优先级。
  2. 使用if()语句在我的新列中查找值“2”。如果存在，则将关联的优先级值复制到新的优先事项列。如果没有，请留空。将公式复制下来。
  3. 然后过滤并删除所有值为“2”的行，因为值为“1”的行现在同时具有分数和优先事项在同一行的两列中。
  4. 根据需要重命名列。
  5. 枢轴，???，和利润！！

答案3

您可以尝试免费的 Microsoft 插件Power Query（来自 Excel 2010）。加载和转换数据非常直观，如第二张表所示。从 Excel 2016 开始，它完全集成在 Excel 中（获取和转换）。

在 Power Query 的 UI 中按照以下步骤操作：

  1. 在 Power Query 中导入数据（功能区 Power Query -> 从表格，光标必须位于数据的某个位置）
  2. （可选）将问题列的类型更改为文本（取决于您的操作系统的数字格式设置，PQ 会将 1.1.1 识别为日期）
  3. 右键单击问题列 -> 按分隔符拆分列 -> 最右边的分隔符，分隔符 --自定义-- = 。
  4. 选择新列问题。2，在功能区“变换”下，单击“数据透视列”，将“分数”定义为值
  5. 将列“Question.1”、“1”、“2”重命名为“Question”、“Score”、“Priority”（双击列名）
  6. 关闭并加载到 Excel（Ribbon Home）

这里是 UI 生成的 Power Query 脚本。

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"FirstName", type text}, {"LastName", type text}, {"Question", type text}, {"Score", Int64.Type}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type","Question",Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.Csv, true),{"Question.1", "Question.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Question.1", type number}, {"Question.2", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type1", {{"Question.2", type text}}, "de-CH"), List.Distinct(Table.TransformColumnTypes(#"Changed Type1", {{"Question.2", type text}}, "de-CH")[Question.2]), "Question.2", "Score", List.Sum),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Question.1", "Question"}, {"1", "Score"}, {"2", "Priority"}})
in
    #"Renamed Columns"

相关内容