Как развернуть лист в экселе

При выполнении расчетов в MS Excel иногда нужно поменять строки и колонки таблицы местами.


Причем речь идет не только о сугубо математических вычислениях.

При обработке экономических или бухгалтерских отчетов такая задача возникает сплошь и рядом!

Обычными формулами или копированием ячеек это делать очень неудобно.

Возникает вопрос: что можно предпринять в подобной ситуации? Но для начала определимся с терминологией.

Операция, в которой строки и столбцы таблицы меняются местами, называется транспонированием.

В Excel (любой версии!) такую операцию можно сделать, как минимум, двумя способами.

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

Способ 1. Транспонирование данных через специальную вставку.

1) Выделите участок таблицы, которую хотите транспонировать.
2) Скопируйте данные в буфер (комбинации «Ctrl+C» или «Ctrl+Ins»).
3) Поставьте указатель активной ячейки в начало блока, где должна находиться транспонированная таблица.
4) В программе Excel 2003 вызовите «Правка \ Специальная вставка» (в MS Excel 2010-2016 щелкните левой кнопкой (ЛКМ) по небольшому треугольнику под иконкой «Вставить»). Откроется окно параметров специальной вставки.
5) В этом окне включите флажок «Транспонировать».
6) Нажмите «ОК».

На листе появится транспонированная таблица. Попробуйте изменить данные в исходной таблице.

Заметьте, что содержимое транспонированной таблицы не изменилось!

Важно! Транспонирование через функцию специальной вставки не связывает исходные данные и результат.

После специальной вставки обе таблицы будут автономны.

В этом и заключается основной недостаток первого способа.

Для устранения этого недостатка можно задействовать другой метод решения задачи, – применить формулу-массив. Вот как это сделать.

Способ 2. Транспонирование данных через формулу массив.

1) На свободном участке листа выделите диапазон ячеек для будущей транспонированной таблицы.
2) Не снимая выделения (!) в первую ячейку диапазона запишите формулу «=Трансп(блок)». В качестве параметра «блок» введите диапазон исходной таблицы. Указать диапазон можно, выделив его прямо на рабочем листе.
3) Когда формула готова, нажмите «Ctrl+Shift+Enter». Это важно, т.к. нам нужно не обычное выражение, а формула-массив.

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

Все изменения появятся в транспонированной таблице.

Важно! Транспонирование таблиц через функцию «=Трансп()» устанавливает связь между источником данных и результатом.

В завершение темы пара замечаний по второму способу.

1) Вставить функцию «=Трансп()» вы можете с помощью Мастера функций, выбирать адреса ячеек можно прямо по рабочему листу. Но есть одна тонкость. После того, как в окне работы с Мастером формула готова, не нажимайте кнопку «ОК» – иначе вы получите обычную формулу, а она для решения задачи не годится. Не закрывая окно Мастера, нажмите комбинацию «Ctrl+Shift+Enter», программа MS Excel вставит на рабочий лист формулу-массив.
2) Диапазон ячеек для транспонированной таблицы желательно выбрать с учетом размеров исходной таблицы. Например, если исходные данные занимали 4 строки и 3 колонки, то диапазон для транспонированной таблицы должен занимать 3 колонки и 4 строки. Если это требование не соблюдать, а диапазон указать «с запасом», ничего страшного не произойдет. Просто в «лишних» ячейках транспонированной таблицы вы увидите значения «#Н/Д». Это означает, что для соответствующих ячеек функция «=Трансп()» не обнаружила данных.

Важно! Стереть значения «#Н/Д» клавишей «Del» не удастся. Excel запрещает удалять элементы формулы-массива.

Чтобы устранить это ограничение, сделайте так.

1) Выделите транспонированную таблицу.
2) Скопируйте ее в буфер обмена.
3) Не снимая выделения, вызовите «Правка \ Специальная вставка» (в MS Excel 2013-2016 щелкните ЛКМ по треугольнику под иконкой «Вставить»).
4) В окне параметров специальной вставки включите флажок «Значения».
5) Нажмите клавишу «ОК».

С этого момента участок рабочего листа с формулой-массивом превратился в обычные значения.

Вы сможете выделить ненужные ячейки и стереть их обычным способом (например, клавишей «Del»).

Вот и все. Удачной работы!

Как развернуть таблицу в Excel (Эксель)

Выполнения разворота
Что значит изменить положение таблицы на 90 градусов? При таком развороте строки занимают место столбцов, а столбцы соответственно место строк. При повороте на 180 градусов первые строки станут последними, то есть таблица перевернется. Рассмотрим технику выполнения разворота на 90 градусов. Такой разворот носит еще название транспонирования.
Самый простой способ поворота на 90 градусов, выполнить его с помощью вставки. Для начала выбираются столбцы и строки, которые следует развернуть.

Выделяем его с помощью мыши или кнопок клавиатуры. Затем всем знакомое «копировать»

И скопированный массив вставляем на выбранное место на листе, или другом листе.

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



Затем ищем в меню специальная вставка название «транспортировать», затем активируем ее. Таким образом, получим развернутый образ предыдущей таблицы.

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

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

