Article #34181

Таблица транспонирования — это функция, о которой знают не все пользователи Microsoft Excel. Он предназначен для случаев, когда вам нужно изменить ориентацию диапазона ячеек с вертикальной на горизонтальную или наоборот. Программа также позволяет вращать столбец, чтобы данные шли в обратном порядке — это полезно, если информация введена неправильно. Давайте узнаем, как изменить расположение данных на листе, не перезаписывая все заново.

Транспонирование таблицы Excel специальной вставкой

Бывают ситуации, когда все данные уже были вставлены в таблицу, но необходимо было расположить содержимое заголовка по вертикали. Для этого вы можете использовать инструмент Специальная вставка — его меню содержит несколько настроек, позволяющих настроить вставку таблицы в новую позицию. Возьмем любую таблицу с заголовком и попробуем на ней «Специальная вставка.

Выделите весь диапазон ячеек и скопируйте его с помощью комбинации клавиш «Ctrl + C». Вы также можете щелкнуть правой кнопкой мыши и выбрать «Копировать» из контекстного меню».

На новом листе или в другой части того же листа найдите свободное место, куда вам нужно вставить таблицу. Нажмите правую кнопку мыши, поместив курсор на одну из ячеек, и выберите «Специальная вставка». В некоторых версиях Microsoft Excel при наведении курсора на этот пункт меню сразу же открываются параметры, но вам нужно найти пункт «Специальная вставка» в нижней части меню надстройки и щелкнуть по нему.

Диалоговое окно содержит несколько параметров, которые позволяют вставлять отдельные данные из таблицы в новое место. Установите флажок «Транспонировать» и нажмите «ОК».

Теперь заголовок таблицы находится в первом столбце, а данные оттуда перемещены в верхнюю строку. Эта таблица также наглядно показывает разницу между объемами продаж.

Как перевернуть таблицу с помощью функции

Мастер содержит формулу, которую можно использовать для обмена строками и столбцами в таблице. Вам нужно будет снова скопировать данные и вставить их в другой диапазон ячеек, чтобы увидеть изменения.

Перейдите в «Параметры» и в разделе «Работа с формулами / формулами» поставьте галочку в столбце «Стиль подключения R1C1». Он понадобится в дальнейшем для правильной работы с формулой. Если этот шаг не пройден, массив данных не может быть добавлен в функцию.

Определите, где будет размещена измененная таблица, и добавьте функцию TRANSPOSE к первой ячейке. Формулу можно найти в функции мастера или написать вручную — вам просто нужно обозначить букву и скобку.

Примечание! Если вы используете меню функций мастера для поиска формул, вам следует открыть категорию «Ссылки и массивы». При написании формулы от руки обязательно ставьте знак равенства в начале.

Перейдите на лист с исходной таблицей и выберите ее, чтобы диапазон ячеек поместился в формулу. Затем нажмите «ОК». Таблица еще не полностью перенесена, но ничего страшного — требуется еще два шага.

На листе с функцией выделите в отчете ячейки, в которых они появятся после перелистывания. Например, в таблице примера 7 строк и 4 столбца — это означает, что вам нужно выбрать 4 строки и 7 столбцов.

Давайте воспользуемся сочетанием клавиш, чтобы формула заработала. Сначала нажмите F2 — если функции настроены на эту кнопку, найдите клавишу, которая останавливает их работу, и удерживайте ее. Формула в ячейке развернется. Затем быстро нажмите три клавиши: Ctrl, Shift, Enter. В выбранном диапазоне появится перевернутая таблица.

Транспонирование с использованием сводной таблицы

Сводные таблицы помогают во многих случаях при использовании Excel. Эта опция также полезна при транспонировании. С помощью сводной таблицы вы можете перемещать только столбцы, а не строки, но не наоборот. Этот метод также имеет то преимущество, что пользователи могут фильтровать данные в таблице.

Выберите исходный диапазон ячеек с помощью курсора.

Перейдите на вкладку «Вставка» на панели инструментов и найдите кнопку «Сводная таблица».

Настройте формирование сводной таблицы в диалоговом окне. Там указывается диапазон ячеек — он уже выбран — и точка вставки. Выберите новый лист, чтобы легко управлять новым диапазоном данных.

Распределяем поля в списке по категориям. Содержимое столбцов должно быть связано со строками и наоборот.

Откройте вкладку «Дизайн» и отключите суммирование и промежуточные итоги для строк и столбцов.

1. Результатом является таблица с теми же значениями, но строки и столбцы изменили положение.

Как перевернуть один столбец функцией

Если в вашей электронной таблице Excel есть столбец, в котором значения указаны в обратном порядке, вы можете перевернуть его с помощью мастера. Есть две формулы, которые помогут развернуть перевернутый столбец».

Вставляем в ячейку, с которой начинается инвертированный столбец, функцию СМЕЩЕНИЕ или ИНДЕКС. Вы можете использовать эти шаблоны, но не забудьте ввести значения, соответствующие положению ячеек в вашей таблице: = OFFSET ($ A $ 8; (ROW ($ A $ 2) -LINE ());) или = ИНДЕКС ($ A $ 2: $ A $ 8; СТРОКИ (A2: $ A $ 8)).

Например, воспользуемся функцией СМЕЩЕНИЕ: добавим ее в ячейку и нажмем Enter.

Заблокируйте индикатор заполнения — черный квадрат в правом нижнем углу ячейки — и перетащите выделение до конца столбца. Все ячейки будут заполнены по плану, то есть в обратном порядке.

Результат заполнения столбца с помощью функции ИНДЕКС выглядит так:

Важно! Столбцы не обязательно должны располагаться рядом, ссылки в формулах по-прежнему ведут к нужным ячейкам. Эти знания будут полезны для создания перевернутого столбца вне твердой таблицы.

Заключение

Большинство методов переворачивания или перестановки стола работают в любом случае. Пользователь может сделать диапазон ячеек горизонтально ориентированным вертикально или заголовки могут быть названиями строк. Только сводные таблицы не подходят для двунаправленного транспонирования. Самый простой способ повернуть стол — с помощью специальной вставки.