☰

在 Excel 中将矩阵样式表转置为 3 列

tag-icon • tag-icon microsoft-excel
在 Excel 中将矩阵样式表转置为 3 列

我在 Excel 中有一个矩阵样式的表格，其中 B1:Z1 是列标题，A2:A99 是行标题。我想将此表转换为 3 列表格（列标题、行标题、单元格值）。新表的顺序无关紧要。

    A   B   C   D            A   B   C            A   B   C   
 1      H1  H2  H3        1  H1  R1  V1        1  H1  R1  V1
 2   R1 V1  V2  V3   =>   2  H1  R2  V4   or   2  H2  R1  V2
 3   R2 V4  V5  V6        3  H1  R3  V7        3  H3  R1  V3
 4   R3 V7  V8  V9        4  H2  R1  V2        4  H1  R2  V4
                          5  H2  R2  V5        5  H2  R2  V5
                          6  H2  R3  V8        6  H3  R2  V6
                          7  H3  R1  V3        7  H1  R3  V7
                          8  H3  R2  V6        8  H2  R3  V8
                          9  H3  R3  V9        9  H3  R3  V8

我一直在尝试使用 OFFSET 函数来创建整个表格，但我觉得需要结合使用 TRANSPOSE 和 V/HLOOKUP。

谢谢

编辑

我设法想出了正确的公式。如果数据在 Sheet1 中（如上例所示），则公式在 Sheet2 中：

[A1] =IF(ROW() <= COUNTA(Sheet1!$B$1:$Z$1)*COUNTA(Sheet1!$A$2:$A$99), OFFSET(Sheet1!$A$1,0,IF(MOD(ROW(),COUNTA(Sheet1!$B$1:$Z$1))=0,COUNTA(Sheet1!$B$1:$Z$1),MOD(ROW(),COUNTA(Sheet1!$B$1:$Z$1)))),"")

[B1] =IF(ROW() <= COUNTA(Sheet1!$B$1:$Z$1)*COUNTA(Sheet1!$A$2:$A$99),OFFSET(Sheet1!$A$1,IF(MOD(ROW(),COUNTA(Sheet1!$A$2:$A$99))=0,COUNTA(Sheet1!$A$2:$A$99),MOD(ROW(),COUNTA(Sheet1!$A$2:$A$99))),0),"")

[C1] =IF(ROW() <= COUNTA(Sheet1!$B$1:$Z$1)*COUNTA(Sheet1!$A$2:$A$99),OFFSET(Sheet1!$A$1,IF(MOD(ROW(),COUNTA(Sheet1!$A$2:$A$99))=0,COUNTA(Sheet1!$A$2:$A$99),MOD(ROW(),COUNTA(Sheet1!$A$2:$A$99))),IF(MOD(ROW(),COUNTA(Sheet1!$B$1:$Z$1))=0,COUNTA(Sheet1!$B$1:$Z$1),MOD(ROW(),COUNTA(Sheet1!$B$1:$Z$1)))),"")

公式仅限于 B1:Z1 作为标题，A2:A99 作为行（如果需要，可以将其增加到最大值）。COUNTA() 公式返回实际具有值的单元格数，这将返回的行数限制为标题*行。否则，由于 MOD 函数，公式可能会无限延续。

答案1

我喜欢这个问题:)

尝试这些函数。将它们插入 h0、r0、v0 并向下拖动

   h   r   v
0 (a) (b) (c)
1 | | | | | |
2 \ / \ / \ /
3  v   v   v

其中：
h = 标题标签
r = 行标签
v = 值标签

（a）=OFFSET($A$1,INT(H2/99)+1,MOD(H2,99)+1,1,1)
（b）=OFFSET($A$1,INT(H2/99)+1,0,1,1)
（c）=OFFSET($A$1,0,MOD(H2,99)+1,1,)

其中 H2 是具有以下值的列：0,1,2,3...

答案2

几周前，我在 StackOverflow 上看到了 @Dick Kusleika 提供的解决方案的一个稍微更通用的版本：在 Excel 2007 中将包含数据列的行转换为包含多行的列。它似乎没有对单个行的引用（ROW()而是使用），但仍然需要对列数进行硬编码。

相关内容