Содержание:
Вчера на марафоне 30 функций Excel за 30 дней мы подсчитали количество столбцов в диапазоне с помощью функции КОЛОННЫ (NUMBERCOLUMN), и теперь пришло время чего-то более востребованного.
13-й день марафона мы посвятим изучению функции ТРАНСПОНИРОВАНИЕ (ТРАНСП). С помощью этой функции вы можете вращать данные, превращая вертикальные области в горизонтальные и наоборот. Есть ли у вас такая необходимость? Можно ли это сделать с помощью специальной вставки? Могут ли это сделать другие функции?
Итак, обратимся к информации и примерам по функции ТРАНСПОНИРОВАНИЕ (ТРАНСП). Если у вас есть дополнительная информация или примеры, поделитесь ими в комментариях.
Функция 13: ТРАНСПОНИРОВАНИЕ
Функция ТРАНСПОНИРОВАНИЕ (ТРАНСПОЗ) возвращает горизонтальный диапазон ячеек как вертикальный диапазон или наоборот.
Как можно использовать функцию ТРАНСП?
Функция ТРАНСПОНИРОВАНИЕ (TRANSP) может менять ориентацию данных, а также работать совместно с другими функциями:
- Измените горизонтальное расположение данных на вертикальное.
- Покажите лучшую совокупную заработную плату за последние годы.
Чтобы изменить ориентацию данных без создания ссылок на исходные данные:
- Используйте паста Специальная (Специальная паста) > транспонировать (Транспонировать).
Синтаксис ТРАНСП (TRANSP)
Функция ТРАНСПОНИРОВАНИЕ (ТРАНСП) имеет следующий синтаксис:
TRANSPOSE(array)
ТРАНСП(массив)
- массив (массив) — массив или диапазон ячеек, подлежащих транспонированию.
Ловушки ТРАНСПОЗ (TRANSPOSE)
- Функция ТРАНСПОНИРОВАНИЕ (ТРАНСП) необходимо ввести как формулу массива, нажав Shift + Ctrl + Enter.
- Диапазон, который получится в результате преобразования функцией ТРАНСПОНИРОВАНИЕ (ТРАНСП) должно иметь то же количество строк и столбцов, что и исходный диапазон, содержащий столбцы и строки соответственно.
Пример 1. Преобразование горизонтальных данных в вертикальные данные
Если данные на листе Excel расположены горизонтально, вы можете применить функцию ТРАНСПОНИРОВАНИЕ (ТРАНСПОН), чтобы преобразовать их в вертикальное положение, но в другом месте листа. Например, в итоговой таблице показателей удобнее было бы вертикальное расположение. Использование функции ТРАНСПОНИРОВАНИЕ (ТРАНСПОН), вы можете ссылаться на исходные горизонтальные данные, не меняя их местоположения.
Транспонирование горизонтального диапазона 2 × 4 в вертикальный диапазон 4 × 2:
- Выделите 8 ячеек, в которые вы хотите поместить полученный вертикальный диапазон. В нашем примере это будут ячейки B4:C7.
- Введите следующую формулу и преобразуйте ее в формулу массива, нажав кнопку Shift + Ctrl + Enter.
=TRANSPOSE(B1:E2)
=ТРАНСП(B1:E2)
Фигурные скобки будут автоматически добавлены в начале и конце формулы, чтобы указать, что была введена формула массива.
Вместо ТРАНСПОНИРОВАНИЕ (ТРАНСПОЗ), для преобразования данных можно использовать другую функцию, например, ИНДЕКС (ИНДЕКС). Вам не требуется вводить формулу массива, и вам не нужно выбирать все ячейки в целевой области при создании формулы.
=INDEX($B$2:$E$2,,ROW()-ROW(C$4)+1)
=ИНДЕКС($B$2:$E$2;;СТРОКА()-СТРОКА(C$4)+1)
Пример 2. Изменение ориентации без ссылок
Если вы просто хотите изменить ориентацию своих данных, не сохраняя ссылку на исходные данные, вы можете использовать Специальную вставку:
- Выберите исходные данные и скопируйте их.
- Выберите верхнюю левую ячейку области, в которую вы хотите поместить результат.
- На вкладке Дополнительно Главная (Главная страница) щелкните раскрывающееся меню команд. макаронные изделия (Вставлять).
- Выбирать транспонировать (Транспонировать).
- Удалите исходные данные (необязательно).
Пример 3: Лучшая общая зарплата за последние годы
Функция ТРАНСПОНИРОВАНИЕ (TRANSP) можно использовать в сочетании с другими функциями, например, в этой потрясающей формуле. Его опубликовал Харлан Гроув в блоке новостей 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)
=МАКС(МУМНОЖ(A8:J8; --(ABS(ТРАНСП(СТОЛБЕЦ(A8:J8))-СТОЛБЕЦ(СМЕЩ(A8:J8;0;0;1;ЧИСЛСТОЛБ(A8:J8)-Number+1))-(Number-1)/2)
Как можно понять по фигурным скобкам в строковой формуле – это формула массива. Ячейка А5 названа Номер регистрации и в этом примере число 4 введено как значение количества лет.
Формулы в широком диапазоне, чтобы увидеть достаточно ли в них последовательных столбцов. Результаты проверки (1 или 0) умножаются на значения ячеек, чтобы получить общий объем заработной платы.
Для проверки результатов на рисунке ниже в строке под значениями зарплат показаны суммарные значения для каждой начальной ячейки, при этом максимальное выделенное значение отмечено желтым цветом. Это более долгий путь к тому же результату, что предыдущая формула массива получается в одной ячейке!
