excel - 如何从看起来像数据透视表的非数据透视矩阵中提取列表？

Question

我有一个非枢轴矩阵，我想将其转换为一个列表，其中一列中表示的列变量作为每一行的特征。但是，有两个单独的字段可以按列检查并构建到单独的列中。

我试图将矩阵变成一个枢轴，这样我就可以在那里解构它，但它不会接受两个不同的列变量。

此外，我尝试使用索引公式根据我的值来自哪个列来提取每个变量行中的值，但它提供了#VALUE!错误。

|  Option 1   | A  | A  | A  | B  |  B  | B  | C  | C  | C  |    
| Option 2    | X  | Y  | Z  | X  | Y   | Z  | X  | Y  | Z  |    
| Houston     | 45 | 40 | 85 | 39 | 2   | 24 | 77 | 82 | 35 |    
| Tallahassee | 20 | 68 | 23 | 94 | 100 | 77 | 99 | 46 | 31 |    
| Albany      | 83 | 93 | 44 | 62 | 39  | 62 | 43 | 83 | 61 |

正如您在上面看到的 - 除了两个列变量之外，每个值都有一行。我想将其转换为如下所示 - 城市和每个变量作为一行的值。

|    City     | Option 1 | Option 2 | Value |    
| Houston     | A        | X        |    45 |    
| Houston     | A        | Y        |    40 |    
| Tallahassee | A        | X        |    20 |    
| Tallahassee | A        | Y        |    68 |    
| Albany      | C        | Y        |    83 |    
| Albany      | C        | Z        |    61 |

如果我能澄清一下，请回复!谢谢!

Answer 1

这可以通过 Power Query 完成。首先，您需要一个公式，将前两行数据组合到一个单元格中，就像这个一样，复制到右侧。

=D2&" - "&D3

第一列使用“城市”之类的标签。现在使用 Data > From Table/Range 将数据带入 Power Query 并执行 不是 勾选“我的表格有标题”框。然后，如果您需要使用新数据重复该过程，Excdl 将使用“Column1，Column2”等作为列名和公式保持不变。

在 Power Query 编辑器中，执行以下步骤

使用第一行作为标题(主页功能区)

选择 City 列并使用转换功能区 > Unpivot 下拉菜单 > unpivot other columns

在城市列中使用过滤器取消勾选 Option1 和 Option2

选择属性列并使用转换功能区 > 拆分列使用自定义分隔符 - 拆分列(即空格破折号空格，即电子表格中用于组合选项行的公式中使用的相同分隔符)

重命名列并保存查询。

屏幕截图显示了使用第 2 行中的公式转换为表格的源数据。绿色表格是 Power Query 的结果，通常位于不同的工作表上。您现在可以在同一个表中使用新数据并简单地刷新查询。

Power Query 在 Excel 2016 及更高版本中称为 Get & Transform，可作为 Excel 2010 和 2013 的加载项安装。