Как: использовать функцию transpose в excel для переворота строк или столбцов — 2021

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

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

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

Создаем её на новом листе.

В свойствах сводной таблицы отключаем общие и промежуточные итоги по столбцам и строкам.

Перетаскиваем поля в макете таблицы, и получаем результат – таблица повёрнута набок.

Транспонирование таблиц в Word

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

Транспонирование при помощи Excel

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

1. В редакторе Word выделяем ячейки таблицы, которую необходимо транспонировать, и копируем их в буфер обмена Windows.

2. Открываем Excel и переносим в него нашу свежеиспеченную таблицу.

3. Не выходя за пределы табличного редактора, вновь выделяем мышью таблицу и копируем ее в буфер обмена.

4. Курсором помечаем левую верхнюю ячейку области вставки

При этом обратите внимание, что область вставки не должна накладываться на область копирования

5. Жмем правую кнопку мыши, в контекстном меню выбираем пункт «Специальная вставка. «, далее в появившемся окне ставим галочку напротив опции «Транспонировать» и нажимаем на клавишу OK для вставки таблицы в лист Excel.

6. Путем копирования переносим транспонированную таблицу обратно в документ Word и повторно форматируем данные на свой лад.

Транспонирование средствами надстройки WrdTools для редактора Microsoft Word

Второй способ, о котором мы бы хотели рассказать, заключается в использовании специального плагина WrdTools (800 кб) стоимостью в 9,5 долларов США. Разработанная Николаем Расторгуевым надстройка в процессе инсталляции интегрируется в панель инструментов редактора Word и умеет выполнять массу других задач кроме банального транспонирования таблиц. Пугаться условий распространения продукта не стоит, так как пользоваться плагином можно в течение пятнадцати дней без регистрации программы. После истечения данного срока надстройку придется либо купить, либо удалить с компьютера.

WrdTools позволяет транспонировать любые таблицы, в том числе и с нерегулярной структурой (с неодинаковым количеством ячеек в строках). При этом таблица может содержать объединенные ячейки или только по колонкам, или только по строкам. Если присутствуют оба варианта объединенных ячеек, то табличные данные необходимо преобразовать таким образом, чтобы они содержали только один тип объединения.

Схема работы с надстройкой следующая.

1. Выделяется мышью таблица, которую нужно преобразовать, и в панели инструментов Word нажимается кнопка «Транспонировать таблицу».

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

3. Нажимается кнопка OK для начала процесса транспонирования таблиц.

Перед установкой программы обязательно убедитесь, что в редакторе стоит разрешение на запуск макросов (меню «Сервис -> Макрос -> Безопасность»), иначе вся эффективность надстройки будет загублена на корню.

Три способа как транспонировать таблицу в Excel

​ обуви (столбец №1) повторяется​ ячейке, у которой​Вместо​H19 ​);​ общий итог.​ СВОДНАЯ ТАБЛИЦА.​ тому же все​ должны выделить диапазон​ Для этого нужно​

​ ТРАНСПОНИРОВАТЬ. Остальное оставляем​ таблицы расположена горизонтально,​ встречается в работе​ ниже

Обратите внимание на​Е​ (у нас будет​ размеров иметь актуальную​ столько раз, сколько​ номер строки совпадает​

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

​ENTER​- ссылка на верхнюю левую​скопировать таблицу в Буфер​Скачать примеры транспонирования таблиц​Местом, где будет создана​ исходное форматирование утеряно.​ ячеек в котором​ выделить только массив​ как есть и​ а данные расположены​ пользователей ПК. Но​ использование смешанных ссылок в формуле.​подсчитаем количество размеров​ их 2).​

​ транспонированную таблицу.​ у него имеется​ с номером столбца​нажать​

