30 Tayo awọn iṣẹ ni 30 ọjọ: TRANSPOSE

Lana ni Ere-ije gigun Awọn iṣẹ Excel 30 ni awọn ọjọ 30 we counted the number of columns in the range using the function Awọn ọwọn (NUMBERCOLUMN), and now it’s time for something more in demand.

On the 13th day of the marathon, we will devote to the study of the function RANRAN (TRANSP). With this function, you can rotate your data by turning vertical areas into horizontal ones and vice versa. Do you have such a need? Can you do this using a special insert? Can other functions do it?

So, let’s turn to information and examples on the function RANRAN (TRANSP). If you have additional information or examples, please share them in the comments.

Function 13: TRANSPOSE

iṣẹ RANRAN (TRANSPOSE) returns a horizontal range of cells as a vertical range, or vice versa.

How can the TRANSPOSE function be used?

iṣẹ RANRAN (TRANSP) can change the orientation of the data, as well as work in conjunction with other functions:

  • Change the horizontal layout of data to vertical.
  • Show the best total wages in recent years.

To change the data orientation without creating links to the original data:

  • lilo lẹẹmọ Pataki (Papa pataki) > Gbigbe (Transpose).

Syntax TRANSPOSE (TRANSP)

iṣẹ RANRAN (TRANSPOSE) has the following syntax:

TRANSPOSE(array)

ТРАНСП(массив)

  • orun (array) is the array or range of cells to be transposed.

Traps TRANSPOSE (TRANSPOSE)

  • iṣẹ RANRAN (TRANSPOSE) must be entered as an array formula, by pressing Konturolu + Yi lọ yi bọ + Tẹ.
  • The range that will result from the transformation by the function RANRAN (TRANSPOSE) must have the same number of rows and columns as the original range has columns and rows respectively.

Example 1: Turning Horizontal Data into Vertical Data

If the data is horizontal in an Excel sheet, you can apply the function RANRAN (TRANSPOSE) to convert them to a vertical position, but in a different place on the sheet. For example, in the final table of benchmarks, a vertical arrangement would be more convenient. Using the function RANRAN (TRANSPOSE), you can reference the original horizontal data without changing its location.

To transpose the horizontal range 2 × 4 into the vertical range 4 × 2:

  1. Select 8 cells where you want to place the resulting vertical range. In our example, these will be cells B4:C7.
  2. Enter the following formula and turn it into an array formula by clicking Konturolu + Yi lọ yi bọ + Tẹ.

=TRANSPOSE(B1:E2)

=ТРАНСП(B1:E2)

Curly braces will automatically be added at the beginning and end of the formula to indicate that an array formula has been entered.

30 Tayo awọn iṣẹ ni 30 ọjọ: TRANSPOSE

Dipo RANRAN (TRANSPOSE), You can use another function to transform the data, for example, INDEX (INDEX). It doesn’t require you to enter an array formula, and you don’t have to select all the cells in the target area when creating the formula.

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

=ИНДЕКС($B$2:$E$2;;СТРОКА()-СТРОКА(C$4)+1)

30 Tayo awọn iṣẹ ni 30 ọjọ: TRANSPOSE

Example 2: Change orientation without links

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

  1. Select source data and copy it.
  2. Select the top left cell of the area where you want to place the result.
  3. Lori To ti ni ilọsiwaju taabu Home (Home) click on the command dropdown menu jẹun (Fi sii).
  4. Jọwọ yan Gbigbe (Transpose).
  5. Delete the original data (optional).

30 Tayo awọn iṣẹ ni 30 ọjọ: TRANSPOSE

Example 3: Best Total Salary in Past Years

iṣẹ RANRAN (TRANSP) can be used in combination with other features, such as in this stunning formula. It was posted by Harlan Grove in the Excel News Bloc in a discussion about calculating the best total wage for the past 5 years (in a row!).

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

=МАКС(МУМНОЖ(A8:J8; --(ABS(ТРАНСП(СТОЛБЕЦ(A8:J8))-СТОЛБЕЦ(СМЕЩ(A8:J8;0;0;1;ЧИСЛСТОЛБ(A8:J8)-Number+1))-(Number-1)/2)

30 Tayo awọn iṣẹ ni 30 ọjọ: TRANSPOSE

Как можно понять по фигурным скобкам в строке формул – это формула массива. Ячейка A5 названа Number и в этом примере число 4 введено, как значение для количества лет.

Формула проверяет диапазоны, чтобы увидеть достаточно ли в них последовательных столбцов. Результаты проверки (1 или 0) умножаются на значения ячеек, чтобы получить суммарный объём заработной платы.

Для проверки результата на рисунке ниже в строке под значениями зарплат показаны суммарные значения для каждой стартовой ячейки, при этом максимальное значение выделено жёлтым. Это более долгий путь к тому же результату, что предыдущая формула массива получает в одной ячейке!

30 Tayo awọn iṣẹ ni 30 ọjọ: TRANSPOSE

Fi a Reply