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

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

Как преобразовать список столбцов в список, разделенный запятыми, в Excel?

Author Sun Last modified

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

Преобразование списка столбцов в список, разделенный запятыми, с помощью формул

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

Преобразование списка столбцов в список, разделенный запятыми, с помощью кода VBA

convert a column list to a comma-separated list

Преобразование списка столбцов в список, разделенный запятыми, с помощью формул

В Excel нет встроенной функции для преобразования столбца в список через запятую, но вы можете легко это сделать: используйте TEXTJOIN в Office 365/2019+, или CONCAT+IF в любой версии для оптимизации ваших данных.

Формула 1: функция TEXTJOIN (Excel 2019 и более поздние версии, Office 365)

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

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

=TEXTJOIN(", ", TRUE, A2:A10)

2. Нажмите Enter, чтобы получить значение, разделенное запятыми.

convert a column list to a comma-separated list with textjoin function

Объяснение этой формулы:
  • ", " — это разделитель (запятая плюс пробел).
  • TRUE указывает Excel пропускать пустые ячейки.
  • A2:A10 — это исходный диапазон.
Pro Tip: Если вам нужны только уникальные элементы, вы можете использовать функцию UNIQUE: 
=TEXTJOIN(", ", TRUE, UNIQUE(A2:A10))
Преимущества:
  • 🔹Простота реализации: требуется только одна функция.
  • 🔹Гибкий разделитель: можно использовать ", " (запятая + пробел) или просто "," по необходимости.
  • 🔹Автоматическая обработка: пропускает пустые ячейки, когда установлено TRUE для ignore_empty.
Недостатки:
  • 🔸Требуется Excel 2019 и более поздние версии / Office 365.

Формула 2: функции CONCAT и IF (все версии Excel)

До появления TEXTJOIN можно было комбинировать CONCAT с функцией IF для создания списка, а затем удалять начальный разделитель.

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

=CONCAT(IF(A2:A10<>"", A2:A10 & ", ", ""))

2. Нажмите клавиши Ctrl + Shift + Enter вместе, чтобы получить результат.

convert a column list to a comma-separated list with CONCAT function

Объяснение этой формулы:
  • IF(A2:A100<>"", A2:A100 & "，", ""): Проверяет каждую ячейку в диапазоне A2:A100. Если ячейка не пустая, возвращает её значение плюс запятую; в противном случае возвращает пустую строку.
  • CONCAT(...): Объединяет все элементы массива в одну непрерывную строку.
Преимущества:
  • 🔹Широкая совместимость: работает в любой версии Excel, поддерживающей формулы массива, без использования новых функций.
  • 🔹Игнорирует пустые ячейки: проверка IF автоматически пропускает пустые ячейки.
Недостатки:
  • 🔸Сложность в использовании: должна быть введена как формула массива, требуя нажатия Ctrl + Shift + Enter — что многие новички забывают.
  • 🔸Низкая производительность: расчет может значительно замедлиться или даже зависнуть при обработке больших наборов данных (десятки тысяч строк).
  • 🔸Фиксированный результат: нужно предпринять дополнительные шаги для удаления последней запятой, если вы хотите убрать последний разделитель.

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

Kutools для Excel содержит мощную функцию «Объединить строки/столбцы/ячейки и сохранить значения», которая позволяет объединить содержимое всего столбца в одну ячейку всего за несколько кликов. Выбрав исходный диапазон и указав разделитель — будь то запятая, точка с запятой, пробел или любой другой символ — вы мгновенно получите объединенный, разделенный список без необходимости копирования и вставки каждого элемента вручную. Инструмент также предлагает возможность игнорировать пустые ячейки, сохранять исходное форматирование, что делает его идеальным решением для быстрого преобразования вертикальных данных в аккуратные, разделенные запятыми списки.

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

1. Выберите список столбцов, который вы хотите преобразовать в список, разделенный запятыми, и нажмите Kutools > Объединить и разделить > Объединить строки/столбцы/ячейки и сохранить значения.

click Combine Rows, Columns or Cells without Losing Data feature of kutools

2. В открывшемся диалоговом окне «Объединить столбцы или строки» вам нужно:

  • (1) Установите флажок «Объединить в одну ячейку» в разделе «Объединить выбранный диапазон следующим образом».
  • (2) В разделе «Указать разделитель» выберите опцию «Другой разделитель» и введите запятую ", ".
  • (3.) Выберите, как обработать данные из исходных ячеек. (Вы можете сохранить содержимое исходных ячеек или удалить содержимое из исходных ячеек.)
  • (4.) Укажите выходную ячейку, под разделом «Разместить объединенные значения в», выберите опцию «Другие ячейки» и нажмите, чтобы выбрать ячейку для вывода результата.
  • (5.) Наконец, нажмите кнопку OK.
    set options in the dialog box

Результат будет вставлен в указанную целевую ячейку.

convert a column list to a comma-separated list with kutools

Преимущества:
  • 🔹Очень удобный для пользователя.
  • 🔹Без формул или кода.
  • 🔹Обрабатывает большие диапазоны плавно.
  • 🔹Объединяет строки, столбцы или диапазоны с любым разделителем, сохраняет форматирование и выбирает место вывода.
Недостатки:
  • 🔸Требуется установка.

Преобразование списка столбцов в список, разделенный запятыми, с помощью кода VBA

Альтернативно, если вы предпочитаете автоматизировать задачу или вам нужно работать с большими динамически изменяющимися диапазонами, вы можете написать макрос VBA, который проходит по каждой ячейке в указанном столбце, добавляет её непустое значение, за которым следует запятая (или любой другой разделитель, который вы выбрали), обрезает лишний разделитель в конце и выводит полностью объединенную строку, разделенную запятыми, в целевую ячейку.

1. Удерживайте клавиши Alt и F11 на клавиатуре, чтобы открыть окно Microsoft Visual Basic для приложений.

2. Нажмите Insert > Module и скопируйте VBA в модуль.

VBA: Преобразование списка столбцов в список, разделенный запятыми

Sub ChangeRange()
    ' Updateby Extendoffice
    Dim rng As Range
    Dim InputRng As Range, OutRng As Range
    Dim outStr As String
    Dim xTitleId As String
    xTitleId = "KutoolsforExcel"
    Set InputRng = Application.Selection
    Set InputRng = Application.InputBox("Select source range:", xTitleId, InputRng.Address, Type:=8)
    Set OutRng = Application.InputBox("Output to (single cell):", xTitleId, Type:=8)
    outStr = ""
    For Each rng In InputRng
        If Len(Trim(rng.Value)) > 0 Then
            If outStr = "" Then
                outStr = rng.Value
            Else
                outStr = outStr & ", " & rng.Value
            End If
        End If
    Next rng
    OutRng.Value = outStr
End Sub

3. Нажмите кнопку Run или клавишу F5 для запуска VBA. На экране появится диалоговое окно, и вы сможете выбрать список столбцов, который хотите преобразовать. См. скриншот:

vba code to select the column list you want to convert

4. Нажмите OK, затем появится ещё одно диалоговое окно, чтобы выбрать ячейку. См. скриншот:

vba code to select a cell for putting the result

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

convert a column list to a comma-separated list with vba code

Заключение:

  • Для простоты и производительности используйте TEXTJOIN в Excel 2019/365.
  • Для совместимости метод массива CONCAT + IF работает во всех версиях.
  • Для разовых задач без формул Kutools предоставляет решение с одним кликом.
  • Для полной автоматизации или сложных правил макрос VBA является наиболее гибким.

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

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

Лучшие инструменты для повышения продуктивности в 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-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
  • Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек