我有一个交叉表,我想自动将其转换为表格,我该如何做呢?请注意,我很乐意使用 Excel 公式或 power 查询(我认为它曾经被称为 M),但不想要 VBA 脚本。此外,如果我添加新值或删除值,我应该能够刷新输出表。
例如
交叉表
第 1 栏 | 第2栏 | 第3栏 | |
---|---|---|---|
第 1 行 | X | ||
第 2 行 | X | ||
第3行 | D | 是 |
转换后的输出
列名 | 行名称 | 价值 |
---|---|---|
第 1 栏 | 第 1 行 | X |
第 1 栏 | 第3行 | D |
第2栏 | 第 2 行 | X |
第3栏 | 第3行 | 是 |
我想这样做的原因是,我有一个情况,即更容易将数据捕获到交叉表中,但更适合以表格格式进行分析。此外,数据捕获和分析不是按顺序发生的,因此我想简化更新。
您可以使用Excel 的Power Query中的Unpivot Columns轻松完成此操作。
然后,您可以在添加到初始工作表后通过按“查询”选项卡中的“刷新”来刷新表数据(或者/并且您可以将其设置为定期自动更新。)
有多种方法可以实现这一点,有些方法比其他方法更巧妙(但更难理解)。这是一个老学校的。
如果交叉表正文中有C列和R行,第一步是生成所有可能的列/行组合的列表。起始点是第 1 列和第 1 行,简写为 (1,1)。将这两个值放入单元格
A2
和中B2
,单元格A1:C1
分别具有标签“列”、“行”和“值”。输入使用R
B3
的硬编码=IF(B2=R,1,B2+1)
值(例如,如果有 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 列中的值而不是公式。正如我在上面指出的,有很多不同的方法可以完成您的任务,但这是一种非常简单易行的方法。
不太容易理解但更巧妙的是:
它使用动态数组并需要 Excel 2021 或 365,但本质上遵循与最初给出的更简单的解决方案非常相似的方法。
交叉表的主体位于
G2:I6
上面屏幕截图所示的范围内,并分配给函数中的变量ctLET()
。同样,在
LET()
c和r中,分别表示交叉表中的列数和行数,lis是从 1 计数到c × r 的列数组,而cols和rows将lis转换为之前在标记的列中找到的值在早期的解决方案中分别是“列”和“行”。allvals使用该
INDEX()
函数获取交叉表中值的单列列表,其中allna#N/A
为交叉表中任何未使用的元素插入。keep是一个布尔值数组,标识allna不等于 的元素#N/A
。在
HSTACK()
let 的最后部分,将列数组cols、rows和allna并排放入一个 3 列数组中,而FILTER()
环绕此数组的 过滤掉与原始交叉表中未使用的单元格相对应的任何行。