Как разделить данные по разделителю и перестроить их в столбец в Excel?
При работе с Excel часто встречаются ячейки, содержащие несколько значений, разделенных запятыми, точками с запятой или другими разделителями. Если вам нужно разделить диапазон таких ячеек по определенному разделителю и объединить все полученные значения в один столбец, как показано на скриншоте ниже, существуют эффективные методы для достижения этого с целью лучшей организации и анализа данных.
Разделение данных по разделителю и перестройка их в столбец
Разделение данных по разделителю и перестройка их в столбец с помощью формулы (Excel 365)
В Excel 365 мы можем выполнить это преобразование динамически, используя современные функции, такие как TEXTSPLIT и TEXTJOIN.
Нажмите на ячейку, куда вы хотите поместить результат, введите следующую формулу и нажмите клавишу Enter. Ячейки, содержащие текстовые строки, разделенные запятыми, будут преобразованы в список сразу. См. скриншот:
=TRANSPOSE(TEXTSPLIT(TEXTJOIN(", ", TRUE, A2:A5), ", "))
- TEXTJOIN(",", TRUE, A2:A5): Объединяет все значения в A2:A5 в одну текстовую строку, разделенную запятыми.
- TEXTSPLIT(..., ","): Разделяет объединенный текст на отдельные значения там, где есть запятая.
- TRANSPOSE(...): Преобразует результирующую строку значений в вертикальный столбец.
Разделение данных по разделителю и перестройка их в столбец с помощью Kutools для Excel
Для пользователей, работающих с устаревшими версиями Excel (2016/2019 и т.д., где отсутствует функция TEXTSPLIT), функция Разделить данные на строки из Kutools для Excel предлагает идеальное решение. Эта функция позволяет быстро разделить содержимое ячеек в один столбец на основе определенного разделителя, экономя ваше время и усилия.
После установки Kutools для Excel выполните следующие шаги:
- Нажмите Kutools > Merge & Split > Разделить данные на строки, см. скриншот:
- В Разделить данные на строки диалоговом окне укажите следующие операции:
- Выберите ячейки, которые вы хотите разделить;
- В разделе Разделитель выберите Другое, затем введите ваш разделитель (например, запятую и пробел), чтобы определить, как должно быть разделено содержимое ячейки;
- Наконец, нажмите 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 Sub3. Нажмите F5 клавишу для запуска кода, появится диалоговое окно для выбора диапазона для разделения и транспонирования.
4. Нажмите OK. Появится диалоговое окно для выбора места размещения данных.
5. Нажмите OK, и выбор был разделен и транспонирован по запятой.
🔚 Заключение
Все методы эффективно решают основную задачу разделения данных по разделителю, при этом оптимальный выбор зависит от вашего уровня владения техникой и конкретных требований.
- Для большинства пользователей Excel 365 подход с использованием формул обеспечивает наилучший баланс мощности и удобства обслуживания.
- Для частых, сложных преобразований пользователями без навыков программирования Kutools является идеальным решением.
- Для настраиваемых автоматизированных решений в корпоративной среде VBA предоставляет наибольший контроль.
Выберите метод в зависимости от ваших конкретных потребностей. Если вы заинтересованы в изучении дополнительных советов и приемов Excel, наш сайт предлагает тысячи учебников, которые помогут вам овладеть Excel.
Лучшие инструменты для повышения продуктивности в 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-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек