Как в 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​ левую ячейку исходной​

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

Читать еще:  Вставка строк в excel со сдвигом вниз

​ формулы будут тормозить.​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 столбец в строку?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читать еще:  Как в эксель показать скрытые строки

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

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

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

Ранее я описал, как транспонировать столбцы в строки с помощью функции ДВССЫЛ.

Напомню, что, если нужно «переделать» таблицу, ориентированную по строкам, в таблицу, ориентированную по столбцам, можно скопировать строки в буфер, и вклеить на тот же или новый лист с помощью специальной вставки (рис. 1).

Рис. 1. Транспонирование с помощью специальной вставки.

Скачать заметку в формате Word, примеры в формате Excel

Это самый простой и удобный способ… если только не требуется сохранить связь новых данных со старыми (рис. 2).

Рис. 2. Результат транспонирования. Обратите внимание, что в строке формул отражается значение ячейки D1 – 1 (единица), константа.

Если же нужно сохранить связь новой области со старой, то можно использовать функцию ДВССЫЛ, но… гораздо проще и изящнее воспользоваться функцией =ТРАНСП(массив). Если вы ранее не сталкивались с функциями массива, рекомендую для начала посмотреть здесь. Что нужно сделать?

  1. В любой ячейке набрать =ТРАНСП(A1:B10); область A1:B10 можно выделить курсором, а не набирать с клавиатуры;
  2. Вырезать это значение в буфер обмена;
  3. Выделить область того же размера, что и исходная, то есть 2х10 ячеек, но ориентированную иначе: в исходной области 2 столбца и 10 строк, а в новой области 2 строки и 10 столбцов;
  4. Вставить содержимое буфера в строку формул (рис. 3);
  5. Одновременно нажать Ctrl + Shift + Enter, чтобы ввести формулу на лист Excel.

Рис. 3. Использование функции ТРАНСП

Получили новую область, связанную с исходной (рис. 4). Обратите внимание, что в ячейках D4:M5 одинаковые формулы массива <=ТРАНСП(A1:B10)>. Попробуйте изменить какое-либо значение в области A1:B10. Соответствующее изменение произойдет и в области D4:M5.

Рис. 4. Транспонирование с помощью функции массива

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

Успехов вам в овладении формулами массива! 🙂

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

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

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

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

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

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

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

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

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

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

Три способа как транспонировать таблицу в 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. Можно убрать галочку у ЦЕНА ЗА ШТ и поставить галочку рядом с ОБЩАЯ СТОИМОСТЬ. И тогда получим сводную таблицу по стоимости товаров, а также опять общий итог.

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

Похожие статьи

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