如何将“矩阵”表转换为excel中的每个条目的一行 [英] How do transform a "matrix"-table to one line for each entry in excel
问题描述
1 2 3
axox
bxxo
cooo
,并将其转换为
1 ax
1 bx
1 cx
2 ao
2 bx
2 co
3 ax
3通过在Excel文档中使用一个公式,bo
3 co
使用$分配每行和列的值确实给我正确的结果。每次我必须对公式进行一些手动更改。
任何提示如何写正确的方式?
假设你的矩阵在单元格A1:D4
在A6中:
= OFFSET($ A $ 1; 0 ; QUOTIENT(ROW() - ROW($ A $ 6); 3)+1)
放置:
= OFFSET($ A $ 1; MOD(ROW() - ROW($ A $ 6); 3)+1 ; 0)
在C6 put:
= VLOOKUP($ B6; $ A $ 1:$ D $ 4; MATCH($ A6; $ A $ 1:$ D $ 1; 0); FALSE)
Den拖下(复制公式)A6:C6到A14:C14
PS:3在公式中是指示例的行数(和列)。
I have something like
1 2 3
a x o x
b x x o
c o o o
and want to transform it into lines like
1 a x
1 b x
1 c x
2 a o
2 b x
2 c o
3 a x
3 b o
3 c o
by using a formula in the excel document. Playing with $ for assigning values for each row and column does give me proper results. Each time I have to do some manual changes to the formula. Any hint how to write it the right way?
Suppose that your matrix in the cells A1:D4
in A6 put:
=OFFSET($A$1;0;QUOTIENT(ROW()-ROW($A$6);3)+1)
in B6 put:
=OFFSET($A$1;MOD(ROW()-ROW($A$6);3)+1;0)
in C6 put:
=VLOOKUP($B6;$A$1:$D$4;MATCH($A6;$A$1:$D$1;0);FALSE)
Den drag down (copying formulas) A6:C6 up to A14:C14
(I translate my formulas from italian so there could be some glitch)
PS: 3 in the formulas refers to the number of rows (and column) of the example.
这篇关于如何将“矩阵”表转换为excel中的每个条目的一行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!