Excel - транспонирование на основе формул из значений, разделенных запятыми, в отдельные строки

Question

Я веду инвентаризацию CD/DVD в файле Excel (Excel 2013). Данные организованы, как показано на снимке ниже.

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

Однако меня интересует представление, в котором значения, разделенные запятыми, транспонируются в строки, так что в каждой ячейке и каждой строке есть только один элемент, как показано ниже.

Я понимаю, VBA может помочь здесь легко, и у меня есть базовые знания VBA, однако есть ли возможное решение на основе формул для переноса данных из ввода в вывод? Я хочу избегать макросов, а также избегать ручного процесса преобразования текста в столбец, а затем копирования и вставки.

Следует также обрезать лишнее пространство, если есть после запятой.

Answer 1

Я хотел бы предложить один простой метод VBA (Macro), который очень прост в использовании и лучше, чем любая утомительная формула.

Private Sub CommandButton1_Click()

    Dim xArr() As String
    Dim xAddress As String
    Dim Rg As Range
    Dim Rg1 As Range

    On Error Resume Next

    xAddress = Application.ActiveWindow.RangeSelection.Address

    Set Rg = Application.InputBox("please select the data range:", "Column to Row", xAddress, , , , , 8)

    If Rg Is Nothing Then Exit Sub

    Set Rg = Application.Intersect(Rg, Rg.Parent.UsedRange)

    If Rg Is Nothing Then Exit Sub

    Set Rg1 = Application.InputBox("please select output cell:", "Column to Row", , , , , , 8)

    If Rg1 Is Nothing Then Exit Sub
    xArr = Split(Join(Application.Transpose(Rg.Value), ","), ",")
    Rg1.Resize(UBound(xArr) + 1) = Application.Transpose(xArr)
    Rg1.Parent.Activate
   Rg1.Resize(UBound(xArr) + 1).Select

Call TrimXcessSpaces

End Sub

Как это устроено:

• Нажмите вкладку « Разработчик», нажмите « Дизайн», затем « Вставить».
• Выберите командную кнопку из категории ActiveX и нарисуйте в любом месте на листе.
• Щелкните правой кнопкой мыши кнопку «Command» и выберите « Свойства».
• Найдите заголовок в списке и измените его на « Столбец в строку».
• Закройте окно свойств и вернитесь к листу.
• Дважды нажмите кнопку «Command», и вы попадете в окно VB Editor.
• Скопируйте и вставьте этот код между Private Sub CommandButton1_Click() и End Sub и нажмите Сохранить значок.
• В окне редактора VB нажмите « Файл» и « Закрыть и вернуться в Microsoft Excel».
• Нажмите режим конструктора, чтобы отключить его.

Теперь нажмите кнопку на листе:

• Появится первое поле ввода , затем выберите Список, разделенный запятой в (B4:B6), и завершите с помощью Ok.

• Появится второе поле ввода , выберите любую отдельную ячейку, как я сделал E4, и нажмите ОК.

Вы найдете список в строках, как показано на скриншоте.

NB. При необходимости измените ссылки на ячейки.

Редакция:

Я редактировал пост, так как PeterH предложил мне удалить пустое пространство из вывода в столбце E.

1. Форма листа Нажмите ALT+F11, чтобы открыть редактор VB.
2. Найдите имя листа в Project Explorer Windows слева.
3. Выберите & щелкните правой кнопкой мыши имя листа.
4. Найдите Вставить из меню, затем нажмите команду Модуль .

5. Скопируйте и вставьте этот код и сохраните, чтобы вернуть лист.

    Option Explicit
   
    Sub TrimXcessSpaces()
   
     Dim cl As Variant
   
     For Each cl In Selection
        If Len(cl) > Len(WorksheetFunction.Trim(cl)) Then
            cl.Value = WorksheetFunction.Trim(cl)
        End If
     Next cl
   End Sub
   

Обратите внимание, добавьте этот код непосредственно перед End Sub в предыдущем коде (проверьте код выше).

Call TrimXcessSpaces

6. На листе нажмите кнопку « Command», результат будет выглядеть, как показано ниже.

Answer 2

Чтобы сделать это по формуле, в более старом, чем Office 365 Excel, вам понадобится вспомогательный столбец.

Рядом с вашим списком с разделителями-запятыми (мой список начинается в C2) введите:

=LEN(C1)-LEN(SUBSTITUTE(C1,",",""))+1+D1

Это будет держать промежуточное количество слов.

Затем вы ссылаетесь на оба этих столбца с помощью INDEX/MATCH для ссылки на правильную ячейку, когда длинная формула перетаскивается вниз.

Длинная формула:

=TRIM(MID(SUBSTITUTE(INDEX(C:C,MATCH(ROW(1:1),D:D)),",",REPT(" ",99)),(ROW(1:1)-INDEX(D:D,MATCH(ROW(1:1),D:D)))*99 +1,99))

Затем два INDEX/MATCH возвращают правильные ячейки для обработки.

ЗАМЕНА добавляет много пустого пространства, давая большую цель для Mid.

Середина расщепляет длинную строку в пустом пространстве.

TRIM удаляет посторонние пробелы.

---

В Office 365 вы можете пропустить вспомогательный столбец и использовать TEXTJOIN(), если строка не слишком длинная.

=TRIM(MID(SUBSTITUTE(TEXTJOIN(",",TRUE,$C$2:$C$5),",",REPT(" ",99)),(ROW(1:1)-1)*99+1,99))

Это заменяет INDEX/MATCH на TEXTJOIN, в противном случае он действует как выше.