Как преобразовать один столбец в несколько в эксель
Хотим, упрощенно говоря, повернуть таблицу на бок, т.е. то, что располагалось в строке - пустить по столбцу и наоборот:
Способ 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.
Теперь соединяем эти функции, чтобы получить нужную нам ссылку, т.е. вводим в любую свободную ячейку вот такую формулу:
=ДВССЫЛ(АДРЕС(СТОЛБЕЦ(A1);СТРОКА(A1)))
в английской версии Excel это будет =INDIRECT(ADDRESS(COLUMN(A1),ROW(A1)))
А затем копируем (протягиваем) формулу на соседние ячейки как обычно черным крестом. В итоге должно получиться примерно следующее:
Т.е. при копировании формулы вниз по столбцу, она выдает ссылку, которая уходит вправо по строке и наоборот. Чего и требовалось.
Плюсы : сохраняются связи между таблицами, можно легко вносить изменения во вторую таблицу.
Минусы : форматирование не сохраняется, но его можно легко воспроизвести Специальной вставкой (вставить только Формат с флажком Транспонировать
Представьте, что после выгрузки из какой-либо корпоративной ERP-системы, базы данных или программы вы получили данные в виде длинного столбца, а для работы вам из них нужно скомпоновать нормальную двумерную таблицу:
Очевидно, что вручную этим заниматься очень долго и муторно, поэтому давайте разберем несколько способов сделать это красиво.
Способ 1. Формулы
Если внимательно присмотреться к исходным данным, то можно заметить четкую систему - столбец делится на блоки по 7 ячеек, каждый из которых нам нужно трансформировать в соответствующую строку. Чтобы это сделать, нужно привязаться к номерам строк в исходной таблице. Каждая седьмая строка (1,8,15. ) - это дата сделки. На одну строку ниже - имя менеджера. Еще на одну ниже - город и т.д. Для простоты и наглядности добавим к заготовке будущей таблицы нумерацию слева и сверху:
Числа слева (1,8,15. ) - это номера строк, начинающих каждый блок. Числа сверху (0,1,2,3. ) - это сдвиг внутри блока. Теперь, чтобы получить адреса нужных нам ячеек можно ввести простую формулу, склеивающую букву столбца с номером строки:
Обратите внимание на корректное закрепление знаками доллара строк и столбцов - это важно при копировании ссылки из первой ячейки на весь зеленый диапазон.
И осталось превратить нашу текстовую ссылку в полноценную. В этом нам поможет функция ДВССЫЛ (INDIRECT) , о которой я подробно уже писал:
Способ 2. Power Query
Power Query - это бесплатная надстройка для Excel, созданная компанией Microsoft. Для Excel 2010-2013 ее можно совершенно свободно скачать и установить (появится вкладка Power Query), а в Excel 2016 она уже встроена по умолчанию и все ее инструменты находятся на вкладке Данные (Data) . Эта надстройка умеет импортировать в Excel данные практически из любых источников и трансформировать их потом любым желаемым образом.
Для начала, превратим наш диапазон в "умную таблицу" - для этого данные нужно выделить и нажать сочетание клавиш Ctrl + T или выбрать на вкладке Главная команду Форматировать как таблицу (Home - Format as Table) . В появившемся затем окне важно снять галочку Таблица с заголовками (My table has headers) , т.к. "шапки" у нас нет:
После загрузки наши данные появятся в новом окне Power Query:
Теперь добавляем к данным столбец индекса (т.е., фактически, имитируем нумерацию строк) через вкладку Добавление столбца - Столбец индекса (Add Column - Index Column) :
А затем добавим столбец, где выведем остаток от деления индекса на 7 с помощью команды Добавление столбца - Стандартные - Остаток от деления (Add Column - Standard - Mod) :
Теперь свернем нашу таблицу, конвертируя числа в получившемся столбце в заголовки новых колонок. Для этого используем команду Столбец сведения на вкладке Преобразовать (Transform - Pivot Column) :
Осталось заполнить получившиеся пустоты (null) во всех столбцах, кроме двух последних командой Заполнить вниз на вкладке Преобразовать (Transform - Fill Down) :
Удалить ненужные столбцы (первый и последний) и отфильтровать лишние строки (убрать галочку Null в фильтре по столбцу 5). Заголовки столбцов можно переименовать двойным щелчком для пущей приятности, а также настроить подходящие форматы данных с помощью иконок в шапке:
Готовую таблицу выгружаем на новый или любой из имеющихся листов с помощью команды Главная - Закрыть и загрузить - Закрыть и загрузить как (Home - Close&Load - Close&Load to) :
В будущем, если исходная таблица с данными поменяется в размерах или в содержимом, достаточно будет просто обновить наш запрос, щелкнув по финальной таблице правой кнопкой мыши и выбрав команду Обновить (Refresh) .
Способ 3. Макрос из надстройки PLEX
Если у вас установлена моя надстройка PLEX для Excel 2007-2016, то можно все сделать еще быстрее и проще. Выделяем исходные данные и жмем на вкладке PLEX - Трансформация - Изменить размеры (Transform - Resize) :
Как транспонировать / преобразовать один столбец в несколько столбцов в Excel?
Предположим, у вас есть таблица, показанная ниже, и вам нужно изменить один столбец на диапазон. Здесь мы покажем вам некоторые хитрости о том, как превратить один столбец в несколько столбцов.
Kutools for Excel's Transform Range feature can help you to convert a single row or column data to a range of cells as you need.
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!
Перенести один столбец в несколько столбцов с формулами
Преобразуйте один столбец в диапазон данных от строки к строке:
В Excel OFFSET функция может вам помочь, вы можете использовать эту формулу как следующие шаги:
1. В пустой ячейке C1 введите следующую формулу: =OFFSET($A$1,COLUMNS($A1:A1)-1+(ROWS($1:1)-1)*5,0) , а затем перетащите маркер заполнения с C1 на G1, см. снимок экрана:
Внимание: A1 это запущенная ячейка, *5 обозначает количество ячеек, которое вы хотите иметь в каждой строке. Между тем вы должны начать эту формулу в столбце C.
2. Затем продолжайте перетаскивать маркер заполнения вниз до нужного диапазона. И данные одного столбца были преобразованы в несколько строк, как показано на следующем снимке экрана:
3. Поскольку они являются формулами, при их копировании необходимо копировать и вставлять их как значения.
Преобразуйте один столбец в диапазон данных от столбца к столбцу:
Приведенная выше формула может помочь вам перенести один столбец в диапазон от строки к строке, если вам нужно перенести столбец в диапазон от столбца к столбцу, следующая формула также может оказать вам услугу.
1. В пустой ячейке C1 введите следующую формулу: =INDEX($A$1:$A$20,ROW(C1)+(5*(COLUMNS($C$1:C$1)-1))) , а затем перетащите маркер заполнения с C1 на C5, см. снимок экрана:
Внимание: A1: A20 это данные столбца, которые вы хотите преобразовать, 5 * обозначает количество ячеек, которое вы хотите иметь в каждом столбце.
2. Затем перетащите маркер заполнения через столбец C в столбец F, и данные в одном столбце будут перенесены в диапазон от столбца к столбцу. см. снимок экрана:
Перенести один столбец в несколько столбцов с помощью Kutools for Excel
У этих формул есть некоторые ограничения, возможно, их сложно применять новичкам. Здесь я продолжу знакомить вас с простым способом решения этой проблемы. С участием Kutools for Excel's Диапазон преобразования вы можете быстро преобразовать данные одной строки или столбца в диапазон ячеек по мере необходимости. См. Демо ниже. Нажмите, чтобы загрузить Kutools for Excel!
После установки Kutools for Excel, пожалуйста, сделайте так:
1. Выберите столбец, который вы хотите преобразовать.
2. Нажмите Кутулс > Диапазон > Диапазон преобразования, см. снимок экрана:
3. В Диапазон преобразования диалоговом окне укажите следующие параметры:
- Выберите Один столбец для диапазона из Тип трансформации;
- Укажите количество ячеек в строке, если вы выбрали столбец с пустыми ячейками в качестве разделителя, вы можете проверить Пустая ячейка ограничивает записи, и данные начнут новую строку в каждой пустой ячейке. Вы также можете указать количество ячеек в строке из Фиксированная стоимость что вам нужно.
4. Затем нажмите OK, чтобы Диапазон преобразования Появится окно подсказки и щелкните ячейку, чтобы поместить результат. Диапазон вывода можно указать в разных листах и книгах. Смотрите скриншот:
5. Нажмите OK, вы увидите, что один столбец был преобразован в несколько столбцов в выбранной ячейке. Смотрите скриншот:
Kutools for Excel's Диапазон преобразования Инструмент может помочь вам легко преобразовать один столбец в диапазон, преобразовать одну строку в диапазон, преобразовать диапазон в одну строку и преобразовать диапазон в один столбец. Нажмите, чтобы узнать больше…
Утилита Диапазон преобразования in Kutools for Excel может помочь вам преобразовать (преобразовать) вертикальный столбец в несколько столбцов и быстро преобразовать строку в несколько строк.
Например, если у вас есть столбец данных, как показано на следующем снимке экрана, информация о каждом клиенте занимает 3 строки сверху вниз в виде имени, телефона и адреса. С участием Диапазон преобразования вы можете быстро преобразовать это в три строки, например:
И наоборот, вы можете преобразовать несколько столбцов или строк в один столбец или строку.
Вкладка Office позволяет редактировать и просматривать в Office с вкладками и значительно упрощает работу .
- Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
- Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
- Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
- Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
- Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
- Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
- Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.
Нажмите Кутулс > Диапазон > Диапазон преобразования. Смотрите скриншоты:
Преобразование или преобразование столбца в несколько столбцов
1. Выберите один столбец, который вы хотите преобразовать в несколько столбцов, как показано ниже (см. Снимок экрана), а затем примените утилиту.
2. Задайте настройку в Диапазон преобразования диалоговое окно следующим образом (см. снимок экрана). Внимание: Пустая ячейка ограничивает записи опция будет начинать новую строку в каждой пустой ячейке при преобразовании столбца в несколько столбцов. Если вы выбрали столбец с пустыми ячейками в качестве разделителя и хотите использовать пустую ячейку в качестве разделителя для преобразования столбца, установите этот флажок. Фиксированная стоимость опция запустит новую строку после того, сколько ячеек в строке.
3. Нажмите OK. И укажите Диапазон выходного чтобы вместить результат. Внимание: Диапазон выходного могут быть указаны в разных листах и книгах. Смотрите скриншот:
4. Нажмите OK. Вы увидите следующий результат. Смотрите скриншоты:
Преобразование или преобразование строки в несколько строк
1. Выберите одну строку, которую вы хотите преобразовать в несколько строк, как показано ниже (см. Снимок экрана), а затем примените утилиту.
2. Задайте настройку в Диапазон преобразования диалоговое окно следующим образом (см. снимок экрана). Внимание: Пустая ячейка ограничивает записи опция запустит новый столбец в каждой пустой ячейке при преобразовании строки в несколько строк. Если вы выбрали строку с пустыми ячейками в качестве разделителя и хотите использовать пустую ячейку в качестве разделителя для преобразования строки, установите этот флажок. Фиксированная стоимость опция запустит новый столбец после того, сколько ячеек в столбце.
3. Нажмите OK. И укажите Диапазон выходного чтобы вместить результат. Внимание: Диапазон выходного могут быть указаны в разных листах и книгах. Смотрите скриншот:
4. Нажмите OK. Вы увидите следующий результат. Смотрите скриншоты:
Преобразование или преобразование нескольких столбцов в один столбец
1. Выберите диапазон, который вы хотите преобразовать в столбец.
2. Задайте настройку в Диапазон преобразования диалоговое окно следующим образом:
3. Нажмите OK. И укажите Диапазон выходного чтобы вместить результат. Обратите внимание Результат ассортимент могут быть указаны в разных листах и книгах. Смотрите скриншот:
4. Нажмите OK. Вы увидите следующий результат. Смотрите скриншоты:
Преобразование или преобразование нескольких строк в одну строку
Чтобы преобразовать несколько строк в одну, вы можете сделать следующее:
1. Выберите диапазон, который вы хотите преобразовать в строку.
2. Задайте параметр в диалоговом окне «Диапазон преобразования» следующим образом:
3. Нажмите OK. И укажите Результат ассортимент чтобы вместить результат. Обратите внимание Результат ассортимент могут быть указаны в разных листах и книгах. Смотрите скриншот:
4. Нажмите OK. Вы увидите следующий результат. Смотрите скриншоты:
Ноты:
Эта функция поддерживает расстегивать (Ctrl + Z).
Данные, которые необходимо преобразовать, остаются неизменными после преобразования вертикального диапазона.
Преобразованный диапазон и выходной диапазон могут находиться на разных листах или книгах.
Демонстрация: преобразование диапазона ячеек в одну строку или столбец и наоборот
Kutools for Excel: с более чем 300 удобными функциями, вы можете попробовать бесплатно без ограничений в течение 30 дней. Загрузите и бесплатную пробную версию прямо сейчас!
Рекомендуемые инструменты для повышения производительности
Следующие ниже инструменты могут значительно сэкономить ваше время и деньги. Какой из них вам подходит?
Office Tab : Использование удобных вкладок в вашем офисе , как и в случае Chrome, Firefox и New Internet Explorer.
Kutools for Excel : Более 300 дополнительных функций для Excel 2021, 2019, 2016, 2013, 2010, 2007 и Office 365.
Kutools for Excel
Описанная выше функциональность - лишь одна из 300 мощных функций Kutools for Excel.
Предназначен для Excel(Office) 2021, 2019, 2016, 2013, 2010, 2007 и Office 365. Бесплатно скачать и использовать в течение 60 дней.
Перенести каждые 5 или n строк из одного столбца в несколько столбцов с формулой
В Excel вы можете применить следующую формулу для транспонирования каждых n строк из одного столбца в несколько столбцов, сделайте следующее:
1. Введите следующую формулу в пустую ячейку, в которую вы хотите поместить результат, например C1, = ИНДЕКС ($ A: $ A; СТРОКА (A1) * 5-5 + КОЛОНКА (A1)) , см. снимок экрана:
Внимание: В приведенной выше формуле A: это ссылка на столбец, которую вы хотите транспонировать, и A1 первая ячейка используемого столбца, число 5 указывает количество столбцов, в которых будут располагаться ваши данные, вы можете изменить их по своему усмотрению. И первая ячейка списка должна располагаться в первой строке рабочего листа.
2. Затем перетащите маркер заливки вправо к пяти ячейкам и продолжайте перетаскивать маркер заливки вниз в диапазон ячеек, пока не отобразится 0, см. Снимок экрана:
Чтобы преобразовать каждые 5 или n строк из одного столбца в несколько столбцов, Kutools for Excel's Диапазон преобразования Утилита поможет вам решить эту задачу как можно быстрее. Это также может помочь вам перенести диапазон данных в одну строку или столбец. Нажмите, чтобы загрузить Kutools for Excel!
Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 30 дней. Загрузите и бесплатную пробную версию прямо сейчас!
Транспонировать каждые 5 или n строк из одного столбца в несколько столбцов с кодом VBA
Если вы не можете правильно применить формулу, следующий код VBA также может вам помочь.
1. Удерживайте ALT + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.
2. Нажмите Вставить > Модулии вставьте следующий код в Модули Окно.
Код VBA: транспонировать каждые 5 или n строк из одного столбца в несколько столбцов:
3. После вставки кода нажмите F5 нажмите клавишу, чтобы запустить его, и появится диалоговое окно, напоминающее вам о выборе столбца, который вы хотите транспонировать, см. снимок экрана:
4. Затем нажмите OKи выберите одну ячейку, в которую вы хотите поместить результат, в другое всплывающее окно, см. снимок экрана:
5, И нажмите OK, данные в столбце были преобразованы в пять столбцов, которые вам нужны, см. снимок экрана:
Внимание: В приведенном выше коде вы можете изменить номер 5 на другой номер, который вам нужен.
Перенести каждые 5 или n строк из одного столбца в несколько столбцов с помощью Kutools for Excel
Если у вас есть Kutools for Excel, С его Диапазон преобразования утилиту, вы можете быстро перенести один столбец или строку на несколько столбцов и строк.
После установки Kutools for Excel, пожалуйста, сделайте следующее:
1. Выберите данные в столбце, а затем щелкните Кутулс > Диапазон > Диапазон преобразования, см. снимок экрана:
2. В Диапазон преобразования диалоговое окно, выберите Один столбец для диапазона вариант под Тип трансформации, а затем проверьте Фиксированная стоимость под Строк на запись, затем укажите количество столбцов, которые вы хотите перенести в Фиксированная стоимость box, см. снимок экрана:
3, Затем нажмите Ok кнопку, в появившемся окне выберите ячейку для вывода результата, см. снимок экрана:
4. Затем нажмите OK , и данные вашего столбца были перенесены каждые 5 строк, как показано на следующем снимке экрана:
Демо: транспонируйте каждые 5 или n строк из одного столбца в несколько столбцов с помощью Kutools for Excel
Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 30 дней. Загрузите и бесплатную пробную версию прямо сейчас!
Читайте также: