如果您在家中、工作场所或家庭办公室中广泛使用Excel ,您可以完全按照自己的意愿设置工作表。(Excel)但是,每个人在使用Excel(Excel)时都有自己的怪癖和方法。有些人喜欢将数据组织成列,而另一些人喜欢将数据组织成行。
如果有人给您一个Excel文件,而您希望数据以不同的排列方式显示,您可以使用Excel的内置转置(Transpose)功能轻松将列转换为行,将行转换为列。在Excel(Excel)中转置数据基本上有两种方法:复制和粘贴或使用转置功能。尽管两者都会转置您的数据,但它们的工作方式不同,我将在下面解释。
使用 TRANSPOSE 函数进行转置
假设(Suppose)有人给您一个Excel文件,其中的数据按列排列,而您希望数据按行排列。
首先选择要将数据从列转置到行的区域。请注意(Notice),在上面的示例中,数据占用了 A1 到 B6。这是一个 2 x 6 (2×6) 数据表。要选择转置区域,您需要相反的区域或 6 x 2 (6×2) 区域。从单元格A12(或其他任何您想要转置数据的地方)开始,标记一个 6×2 区域(mark out a 6×2 area)。
请注意(Notice),我们选择的单元格包括A12到F13,一个 6×2 的区域。选择此区域后,单击上面的公式栏( formula bar)。在开始输入公式之前,请(Make)确保您选择的区域仍处于选中状态。现在在公式栏中输入以下公式
=transpose(a1:b6)
但不要按 Enter 键(Enter)。将此公式输入Excel(Enter)与(Excel)大多数其他公式不同。当您按下Enter时,您需要按住Ctrl和Shift键。所以按Ctrl + Shift + Enter。您的 Excel 工作表现在应如下所示:
Ctrl + Shift + Enter组合键在公式周围放置一组大括号。这告诉Excel公式的输出将是一个数据数组,而不仅仅是一个单元格。奇怪的是,您不能自己输入大括号;您必须使用Ctrl + Shift + Enter组合键。
当您使用转置功能转置数据时,两组数据都被链接。这意味着如果您更改 A1 中的数据,例如,它也会更改A12中的值。即使单元格有公式,它也会更新两个地方的值。
在下面的示例中,我有一些关于工人、工作时间和总工资的数据。我使用转置函数来转置数据。
我继续更改了一个名称和所有个人的工作时间,如您所见,两组数据都是同步的。
这也意味着,如果您删除原始数据的单元格或行,您将在转置的单元格中得到引用错误!如果您不希望两组数据链接,更好的选择是使用下面的复制和粘贴方法,它复制数据而不是链接它。
使用复制和粘贴转置
在Excel(Excel)中转置数据的更简单方法是使用复制和粘贴功能。数据不会被链接,因此您可以根据需要安全地删除原始数据集。但是,如果您对原始数据集进行更改,它不会反映在转置数据中,因为它只是一个副本。
选择要转置的数据,然后右键单击并选择复制(Copy)或按键盘上的 CTRL + C
现在右键单击要粘贴数据的任何空单元格,然后单击“转(Transpose)置”按钮。如果您只是将鼠标悬停在转置按钮上,它实际上会为您提供工作表上数据的实时预览。
如果更改原始数据集中的任何数据,则不会影响转置后的数据,反之亦然。就是这样。
2 Ways to Use Excel’s Transpose Function
If you work extensively with Excel at home, at work, or іn a home office, you have the luxury of setting up your worksheets exactlу as уou want them. However, еach person hаs their own quirks and methods for working with Excel. Some prefer orgаnizing dаtа into colυmns and others into rows.
If someone gives you an Excel file and you prefer the data to be displayed in a different arrangement, you can easily turn columns into rows and rows into columns using Excel’s built in Transpose function. There are basically two ways to transpose data in Excel: by copying and pasting or by using the transpose function. Even though both will transpose your data, they work differently, which I will explain below.
Transpose using TRANSPOSE Function
Suppose someone gives you an Excel file with the data arranged into columns and you prefer the data to be in rows.
Begin by selecting the area into which you want to transpose the data from columns to rows. Notice in the example above that the data occupies A1 to B6. That’s a 2 by 6 (2×6) data table. To select the area for transposition, you need the opposite or a 6 by 2 (6×2) area. Starting in cell A12 (or wherever else you want the transposed data), mark out a 6×2 area.
Notice that the cells we chose include A12 to F13, a 6×2 area. With this area selected, click on the formula bar above. Make sure the area you selected is still selected before you start typing the formula. Now type the following formula into the formula bar
=transpose(a1:b6)
but don’t hit Enter yet. Entering this formula into Excel differs from most other formulas. You need to hold down the Ctrl and Shift keys when you hit Enter. So press Ctrl + Shift + Enter. Your Excel worksheet should now look like this:
The Ctrl + Shift + Enter key combination put a set of braces around the formula. This tells Excel that the formula’s output will be an array of data rather than just a single cell. Oddly, you cannot type the braces in yourself; you must use the Ctrl + Shift + Enter key combination.
When you use the transpose function to transpose data, both sets of data are linked. This means that if you change the data in A1, for example, it would change the value in A12 too. Even if the cells have formulas, it will update the values in both places.
In the example below, I have some data on workers, hours worked and total pay. I used the transpose function to transpose the data.
I went ahead and changed one name and the hours worked for all the individuals and as you can see, both sets of data are synced.
This also means that if you delete the cells or rows of original data, you’ll get a reference error in the transposed cells! If you don’t want the two sets of data linked, the better option is to use the copy and paste method below, which duplicates the data rather than links it.
Transpose using Copy and Paste
The easier way to transpose data in Excel is to use the copy and paste feature. The data will not be linked, so you can safely delete the original set of data if you like. However, if you make changes to the original set of data, it will not be reflected in the transposed data since it’s just a copy.
Select the data you want to transpose and then right-click and choose Copy or press CTRL + C on your keyboard.
Now right-click on any empty cell where you want to paste the data and click on the Transpose button. If you just hover over the transpose button, it’ll actually give you a live preview of the data on the sheet.
If you change any data in the original set of data, it will not affect the transposed data and vice versa. That’s about it.