Как преобразовать одну строку в несколько столбцов и строк в Excel?
В Excel пользователи часто сталкиваются с ситуациями, когда им нужно реорганизовать длинную одиночную строку данных в более читаемый формат с несколькими столбцами и строками. Такое преобразование распространено при работе с импортированными, экспортированными или вручную составленными данными, которые представлены в горизонтальной компоновке, но требуют анализа, обработки или представления в табличной сетке. Например, у вас может быть набор данных, перечисленный по одной строке, и вы хотите переформатировать его в структуру таблицы, как показано ниже:
Преобразование строки данных в структурированный табличный формат имеет несколько преимуществ: оно улучшает читаемость, облегчает анализ данных и позволяет использовать более широкий спектр инструментов Excel, таких как фильтрация и построение диаграмм. В зависимости от ваших конкретных потребностей, Excel предоставляет несколько способов достижения этого преобразования — от встроенных функций и формул до специализированных надстроек и автоматизации через код VBA. В этой статье мы рассмотрим несколько практических подходов, каждый из которых подходит для различных сценариев и предпочтений пользователей.
Содержание:
- Преобразование строки ячеек в несколько столбцов или строк с помощью функций «Текст по столбцам» и «Вставить с транспонированием»
- Преобразование одной строки в несколько столбцов и строк с помощью «Преобразовать диапазон»
- Преобразование одной строки в несколько столбцов и строк с использованием формулы Excel INDEX
- Преобразование одной строки в несколько столбцов и строк с помощью кода VBA
Преобразование строки ячеек в несколько столбцов или строк с помощью функций «Текст по столбцам» и «Вставить с транспонированием»
Excel предоставляет функции «Текст по столбцам» и «Вставить особые параметры (Транспонировать)» для базовой реорганизации данных. Эти опции полезны, если ваши данные находятся в одной ячейке или требуется простая реструктуризация.
1. Выберите ячейку, которую необходимо преобразовать, и нажмите Данные > Текст по столбцам. См. скриншот:
2. В появившемся диалоговом окне сначала выберите опцию Разделители, затем нажмите Далее. На следующем шаге выберите Пробел (или фактический разделитель, который используется в ваших данных) в разделе Разделители. См. скриншот:
3. Нажмите Готово. Ваша одиночная ячейка будет разделена на несколько столбцов на основе указанного разделителя. Возможно, вам потребуется скорректировать ширину столбцов для лучшей видимости. См. скриншоты:
 |
 |
 |
Примечание: Если вы хотите дополнительно преобразовать эти значения столбцов в несколько строк:
- Выберите значения в новых столбцах, нажмите Ctrl + C для копирования.
- Щелкните правой кнопкой мыши на ячейке, где вы хотите начать размещение транспонированных данных, и выберите Вставить особые параметры > Транспонировать. См. скриншоты:
 |
|
 |
Этот подход прост для базовых преобразований и когда объем данных управляем. Однако он наиболее подходит для случаев, когда исходные данные находятся в одной ячейке. Если у вас одна строка (а не одна ячейка) данных, которую нужно преобразовать в сетку из нескольких столбцов и строк, эти функции сами по себе недостаточны, и потребуется дополнительная ручная корректировка. Для более сложных задач продолжайте со следующими решениями.
Преобразование одной строки в несколько столбцов и строк с помощью «Преобразовать диапазон»
Если у вас установлена Kutools for Excel, вы можете воспользоваться инструментом «Преобразовать диапазон». Эта функция позволяет напрямую преобразовывать однострочные или одностолбцовые макеты данных в многострочные и многоколоночные диапазоны всего за несколько кликов. Она особенно полезна для работы с большими наборами данных и минимизирует необходимость ручного копирования или использования сложных формул.
После бесплатной установки Kutools for Excel следуйте этим шагам:
1. Выберите одну строку, которую хотите преобразовать, затем перейдите в Kutools > Диапазон > Преобразовать диапазон. См. скриншот:
2. В диалоговом окне «Преобразовать диапазон» выберите Одна строка в диапазон. В области Колонки на запись укажите, сколько колонок должно содержать каждая строка результата. Вы можете задать это число на основе предпочитаемого макета или требований вашего отчета. См. скриншот:
Советы:
- «Фиксированное значение» для колонок определяет, сколько элементов будет содержать каждая новая строка. Например, если у вас одна строка из 18 значений, и вы установите колонки на запись равным 6, ваши данные будут преобразованы в 3 строки по 6 столбцов каждая.
- Если длина строки не делится нацело, последняя строка может содержать меньше столбцов, чем указано.
3. Нажмите OK. Появится диалоговое окно, в котором вас попросят выбрать целевую ячейку для преобразованного результата. Выберите ячейку вне исходного диапазона, чтобы избежать перезаписи данных.
4. Нажмите OK снова. Ваша одиночная строка теперь будет преобразована в аккуратно организованный диапазон с множеством строк и столбцов. Смотрите результат ниже:
Примечания:
- Эта функция также работает в обратном направлении — позволяя сжимать многостолбцовые/многострочные диапазоны в одну строку или столбец, если это необходимо. Нажмите здесь, чтобы узнать больше о «Преобразовать диапазон».
- Обязательно сделайте резервную копию своих данных или работайте с копией, особенно если исходный или целевой диапазоны могут пересекаться.
Kutools для Excel - Усильте Excel более чем 300 необходимыми инструментами. Наслаждайтесь постоянно бесплатными функциями ИИ! Получите прямо сейчас
Преобразование одной строки в несколько столбцов и строк с помощью формулы Excel INDEX
Если вы предпочитаете подход на основе формул — идеально подходит для пользователей, которые не хотят использовать надстройки или желают автоматизировать работу с формулами листа — функция INDEX Excel, часто в сочетании с 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 — количество столбцов для каждой строки вывода. Измените эти значения по необходимости.
Она автоматически заполнит сетку 3x6 (работает только в 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. Макрос предложит вам:
- Выбрать диапазон одной строки, который нужно преобразовать.
- Выбрать верхнюю левую ячейку, где должен появиться вывод (убедитесь, что этот диапазон не пересекается с вашим входным).
- Введите желаемое количество столбцов для каждой строки (например, 6 для размещения в несколько строк по 6 столбцов каждая).
Макрос быстро и эффективно заполнит вашу область вывода переупорядоченными данными.
Преимущества: Обеспечивает быструю операцию одним щелчком и гибкость в определении формы вывода. Подходит для сложных сценариев, больших наборов данных и при частом использовании.
Советы: Всегда сохраняйте свою книгу перед запуском макросов, так как действия 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-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек