I have a spreadsheet with data like this:
我有这样的数据电子表格:
98929 11.38 3.39 9.10
98930 15.24 2.86 7.05
Each value is in a separate column. I need it to display like this:
每个值都在一个单独的列中。我需要它像这样显示:
98929 11.38
3.39
9.10
98930 15.24
2.86
7.05
Is this possible?
这是可能的吗?
2 个解决方案
#1
1
There is a TRANSPOSE array function that can be used transpose data, but for most scenarios I find it easier to use copy->paste-special->transpose.
有一个转置数组函数可以使用转置数据，但是对于大多数情况，我发现使用copy->paste-special->转置更容易。
http://www.techrepublic.com/blog/microsoft-office/transpose-excel-data-from-rows-to-columns-or-vice-versa/
Edit: Based on the data structure you want, using a pivot table might be a relatively easy way to reshape the data. Assuming the following data structure
编辑:根据您想要的数据结构，使用pivot表可能是一种相对容易的数据重构方法。假设有以下数据结构
Key a b c
98929 11.38 3.39 9.1
98930 12.38 4.39 10.1
98931 13.38 5.39 11.1
Add a pivot table based on your data and
根据您的数据添加一个数据透视表
- Add 'Key' to the rows section
- 向row部分添加“Key”
- Add 'a', 'b', 'c' (etc) to the values section
- 在值部分添加'a'、'b'、'c'(等等)
- Move the 'Values' entry (i.e., summation sign) to the rows sections
- 移动“值”条目(例如。，求和符号)到行部分
- Turn off subtotals, grand totals, and use the 'Tabular' layout
- 关掉小计，总计，并使用“表格”布局
Here's a picture, if the text above isn't clear.
这是一张图片，如果上面的文字不清楚的话。
It will depend a little on your version of Excel, but in the Ribbon, click on the 'Design' tab with the pivot table selected, and chose 'Do not show' under both the subtotals and grand totals options in the upper left hand corner, and select the 'Tabular' option under Report layout.
它会有点依赖你的Excel版本,但在丝带,单击“设计”选项卡选中数据透视表,并选择“不显示”下的小计和总计左上角的选项,并选择“表格”选项下报告布局。
#2
0
Here is one solution, assuming your "Key" values are greater than 9000, and the other column values are not (more generally speaking, assuming there is no intersection between "Key" and other columns).
这里有一个解决方案，假设您的“键”值大于9000，而其他列值不是(更一般地说，假设“键”和其他列之间没有交集)。
Join all columns in just one unique line using the text editor NotePad++. See here, how to do it.
使用文本编辑器NotePad++将所有列合并到一个惟一的行中。看这里，怎么做。
Then, transpose this unique line to one unique column. For example, transpose on column B, starting on cell B3. Then, type on cell A3:
然后，把这唯一的线转到唯一的一列。例如，在B列上的转置，从单元格B3开始。然后，输入单元格A3:
=IF(B3>9000,"Delete This Row",IF(OFFSET(A3,-1,1)>9000,OFFSET(A3,-1,1),""))
Drag the formula down to all rows in column A where there are data on column B.
将公式拖到A列中所有有B列数据的行。
It should yield a result similar to this:
它应该产生类似的结果:
Alright, now filter column A by "Delete This Row" values and delete all filtered rows.
好了，现在通过“删除这一行”值来过滤A列，并删除所有经过过滤的行。
#1
1
There is a TRANSPOSE array function that can be used transpose data, but for most scenarios I find it easier to use copy->paste-special->transpose.
有一个转置数组函数可以使用转置数据，但是对于大多数情况，我发现使用copy->paste-special->转置更容易。
http://www.techrepublic.com/blog/microsoft-office/transpose-excel-data-from-rows-to-columns-or-vice-versa/
Edit: Based on the data structure you want, using a pivot table might be a relatively easy way to reshape the data. Assuming the following data structure
编辑:根据您想要的数据结构，使用pivot表可能是一种相对容易的数据重构方法。假设有以下数据结构
Key a b c
98929 11.38 3.39 9.1
98930 12.38 4.39 10.1
98931 13.38 5.39 11.1
Add a pivot table based on your data and
根据您的数据添加一个数据透视表
- Add 'Key' to the rows section
- 向row部分添加“Key”
- Add 'a', 'b', 'c' (etc) to the values section
- 在值部分添加'a'、'b'、'c'(等等)
- Move the 'Values' entry (i.e., summation sign) to the rows sections
- 移动“值”条目(例如。，求和符号)到行部分
- Turn off subtotals, grand totals, and use the 'Tabular' layout
- 关掉小计，总计，并使用“表格”布局
Here's a picture, if the text above isn't clear.
这是一张图片，如果上面的文字不清楚的话。
It will depend a little on your version of Excel, but in the Ribbon, click on the 'Design' tab with the pivot table selected, and chose 'Do not show' under both the subtotals and grand totals options in the upper left hand corner, and select the 'Tabular' option under Report layout.
它会有点依赖你的Excel版本,但在丝带,单击“设计”选项卡选中数据透视表,并选择“不显示”下的小计和总计左上角的选项,并选择“表格”选项下报告布局。
#2
0
Here is one solution, assuming your "Key" values are greater than 9000, and the other column values are not (more generally speaking, assuming there is no intersection between "Key" and other columns).
这里有一个解决方案，假设您的“键”值大于9000，而其他列值不是(更一般地说，假设“键”和其他列之间没有交集)。
Join all columns in just one unique line using the text editor NotePad++. See here, how to do it.
使用文本编辑器NotePad++将所有列合并到一个惟一的行中。看这里，怎么做。
Then, transpose this unique line to one unique column. For example, transpose on column B, starting on cell B3. Then, type on cell A3:
然后，把这唯一的线转到唯一的一列。例如，在B列上的转置，从单元格B3开始。然后，输入单元格A3:
=IF(B3>9000,"Delete This Row",IF(OFFSET(A3,-1,1)>9000,OFFSET(A3,-1,1),""))
Drag the formula down to all rows in column A where there are data on column B.
将公式拖到A列中所有有B列数据的行。
It should yield a result similar to this:
它应该产生类似的结果:
Alright, now filter column A by "Delete This Row" values and delete all filtered rows.
好了，现在通过“删除这一行”值来过滤A列，并删除所有经过过滤的行。