时间戳

首页 > 解决方案 > 如何在数据透视表中将来自 2 个不同列的信息表示为 1？

excel - 如何在数据透视表中将来自 2 个不同列的信息表示为 1？

问题描述

在我的数据中，我有 2 列代表之前访问过的国家和在我正在研究的城市之后访问过的国家。

这是我的数据样本的图片：https ://i.imgur.com/kS4K9uK.png

我想在我的数据透视表中代表与每个城市相关的所有国家（所以在城市之前和之后）。我希望将我的行中的城市和所有可能在之前和之后访问的国家作为我的专栏和我的价值观中的计数。

这是我想要实现的目标的图片，但我只能为其中一个列（在这种情况下为国家之后）做到这一点。我想要相同的格式，但之前和之后都有数据（但重要的是要知道两列中不一定是相同的国家，所以我不能只将国家列中的一个作为头部并且两者都有作为值）：https ://i.imgur.com/PUjhSmB.png

当我将城市放在一行中，将 2 个国家列放在 value 和 columns 中时，很难阅读表格，因为前后都是分开的，甚至可能被视为一对。如果它们不在数据透视表列中，它们只会给我之前和之后的国家数量，而不是国家数量，这不是我想要的。

这是数据透视表的结果图片：https ://i.imgur.com/3j4BD3k.png

我还尝试通过执行«Country before» + «Country after» 来创建一个新字段，但它似乎不起作用，因为数据是文本中的。

标签： excelmergepivot-table

解决方案

好的，我想现在了解输出。您基本上想要计算 B+C 列中每个国家/地区出现的次数，按城市分组。我将提供几种方法，以便您选择最适合您的方法。

最简单的方法

我能想到的最简单的方法是将第二列粘贴到第一列下面，然后在这个新表上进行旋转。

计数

一种更可重复的方法是基本上制作自己的数据透视表并使用该COUNTIF函数来计算每个国家/地区的实例。

=COUNTIFS($A$1:$A$6,$F2,$B$1:$B$6,G$1)+COUNTIFS($A$1:$A$6,$F2,$C$1:$C$6,G$1)

电源查询

最可重复的方法是使用 PowerQuery。这将使您能够通过单击按钮来刷新数据。为此（假设您有 excel 2016），请转到“数据”选项卡，然后在选择数据的情况下单击“从表/范围”。Power Query 窗口将打开。屏幕左上方将是一个带有高级编辑器的按钮。打开后会看到如下代码：

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"City", type text}, {"Country Before", type text}, {"Country After", type text}})
in
    #"Changed Type"

将代码替换为以下代码。请注意，您的表可能被称为不同的东西。您可以在代码的第二行看到它的名称。下面的代码使用“Table1” - 您可以将其替换为您的表格名称。

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"City", type text}, {"Country Before", type text}, {"Country After", type text}}),
    #"Before" = Table.RemoveColumns(#"Changed Type",{"Country Before"}),
    #"After" = Table.RemoveColumns(#"Changed Type",{"Country After"}),
    #"Append" = Table.Combine({#"Before",#"After"}),
    #"Inserted Merged Column" = Table.AddColumn(Append, "Country", each Text.Combine({[Country After], [Country Before]}, ""), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Merged Column",{"Country After", "Country Before"})
in
    #"Removed Columns"

希望有帮助。

推荐阅读