Другой возможный вариант смещения — использование функции ‘special’. Это утверждение больше не используется в типе, но является одним из распространенных способов преобразования столбцов в строки и наоборот.
6 способов быстро транспонировать таблицу
В этой статье показано, как преобразовать столбцы в строки Excel с помощью транспортных функций, специальных введений, кода VBA или специальных инструментов. Другими словами, вы научитесь конкурировать с таблицами.
Как вы увидите в этой статье, существует несколько способов обмена строками и столбцами (или столбцами и строками). Как вы говорите, эти решения работают во всех версиях Excel 2016, 2013, 2010, 2007 и т.д., они представляют множество возможных сценариев и объясняют наиболее распространенные ошибки. Итак, мы говорим:.
Нет никаких разногласий по поводу предпочтений. То же самое относится и к рабочим привычкам. Некоторые пользователи Excel предпочитают организовывать свои данные вертикально в столбцах, в то время как другие выбирают горизонтальную компоновку со строками. А когда вы работаете с бухгалтерскими листами, предпочтения часто не совпадают …
Часто создаются сложные вычислительные листы. В конце концов, вы просто понимаете, что имеет смысл транспонировать его, чтобы облегчить анализ числовых элементов или представить его в виде графика.
Транспозиция — это математический акт. Это преобразование таблицы, при котором создаются столбцы, а строки становятся столбцами. Проще говоря, все значения в одной строке (горизонтальной) столбца (вертикальной) нужно поменять местами.
Способ 1. Использовать функцию ТРАНСП.
Если вам нужно быстро изменить ориентацию таблицы, вы можете использовать функцию Transpose.
Функция Transpose в Excel предназначена для преобразования строк в столбцы. То есть, изменить ориентацию с горизонтальной на вертикальную или наоборот.
Функция ТРАНСП в Excel — синтаксис
Функция транспонирования принимает только один аргумент.
= transp (массив).
Диапазон, в котором противопоставляется таблица. Данные сдвигаются следующим образом. Первая строка исходного диапазона становится первым столбцом нового диапазона, вторая строка становится вторым столбцом и т.д.
Важно: Чтобы функция Transp работала, ее необходимо ввести как тип таблицы, нажав Ctrl + Shift + Enter.
Transp Retirement не оставляет места для ошибки при создании типа. Самое сложное задание — правильно оформить его в рабочем листе. Если у вас нет большого опыта работы с типами Excel в целом и типами массивов в частности, обратите внимание на следующие инструкции
1. измерьте количество столбцов и строк в исходной таблице.
Эти цифры понадобятся вам на следующем этапе.
В этом примере мы передаем небольшую таблицу, показывающую количество экспортируемых свежих фруктов по регионам.
2. выберите то же количество ячеек, но измените их ориентацию.
Новая таблица будет содержать то же количество цен, но повернутых из горизонтального положения в вертикальное. Поэтому выделите область пустых ячеек, занимающую столько же строк, сколько в исходной таблице, и столько же столбцов, сколько в исходной таблице.
В нашем случае мы выбираем диапазон из пяти столбцов и четырех строк.
После выбора этого пустого диапазона мы используем режим Shift.
Сначала введите символ равенства, имя функции и начальную скобку: = transp (
Затем выберите исходный диапазон с помощью мыши или введите его вручную.
Наконец, введите закрывающую скобку, но не нажимайте Enter! На этом этапе ваш мужчина должен выглядеть следующим образом
Нажмите Ctrl + Shift + Enter, чтобы правильно ввести тип таблицы. Почему вы это делаете; потому что формулы должны применяться ко всем данным. Именно для этого и существуют типы массивов.
Затем Excel окружает формулу перестановками, которые появляются в строке формул и визуально представляют тип массива. Ни в коем случае не вводите данные вручную. Это не сработает.
Следующий снимок экрана показывает, что исходная таблица значений была успешно перемещена, и четыре столбца были преобразованы в четыре строки. Теперь есть пять колонок:.
Преимущества функции ТРАНСП:
Основное преимущество использования этой функции заключается в том, что повернутая таблица сохраняет свое отношение к исходной таблице. Каждый раз, когда исходные данные изменяются, передаваемые данные также изменяются соответствующим образом.
Слабые стороны функции ТРАНСП:
- Исходное форматирование не сохраняется в преобразованной таблице, как вы видите на скриншоте выше.
- Если в исходной таблице есть пустые клетки, вместо них ячейки новой таблицы будут содержать ноль. Чтобы исправить это, используйте ТРАНСП в комбинации с функцией ЕСЛИ, как описано в этом примере ниже: Как транспонировать без нулей.
- Вы не можете редактировать какие-либо данные в преобразованной таблице, потому что она полностью завязана на исходные данные. Если вы попытаетесь изменить какое-либо значение, то получите ошибку «Вы не можете изменить часть массива».
Способ 2. Как транспонировать с помощью формул
Этот метод несколько похож на предыдущий, но позволяет свободно редактировать значения во второй таблице и вносить изменения по мере необходимости.
Для создания отчетов по строкам и столбцам вам понадобятся четыре функции из категории Browse and Arrays.
- Функция АДРЕС(номер_строки; номер_столбца) – выдает адрес ячейки по номеру строки и столбца на листе, т.е. АДРЕС(2;3) выдаст, например, ссылку на C2.
- Функция ДВССЫЛ(ссылка_в_виде_текста) – преобразует текстовую строку, например, «F3» в настоящую ссылку на ячейку F3.
- Функции СТРОКА() и СТОЛБЕЦ() — выдают номер строки и столбца для заданной ячейки, например =СТРОКА(F1) выдаст 1, а формула =СТОЛБЕЦ(А3) возвратит 3.
Затем эти функции объединяются для получения ссылок, необходимых в исходной таблице. Предположим, что координата левого верхнего угла равна A1.
Введите следующую формулу в пустую ячейку (например, A8).
В английском языке Excel = INDIRECT (ADDRESS (COLUMN (A1), ROW (A1)).
Чтобы преобразовать столбцы в строки, перетащите маленький черный крестик в правом нижнем углу выделенной ячейки и скопируйте (перетащите) формулу в правый нижний угол. Вам нужно придумать что-то вроде.
= DVSLIP(ADDRESS(COLUMN(B6)-COLUMN($ B $ 6) + ROW($ B $ 6); ROW(B6)-ROW($ B $ 6) + COLUMN($ B $ 6））））
Здесь B6 — это левая верхняя ячейка исходной таблицы. Обратите внимание также на использование абсолютных и связанных ссылок.
Однако переданные данные кажутся очень простыми и скучными по сравнению с исходными данными. Все форматирование будет потеряно.
Однако не стоит отчаиваться. Эту проблему можно легко решить. Чтобы восстановить исходный формат, выполните следующие действия
- Скопируйте исходную таблицу.
- Выделите получившуюся таблицу.
- Щелкните её правой кнопкой мыши и выберите «Параметры вставки» > «Форматирование» .
Плюсы: формула обеспечивает более гибкий способ замены строк и столбцов в Excel. В нем используется обычный, а не табличный тип, что позволяет вносить изменения в поворотную таблицу.
Минусы: Отображается только один. Форматирование данных теряется. Однако его можно быстро восстановить, как описано выше.
Плюсы: формула обеспечивает более гибкий способ замены строк и столбцов в Excel. В нем используется обычный, а не табличный тип, что позволяет вносить изменения в поворотную таблицу.
Транспонирование таблицы в Microsoft Excel
Возможно, вы захотите инвертировать таблицу, т.е. поменять местами строки и столбцы. Конечно, при необходимости можно полностью перегруппировать все данные, но это может занять много времени. Не все пользователи Excel знают, что в этом рабочем листе есть функции, которые могут помочь автоматизировать этот процесс. Давайте подробнее рассмотрим, как преобразовать строки в столбцы в Excel.
Процедура транспонирования
Переключение между столбцами и строками в Excel называется сдвигом. Этот процесс может осуществляться двумя способами: специальным вводом и с помощью функций.
Способ 1: специальная вставка
Узнайте, как перемещать листы в Excel. Перестановка с помощью специальных вставок — самый простой и популярный среди пользователей тип перестановки столов.
-
Выделяем всю таблицу курсором мышки. Кликаем по ней правой кнопкой. В появившемся меню выбираем пункт «Копировать» или просто жмем на клавиатуре сочетание Ctrl+C.
Как видите, после этих действий исходная таблица копируется в новое место, но ячейки уже перевернуты.
Затем в меню Вид выберите команду Удалить…. и выберите Удалить…, чтобы выбрать исходную таблицу для удаления. Однако это не обязательно делать, если это не влияет на рабочий лист.
Способ 2: применение функции
Второй способ сдвига в Excel требует использования специальной функции-трансформатора.
- Выделяем область на листе, равную по вертикальному и горизонтальному диапазону ячеек исходной таблице. Кликаем по значку «Вставить функцию» , размещенному слева от строки формул.
Особенностью этого метода перемещения является то, что, в отличие от предыдущего, исходные данные не могут быть удалены. Это происходит потому, что при этом удаляется область смещения. Более того, изменения в исходных данных приводят к таким же изменениям в новой таблице. Поэтому данный метод особенно подходит для манипулирования связанными таблицами. В то же время он значительно сложнее, чем первый выбор. Кроме того, при использовании этого метода необходимо сохранить источник. Это не всегда лучшее решение.
Теперь мы понимаем, как переставлять столбцы и строки в Excel. Существует два основных способа опрокидывания стола. Какой из двух вариантов вы используете, зависит от того, планируете ли вы применять подключенные данные. Если у вас нет такого плана, мы рекомендуем использовать первый вариант, так как он проще.
Мы рады, что смогли помочь вам решить вашу проблему, и надеемся, что вы найдете эту статью полезной.
Помимо этой статьи, на сайте есть еще 12844 полезные инструкции. Добавление lumpics.ru в закладки (ctrl+d), безусловно, полезно.
В следующей статье вы узнаете, как быстро развернуть весь столбец снизу вверх, как поменять местами строки справа налево и как поменять местами строки в столбце полностью.
Функция транспонирования в Excel
Перед пользователями Excel может возникнуть задача преобразования диапазона данных с вертикальной структурой в горизонтальную. Эта процедура называется сдвигом. Для большинства людей это слово является новым. Это связано с тем, что при нормальной работе ПК нет необходимости применять эту функцию. Однако те, кому необходимо работать с большими массивами данных, должны знать, как это делать. Сегодня мы более подробно рассмотрим, как это сделать и какие функции используются. Мы также рассмотрим некоторые другие методы более подробно.
Одним из наиболее интересных и функциональных методов сдвига таблиц в Excel является функция трансформатора. Его можно использовать для преобразования горизонтального диапазона данных в вертикальный или для выполнения обратного действия. Узнайте, как им пользоваться.
Синтаксис функции
Редактировать эту функцию очень просто: transp (массив). Это означает, что необходимо использовать только один аргумент — набор данных. Набор данных необходимо преобразовать по горизонтали или вертикали, в зависимости от того, что было использовано изначально.
Транспонирование вертикальных диапазонов ячеек (столбцов)
Предположим, есть столбцы в диапазоне B2:B6. Они могут содержать либо значения, либо типы, готовые вернуть результаты в эти ячейки. В обоих случаях мы не заинтересованы в сдвиге. После применения этой функции длина линии будет такой же, как длина столбцов в исходной области.
Последовательность использования этого типа следующая
- Выделяем строку. В нашем случае она имеет длину в пять ячеек.
- После этого перемещаем курсор на строку формул, и там вводим формулу =ТРАНСП(B2:B6).
- Нажимаем комбинацию клавиш Ctrl + Shift + Enter.
Конечно, в вашем случае необходимо указать диапазон таблицы.
Транспонирование горизонтальных диапазонов ячеек (строк)
В принципе, механизм практически идентичен предыдущему пункту. Предположим, у вас есть линия с координатами начала и окончания срока действия B10:F10. Он также может содержать как значения, так и типы. Давайте создадим колонку из того же измерения, что и исходная строка. Последовательность операций следующая.
- С помощью мыши выделяем эту колонку. Также можно воспользоваться клавишами на клавиатуре Ctrl и стрелочку вниз, предварительно нажав на самую верхнюю ячейку этой колонки.
- После этого записываем формулу =ТРАНСП(B10:F10) в строку формул.
- Записываем ее, как формулу массива, с помощью комбинации клавиш Ctrl + Shift + Enter.
Транспонирование с помощью Специальной вставки
Другой возможный вариант смещения — использование функции ‘special’. Это утверждение больше не используется в типе, но является одним из распространенных способов преобразования столбцов в строки и наоборот.
Эта опция находится на вкладке Главная. Чтобы получить к нему доступ, нужно найти команду Clip и кнопку Paste. Затем откройте меню под этой опцией и выберите «Transpose». Прежде чем это сделать, необходимо выбрать диапазон, который вы хотите выделить. В результате получается тот же диапазон, только с отражениями.
3 способа, как транспонировать таблицу в Excel
На самом деле, существует еще больше способов превратить столбец в строку. То же самое касается и обратного. Давайте обсудим три способа, с помощью которых можно отразить таблицы Excel. Мы рассмотрели два из них выше, но вот еще несколько примеров, чтобы дать вам лучшее представление о том, как осуществляется этот процесс.
Способ 1. Специальная вставка
Этот метод является самым простым. Достаточно нажать несколько кнопок, и пользователь получает сдвинутую версию таблицы. Чтобы сделать это более понятным, давайте рассмотрим небольшой пример. Предположим, что таблица содержит информацию о количестве продуктов, находящихся в данный момент на складе, и информацию о соответствующих затратах. Сама таблица имеет следующий формат
Вы можете видеть, что есть колонки с заголовками и номерами продуктов. В данном примере заголовок содержит информацию о том, какой товар, стоимость, сколько имеется на складе и какова общая стоимость всех товаров, связанных с этим товаром. Стоимость получается в результате использования вида затрат, умноженного на количество. Чтобы сделать пример более наглядным, создайте зеленый заголовок.
Наша цель — отобразить информацию, содержащуюся в таблице, горизонтально. Другими словами, колонки должны образовывать линию. Последовательность действий в нашем случае следующая
- Выделяем диапазон данных, который нам нужно повернуть. После этого копируем эти данные.
- Размещаем курсор в каком-угодно месте листа. Затем нажимаем на правую кнопку мыши и открываем контекстное меню.
- Потом нажимаем на кнопку «Специальная вставка».
После выполнения этих действий необходимо нажать кнопку Transpose. Скорее, установите флажок напротив этого пункта. Не изменяйте другие настройки и нажмите кнопку OK.
После этих действий мы имеем ту же таблицу, только ее строки и столбцы находятся в разных позициях. Также обратите внимание, что ячейки, содержащие одинаковую информацию, выделены зеленым цветом. В: Что случилось с типами оригинальных серий? Их позиция изменилась, но они остались самими собой. Они просто изменили адрес ячейки на тот, который образовался после смещения.
Чтобы передать значение вместо типа, сделайте почти то же самое. В этом случае также необходимо использовать меню «Импорт специалиста», но сначала выбрать область данных, содержащую цены. Специальное окно вставки можно вызвать двумя способами — через меню Special ленты или через меню Environment.
Способ 2. Функция ТРАНСП в Excel
На самом деле, этот метод уже не так широко используется, как в первые годы существования бухгалтерских программ. Это связано с тем, что это гораздо более сложный метод, чем использование специальной пасты. Однако ему можно найти применение для автоматизации вычислительных листов.