Потом находим «удалить» и смотрим нужный вариант удаления строк. Жмем ок, и на листе останется только нужная таблица, которую следует привести в нужный вид.
То есть занимаемся форматированием таблицы. Лучше всего это поставить авто подбор ширины столбцов.
Произведя, подобные манипуляции с ячейками и столбцами получим более презентабельный вид таблицы.
Существует еще одни способ разворота таблицы. Он совершенно отличается от описанных способов выше и его стоит описывать отдельно.
В ексель существует функция ТРАНСП(), вот с ее помощью и делается транспортирование необходимой таблицы.
Для того что бы ей воспользоваться необходимо выделить область ячеек куда будет переноситься копируемая таблица. Следующим действием, вызываем саму функцию, нажав на значок функции.
Перед нами откроется окно. Это будет мастер функций. Ищем в нем название нужной функции, в нашем примере это транспорт и кнопкой ок активируем ее.
Перед нами будет окно. В нем находим поле под названием «массив».
Удерживаем левую кнопку мыши и выделяем необходимую для разворота область таблицы. И так как при работе с этим оператором имеем дело с массивом, то следует воспользоваться сочетанием трех клавиш ctrl, shift, enter. Одновременно нажимаем на них. В результате получим перевернутую таблицу в предполагаемом диапазоне.
Следует отметить, что при таком переносе таблицы в новый вид, связь новой таблицы с предыдущей сохраняется. И при изменении данных в первой произойдут изменения и во второй. Что можно считать при определенном варианте преимуществом. Например, вы презентуете таблицу номер два, а в первой проводите расчеты или что-либо еще. В случае же если есть необходимость сохранить данные во второй таблице неизменными, и что бы первая таблица не влияла на вторую, копируем вторую таблицу и вставляем на то же место, но используем функцию значения. На рисунках видно как ее найти и что сделать, что бы она сработала. После такого специального сохранения во второй таблицы будет только значения и связи с таблицей номер один будут разорваны. Что позволяет менять данные как угодно в обеих таблицах и это будут совершенно разные таблицы по своим значениям. После вышеописанного сохранения второй таблицы можно смело удалять, при необходимости первую таблицу. Ее удаление уже никак не повлияет на вторую таблицу. Удаление таблицы номер один проводится, так же как и в случае, описанном выше по тексту.
Разворачиваем таблицу на 180 градусов.
Напомним, что при таком развороте таблицы меняются местами строки таблицы с последней на первую.
Первый способ это использовать сортировку. Для того что бы воспользоваться данной функцией следует пронумеровать строки по порядку: 1, 2, 3 … При больших таблицах, где число строк больше 30 что бы быстро пронумеровать строки используем метод при котором нумеруем 1 сроку и удерживая палец на ctrl клавише и курсором тянем образовавший маркер до нижней строки таблицы. Нумерация строк произойдет автоматически. Что и видно на рисунках.
Далее выделяем столбец, в котором нумеровали строки, идем точно так как показано на рисунках ниже.
Путь: главная/сортировка и фильтр/настраиваемая сортировка. В результате получим сообщение, в котором нас уведомляют о том, что данные обнаружены не в рамках указанного диапазона.
Не трогаем вкладку «автоматически расширять диапазон», а активируем кнопку «сортировка». Получим окно с предлагаемым меню возможных сортировок.
Следует при сортировке игнорировать заголовки. Делаем, это удаляя птичку напротив надписи о сохранении заголовков в данных для сортировки. Область сортировки это тот столбец, который мы подвергли нумерации. Далее нас интересуют «значения». Находим ее и если там все проставлено, не трогаем. Идем на поиски «порядок» и ставим » по убыванию». Давим ок и получаем таблицу с заданными параметрами.
То есть нижняя строка стала верхней, что и требовалось сделать.
Функцией «очистить содержимое, удаляем не нужные значения в пронумерованном столбце.
На этом разворот таблицы на 180 градусов совершен.
Рассматривая этот способ, легко заметить, что таблица преобразовалась, и начальная структура таблицы не сохранилась. Довольно часто такое положение дел не всегда устраивает при работе с базой данных в таблицах. Поэтому при желании сохранить исходные данные, пользуются другим способом использую функцию «СМЕЩ ()» или смещения. Обычно с ним работают, когда хотят сместить данные какой-то одной из колонок. Но если применять эту формулу или функцию для каждого столбца, то это вполне можно достичь, создавая для каждого нужного столбца свои формулы. По сути, повторяя одну и ту же функцию изменяя лишь параметры столбцов.
Первым делом выделяем необходимую ячейку.
Нажав на значок функции, вставляем в нее СМЕЩ (). Перед нами окно мастера функций, в котором находим ссылки, массивы, активируем кнопку ок. Новое меню. Это меню самой функции.
Все значения составляющие аргументы функции, станут понятными, если поставить курсор в окошко каждого аргумента. Внизу окошка мелким шрифтом будет показано описание каждого аргумента. Нам лишь остается вставлять подходящие координаты ссылок.
Делаем, все как указано на рисунках.
В результате этих действий получим в первой ячейке наименование последней.
Для того что бы проставить все значения для каждой строки, следует скопировать уже имеющуюся формулу на все остальные не заполненные строки. Используем технику копирования, как проставляли номера столбцов в выше описанном способе. На рисунке ниже хорошо виден результат наших усилий.
Но в нашей таблице формулы. Если нужны значения, то копируем массив, и вставляем его в те же ячейки затем, через функцию «специальная вставка» и, активируя «значения», получаем вместо формул значения.
Вот и всё. Данные таблицы заполнены значениями, и их можно менять, править и так далее. Все способы, описанные выше, разнообразны и подходят для решения различных задач, где следует перевернуть таблицу на 90 или 180 градусов.


Как развернуть лист в экселе

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *