如何使用Excel将矩阵转换为单列 [英] How to convert a matrix to a single column using Excel
问题描述
我在Excel中有以下矩阵:
I have the following matrix in Excel:
3列: A,B,C
行1: abc
第2行: def
第3行: ghi
我需要的是一个包含所有这些值的列。结果应该如下所示:
What I need is a single column with all these values. The result should look like that:
a
b
c
d
e
f
g
h
我
TRANSPOSE功能不适用于这种情况。我尝试了INDIRECT功能,但没有找到解决方案。我宁愿更喜欢用标准的Excel公式来处理它,而不是使用makro。
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.
任何想法?
推荐答案
说我们有:
在 E1 输入:
=INDEX($A$1:$C$3,ROUNDUP(ROW()/3,0),IF(MOD(ROW(),3)=0,3,MOD(ROW(),3)))
并复制以获取:
使用类似的公式,您可以将任何二维表映射到单个行或单个列。将单个列或行映射到表格同样容易。
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.
这篇关于如何使用Excel将矩阵转换为单列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!