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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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. На экране появится диалоговое окно, в котором вы сможете выбрать список столбцов, который хотите преобразовать. См. скриншот:

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

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

Заключение:

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

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

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

• Преобразование названия месяца в число в Excel
• Преобразование военного времени в стандартное время
• Преобразование одной ячейки в несколько ячеек/строк в Excel