Разворот таблицы в Microsoft Excel

Автор: | 29.11.2021

Распространение в Microsoft Excel

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

Выполнения разворота

Как уже было сказано, стол можно повернуть на 90 или 180 градусов. В первом случае это означает, что столбцы и строки меняются местами, а во втором таблица переворачивается сверху вниз, то есть таким образом, что первая строка становится последней. Для выполнения этих задач существует несколько методик разной сложности. Изучаем алгоритм их применения.

Способ 1: разворот на 90 градусов

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

Транспонировать с помощью кнопки на ленте в Microsoft Excel

Транспонированная таблица в Microsoft Excel

Удаление основной таблицы в Microsoft Excel

основная таблица удалена в Microsoft Excel

Включить автоматическую настройку ширины столбца в Microsoft Excel

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

    Скопируйте таблицу через контекстное меню в Microsoft Excel

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

    Скопируйте таблицу с помощью кнопки на ленте в Microsoft Excel

    Но самый быстрый вариант — нажать одновременно Ctrl + C после отметки фрагмента. В этом случае также будет выполнено копирование.

  2. Любую пустую ячейку на листе обозначаем свободным свободным местом. Этот элемент должен стать левой верхней ячейкой транспонированного диапазона. Щелкните этот объект правой кнопкой мыши. Блок «Специальная вставка» может содержать значок «Транспонировать». Подбираем его.

    Транспонировать через контекстное меню в Microsoft Excel

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

    Вставить через контекстное меню в Microsoft Excel

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

    Перейдите в окно Специальная вставка через контекстное меню в Microsoft Excel

    Откроется окно Специальная вставка. Установите флажок напротив значения «Транспонировать». Никаких дополнительных манипуляций в этом окне производить не нужно. Нажимаем на кнопку «ОК».

    Транспонировать с помощью специальной вставки в Microsoft Excel

    Эти действия также можно выполнить с помощью кнопки на ленте. Обозначим ячейку и щелкнем по треугольнику, который находится под кнопкой «Вставить», расположенной на вкладке «Главная» в разделе «Буфер обмена». Список открывается. Как видите, он содержит как значок «Транспонировать», так и элемент «Специальная вставка…». Если вы выберете значок, транспонирование произойдет мгновенно. Когда вы нажимаете на пункт «Специальная вставка», откроется окно Специальная вставка, о котором мы уже говорили выше. Все дальнейшие действия в нем точно такие же.

  3. После выполнения одного из этих множества вариантов результат будет таким же: будет сформировано табличное пространство, которое является 90-градусной версией первичного массива. То есть, по сравнению с исходной таблицей, в транспонированной области строки и столбцы поменялись местами.
  4. Мы можем оставить оба табличных пространства на листе или удалить основное, если оно больше не нужно. Для этого укажите весь диапазон, который нужно удалить, над транспонированной таблицей. Затем на вкладке «Главная» щелкните треугольник, который находится справа от кнопки «Удалить» в разделе «Ячейки». В раскрывающемся списке выберите параметр «Удалить строки из листа».
  5. Впоследствии все строки, включая область первичной таблицы, которые находятся над транспонированным массивом, будут удалены.
  6. Затем, чтобы транспонированный интервал принял компактный вид, обозначьте его целиком и, перейдя во вкладку «Основные», нажмите кнопку «Форматировать» в разделе «Ячейки». В открывшемся списке выберите параметр «По ширине столбца».
  7. После выполнения последнего шага массив таблиц приобрел компактный и презентабельный вид. Теперь мы можем ясно видеть, что в нем, по сравнению с исходным диапазоном, строки и столбцы поменялись местами.

Перевернутая таблица теперь компактна в Microsoft Excel

Кроме того, вы можете транспонировать область таблицы с помощью специального оператора Excel под названием «TRANSPOSE». Функция TRANSPOSE специально разработана для преобразования вертикального диапазона в горизонтальный и наоборот. Его синтаксис следующий:

= ПЕРЕДАЧА (массив)

«Массив» — единственный аргумент этой функции. Это ссылка на диапазон, который нужно переворачивать.

Перейти к функции мастера в Microsoft Excel

Перейдите в окно аргументов функции ТРАНСПОРТ в Microsoft Excel

Окно темы TRANSPOSE в Microsoft Excel

