Excel из строки в столбец excel

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

Необходимость преобразовать строку в столбец в Excel возникает довольно часто. Допустим, мы получаем выгрузку данных или уже готовую таблицу (ориентированную горизонтально) и для анализа этих данных нам требуется развернуть строку в столбец (вертикально ориентировать таблицу). Операцию преобразования строки в столбец можно проводить как для одного столбца (строки) так и для группы столбцов (строк), главное чтобы размер разворачиваемых данных не превышал количества доступных строк (столбцов) в Excel.

Чтобы решить нашу проблему и преобразовать строку в столбец нам потребуется функция транспонирования (операция поможет развернуть наши данные как из строки в столбец, так и из столбца в строку).

Рассмотрим пример: у нас есть две строки, которые по каким-то причинам мы хотим развернуть в Excel:

Данные для преобразования

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

Контекстное меню- специальная вставка

В открывшемся окне поставим галочку «Транспонировать»:

Если дополнительно мы отметим точкой «Значения», то будут вставлены только значения (принтскрин ниже, слева), если «Все» то вставка осуществится вместе с форматом (принтскрин ниже, справа):

Результат перевода строк в столбцы

Для обратного преобразования (столбцов в строки) нужно выполнить туже самую операцию транспонирования.

Если есть вопросы — задавайте!

Будем благодарны, если Вы нажмете +1 и/или Мне нравится внизу данной статьи или поделитесь с друзьями с помощью кнопок расположенных ниже.

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

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

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

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

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

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

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

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

Excel. Преобразование массива в столбец или строку

Если необходимо преобразовать массив в столбец или строку (рис. 1), можно воспользоваться функцией Индекс.

Рис. 1. Массив преобразован в столбец

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

Функция ИНДЕКС возвращает значение, хранящееся в ячейке (являющейся элементом массива). Какую ячейку выбрать, функция указывает по горизонтальному и вертикальному номеру ячейки, отсчитываемому от левого верхнего угла массива. Например (рис. 2):

Рис. 2. Пример «работы» функции ИНДЕКС

где A1:G16 – область массива

3 – номер строки массива

5 – номер столбца массива

то есть, индекс задал ячейку Е3

Чтобы легче понять формулу ИНДЕКС, преобразующую массив в столбец, выполним последовательные шаги (см. также лист «Рис. 3» Excel-файла):

  1. Функция СТРОКА() возвращает номер строки той ячейки, в которой она забита (рис. 3); столбец I;
  2. Функция ЧИСЛСТОЛБ($A$1:$G$16) возвращает число столбцов в массиве;
  3. Функция ОКРВВЕРХ(СТРОКА()/ЧИСЛСТОЛБ($A$1:$G$16);1) возвращает номер столбца, ячейки, значение который мы хотим получить, столбец J; дает значение 1 для первых 7 значений (в общем случае, для первых N значений, где N – число столбцов в массиве);
  4. Функция ОСТАТ(СТРОКА();ЧИСЛСТОЛБ($A$1:$G$16)+0,0001) возвращает номер от 1 до 7 (в общем случае, от 1 до N, где N – число столбцов в массиве); получается, что идет перебор индексов: сначала первый индекс равен 1 (строка), а второй изменяется от 1 до 7 (столбец); далее строка = 2, а столбец перебирается от 1 до 7 и т.д., пока не пройдемся по всем строкам массива; +0,0001 – это маленькая хитрость; без этой добавочки при делении 7/7 будет получаться 0 в остатке, а нам нужно получить 7; эта формула расположена в столбце K;
  5. Функция ИНДЕКС($A$1:$G$16;ОКРВВЕРХ(СТРОКА()/ЧИСЛСТОЛБ($A$1:$G$16);1); ОКРВВЕРХ(ОСТАТ(СТРОКА();ЧИСЛСТОЛБ($A$1:$G$16)+0,0001);1)) возвращает значение из ячейки; см. столбец L
    1. Массив, где ищется значение, выделен желтым
    2. Номер строки внутри массива – зеленым
    3. Номер столбца внутри массива – серым

В Excel-файле представлено преобразование массива в столбец и строку. При этом приведено два варианта преобразования:

  • Сначала по строкам, затем по столбцам массива
  • Сначала по столбцам, затем по строкам массив

Как сделать столбцы строками в Excel

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

Читать еще:  Зафиксировать строку в excel

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

Способ № 1. Используем копирование и вставку с транспонированием.

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

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

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

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

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

Для того чтобы транспонировать таблицу Excel ее нужно сначала преобразовать в диапазон. Делается это при помощи кнопки «Преобразовать в диапазон» на вкладке «Робота с таблицами – Конструктор».

Кроме этого, таблицу Excel можно транспонировать при помощи функции ТРАНСП, о которой мы расскажем ниже.

Способ № 1. Используем формулу «ТРАНСП».

Если ваши данные в таблице регулярно меняются и вам постоянно нужно преобразовывать столбцы в строки, то описанный выше способ будет очень неудобен. Вместо него лучше использовать формулу «ТРАНСП», которая делает все тоже самое, но автоматически.

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

Для начала вам нужно выделить область таблицы, в которую будут помещаться транспонированные данные. Область должна быть такой же, как и область с исходными данными, но количество столбцов и строк нужно поменять местами. Например, если ваша исходная область имела 30 столбцов и 1 строку, то область для транспонированных данных должна иметь 30 строк и 1 столбец.

После выделения области нужного размера можно приступать к вводу формулы, которая сделает столбцы строками и наоборот. Для этого нажимаем Enter, вводим знак «=», вводим название формулы «ТРАНСП» и открываем круглую скобку.

Дальше выделяем мышкой область с исходными данными либо вручную вводим адрес нужного массива данных.

После ввода адреса массива с данными закрываем круглую скобку и нажимаем комбинацию Ctrl-Shift-Enter. Нажимать нужно именно Ctrl-Shift-Enter, а не просто Enter, так как эта формула работает с массивом.

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

После нажатия на Ctrl-Shift-Enter вы получите новую область таблицы, в которой столбцы преобразованы в строки и наоборот.

При этом, теперь все ячейки новой области связаны с исходными ячейками с помощью формулы «ТРАНСП». Это означает, что при изменении данных в исходной области, данные будут изменяться и в новой преобразованной области.

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

Решение одним словом: транспонирование (transpose). Дальше ищущий может гуглить и найти данную статью.

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

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

Выделяем один столбец или строку, копируем. В новом месте или листе, где будет располагаться транспонированная таблица, кликаем правой кнопкой «Специальная вставка».

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

Из спорных преимуществ: сохранится все оформление ячеек, что требуется не всегда. Но главный недостаток способа – довольно трудоемкий процесс. Если строк и столбцов больше 100? Сто раз переносить данные построчно?

  1. Используем формулу

Гораздо более изящное решение.

Функция АДРЕС(номер_строки; номер_столбца) отдает ссылку (адрес) ячейки по 2 числам, где первое — номер строки, второе — номер столбца. Т.е. запись =АДРЕС(1;1) вернет нам ссылку на ячейку А1.

С помощью функций СТРОКА(ячейка) и СТОЛБЕЦ(ячейка) меняем порядок выдачи у функции АДРЕС — не (строка, столбец), а (столбец, строка).

В текущем виде формула =АДРЕС(СТОЛБЕЦ(A1);СТРОКА(A1)) вернет текст $A$1, надо преобразовать результат в ссылку, обернув все выражение в функцию ДВССЫЛ(ссылка_в_виде_текста).

В английском Excel:

Применив формулу для ячейки А9 (в примере на картинке), растягиваем ее на остальные. Результат:

И сразу можно увидеть 2 небольших минуса этого способа:

  • Пустые ячейки заполняются нулями, правим вручную;
  • Формат ячеек – числовой, так Даты тоже нужно будет преобразовывать вручную.

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

  1. Сводная таблица

Даже формула – не совсем то, что надо. Мы преобразовываем данные туда-сюда, хотя нам всего-то требуется поработать с самой таблицей.

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

Выделяем таблицу, выбираем в меню Вставка – Сводная таблица. Указываем, куда вставить новую таблицу (можно на новый лист или куда-нибудь на текущий), график – да/нет. Ок. В настройках меняем местами блоки названия строк и названия столбцов. Результат:

Excel: перенос данных из столбцов в строки и наоборот: 2 комментария

Добрый день! Есть данные по выручке которые расположены по строке , теперь нужно перенести их вместе с датами в столбец . Можно ли это сделать без VBA и макросов?

Можно сводной таблицей, см. пример в тексте

Добавить комментарий Отменить ответ

Этот сайт использует Akismet для борьбы со спамом. Узнайте как обрабатываются ваши данные комментариев.

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

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