entityMap|0|type|IMAGE|mutability|IMMUTABLE|data|imageUrl|https://ask.qcloudimg.com/http-save/actteam/87b09bd0d8f54693f5e6dcd763a6b965.png|imageAlt|1|https://ask.qcloudimg.com/http-save/actteam/9e921f50b313adfae1796bd3b7b7bf31.png|blocks|key|67lof|text|假设我们有：|unstyled|depth|inlineStyleRanges|entityRanges|c8n45|?|atomic|offset|length|96bv9|​在E1输入：|aiklm|=INDEX($A$1:$C$3,ROUNDUP(ROW()/3,0),IF(MOD(ROW(),3)=0,3,MOD(ROW(),3)))|code-block|syntax|js|adf2e|然后向下复制以获取：|fvgdl|bnqab|​使用类似的公式，您可以将任何二维表映射到单行或单列。将单个列或行映射到表中也同样容易。^0|0|0|1|0|0|0|0|0|0|1|1|0^^$0|$1|$2|3|4|5|6|$7|8|9|-4]]|A|$2|3|4|5|6|$7|B|9|-4]]]|C|@$D|E|F|G|2|H|I|12|J|@]|K|@]|6|$]]|$D|L|F|M|2|N|I|13|J|@]|K|@$O|14|P|15|D|16]]|6|$]]|$D|Q|F|R|2|H|I|17|J|@]|K|@]|6|$]]|$D|S|F|T|2|U|I|18|J|@]|K|@]|6|$V|W]]|$D|X|F|Y|2|H|I|19|J|@]|K|@]|6|$]]|$D|Z|F|M|2|N|I|1A|J|@]|K|@$O|1B|P|1C|D|1D]]|6|$]]|$D|10|F|11|2|H|I|1E|J|@]|K|@]|6|$]]]]

Say we have:

<img src="https://i.stack.imgur.com/j3nYP.png" alt="enter image description here">

In E1 enter:

<pre><code>=INDEX($A$1:$C$3,ROUNDUP(ROW()/3,0),IF(MOD(ROW(),3)=0,3,MOD(ROW(),3)))
</code></pre>

and copy down to get:

<img src="https://i.stack.imgur.com/5YEL3.png" alt="enter image description here">

 
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.

entityMap|0|type|LINK|mutability|MUTABLE|data|url|http://www.cpearson.com/excel/TableToColumn.aspx|blocks|key|dci6g|text|上面的答案很好，但我想，Chip+Pearson在这里提供的解决方案(http://www.cpearson.com/excel/TableToColumn.aspx)，是最高级的，因为它立即/自动地：|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|4v2p3|1)自行确定Row/Col+delim值，并立即适用于矩形，例如，上面必须显式地为num+Cols输入3，为num+Rows输入3，并且还必须找出哪个是哪个。而皮尔逊的解决方案会自动实现这一点(例如，rmf的评论/关注)。|8vo0q|2)+Pearson提供了列排序和行排序的两种变体。^0|Z|1C|0|0|0^^$0|$1|$2|3|4|5|6|$7|8]]]|9|@$A|B|C|D|2|E|F|O|G|@]|H|@$I|P|J|Q|A|R]]|6|$]]|$A|K|C|L|2|E|F|S|G|@]|H|@]|6|$]]|$A|M|C|N|2|E|F|T|G|@]|H|@]|6|$]]]]

The answers above are quite good, but IMHO, the solution provided by Chip Pearson here (<a href="http://www.cpearson.com/excel/TableToColumn.aspx" rel="nofollow noreferrer">http://www.cpearson.com/excel/TableToColumn.aspx</a>), is superior is most respects since it immediately/automatically:

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).

2) Pearson provides both variants for Col-ordered and also Row-ordered.

entityMap|0|type|LINK|mutability|MUTABLE|data|url|https://stackoverflow.com/questions/20800820/excel-formulas-for-converting-data-among-column-row-matrix/20800915#20800915|blocks|key|9uudk|text|这一点已经在非常一般的第2项中讨论过了。|unstyled|depth|inlineStyleRanges|entityRanges|d89uh|Excel：列/行/矩阵间数据转换公式：|offset|length|eguoj|目标区域的顶部单元格(例如，$H$1)应包含|style|CODE|6tca|=INDEX($A$1:$C$3,INT((ROW()-ROW($H$1))/3)%2B1,MOD(ROW()-ROW($H$1),3)%2B1)|code-block|syntax|js|8pdob|在哪里$A$1:$C$3包含您的源数据。根据需要向下复制公式。|asm2j|您还可以使用|chnhu|=OFFSET($A$1,INT((ROW()-ROW($H$1))/3),MOD(ROW()-ROW($H$1),3))|en665|如参考文章中所述。^0|0|0|K|0|0|E|4|0|0|3|9|0|0|0^^$0|$1|$2|3|4|5|6|$7|8]]]|9|@$A|B|C|D|2|E|F|13|G|@]|H|@]|6|$]]|$A|I|C|J|2|E|F|14|G|@]|H|@$K|15|L|16|A|17]]|6|$]]|$A|M|C|N|2|E|F|18|G|@$K|19|L|1A|O|P]]|H|@]|6|$]]|$A|Q|C|R|2|S|F|1B|G|@]|H|@]|6|$T|U]]|$A|V|C|W|2|E|F|1C|G|@$K|1D|L|1E|O|P]]|H|@]|6|$]]|$A|X|C|Y|2|E|F|1F|G|@]|H|@]|6|$]]|$A|Z|C|10|2|S|F|1G|G|@]|H|@]|6|$T|U]]|$A|11|C|12|2|E|F|1H|G|@]|H|@]|6|$]]]]