Результат обработки функции ТРАНСПОРТ в Microsoft Excel

Информационное сообщение в Microsoft Excel

Скопируйте таблицу с помощью кнопки на ленте в Microsoft Excel

Ввод значений через контекстное меню в Microsoft Excel

  1. Обозначьте на листе диапазон пустых ячеек. Количество элементов в указанном столбце фрагмента должно соответствовать количеству ячеек в строке таблицы, а количество элементов в строках пустого массива должно совпадать с количеством ячеек в столбцах области таблицы. Затем щелкаем по значку «Вставить функцию».
  2. Мастер активирован. Переходим в раздел «Ссылки и массивы». Отмечаем там название «ТРАНСПОРТ» и нажимаем «ОК»
  3. Откроется окно аргументов для предыдущего оператора. Устанавливаем курсор в его единственное поле — «Массив». Зажимаем левую кнопку мыши и отмечаем область таблицы, которую нужно расширить. В этом случае его координаты будут отображаться в поле. После этого не спешите нажимать кнопку «ОК», как обычно. Это функция массива, поэтому нажмите комбинацию клавиш Ctrl + Shift + Enter для правильного выполнения процедуры.
  4. Перевернутая таблица, как видите, вставляется в отмеченный массив.
  5. Как видите, недостатком этого варианта перед предыдущим является то, что исходное форматирование не сохраняется при транспонировании. Кроме того, когда вы пытаетесь изменить данные в любой ячейке транспонированного диапазона, появляется сообщение о том, что часть массива не может быть изменена. Кроме того, транспонированный массив связан с первичным диапазоном, и когда источник удаляется или изменяется, он также будет удален или изменен.
  6. Но с двумя последними недостатками справиться довольно просто. Отмечаем весь транспонированный интервал. Щелкаем по значку «Копировать», который находится на ленте в категории «Буфер обмена».
  7. После этого, не снимая обозначения, щелкните по транспонированному фрагменту правой кнопкой мыши. В контекстном меню в категории «Параметры вставки» щелкните значок «Значения». Эта пиктограмма представлена ​​в виде квадрата, на котором написаны цифры.
  8. После выполнения этого шага формула в диапазоне будет преобразована в нормальные значения. Теперь найденные в нем данные можно редактировать как угодно. Кроме того, этот массив больше не связан с исходной таблицей. Теперь, при желании, исходная таблица может быть отброшена таким же образом, как описано выше, а инвертированный массив может быть отформатирован соответствующим образом, чтобы он выглядел информативным и презентабельным.

Транспонированный диапазон преобразован в значения в Microsoft Excel

Способ 2: разворот на 180 градусов

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

Самый простой способ сделать это — воспользоваться возможностями сортировки.

Индикатор заполнения в Microsoft Excel

Столбец заполняется числами по порядку с помощью индикатора заполнения в Microsoft Excel

Перейти на настраиваемую сортировку в Microsoft Excel

Расширить выбранный диапазон через диалоговое окно в Microsoft Excel

Окно произвольной сортировки в Microsoft Excel

Очистить содержимое пронумерованного столбца в Microsoft Excel

  1. Справа от таблицы, возле ее верхнего ряда, ставим цифру «1». Далее мы помещаем курсор в правый нижний угол ячейки, где задано указанное число. Это превращает курсор в маркер заполнения. Одновременно нажмите и удерживайте левую кнопку мыши и клавишу Ctrl. Продвигаем курсор к нижнему краю таблицы.
  2. Как видите, после чего весь столбец заполняется числами по порядку.
  3. Размечаем столбик с нумерацией. Перейдите на вкладку «Главная» и нажмите кнопку «Сортировка и фильтр», которая находится на ленте в разделе «Редактировать». В открывшемся списке останавливаем выбор на опции «Пользовательская сортировка».
  4. Откроется диалоговое окно, информирующее вас о том, что данные были найдены за пределами указанного диапазона. По умолчанию переключатель в этом окне установлен на «Автоматически расширять выбранный диапазон». Вы должны оставить его в том же положении и нажать на кнопку «Заказать…».
  5. Откроется окно пользовательской сортировки. Убедитесь, что флажок «Мои данные содержат заголовки» снят, даже если заголовки действительно есть. В противном случае они не будут опускаться, а останутся на столе. В области «Сортировать по» нужно выбрать название столбца, в котором выставлена ​​нумерация по порядку. В области «Сортировка» нужно оставить параметр «Значения», который выбран по умолчанию. В области «Порядок» установите параметр «По убыванию». После выполнения этих инструкций нажмите кнопку «ОК».
  6. Далее массив таблиц будет отсортирован в обратном порядке. В результате такой сортировки она будет перевернута, т.е последняя строка станет заголовком, а заголовок станет последней строкой.

    Таблица отсортирована в Microsoft Excel

    Важная заметка! Если в таблице есть формулы, из-за такой сортировки их результат может отображаться некорректно. Поэтому в этом случае нужно вообще отказаться от переворачивания или сначала преобразовать результаты расчетных формул в значения.

  7. Теперь мы можем удалить лишний столбец с нумерацией, так как он нам больше не нужен. Отмечаем его, щелкаем правой кнопкой мыши по отмеченному фрагменту и выбираем в списке «Удалить контент».
  8. Теперь работы по расширению стола на 180 градусов можно считать завершенными.

