Как разделить значения ячеек по вертикали в Excel?
При работе в Excel вы можете столкнуться с ситуациями, когда несколько значений в одной строке или нескольких строках необходимо преобразовать в вертикальный список. Это особенно полезно, когда данные хранятся в горизонтальном формате, но их нужно обрабатывать или анализировать в виде столбца.
В этом руководстве рассматриваются три эффективных метода преобразования значений ячеек по вертикали:
Разделение значений ячеек по строкам вертикально в Excel
- Использование формулы (Excel 365)– Лучший вариант для динамических и автоматических преобразований
- Использование Kutools для Excel– Быстрое и простое решение с помощью специализированного инструмента для всех версий Excel
- Использование кода VBA– Идеально подходит для автоматизации процесса во всех версиях Excel
Разделение значений ячеек по строкам вертикально с помощью формулы (Excel 365)
Если у вас установлен Excel 365, вы можете легко преобразовывать значения ячеек в вертикальный формат без ручного вмешательства с помощью функций TEXTSPLIT, TEXTJOIN и TRANSPOSE.
Скопируйте приведённую ниже формулу, вставьте её в пустую ячейку и нажмите клавишу «Enter». Excel автоматически распределит данные по нескольким строкам — см. скриншот:
=TRANSPOSE(TEXTSPLIT(TEXTJOIN(";", TRUE, A1:A4), ";"))
- TEXTJOIN(";"; ИСТИНА; A1:A4): объединяет текст из ячеек A1–A4 в одну строку, разделяя элементы точкой с запятой (;).
- TEXTSPLIT(…): разделяет объединённую строку на отдельные значения, используя точку с запятой в качестве разделителя.
- TRANSPOSE(…): Converts the resulting vertical list of values into a horizontal row (or vice versa, depending on the original orientation).
✅Преимущества:
- ✔ Динамическое обновление: если исходные данные изменятся, формула автоматически обновит результаты.
- ✔ Отсутствие ручных действий: избавляет от необходимости повторно разделять данные вручную.
- ✔ Настраиваемые разделители: замените точку с запятой (;) на любой другой разделитель — например, запятую или пробел — в соответствии с форматом ваших данных.
❌Недостатки:
- ✘ Доступно исключительно в Excel 365 и не поддерживается в более ранних версиях Excel.
Разделение значений ячеек по строкам вертикально с помощью Kutools для Excel
Если вы работаете в Excel 2019 или более ранней версии, воспользуйтесь надстройкой «Kutools для Excel» — мощным инструментом с продуманными функциями для удобного преобразования и управления данными. С помощью опции «Разделить на несколько строк» вы без труда разобьёте содержимое ячеек по вертикали, используя любой нужный вам разделитель.
После установки «Kutools для Excel» выполните следующие действия:
- Нажмите «Kutools» → «Объединить и разделить» → «Разделить на несколько строк» (см. скриншот):
- В диалоговом окне «Разделить на несколько строк» укажите следующие параметры:
- Выделите ячейки, которые необходимо разделить;
- В разделе «Разделитель» выберите нужный разделитель, чтобы задать способ разделения содержимого ячеек. В данном случае я выбираю «точку с запятой».
- Наконец, нажмите кнопку «ОК».
Результат: данные преобразуются в вертикальный столбец.
✅Преимущества:
- ✔ Работает во всех версиях Excel (2010, 2013, 2016, 2019, 2021, 365)
- ✔ Не требует сложных формул или навыков программирования.
- ✔ Поддерживает широкий спектр разделителей.
- ✔ Kutools предлагает более 300 инструментов для работы с данными, освобождая ваше время для других задач.
❌Недостатки:
- ✘ Требуется установка Kutools
Разделение значений ячеек по строкам вертикально с помощью кода VBA
Для продвинутых пользователей VBA предлагает надёжное решение для автоматизации вертикального разделения значений ячеек. Приведённый ниже код VBA позволяет легко разделять содержимое ячеек по строкам с использованием любого заданного разделителя.
1. Нажмите и удерживайте клавиши «ALT» + «F11», чтобы открыть окно «Microsoft Visual Basic для приложений».
2. Выберите «Вставка» > «Модуль» и вставьте приведённый ниже код в открывшееся окно модуля.
Код VBA: разделение значений ячеек по строкам вертикально с помощью указанного разделителя
Sub splitvertically()
'updatebyExtendoffice
Dim xRg As Range
Dim xOutRg As Range
Dim xCell As Range
Dim xTxt As String
Dim xStr As String
Dim xOutArr As Variant
On Error Resume Next
xTxt = ActiveWindow.RangeSelection.Address
Set xRg = Application.InputBox("please select the data range:", "Kutools for Excel", xTxt, , , , , 8)
If xRg Is Nothing Then Exit Sub
Set xOutRg = Application.InputBox("please select output cell:", "Kutools for Excel", , , , , , 8)
If xOutRg Is Nothing Then Exit Sub
For Each xCell In xRg
If xStr = "" Then
xStr = xCell.Value
Else
xStr = xStr & ";" & xCell.Value
End If
Next
xOutArr = VBA.Split(xStr, ";")
xOutRg.Range("A1").Resize(UBound(xOutArr) + 1, 1) = Application.WorksheetFunction.Transpose(xOutArr)
End Sub
3. Затем нажмите клавишу «F5», чтобы запустить код. Появится диалоговое окно с запросом выбрать ячейки для вертикального разделения (см. скриншот):
4. После этого нажмите кнопку «ОК» — появится ещё одно окно с запросом выбрать ячейку для размещения результата (см. скриншот):
5. Нажмите кнопку «ОК», и значения ячеек будут разделены по списку с использованием указанного разделителя (см. скриншот):
✅Преимущества:
- ✔ Автоматизирует процесс и экономит время
- ✔ Работает во всех версиях Excel
- ✔ Можно настроить под различные разделители
❌Недостатки:
- ✘ Требует базовых знаний программирования на VBA
- ✘ Немного сложно для новичков
Преобразовать значения ячеек по вертикали в Excel можно разными способами — у каждого из них свои преимущества. Ознакомившись с этими методами, вы легко подберёте оптимальный для своих задач и значительно ускорите работу в Excel. Независимо от того, новичок вы или опытный пользователь, эти инструменты и приёмы помогут вам работать с данными эффективнее и продуктивнее. Хотите освоить ещё больше полезных фишек и приёмов в 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-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек