Функция ТРАНСП в Excel, чтобы изменить столбец на строку с формулой
В учебном пособии объясняется синтаксис функции ТРАНСП и показано, как правильно ее использовать для переноса данных в Excel.
О вкусах не спорят. Это также верно и для рабочих привычек. Некоторые пользователи Excel предпочитают располагать данные вертикально в столбцах, тогда как другие выбирают горизонтальное расположение в строках. В ситуациях, когда вам нужно быстро изменить ориентацию заданного диапазона, используйте функцию ТРАНСП.
Функция ТРАНСП в Excel — синтаксис
Целью функции ТРАНСП в Excel является преобразование строк в столбцы, т.е. переключение ориентации заданного диапазона с горизонтальной на вертикальную или наоборот.
Функция принимает всего один аргумент:
ТРАНСП(массив)
Где массив — это диапазон ячеек для транспонирования.
Массив преобразуется следующим образом: первая строка исходного массива становится первым столбцом нового массива, вторая строка становится вторым столбцом и так далее.
Важная заметка! Чтобы функция ТРАНСП работала в Excel 2019 и ниже, необходимо ввести ее как формулу массива, нажав Ctrl + Shift + Enter. В Excel 2021 и Excel 365, которые изначально поддерживают массивы, его можно ввести как обычную формулу.
Как использовать функцию ТРАНСП в Excel
Синтаксис ТРАНСП не оставляет места для ошибок при построении формулы. Более сложная часть состоит в том, чтобы правильно ввести его в рабочий лист. Если у вас нет большого опыта работы с формулами Excel в целом и формулами массива в частности, внимательно следуйте приведенным ниже шагам.
1. Подсчитайте количество столбцов и строк в исходной таблице.
Для начала узнайте, сколько столбцов и строк содержит ваша исходная таблица. Эти цифры понадобятся вам на следующем шаге.
В этом примере мы транспонируем таблицу, которая показывает объем экспорта свежих фруктов по округам:
Наша исходная таблица имеет 4 столбца и 5 строк. Помня об этих цифрах, переходите к следующему шагу.
2. Выберите такое же количество ячеек, но измените ориентацию
Ваша новая таблица будет содержать то же количество ячеек, но будет повернута от горизонтальной ориентации к вертикальной или наоборот. Таким образом, вы выбираете диапазон пустых ячеек, который занимает столько же строк, сколько столбцов в исходной таблице, и столько же столбцов, сколько строк в исходной таблице.
В нашем случае мы выбираем диапазон из 5 столбцов и 4 строк:
3. Введите формулу ТРАНСП
Выделив диапазон пустых ячеек, введите формулу транспонирования:
=ТРАНСП(A1:D5)
Вот подробные шаги:
Сначала вы вводите знак равенства, имя функции и открывающую скобку: =ТРАНСП(
Затем выберите исходный диапазон с помощью мыши или введите его вручную:
Наконец, введите закрывающую скобку, но не нажимайте клавишу Enter! На этом этапе ваша формула Excel Transpose должна выглядеть примерно так:
4. Заполните формулу ТРАНСП.
Нажмите Ctrl + Shift + Enter, чтобы правильно завершить формулу массива. зачем вам это? Потому что формула должна применяться более чем к одной ячейке, и именно для этого предназначены формулы массива.
Как только вы нажмете Ctrl + Shift + Enter, Excel окружит вашу формулу транспонирования {фигурными скобками}, которые видны в строке формул и являются визуальным указанием формулы массива. Ни в коем случае не набирайте их вручную, это не сработает.
На снимке экрана ниже показано, что наша исходная таблица была успешно транспонирована и 4 столбца были преобразованы в 4 строки:
Формула ТРАНСП в Excel 365
В Dynamic Array Excel (365 и 2021) функция ТРАНСП невероятно проста в использовании! Вы просто вводите формулу в верхнюю левую ячейку диапазона назначения и нажимаете клавишу Enter. Вот и все! Без подсчета строк и столбцов, без формул массива CSE. Это просто работает.
=ТРАНСП(A1:D5)
Результатом является динамический диапазон переноса, который автоматически распределяется по стольким строкам и столбцам, сколько необходимо:
Как транспонировать данные в Excel без нулей на пробелы
Если одна или несколько ячеек в исходной таблице пусты, эти ячейки будут иметь нулевые значения в транспонированной таблице, как показано на снимке экрана ниже:
Если вместо этого вы хотите вернуть пустые ячейки, вложите функцию ЕСЛИ в формулу ТРАНСП, чтобы проверить, является ли ячейка пустой или нет. Если ячейка пуста, ЕСЛИ вернет пустую строку (“”), в противном случае укажите значение для транспонирования:
=ТРАНСП(ЕСЛИ(A1:D5=””,””,A1:D5))
Введите формулу, как описано выше (не забудьте нажать Ctrl + Shift + Enter, чтобы правильно завершить формулу массива), и вы получите результат, подобный этому:
Советы и примечания по использованию ТРАНСП в Excel
Как вы только что видели, функция ТРАНСП имеет ряд особенностей, которые могут запутать неопытных пользователей. Приведенные ниже советы помогут вам избежать типичных ошибок.
1. Как редактировать формулу ТРАНСП
Как функция массива, TRANSPOSE не позволяет изменять часть возвращаемого массива. Чтобы изменить формулу транспонирования, выберите весь диапазон, на который ссылается формула, внесите необходимые изменения и нажмите Ctrl + Shift + Enter, чтобы сохранить обновленную формулу.
2. Как удалить формулу ТРАНСП
Чтобы удалить формулу транспонирования с рабочего листа, выделите весь диапазон, указанный в формуле, и нажмите клавишу Delete.
3. Замените формулу ТРАНСП на значения
Когда вы переворачиваете диапазон с помощью функции ТРАНСП, исходный диапазон и выходной диапазон связываются. Это означает, что всякий раз, когда вы изменяете какое-либо значение в исходной таблице, соответствующее значение в транспонированной таблице изменяется автоматически.
Если вы хотите разорвать связь между двумя таблицами, замените формулу вычисляемыми значениями. Для этого выберите все значения, возвращаемые вашей формулой, нажмите Ctrl + C, чтобы скопировать их, щелкните правой кнопкой мыши и выберите Специальная вставка > Ценности из контекстного меню.
Дополнительные сведения см. в разделе Как преобразовать формулы в значения.
Вот как вы используете функцию ТРАНСП для поворота данных в Excel. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
