30 функций Excel за 30 дней: ТРАНСП (TRANSPOSE)

Вчера в марафоне 30 функций Excel за 30 дней мы считали количество столбцов в диапазоне, используя функцию COLUMNS (ЧИСЛСТОЛБ), а теперь пришло время для чего-то более востребованного.

13-й день марафона мы посвятим исследованию функции TRANSPOSE (ТРАНСП). При помощи этой функции Вы можете поворачивать свои данные, превращая вертикальные области в горизонтальные и наоборот. У Вас возникает такая необходимость? Сможете сделать это, используя специальную вставку? Смогут ли это сделать другие функции?

Итак, обратимся к информации и примерам по функции TRANSPOSE (ТРАНСП). Если у Вас есть дополнительные сведения или примеры, пожалуйста, делитесь ими в комментариях.

Функция 13: TRANSPOSE (ТРАНСП)

Функция TRANSPOSE (ТРАНСП) возвращает горизонтальный диапазон ячеек как вертикальный или наоборот.

Как можно использовать функцию TRANSPOSE (ТРАНСП)?

Функция TRANSPOSE (ТРАНСП) может изменять ориентацию данных, а также работать совместно с другими функциями:

• Изменить горизонтальное расположение данных на вертикальное.
• Показать лучший общий объём заработной платы за последние годы.

Чтобы изменить ориентацию данных, не создавая ссылок на исходные данные:

• Используйте Paste Special (Специальная вставка) > Transpose (Транспонировать).

Синтаксис TRANSPOSE (ТРАНСП)

Функция TRANSPOSE (ТРАНСП) имеет следующий синтаксис:

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

• array (массив) – это массив или диапазон ячеек, которые нужно транспонировать.

Ловушки TRANSPOSE (ТРАНСП)

• Функция TRANSPOSE (ТРАНСП) должна быть введена как формула массива, нажатием Ctrl+Shift+Enter.
• Диапазон, который получится в результате преобразования функцией TRANSPOSE (ТРАНСП), должен иметь то же количество строк и столбцов, сколько столбцов и строк соответственно имеет исходный диапазон.

Пример 1: Превращаем горизонтальные данные в вертикальные

Если на листе Excel данные расположены горизонтально, Вы можете применить функцию TRANSPOSE (ТРАНСП), чтобы преобразовать их в вертикальное положение, но уже в другом месте листа. Например, в итоговой таблице контрольных показателей вертикальное расположение будет более удобным. Используя функцию TRANSPOSE (ТРАНСП), Вы можете сослаться на исходные горизонтальные данные, не меняя их расположение.

Чтобы транспонировать горизонтальный диапазон 2х4 в вертикальный диапазон 4х2:

1. Выделите 8 ячеек, куда Вы хотите разместить полученный вертикальный диапазон. В нашем примере это будут ячейки B4:C7.
2. Введите следующую формулу и превратите её в формулу массива нажатием Ctrl+Shift+Enter.

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

Автоматически будут добавлены фигурные скобки в начале и в конце формулы, чтобы показать, что введена формула массива.

Вместо TRANSPOSE (ТРАНСП), Вы можете использовать другую функцию для преобразования данных, например, INDEX (ИНДЕКС). Она не потребует ввода формулы массива, и Вам не придётся выделять все ячейки конечной области, при создании формулы.

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

Пример 2: Изменяем ориентацию без ссылок

Если Вы просто хотите изменить ориентацию Ваших данных, не сохраняя ссылку на изначальные данные, Вы можете использовать специальную вставку:

1. Выберите исходные данные и скопируйте их.
2. Выберите верхнюю левую ячейку области, куда необходимо поместить результат.
3. На вкладке Home (Главная) нажмите на выпадающее меню команды Paste (Вставить).
4. Выберите Transpose (Транспонировать).
5. Удалите исходные данные (по желанию).

Пример 3: Лучший общий объём заработной платы за прошедшие годы

Функция TRANSPOSE (ТРАНСП) может использоваться в сочетании с другими функциями, например, как в этой сногсшибательной формуле. Она была опубликована Харланом Гроувом в новостном блоке 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)))
=МАКС(МУМНОЖ(A8:J8; --(ABS(ТРАНСП(СТОЛБЕЦ(A8:J8))-СТОЛБЕЦ(СМЕЩ(A8:J8;0;0;1;ЧИСЛСТОЛБ(A8:J8)-Number+1))-(Number-1)/2)<Number/2)))

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

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

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