堆叠列并转置 [英] Stack columns and transpose

问题描述

我有这样的数据:

我想在|处分隔每个单元格然后像这样转置它:

I would like to separate each cell at the | and then transpose it like this:

某些sku具有数百个值，因此使用文本到列"并手动转置每个sku并不是时间效应.有没有办法通过公式做到这一点?

Some sku's have hundreds of values, so it is not time effect to use "Text to Columns" and then transpose each sku manually. Is there a way to do this via formulas?

推荐答案

请参考此文章，以了解如何在您的Excel版本上使用 Power Query .它在 Excel 2010 Professional Plus和更高版本中可用.我的演示使用的是 Excel 2016 .

Please refer to this article to find out how to use Power Query on your version of Excel. It is available in Excel 2010 Professional Plus and later versions. My demonstration is using Excel 2016.

步骤是:

1. 使用 Data 标签下的 From Table 函数将您的2列数据表加载到Power查询编辑器中；
2. 突出显示第二列，在 Transform 标签下使用 Split Columns 功能通过自定义定界符 | 和高级选项选择将结果放入行;
3. 关闭&将数据加载到新的工作表中(默认情况下).

1. Use From Table function under the Data tab to load your 2-column data table to the power query editor;
2. Highlight the second column, use Split Columns function under the Transform tab to split the column by custom delimiter |, and in the advanced option choose to put the results into Rows;
3. Close & Load the data to a new worksheet (by default).

如果您有任何疑问，请告诉我.欢呼声:)

Let me know if you have any questions. Cheers :)

这篇关于堆叠列并转置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！