Как сделать вертикальный список горизонтальным и наоборот горизонтальный вертикальным при помощи кнопки транспонировать в «Excel».
Конечно не каждый пользователь «Excel» часто встречается с потребностью «переворачивать» список из горизонтального положения в вертикальное, но пригодится опция транспонирования может каждому. Кроме того, опция транспонирования данных значительно расширяет возможности аналитического анализа, выполняемого в «экселе» и упрощает сильно работу с таблицами.
Транспонировать (англ. Transpose) — переставлять, перемещать.
Рассмотрим работу опции «транспонировать» на примере случайно придуманного списка овощей, хранящихся на овоще базе.
Список овощей расположен вертикально:
| Овощи |
| Томаты; |
| Свекла; |
| Огурцы; |
| Репа; |
| Редька черная; |
| Редька белая. |
Приведенный список требуется расположить горизонтально.
Пошаговые действия:
Шаг 1.
Выделить ячейки со списком;
Шаг 2.
Копировать ячейки сочетанием клавиш » ctrl»+»C» или вызвав правой кнопкой мыши контекстное меню и выбрав строчку «Копировать»;
Шаг 3.
Перенести курсор в начало предполагаемого горизонтального списка;
Шаг 4.
Вызвать меню специальной вставки и выбрать опцию «Транспонировать».
Скопированные ячейки из колонки (столбца) будут вставлены в строку горизонтально.
Добавить комментарий Отменить ответ
Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.
Транспонирование таблицы в Microsoft Excel
Иногда встречаются ситуации, когда требуется перевернуть таблицу, то есть, поменять строки и столбцы местами. Конечно, можно полностью перебить все данные, как вам нужно, но это может занять значительное количество времени. Далеко не всем пользователям Excel известно о том, что в данном табличном процессоре имеется функция, которая поможет автоматизировать эту процедуру. Давайте изучим подробно, как строки сделать столбцами в Excel.
Процедура транспонирования
Смена местами столбцов и строчек в Экселе называется транспонированием. Выполнить данную процедуру можно двумя способами: через специальную вставку и с применением функции.
Способ 1: специальная вставка
Выясним, как транспонировать таблицу в Excel. Транспонирование с помощью специальной вставки является самым простым и популярным видом переворота табличного массива у пользователей.
-
Выделяем всю таблицу курсором мышки. Кликаем по ней правой кнопкой. В появившемся меню выбираем пункт «Копировать» или просто жмем на клавиатуре сочетание Ctrl+C.
Как видим, после этих действий исходная таблица была скопирована в новое место, но уже с перевернутыми ячейками.
Затем, можно будет удалить первоначальную таблицу, выделив её, кликнув курсором, и в появившемся меню выбрав пункт «Удалить…». Но можно этого и не делать, если она не будет вам мешать на листе.
Способ 2: применение функции
Второй способ переворачивания в Экселе предполагает применения специализированной функции ТРАНСП.
- Выделяем область на листе, равную по вертикальному и горизонтальному диапазону ячеек исходной таблице. Кликаем по значку «Вставить функцию», размещенному слева от строки формул.
- Открывается Мастер функций. В списке представленных инструментов ищем наименование «ТРАНСП». После того, как нашли, выделяем и жмем на кнопку «OK».
- Открывается окно аргументов. У данной функции только один аргумент – «Массив». Ставим курсор в его поле. Вслед за этим, выделяем всю таблицу, которую хотим транспонировать. После того, как адрес выделенного диапазона записан в поле, жмем на кнопку «OK».
Особенностью данного способа транспонирования, в отличие от предыдущего, является то, что изначальные данные удалять нельзя, так как это приведет к удалению транспонированного диапазона. Более того, любые изменения первичных данных приведут к такому же изменению их в новой таблице. Поэтому, этот способ особенно хорош для работы со связанными таблицами. В то же время, он существенно сложнее первого варианта. Кроме того, при использовании данного способа необходимо сохранять исходник, что не всегда является оптимальным решением.
Мы выяснили, как поменять местами столбцы и строки в Excel. Существует два основных способа перевернуть таблицу. Каким из них воспользоваться зависит от того, планируете ли вы применение связанных данных или нет. Если таких планов не имеется, то рекомендуется использовать первый вариант решения поставленной задачи, как более простой.
Транспонирование таблицы в Excel
Наверняка каждый пользователь, работающий в Экселе, сталкивался с такой ситуацией, когда строки и столбцы таблицы нужно поменять местами. Если речь идет про небольшой объем данных, то процедуру можно выполнить вручную, а в остальных случаях, когда информации много, весьма полезными или даже незаменимыми окажутся специальные инструменты, с помощью которых можно автоматически перевернуть таблицу. Давайте посмотрим, как это делается.
- Транспонирование таблицы
- Метод 1: используем специальную вставку
- Метод 2: применяем функцию “ТРАНСП”
Транспонирование таблицы
Транспонирование – это и есть “переброс” строк и столбцов таблицы местами. Данную операцию можно выполнить по-разному.
Метод 1: используем специальную вставку
Этот способ применяется чаще всего, и вот в чем он заключается:
- Любым удобным способом выделяем таблицу (например, с помощью зажатой левой кнопки мыши от левой верхней ячейки до нижней правой).
- Теперь щелкаем по выделенной области правой кнопкой мыши и в открывшемся контекстном меню выбираем команду “Копировать” (или вместо этого просто жмем комбинацию Ctrl+C).
- На этом же или на другом листе встаем в ячейку, которая станет самой верхней левой ячейкой транспонированной таблицы. Щелкаем по ней правой кнопкой мыши, и на этот раз нам в контекстном меню нужна команда “Специальная вставка”.
- В открывшемся окне ставим галочку напротив пункта “Транспонировать” и жмем OK.
- Как мы видим, в выбранном месте появилась автоматически перевернутая таблица, в которой столбцы исходной таблицы стали строками и наоборот.
Теперь мы можем приступить к настройке внешнего вида данных на наше усмотрение. Если исходная таблица больше не нужна, ее можно удалить.
Метод 2: применяем функцию “ТРАНСП”
Чтобы перевернуть таблицу в Эксель, можно воспользоваться специальной функцией “ТРАНСП”.
- На листе выделяем диапазон ячеек, который содержит столько же строк, сколько столбцов в исходной таблице, и соответственно, то же самое касается столбцов. Затем жмем кнопку “Вставить функцию” слева от строки формул.
- В открывшемся Мастере функций выбираем категорию “Полный алфавитный перечень”, находим оператор “ТРАНСП”, отмечаем его и жмем OK.
- На экране появится окно аргументов функции, где нужно указать координаты таблицы, на основании которой будет выполнено транспонирование. Сделать это можно вручную (ввод с клавиатуры) или путем выделения диапазона ячеек на листе. Когда все готово, нажимаем OK.
- Получаем вот такой результат на листе, но это еще не все.
- Теперь, чтобы вместо ошибки появилась транспонированная таблица, щелкаем по строке формул, чтобы приступить к редактированию ее содержимого, ставим курсор в самый конец, после чего жмем комбинацию клавиш Ctrl+Shift+Enter.
- Таким образом, нам удалось успешно транспонировать исходную таблицу. В строке формул видим, что теперь выражение обрамлено фигурными скобками.
Примечание: В отличие от первого способа, здесь не сохранилось первичное форматирование, что в некоторых случаях даже хорошо, так как мы можем настроить всё с нуля так, как нам хочется. Также у нас здесь нет возможности удалить первоначальную таблицу, так как функция “подтягивает” данные из нее. Зато безусловным плюсом является то, что таблицы связаны, т.е. любые изменения исходных данных сразу же отразятся на транспонированных.
Заключение
Таким образом, есть два способа, пользуясь которыми можно транспонировать таблицу в Excel. Они оба легко реализуемы, а выбор того или иного варианта зависит от дальнейших планов по работе с изначальными и полученными данными.
Превращение строк в столбцы и обратно
Хотим, упрощенно говоря, повернуть таблицу на бок, т.е. то, что располагалось в строке — пустить по столбцу и наоборот:
Способ 1. Специальная вставка
Выделяем и копируем исходную таблицу (правой кнопкой мыши — Копировать). Затем щелкаем правой по пустой ячейке, куда хотим поместить повернутую таблицу и выбираем из контекстного меню команду Специальная вставка (Paste Special) . В открывшемся диалоговом окне ставим галочку Транспонировать (Transpose) и жмем ОК.
Минусы : не всегда корректно копируются ячейки с формулами, нет связи между таблицами (изменение данных в первой таблице не повлияет на вторую).
Плюсы : в транспонированной таблице сохраняется исходное форматирование ячеек.
Способ 2. Функция ТРАНСП
Выделяем нужное количество пустых ячеек (т.е. если, например, исходная таблица была из 3 строк и 5 столбцов, то выделить обязательно нужно диапазон из 5 строк и 3 столбцов) и вводим в первую ячейку функцию ТРАНСП (TRANSPOSE) из категории Ссылки и массивы (Lookup and Reference):
После ввода функции необходимо нажать не Enter, а Ctrl+Shift+Enter, чтобы ввести ее сразу во все выделенные ячейки как формулу массива . Если раньше не сталкивались с формулами массивов, то советую почитать тут — это весьма экзотический, но очень мощный инструмент в Excel.
Плюсы : между таблицами сохраняется связь, т.е. изменения в первой таблице тут же отражаются во второй.
Минусы : не сохраняется форматирование, пустые ячейки из первой таблицы отображаются в виде нулей во второй, нельзя редактировать отдельные ячейки во второй таблице, поскольку формулу массива можно менять только целиком.
Способ 3. Формируем адрес сами
Этот способ отчасти похож не предыдущий, но позволяет свободно редактировать значения во второй таблице и вносить в нее любые правки при необходимости. Для создания ссылок на строки и столбцы нам понадобятся четыре функции из категории Ссылки и массивы:
- Функция АДРЕС(номер_строки; номер_столбца) — выдает адрес ячейки по номеру строки и столбца на листе, т.е. АДРЕС(2;3) выдаст, например, ссылку на ячейку C2.
- Функция ДВССЫЛ(ссылка_в_виде_текста) — преобразует текстовую строку, например, «F3» в настоящую ссылку на ячейку F3.
- Функции СТРОКА(ячейка) и СТОЛБЕЦ(ячейка) — выдают номер строки и столбца для заданной ячейки, например =СТРОКА(F1) выдаст 1, а =СТОЛБЕЦ(А3) выдаст 3.
Теперь соединяем эти функции, чтобы получить нужную нам ссылку, т.е. вводим в любую свободную ячейку вот такую формулу:
=ДВССЫЛ(АДРЕС(СТОЛБЕЦ(A1);СТРОКА(A1)))
в английской версии Excel это будет =INDIRECT(ADDRESS(COLUMN(A1),ROW(A1)))
А затем копируем (протягиваем) формулу на соседние ячейки как обычно черным крестом. В итоге должно получиться примерно следующее:
Т.е. при копировании формулы вниз по столбцу, она выдает ссылку, которая уходит вправо по строке и наоборот. Чего и требовалось.
Плюсы : сохраняются связи между таблицами, можно легко вносить изменения во вторую таблицу.
Минусы : форматирование не сохраняется, но его можно легко воспроизвести Специальной вставкой (вставить только Формат с флажком Транспонировать