Как преобразовать одну строку в несколько столбцов и строк в Excel?
В Excel пользователи часто сталкиваются с ситуациями, когда необходимо переформатировать длинную строку данных в более удобочитаемый вид с несколькими столбцами и строками. Такое преобразование требуется при работе с импортированными, экспортированными или вручную составленными данными, представленными в горизонтальном виде, но требующими анализа, обработки или представления в виде табличной сетки. Например, у вас может быть набор данных, расположенный в одной строке, который необходимо преобразовать в структуру таблицы, как показано ниже:
Преобразование строки данных в структурированную табличную форму даёт сразу несколько преимуществ: повышает читаемость, упрощает анализ и открывает доступ к расширенным инструментам Excel — таким как фильтрация и построение диаграмм. В зависимости от ваших задач Excel предлагает разные способы выполнить такое преобразование: от встроенных функций и формул до специализированных надстроек и автоматизации с помощью VBA. В этой статье мы рассмотрим несколько практических подходов, каждый из которых идеально подходит для определённых сценариев и предпочтений пользователей.
Содержание:
- Преобразуйте строку ячеек в несколько столбцов или строк с помощью функций «Текст по столбцам» и «Вставить со сменой ориентации»
- Преобразуйте одну строку в несколько столбцов и строк с помощью Преобразовать диапазон
- Преобразуйте одну строку в несколько столбцов и строк с помощью формулы Excel INDEX
- Преобразуйте одну строку в несколько столбцов и строк с помощью кода VBA
Преобразуйте строку ячеек в несколько столбцов или строк с помощью функций «Текст по столбцам» и «Вставить со сменой ориентации»
Excel предлагает функции «Текст по столбцам» и «Вставить специально» («Смена ориентации») для базовой реорганизации данных. Они особенно полезны, если ваши данные находятся в одной ячейке или требуют простого преобразования.
1. Выделите ячейку, которую нужно преобразовать, и нажмите Данные > Текст по столбцам. См. снимок экрана:
2. В открывшемся диалоговом окне сначала выберите параметр С разделителями, затем нажмите Далее. На следующем шаге отметьте Пробел (или фактический разделитель, используемый в ваших данных) в разделе Разделители. См. снимок экрана:
3. Нажмите Готово. Одна ячейка будет разбита на несколько столбцов в соответствии с указанным разделителем. При необходимости вы можете скорректировать ширину столбца для лучшей видимости. См. снимки экрана:
 |
 |
 |
Примечание:Если вы хотите дополнительно преобразовать полученные столбцы в несколько строк:
- Выделите значения в новых столбцах и нажмите Ctrl + C, чтобы скопировать.
- Щёлкните правой кнопкой мыши по ячейке, с которой должен начинаться транспонированный диапазон, и выберите Вставить специально > Смена ориентации. См. снимки экрана:
 |
|
 |
