excel - 如何将多维excel表格转换为一维？

Question

我正在使用的表看起来像这样

X    x1    x1    x1    x1    x1    x1    x1    x1    x2    x2    x2    x2   
Y    y1    y1    y1    y1    y2    y2    y2    y2    y1    y1    y1    y1
Z    z1    z2    z3    z4    z1    z2    z3    z4    z1    z2    z3    z4
30%  23    22    25    19    24    27    22    32    21    19    31    29 
31%  25    23    27    22    26    29    24    33    24    22    33    31
32%  29    26    31    25    28    33    27    36    27    25    35    33
.    .     .     .     .     .     .     .     .     .     .     .     .
.    .     .     .     .     .     .     .     .     .     .     .     .
.    .     .     .     .     .     .     .     .     .     .     .     .
.    .     .     .     .     .     .     .     .     .     .     .     .
300% 500   520   613   476   512   312   324   754   432   345   421   444

我想要实现的是这样的

X   Y   Z   Per. Value
x1  y1  z1  30%  23
x1  y1  z1  31%  25
x1  y1  z1  32%  29
.   .   .   .    .
.   .   .   .    .
x1  y1  z1  300% 500
x1  y1  z2  30%  22
x1  y1  z2  31%  23
x1  y1  z2  32%  26
.   .   .   .    .
.   .   .   .    .
x1  y2  z1  30%  24
x1  y2  z1  31%  26
.   .   .   .    .
.   .   .   .    .
x1  y2  z4  30%  32
x1  y2  z4  31%  33  
.   .   .   .    .
.   .   .   .    .
.   .   .   .    .
x10 y3  z7  300% 431

我尝试使用此线程中给出的宏 How to "flatten" or "collapse" a 2D Excel table into 1D? ，但无法让它工作，可能是因为我的 table 不仅仅是 2D，更像是 4D。我不是宏观专家。

有什么方法可以将第一个表转换为第二个表、宏或其他方式。我意识到它大大增加了行数，但这不是问题。

Answer 1

如果这不必用 VBA 完成，这里有一个解决方案:

我们将使用索引和匹配与自定义构建的键表进行匹配查找。

1. 第 1 行

我在您的数据集顶部插入了一行，以便在进行列查找时与之匹配:
该公式在 B1 中显示为文本，但应用于第 1 行的其余部分。



2. 第 20 行

在第 20 行，我有以下内容:

A20:=B20 & C20 & D20
B20:x1
C20:y1
D20:z1
E20:0.3
F20:=INDEX($C$5:$N$12,MATCH(E20,$B$5:$B$12),MATCH(A20,$C$1:$N$1,0))
F 列使用 index(array, rowValue, columnValuearray是您的数据表的位置。rowValue是百分比列与数据表左侧标题列的匹配。columnValue在与我在第一步插入的标题行匹配时使用连接的 xyz 列。



3. 第 21 行

现在你所要做的就是建立你的 table 。这是第 21 行，从右到左。

E 列:您希望将 E 列每行增加 1%，除非您将其增加到 301%，在这种情况下您需要重新开始:
=IF(E20+0.01 < 3.001 , E20 + 0.01 , 0.3)
实际上 - 当我们添加那么多 0.01(其中 270 个，我们会得到一点浮点漂移，所以我每次都会四舍五入最接近的 0.01:
=MROUND(IF(E20+0.01<3.001,E20+0.01,0.3),0.01)
D 列:您希望 D 列的值仅在 E 列重置为 0.3 时才更改。否则，保持不变:
IF(E21=0.3,IF(D20="z4","z1","z"&RIGHT(D20,1)+1),D20)
C 列:类似地，D 列仅在 E 和 D 刚刚重置时才发生变化。
=IF(AND(D21="z1",E21=0.3),IF(C20="y1","y2","y1"),C20)
B 列:同样，此列仅在其他三个全部重置时才会更改:
=IF(AND(C21="y1",D21="z1",E21=0.3),"x"&RIGHT(B20,1)+1,B20)
(请注意，如果您的数据超过 x!0，则您必须稍微修改 Right( 公式。)

现在只需从第 21 行填写到第 21,701 行。应该不错

结果

我没有完整的数据表来提供它，所以有很多漏洞。看起来它在有数据的地方非常符合您的意图(请注意，我隐藏了一些行，因为它们是(预期的) N/A 的。