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

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

Транспонирование данных из строк в столбцы (или наоборот) в Excel для Mac

​Смотрите также​Как можно решить​ а не как​1aaa​ работать формулы, если​​Заранее спасибо!​​ красными горизонтальными линиями​ в ячейке​ в столбце​ одного наименования не​

​ столько раз, сколько​ исходной таблицы на​). Число выделенных строк​A8​ углами, стоит создать​Повернув данные, можно удалить​ столбцов, а затем​При наличии листа с​ такую задачу? Прикрепляю​

​ сейчас:​1bbb​ значение столбца А​PS. Поиск пользовал,​ с помощью Условного​F6​

​ может быть больше​ у него имеется​ листе: когда левый​ должно совпадать с​);​​ сводную таблицу. С​​ исходные.​​ на вкладке​ ​ данными в столбцах,​

​ во вложение файл​​1aaa​1ccc​ будет повторяться через​​ гугл уже два​​ форматирования.​

​формулу для транспонирования​с помощью формулы​ 10 (определяет количество​ различных размеров (столбец​​ верхний угол таблицы​​ количеством столбцов в​в меню Вставить (Главная​​ ее помощью вы​​Если данные содержат формулы,​​Главная​​ которые нужно повернуть,​

​ с текущим набором​1bbb​1ddd​ несколько строк от​ дня мучаю, но​Транспонированная таблица обновится автоматически.​

Советы по транспонированию данных

​ столбцов с размерами​ №2). Иногда требуется​ расположен на «диагонали»​ исходной таблице, а​ / Буфер обмена)​ сможете быстро свести​ Excel автоматически обновляет​выберите команду​ чтобы упорядочить строки,​ данных и желаемым​

​1ccc​2eee​​ первоначального массива. (Массив​​ ничего подходящего не​un_kind​В​=ЕСЛИОШИБКА(ИНДЕКС(Наименования;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$5:D5;Наименования);0));»»)​ в транспонированной таблице);​​ «перевернуть» не всю​​ листа, т.е. в​​ число выделенных столбцов​​ выбираем Транспонировать;​ данные, перетащив поля​ их в соответствии​

​Копировать​ можно использовать функцию​ результатом (на следующем​1ddd​2fff​ одинаковых значений в​ нашел.​: Здравствуйте!​в горизонтальный массив.​Формулу введите в ячейку​ таблица отсортирована сначала​ таблицу, а только​ ячейке, у которой​

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

​ – с количеством​нажимаем ОК.​ из области строк​

​ с новым расположением.​или нажмите CTRL+C.​Транспонирование​

​ листе)​2eee​5iii​ столбце А прерывается.)​_Boroda_​

Специальная ставка — транспонировать

• ​Есть таблица в​​=ЕСЛИ($E6>=F$4;СМЕЩ($B$6;ПОИСКПОЗ($D6;Наименования;0)+F$4-2;;1);»»)​​D6​
• ​ по наименованию, затем​ один столбец №2.​​ номер строки совпадает​​ строк;​
• ​Наиболее простой способ транспонировать​ в область столбцов​​ Проверьте, используются ли​​Примечание:​
• ​. Она позволяет повернуть​vovo​2fff​
• ​6ggg​

Транспонирование функцией СМЕЩ()

​ и если значение​: Так нужно?​ которой в первом​Формулу протяните на 9​

​. Не забудьте после ввода​

​ по размеру (чтобы​​ Т.е. теперь у​​ с номером столбца​в Строке формул ввести​ таблицу с помощью​

​ (и наоборот) в​​ в этих формулах​​ Обязательно скопируйте данные. Это​ данные из столбцов​: Как только сформулировал​5iii​6kkk​ в столбце С​

​Первый столбец формула​ столбце есть повторяющиеся​

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

• ​ ячеек вправо и​ формулы массива нажать​ размеры в транспонированной​​ нас будет столбец​​ (​ =ТРАНСП(A1:E5) – т.е.​ формул — использовать​ списке полей сводной​ абсолютные ссылки. Если​ не получится сделать​ в строки и​
• ​ вопрос, понял как​6ggg​6lll​ будет встречаться ниже,​
• ​ массива, вводится одновременным​​ значения, а во​​ на 2 строки​​CTRL+SHIFT+ENTER. ​​ таблице шли по​

Транспонирование функцией ДВССЫЛ()

• ​ наименований обуви (уже​А1, B2, C3​​ дать ссылку на​​ функцию СМЕЩ()​ таблицы.​​ нет, перед поворотом​​ с помощью команды​ наоборот.​
• ​ его решить :-)​6kkk​6mmm​ но уже с​

​ нажатием Контрл Шифт​ втором прочие данные.​ ниже. Обратите внимание на​Протяните формулу вниз с​ порядку).​ без повторов), а​и т.п.,​ исходную таблицу;​=СМЕЩ($J$2;СТОЛБЕЦ()-СТОЛБЕЦ($H$19);СТРОКА()-СТРОКА($H$19))​Иногда требуется «перевернуть» таблицу,​ данных можно сделать​​Вырезать​​Например, если данные выглядят​​ Ведь можно после​​6lll​8zzz​​ другим «аргументом» в​​ Ентер​Как можно транспонировать​

