Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Распределение данных из строки по столбцам
 
#1
02.06.2022 23:31:16
Здравствуйте. Есть ли способ распределения данных из строк по столбцам так, чтобы значение до ":" стало названием столбца, а значение после ":" осталось значением  в ячейке? Ну а символ "|" является разделителем столбцов.



В итоге должно получиться столбец Размеры (со своими значениями), столбец Тип (со своими значениями) и т.д.
 
 
 
#2
03.06.2022 06:20:24
Дмитрий,
здравствуйте, предполагаю, что можно с помощью Power Query, но нужен небольшой файл-пример, а не скрин
 
 
 
#3
03.06.2022 13:53:04
Прикрепил файл с примером. Синяя таблица - источник, оранжевая таблица - часть от желаемого результата.
 
 
 
#4
03.06.2022 14:11:34
Дмитрий,
если заранее известны названия всех столбцов, то в тупую можно вот так попробовать:

Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Разделить столбец по разделителю" = Table.SplitColumn(Источник, "product_attribute", Splitter.SplitTextByEachDelimiter({"Размеры:"}, QuoteStyle.Csv, false), {"product_attribute.1", "product_attribute.2"}),
    #"Разделить столбец по разделителю1" = Table.SplitColumn(#"Разделить столбец по разделителю", "product_attribute.2", Splitter.SplitTextByEachDelimiter({"|"}, QuoteStyle.Csv, false), {"product_attribute.2.1", "product_attribute.2.2"}),
    #"Другие удаленные столбцы" = Table.SelectColumns(#"Разделить столбец по разделителю1",{"product_id", "product_attribute.2.1"}),
    #"Переименованные столбцы" = Table.RenameColumns(#"Другие удаленные столбцы",{{"product_attribute.2.1", "Размеры"}})
in
    #"Переименованные столбцы"
 
 
 
#5
03.06.2022 14:33:20
Или так:
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица14"]}[Content],
    #"Разделить столбец по разделителю" = Table.ExpandListColumn(Table.TransformColumns(Источник, {{"product_attribute", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "product_attribute"),
    #"Вставленный текст перед разделителем" = Table.AddColumn(#"Разделить столбец по разделителю", "Текст перед разделителем", each Text.BeforeDelimiter([product_attribute], ":"), type text),
    #"Извлеченный текст после разделителя" = Table.TransformColumns(#"Вставленный текст перед разделителем", {{"product_attribute", each Text.AfterDelimiter(_, ":"), type text}}),
    #"Сведенный столбец" = Table.Pivot(#"Извлеченный текст после разделителя", List.Distinct(#"Извлеченный текст после разделителя"[#"Текст перед разделителем"]), "Текст перед разделителем", "product_attribute")
in
    #"Сведенный столбец"
 
 
 
#6
03.06.2022 14:36:11
Код
Sub SplitStrToTabl()
  Dim a, b, c, d, i&, k$, r&, tm
  tm = Timer: a = [a1].CurrentRegion: c = Split([b2], "|")
  ReDim b(1 To UBound(a), 1 To UBound(c) + 2)
  Set d = CreateObject("Scripting.Dictionary"): d("product_id") = 1
  For r = 2 To UBound(a)
    c = Split(a(r, 2), "|"):  b(r, d("product_id")) = a(r, 1)
    For i = 0 To UBound(c)
     k = Left(c(i), InStr(c(i), ":") - 1)
     If Not d.exists(k) Then d(k) = d.Count + 1
     If UBound(b, 2) < d(k) Then ReDim Preserve b(1 To UBound(a), 1 To d(k))
     b(r, d(k)) = Right(c(i), Len(c(i)) - Len(k) - 1)
    Next
  Next
  c = d.keys: For i = 0 To UBound(c):  b(1, i + 1) = c(i): Next
  [d1].CurrentRegion.ClearContents: [d1].Resize(UBound(b), UBound(b, 2)) = b
  MsgBox UBound(b) - 1 & "  товаров" & vbLf & "разнесены в  " & _
  UBound(b, 2) & "  колонок", , "Готово!   (за  " & Timer - tm & "  сек)"
End Sub
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
#7
03.06.2022 14:36:44
Спасибо. Попробую так. Думаю подойтет если надо выборку по нескольким атрибутам сделать, а вот если атрибутов будет несколько десятков, то времени все их перечислить уйдет много.
 
 
 
#8
03.06.2022 14:43:45
Цитата
написал:
Код
    [URL=#]?[/URL]       1  2  3  4  5  6  7  8  9  10  11  12  13  14  15  16  17  18  19      Sub   SplitStrToTabl()        Dim   a, b, c, d, i&, k$, r&, tm        tm = Timer: a = [a1].CurrentRegion: c = Split([b2],   "|"  )        ReDim   b(1   To   UBound(a), 1   To   UBound(c) + 2)        Set   d = CreateObject(  "Scripting.Dictionary"  ): d(  "product_id"  ) = 1        For   r = 2   To   UBound(a)          c = Split(a(r, 2),   "|"  ):  b(r, d(  "product_id"  )) = a(r, 1)          For   i = 0   To   UBound(c)           k = Left(c(i), InStr(c(i),   ":"  ) - 1)           If   Not   d.exists(k)   Then   d(k) = d.Count + 1           If   UBound(b, 2) < d(k)   Then   ReDim   Preserve   b(1   To   UBound(a), 1   To   d(k))           b(r, d(k)) = Right(c(i), Len(c(i)) - Len(k) - 1)          Next        Next        c = d.keys:   For   i = 0   To   UBound(c):  b(1, i + 1) = c(i):   Next        [d1].CurrentRegion.ClearContents: [d1].Resize(UBound(b), UBound(b, 2)) = b        MsgBox UBound(b) - 1 &   "  товаров"   & vbLf &   "разнесены в  "   & _        UBound(b, 2) &   "  колонок"  , ,   "Готово!   (за  "   & Timer - tm &   "  сек)"    End   Sub  
 
Огромаднейшее спасибо :excl:  :)  
 
 
 
#9
24.11.2022 19:10:22
Макрос хороший и пользоваться им удобней0 создав надстройку , но выбивает ошибку из-за  большого числа  элементов в ячейке. Исходный файл для теста был 12 000 строк . в итоге разбить пришлось  на несколько файлов . В итоге был кусочек где  даже 5 строк  вызывало ошибку.

Самый универсальный вариант от  
Msi2102.
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица14"]}[Content],
    #"Разделить столбец по разделителю" = Table.ExpandListColumn(Table.TransformColumns(Источник, {{"product_attribute", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "product_attribute"),
    #"Вставленный текст перед разделителем" = Table.AddColumn(#"Разделить столбец по разделителю", "Текст перед разделителем", each Text.BeforeDelimiter([product_attribute], ":"), type text),
    #"Извлеченный текст после разделителя" = Table.TransformColumns(#"Вставленный текст перед разделителем", {{"product_attribute", each Text.AfterDelimiter(_, ":"), type text}}),
    #"Сведенный столбец" = Table.Pivot(#"Извлеченный текст после разделителя", List.Distinct(#"Извлеченный текст после разделителя"[#"Текст перед разделителем"]), "Текст перед разделителем", "product_attribute")
in
    #"Сведенный столбец"



Оптимально будет - сначала можно пробовать vba и если не работает, то выполнить запрос через Power Query ( у меня и 1 млн строк  отработало)
Изменено: Кирилл Ян - 24.11.2022 19:17:26
 
 
 
Страницы: 1
Наверх