​ ячейку транспонированной таблицы​ обмена (​ в Excel.​ сводная таблица, выбираем​

1. ​ Придется выравнивать и​ будет 6 столбцов​
2. ​ со значениями и​ жмем ОК.​ вертикально соответственно. Стоимость​ тем, кто работает​
3. ​ При копировании формулы​ каждого наименования: =СЧЁТЕСЛИ(Наименования;D6)​
4. ​Для этого создадим Динамический​Для упрощения формул наложим​ различных размеров (столбец​ (​CTRLSHIFTENTER​

​ (на ту ячейку,​CTRL+C​Это достаточно своеобразный способ​ новый лист.​ подсвечивать снова. Так​ и 4 строки.​ проделать те же​В результате получили ту​ рассчитана по формуле:​ с массивами, будь​ вправо ссылка $E6​Примечание​ диапазон Наименования, чтобы​ ограничения на исходную​ №2). Иногда требуется​А1, B2, C3​.​ в которую Вы​);​

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

​: Не забудьте, что Наименования​

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

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

файл​выделить ячейку ниже таблицы​ заменить столбцы на​ таблиц можем выбрать​ внимание на то​ рисунке:​

1. ​ с другим расположением​ примера подсветим шапку​ высшей математике или​ зафиксирован столбец (зато​ в вышеприведенной формуле​ данных в исходную​ одного наименования не​ таблицу, а только​В​ (​ примера.​ (​
2. ​ строки (наоборот –​ необходимые пункты и​ что транспонированная таблица​Сразу заполняем активную ячейку​
3. ​​ строк и столбцов.​ таблицы зеленым цветом.​ таблицы в Excel,​ F$4 изменяется на​ — это Имя​ таблицу, транспонированная таблица​ может быть больше​ один столбец №2.​

​– это второй​A7​Также см. статью Функция СМЕЩ()​A8​ не получится) и​ перенести их в​ привязанная к исходной.​ так чтобы не​С появлением СПЕЦИАЛЬНОЙ ВСТАВКИ​ Причем, заметим, что​Нам нужно расположить данные​ приходится сталкиваться с​ G$4, затем на​ созданного ранее Вами​ автоматически обновлялась.​ 10 (определяет количество​ Т.е. теперь у​

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

​ столбец,​) ввести формулу =ДВССЫЛ(АДРЕС(СТОЛБЕЦ(A1);СТРОКА(A1))),​ в MS EXCEL​);​ дополнительно узнать общую​ нужные поля. Перенесем​ Измененные значения исходной​ снять выделенную область.​ транспонирование таблицы при​ зеленым подсвечены ячейки​

1. ​ таблицы горизонтально относительно​ этим явлением.​ H$4 и т.д.),​ Динамического диапазона.​Формула для Динамического диапазона: =СМЕЩ(Лист1!$A$6;;;СЧЁТЗ(Лист1!$A$6:$A$19)).​
2. ​ столбцов с размерами​ нас будет столбец​С​
3. ​ где​выделить нужное количество пустых​в меню Вставить (Главная​ сумму по заданному​ «продукт» в НАЗВАНИЯ​ таблице автоматически обновляются​ Вводим следующую формулу:=ТРАНСП(A1:D6)​ помощи команды ТРАНСП​ с тем же​
4. ​ вертикального расположения ее​Транспонированием таблицы в Excel​ а при копировании​В строке 4 (см.​
5. ​Затем создадим перечень уникальных наименований​ в транспонированной таблице);​ наименований обуви (уже​– это третий​А1​ ячеек вне таблицы​ / Буфер обмена)​ полю.​ СТОЛБЦОВ, а «цена​

​ в транспонированной.​Нажимаем CTRL+SHIFT+ENTER

Внимание! Функия​. ​ почти не используется​ содержанием

Формула стоимости​ шапки.​ называется замена столбцов​ вниз наоборот ссылка F$4 не​ файл примера) создайте​ в столбце​ таблица отсортирована сначала​

Формула стоимости​ шапки.​ называется замена столбцов​ вниз наоборот ссылка F$4 не​ файл примера) создайте​ в столбце​ таблица отсортирована сначала​

​ почти не используется​ содержанием. Формула стоимости​ шапки.​ называется замена столбцов​ вниз наоборот ссылка F$4 не​ файл примера) создайте​ в столбце​ таблица отсортирована сначала​

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

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

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

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

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

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

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

Превращение строк в столбцы и обратно

Постановка задачи

Хотим, упрощенно говоря, повернуть таблицу на бок, т.е. то, что располагалось в строке — пустить по столбцу и наоборот:

Способ 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.

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

в английской версии Excel это будет =INDIRECT(ADDRESS(COLUMN(A1),ROW(A1)))

А затем копируем (протягиваем) формулу на соседние ячейки как обычно черным крестом. В итоге должно получиться примерно следующее:

Т.е. при копировании формулы вниз по столбцу, она выдает ссылку, которая уходит вправо по строке и наоборот. Чего и требовалось.

Плюсы : сохраняются связи между таблицами, можно легко вносить изменения во вторую таблицу.

Минусы : форматирование не сохраняется, но его можно легко воспроизвести Специальной вставкой (вставить только Формат с флажком Транспонировать

30 функций Excel за 30 дней: ТРАНСП (TRANSPOSE)

​ чтобы она стала​​ наименованием обуви Кроссовки дет.​Формула для Динамического диапазона: =СМЕЩ(Лист1!$A$6;;;СЧЁТЗ(Лист1!$A$6:$A$19)).​​ столько раз, сколько​ желанию).​4х2​​ последние годы.​​ таблицы расположен в​ (на ту ячейку,​ см. статью Транспонирование​

​ на рабочем листе,​ объект вовсе, и​​ со Smart в​​ ней определяют что​ таблицы перенести столбец​ появится диалоговое окно​ характеризует определенный диапазон​ более удобной для​ с 3-мя размерами​Затем создадим перечень уникальных наименований​ у него имеется​Функция​:​Чтобы изменить ориентацию данных,​ другой ячейке, например​

​ в которую Вы​ таблиц.​ который нужно транспонировать.​​ все эти Range,​​ VBA тяжело работать,​ она Smart, а​ показателей (повтор каждого​ с ошибкой «Нельзя​ ячеек, над которым​

Функция 13: TRANSPOSE (ТРАНСП)

​ работы с ней.​​ (12, 13, 14).​​ в столбце​ различных размеров (столбец​TRANSPOSE​

Как можно использовать функцию TRANSPOSE (ТРАНСП)?

​Выделите 8 ячеек, куда​​ не создавая ссылок​​ в​ вводите первую формулу) См. файл​Также транспонирование диапазонов значений​ Транспонирование массива заключается​

• ​ Cells …. это​ почему и ушел​
• ​ то что это​ показателя по количеству​ изменить часть массива».​

​ производится операция транспонирования.​Таблица зарплат:​Различные наименования обуви отделены​

​D​​ №2). Иногда требуется​​(ТРАНСП) может использоваться​​ Вы хотите разместить​​ на исходные данные:​

Синтаксис TRANSPOSE (ТРАНСП)

​J2​​ примера.​​ можно осуществить с​ в том, что​

​ именованный диапазон с​​ дат в первой​ Для получения возможности​Примечания 1:​Сначала выделяем диапазон ячеек​

Ловушки TRANSPOSE (ТРАНСП)

• ​ красными горизонтальными линиями​​с помощью формулы​​ «перевернуть» не всю​ в сочетании с​ полученный вертикальный диапазон.​​Используйте​​, формула немного усложняется:​
• ​Также см. статью Функция СМЕЩ()​ помощью Специальной вставки​​ первая строка массива​​ чтоб обратится, а​ последнее время.​ именованными внутри него​ строке исходной таблицы)​ редактировать новую таблицу​Если в качестве аргумента​

Пример 1: Превращаем горизонтальные данные в вертикальные

​ A9:G21. Ведь для​ с помощью Условного​ массива: ​ таблицу, а только​​ другими функциями, например,​​ В нашем примере​Paste Special​=ДВССЫЛ(​ в MS EXCEL​ (Главная/ Буфер обмена/​ становится первым столбцом​ для того чтоб​а, да -​ полями, с авто-изменением​​2) перенести даты​​ необходимо:​ функции были ошибочно​ транспонирования приведенной таблицы​ форматирования.​

​=ЕСЛИОШИБКА(ИНДЕКС(Наименования;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$5:D5;Наименования);0));»»)​​ один столбец №2.​​ как в этой​​ это будут ячейки​​(Специальная вставка) >​

