vba Excel中如何将一列转成一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15119335/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I transpose a column into a row in Excel
提问by Pr0no
I have the following Excel worksheet:
我有以下 Excel 工作表：
A
1 foo
2 bar
3 baz
4 bam
(In reality, the column is much, much longer making transponing by hand not an option). How can I transform this sheet into
（实际上，该列要长得多，因此无法手动转发）。我怎样才能把这张表变成
A B C D
1 foo bar baz bam
I've tried the PivotTable function but am unable to get the output I want. I could also write a (php) script to read and transform the Excel, but I cannot imagine this is something that is not easily done in Excel. However, I have been unable to find the answer to this problem. Can you help?
我已经尝试了数据透视表功能，但无法获得我想要的输出。我也可以编写一个 (php) 脚本来读取和转换 Excel，但我无法想象这是在 Excel 中不容易完成的事情。但是，我一直无法找到这个问题的答案。你能帮我吗？
回答by Peter Albert
There are two easy, non-programmatic ways:
有两种简单的非编程方式：
- Copy the data and at the target, use Paste Special->Transpose
- Apply the
TRANSPOSEworksheet function: select the range of the transposed size (in your example 1 row x 4 columns and enter=TRANSPOSE(A1:A4). You need to enter this as an array formula, i.e. press Ctrl-Shift-Enter
- 复制数据并在目标处，使用选择性粘贴->转置
- 应用
TRANSPOSE工作表功能：选择转置大小的范围（在您的示例中为 1 行 x 4 列并输入=TRANSPOSE(A1:A4)。您需要将此作为数组公式输入，即按Ctrl- Shift-Enter
If for some reason you need to do this via code, this VBA code will do:
如果由于某种原因您需要通过代码执行此操作，则此 VBA 代码将执行以下操作：
Sub CopyTransposed(rngSource As Range, rngTargetCell As Range)
rngTargetCell.Resize(rngSource.Columns.Count, rngSource.Rows.Count).Value = _
Application.WorksheetFunction.Transpose(rngSource)
End Sub
You can simply call it like this:
你可以简单地这样称呼它：
CopyTransposed [A1:A4], [C1]
or to be more explicit
或者更明确
CopyTransposed Sheets("SourceSheet").Range("A1:A4"),Sheets("TargetSheet").Range("C1")
回答by chris neilsen
If you only need to transpose data, (ie no formats) try this
如果你只需要转置数据，（即没有格式）试试这个
Sub zx()
Dim rng As Range
Dim dat As Variant
With ActiveSheet ' can be any sheet
Set rng = .[A1:A5] ' get refernce to your data, by any means
dat = rng ' copy data to array
rng.Clear ' clear old data
' paste transposed data
.Range(rng.Cells(1, 1), Cells(rng.Row, rng.Rows.Count)) = Application.Transpose(dat)
End With
End Sub
回答by Kevin
Ctrl+C to copy and paste using Paste Special. Click transpose on the dialog box.
Ctrl+C 使用选择性粘贴进行复制和粘贴。单击对话框上的转置。
This will paste it as values though....
虽然这会将其粘贴为值....
