excel 转换文本 函数_30天中30个Excel函数：13 –转换

excel 转换文本 函数

Yesterday, in the 30XL30D challenge, we counted columns with the COLUMNS function, and now it's time for something a bit more demanding. For day 13 in the challenge, we'll examine the TRANSPOSE function.

昨天，在30XL30D挑战中，我们使用COLUMNS函数对列进行了计数，现在是时候进行一些更苛刻的了。 在挑战的第13天，我们将检查TRANSPOSE函数。

转换功能 (TRANSPOSE Function)

With the TRANSPOSE function, you can flip things around, changing vertical ranges to horizontal ones, or vice versa. Is that something you need to do? Could you use Paste Special instead? Could other functions do the same thing?

使用TRANSPOSE功能，您可以翻转事物，将垂直范围更改为水平范围，反之亦然。 那是你需要做的吗？ 您可以改用选择性粘贴吗？ 其他功能可以做同样的事情吗？

NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file -- the 30 Excel Functions in 30 Days eBook Kit ($10).

注意：您可以在一个易于使用的单个参考文件中获得这30个函数的全部内容-30天电子书工具包中的30个Excel函数 (10美元)。

So, let's take a look at the TRANSPOSE information and examples, and if you have other tips or examples, please share them in the comments.

因此，让我们看一下TRANSPOSE的信息和示例，如果还有其他提示或示例，请在评论中分享。

功能13：转换 (Function 13: TRANSPOSE)

The TRANSPOSE function returns a horizontal range of cells as a vertical range, or vice versa.

TRANSPOSE函数将单元格的水平范围作为垂直范围返回，反之亦然。

您如何使用TRANSPOSE？ (How Could You Use TRANSPOSE?)

The TRANSPOSE function can change the orientation of data, or be used with other functions:

TRANSPOSE函数可以更改数据的方向，或与其他函数一起使用：

• change horizontal data to vertical
  将水平数据更改为垂直
• show total salary over best consecutive years
  显示连续最佳年份的总薪水

To change data orientation, without links,

要更改数据方向(无链接)，

• use Paste Special > Transpose.
  使用选择性粘贴>转置。

TRANSPOSE语法 (TRANSPOSE Syntax)

The TRANSPOSE function has the following syntax:

TRANSPOSE函数具有以下语法：

• • array is an array or a range of cells to be transposed
    数组是要转置的数组或一系列单元格

  TRANSPOSE(array)

  TRANSPOSE(数组)

透明陷阱 (TRANSPOSE Traps)

• TRANSPOSE must be entered as an array formula, by pressing Ctrl+Shift+Enter.
  必须通过按Ctrl + Shift + Enter输入TRANSPOSE作为数组公式。
• The TRANSPOSE destination range must have the same number of rows and columns, respectively, as the array has columns and rows.
  TRANSPOSE目标范围的行和列数必须分别与数组的列和行数相同。

示例1：将水平数据更改为垂直数据 (Example 1: Change Horizontal Data to Vertical)

If data is arranged horizontally in a worksheet, you can use the TRANSPOSE function to list the data vertically, in a different location. For example, in a summary sheet, or dashboard, a vertical layout might be best. With the TRANSPOSE function, you could link to your original horizontal data, without changing its layout.

如果数据在工作表中水平排列，则可以使用TRANSPOSE函数在其他位置垂直列出数据。 例如，在摘要表或仪表板中，最好采用垂直布局。 使用TRANSPOSE功能，您可以链接到原始水平数据，而无需更改其布局。

To transpose a 2 row x 4 column horizontal range to a 4 row x 2 column vertical range:

要将2行x 4列水平范围转换为4行x 2列垂直范围，请执行以下操作：

1. Select the 8 cells where you want to display the data vertically -- cells B4:C7 in this example.
   选择要垂直显示数据的8个单元格-在此示例中为单元格B4：C7。
2. Type this formula, then enter it as an array formula, by pressing Ctrl+Shift+Enter.
   键入此公式，然后按Ctrl + Shift + Enter将其输入为数组公式。

=TRANSPOSE(B1:E2)

=翻译(B1：E2)

