Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Сортировка многоуровнего списка в PQ
 
#1
01.07.2019 23:47:36
Здравствуйте!

Подскажите, пожалуйста, как в PQ отсортировать многоуровневый список (в текстовом формате):
Дано:         Нужно:
1.1
1.2
1.2.1
1.2.1.1
1.3
10
2
2.1		1.1
1.2
1.2.1
1.2.1.1
1.3
2
2.1
10
 
 
 
#2
02.07.2019 00:11:14
Делаем столбец с формулой типа:
Код
=Number.From( Text.BeforeDelimeter([Column1], "." ) & "." & Text.Replace(Text.AfterDelimeter([Column1], "."), ".", "") )

И по нему сортируем.
Писал с телефона, код не проверял, возможно что объединять надо через Text.Combine, а не амперсандами. Но думаю что идея понятна.
З.Ы. не, если уровни кроме первого также могут быть больше 9 то надо делить текст на столбцы то такой вариант не катит.
Тут надо делить на столбцы предварительно посчитав какое может быть максимальное количество разделителей. И эти столбцы уже сортировать. В общем-то тоже ничего сложного, но с телефона такое не напишу.
Изменено: PooHkrd - 02.07.2019 00:21:43
Вот горшок пустой, он предмет простой...
 
 
 
#3
02.07.2019 00:21:24
Цитата
PooHkrd написал:
думаю что идея понятна.
Привет, Алексей.
Не очень. Что если будет 1.2.1 и 1.10.1?
По идее,
1. Разбить в список по точке
2. Каждый элемент дополнить слева, допустим, до восьми символов общей длины элемента нулями
3. Собрать в строку и тогда уже сортировать.
Изменено: Андрей VG - 02.07.2019 00:26:18
 
 
 
#4
02.07.2019 00:23:15
Андрей VG, уже сам допер. Все равно не сложная задача, если вы конечно предложите нечто отличное от разделения столбцов, то как всегда будет интересно посмотреть на ход вашей мысли.
Вот горшок пустой, он предмет простой...
 
 
 
#5
02.07.2019 04:22:12
здравствуйте
Цитата
Андрей VG написал:
1. Разбить в список по точке
2. Каждый элемент дополнить слева, допустим, до восьми символов общей длины элемента нулями
3. Собрать в строку и тогда уже сортировать.
не могли бы на примере показать
 
 
 
#6
02.07.2019 06:33:37
Цитата
artyrH написал:
не могли бы на примере показать
Почему бы и нет, пока пьёшь утренний кофе :)
 
 
 
#7
02.07.2019 07:03:17
Цитата
Андрей VG написал: Почему бы и нет, пока пьёшь утренний кофе
Доброе утро, Андрей! А за чашкой чая можно и мышкой все сделать (для 5 6 уровней) :)
Изменено: ZVI - 02.07.2019 10:16:25 (Исправил на 6 уровней, по #9 - спасибо PooHkrd! )
 
 
 
#8
02.07.2019 08:07:58
Андрей VG, ZVI, спасибо
 
 
 
#9
02.07.2019 09:20:30
ZVI, делите на 5 столбцов, а в примере есть строки, которые надо делить на 6.  ;)
Слегка модифицировал ваш код, теперь он динамически определяет количество столбцов, на которые нужно делить:
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    // На всякмй случай - все текстовые
    #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"Дано", type text}}),
    // Определяем количество столбцов, на которые нужно делить столбец
    КоличествоСтолбцов = List.Max( Table.AddColumn(#"Измененный тип", "длина", each Text.Length( Text.Select([Дано],".") ) )[длина] ) + 1,
    // Разделитель - точка
    #"Разделить столбец по разделителю" = Table.SplitColumn(#"Измененный тип", "Дано", Splitter.SplitTextByDelimiter(".",  QuoteStyle.Csv), List.Transform({1..КоличествоСтолбцов}, each "Дано." & Text.From(_) ) ),
    // Преобразовать в целые числа
    #"Измененный тип1" = Table.TransformColumnTypes(#"Разделить столбец по разделителю", List.Transform({1..КоличествоСтолбцов}, each {"Дано." & Text.From(_), Int64.Type} ) ),
    // Сортировать A-Z
    #"Сортированные строки" = Table.Sort(#"Измененный тип1", List.Transform({1..КоличествоСтолбцов}, each {"Дано." & Text.From(_), Order.Ascending} )),
    // Объединить с пробелами
    #"Объединенные столбцы" = Table.CombineColumns(Table.TransformColumnTypes(#"Сортированные строки", List.Transform({1..КоличествоСтолбцов}, each {"Дано." & Text.From(_), type text} ), "ru-RU"), List.Transform({1..КоличествоСтолбцов}, each "Дано." & Text.From(_) ), Combiner.CombineTextByDelimiter(" ", QuoteStyle.None), "Сведено"),
    // Удалить конечные пробелы
    #"Обрезанный текст" = Table.TransformColumns(#"Объединенные столбцы",{{"Сведено", Text.Trim, type text}}),
    // Заменить пробелы точками
    #"Замененное значение" = Table.ReplaceValue(#"Обрезанный текст"," ",".",Replacer.ReplaceText,{"Сведено"})
in
    #"Замененное значение"

Собственно про него я и писал в своем посте №4.
Изменено: PooHkrd - 02.07.2019 09:22:26
Вот горшок пустой, он предмет простой...
 
 
 
#10
02.07.2019 09:47:47
Цитата
PooHkrd написал: ...теперь он динамически определяет количество столбцов...
У меня в своем примере было 5 уровней, а потом скопировал новые данные и не посмотрел, сколько там в таблице полей оказалось. С динамическим определением у Вас красиво получилось, спасибо!
Можно, конечно, и упростить, задав с запасом:  КоличествоСтолбцов = 10,
Изменено: ZVI - 02.07.2019 10:05:12 (На всякий случай приложил обновление)
 
 
 
#11
02.07.2019 13:11:27
ZVI, Владимир, правильно ли я понимаю, что в полку Power Query прибыло? Жду не дождусь ваших козырных решений и в этой области :)
 
 
 
#12
02.07.2019 13:16:52
Цитата
ZVI написал:
Можно, конечно, и упростить, задав с запасом
Было дело - делал с запасами, но жизнь не стоит на месте, и случается так что запаса не хватает. Причем случается именно тогда, когда вот ну вообще времени нет на разгребание причин, чего там и почему не работает. Потому я за динамику - и голова не болит.
Поддерживаю Андрея, ваши решения на VBA и познания в особенностях работы офиса - уникальны, надеюсь на пополнение своей копилки вашими решениями на базе PQ.
Вот горшок пустой, он предмет простой...
 
 
 
#13
02.07.2019 13:25:48
На случай, если список нужно перенумеровать заново:
https://social.technet.microsoft.com/Forums/en-US/0d7620e1-46a3-4b6e-8ccd-b01d86aa9b07/dynamicly-renumber-heirarchal-numerical-titles
 
 
 
#14
02.07.2019 14:28:37
Коллеги, протестировал подходы. Размножил данные из #5 до 989 с копейками тысяч строк. Время обновления
Алексея - 218 сек., Владимира - 233 сек., мой - 55 сек.
Изменено: Андрей VG - 02.07.2019 14:28:51
 
 
 
#15
02.07.2019 16:09:41
Цитата
Андрей VG написал: ... протестировал подходы ... 55 сек
Интересные результаты, спасибо. Средствами VBA+Excel получилось бы значительно быстрее, но на практических данных время выполнения не критично.
Насчет PQ-рить - на форуме изящные решения приводятся, буду изучать потихоньку.
 
 
 
#16
02.07.2019 16:57:37
Цитата
ZVI написал:
VBA+Excel получилось бы значительно быстрее,
Ну, с этим не поспоришь. Как только чуть в сторону от того, что может предугадать анализатор/оптимизатор, Power Query начинает тормозить... Версия на VBA отработала на том же объёме за 12 секунд.
Скрытый текст
 
 
 
#17
02.07.2019 22:05:24
Всем спасибо за помощь!
Особенно динамические столбцы крутая подсказка, т.к. у меня уровень вложенных номеров может меняться.
 
 
 
#18
03.07.2019 00:13:27
Доброго времени суток
Цитата
Андрей VG написал:
Время обновления Алексея - 218 сек., Владимира - 233 сек., мой - 55 сек.
А как насчет такого варианта?
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    GetBin = Table.AddColumn(Source, "a",(_)=>#binary(Expression.Evaluate(Text.Replace("({"&[Дано]&"})",".",",")))),
    Sorted = Table.RemoveColumns(Table.Sort(GetBin,{{"a", Order.Ascending}}),{"a"})
in
    Sorted
или, может, лучше так
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    GetHex = Table.AddColumn(Source, "a",(_)=>Binary.ToText(#binary(Expression.Evaluate(Text.Replace("({"&[Дано]&"})",".",","))),1)),
    Sorted = Table.RemoveColumns(Table.Sort(GetHex,{{"a", Order.Ascending}}),{"a"})
in
    Sorted
 
 
 
#19
03.07.2019 08:36:30
Цитата
Андрей Лящук написал:
А как насчет такого варианта? или, может, лучше так
По или варианту 49 секунд. До VBA ещё далеко :)
 
 
 
#20
04.07.2019 14:04:44
Принципиально не смотрел вариант Андрей VG, пока не сделаю свой :)

Пойду посмотрю :)
F1 творит чудеса
 
 
 
#21
04.07.2019 14:15:06
Андрей Лящук, кстати, хорошая идея насчёт #binary. А то я замаялся свой сравниватель писать :) Как-то это мимо меня пробегало всегда.

Но тогда уж проще так, нет?
Код
// Таблица2 (2)
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица2"]}[Content],
    splitter = (text as text, delimiter as text)  => #binary(List.Transform(Text.Split(text, delimiter), Number.From)),
    Sorted = Table.Sort(Source,(x, y)=>Value.Compare(splitter(x[Столбец1], "."), splitter(y[Столбец1], ".")))
in
    Sorted


ЗЫ не, не проще
Изменено: Максим Зеленский - 04.07.2019 15:09:34
F1 творит чудеса
 
 
 
#22
04.07.2019 14:45:10
Максим Зеленский, или может кто еще, а вот каким таким образом в Table.Sort вместо второго аргумента со списком списков из столбцов и преобразований типа {{"a", Order.Ascending}} можно вот такое вот написать:
Код
(x, y)=>Value.Compare(splitter(x[Столбец1], "."), splitter(y[Столбец1], "."))

пару раз такое видел у умных людей, но моего опыта пока не хватает для понимания. Можно как-то на пальцах разобрать механизм как оно работает? Или статейку какую на буржуйском, где такое разбирается?
Вот горшок пустой, он предмет простой...
 
 
 
#23
04.07.2019 14:59:32
PooHkrd, я вот тут когда-то баловался.
F1 творит чудеса
 
 
 
#24
04.07.2019 15:16:10
Цитата
Максим Зеленский написал:
Но тогда уж проще так, нет?
Максим, так хуже будет. Если у тёзки N преобразований в список,  то у вас N * Log(N, 2).
Для сравнения на другом железе у тёзки 64 секунды, у вас 73 секунды.
 
 
 
#25
04.07.2019 15:17:02
Максим Зеленский, спасибо за ссылку. Такое надо переварить. C List.Sort как-то понятнее, а со столбцами там что-то слишком мудрено.
Вот горшок пустой, он предмет простой...
 
 
 
#26
04.07.2019 15:37:54
Андрей VG, да, я сам уже понял. мне было интересно сравнить, на самом деле, что быстрее в список преобразовывает, мой сплиттер или Expression.Evaluate. Далее уже технически можно сделать так же через столбец.
F1 творит чудеса
 
 
 
Страницы: 1
Наверх