Как транспонировать столбец в строку
Как преобразовать в excel столбец в строку?
17.10.2013 Григорий Цапко Полезные советы
Когда возникает вопрос преобразования в excel столбца в строку, то имеется ввиду перемещение данных находящихся в столбце в строку. Или так называемое транспонирование. Весьма распространенная ситуация при перекладке данных из одного формата в другой.
Для того чтобы преобразовать в excel столбец в строку можно воспользоваться двумя способами:
Первый способ – использование специальной вставки.
Выделяем данные в столбце и копируем их. Затем правой кнопкой мыши вызываем контекстное меню, и выбираем пункт Специальная вставка. В появившемся диалоговом окне ставим галочку «транспонировать» и нажимаем ОК.
Данные из столбца в excel будут преобразованы (транспонированы) в строку.
Второй способ – использование функции ТРАНСП.
Функция ТРАНСП преобразует вертикальный диапазон ячеек (столбец) в горизонтальный (строку).
Функция ТРАНСП имеет очень простой синтаксис:
=ТРАНСП(массив), где массив – преобразуемый из столбца в строку массив данных.
Чтобы транспонировать данные в excel из столбца в строку, при помощи этой функции необходимо:
1) Выделить горизонтальный диапазон: внимание! – с количеством ячеек соответствующий количеству ячеек в вертикальном диапазоне.
2) В первую ячейку горизонтального диапазона ввести формулу =ТРАНСП(массив), где массив – это вертикальный диапазон ячеек.
3) Нажать комбинацию клавиш Ctrl + Shift + Enter. Формула будет введена как формула массива в фигурных скобках
Какой способ выбрать при преобразовании в excel столбца в строку?
В первом случае, данные будут вставлены значениями, без связи с источником. Во втором случае, данные из столбца будут преобразованы в строку с сохранением связи с источником, и при изменении данных в столбце, будут меняться данные в строке.
Как в excel преобразовать строки в столбцы?
Как вы правильно догадались, чтобы преобразовать данные в excel из строки в столбец нужно проделать аналогичные операции, что и при преобразовании столбца в строку. Можно воспользоваться как специальной вставкой, так и функцией ТРАНСП.
6 способов быстро транспонировать таблицу
В этой статье показано, как столбец можно превратить в строку в Excel с помощью функции ТРАНСП, специальной вставки, кода VBA или же специального инструмента. Иначе говоря, мы научимся транспонировать таблицу.
В этой статье вы найдете несколько способов поменять местами строки и столбцы (или столбцы и строки), как бы вы это ни называли, это одно и то же 🙂 Эти решения работают во всех версиях Excel 2016, 2013, 2010, 2007 и ниже, представляют множество возможных сценариев и объясняют наиболее типичные ошибки. Вот о чём мы поговорим:
О вкусах не спорят. То же самое и с рабочими привычками. Некоторые пользователи Excel предпочитают организовывать данные вертикально в столбцы, тогда как другие выбирают горизонтальное расположение в строках. И при совместной работе над таблицами вкусы зачастую не совпадают …
Довольно часто вы строите сложную таблицу только для того, чтобы в конце концов понять, что имеет смысл повернуть ее для более удобного анализа показателей или же для представления их в виде графиков.
Транспонирование – это математическая операция. Это преобразование матрицы, в результате чего столбцы становятся строками, а строки превращаются в столбцы. Говоря проще, нужно все значения в строке (горизонталь) перекинуть в столбец (вертикаль).
Способ 1. Использовать функцию ТРАНСП.
В случае, когда вам нужно быстро изменить ориентацию вашей таблицы, можно использовать функцию ТРАНСП (TRANSPOSE в англоязычной версии).
Назначение функции ТРАНСП в Excel – превратить строки в столбцы, т. е. поменять ориентацию с горизонтальной на вертикальную или наоборот.
Функция транспонировать принимает только один аргумент:
= ТРАНСП(массив)
Где массив – это диапазон для транспонирования. Данные преобразуются следующим образом: первая строка исходного диапазона становится первым столбцом нового, вторая строка становится вторым столбцом и так далее.
Синтаксис ТРАНСП не оставляет места для ошибок при создании формулы. Более сложная задача – правильно записать её на рабочий лист. Если у вас нет большого опыта работы с формулами Excel в целом и формулами массивов в частности, внимательно следуйте приведенным ниже инструкциям.
1. Подсчитайте количество столбцов и строк в исходной таблице.
Эти числа понадобятся вам на следующем шаге.
В этом примере мы собираемся транспонировать небольшую табличку, которая показывает объем экспорта свежих фруктов по регионам:
2. Выберите такое же количество ячеек, но измените их ориентацию.
Ваша новая таблица будет содержать такое же количество значений, но будет повернута с горизонтальной ориентации на вертикальную. Итак, вы выбираете диапазон пустых ячеек, который занимает то же количество строк, что и исходная таблица имела столбцов, и такое же количество колонок, как исходная таблица – строк.
В нашем случае мы выбираем диапазон из 5 столбцов и 4 строк:
3. Введите формулу ТРАНСП.
Выделив этот пустой диапазон, используйте функцию транспонирования:
Вот подробные шаги:
Сначала вы вводите знак равенства, имя функции и открывающую скобку: = ТРАНСП(
Затем выберите исходный диапазон с помощью мыши или введите его вручную:
Наконец, введите закрывающую скобку, но не нажимайте клавишу Enter! На этом этапе ваша формула должна выглядеть примерно так:
4. Запишите формулу.
После этого Excel окружит вашу формулу транспонирования <фигурными скобками>, которые видны в строке формул и являются визуальным индикатором формулы массива. Ни в коем случае не набирайте их вручную, это не сработает.
На скриншоте ниже показано, что наша исходная матрица значений была успешно транспонирована и 4 столбца были преобразованы в 4 строки. А столбцов теперь стало пять:
Преимущества функции ТРАНСП:
Основное преимущество использования этой функции заключается в том, что повернутая таблица сохраняет связь с исходной. Всякий раз, когда вы меняете исходные данные, транспонированная будет меняться соответствующим образом.
Слабые стороны функции ТРАНСП:
Итак, какой бы хорошей и простой в использовании не была бы функция ТРАНСП, ей определенно не хватает гибкости. Поэтому она может быть не самым оптимальным вариантом во многих ситуациях.
Как транспонировать таблицу без замены нулей на пробелы.
Выше мы уже упоминали, что если одна или несколько ячеек в исходной таблице пусты, то они будут иметь нулевые значения после преобразования, как показано на скриншоте ниже:
То есть, если при транспонировании строки в столбец программа обнаруживает пустую ячейку, то вместо нее сразу же вставляется ноль. Это особенность Экселя: стоит с пустой ячейкой произвести какую-то математическую операцию, и там тут же появляется ноль. А транспонирование в Excel – это математическая операция с матрицей значений.
С одной стороны, это выглядит несущественно, поскольку не меняет математические результаты в итогах. Но для вас эти нули могут выглядеть не совсем эстетично, так как меняют внешний вид данных. Ну а если они появляются посреди текстовых значений – это совсем некрасиво.
Если вы хотите оставить в новой таблице по-прежнему пустые ячейки, а не нули, то вложите функцию ЕСЛИ в формулу ТРАНСП, чтобы предварительно проверить, является ли ячейка пустой. Если она пуста, функция ЕСЛИ вернет пустую строку («»), в противном случае предоставит значение для транспонирования:
Как видите, мы транспонируем не все ячейки, а только непустые.
Советы по использованию ТРАНСП
Как вы только что видели, функция ТРАНСП имеет ряд особенностей, которые могут сбить с толку неопытных пользователей. Приведенные ниже советы помогут вам избежать некоторых типичных ошибок.
1. Как редактировать формулу?
2. Как удалить формулу?
Чтобы удалить формулу транспонирования с листа, выберите весь диапазон, на который имеется ссылка в формуле, и нажмите клавишу Del.
3. Как заменить формулу на значения?
Когда вы переворачиваете таблицу с помощью функции ТРАНСП, исходная и новая таблицы связываются. Это означает, что всякий раз, когда вы изменяете какое-либо значение в исходной таблице, соответствующее значение в новой изменяется автоматически.
Способ 2. Как транспонировать с помощью формул
Этот способ отчасти похож на предыдущий, но позволяет свободно редактировать значения во второй таблице и вносить в нее любые правки при необходимости.
Для создания ссылок на строки и столбцы нам понадобятся четыре функции из категории Ссылки и массивы:
Теперь соединяем эти функции, чтобы получить нужную нам ссылку на исходную таблицу. Предположим, координаты её верхнего левого угла – A1.
Вводим в любую свободную ячейку (скажем, в A8) вот такую формулу:
в английской версии Excel это будет =INDIRECT(ADDRESS(COLUMN(A1),ROW(A1)))
Чтобы столбцы превратились в строки, копируем (протягиваем) формулу вправо и вниз, перетащив маленький черный крестик в нижнем правом углу выбранных ячеек. В итоге должно получиться примерно следующее:
Вот и все! В вашей вновь созданной таблице все столбцы поменяны местами со строками.
Если ваши данные начинаются с некоторой строки, отличной от 1, и столбца, отличного от A, вам придется использовать немного более сложную формулу:
Однако транспонированные данные выглядят очень просто и тускло по сравнению с исходными. Всё форматирование потеряно.
Но не расстраивайтесь, эту проблему легко решить. Чтобы восстановить исходное форматирование, выполните следующие действия:
Преимущества: эта формула обеспечивает более гибкий способ замены строк и столбцов в Excel. Это позволяет вносить любые изменения в итоговую таблицу, поскольку вы используете обычную формулу, а не формулу массива.
Способ 3. Специальная вставка
Предположим, у вас есть набор данных, аналогичный тому, что вы видите в верхней части рисунка ниже. Названия стран организованы в столбцы. Но их список слишком длинный, поэтому нам лучше перевернуть столбцы в строки, чтобы всё поместилось на экране:
Для этого выполните следующие действия:
Примечание. Если ваши исходные данные содержат формулы, убедитесь, что вы правильно используете относительные и абсолютные ссылки в зависимости от того, должны ли они быть изменены или оставаться зафиксированными.
Как вы только что видели, функция «Специальная вставка» позволяет выполнять преобразование из строки в столбец (или из столбца в строку) буквально за несколько секунд. Этот метод также копирует форматирование ваших исходных данных, чем добавляет еще один плюс в свою пользу.
Однако у этого подхода есть два недостатка, которые не позволяют называть его идеальным решением чтобы перевернуть данные в Excel:
Как транспонировать таблицу и связать ее с исходными данными
Давайте посмотрим, как вы можете превращать строки в столбцы, используя уже знакомую нам технику специальной вставки, но при этом связывая полученную таблицу с исходным набором данных. То есть, всякий раз, когда вы меняете данные в исходной, перевернутая будет отражать изменения и обновляться соответствующим образом. Чуть выше мы назвали отсутствие такой связи недостатком. А сейчас посмотрим, как этот недостаток можно обойти.
Эта операция превратит ваши данные во что-то немного пугающее, как вы видите на скриншоте ниже. Но не паникуйте – еще 2 шага, и вы достигнете желаемого результата.
Это длинноватое, но элегантное решение, не правда ли? Единственным недостатком этого подхода является то, что исходное форматирование теряется в процессе, и вам нужно будет восстановить его вручную (в дальнейшем в этом руководстве я покажу вам быстрый способ сделать это).
Способ 4. Перенести данные в Excel с помощью макроса VBA
Чтобы автоматизировать преобразование строк в столбцы в Excel, вы можете использовать следующий макрос:
Примечание. Транспонирование с помощью VBA имеет ограничение в 65536 элементов. Если ваш массив превышает этот предел, дополнительные данные будут автоматически отброшены.
Как использовать макрос для преобразования строки в столбец
Вставив макрос в книгу, выполните следующие шаги, чтобы повернуть таблицу:
Способ 5. Сводная таблица.
Сначала необходимо создать сводную таблицу (здесь есть подробная инструкция). Выделяем исходные данные:
Создаем её на новом листе.
В свойствах сводной таблицы отключаем общие и промежуточные итоги по столбцам и строкам.
Перетаскиваем поля в макете таблицы, и получаем результат – таблица повёрнута набок.
Преобразуйте столбцы в строки с помощью инструмента транспонирования
Если вам нужно выполнять преобразования строки в столбец достаточно часто, то возможно, вы действительно ищете более быстрый и простой способ. К счастью, у меня есть такой способ в моем Excel, как и у других пользователей надстройки Ultimate Suite 🙂
Позвольте мне продемонстрировать вам, как перенести строки в столбцы в Excel буквально за пару кликов при помощи инструмента транспонирования таблицы:
Если вы хотите Создать ссылки на исходные данные, чтобы повернутая таблица обновлялась автоматически при каждом изменении исходной таблицы, выберите соответствующий вариант:
Готово! Таблица транспонирована:
Если вам интересно попробовать этот и более 60 других профессиональных инструментов для Excel, я приглашаю вас загрузить пробную версию Ultimate Suite.
Итак, вы можете выбрать для себя любой из этих шести методов транспонирования в Excel.
Благодарю вас за чтение и надеюсь увидеть вас в нашем блоге!
Как преобразовать столбец в строку в Excel – простой способ
Иногда мы аккуратно и старательно вводим данные в столбцах Excel и только к концу понимаем, что гораздо удобнее расположить их горизонтально, т.е. не в столбцах, а в строках. Да, ради этого стоило потрудиться, чтобы понять, что с самого начала надо было делать всё наоборот! Не спешите удалять проделанную работу и начинать всё заново. В Excel есть простой способ транспонировать всю таблицу целиком и сохранить структуру.
В этой статье мы приводим примеры и снимки экрана из версии Excel 2010 х64. В других версиях, начиная с Excel 2003, этот приём работает аналогично, но могут быть отличия в интерфейсе, структуре меню и диалоговых окнах.
Преобразовываем столбец в строку
Итак, у нас имеется вот такая таблица Excel:
Данные расположены в двух столбцах, но мы хотим сделать таблицу горизонтальной и расположить те же данные, но в двух строках. Выделите ячейки, которые необходимо преобразовать. Затем щелкните правой кнопкой мыши и выберите пункт Copy (Копировать) или просто нажмите Ctrl+C.
Теперь щелкните правой кнопкой мыши в ячейке, где требуется поместить преобразованные данные и выберите из контекстного меню пункт Paste Special (Специальная вставка):
В открывшемся диалоговом окне Paste special (Специальная вставка) выберите пункт Transpose (Транспонировать) и нажмите ОК.
Теперь Ваши столбцы преобразовались в строки!
Точно таким же образом Вы можете преобразовать строки в столбцы. Вот небольшой пример:
После копирования и специальной вставки с включенным параметром Transpose (Транспонировать) строки преобразовались в столбцы.
Это отличный способ расположить информацию на листе Excel так, как Вам нужно.
Транспонирование в Excel – преобразуем строки в столбцы и наоборот
Транспонирование данных в Excel – это задача, знакомая многим пользователям. Очень часто после создания сложной таблицы, становится очевидным, что эффективнее было бы развернуть её для более удобного анализа или построения диаграммы по имеющимся данным.
В этой статье Вы найдёте несколько способов преобразовать строки в столбцы (или столбцы в строки) в Excel. Эти решения работают во всех версиях Excel (2013, 2010, 2007 и более ранних), охватывают множество возможных сценариев, объясняют наиболее типичные ошибки и показывают хорошие примеры использования в Excel формул для транспонирования.
Преобразуем строки в столбцы в Excel при помощи Специальной вставки
Предположим, Вы создали таблицу на листе Excel, похожую на ту, что показана в верхней части рисунка, расположенного ниже. Названия стран распределены по столбцам, но список стран может оказаться очень длинным, поэтому нам будет гораздо удобнее преобразовать столбцы в строки, чтобы таблица уместилась на экране.
Замечание: Важно выбрать ячейку, которая находится за пределами диапазона исходных данных, чтобы скопированная область и область вставки не пересеклись. Например, если исходные данные состоят из 4 столбцов и 10 строк, то транспонированная таблица будет состоять из 10 столбцов и 4 строк.
Как Вы только что увидели, опция Transpose (Транспонировать) в Excel позволяет преобразовать строки в столбцы (или столбцы в строки) буквально за несколько секунд. Уверен, что этот способ очень полезен, когда требуется транспонировать таблицу один раз. Пользуясь этим методом, Вы сохраняете исходные данные, и это ещё один аргумент в его пользу.
Однако, вышеописанный метод имеет два существенных недостатка, которые не позволяют считать его идеальным решением для транспонирования данных в Excel.
Как транспонировать таблицу, ссылающуюся на исходные данные
Давайте посмотрим, как можно преобразовать строки в столбцы в Excel при помощи уже знакомой нам техники Копировать > Вставить, так, чтобы транспонированные данные ссылались на исходные данные. Главное преимущество такого подхода в том, что при изменении данных в исходной таблице, транспонированная таблица обновится и отразит эти изменения.
Быстрое, спокойное и элегантное решение, не так ли? Единственный недостаток этого подхода состоит в том, что в процессе выполнения форматирование исходной таблицы будет потеряно, и Вам придётся восстанавливать его вручную (далее в этом уроке я покажу быстрый способ сделать это).
Транспонируем данные в Excel при помощи формул
Если Вы не ищите лёгких путей, то можете использовать формулы для преобразования столбцов в строки и наоборот.
Преобразуем в Excel строки в столбцы при помощи функции TRANSPOSE (ТРАНСП)
Как можно понять из её названия, функция TRANSPOSE (ТРАНСП) специально разработана для транспонирования данных в Excel. Если у Вас нет большого опыта работы с формулами в Excel, и в частности с функциями массива, то, пожалуйста, внимательно следите за тем, чтобы выполнять следующие шаги в точности как написано.
В этом примере мы будем преобразовывать таблицу, в которой перечислены штаты США с данными о населении:
Во-первых, необходимо определить точное количество строк и столбцов в таблице. В нашем примере это 7 столбцов и 6 строк, включая заголовки. Запомним эти цифры и приступим к преобразованию строк в столбцы.
Замечание: Всегда используйте абсолютные ссылки для работы с функцией TRANSPOSE (ТРАНСП).
Замечание: Обязательно нажмите Ctrl+Shift+Enter, поскольку это формула массива. Если для ввода формулы просто нажать Enter, то она работать не будет.
Готово! Наши данные транспонированы, 7 столбцов превратились в 6 столбцов, что и требовалось получить.
Преимущества функции TRANSPOSE (ТРАНСП)
Главное преимущество функции TRANSPOSE (ТРАНСП): преобразованная таблица сохраняет связь с источником данных, т.е. какие бы изменения в исходных данных Вы ни вносили, транспонированная таблица также будет изменяться.
Недостатки функции TRANSPOSE (ТРАНСП)
И в завершение, какой бы хорошей и простой ни была функция TRANSPOSE (ТРАНСП), ей не хватает гибкости, и поэтому она становится не лучшим решением во многих ситуациях.
Преобразовываем строку в столбец или столбец в строку при помощи функции INDIRECT (ДВССЫЛ)
Этот метод очень похож на предыдущий. Его главное преимущество перед функцией TRANSPOSE (ТРАНСП) состоит в том, что он оставляет возможность изменять данные в транспонированной таблице, при этом сохраняя связь с исходной таблицей.
В этом примере мы будем использовать небольшую таблицу, чтобы Вы могли сосредоточить свое внимание на процессе транспонирования, а не на данных в таблице. Мы будем использовать немного замысловатую комбинацию функций INDIRECT (ДВССЫЛ) и ADDRESS (АДРЕС).
Предположим, у Вас есть данные в 4 столбцах (A – D) и в 5 строках (1 – 5):
Если данные начинаются не в строке с номером 1 и не в столбце A, то потребуется немного более сложная формула:
Вот и всё! В Вашей новой таблице все столбцы преобразованы в строки. Однако, транспонированные ячейки выглядят очень плоско и скучно по сравнению с исходными данными.
Но не расстраивайтесь, это вовсе не проблема. Вот как можно легко восстановить исходное форматирование:
Подводя итог, метод с функцией INDIRECT (ДВССЫЛ) кажется более сложным решением задачи по преобразованию строк в столбцы, чем с функцией TRANSPOSE (ТРАНСП).
Преимущества: Вы можете спокойно редактировать любую ячейку (ячейки) в транспонированной таблице, при этом она будет оставаться связанной с таблицей-источником и будет автоматически обновляться каждый раз, когда Вы будете вносить изменения в исходные данные.
Недостатки: На самом деле, я вижу только один – форматирование исходных данных не сохраняется. Впрочем, Вы можете легко восстановить его, как это показано выше.
Теперь Вы понимаете, как работает функция INDIRECT (ДВССЫЛ), и, возможно, захотите более подробно разобраться, что на самом деле делает эта формула и почему мы использовали комбинацию с функцией ADDRESS (АДРЕС). Если технические детали Вас не интересуют, следующий раздел Вы можете пропустить.
Функции INDIRECT (ДВССЫЛ) и ADDRESS (АДРЕС) – синтаксис и логика
Функция INDIRECT (ДВССЫЛ) служит для создания непрямой ссылки на ячейку. Например, если Вы хотите поместить значение ячейки B1 в ячейку A8, то записываете в ячейку A8 следующую формулу:
Но реальная сила функции INDIRECT (ДВССЫЛ) в том, что она может превратить любую текстовую строку в ссылку, в том числе строку, которую Вы создаёте при помощи других функций и значений других ячеек. Именно это мы и собираемся сейчас сделать. Если Вы поймёте это, то всё остальное поймёте с лёгкостью 🙂
Думаю, Вы помните, что в формуле мы использовали ещё 3 функции – ADDRESS (АДРЕС), COLUMN (СТОЛБЕЦ) и ROW (СТРОКА).
Функция ADDRESS (АДРЕС) используется для того, чтобы получить адрес ячейки из номеров строки и столбца, заданных Вами. Запомните: сначала строка, потом столбец. Например, АДРЕС(8;1) возвратит $A$8.
Функция COLUMN (СТОЛБЕЦ) возвращает номер столбца из заданной ссылки на ячейку. Например, СТОЛБЕЦ(A3) возвратит значение 1, поскольку столбец A – это первый столбец. Как не трудно догадаться, функция ROW (СТРОКА) работает точно так же, только для строк.
А теперь позвольте напомнить всю формулу, которую мы использовали для преобразования строк в столбцы на листе Excel:
Как видите, в функции ADDRESS (АДРЕС) мы задаём сначала столбец, а затем строку, и в этом кроется весь фокус! Помните, что функция ADDRESS (АДРЕС) понимает первое число как номер строки, а второе число как номер столбца. Другими словами, функция ADDRESS (АДРЕС) берёт номер столбца, возвращённый функцией COLUMN (СТОЛБЕЦ), и превращает его в номер строки, затем берёт номер строки и превращает его в номер столбца, то есть меняет местами строки и столбцы.
Теперь, когда Вы знаете, что делает каждая функция, давайте сведём воедино логику всей формулы:
Совсем не страшно, правда?
Транспонируем данные при помощи макроса VBA
Не составит труда написать скрипт, который будет транспонировать строки в столбцы, при условии, что Вы достаточно хорошо владеете VBA. Если нет, то можете найти множество готовых макросов в интернете. Но если Вы работаете с большими таблицами, помните, что метод Transpose в VBA ограничен 65536 элементами. Если Ваш массив выйдет за рамки этого ограничения, то все лишние данные будут молча потеряны.