Как сделать из горизонтальной таблицы excel вертикальную?

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

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

Процедура транспонирования

Смена местами столбцов и строчек в Экселе называется транспонированием. Выполнить данную процедуру можно двумя способами: через специальную вставку и с применением функции.

Способ 1: специальная вставка

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

  1. Выделяем всю таблицу курсором мышки. Кликаем по ней правой кнопкой. В появившемся меню выбираем пункт «Копировать» или просто жмем на клавиатуре сочетание Ctrl+C.

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

  • Открывается окно настройки специальной вставки. Устанавливаем галочку напротив значения «Транспонировать». Жмем на кнопку «OK».
  • Как видим, после этих действий исходная таблица была скопирована в новое место, но уже с перевернутыми ячейками.

    Затем, можно будет удалить первоначальную таблицу, выделив её, кликнув курсором, и в появившемся меню выбрав пункт «Удалить…». Но можно этого и не делать, если она не будет вам мешать на листе.

    Способ 2: применение функции

    Второй способ переворачивания в Экселе предполагает применения специализированной функции ТРАНСП.

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

    Открывается Мастер функций. В списке представленных инструментов ищем наименование «ТРАНСП». После того, как нашли, выделяем и жмем на кнопку «OK».

    Открывается окно аргументов. У данной функции только один аргумент – «Массив». Ставим курсор в его поле. Вслед за этим, выделяем всю таблицу, которую хотим транспонировать. После того, как адрес выделенного диапазона записан в поле, жмем на кнопку «OK».

    Ставим курсор в конец строки формул. На клавиатуре набираем сочетание клавиш Ctrl+Shift+Enter. Это действие нужно для того, чтобы данные корректно были преобразованы, так как мы имеем дело не с одной ячейкой, а с целым массивом.

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

  • Форматируем таблицу так, чтобы она имела приемлемый вид.
  • Особенностью данного способа транспонирования, в отличие от предыдущего, является то, что изначальные данные удалять нельзя, так как это приведет к удалению транспонированного диапазона. Более того, любые изменения первичных данных приведут к такому же изменению их в новой таблице. Поэтому, этот способ особенно хорош для работы со связанными таблицами. В то же время, он существенно сложнее первого варианта. Кроме того, при использовании данного способа необходимо сохранять исходник, что не всегда является оптимальным решением.

    Мы выяснили, как поменять местами столбцы и строки в Excel. Существует два основных способа перевернуть таблицу. Каким из них воспользоваться зависит от того, планируете ли вы применение связанных данных или нет. Если таких планов не имеется, то рекомендуется использовать первый вариант решения поставленной задачи, как более простой.

    Отблагодарите автора, поделитесь статьей в социальных сетях.

    Создание и управление таблицами в Excel

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

    Читать еще:  Как сделать табель учета рабочего времени в excel с нуля?

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

    Для начала необходимо активировать требуемые ячейки Excel посредством выделения мышкой (удержанием левой кнопки). После этого к выделенным ячейкам применяется форматирование.

    Для оформления таблицы в Excel требуемым образом используются следующие положения.

    Изменение высоты и ширины выделенных ячеек. Для изменения габаритов ячеек лучше всего использовать заголовки полей [A B C D] — по горизонтали и [1 2 3 4] — по вертикали. Следует навести курсор мышки на границу между двумя ячейками, после чего, зажав левую кнопку, потянуть границу в сторону и отпустить.

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

    Окно «Формат ячеек» может быть вызван тремя простыми способами:

    1. Комбинация клавиш Ctrl + 1 (единица не на цифровой клавиатуре, а над буквой «Й») — самый быстрый и удобный способ.
    2. В частности, можно использовать верхний блок меню функций Excel (что является максимально удобным способом, учитывая размещение блока всегда под рукой)
    3. Используя главное меню на закладку «Формат».

    Далее выполняем следующие действия:

    1. Наведя и нажав курсором мышки на закладку: «Формат ячеек».
    2. Всплывает окно с такими закладками, как «Защита», «Число», «Граница», «Вид», а также «Шрифт» и «Выравнивание».
    3. Для данной задачи необходимо использовать закладки «Граница» и «Выравнивание».

    Инструменты на вкладке «Выравнивание» имеют ключевые инструменты для эффективного редактирования введенного ранее текста внутри ячеек, а именно:

    1. Объединение выделенных ячеек.
    2. Возможность переноса по словам.
    3. Выравнивание введенного текста по вертикали и горизонтали (также в качестве быстрого доступа вкладка размещена в верхнем блоке меню).
    4. Ориентации текста по вертикали и под углом.

    Excel дает возможность проводить быстрое выравнивание всего набранного ранее текста по вертикали с использованием вкладки, размещенной в Главном меню.

    На вкладке «Граница» мы работаем с оформлением стиля линий границ таблицы.

    Переворот таблицы: как это делается?

    Допустим, пользователь создал в файле Excel таблицу следующего вида:

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

    Для начала необходимо выделить и скопировать всю таблицу. После этого следует активировать любую свободную в Excel ячейку, а потом посредством правой кнопки мышки вызвать меню, где нужно нажать вкладку «Специальная вставка». Или нажать комбинацию клавиш CTRL + ALT + V

    Далее необходимо установить галочку на вкладке «Транспонировать»

    И нажать левой кнопкой на «Ок». Как следствие, пользователь получит:

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

    Перенос значений из горизонтальной таблицы в вертикальную

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

    Допустим, что у пользователя Excel имеется прайс-лист, где прописаны цены следующего вида:

    Также имеется таблица, в которой произведен расчет стоимости всего заказа:

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

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

    1. В таблице, где размещен прайс-лист с ценами необходимо при помощи мышки выделить все значения. После этого, удерживая курсор мышки на ранее выделенном поле, необходимо правой кнопкой мышки вызвать меню и выбрать кнопку «Копировать»:
    2. Потом выделяется диапазон, в который следует вставить ранее выделенные цены.
    3. При помощи правой кнопки мышки вызывается меню, после чего, удерживая курсор над выделенной областью, необходимо выбрать кнопку «Специальная вставка».
    4. В завершение галочкой отмечается кнопка «Транспонировать» и наживается «Ок».
    Читать еще:  Как сделать несколько строчек в ячейке excel?

    Как следствие, получится следующий результат:

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

    Изменение размера таблицы во время корректировки Excel

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

    Для уменьшения размеров таблицы следует зайти на вкладку «Вид», выбрать при помощи мышки вкладку «Масштаб», после чего достаточно подобрать из выплывшего меню наиболее подходящий размер. Например, 80 процентов либо 95 процентов.

    Для увеличения размеров таблицы используется тот же порядок действий с той небольшой разницей, что масштаб ставится больше ста процентов. Например, 115 процентов либо 125 процентов.

    Excel располагает широким спектром возможностей для построения быстрой и эффективной работы. К примеру, при помощи специальной формулы (например, смещ($a$1;0;0счеттз($а:$а);2) можно настроить динамический диапазон используемой таблицы, что в процессе работы может быть крайне удобно, особенно при работе сразу с несколькими таблицами.

    ИнтеллигенцияX

    Excel: как «перевернуть»… таблицу

    При выполнении расчетов в 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 строки. Если это требование не соблюдать, а диапазон указать «с запасом», ничего страшного не произойдет. Просто в «лишних» ячейках транспонированной таблицы вы увидите значения «#Н/Д». Это означает, что для соответствующих ячеек функция «=Трансп()» не обнаружила данных.

    Читать еще:  Как сделать счетчик в excel с помощью формул?

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

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

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

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

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

    Горизонтальная таблица в вертикальную Excel

    Понятие «транспонировать» почти не встречается в работе пользователей ПК. Но тем, кто работает с массивами, будь то матрицы в высшей математике или таблицы в Excel, приходится сталкиваться с этим явлением.

    Транспонированием таблицы в Excel называется замена столбцов строками и наоборот. Иными словами — это поворот в двух плоскостях: горизонтальной и вертикальной. Транспонировать таблицы можно тремя способами.

    Способ 1. Специальная вставка

    Самый простой и универсальный путь. Рассмотрим сразу на примере. Имеем таблицу с ценой некоего товара за штуку и определенным его количеством. Шапка таблицы расположена горизонтально, а данные расположены вертикально соответственно. Стоимость рассчитана по формуле: цена*количество. Для наглядности примера подсветим шапку таблицы зеленым цветом.

    Нам нужно расположить данные таблицы горизонтально относительно вертикального расположения ее шапки.

    Чтобы транспонировать таблицу, будем использовать команду СПЕЦИАЛЬНАЯ ВСТАВКА. Действуем по шагам:

    1. Выделяем всю таблицу и копируем ее (CTRL+C).
    2. Ставим курсор в любом месте листа Excel и правой кнопкой вызываем меню.
    3. Кликаем по команде СПЕЦИАЛЬНАЯ ВСТАВКА.
    4. В появившемся окне ставим галочку возле пункта ТРАНСПОНИРОВАТЬ. Остальное оставляем как есть и жмем ОК.

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

    Аналогично можно транспонировать только значения, без наименований строк и столбцов. Для этого нужно выделить только массив со значениями и проделать те же действия с командой СПЕЦИАЛЬНАЯ ВСТАВКА.

    Способ 2. Функция ТРАНСП в Excel

    С появлением СПЕЦИАЛЬНОЙ ВСТАВКИ транспонирование таблицы при помощи команды ТРАНСП почти не используется по причине сложности и большего времени на операцию. Но функция ТРАНСП все же присутствует в Excel, поэтому научимся ею пользоваться.

    Снова действует по этапам:

    1. Правильно выделяем диапазон для транспонирования таблицы. В данном примере исходной таблицы имеется 4 столбца и 6 строк. Соответственно мы должны выделить диапазон ячеек в котором будет 6 столбцов и 4 строки. Как показано на рисунке:

    Обратите внимание, что формула не скопировалась. При нажатии на каждую ячейку становится видно, что эта таблица была транспонирована. К тому же все исходное форматирование утеряно. Придется выравнивать и подсвечивать снова. Так же стоит обратить внимание на то что транспонированная таблица привязанная к исходной. Измененные значения исходной таблице автоматически обновляются в транспонированной.

    Способ 3. Сводная таблица

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

      Создадим сводную таблицу. Для этого выделим исходную таблицу и откроем пункт ВСТАВКА — СВОДНАЯ ТАБЛИЦА.

    Скачать примеры транспонирования таблиц в Excel.

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

    Ссылка на основную публикацию
    Adblock
    detector