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

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

Разделение данных по разделителю и перестройка их в столбец

• Используя формулу (Excel 365)
• Используя Kutools для Excel
• Используя код VBA

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

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

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

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

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

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

1. Нажмите Kutools > Объединить и Разделить > Разделить данные на строки, см. скриншот:
   
2. В окне Разделить данные на строки диалогового окна укажите следующие операции:
   • Выберите ячейки, которые вы хотите разделить;
   • В разделе Разделитель выберите Другой, затем введите ваш разделитель (например, запятую и пробел), чтобы определить, как должно быть разделено содержимое ячейки;
   • Наконец, нажмите OK кнопку.
     

Результат: Выбранные ячейки разделяются на столбец по указанным разделителям.

Разделение данных по разделителю и перестройка их в столбец с помощью кода 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 клавишу для запуска кода, появится диалоговое окно для выбора диапазона для разделения и транспонирования.

4. Нажмите OK. Появится диалоговое окно для выбора места размещения данных.

5. Нажмите OKи выбор будет разделен и транспонирован по запятой.

🔚 Заключение

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

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

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