Этот подход прост в использовании для базовых преобразований и хорошо подходит при небольшом объёме данных. Однако если требуется преобразовать не отдельную ячейку, а целую строку (а не только одну ячейку) в таблицу из нескольких столбцов и строк, этих функций будет недостаточно — понадобится дополнительная ручная настройка. Для решения более сложных задач перейдите к следующим методам.
Преобразуйте одну строку в несколько столбцов и строк с помощью Преобразовать диапазон
Если у вас установлен Kutools для Excel, воспользуйтесь инструментом Преобразовать диапазон! Эта функция позволяет всего за несколько щелчков мгновенно преобразовать данные из одной строки или одного столбца в полноценный диапазон с несколькими строками и столбцами. Она особенно незаменима при работе с большими наборами данных, минимизируя необходимость ручного копирования или сложных формул.
После бесплатной установкиKutools для Excel выполните следующие действия:
1. Выделите строку, которую нужно преобразовать, затем перейдите в меню Kutools > Диапазон > Преобразовать диапазон. См. снимок экрана:
2. В диалоговом окне Преобразовать диапазон выберите Одна строка в диапазон. В поле Столбцов на запись укажите, сколько столбцов должно содержаться в каждой строке результата. Это число можно задать в зависимости от желаемого формата или требований отчёта. См. снимок экрана:
Советы:
- Параметр «Фиксированное значение» для столбцов задаёт количество элементов в каждой новой строке. Например, если у вас есть одна строка из 18 значений и вы установите количество столбцов на запись равным 6, данные автоматически преобразуются в 3 строки по 6 столбцов каждая.
- Если длина вашей строки не делится нацело, последняя строка может содержать меньше столбцов, чем указано.
3. Нажмите OK. Появится диалоговое окно с запросом выбора ячейки назначения для преобразованного результата. Укажите ячейку за пределами исходного диапазона, чтобы избежать перезаписи данных.
4. Снова нажмите OK. Ваша строка преобразуется в аккуратно организованный диапазон с несколькими строками и столбцами. Результат показан ниже:
Примечания:
- Эта функция также работает в обратном направлении — позволяя сжимать диапазоны с несколькими столбцами/строками в одну строку или столбец при необходимости.Нажмите здесь, чтобы узнать больше о Преобразовать диапазон.
- Обязательно создайте резервную копию данных или работайте с их копией, особенно если исходный и целевой диапазоны могут пересекаться.
Kutools для Excel— раскройте весь потенциал Excel с помощью более чем 300 незаменимых инструментов, которые ускоряют и упрощают вашу работу, а также воспользуйтесь возможностями ИИ для более разумной обработки данных и повышения продуктивности.Получить сейчас
Преобразуйте одну строку в несколько столбцов и строк с помощью формулы Excel INDEX
Если вы предпочитаете подход на основе формул — идеальный выбор для тех, кто не хочет использовать надстройки или стремится автоматизировать процесс с помощью формул листа, — функция Excel INDEX, часто в сочетании с функцией SEQUENCE (доступна в Microsoft 365 и новее), позволяет эффективно преобразовать одну строку в двумерный диапазон.
Этот метод обеспечивает динамическую связь между исходными данными и результатом: любые изменения в исходной строке автоматически отображаются в выходных данных, представленных в виде нескольких строк и столбцов.
Пример сценария: Допустим, ваши данные находятся в строке 1, от ячейки A1 до R1 (всего 18 элементов), и вы хотите разместить их в 6 столбцах и 3 строках.
1. Выделите верхнюю левую ячейку нужного диапазона для размещения списка (например, ячейку)A3).
2. Введите следующую формулу в ячейку A3:
=INDEX($A$1:$R$1, SEQUENCE(3,6))Пояснение параметров: Диапазон $A$1:$R$1 — это ваша исходная строка. 6 — количество столбцов в каждой строке результата. При необходимости скорректируйте эти значения.
Формула автоматически заполнит сетку размером 3×6 (доступно только в версиях Excel с поддержкой динамических массивов).
Ограничения: При работе с очень большими наборами данных, сложными требованиями к перестройке или когда важен пользовательский порядок, формулы могут становиться громоздкими и трудными в управлении. В таких случаях рекомендуем использовать автоматизацию с помощью VBA (см. ниже).
Преобразуйте одну строку в несколько столбцов и строк с помощью кода VBA
Для полной автоматизации и максимального контроля вы можете использовать макрос VBA, чтобы преобразовать одну строку в сетку столбцов и строк нужного размера. Такой подход особенно эффективен при выполнении повторяющихся задач и работе с большими объёмами данных, а также легко адаптируется под любые требуемые размеры вывода.
Типичный сценарий использования: У вас есть строка переменной длины, которую нужно разбить на заданное количество столбцов в каждой строке (например, каждые 6 значений образуют новую строку), и вы хотите избежать ручного или формульного преобразования.
Инструкции:
1. Откройте Excel и нажмите Alt + F11, чтобы запустить редактор VBA.
2. В редакторе VBA выберите Вставка > Модуль и вставьте следующий код в окно модуля:
Sub RowToMultiRowCol()
Dim inputRng As Range
Dim outputCell As Range
Dim nCols As Integer
Dim nData As Integer
Dim i As Integer
Dim r As Integer
Dim c As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set inputRng = Application.InputBox("Select the single row to convert", xTitleId, "", Type:=8)
Set outputCell = Application.InputBox("Select the top-left cell for the result", xTitleId, "", Type:=8)
nCols = Application.InputBox("Number of columns per row:", xTitleId, "6", Type:=1)
On Error GoTo 0
If inputRng Is Nothing Or outputCell Is Nothing Or nCols <= 0 Then Exit Sub
nData = inputRng.Columns.Count
For i = 1 To nData
r = Int((i - 1) / nCols)
c = ((i - 1) Mod nCols)
outputCell.Offset(r, c).Value = inputRng.Cells(1, i).Value
Next i
End Sub3. Закройте редактор VBA. В Excel перейдите в раздел Средства разработчика → Макросы, выберите макрос RowToMultiRowCol и нажмите Выполнить.
4. Макрос предложит вам:
- Выделите диапазон, содержащий одну строку, которую нужно преобразовать.
- Выберите верхнюю левую ячейку, в которую должен быть помещён результат (убедитесь, что этот диапазон не пересекается с исходным).
- Enter the desired number of columns per row (e.g.,6 for arranging into multiple rows of 6 columns each).
Макрос быстро и эффективно заполнит область вывода преобразованными данными.
Преимущества: Позволяет выполнять задачи в один клик быстро и гибко настраивать формат вывода. Идеально подходит для сложных сценариев, работы с большими объёмами данных и частого использования.
Совет: Всегда сохраняйте книгу перед запуском макросов — действия VBA отменить невозможно.
Демонстрация: Транспонирование диапазона
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек