如何在Excel中将列转置为行 [英] How do I transpose a column into a row in Excel

问题描述

我有以下Excel工作表:

I have the following Excel worksheet:

   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

我尝试了数据透视表功能，但无法获取所需的输出.我也可以编写一个(php)脚本来读取和转换Excel，但是我无法想象这在Excel中不容易完成.但是，我一直找不到这个问题的答案.你能帮忙吗?

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?

推荐答案

有两种简单的非编程方式:

There are two easy, non-programmatic ways:

1. 复制数据并在目标位置使用选择性粘贴-> 转置
2. 应用TRANSPOSE工作表功能:选择转置大小的范围(在您的示例中为1行x 4列，然后输入=TRANSPOSE(A1:A4).您需要将其作为数组公式输入，即按 Ctrl - Shift -输入

1. Copy the data and at the target, use Paste Special->Transpose
2. Apply the TRANSPOSE worksheet 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

如果出于某些原因需要通过代码执行此操作，则此VBA代码将执行以下操作:

If for some reason you need to do this via code, this VBA code will do:

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] 

或更明确地说

CopyTransposed Sheets("SourceSheet").Range("A1:A4"),Sheets("TargetSheet").Range("C1")

这篇关于如何在Excel中将列转置为行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！