excel - Excel 数据透视表中的相同/重复列名

问题描述

我在 Excel 中有一个数据表，如下所示：

| Country |   City   | Jan 1, 2020 | Jan 2, 2020 | Jan 3, 2020 | .... | Jan 8, 2020 | Jan 9, 2020 | .... |
----------------------------------------------------------------------------------------------------------
|   USA   | New York |    88647    |    83247    |    92621    | ...  |     32874   |    31940    | .... |
|   USA   |  Boston  |    31647    |    73242    |    42620    | ...  |     22870   |    11944    | .... |
|   USA   |   Miami  |    58641    |    23241    |    92627    | ...  |     22872   |    61943    | .... |
| England |  London  |    88621    |    23324    |    32620    | ...  |     12874   |    21940    | .... |
| England |  Bristol |    73612    |    13320    |    62626    | ...  |     32876   |    81903    | .... |

这些列一直持续到 2020 年 12 月 31 日……所以它是全年的数据。

我想看到全年所有星期一、星期二、……星期日的国家明智数字加起来。为此，我从日期列中提取了 WEEKDAY，但这会给我提供重复名称的列名称，因为日期名称每 7 天重复一次。下面是我提取给定日期的工作日后表格的样子。

| Country |   City   | Wednesday | Thursday | Friday | .... | Wednesday | Thursday | .... |
--------------------------------------------------------------------------------------------
|   USA   | New York |   88647   |   83247  |  92621 | ...  |   32874   |  31940   | .... |
|   USA   |  Boston  |   31647   |   73242  |  42620 | ...  |   22870   |  11944   | .... |
|   USA   |   Miami  |   58641   |   23241  |  92627 | ...  |   22872   |  61943   | .... |
| England |  London  |   88621   |   23324  |  32620 | ...  |   12874   |  21940   | .... |
| England |  Bristol |   73612   |   13320  |  62626 | ...  |   32876   |  81903   | .... |

由于重复的列名，因此旋转这是一个死胡同。周三列的第二个实例将在枢轴中读取为周三 2，第三个实例为周三 3，依此类推。有没有办法让数据透视表将周三的所有实例视为一个周三，依此类推？

下面是我的最终输出应该是什么样子：

| Country | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday |
-------------------------------------------------------------------------
|   USA   |        |         |           |          |        |          |
| England |        |         |           |          |        |          |

标签： excelpivot-table

解决方案

根据您想要聚合结果的方式，您可以通过Power Query在 Excel 2010+ 和 O365 中进行数据透视来轻松完成此操作

算法

通过以下方式打开 PQ 编辑器
- 选择表格中的一个单元格
- 数据 / 获取和转换数据 / 从表/范围
删除 City 列，因为我们不会使用它
选择国家列和“取消透视其他列”
- 这会将您的数据转换为一个三列的表Country, Attribute, Value
使用工作日编号创建自定义列以进行排序
将属性列（日期）转换为仅显示星期几
按工作日编号列排序，然后将其删除。
转动 Attribute 列，并根据需要聚合结果
- 在示例中，我使用了Average聚合函数，并将其显示为整数，但 UI 中的其他选项包括、Sum、Max、Min、Median，Count您甚至可以编写自己的聚合函数

M代码

如果您将其复制/粘贴到高级编辑器中，请务必将第 2 行中的表名称更改为您的数据表的实际名称

检查应用步骤窗口以查看查询的每个步骤发生的情况

注意：代码自最初发布以来已被编辑以清理它并使其更灵活。确保您使用的是最新版本

let
    Source = Excel.CurrentWorkbook(){[Name="Table11"]}[Content],

    //Remove unneeded city column
    #"Removed Columns" = Table.RemoveColumns(Source,{"City"}),

    //Unpivot to generate a three column table
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Country"}, "Attribute", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Country", type text}, {"Attribute", type date}, {"Value", Int64.Type}}),

    //generate weekday number for sorting
    #"Added Custom" = Table.AddColumn(#"Changed Type", "weekdayNum", each Date.DayOfWeek([Attribute])),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"weekdayNum", Order.Ascending}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"weekdayNum"}),

    //change date to day name
    #"Extracted Day Name" = Table.TransformColumns(#"Removed Columns1", {{"Attribute", each Date.DayOfWeekName(_), type text}}),   
    
    //Pivot on weekday name
    #"Pivoted Column" = Table.Pivot(#"Extracted Day Name", List.Distinct(#"Extracted Day Name"[Attribute]), "Attribute", "Value", List.Average),

    //added code since this example doesn't have all the days of the week
        colNames = List.RemoveFirstN(Table.ColumnNames(#"Pivoted Column"),1),
        colTypes = List.Repeat({Int64.Type},List.Count(colNames)),

    //Round to Integer
    #"Changed Type1" = Table.TransformColumnTypes(#"Pivoted Column",
        List.Zip({colNames,colTypes})
        )
in
    #"Changed Type1"

excel - Excel 数据透视表中的相同/重复列名

问题描述

解决方案

推荐阅读