​ использование смешанных ссылок в формуле.​ помощью Маркера заполнения​Примечание​ по горизонтали -​​В​​Вместо​
​Ячейка ​
​ т.е. поменять столбцы​
​ абсолютные ссылки относительными.​
​или клавиш CONTROL+X.​

Транспонирование столбца в MS EXCEL в таблице с повторами

​ так, как показано​ тупого копирования столбцов​6mmm​То есть первая​ столбце А.​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИОШИБКА(ИНДЕКС($A$2:$A$19;ПОИСКПОЗ(;СЧЁТЕСЛИ(F$1:F1;$A$2:$A$19);));»»)​ значения из второго​ При копировании формулы​ на 2 ячейки​: О сортировке таблицы​ список размеров. Транспонирование​– это второй​ENTER​J2 ​ и строки местами.​Если данные хранятся в​Выделите первую ячейку диапазона,​

​ ниже, где продажи​ и вставки один​8zzz​

​ цифра это идентификатор,​_Boroda_​ВторойКод200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИОШИБКА(ВПР(F2;A$2:B$19;2;);»»)​

​ столбца в одну​ вправо ссылка $E6​ ниже.​ средствами EXCEL читайте​ произведем формулами.​ столбец,​нажать​

​- ссылка на верхнюю​Пусть имеется таблица продаж​ таблице Excel, функция​ в который требуется​ по регионам указаны​ за другим взять​Руками это делать​ но при этом​: На 17000 такие​ТретийКод200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$19;ПОИСКПОЗ($F2;$A$2:$A$19;)-1+СТОЛБЕЦ(A2)/(СТОЛБЕЦ(A2)​ строку при условии​ не изменяется, т.к.​В столбце​ статью Автофильтр.​

​Пусть имеется исходная таблица​​С​CTRLSHIFTENTER​ левую ячейку исходной​

​ по отделам и​Транспонировать​ вставить данные, а​ в верхней части​ и отсортировать их​ слишком сложно, так​ не для всех​

​ формулы будут тормозить.​un_kind​ что соответствующее значение​ зафиксирован столбец (зато​Е​С помощью идей из​

​ наименований обуви с​

​– это третий​.​​ таблицы;​​ кварталам.​будет недоступна. В​

​ затем на вкладке​

​ листа, а продажи​​ по алфавиту -​​ как в таблице​ ИД есть свои​​ Предлагаю пару допстолбцов,​​:​ в первом столбце​ F$4 изменяется на​подсчитаем количество размеров​

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

​Ячейка ​​Необходимо поменять строки и​ этом случае вы​Главная​ по кварталам —​ порядок ИДшников сохранится,​

​ больше 20 000​ данные (в примере,​ сводную и по​_Boroda_​ одинаковое. => Одна​

​ G$4, затем на​ каждого наименования: =СЧЁТЕСЛИ(Наименования;D6)​​ значений сформируем в​​Требуется для каждого наименования​Если левый верхний угол​​ (​​H19 ​

​ столбцы местами. Транспонировать​

​ можете сначала преобразовать​щелкните стрелку рядом​ в левой части​ и все довольны​ строк, в каждой​ 3,4 и 7​ ней на другом​, спасибо что так​ строка — значение​ H$4 и т.д.),​Примечание​ новой транспорнированной таблице​ обуви вывести горизонтально​ таблицы расположен в​A7​- ссылка на верхнюю левую​ их можно несколькими​ таблицу в диапазон​

​ с командой​ листа.​ :-)​ из которых иногда​

​ — нет, вместо​ листе (не обязательно,​ быстро ответили. Вроде​ из первого столбца​

​ а при копировании​

«Транспонирование» значений из столбца в строку при условии (Формулы/Formulas)

​: Не забудьте, что Наименования​​ перечень уникальных наименований​
​ список размеров.​ другой ячейке, например​) ввести формулу =ДВССЫЛ(АДРЕС(СТОЛБЕЦ(A1);СТРОКА(A1))),​ ячейку транспонированной таблицы​ способами (см. Файл​
​ с помощью команды​Вставить​Вы можете повернуть столбцы​Rustem​ 1 ячейка, а​ них идут пустые​ можно и на​ так, но попробовал​ и все соответствующие​ вниз наоборот ссылка F$4 не​ в вышеприведенной формуле​
​ (у нас будет​Сделаем такое преобразование таблицы​ в​ где​ (на ту ячейку,​ примера).​Преобразовать в диапазон​и выберите пункт​
​ и строки, чтобы​
​: Попробуйте такую формулу.​ иногда 10, а​ строки ).​ текущем) уже таблицу​ увеличить таблицу до​

​ значения из второго.​​ изменяется, т.к. зафиксирована​
​ — это Имя​ их 2).​ с помощью формул,​J2​ ​А1​
​ в которую Вы​
​выделить всю таблицу (​

​на вкладке​​Транспонировать​​ отобразить кварталы в​​ Нули уберите фильтром.​ иногда и вообще​Суть проблемы в​vovo​ 17000 и ничего​Может сумбурно объяснил. ​ строка (зато $E6 изменяется​ созданного ранее Вами​Для этого создадим Динамический​ что позволит в​, формула немного усложняется:​
​– координата левого​ вводите первую формулу) См. файл​A1:E5​Таблица​.​ верхней части листа,​Rustem​ нет ячеек.​ том, что не​: Добрый день! У​ не получилось. Как​ Для пояснения прикрепляю​ на $E7, затем​ Динамического диапазона.​

​ диапазон Наименования, чтобы​​ дальнейшем при вводе​=ДВССЫЛ(​ верхнего угла таблицы.​ примера.​);​или воспользоваться функцией​Выберите место на листе,​ а регионы —​

Преобразовать строки в столбцы (НЕ транспонирование!) (Транспонирование не подходит из-за разной длины строк)

​: Попробуйте такую формулу.​​Можно было бы​ могу понять, как​ меня есть большая​ «размножить»? Вручную поправить​ Файл с примером​ на $E8 и т.д.)​В строке 4 (см.​

Транспонирование данных из строк в столбцы и наоборот

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

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

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

Примечание: Если данные хранятся в таблице Excel, функция Транспонирование будет недоступна. Можно сначала преобразовать таблицу в диапазон или воспользоваться функцией ТРАНСП, чтобы повернуть строки и столбцы.

Вот как это сделать:

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

Примечание: Убедитесь, что вы хотите скопировать данные для этого, так как при использовании команды Вырезать или CTRL + X не работает.

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

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

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

Советы по транспонированию данных

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

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

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

Вот как можно транспонировать содержимое ячейки:

Копирование диапазона ячеек.

Выделите пустые ячейки, в которые нужно вставить данные.

На вкладке Главная щелкните значок Вставить и выберите команду Вставить транспонировать.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

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

Как преобразовать в excel столбец в строку?

17.10.2013 Григорий Цапко Полезные советы

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

Для того чтобы преобразовать в excel столбец в строку можно воспользоваться двумя способами:

Первый способ – использование специальной вставки.

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

Данные из столбца в excel будут преобразованы (транспонированы) в строку.

Второй способ – использование функции ТРАНСП.

Функция ТРАНСП преобразует вертикальный диапазон ячеек (столбец) в горизонтальный (строку).

Функция ТРАНСП имеет очень простой синтаксис:

=ТРАНСП(массив), где массив – преобразуемый из столбца в строку массив данных.

Чтобы транспонировать данные в excel из столбца в строку, при помощи этой функции необходимо:

1) Выделить горизонтальный диапазон: внимание! – с количеством ячеек соответствующий количеству ячеек в вертикальном диапазоне.

2) В первую ячейку горизонтального диапазона ввести формулу =ТРАНСП(массив), где массив – это вертикальный диапазон ячеек.

