I have the following matrix in Excel:
Excel中有如下矩阵:
3 Columns: A, B, C
3列:A、B、C。
Row 1: a b c
第一行:a b c。
Row 2: d e f
第二行:def
Row 3: ghi
第3行:全球健康行动计划
What I need is a single column with all these values. The result should look like that:
我需要的是一个包含所有这些值的列。结果应该是这样的:
a
一个
b
b
c
c
d
d
e
e
f
f
g
g
h
h
i
我
The TRANSPOSE function doesn't work for that case. I tried out the INDIRECT function, but did not find a solution. I would rather prefer to handle it with standard Excel formulas than with a makro.
转置函数在这种情况下不成立。我尝试了间接函数，但没有找到解。我宁愿用标准的Excel公式来处理，也不愿用makro。
Any ideas?
什么好主意吗?
3 个解决方案
#1
6
Say we have:
我们说:
In E1 enter:
在E1输入:
=INDEX($A$1:$C$3,ROUNDUP(ROW()/3,0),IF(MOD(ROW(),3)=0,3,MOD(ROW(),3)))
and copy down to get:
然后拷贝下来得到:
Using similar formulas you can map any two dimensional table into a single row or single column. It is equally easy to map a single column or row into a table.
使用类似的公式，您可以将任何二维表映射为单个行或单个列。将单个列或行映射到表中同样容易。
#2
1
The answers above are quite good, but IMHO, the solution provided by Chip Pearson here (http://www.cpearson.com/excel/TableToColumn.aspx), is superior is most respects since it immediately/automatically:
上面的答案是很好的，但是IMHO，这个由Chip Pearson提供的解决方案(http://www.cpearson.com/excel/TableToColumn.aspx)，是最优的，因为它立即/自动地:
1) Determines the Row/Col delim values on its own, and immediately works for rectangular, e.g. above one must explicitly enter 3 for num Cols and 3 for num Rows, and must also figure out which is which. Whereas Pearson's solution does this automatically (eg. rmf's comment/concern above).
1)自行确定行/Col delim值，并立即适用于矩形，例如，上面必须显式地为num Cols输入3，为num行输入3，还必须确定哪个是哪个。而皮尔森的解决方案则自动地做到这一点。说明了上面的评论/关注)。
2) Pearson provides both variants for Col-ordered and also Row-ordered.
2) Pearson提供coll -ordered和Row-ordered两种变体。
#3
1
This was already covered in Item 2 from the very general
这一点已经在非常一般的项目2中得到了讨论
Excel: Formulas for converting data among column / row / matrix :
Excel:列/行/矩阵之间数据转换公式:
The top cell of your target range (say, $H$1) should contain
目标范围的顶部单元(比如$H$1)应该包含。
=INDEX($A$1:$C$3,INT((ROW()-ROW($H$1))/3)+1,MOD(ROW()-ROW($H$1),3)+1)
where $A$1:$C$3 cotains your source data. Copy the formula downwards as needed.
其中$1:$ 3包含您的源数据。根据需要向下复制公式。
You could also use
您还可以使用
=OFFSET($A$1,INT((ROW()-ROW($H$1))/3),MOD(ROW()-ROW($H$1),3))
as metioned in the referred article.
如上文所述。
#1
6
Say we have:
我们说:
In E1 enter:
在E1输入:
=INDEX($A$1:$C$3,ROUNDUP(ROW()/3,0),IF(MOD(ROW(),3)=0,3,MOD(ROW(),3)))
and copy down to get:
然后拷贝下来得到:
Using similar formulas you can map any two dimensional table into a single row or single column. It is equally easy to map a single column or row into a table.
使用类似的公式，您可以将任何二维表映射为单个行或单个列。将单个列或行映射到表中同样容易。
#2
1
The answers above are quite good, but IMHO, the solution provided by Chip Pearson here (http://www.cpearson.com/excel/TableToColumn.aspx), is superior is most respects since it immediately/automatically:
上面的答案是很好的，但是IMHO，这个由Chip Pearson提供的解决方案(http://www.cpearson.com/excel/TableToColumn.aspx)，是最优的，因为它立即/自动地:
1) Determines the Row/Col delim values on its own, and immediately works for rectangular, e.g. above one must explicitly enter 3 for num Cols and 3 for num Rows, and must also figure out which is which. Whereas Pearson's solution does this automatically (eg. rmf's comment/concern above).
1)自行确定行/Col delim值，并立即适用于矩形，例如，上面必须显式地为num Cols输入3，为num行输入3，还必须确定哪个是哪个。而皮尔森的解决方案则自动地做到这一点。说明了上面的评论/关注)。
2) Pearson provides both variants for Col-ordered and also Row-ordered.
2) Pearson提供coll -ordered和Row-ordered两种变体。
#3
1
This was already covered in Item 2 from the very general
这一点已经在非常一般的项目2中得到了讨论
Excel: Formulas for converting data among column / row / matrix :
Excel:列/行/矩阵之间数据转换公式:
The top cell of your target range (say, $H$1) should contain
目标范围的顶部单元(比如$H$1)应该包含。
=INDEX($A$1:$C$3,INT((ROW()-ROW($H$1))/3)+1,MOD(ROW()-ROW($H$1),3)+1)
where $A$1:$C$3 cotains your source data. Copy the formula downwards as needed.
其中$1:$ 3包含您的源数据。根据需要向下复制公式。
You could also use
您还可以使用
=OFFSET($A$1,INT((ROW()-ROW($H$1))/3),MOD(ROW()-ROW($H$1),3))
as metioned in the referred article.
如上文所述。