Как разделить содержимое ячейки на несколько строк в Excel (сценарии + сравнение инструментов)

Совет: при необходимости разделитель "," можно заменить на ";" или "|" в зависимости от формата ваших данных.

Шаг2. Нажмите Enter.

Метод3: Power Query – разделение и расширение строк (Microsoft2016 и новее)

Power Query отлично подходит для структурированных и повторяемых операций. Это мощный инструмент для пакетных преобразований и обновления данных.

Подходит для: Оба сценария —1 (Разделить только целевую ячейку) и2 (Разделить ячейку и сохранить данные других столбцов)

Шаги: рассмотрим пример для Сценария1 (Разделить только целевую ячейку)

Шаг1. Выделите ваш диапазон, перейдите в Данные > Получить и преобразовать > Из таблицы/диапазона.

an

Шаг2. В окне Создать таблицу, нажмите ОК. (Если в выделенном диапазоне есть заголовки, установите флажок Моя таблица с заголовками.)

Шаг3. В редакторе Power Query используйте команду Разделить столбец по разделителю.

Шаг4. Выберите или введите разделитель, затем нажмите Дополнительно для расширенных настроек, выберите Разделить на строки, затем нажмите ОК.

Шаг5. Нажмите Закрыть и загрузить > Закрыть и загрузить или Закрыть и загрузить в.

Такие же шаги применяются для Сценария2 (разделение ячеек с сохранением остальных данных) — рассмотрим результат.

Целевой диапазон ячеек:

Результат:

Примечание: В окне редактора Power Query выберите только тот столбец, который нужно разбить на строки, затем нажмите По разделителю в разделе Разделить столбец.

Совет: Добавьте шаги Trim и Clean, чтобы убрать лишние пробелы или специальные символы.

Метод4: Плагин Kutools – разделение в несколько кликов

Если вам часто нужно делить ячейки и вы предпочитаете решения с графическим интерфейсом, Kutools — отличный инструмент. Он упрощает сложные операции до нескольких кликов.

Подходит для: Оба сценария —1 (Разделить только целевую ячейку) и2 (Разделить ячейку и сохранить данные других столбцов)

Шаги: Сценарий1 (Разделить только целевую ячейку)

Шаг1. Выберите целевую ячейку.

Шаг2. Перейдите в Kutools > Объединить и Разделить > Разделить ячейки.

Шаг3. Выберите Разделить на строки и задайте разделитель. Нажмите ОК.

Шаг4. Выберите ячейку назначения. Нажмите ОК.

Шаги: Сценарий2 (Разделить ячейку и сохранить данные других столбцов)

Шаг1. Выделите столбец, который хотите разбить на строки.

Шаг2. Перейдите в Kutools > Объединить и Разделить > Разделить данные на строки.

Шаг3. Укажите разделитель. Нажмите ОК.

Такие же шаги применимы для сценария2 (разделение с сохранением других данных) — рассмотрим результат.

Целевой диапазон ячеек:

Результат:

Примечание: Пробная версия на30 дней предоставляет полный функционал для тестирования.

Метод5: Скрипт VBA – полный пользовательский контроль

Для пользователей, знакомых с программированием, VBA открывает максимальные возможности настройки. Скрипты можно адаптировать под нужную структуру данных и ваши бизнес-правила.

Подходит для: Оба сценария —1 (Разделить только целевую ячейку) и2 (Разделить ячейку и сохранить данные других столбцов)

Шаги:

Шаг1. Нажмите Alt + F11 для открытия редактора VBA.

Шаг2. Введите Вставка > Модуль.

Шаг3. Скопируйте и вставьте приведённый ниже код VBA.

Сценарий1 — Разделить только целевую ячейку

'Update by Extendoffice
Sub SplitCellToRows_Simple()
    Dim InputCell As Range
    Dim Values As Variant
    Dim i As Long

  
    Set InputCell = Range("A1") ' change A1 to your target cell

    Values = Split(InputCell.Value, ",") 
    For i = 0 To UBound(Values)
        InputCell.Offset(i, 1).Value = Trim(Values(i))
    Next i
End Sub

Сценарий2 — Разделить ячейку и сохранить данные других столбцов

'Update by Extendoffice
Sub SplitRowsWithOtherColumns()
    Dim ws As Worksheet
    Dim LastRow As Long, i As Long
    Dim arr As Variant, j As Long
    Dim SplitVals As Variant
    Dim CurrentRow As Long

    Set ws = ActiveSheet
    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    For i = LastRow To 2 Step -1
        If InStr(ws.Cells(i, 2).Value, ",") > 0 Then
            SplitVals = Split(ws.Cells(i, 2).Value, ",")

            For j = UBound(SplitVals) To 1 Step -1
                ws.Rows(i + 1).Insert Shift:=xlDown
                ws.Cells(i + 1, 1).Value = ws.Cells(i, 1).Value
                ws.Cells(i + 1, 2).Value = Trim(SplitVals(j))
            Next j

            ws.Cells(i, 2).Value = Trim(SplitVals(0))
        End If
    Next i
End Sub

Примечание: Этот VBA-скрипт рассчитан на работу, если ваши данные находятся в столбцах A и B, а значения для разделения — в столбце B.

💡Советы:

• Перед запуском макроса всегда делайте резервную копию данных.
• Замените "," в коде скрипта на свой разделитель (например, ";" для точек с запятой или "|" для вертикальных черт), чтобы соответствовать вашему формату данных.

Частые проблемы

• Пустые строки или столбцы: обычно возникают из-за лишних разделителей или скрытых пробелов.
• Ошибки из-за объединённых ячеек: перед началом разделите объединённые ячейки.
• Power Query не обновляется: после изменения исходных данных не забудьте нажать Обновить.
• Неверный разделитель: ошибка между точкой с запятой и запятой может привести к некорректным результатам.

Связанные статьи:

• Утилита Транспонирование диапазона: легко преобразуйте несколько строк в столбцы и строки в Excel
• Инструмент для конвертации единиц: быстро преобразуйте десятичное число в двоичное/восьмеричное/шестнадцатеричное или наоборот