Страницы: 1
RSS
Преобразование множества значений из ячейки в строки с повторением соседних столбцов, Преобразование таблицы
 
Добрый день.
Прошу простить за название темы, не очень понимаю как это выразить одним предложением )))

В общем задача следующая:
нужно преобразовать таблицу вида
123   a; b; c; d
321     x; y; z
в таблицу вида
123   a
123   b
123 c
123 d
321 x
321 y
321 z
причем количество значений в ячейках второго столбца заранее неизвестно.
делиметр всегда ";"


Заранее благодарю за предложенные варианты!
Изменено: KDias - 28.09.2022 14:41:20
 
PQ
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Разделить столбец по разделителю" = Table.ExpandListColumn(Table.TransformColumns(Источник, {{"Столбец2", Splitter.SplitTextByDelimiter("; ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Столбец2")
in
    #"Разделить столбец по разделителю"
 
VBA
Код
Sub test()
    myTransform Range("A1:B2"), Workbooks.Add(1).Sheets(1).Cells(1, 1)
End Sub

Sub myTransform(rangeSource As Range, rangeTarget As Range)
    Dim bb As Variant
    Dim urr As Variant
    Dim brr As Variant
    Dim arr As Variant
    arr = rangeSource.Columns("A:B")
    
    Dim yy As Long
    Dim uu As Long
    Dim ii As Long
    
    For ii = 0 To 1
        If ii = 1 Then
            If uu > 0 Then
                ReDim urr(1 To uu, 1 To 2)
                uu = 0
            Else
                Exit For
            End If
        End If
        For yy = 1 To UBound(arr, 1)
            If Not IsEmpty(arr(yy, 2)) Then
                brr = Split(arr(yy, 2), ";")
                For Each bb In brr
                    uu = uu + 1
                    If Not IsEmpty(urr) Then
                        urr(uu, 1) = arr(yy, 1)
                        urr(uu, 2) = Trim(bb)
                    End If
                Next
            End If
        Next
    Next
    
    If IsEmpty(urr) Then
        With rangeTarget.Parent.Parent
            If .Path = "" Then .Close False
        End With
    Else
        rangeTarget.Resize(UBound(urr, 1), UBound(urr, 2)) = urr
    End If
End Sub
 
Формульный вариант.
Первый столбец.
Код
=IF(ROWS(D$1:D1)>SUM(1+LEN(TRIM(SUBSTITUTE($B$1:$B$2&";";"; ";" ")))-LEN(SUBSTITUTE($B$1:$B$2&";";"; ";"")));"";INDEX(A$1:A$2;MATCH(ROWS(D$1:D1)-1;MMULT(--(ROW(A$1:A$2)>TRANSPOSE(ROW(A$1:A$2)));1+LEN(TRIM(SUBSTITUTE($B$1:$B$2&";";"; ";" ")))-LEN(SUBSTITUTE($B$1:$B$2&";";"; ";""))))))

Второй.
Код
=IFERROR(TRIM(MID(SUBSTITUTE(INDEX($B$1:$B$2;MATCH(ROWS(E$1:E1)-1;MMULT(--(ROW($B$1:$B$2)>TRANSPOSE(ROW($B$1:$B$2)));1+LEN(TRIM(SUBSTITUTE($B$1:$B$2&";";"; ";" ")))-LEN(SUBSTITUTE($B$1:$B$2&";";"; ";"")))));"; ";REPT(" ";99));IFERROR(MOD(SMALL(IF(LEN($B$1:$B$2&0)-LEN(SUBSTITUTE($B$1:$B$2;";";))>=COLUMN($A$1:$Z$1);ROW($B$1:$B$2)*100+COLUMN($A$1:$Z$1));ROWS(E$1:E1));100);"")*99-98;99));"")

Обе массивные.
 
memo, что-то перемудрено
=IF(ROWS(D$1:D1)>SUM(1+LEN(B$1:$B$2)-LEN(SUBSTITUTE($B$1:$B$2;";";)));"";INDEX(A$1:A$2;MATCH(ROWS(D$1:D1)-1;MMULT(--(ROW(A$1:A$2)>TRANSPOSE(ROW(A$1:A$2)));1+LEN($B$1:$B$2)-LEN(SUBSTITUTE($B$1:$B$2;";";))))))

вторая аналогично в этой части. +
=IF(D1="";"";TRIM(MID(SUBSTITUTE(";"&INDEX($B$1:$B$2;MATCH(ROWS(E$1:E1)-1;MMULT(--(ROW($B$1:$B$2)>TRANSPOSE(ROW($B$1:$B$2)));1+LEN($B$1:$B$2)-LEN(SUBSTITUTE($B$1:$B$2;";";)))));";";REPT(" ";99));COUNTIF(D$1:D1;D1)*99;99)))
По вопросам из тем форума, личку не читаю.
 
БМВ, Не перемудрено, скорее перестрахованно. К примеру если у букв поставить лишние точки с запятой ваша формула посчитает их и будет ненужный сдвиг.
 
memo, ну тогда надо убирать неразрывный пробел и непечатные символы. По идее разделитель и есть разделитель, онопределяет количество значений.
По вопросам из тем форума, личку не читаю.
 
БМВ, Ну, по идее, кроме точек с запятой там ничего не должно быть, хотя что там будет на практике один автор знает.
Страницы: 1
Наверх