Бывают случаи, когда после того, как пользователь уже заполнил значительную часть таблицы или даже закончил над ней работать, он понимает, что нагляднее будет развернуть таблицу на 90 или 180 градусов. Конечно, если стол сделан для собственных нужд, а не на заказ, вы вряд ли сделаете его повторно, но продолжите работу над уже существующим вариантом. Если работодатель или клиент требует изменить пространство стола, в этом случае придется попотеть. Но на самом деле существует ряд нехитрых приемов, которые позволят вам сравнительно легко и быстро повернуть столовый ряд в нужном направлении, вне зависимости от того, изготавливается ли стол для вас или на заказ. Давайте посмотрим, как это сделать в Excel.
Выполнения разворота
Как уже было сказано, стол можно поворачивать на 90 или 180 градусов. В первом случае это означает, что столбцы и строки будут переставлены местами, а во втором таблица будет перевернута сверху вниз, то есть таким образом, что первая строка станет последней. Для выполнения этих задач существует несколько методов различной сложности. Изучим алгоритм его применения.
Способ 1: разворот на 90 градусов
Во-первых, давайте разберемся, как поменять местами строки со столбцами. Эта процедура также называется перестановкой. Проще всего это реализовать с помощью специальной вставки.
- После выполнения любой из этих многочисленных опций результат будет один: будет сформировано табличное пространство, представляющее собой повернутую на 90 градусов версию основного массива. То есть по сравнению с исходной таблицей в транспонированной области строки и столбцы поменялись местами.
- Мы можем оставить оба табличных пространства на листе, а можем удалить основное, если оно больше не нужно. Для этого обозначим весь удаляемый диапазон над транспонированной таблицей. После этого на вкладке «Главная» нажмите на треугольник, который находится справа от кнопки «Удалить» в разделе «Ячейки». В выпадающем списке выберите опцию «Удалить строки с листа».
- После этого все строки, включая родительское табличное пространство, находящиеся выше транспонированного массива, будут удалены.
- Затем, чтобы транспонированный диапазон принял компактный вид, обозначаем его целиком и, перейдя на вкладку «Главная», нажимаем кнопку «Формат» в разделе «Ячейки». В открывшемся списке выберите опцию «Автоподбор ширины столбца».
- После выполнения последнего действия массив таблиц приобрел компактный и презентабельный вид. Теперь мы ясно видим, что в нем по сравнению с исходным диапазоном строки и столбцы поменялись местами.
Также транспонировать область таблицы можно с помощью специального оператора Excel, который называется «ТРАНСПОНИРОВАТЬ». Функция ТРАНСП специально разработана для преобразования диапазона по вертикали в диапазон по горизонтали и наоборот. Его синтаксис:
= ПРОЗРАЧ(массив)
«Массив» — единственный аргумент этой функции. Представляет ссылку на диапазон, который нужно перевернуть.
- Функция помощник активирована. Перейдите в раздел «Ссылки и исправления». Отмечаем там название «TRANSP» и нажимаем «ОК»
- Откроется окно аргументов предыдущего оператора. Ставим курсор в его единственное поле: «Массив». Удерживая левую кнопку мыши, отметьте область таблицы, которую хотите расширить. В этом случае его координаты будут отображаться в поле. После этого не спешим нажимать кнопку «ОК», как это часто бывает. Мы имеем дело с функцией массива, а потому для корректного проведения процедуры необходимо нажать комбинацию клавиш Ctrl+Shift+Enter.
- Перевернутая таблица, как мы видим, вставлена в отмеченный массив.
- Как видите, минус этого варианта по сравнению с предыдущим в том, что исходное форматирование не сохраняется при транспонировании. Кроме того, если вы попытаетесь изменить данные в любой ячейке транспонированного диапазона, появится сообщение о том, что вы не можете изменить часть массива. Кроме того, транспонированный массив связан с родительским диапазоном, и при удалении или изменении источника он также будет удален или изменен.
- А вот с последними двумя недостатками можно справиться достаточно просто. Отмечаем весь транспонированный диапазон. Нажимаем на значок «Копировать», который находится на ленте в категории «Буфер обмена».
- После этого, не снимая обозначения, щелкните по транспонированному фрагменту правой кнопкой мыши. В контекстном меню в категории «Параметры вставки» нажмите значок «Настройки». Данная пиктограмма представлена в виде квадрата, в котором находятся цифры.
- После выполнения этого действия формула в диапазоне будет преобразована в нормальные значения. Теперь находящиеся в нем данные можно изменять как угодно. Кроме того, этот массив больше не связан с исходной таблицей. Теперь, если вы хотите, исходную таблицу можно удалить так же, как мы обсуждали выше, а инвертированный массив можно отформатировать соответствующим образом, чтобы он выглядел информативно и презентабельно.
Урок: Транспонирование таблицы в Excel
Способ 2: разворот на 180 градусов
Теперь пришло время разобраться, как можно развернуть стол на 180 градусов. То есть мы должны убедиться, что первая строка идет вниз, а последняя строка идет вверх. При этом остальные строки табличного массива также соответственно изменили свое начальное положение.
Самый простой способ выполнить эту задачу — использовать возможности сортировки.
- Как видите, после этого весь столбец заполняется цифрами по порядку.
- Отмечаем столбец нумерацией. Перейдите на вкладку «Главная» и нажмите кнопку «Сортировка и фильтрация», которая находится на ленте в разделе «Редактировать». Из открывшегося списка остановите выбор на опции «Выборочная сортировка».
- Это откроет диалоговое окно, информирующее вас о том, что данные были найдены за пределами указанного диапазона. По умолчанию переключатель в этом окне установлен на «Автоматически расширять выбранный диапазон». Требуется оставить его в прежнем положении и нажать кнопку «Сортировать…».
- Откроется окно пользовательской сортировки. Убедитесь, что флажок «Мои данные содержат заголовки» не установлен, даже если заголовки действительно присутствуют. В противном случае они не будут понижены, а останутся в верхней части таблицы. В области «Сортировать по» необходимо выбрать название столбца, в котором нумерация установлена по порядку. В области «Сортировка» следует оставить опцию «Значения», которая установлена по умолчанию. В области «Порядок» установите параметр «По убыванию». После выполнения этих инструкций нажмите кнопку «ОК».
- После этого массив таблицы будет отсортирован в обратном порядке. В результате такой сортировки он будет инвертирован, т.е последняя строка станет заголовком, а заголовок станет последней строкой.
Важная заметка! Если в таблице были формулы, то из-за такой сортировки их результат может отображаться некорректно. Поэтому в этом случае необходимо полностью отказаться от инвестиций или предварительно преобразовать результаты расчетных формул в значения.
- Теперь мы можем удалить лишний столбец с номером, так как он нам больше не нужен. Отмечаем его, кликаем правой кнопкой мыши по отмеченному фрагменту и выбираем в списке пункт «Удалить содержимое».
- Теперь работу по расширению стола на 180 градусов можно считать завершенной.
Но, как вы могли заметить, при использовании этого метода расширения исходная таблица просто становится расширенной. Оригинал не сохраняется. Но бывают случаи, когда массив нужно перевернуть, но при этом сохранить оригинал. Это можно сделать с помощью функции OFFSET. Этот вариант подходит для матрицы, состоящей из одного столбца.
- Запустится мастер функций. Заходим в раздел «Ссылки и исправления» и отмечаем название «СМЕЩЕНИЕ», после чего нажимаем «ОК».
- Откроется окно аргументов. Функция OFFSET предназначена для изменения диапазонов и имеет следующий синтаксис:
=СМЕЩЕНИЕ(ссылка,смещение_строки,смещение_столбца,высота,ширина)
Аргумент Link — это ссылка на последнюю ячейку или диапазон прокручиваемого массива.
«Смещение по строкам» — аргумент, указывающий, насколько таблица должна быть смещена по строкам;
«Сдвиг столбца»: аргумент, указывающий, насколько таблица должна быть смещена по столбцам;
Аргументы Высота и Ширина необязательны. Они указывают высоту и ширину ячеек инвертированной таблицы. Если вы опустите эти значения, они считаются равными высоте и ширине шрифта.
Итак, ставим курсор в поле «Ссылка» и отмечаем последнюю ячейку диапазона, которую необходимо перевернуть. В этом случае связь должна быть абсолютной. Для этого выделите его и нажмите клавишу F4. Рядом с координатами ссылки ($) должен появиться знак доллара ($).
Далее ставим курсор в поле «Прокрутка по строкам» и в нашем случае набираем следующее выражение:
(СТРОКА()-СТРОКА($A$2))*-1
Если вы сделали все так же, как описано выше, то в этом выражении у вас, вероятно, будет отличаться только аргумент второго оператора STRING. Здесь нужно указать координаты первой ячейки инвертированного диапазона в абсолютном виде.
В поле «Сдвиг столбца» поставьте «0».
Оставьте поля Высота и Ширина пустыми. Нажимаем «Принять».
- Как видите, значение, которое было в нижней ячейке, теперь отображается вверху нового массива.
- Чтобы изменить другие значения, нужно скопировать формулу из этой ячейки на весь нижний диапазон. Сделаем это маркером заливки. Поместите курсор в нижний правый край элемента. Ждем, когда он станет крестиком. Удерживая левую кнопку мыши, перетащите ее к краю массива.
- Как видите, весь диапазон заполнен инвертированными данными.
- Если мы хотим, чтобы в его ячейках были не формулы, а значения, отмечаем указанную область и нажимаем кнопку «Копировать» на ленте.
- Затем кликаем по отмеченному фрагменту правой кнопкой мыши и в блоке «Параметры вставки» выбираем иконку «Значения».
- Теперь данные в инвертированном диапазоне вставляются как значения. Исходную таблицу можно удалить или оставить как есть.
Как видите, есть несколько совершенно разных способов повернуть массив таблиц на 90 и 180 градусов. Выбор конкретного варианта, в первую очередь, зависит от поставленной перед пользователем задачи.