This was already covered in Item 2 from the very general

<a href="https://stackoverflow.com/questions/20800820/excel-formulas-for-converting-data-among-column-row-matrix/20800915#20800915">Excel: Formulas for converting data among column / row / matrix</a> :

The top cell of your target range (say, <code>$H$1</code>) should contain

<pre><code>=INDEX($A$1:$C$3,INT((ROW()-ROW($H$1))/3)+1,MOD(ROW()-ROW($H$1),3)+1)
</code></pre>

where <code>$A$1:$C$3</code> cotains your source data.
Copy the formula downwards as needed.

You could also use

<pre><code>=OFFSET($A$1,INT((ROW()-ROW($H$1))/3),MOD(ROW()-ROW($H$1),3))
</code></pre>

as metioned in the referred article.

entityMap|0|type|LINK|mutability|MUTABLE|data|url|https://www.microsoft.com/en-us/microsoft-365/blog/2015/12/15/learn-how-to-unpivot-static-tables-in-excel-2016/|blocks|key|e96iv|text|我建议你去查一查Excel取消打印选项来完成你的任务。|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|dlmnq|选择您的矩阵|ordered-list-item|v9vo|浏览获取和转换部分中的数据选项卡，然后单击From+Table/Range|style|CODE|am6v0|在新的Power+Query+Editor选择要取消透视的列|3ugoe|浏览任何列部分中的转换选项卡中，单击附近的箭头Unpivot+Columns并选择最佳选项(如果您按照步骤3操作，可以单击Unpivot+only+selected+columns)|d8t7v|关闭加载|2da78|这个过程对于复矩阵特别有用。|9hqsa|查看上面的链接以了解更多信息^0|8|B|0|0|0|L|G|0|0|N|F|1P|T|0|0|0^^$0|$1|$2|3|4|5|6|$7|8]]]|9|@$A|B|C|D|2|E|F|11|G|@]|H|@$I|12|J|13|A|14]]|6|$]]|$A|K|C|L|2|M|F|15|G|@]|H|@]|6|$]]|$A|N|C|O|2|M|F|16|G|@$I|17|J|18|P|Q]]|H|@]|6|$]]|$A|R|C|S|2|M|F|19|G|@]|H|@]|6|$]]|$A|T|C|U|2|M|F|1A|G|@$I|1B|J|1C|P|Q]|$I|1D|J|1E|P|Q]]|H|@]|6|$]]|$A|V|C|W|2|M|F|1F|G|@]|H|@]|6|$]]|$A|X|C|Y|2|E|F|1G|G|@]|H|@]|6|$]]|$A|Z|C|10|2|E|F|1H|G|@]|H|@]|6|$]]]]

I suggest you to check <a href="https://www.microsoft.com/en-us/microsoft-365/blog/2015/12/15/learn-how-to-unpivot-static-tables-in-excel-2016/" rel="nofollow noreferrer">Excel unpivot option</a> to perform your task.
<ol>
<li>Select your matrix</li>
<li>Go through the Get &amp; Transform section in the Data tab and click <code>From Table/Range</code></li>
<li>In the new Power Query Editor select the columns you want to unpivot</li>
<li>Go through the Any Column section in the Transform tab, click the arrow nearby <code>Unpivot Columns</code> and choose the best option (if you follow the step 3 you can click <code>Unpivot only selected columns</code>)</li>
<li>Close &amp; Load</li>
</ol>
This process is useful especially for complex matrices. 
Check the above link for further information

I have the following matrix in Excel:

3 Columns: A, B, C

Row 1: a b c

Row 2: d e f

Row 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

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.

Any ideas?

How to convert a matrix to a single column using Excel

33 天实现自己的 AI 进化论？发文瓜分机械键盘、耳机等万元奖池！点击参加活动：<a href="https://cloud.tencent.com/developer/article/2503022" target="_blank">https://cloud.tencent.com/developer/article/2503022</a> 
 <img src="https://qcloudimg.tencent-cloud.cn/raw/01543e0407db10898a7ee9f420794432.jpg"/>

技术创作特训营有奖征文

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋 

腾讯云AI代码助手

CODING DevOps

Cloud Studio

SDK中心

API中心

命令行工具

文章&问答评论现已支持表情

 我在Excel中有以下矩阵： 3列：A、B、C 第1行：A、b、c 第2行：D e f 第3行：ghi 我需要的是包含所有这些值的单个列。结果应该是这样的： a b c d e f g h i 转置函数在这种情况下不起作用。我尝试了间接函数，但没有找到解决方案。我更喜欢用标准的Excel公式来处理它，而不是用makro。 有什么想法吗？

问如何使用Excel将矩阵转换为单列
EN

回答 4

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何使用Excel将矩阵转换为单列EN

回答 4

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何使用Excel将矩阵转换为单列
EN