1. ​АДРЕС(СТОЛБЕЦ(J2)+СТРОКА($J$2)-СТОЛБЕЦ($J$2);​выделить нужное количество пустых​ Вставить/ Транспонировать). Для​ нового массива, вторая​ в массив запихнуть?​ если галки не​
2. ​ диапазона с авто-заполнением​ в плоскую таблицу​выделить весь диапазон входящих​​ переданы данные формата​​ мы выделяем область​

​ Т.е. теперь у​ сногсшибательной формуле. Она​ B4:C7.​Transpose​СТРОКА(J2)-СТРОКА($J$2)+СТОЛБЕЦ($J$2))​ ячеек вне таблицы​

​ этого выделите исходный​​ — вторым столбцом​​ Ну пусть будет​ там стоят, то​ строк формулами ,​ для каждого показателя​​ в ее состав​​ Имя, функция ТРАНСП​ шириной в 6​Функция ТРАНСП в Excel​D6​ нас будет столбец​ была опубликована Харланом​Введите следующую формулу и​

Пример 2: Изменяем ориентацию без ссылок

​ (​ диапазон.​ и т. д.​ так, а то​ формулы получаются нереально​ форматом…. Мы же​

1. ​С первым пунктом​ ячеек и скопировать​
2. ​ вернет код ошибки​ ячеек и высотой​ используется для транспонирования​
3. ​. Не забудьте после ввода​​ наименований обуви (уже​​ Гроувом в новостном​ превратите её в​​Функция​​Вчера в марафоне​
4. ​A7:E11​​Выделите левую верхнюю ячейку​​Пусть дан столбец с​
5. ​ я может еще​ длинные и нечитаемые.​

​ можем сформировать кросс​​ благополучно справился собственноручно.​​ данные в буфер​ #ИМЯ?. Числовые, текстовые​ в 12 ячеек​ (изменения направления отображения)​ формулы массива нажать​ без повторов), а​ блоке Excel в​ формулу массива нажатием​TRANSPOSE​30 функций Excel за​). Число выделенных строк​ диапазона, в который​ пятью заполненными ячейками​

Перевернуть столбец или строку при помощи кнопки «Транспонировать» в Excel.

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

Транспонировать (англ. Transpose) — переставлять, перемещать.

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

Список овощей расположен вертикально:

Овощи

Томаты;

Свекла;

Огурцы;

Репа;

Редька черная;

Редька белая.

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

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

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

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

Как вы только что видели, функция «Специальная вставка» позволяет выполнять преобразование из строки в столбец (или из столбца в строку) буквально за несколько секунд. Этот метод также копирует форматирование ваших исходных данных, чем добавляет еще один плюс в свою пользу.

Однако у этого подхода есть два недостатка, которые не позволяют называть его идеальным решением чтобы перевернуть данные в Excel:

• Он не слишком подходит для вращения полнофункциональных таблиц Excel. Если вы скопируете всю таблицу и затем откроете диалоговое окно «Специальная вставка», то обнаружите, что пункт «Транспонировать» неактивен. В этом случае вам нужно либо скопировать ее без заголовков столбцов, либо сначала преобразовать ее в обычный диапазон.
• Операция Специальная вставка > Транспонировать не связывает новую таблицу с исходными данными. Поэтому она хорошо подходит только для одноразовых преобразований. Всякий раз, когда исходные данные меняются, вам нужно будет повторить процесс и повернуть таблицу заново. Никто не захочет тратить свое время на преобразование одних и тех же строк и столбцов снова и снова, верно?

Как транспонировать таблицу и связать ее с исходными данными

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

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

1. Результат будет примерно таким:

1. Выберите новую таблицу и откройте диалоговое окно Excel «Найти и заменить» (или нажмите , чтобы сразу перейти на вкладку Заменить).
2. Замените все знаки «=» на «###» или любые другие символы, которых нет нигде в ваших реальных данных. Таким нехитрым образом мы преобразуем наши формулы-ссылки в текст.

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

1. Скопируйте диапазон со значениями «###» в буфер обмена, а затем используйте Специальная вставка > Транспонировать, чтобы преобразовать столбцы в строки.

1. Наконец, откройте диалоговое окно «Найти и заменить» еще раз, чтобы отменить изменение, т.е. обратно заменить все «###» на «=», и восстановить ссылки на исходные данные.

Это длинноватое, но элегантное решение, не правда ли? Единственным недостатком этого подхода является то, что исходное форматирование теряется в процессе, и вам нужно будет восстановить его вручную (в дальнейшем в этом руководстве я покажу вам быстрый способ сделать это).

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

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

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

Правильно выделяем диапазон для транспонирования таблицы. В данном примере исходной таблицы имеется 4 столбца и 6 строк. Соответственно мы должны выделить диапазон ячеек в котором будет 6 столбцов и 4 строки. Как показано на рисунке:
Сразу заполняем активную ячейку так чтобы не снять выделенную область. Вводим следующую формулу:=ТРАНСП(A1:D6)
Нажимаем CTRL+SHIFT+ENTER

Внимание! Функия ТРАНСП()работает тилько в массиве. Поэтому после ее ввода нужно обязательно нажать комбинацию горячих клавиш CTRL+SHIFT+ENTER для выполнения функции в массиве, а не просто ENTER.

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

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

3 способа транспонировать в Excel данные

Добрый день уважаемый читатель!

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

Итак, транспонирование — происходит от глагола «транспонировать», далее от немецкого «transponieren», далее из французского «transposer» «переставлять, перемещать», далее из латынского «trānspōnere» — «перекладывать», «переносить», , математическое обозначение, это преобразование матрицы, в результате чего, столбцы становятся строками, а строки превращаются в столбцы. Говоря простым языком, это все значение в строке (горизонталь) перекинуть в столбец (вертикаль).

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

Возможность транспонировать в Excel реализовано 3 способами:

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

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

Для начала создаем таблицу для исходных данных:

Следующим шагом переходим к меню «Вставка» — «Сводная таблица», в диалоговом окне указываем данные для анализа и место где расположится сводная таблица:

Для анализа использована функция транспонировать в Excel, я использовал наглядно столбик «Наименование», которое сделал строкой и шапкой в таблице и столбец «Всего» которое превратилось в строковое значение:

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

Вторым вариантом транспонировать в Excel будет использование функции Excel «=ТРАНСП()», которая разрешит сохранить связь с первоначальными данными:

Создаем исходные данные, ну или они уже есть у нас в наличии. К примеру, наши данные расположены с 4-й по 14-ю строки и соответственно, 3 столбца:

В нужном вам месте вводите формулу «=ТРАНСП()» и передаете в неё ссылку на весь объем диапазона, который необходимо транспонировать в Excel:

После того как выделен диапазон для вставки формулы, в формулу введен массив исходных данных, клацнем на кнопочку F2 и сразу же используем комбинацию гарячих клавиш Ctrl+Shift+Enter, таким способом вводится формула на весь массив и как результат получаем перевернутые данные которые сохраняют ссылки на исходные данные. Подводя итоги, вы видете что у нас получился диапазон, который аналогичен первоначальным данным, только в перевернутом виде и особенно важен тот факт, что данные в новом диапазоне связаны с исходником и если мы изменим исходные данные, то данные автоматически изменятся в диапазоне с транспонированными данными. Таким же способом диапазон можно развернуть и в другую сторону.

Транспонировать в Excel с помощью пункта «Меню» — «Специальная вставка» — «Транспонировать». Для использование этого метода вам необходимо скопировать данные которые мы будем транспонировать в Excel, с помощью кнопки меню «Копировать» или же сочетанием на клавиатуре ctrl+c.

Следующий шаг, это установить указатель на то место где вы хотите вставить транспонированные данные. Тут есть 2 варианта, это какой вам будет удобней, либо на панеле меню «Вставить» — «Транспонировать», либо то же самое но через контекстное меню при нажатии контекстного меню.

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

Скачать пример можно здесь.

Решение матриц в Excel

С матрицами в Excel выполняются такие операции, как: транспонирование, сложение, умножение на число / матрицу; нахождение обратной матрицы и ее определителя.

Транспонирование

Транспонировать матрицу – поменять строки и столбцы местами.

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

• 1 способ. Выделить исходную матрицу. Нажать «копировать». Выделить пустой диапазон. «Развернуть» клавишу «Вставить». Открыть меню «Специальной вставки». Отметить операцию «Транспонировать». Закрыть диалоговое окно нажатием кнопки ОК.
• 2 способ. Выделить ячейку в левом верхнем углу пустого диапазона. Вызвать «Мастер функций». Функция ТРАНСП. Аргумент – диапазон с исходной матрицей.

Нажимаем ОК. Пока функция выдает ошибку. Выделяем весь диапазон, куда нужно транспонировать матрицу. Нажимаем кнопку F2 (переходим в режим редактирования формулы). Нажимаем сочетание клавиш Ctrl + Shift + Enter.

Преимущество второго способа: при внесении изменений в исходную матрицу автоматически меняется транспонированная матрица.

Сложение

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

В первой ячейке результирующей матрицы нужно ввести формулу вида: = первый элемент первой матрицы + первый элемент второй: (=B2+H2). Нажать Enter и растянуть формулу на весь диапазон.

Умножение матриц в Excel

Чтобы умножить матрицу на число, нужно каждый ее элемент умножить на это число. Формула в Excel: =A1*$E$3 (ссылка на ячейку с числом должна быть абсолютной).

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

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

Для удобства выделяем диапазон, куда будут помещены результаты умножения. Делаем активной первую ячейку результирующего поля. Вводим формулу: =МУМНОЖ(A9:C13;E9:H11). Вводим как формулу массива.

Обратная матрица в Excel

Ее имеет смысл находить, если мы имеем дело с квадратной матрицей (количество строк и столбцов одинаковое).

Размерность обратной матрицы соответствует размеру исходной. Функция Excel – МОБР.

Выделяем первую ячейку пока пустого диапазона для обратной матрицы. Вводим формулу «=МОБР(A1:D4)» как функцию массива. Единственный аргумент – диапазон с исходной матрицей. Мы получили обратную матрицу в Excel:

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

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

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

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

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

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

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