Как перевернуть таблицу в Excel?
В процессе работы с таблицами Excel иногда возникает необходимость её разворота. Таковой может сделать таблицу более наглядной, или быть требованием заказчика проекта. Угол разворота обычно равняется 90 или 180 градусам. В первом случае строки и столбцы таблицы меняются местами. Эту операцию принято называть транспонированием. Во втором случае таблица переворачивается «с ног на голову».
Транспонирование
- Выделить диапазон данных, подлежащий транспонированию.
- В его контекстном меню щёлкнуть пункт «Копировать».
Второй шаг имеет две альтернативы.
На вкладке «Главная» можно открыть выпадающий список инструмента «Копировать», и щёлкнуть одноимённый пункт. Другая альтернатива – комбинация клавиш Ctrl + С, видна на скриншоте в скобках.
- Выделить свободную ячейку (например, A15), которая станет левой верхней для транспонируемого диапазона данных. В её контекстном меню нам могут понадобиться два пункта – «Параметры вставки» и «Специальная вставка».
Вместо контекстного меню, эти же пункты «Вставка» и «Специальная вставка» доступны и через выпадающий список инструмента «Вставить» на вкладке «Главная».
При внимательном взгляде на нижние пиктограммы первого пункта «Параметры вставки», обращает на себя внимание подчёркнутая пиктограмма с двунаправленной изогнутой стрелкой. При установке на ней курсора мыши не только появляется осведомительная надпись: «Транспонировать», но и уже транспонированный диапазон данных.
- Для его фиксации остаточно щёлкнуть пиктограмму «Транспонировать».
При выполнении шага 3 возможна ситуация (связанная с предыдущими действиями пользователя), при которой вышеуказанной пиктограммы «Транспонировать» не будет. В таком случае она будет в пиктограммах выпадающего списка «Специальная вставка» (см. скриншот).
В выпадающем списке «Специальная вставка» есть альтернатива рассмотренной в шагах 3 и 4 работе с пиктограммами.
Для этого в выпадающем списке «Специальная вставка» следует щёлкнуть одноимённый инструмент.
Затем в появившемся одноимённом окне активировать пункт «транспонировать», и нажать OK.
Полученный транспонированный диапазон данных можно сделать компактнее. Для этого его нужно выделить, открыть выпадающий список «Формат», и щёлкнуть пункт «Автоподбор ширины столбца».
При необходимости после этого можно удалить первоначальный диапазон данных (с помощью пункта «Удалить в контекстном меню).
С помощью функции ТРАНСП
В категории «Ссылки и массивы» в Excel присутствует специальная функция ТРАНСП. Её единственный аргумент – подлежащий транспонированию диапазон данных.
- Выделить пустой диапазон на листе Excel согласно размерам транспонируемого участка. Иными словами, количество выделяемых пустых строк должно равняться числу столбцов транспонируемого диапазона, а количество столбцов – числу строк.
- Щёлкнуть инструмент fx.
- В окошке «Вставка функции» раскрыть список «Категория», и щёлкнуть «Ссылки и массивы».
- Прокрутить вниз список «Ссылки и массивы», выделить функцию ТРАНСП, и нажать OK.
- В появившемся окошке «Аргументы функции» в поле «Массив» ввести транспонируемый диапазон, выделив его.
- Щёлкнуть комбинацию клавиш Ctrl + Shift + Enter вместо привычного OK. Именно так инициируются функции категории массивов.
Как видно, транспонирование с помощью функции не сохраняет начальное форматирование. Второй недостаток этого варианта – при удалении первичного диапазона не сохранится и транспонированный: ведь функция тесно связана со своими аргументами.
Правда, последний недостаток легко преодолим. Для этого достаточно скопировать значения транспонированного диапазона и вставить в то же место именно как значения, без связи с реализованной функцией.
- Выделить транспонированный участок, и в его контекстном меню щёлкнуть пункт «Копировать».
- В том же контекстном меню, в пункте «Параметры вставки», щёлкнуть вариант «Значения», маркированный цифрами 123.
После этого на строке формул больше не отображается формула. Ничто не помешает пользователю при необходимости удалить диапазон-источник. К тому же, транспонированный диапазон перестаёт быть массивом, и в нём становятся возможными локальные изменения.
Переворот
Разворот на таблицы на 180 градусов предполагает, что её верхняя строка станет нижней, и наоборот. С соответствующим изменением позиций всех других строк. Самый простой способ реализации такого переворота – использовать возможности сортировки.
Сначала добавим к переворачиваемому диапазону дополнительный столбец с нумерацией строк, включая заголовки столбцов.
- Записать единицу в ячейку F1.
- Установить курсор в её правый нижний угол. При появлении маленького крестика, держа нажатой клавишу Ctrl, движением вниз нажатой мыши, распространить цифры на нижележащие ячейки до границы заполненного участка.
- Выделить добавленный столбец,
- В его контекстном меню установить курсор на пункт «Сортировка».
- В раскрывшемся списке щёлкнуть «Настраиваемая сортировка».
Прежде чем приступить к сортировке, программа Excel уточняет у пользователя её объект – только ли выделенный столбец с цифрами или вместе с ним нужно сортировать и расположенные слева. По умолчанию в окошке предлагается именно этот вариант.
- Щёлкнуть пункт «Сортировка».
- В появившемся окошке «Сортировка»:
- убедиться в дезактивации пункта «Мои данные содержат заголовки»;
- в списке «Сортировать по» выбрать добавленный вспомогательный столбец с цифрами;
- в списке «Сортировка» выбрать «Значения»;
- в списке «Порядок» выбрать «По убыванию»
- нажать OK.
Первоначальная таблица перевёрнута: задача решена. Вспомогательный столбец с цифрами более не нужен, и его можно удалить.
Отметим, что если в таблице были формулы, то описанная сортировка может сработать некорректно. В таком случае рекомендуется предварительно в начальной таблице преобразовать результаты вычисления формул в значения.
С помощью функции СМЕЩ и СТРОКА
Описанный выше переворот таблицы не сохраняет её первоначального вида. Сохранить его помогут две функции категории «Ссылки и массивы» – СМЕЩ и СТРОКА. Правда, таблица должна состоять из единственного столбца.
Синтаксис функции СМЕЩ имеет следующий вид.
А вот синтаксис функции СТРОКА.
- Выделить верхнюю ячейку будущего перевёрнутого столбца (B1).
- Щёлкнуть fx слева от строки формул.
- В окне «Вставка функции» выбрать категорию «Ссылки и массивы».
- В списке функций этой категории выбрать CMEЩ, и нажать OK.
- Заполнить обязательные аргументы функции СМЕЩ, как показано на скриншоте. Значение аргумента «Ссылка» – абсолютный адрес нижней ячейки (A13) переворачиваемого столбца. А в выражении для аргумента присутствует абсолютный адрес верхней строки (A1) переворачиваемого столбца.
- Нажать OK.
После этого нижняя ячейка переворачиваемого оказалась в верхней ячейке соседнего столбца.
Пользователю остаётся распространить полученный результат на нижележащие строки перевёрнутого столбца посредством крестика – маркера автозаполнения, и движения вниз нажатой мыши.
При необходимости пользователь может преобразовать перевёрнутый столбец в значения, или удалить начальный столбец, как было описано выше.
Добавить отзыв