Contents
Yesterday in the marathon 30 Excel functions in 30 days we counted the number of columns in the range using the function COLUMNS (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 TRANSPOSE (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 TRANSPOSE (TRANSP). If you have additional information or examples, please share them in the comments.
Function 13: TRANSPOSE
Function TRANSPOSE (TRANSPOSE) returns a horizontal range of cells as a vertical range, or vice versa.
How can the TRANSPOSE function be used?
Function TRANSPOSE (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:
- Use paste Special (Special Paste) > Transpose (Transpose).
Syntax TRANSPOSE (TRANSP)
Function TRANSPOSE (TRANSPOSE) has the following syntax:
TRANSPOSE(array)
ТРАНСП(массив)
- array (array) is the array or range of cells to be transposed.
Traps TRANSPOSE (TRANSPOSE)
- Function TRANSPOSE (TRANSPOSE) must be entered as an array formula, by pressing Ctrl + Shift + Enter.
- The range that will result from the transformation by the function TRANSPOSE (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 TRANSPOSE (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 TRANSPOSE (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:
- Select 8 cells where you want to place the resulting vertical range. In our example, these will be cells B4:C7.
- Enter the following formula and turn it into an array formula by clicking Ctrl + Shift + Enter.
=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.
Instead of TRANSPOSE (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)
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:
- Select source data and copy it.
- Select the top left cell of the area where you want to place the result.
- On the Advanced tab Home (Home) click on the command dropdown menu paste (Insert).
- Выберите Transpose (Transpose).
- Delete the original data (optional).
Example 3: Best Total Salary in Past Years
Function TRANSPOSE (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)
Как можно понять по фигурным скобкам в строке формул – это формула массива. Ячейка A5 названа Number и в этом примере число 4 введено, как значение для количества лет.
Формула проверяет диапазоны, чтобы увидеть достаточно ли в них последовательных столбцов. Результаты проверки (1 или 0) умножаются на значения ячеек, чтобы получить суммарный объём заработной платы.
Для проверки результата на рисунке ниже в строке под значениями зарплат показаны суммарные значения для каждой стартовой ячейки, при этом максимальное значение выделено жёлтым. Это более долгий путь к тому же результату, что предыдущая формула массива получает в одной ячейке!