Curly brackets will be automatically added at the start and end of the formula, to show that it is array entered.

括号将自动添加到公式的开头和结尾，以表明它是数组输入。

Instead of using TRANSPOSE, you could use another formula to display the data, such as this INDEX formula. It does not require array entry, and you don't have to select all the destination cells when creating the formula.

除了使用TRANSPOSE，您可以使用其他公式来显示数据，例如此INDEX公式。 它不需要数组输入，并且在创建公式时不必选择所有目标单元格。

=INDEX($B$2:$E$2,,ROW()-ROW(C$4)+1)

= INDEX($ B $ 2：$ E $ 2，，ROW()-ROW(C $ 4)+1)

示例2：没有链接的更改方向 (Example 2: Change Orientation Without Links)

If you just want to change the orientation of your data, without keeping a link to the original data, you can use Paste Special:

如果只想更改数据的方向而又不保留指向原始数据的链接，则可以使用选择性粘贴：

1. Select the original data and copy it
   选择原始数据并复制
2. Select the top left cell of the destination range
   选择目标范围的左上方单元格
3. On the Ribbon's Home tab, click the Paste drop down arrow
   在功能区的“主页”选项卡上，单击“粘贴”下拉箭头
4. Click Transpose
   单击移调
5. (optional) Delete the original data.
   (可选)删除原始数据。

示例3：最佳连续年份的总工资 (Example 3: Total Salary For Best Consecutive Years)

The TRANSPOSE function can be used with other functions, as in this eye-popping formula. It was posted by Harlan Grove, in the Excel newsgroups, in a discussion on calculating the total salary for best 5 consecutive years.

TRANSPOSE函数可以与其他函数一起使用，如此引人注目的公式所示。 它由Harlan Grove在Excel新闻组中发布，讨论了连续5年最佳总工资的计算方法。

=MAX(MMULT(A8:J8,–(ABS(TRANSPOSE(COLUMN(A8:J8)) -COLUMN(OFFSET(A8:J8,0,0,1,COLUMNS(A8:J8)-Number+1)) -(Number-1)/2)<Number/2)))

= MAX(MMULT(A8：J8，–(ABS(TRANSPOSE(COLUMN(A8：J8)))-COLUMN(OFFSET(A8：J8,0,0,1，COLUMNS(A8：J8)-Number + 1))- (Number-1)/ 2)<Number / 2)))

As you can see by the curly brackets in the formula bar, this formula is array entered. Cell A5 is named Number, and I've entered 4, for the number of years this example.

如您在公式栏中的大括号中所见，该公式是数组输入。 单元格A5命名为Number ，对于这个示例的年数 ，我输入了4。

The formula tests the ranges to see if there are enough consecutive COLUMNS. The results of those test (1 or 0) are multiplied by the cell values, to get the total salaries.

该公式测试范围以查看是否有足够的连续COLUMNS。 将这些测试的结果(1或0)乘以单元格值，以得到总工资。

To check the results, in the rows below the salaries, the total salaries for each starting cell are shown, with the maximum value highlighted in yellow. This is a long way to accomplish what the previous formula did in one cell!

为了检查结果，在薪金下面的行中，显示每个起始单元格的总薪水，最大值以黄色突出显示。 这是完成以前的公式在一个单元格中完成的工作的很长的路要走！

下载TRANSPOSE功能文件 (Download the TRANSPOSE Function File)

To see the formulas used in today's examples, you can download the TRANSPOSE function sample workbook. The file is zipped, and is in Excel 2007 file format.

要查看当今示例中使用的公式，可以下载TRANSPOSE函数示例工作簿 。 该文件已压缩，并且为Excel 2007文件格式。

观看TRANSPOSE视频 (Watch the TRANSPOSE Video)

To see a demonstration of the examples in the TRANSPOSE function sample workbook, watch this short Excel video tutorial.

要查看TRANSPOSE函数示例工作簿中示例的演示，请观看此简短的Excel视频教程。

演示地址

翻译自: https://contexturesblog.com/archives/2011/01/14/30-excel-functions-in-30-days-13-transpose/

excel 转换文本 函数