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

Как разделить данные по разделителю и переупорядочить их в столбец в Excel?

Author: Sun Last Modified: 2025-08-07
split data by delimiter and rearrange it into a column

При работе с Excel часто встречаются ячейки, содержащие несколько значений, разделенных запятыми, точками с запятой или другими разделителями. Если вам нужно разделить диапазон таких ячеек по определенному разделителю и объединить все полученные значения в один столбец, как показано на скриншоте ниже, существуют эффективные методы для достижения этого, что улучшит организацию и анализ данных.

Разделение данных по разделителю и переупорядочивание их в столбец


Разделение данных по разделителю и переупорядочивание их в столбец с помощью формулы (Excel 365)

В Excel 365 мы можем выполнить это преобразование динамически, используя современные функции, такие как TEXTSPLIT и TEXTJOIN.

Щелкните ячейку, куда вы хотите поместить результат, введите формулу ниже и нажмите клавишу Enter. Ячейки, содержащие строки текста, разделенные запятыми, преобразуются в список сразу. См. скриншот:

=TRANSPOSE(TEXTSPLIT(TEXTJOIN(", ", TRUE, A2:A5), ", "))
split data by delimiter and rearrange it into a column with formula

 

Объяснение этой формулы:
  • TEXTJOIN(",", TRUE, A2:A5): Объединяет все значения в A2:A5 в одну текстовую строку, разделенную запятыми.
  • TEXTSPLIT(..., ","): Разделяет объединенный текст на отдельные значения везде, где есть запятая.
  • TRANSPOSE(...): Преобразует результирующую строку значений в вертикальный столбец.

Разделение данных по разделителю и переупорядочивание их в столбец с помощью Kutools для Excel

Для пользователей, работающих с устаревшими версиями Excel (2016/2019 и т.д., не имеющими функции TEXTSPLIT), функция «Разделить данные на строки» из Kutools для Excel предлагает идеальное решение. Эта функция позволяет быстро разделить содержимое ячеек в один столбец на основе определенного разделителя, экономя ваше время и усилия.

Kutools для Excel предлагает более 300 продвинутых функций для упрощения сложных задач, повышая креативность и эффективность. Интеграция с возможностями ИИ позволяет Kutools автоматизировать задачи с высокой точностью, делая управление данными без усилий. Подробная информация о Kutools для Excel...  Бесплатная пробная версия...

После установки Kutools для Excel выполните следующие шаги:

  1. Нажмите Kutools > Объединить и разделить > Разделить данные на строки, см. скриншот:
    click Split Data to Rows feature of kutools
  2. В Разделить данные на строки диалоговом окне укажите следующие операции:
    • Выберите ячейки, которые вы хотите разделить;
    • В разделе Разделитель выберите Другой, затем введите свой разделитель (например, запятую и пробел), чтобы определить, как должно разделяться содержимое ячейки;
    • Наконец, нажмите ОК кнопку.
      specify the options in the dialog box

Результат: Выбранные ячейки разделены на столбец по указанным разделителям.
split data by delimiter and rearrange it into a column with kutools

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

Разделение данных по разделителю и переупорядочивание их в столбец с помощью кода VBA

Здесь мы покажем вам, как использовать VBA для разделения содержимого ячеек по разделителю и переупорядочивания результатов в один столбец.

1. Нажмите клавиши Alt + F11 для отображения окна Microsoft Visual Basic for Applications.

2. Нажмите Вставка > Модуль и вставьте приведенный ниже код в новое окно Модуля.

VBA: Разделить и транспонировать

Sub Vertical()
'Updateby Extendoffice
    Dim i As Long, strTxt As String
    Dim startP As Range
    Dim xRg As Range, yRg As Range
    On Error Resume Next
    Set xRg = Application.InputBox _
    (Prompt:="Range Selection...", _
    Title:="Kutools For Excel", Type:=8)
    i = 1
    Application.ScreenUpdating = False
    For Each yRg In xRg
        If i = 1 Then
            strTxt = yRg.Text
            i = 2
        Else
            strTxt = strTxt & "," & yRg.Text
        End If
    Next
    Application.ScreenUpdating = True
    Set startP = Application.InputBox _
    (Prompt:="paste range...", _
    Title:="Kutools For Excel", Type:=8)
    ary = Split(strTxt, ",")
    i = 1
    Application.ScreenUpdating = False
    For Each a In ary
        startP(i, 1).Value = a
        i = i + 1
    Next a
    Application.ScreenUpdating = True
End Sub

3. Нажмите F5 клавишу для запуска кода, появится диалоговое окно для выбора диапазона для разделения и транспонирования.
vba code to select the data list

4. Нажмите ОК. Появится диалоговое окно для выбора места размещения данных.
vba code to select a cell to lit the result

5. Нажмите ОКи выбор был разделен и транспонирован по запятой.
split data by delimiter and rearrange it into a column by vba code

Примечание: В коде запятая в strTxt & "," и ary = Split(strTxt, ",") являются разделителем, по которому вы будете разделять данные, вы можете изменить их по мере необходимости.

🔚 Заключение

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

  • Для большинства пользователей Excel 365 подход с использованием формулы обеспечивает лучший баланс между мощностью и удобством обслуживания.
  • Для частых, сложных преобразований, выполняемых непрограммистами, Kutools является идеальным решением.
  • Для настраиваемых автоматизированных решений в корпоративных средах VBA предоставляет наибольший контроль.

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

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

🤖 Kutools AI Aide: Переворот в анализе данных на основе: Интеллектуальное выполнение   |  Сгенерировать код  |  Создать Пользовательские Формулы  |  Анализ данных и генерация диаграмм  |  Вызов Kutools Functions
Популярные функции: Найти, выделить или отметить дубликаты   |  Удалить пустые строки   |  Объединить столбцы или ячейки без потери данных   |   Округлить...
Супер ПОИСК: VLookup с несколькими критериями    VLookup с несколькими значениями  |   Многолистовой поиск   |   Распознавание нечетких соответствий ....
Расширенный раскрывающийся список: Быстро создать раскр. список   |  Зависимый раскрывающийся список   |  Множественный выбор в раскрывающемся списке ....
Менеджер столбцов: Добавить определенное количество столбцов  |  Переместить столбцы  |  Переключить статус видимости скрытых столбцов  |  Сравнить диапазоны и столбцы ...
Рекомендуемые функции: Сетка фокусировки   |  Дизайн листа   |   Улучшенная строка формулы    Управление книгой и листами   |  Библиотека автотекста (Auto Text)   |  Выбор даты   |  Объединить данные   |  Шифрование/Расшифровка ячеек    Отправить письмо по списку   |  Супер фильтр   |   Специальный фильтр (фильтр по жирному/курсиву/зачеркиванию...) ...
Топ–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% и сократите сотни кликов мышью ежедневно!