将Excel列转换为行(文本数据，而不是数字) [英] Convert Excel columns into rows (text data, not numbers)

问题描述

我在Excel中有一个电子表格，该电子表格的第一列包含成员ID"，接下来的六列具有与该成员ID相关的六个变量.

I have a spreadsheet in Excel that contains a "Member ID" in the first column, with six variables, related to this Member ID, in the next six columns.

我需要以某种方式将这些列转换为行，但在每一行的开头仍要具有会员ID"列.

I need to somehow convert these columns into rows, but still have the Member ID column at the beginning of each row.

这是当前的数据(有5000行，因此希望找到一个自动化的解决方案):

Here's the data as it stands (there are 5000 rows, hence hoping to find an automated solution):

成员1 |AAA |BBB |CCC |DDD |EEE |FFF
成员2 |BBB |ZZZ |FFF |AAA |$$ |SSS
成员3 |YYY |FFF |OOO |MMM |PPP |AAA

MEMBER 1 | AAA | BBB | CCC | DDD | EEE | FFF
MEMBER 2 | BBB | ZZZ | FFF | AAA | RRR | SSS
MEMBER 3 | YYY | FFF | OOO | MMM | PPP | AAA

这是我需要的格式:

成员1 AAA
成员1 BBB
成员1 CCC
成员1 DDD
成员1 EEE
成员1 FFF
成员2 BBB
成员2 ZZZ
成员2 FFF
成员2 AAA
会员2 $$
成员2 SSS
成员3 YYY
成员3 FFF
成员3 OOO
成员3 MMM
成员3 PPP
成员3 AAA

MEMBER 1 AAA
MEMBER 1 BBB
MEMBER 1 CCC
MEMBER 1 DDD
MEMBER 1 EEE
MEMBER 1 FFF
MEMBER 2 BBB
MEMBER 2 ZZZ
MEMBER 2 FFF
MEMBER 2 AAA
MEMBER 2 RRR
MEMBER 2 SSS
MEMBER 3 YYY
MEMBER 3 FFF
MEMBER 3 OOO
MEMBER 3 MMM
MEMBER 3 PPP
MEMBER 3 AAA

我尝试按照此问题中的步骤操作:将多个excel列拆分为行，但这似乎仅适用于数字值而不适用于文本.

I attempted to follow the steps in this question: Split multiple excel columns into rows , however that seems to only work for numeric values and not text.

任何人都能给我的帮助将不胜感激，我对如何做到这一点感到困惑.提前非常感谢！

Any help that anyone can give me would be hugely appreciated, I'm stumped as to how to do this. Thanks so much in advance!

推荐答案

这个问题很陈旧，但我只需要它，所以我会回答.

This question is stale but I just needed it, so I'll answer.

我在Quora上找到了Chris Chua的解决方案，如果有帮助，请在那里给他投票:

I found the solution by Chris Chua on Quora, please upvote him there if this is helpful: https://www.quora.com/How-do-I-convert-multiple-column-data-into-a-column-with-multiple-rows-in-excel

在链接断开的情况下，我还将在此处复制并粘贴它:

I'll also copy-paste it here in case of a broken link:

道歉.我看不清文章，但我假设您想取消列标题作为数据集的一部分.

Apologies. I can’t really see the writings clearly, but I’m assuming that you want to unpivot the column headers as part of the data set.

在这里，我建议使用Microsoft Excel的Power Query功能的解决方案.如果您有Excel 2016，则可以在数据" |数据"下找到该功能.取得&转换功能区命令.否则，如果您具有Excel 2013或Professional Plus版本2010，则可以从此处下载Power Query加载项.

Here I am suggesting a solution using Microsoft Excel’s Power Query feature. If you have Excel 2016, the feature is found under Data | Get & Transform ribbon command. Otherwise if you have Excel 2013 or the Professional Plus version of 2010, you can download the Power Query add-in from here.

第1步:选择所有数据，按 Ctrl + T 创建一个表，然后在创建表"下，确保选择了"我的表具有标题" .示例数据创建表对话框

Step 1: Select all the data, press Ctrl+T to create a table, and under Create Table, make sure that My table has headers is selected. Sample data create table dialog

步骤2:在Excel 2016中，转到 Data |取得&转换|从表格开始.对于Excel 2013和2010的Power Query加载项，请在Power Query选项卡功能区中找到 From Table .来自表格"的功能区位置

Step 2: In Excel 2016, go to Data | Get & Transform | From Table. For Power Query add-in for Excel 2013 and 2010, look for something in the Power Query tab ribbon that says From Table. Ribbon location for "From Table"

第3步:将打开Power Query界面.单击编号列的标题，按住Ctrl，然后单击名称列的标题.选中两列之后，右键单击标题，然后选择取消其他列.右键单击并取消透视其他列的屏幕截图

Step 3: The Power Query interface will open up. Click on the header of No. Column, hold Ctrl and click on the header of the Name Column. With both columns now selected, right click on the headers and select Unpivot Other Columns. Right Click, Unpivot Other Columns screencap

第4步:请注意，数据现在已被透视(这就是您想要的).双击属性和值标头将其重命名.

Step 4: Notice that the data is now unpivoted (which is what you wanted). Double click on the Attribute and Value headers to rename them.

第5步:点击主页|关闭并加载.关闭并关闭加载屏幕截图

第6步:Excel将新的清理表加载到新表上.-，您完成了！数据透视表的屏幕截图，这是所需的输出.

Step 6: Excel will load the new cleaned up table onto a new sheet. Ta-da you’re done! Screencap of pivoted table, which is the desired output.

将来要更新任何新数据:

To update any new data in the future:

假设您现在在原始表中有新数据.更新了示例数据表屏幕截图，并插入了新的列，行和值.

Supposed you now have new data in your original table. Updated sample data table screencap with a new column, row and value inserted.

转到新的清理表，右键单击并选择刷新.在透视输出表中右键单击并单击刷新的屏幕截图.

Go to the new cleaned up table, right click and select Refresh. Screencap of right-clicking inside the pivoted output table, clicking Refresh.

新数据将立即更新.

这篇关于将Excel列转换为行(文本数据，而不是数字)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！