Как перевернуть таблицу в Excel?

В процессе работы с таблицами Excel иногда возникает необходимость её разворота. Таковой может сделать таблицу более наглядной, или быть требованием заказчика проекта. Угол разворота обычно равняется 90 или 180 градусам. В первом случае строки и столбцы таблицы меняются местами. Эту операцию принято называть транспонированием. Во втором случае таблица переворачивается «с ног на голову».

Транспонирование

  1. Выделить диапазон данных, подлежащий транспонированию.
  2. В его контекстном меню щёлкнуть пункт «Копировать».

Второй шаг имеет две альтернативы.

На вкладке «Главная» можно открыть выпадающий список инструмента «Копировать», и щёлкнуть одноимённый пункт. Другая альтернатива – комбинация клавиш Ctrl + С, видна на скриншоте в скобках.

  1. Выделить свободную ячейку (например, A15), которая станет левой верхней для транспонируемого диапазона данных. В её контекстном меню нам могут понадобиться два пункта – «Параметры вставки» и «Специальная вставка».

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

При внимательном взгляде на нижние пиктограммы первого пункта «Параметры вставки», обращает на себя внимание подчёркнутая пиктограмма с двунаправленной изогнутой стрелкой. При установке на ней курсора мыши не только появляется осведомительная надпись: «Транспонировать», но и уже транспонированный диапазон данных.

 

 

 

  1. Для его фиксации остаточно щёлкнуть пиктограмму «Транспонировать».

При выполнении шага 3 возможна ситуация (связанная с предыдущими действиями пользователя), при которой вышеуказанной пиктограммы «Транспонировать» не будет. В таком случае она будет в пиктограммах выпадающего списка «Специальная вставка» (см. скриншот).

В выпадающем списке «Специальная вставка» есть альтернатива рассмотренной в шагах 3 и 4 работе с пиктограммами.

Для этого в выпадающем списке «Специальная вставка» следует щёлкнуть одноимённый инструмент.

Затем в появившемся одноимённом окне активировать пункт «транспонировать», и нажать OK.

Полученный транспонированный диапазон данных можно сделать компактнее. Для этого его нужно выделить, открыть выпадающий список «Формат», и щёлкнуть пункт «Автоподбор ширины столбца».

При необходимости после этого можно удалить первоначальный диапазон данных (с помощью пункта «Удалить в контекстном меню).

С помощью функции ТРАНСП

В категории «Ссылки и массивы» в Excel присутствует специальная функция ТРАНСП. Её единственный аргумент – подлежащий транспонированию диапазон данных.

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

  1. Щёлкнуть инструмент fx.
  2. В окошке «Вставка функции» раскрыть список «Категория», и щёлкнуть «Ссылки и массивы».

  1. Прокрутить вниз список «Ссылки и массивы», выделить функцию ТРАНСП, и нажать OK.

  1. В появившемся окошке «Аргументы функции» в поле «Массив» ввести транспонируемый диапазон, выделив его.
  2. Щёлкнуть комбинацию клавиш Ctrl + Shift + Enter вместо привычного OK. Именно так инициируются функции категории массивов.

Как видно, транспонирование с помощью функции не сохраняет начальное форматирование. Второй недостаток этого варианта – при удалении первичного диапазона не сохранится и транспонированный: ведь функция тесно связана со своими аргументами.

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

  1. Выделить транспонированный участок, и в его контекстном меню щёлкнуть пункт «Копировать».

 

  1. В том же контекстном меню, в пункте «Параметры вставки», щёлкнуть вариант «Значения», маркированный цифрами 123.

После этого на строке формул больше не отображается формула. Ничто не помешает пользователю при необходимости удалить диапазон-источник. К тому же, транспонированный диапазон перестаёт быть массивом, и в нём становятся возможными локальные изменения.

Переворот

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

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

  1. Записать единицу в ячейку F1.

 

  1. Установить курсор в её правый нижний угол. При появлении маленького крестика, держа нажатой клавишу Ctrl, движением вниз нажатой мыши, распространить цифры на нижележащие ячейки до границы заполненного участка.

  1. Выделить добавленный столбец,
  2. В его контекстном меню установить курсор на пункт «Сортировка».
  3. В раскрывшемся списке щёлкнуть «Настраиваемая сортировка».

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

  1. Щёлкнуть пункт «Сортировка».

  1. В появившемся окошке «Сортировка»:
  • убедиться в дезактивации пункта «Мои данные содержат заголовки»;
  • в списке «Сортировать по» выбрать добавленный вспомогательный столбец с цифрами;
  • в списке «Сортировка» выбрать «Значения»;
  • в списке «Порядок» выбрать «По убыванию»
  • нажать OK.

Первоначальная таблица перевёрнута: задача решена. Вспомогательный столбец с цифрами более не нужен, и его можно удалить.

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

С помощью функции СМЕЩ и СТРОКА

Описанный выше переворот таблицы не сохраняет её первоначального вида. Сохранить его помогут две функции категории «Ссылки и массивы» – СМЕЩ и СТРОКА. Правда, таблица должна состоять из единственного столбца.

Синтаксис функции СМЕЩ имеет следующий вид.

А вот синтаксис функции СТРОКА.

  1. Выделить верхнюю ячейку будущего перевёрнутого столбца (B1).
  2. Щёлкнуть fx слева от строки формул.
  3. В окне «Вставка функции» выбрать категорию «Ссылки и массивы».
  4. В списке функций этой категории выбрать CMEЩ, и нажать OK.

  1. Заполнить обязательные аргументы функции СМЕЩ, как показано на скриншоте. Значение аргумента «Ссылка» – абсолютный адрес нижней ячейки (A13) переворачиваемого столбца. А в выражении для аргумента присутствует абсолютный адрес верхней строки (A1) переворачиваемого столбца.
  2. Нажать OK.

После этого нижняя ячейка переворачиваемого оказалась в верхней ячейке соседнего столбца.

Пользователю остаётся распространить полученный результат на нижележащие строки перевёрнутого столбца посредством крестика – маркера автозаполнения, и движения вниз нажатой мыши.

При необходимости пользователь может преобразовать перевёрнутый столбец в значения, или удалить начальный столбец, как было описано выше.

Оцените статью: 
Средняя: 5 (1 оценка)

Добавить отзыв