Таблица повернута на 180 градусов в Microsoft Excel

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

Перейти к функции мастера в Microsoft Excel

Перейдите в окно аргументов функции СМЕЩЕНИЕ в Microsoft Excel

Окно аргументов функции СМЕЩЕНИЕ в Microsoft Excel

Результат вычисления функции СМЕЩЕНИЕ в Microsoft Excel

Индикатор заполнения в Microsoft Excel

Диапазон, заполненный данными, перевернутыми в Microsoft Excel

Скопируйте диапазон с помощью кнопки на ленте в Microsoft Excel

Вставьте значения в Microsoft Excel

  1. Отметьте ячейку справа от диапазона, который нужно перевернуть, в первой строке. Нажмите кнопку «Вставить функцию».
  2. Мастер запускается. Перейдите в раздел «Подключения и массивы» и отметьте имя «СМЕЩЕНИЕ», затем нажмите «ОК».
  3. Откроется окно темы. Функция СМЕЩЕНИЕ предназначена для интервалов смещения и имеет следующий синтаксис:

    = СМЕЩЕНИЕ (ref; line_offset; column_offset; height; width)

    Аргумент Link — это ссылка на последнюю перемещаемую ячейку или диапазон в массиве.

    Смещение по строкам — это аргумент, указывающий, насколько таблица должна быть смещена по строкам;

    «Смещение по столбцам» — аргумент, указывающий, насколько таблица должна быть смещена по столбцам;

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

    Затем мы помещаем курсор в поле «Ссылка» и отмечаем последнюю ячейку диапазона, которую нужно перевернуть. В этом случае ссылка должна быть абсолютной. Для этого отметьте его и нажмите клавишу F4. Рядом с координатами ссылки должен появиться знак доллара ($).

    Затем поместите курсор в поле «Смещение линии» и в нашем случае напишите следующее выражение:

    (СТРОКА () — СТРОКА ($ A $ 2)) * — 1

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

    В поле «Сдвиг столбца» введите «0».

    Оставьте поля «Высота» и «Ширина» пустыми. Щелкните «ОК».

  4. Как видите, значение, которое раньше находилось в самой нижней ячейке, теперь отображается вверху нового массива.
  5. Чтобы перевернуть другие значения, вам нужно скопировать формулу из этой ячейки во весь нижний диапазон. Сделаем это с помощью маркера заливки. Поместите курсор в нижний правый край элемента. Ждем, когда он превратится в маленький крестик. Удерживая левую кнопку мыши, перетащите вниз до края массива.
  6. Как видите, весь диапазон заполнен инвертированными данными.
  7. Если мы хотим, чтобы ячейки содержали не формулы, а значения, отметьте указанную область и нажмите кнопку «Копировать» на ленте.
  8. Затем щелкаем правой кнопкой мыши по отмеченному фрагменту и в блоке «Вставить параметры» выбираем значок «Значения».
  9. Данные в инвертированном диапазоне теперь вставляются как значения. Исходную таблицу можно удалить или оставить как есть.

Данные в виде значений, введенных в Microsoft Excel

Как видите, существует несколько совершенно разных способов расширить массив таблиц на 90 и 180 градусов. Выбор конкретного варианта, в первую очередь, зависит от поставленной перед пользователем задачи.