3) Нажать комбинацию клавиш Ctrl + Shift + Enter. Формула будет введена как формула массива в фигурных скобках

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

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

Как в excel преобразовать строки в столбцы?

Как вы правильно догадались, чтобы преобразовать данные в excel из строки в столбец нужно проделать аналогичные операции, что и при преобразовании столбца в строку. Можно воспользоваться как специальной вставкой, так и функцией ТРАНСП.

Департамент ИТ

Ранее мы рассказывали о том, как вручную преобразовать столбец в строку в Excel. В случае же, если в каком-то документе необходимо проводить транспонирование столбца в строку (или наоборот) на регулярной основе, то для этого в Excel существует функция транспонирования. Чтобы воспользоваться ей, проделайте следующее: в ячейку, в начало которой вы хотите транспонировать столбец или строку, введите функцию «ТРАНСП» (Рис. 1).

В качестве массива данных укажите набор ячеек или всю строку (или столбец), которые Вы хотите транспонировать (Рис. 2).

После выполнения этой операции мы получаем следующее (Рис. 3):

Далее, чтобы произошло корректное преобразование нашего массива данных, необходимо нашу формулу перевести в формулу массива. Для этого в нашем примере выделяем ячейки с C3 до L3, нажимаем кнопку F2, а затем комбинацию клавиш Ctrl+Shift+Enter для преобразование формулы в формулу массивов. При этом сама формула будет взята в фигурные скобки, что означает, что это формула массива (Рис. 4).

В дальнейшем при изменений значений в столбце A1-A10 автоматически будут изменяться значения в строке C3-L3.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1. Создадим сводную таблицу. Для этого выделим исходную таблицу и откроем пункт ВСТАВКА – СВОДНАЯ ТАБЛИЦА.
2. Местом, где будет создана сводная таблица, выбираем новый лист.
3. В получившемся макете сводных таблиц можем выбрать необходимые пункты и перенести их в нужные поля. Перенесем «продукт» в НАЗВАНИЯ СТОЛБЦОВ, а «цена за шт» в ЗНАЧЕНИЯ.
4. Получили сводную таблицу по нужному нам полю. Также команда автоматически подсчитала общий итог.
5. Можно убрать галочку у ЦЕНА ЗА ШТ и поставить галочку рядом с ОБЩАЯ СТОИМОСТЬ. И тогда получим сводную таблицу по стоимости товаров, а также опять общий итог.

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