☰

如何在不使用宏的情况下将交叉表转换为 Excel 中的表格？

tag-icon • tag-icon microsoft-excel
如何在不使用宏的情况下将交叉表转换为 Excel 中的表格？

我有一个交叉表，我想自动将其转换为表格，我该怎么做呢？请注意，我很乐意使用 excel 公式或 power query（我认为它以前被称为 M），但不想使用 VBA 脚本。此外，如果我添加新值或删除值，我应该能够刷新输出表。

例如

交叉表

第 1 列 第 2 列 第 3 列
第 1 行 X
第 2 行 X
第 3 行 德 是

转换后的输出

列名称 行名称 价值
第 1 列 第 1 行 X
第 1 列 第 3 行 德
第 2 列 第 2 行 X
第 3 列 第 3 行 是

我想这样做的原因是，我遇到过一种情况，将数据捕获到交叉表中更容易，但以表格形式进行分析更方便。此外，数据捕获和分析不是按顺序进行的，所以我想简化更新。

答案1

您可以使用以下方式轻松完成此操作逆透视列在 Excel 中Power Query。

  1. 选择数据范围
  2. 右键单击并选择从表/范围获取数据...，并确保我的表格有标题复选框被勾选（这将打开Power Query 编辑器与您的数据/范围）
  3. 选择数据列（选择第 1 列然后按住 Shift 键选择第 3 列）
  4. 在下面转换按逆透视列
  5. 确保外观符合您的要求后，点击加载并保存并在新工作表中查看新创建的表格

然后，您可以在添加到初始工作表后通过按以下方式刷新表格数据：刷新在查询选项卡中（或者您可以将其设置为定期自动更新。）

在此处输入图片描述

答案2

有多种方法可以解决这个问题，有些方法比其他方法更巧妙（但更难理解）。以下是比较老派的方法。

如果你有C列和R交叉表主体中的行，第一步是生成所有可能的列/行组合的列表。起点是第 1 列和第 1 行，简写为 (1,1)。将这两个值放入单元格A2和中B2，单元格A1:C1分别带有标签“列”、“行”和“值”。

使用以下硬编码值输入B3公式
=IF(B2=R,1,B2+1)
R（例如，如果您有 5 行，则为 5）或作为绝对单元格引用（即使用 $ 符号），其中包含R如果这是你喜欢的。

输入A3公式
=IF(B3=1,1+A2,A2)。

向下复制范围A3:B3，直到获得最后一对值C和R位于列表末尾的A和B列中。

您现在应该有一个列表C×R包含所有可能的列/行组合的行。列表从 (1,1)、(1,2) 开始，一直到 (1,R)，然后是 (2,1)，(2,2)，...(2，R）...并完成（C,1),（C,2),...,(C，R）

下一步是从交叉表主体中提取值。由于交叉表中的每个单元格都未填充，因此这一步略显复杂。

使用名称CrossTab来表示交叉表主体的范围，输入公式

=IF(ISBLANK(INDEX(CrossTab,B2,A2)), NA(), INDEX(CrossTab,B2,A2))

在单元格中C2并向下复制到列表末尾的列A和B。

如果您不想命名范围，那么CrossTab您可以使用交叉表主体的绝对单元格引用范围$G$2:$I$6来代替CrossTab上述公式。

现在，您已拥有所需列表格式的交叉表中的所有数据，但交叉表中的任何空单元格均由错误#N/A值表示。

最后一步是使用 Excel 的列表过滤功能（通过功能区的数据选项访问）过滤掉不需要的#N/A，然后只需将过滤后的列表复制并粘贴到工作簿中的方便位置即可。粘贴的值将是所有 3 列中的值，而不是公式。

正如我上面提到的，有很多不同的方法可以完成你的任务，但这是一个相当直接和简单的方法。

将交叉表转换为列表

有些事情不太容易理解，但有点巧妙，那就是：

=LET(
    ct, G2:I6,
    r, ROWS(ct),
    c, COLUMNS(ct),
    lis, SEQUENCE(r * c, 1, ),
    cols, 1 + INT((lis - 1) / r),
    rows, 1 + MOD(lis - 1, r),
    allvals, INDEX(ct, rows, cols),
    allna, IF(
        ISBLANK(allvals),
        NA(),
        allvals
    ),
    keep, NOT(ISNA(allna)),
    FILTER(
        HSTACK(cols, rows, allna),
        keep
    )
)

它使用动态数组并且需要 Excel 2021 或 365，但本质上，它遵循与最初给出的更简单的解决方案非常类似的方法。

交叉表的主体在范围内，G2:I6如上面的屏幕截图所示，并被分配给变量电脑断层扫描在LET()函数中。

此外LET() C和r分别是交叉表中的列数和行数，利斯是一个列数组，从 1 计数到C×r同时列和行转变利斯分别为先前解决方案中标记为“列”和“行”的列中找到的值。

全部值使用该INDEX()函数获取交叉表中值的单列列表，其中阿拉纳插入#N/A交叉表的任何未使用的元素。保持是一个布尔数组，用于标识阿拉纳不等于#N/A。

HSTACK()在 let 的最后一部分中放置列数组列，行和阿拉纳并排放入一个 3 列数组中，同时FILTER()环绕该数组的会过滤掉与原始交叉表中未使用的单元格相对应的任何行。

相关内容