Excel 数据透视表:显示值而不是摘要

Excel 数据透视表:显示值而不是摘要

我有一张这样的数据表:

ID  |Name   |Brand  |Model  |License
------------------------------------
1   |Foo    |KOM    |830 AC |A
1   |Foo    |KOM    |930 E  |B
1   |Foo    |CAT    |980 H  |B
2   |Bar    |KOM    |930 E  |A
2   |Bar    |CAT    |980 H  |A
3   |Baz    |P&H    |280 XB |B

ID 和名称绑定在一起(ID 1 始终为 Foo),并且一对名称和型号永远不会有多个许可证)

我想使用数据透视表将其变成这样:

    |       |CAT    |KOM            |P&H
ID  |Name   |980 H  |830 AC |930 E  |280 XB
-------------------------------------------
1   |Foo    |B      |A      |B      |
2   |Bar    |A      |       |A      |
3   |Baz    |       |       |       |B

这是我目前得到的信息:

数据透视表

但:

  • Ids 和 Names 位于不同的行
  • 它显示许可证的数量(始终为 1),而不是显示值

有没有办法让它按照我想要的方式工作?

答案1

不幸的是,数据透视表无法返回文本。它们只是聚合而已。

但是,还是有解决方法的。最简单的方法是使用“Helper”列和 VLOOKUP。请参阅下面的屏幕截图。

首先,在“许可证”列的左侧插入一个“助手”列,将 ID、名称、品牌和型号连接起来。

接下来,构建与之前相同的数据透视表,但选择“经典视图”,以便您的布局与第二张屏幕截图相同。选择整个数据透视表,然后选择“复制、选择性粘贴、值”。这将保留数据透视表布局,但会删除数据透视表功能。

最后,按照指示使用 VLOOKUP。密切注意公式中 $ 符号的位置,以便保留绝对引用。屏幕截图显示了单元格 S13 中的公式;根据需要将其复制过来并向下复制。

(在数据透视表布局中,您还需要填写“品牌”行中的空白。在下面的屏幕截图中,单元格 U11 中有一个公式:= T11)。

不可否认,如果您的源数据扩大,您将需要扩展此技术。

其他方法涉及表、INDEX+MATCH 和数组公式。

在此处输入图片描述

答案2

好的,正如你所看到的这个问题,您可以按照以下步骤将一列值转置为另一列值:

  • 在 Power Query 编辑器中打开表
    • 将数据制成表格
    • 选择表格
    • 选择数据 -> 从表/范围
  • 使用 Power Query 进行转置
    • 选择主题和课程列
    • 选择“变换”选项卡:
    • 选择数据透视列,你可能需要悬停才能找到它
    • 值列 = 课程选择
    • 高级选项 -> 不聚合
  • 将其带回您的电子表格
    • 主页选项卡(仍在 Power Query 中)
    • 获取数据 -> 关闭并加载..

看起来像这样

为了得到完整的答案,你需要将品牌添加到标题行

相关内容