Перейти к содержимому

Kutools для Office — один пакет. Пять инструментов. Выполняйте больше.

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

Author Sun Last modified

В этом руководстве рассматривается процесс разделения содержимого одной ячейки на несколько строк в Excel. Описаны два распространённых сценария: в первом разделяется только целевая ячейка (другие столбцы игнорируются), во втором — данные ячейки распределяются по строкам с сохранением информации из остальных столбцов. Приведены различные методы для решения обеих задач: от стандартных функций Excel до продвинутых инструментов, таких как Power Query, VBA и сторонние плагины.

Два типичных сценария разделения

В зависимости от необходимости сохранить данные других столбцов, разделение может происходить разными способами. Ниже описаны два основных подхода, чтобы вы могли выбрать для себя оптимальный.

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

Иногда важно только содержимое одной ячейки. Нужно разбить значения на строки для наглядности, не дублируя информацию в других столбцах строки.

Пример

A,B,C

Результат:

A
B
C

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

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

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

Пример

USA | A,B,C

Результат:

USA | A
USA | B
USA | C

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

Когда может понадобиться разделить ячейку на несколько строк?

Пошаговые методы разделения ячеек

Распространённые проблемы

Когда может понадобиться разделить ячейку на несколько строк?

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

Типичные ситуации использования:

  • Несколько значений в одном столбце: например, теги, идентификаторы или адреса, записанные совместно.
  • Сводные таблицы или диаграммы: для которых нормализованный формат данных выгоднее.
  • Очистка импортированных данных: особенно при экспорте из CRM или опросников.
  • Стандартизация данных: чтобы таблица была подготовлена для базы данных и аналитики.

Пошаговые методы разделения ячеек

Теперь, когда вы определились со сценарием, рассмотрим различные методы — от стандартных функций до автоматизации. Выберите подходящий вариант в зависимости от ваших навыков и частоты задач.

Метод1: Встроенные функции Excel (Текст по столбцам + опция вставки «Транспонировать»)

Это простой ручной способ для единичных случаев, в котором используются только стандартные инструменты Excel.

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

Шаги:

Шаг1. Выделите ячейку.

Шаг2. Выберите Данные > Текст по столбцам.

doc-method-1-select-text-to-column

Шаг3. В мастере преобразования текста в столбцы

  1. Выберите «С разделителями», нажмите «Далее».

    doc-method-1-text-to-column-select-delimited

  2. Укажите «Запятая», нажмите «Далее».

    doc-method-1-text-to-column-select-comma

  3. Выберите целевую ячейку, нажмите «Завершить».

    doc-method-1-text-to-column-select-destination-cell

Теперь содержимое ячейки разбито запятой по нескольким столбцам.

doc-method-1-text-to-column

Шаг4. Выберите разделённые ячейки и нажмите Ctrl + C для копирования. Кликните правой кнопкой мыши по целевой ячейке, в опциях вставки выберите значок «Транспонировать».

doc-method-1-transpose-paste

Содержимое вставляется в строки транспонировано.

doc-method-1-transpose-paste-result

Преимущества Недостатки
  • Установка не требуется.
  • Просто для понимания.
  • Не обновляется автоматически.
  • Ручной процесс, не масштабируется.

Метод2: Функция TRANSPOS(TEXTSPLIT) – (Microsoft365)

Этот способ сочетает функцию TEXTSPLIT (разделение текста по разделителю) с TRANSPOSE и мгновенно отображает разделённые значения вертикально в строках.

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

Шаги:

Шаг1. Введите формулу в пустую ячейку, откуда должен начаться вертикальный список.

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

В таком случае формула будет

=TRANSPOSE(TEXTSPLIT(A1,","))</div)

Совет: Вы можете заменить разделитель «,» на «;» или «|» в зависимости от структуры ваших данных.

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

doc-method-2-use-transpose-split-function

Преимущества Недостатки
  • Полностью динамично: результат обновляется при изменении исходных данных.
  • Не требует плагинов или ручных действий.
  • Работает только в версиях, поддерживающих TEXTSPLIT и динамические массивы (Excel365).

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

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

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

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

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

an 

Шаг2. В окне создания таблицы нажмите OK. (Если выделенный диапазон содержит заголовки, отметьте опцию «Таблица с заголовками».)

doc-method-3-convert-to-table

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

doc-method-3-select-delimiter

Шаг4. Выберите либо введите нужный разделитель, затем нажмите «Дополнительно», раскройте опции, выберите «Разделить на строки», нажмите OK.

doc-method-3-select-delimiter-and-rows

Шаг5. Выберите Закрыть и загрузить > Закрыть и загрузить или Закрыть и загрузить в.

doc-method-3-load

  • Закрыть и загрузить

    Назначение: Загружает результат запроса в стандартное место.

    doc-method-3-close-load

  • Закрыть и загрузить в...

    Назначение: Позволяет выбрать, куда и как разместить данные. Оптимально для регулярных отчетов или больших массивов.

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

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

doc-method-3-scenario-2-target-cells

Результат:

result

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

Преимущества Недостатки
  • Полностью автоматизировано.
  • Подходит для больших объемов данных.
  • Необходимы базовые знания работы с инструментом.
  • Для загрузки новых данных требуется ручное обновление..

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

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

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

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

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

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

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

Шаг3. Выберите «Разделить на строки» и укажите разделитель. Нажмите OK.

doc-method-4-kutools-select-rows-delimiter

Шаг4. Выберите целевую ячейку. Нажмите OK.

doc-method-4-kutools-select-destination

doc-method-4-kutools-result

Скачать сейчас

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

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

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

doc-method-4-kutools-split-data-to-rows

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

doc-method-4-kutools-split-data-to-rows-select-delimiter

doc-method-4-kutools-split-data-to-rows-result

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

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

doc-method-3-scenario-2-target-cells

Результат:

result

Преимущества Недостатки
  • Очень простое использование.
  • Обрабатывает большие объемы за несколько кликов.
  • Требуется плагин (не все функции бесплатны).
Скачать сейчас

Примечание: Пробная версия на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

doc-method-5-split-to-rows-simple

Сценарий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

doc-method-5-split-to-rows-complex

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

Недостатки
  • Файл необходимо сохранять в формате с поддержкой макросов (.xlsm) для дальнейшего использования скрипта.

💡Советы:

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

Распространённые проблемы

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

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

Лучшие инструменты для повышения продуктивности в Office

🤖 Kutools AI Aide: Совершенно новый подход к анализу данных благодаря: Интеллектуальное выполнение |  Генерация кода  |  Создание пользовательских формул |  Анализ данных и построение диаграмм  |  Вызов Kutools Functions…
Популярные функции: Поиск, выделение или отметка дубликатов | Удалить пустые строки | Объединить столбцы или адреса без потери данных | Округлить ...
Супер ПОИСК: VLOOKUP по нескольким критериям | VLOOKUP по нескольким значениям | Многолистовой поиск | Распознавание нечетких соответствий ...
Расширенный раскрывающийся список: Быстро создать раскрывающийся список | Зависимый раскрывающийся список | Множественный выбор в раскрывающемся списке ...
Менеджер столбцов: Добавить определённое количество столбцов | Переместить столбцы | Переключить видимость скрытых столбцов | Сравнить диапазоны и столбцы ...
Рекомендуемые функции: Сетка фокусировки | Дизайн листа | Улучшенная строка формулы | Управление книгой и листами | Библиотека автотекста | Выбор даты | Объединить данные | Зашифровать/расшифровать ячейки | Отправить письмо по списку | Супер фильтр | Специальный фильтр (фильтр жирный/курсив/зачеркнутый...) ...
Топ15 наборов инструментов:12 текстовых инструментов (Добавить текст, Удалить определенные символы, ...) |50+ типов диаграмм (Диаграмма Ганта, ...) |40+ полезных формул (Расчет возраста на основе даты рождения, ...) |19 инструментов для вставки (Вставить QR-код, Вставить изображение по пути, ...) |12 инструментов преобразования (Преобразовать в слова, Конвертация валюты, ...) |7 инструментов объединения и разделения (Расширенное объединение строк, Разделить ячейки, ...) | ... и многое другое
Используйте Kutools на предпочитаемом вами языке — поддерживает Английский, Испанский, Немецкий, Французский, Китайский и более40 других языков!

Повысьте свои навыки работы в 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 Tabs PowerPoint
  • Комплексный набор — надстройки для Excel, Word, Outlook и PowerPoint плюс Office Tab Pro
  • Один установщик, одна лицензия — настройка занимает считанные минуты (MSI-совместимо)
  • Совместная работа — максимальная эффективность между приложениями Office
  • 30-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
  • Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек