Страницы: 1
RSS
Поиск одинаковых значений в строках и сортировка строк в порядке убывания
 
Добрый вечер всем, специально зарегистрировалась тут, дабы задать вопрос, в надежде, что тут помогут. Самой не хватает знаний:( Искала в поиске на данном форуме похожий вопрос, но вроде бы прямо такого нету.
В общем, есть определенное количество строк со значениями. Значения вынесены в отдельный столбец(ячейку). Нужно найти строчки с нужными мне повторяющимися значениями и как то вынести их и отсортировать в порядке убывания. Приложу пример. Если что, объясню подробнее... Я не знаю, нужна тут формула или может быть макрос какой нибудь. Или утилита.
 
Мария, очень непросто понять вашу задачу. Поэтому и ответов до сих пор нет.
Вы бы просто указали в своем примере, что дано (исходные данные) и что вы хотите получить (конечный результат).
А все лишнее, включая радугу, лучше бы убрать.
Компьютер никогда не заменит человека (©️ Hannibal Lecter)
 
Радуга просто чтобы указать на строчки с повторяющиеся значения :D  :D   Я не знаю как правильно объяснить. Есть несколько строчек, как в примере. Там 7 строчек. В каждой строчке есть значения вынесенные в отдельный столбец(ячейку). Строчка может быть и из 10 значений и из 20. Не важно. И вот в каждой строчке есть повторяющиеся значения. Я хотела как-то отсортировать эти строчки по кол-ву повторяющихся в них значений. В примере использовала просто английские буквы.
Upd. Прикреплю ещё раз пример, без радуги :D  Как бы не важна сама сортировка. Главное вынести строки с одинаковыми значениями отдельно. Ну если так будет удобнее. Как в примере, в 3 строчках 5 значений повторились. Потом ещё в 2 других строчках повторились так же 5 значений и тд. Затем строчки, где уже 4 значения повторились, затем 3 значения и тд.
Изменено: Мария Гончарова - 22.11.2021 17:01:28
 
, а сколько таких выборок? (s f e g j,  a b c d e, и т.д.)
а в приведеном Вами примере (желаемый результ) почему не содержит строку
fsegj
для (s e)
Код
Sub mrshkei()
Dim arr, i As Long, n As Long, arr2, k As Long, s As Long
arr = Range("A2:E8") ' ВАШ ДИАПАЗОН С ДАННЫМИ *ИСХОДНЫЕ ДАННЫЕ
n = 2 'НОМЕР СТРОКИ С КОТОРОЙ БУДУТ ВНИЗ ВСТАВЛЯТЬСЯ ДАННЫЕ
For x = 1 To 6
    If x = 1 Then
        arr3 = Array("s", "f", "e", "g", "j")' выборка  (условия ВЫБОРКИ) 1
    ElseIf x = 2 Then
        arr3 = Array("a", "b", "c", "d", "e")' выборка 2
    ElseIf x = 3 Then
        arr3 = Array("a", "s", "x", "e", "f")' выборка 3
    ElseIf x = 4 Then
        arr3 = Array("a", "l", "e")' выборка 4
    ElseIf x = 5 Then
        arr3 = Array("a", "e")' выборка 5
    ElseIf x = 6 Then
        arr3 = Array("s", "e")' выборка 6
    End If
For i = 2 To 8
    For k = LBound(arr3) To UBound(arr3)
        s = s + Application.WorksheetFunction.CountIfs(Range(Cells(i, 1), Cells(i, 5)), arr3(k))
    Next k
    If s >= UBound(arr3) + 1 Then
        Range(Cells(i, 1), Cells(i, 5)).Copy Destination:=Cells(n, 7) ' ВСТАВЛЯЕМ В 7 СТОЛБЕЦ СО ВТОРОЙ СТРОКИ И ДАЛЕЕ ВНИЗ
        Cells(n, 12) = Join(arr3)
        n = n + 1
        s = 0
    End If
Next i
n = n + 1
Next x
End Sub

Изменено: Mershik - 23.11.2021 08:44:40
Не бойтесь совершенства. Вам его не достичь.
 
Mershik,
Медведь меня вот так научил)))
я раньше меткой сокращал, а можно вот так штатно  :idea:
Изменено: Jack Famous - 23.11.2021 09:20:23
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Цитата
написал:
а в приведеном Вами примере (желаемый результ) почему не содержит строкуfsegjдля (s e)
Не увидела, писала же ручками:) Сейчас попробую в макросе разобраться. Но на вряд ли разберусь :D  
 
А мне казалось, что комбинации, которые ищем, не указываются. Сначала находим все сочетания в каждой строке, потом количество повторений сочетаний во всех строках. На вывод все, что повторяется.
 
Ну да. Я там в верхнем макросе ещё не разобралась. По идее так и надо. Есть только строчки со значениями. Надо найти повторяющиеся значения в строках. Потом эти строки вывести так, чтобы наибольшое кол-во повторений было вверху. То есть на убывание.

P.S.
Выше макрос работает, почему то в начала ошибку синтаксиса показывал, но всё же, значения то, повторяющиеся я не знаю:(
 
Выше макрос работает, почему то в начала ошибку синтаксиса показывал, но всё же, значения то, повторяющиеся я не знаю:(
 
Мария Гончарова, вариант в PQ:
Код
let
  src = Excel.CurrentWorkbook(){[ Name = "data" ]}[Content],
  allToText = Table.FromColumns ( List.Transform ( Table.ToColumns ( src ), ( x ) => List.Transform ( x, Text.From ) ), Table.ColumnNames ( src ) ),
  addComb = Table.AddColumn (
    allToText,
    "comb",
    each List.Accumulate ( List.Sort ( Record.ToList ( _ ) ), { {} }, ( s, c ) => s & List.Transform ( s, ( x ) => x & { c } ) )
  ),
  expand = Table.ExpandListColumn ( addComb[[comb]], "comb" ),
  addCount = Table.AddColumn ( expand, "Количество значений", each List.Count ( [comb] ), Int64.Type ),
  filter = Table.SelectRows ( addCount, each [Количество значений] > 0 ),
  toRec = Table.TransformColumns ( filter, { "comb", ( x ) => Record.FromList ( x, List.FirstN ( Table.ColumnNames ( src ), List.Count ( x ) ) ) } ),
  group = Table.Group ( toRec, { "comb", "Количество значений" }, { { "Число повторений", each Table.RowCount ( _ ), Int64.Type } } ),
  sort = Table.Sort ( group, { { "Количество значений", Order.Descending }, { "Число повторений", Order.Descending } } ),
  expandRecs = Table.ExpandRecordColumn ( sort, "comb", Table.ColumnNames ( src ) )
in
  expandRecs
Изменено: surkenny - 23.11.2021 00:26:54
 
Добавил 3 разных варианта. В общем-то, разницы в них только в отсутствии/наличии List.Distinct в разных частях третьего шага.
1. Абсолютно все сочетания (неважно, есть ли повторения значений и сочетаний в одной строке; если 3 значения "a" в строке, то это будет 3 повторения "a, a")
Скрытый текст

2. Только уникальные сочетания из строки (если 3 значения "a" в строке, то это будет 1 повторение "a, a")
Скрытый текст

3. Сочетания только уникальных значений (сочетания типа "a, a" или "a, a, а" вообще не учитываем):
Скрытый текст
 
surkenny, Спасибо огромное. Как раз пользовалась и вот как раз делала Ваш третий вариант:) Но так как в pq не бум-бум, заняло бы это долгое время. Не подскажете, возможно как-то ограничивать вывод повторяющихся значений, к примеру до 4. А то я добавила до 20 значений в строке и сделала чуть больше строк, уже начало дольше работать(намного). Я так понимаю, как раз из-за вывода повторяющихся значений один, два раза и т.д. И кстати, я ведь могу и цифры подставлять туда, не надо ведь нигде number прописывать и т.д.?
Изменено: Мария Гончарова - 23.11.2021 09:40:44
 
Мария Гончарова, Вы только третий запрос обновляете? Если по кнопке "Обновить все", то удалите первые 2 запроса.
Цитата
Мария Гончарова написал:
добавила до 20 значений в строке и сделала чуть больше строк, уже начало дольше работать
Вы понимаете, что число сочетаний (если все уникальные) в вашем примере равно k(n-1)!, где n - количество элементов в строке, k - количество строк. То есть для одной строки в 5 элементов - это 24 сочетания, для 7 элементов - 720. Для 20 значений в одной строке 1,2 * 10^17, а для 8 строк это миллиард миллиардов сочетаний.
Цитата
Мария Гончарова написал:
я ведь могу и цифры подставлять туда
в шаге
Код
allToText = Table.FromColumns ( List.Transform ( Table.ToColumns ( src ), ( x ) => List.Transform ( x, Text.From ) ), Table.ColumnNames ( src ) )

абсолютно все значения всех столбцов переводятся в текст. Так что можете туда что угодно впихивать :).
Изменено: surkenny - 23.11.2021 09:43:38
 
Цитата
написал:
То есть для одной строки в 5 элементов - это 24 сочетания, для 7 элементов - 720. Для 20 значений в одной строке 1,2 * 10^17, а для 8 строк это миллиард миллиардов сочетаний.
Не поняла. В одной строке ведь не может быть сочетаний. 20 уникальных значений. Нужно же 2 минимум строчки. Запуталась в Ваших вычислениях :D . Но думаю, туплю я с утра. Раз так, можно как-то переделать так, чтобы можно было указать сколько комбинаций в этих строках я хочу найти. К примеру строчки с 4 комбинациями?
 
Мария Гончарова,
1. Нам нужно найти все сочетания уникальных значений (наборы значений по 1шт, по 2шт, по 3шт, по 5шт .. по 20шт) в каждой строке.
2. Вычислить количество повторений определенных сочетаний (по сути, если берем только уникальные значения - в каком количестве строк такие сочетания есть).
В первом шаге количество сочетаний равно колСтрок * (колЗначений - 1)!. Для 8 строк и 20 уникальных значений в каждой это 1 000 000 000 000 000 000 сочетаний.
3. Этот миллиард миллиардов сочетаний мы должны проверить на повторения.
Конечно, это будет долго :)
Цитата
Мария Гончарова написал:
К примеру строчки с 4 комбинациями
Не совсем понял. Вы имеете ввиду, хотите найти повторения только комбинаций из 4-х элементов? Тогда в коде есть строка:
Код
filter = Table.SelectRows ( addCount, each [Количество значений] > 0 )

Комбинации только из 4-х элементов :
Код
filter = Table.SelectRows ( addCount, each [Количество значений] = 4 )

Комбинации только из 4-х и более элементов:
Код
filter = Table.SelectRows ( addCount, each [Количество значений] >= 4 )

Если нужно найти комбинации, которые повторяются более 3-х раз, то после шага group добавьте выбор нужных строк:
Скрытый текст
Изменено: surkenny - 23.11.2021 10:15:13
 
,Ещё раз огромнейшее спасибо. Работает как часы. Насчёт сочетаний поняла. Попробовала для проверки сделать 10 строк и 20 значений. Поискал комбо на  5 значениях. Вышло 150к строк. Поиск на 6 значениях, уже 300к.
Цитата
написал:
Если нужно найти комбинации, которые повторяются более 3-х раз, то после шага group добавьте выбор нужных строк:
Не поняла вот это :sceptic: .

Да уж, строк выходит много. В каком шаге редактировать, чтобы количество повторений регулировать? Как раз таки случаем не filter2? Пробовала так:
Код
filter2 = Table.SelectRows ( group, each [Число повторений] = 2 ),
Не получилось :D . Большинство строк имеет кол-во повторений-1. То есть это же значения, которые не повторяются вовсе? Хотелось бы вообще тогда не выводить их, дабы ускорить процесс. Так пк мой вроде бы хороший:)
 
Мария Гончарова, именно filter 2. Работает корректно.
Отбор только тех сочетаний, которые повторяются 2 раза (присутствуют в 2-х каких-то строках):
Код
filter2 = Table.SelectRows ( group, each [Число повторений] = 2 )

Отбор только тех сочетаний, которые повторяются более 1 раза (присутствуют в 2-х и более строках):
Код
filter2 = Table.SelectRows ( group, each [Число повторений] >1 )

Немного изменил наименование столбца: "Количество значений" на "Количество элементов"
Пример для поиска сочетаний уникальных значений строки из 2-х и более элементов (задается в шаге filterElements), повторяющихся 3 и более раз (задается в шаге filterRepeats):
Скрытый текст
Изменено: surkenny - 23.11.2021 12:20:51
 
surkenny, Извиняюсь, помогите добавить в исключения пустые ячейки в Вашем коде :)  
Изменено: Мария Гончарова - 25.11.2021 14:27:26
 
Мария Гончарова,
1. На листе "Фильтр" левая таблица - это исключаемые значения. Можно указывать любое количество исключений.
2. Правая таблица - фильтр результатов (сочетания от скольких элементов ищем, от какого количества повторов выводим): не нужно руками править код.
3. В код добавлено исключение пустых ячеек из сочетаний. Любые другие (к примеру, пробел, можно добавить в таблицу из п.1).
4. В результат теперь выводится количество столбцов, равное максимальному количеству элементов в сочетании (не будет полностью пустых столбцов "ЗначениеN")/
5. Немного оптимизировал код:
Скрытый текст


P.S. Стыдно, что наврал Вам про количество сочетаний:) Если нам неважен порядок, то число разных сочетаний в одной строке равно 2^k-1, где k - количество уникальных элементов. Для 20 элементов это чуть больше миллиона.
Изменено: surkenny - 25.11.2021 15:25:23
 
surkenny, Вы лучший, спасибо ещё раз. Мастер своего дело. Довели до совершенства, стало удобнее регулировать мин. кол-во элементов и число повторений. Прямо искусство. Я теперь всё верно отображается, в плане пустых строк.
Цитата
написал: Стыдно, что наврал Вам про количество сочетаний:)
Ничего страшного, со всеми бывает. Я тогда тоже раз 50 подставляла под Вашу формулу и не понимала, почему не правильно. Но не стала спорить, ибо на практике главное, было верно:)
Изменено: Мария Гончарова - 25.11.2021 17:52:32
 
Всем привет и всех с новым годом:) Может кто пожалуйста добавить в код surkenny в лист "Фильтр" максимальное число элементов в сочетании. То есть так же, как он сделал сообщением выше в пункте 2:
Цитата
написал:
2. Правая таблица - фильтр результатов (сочетания от скольких элементов ищем, от какого количества повторов выводим): не нужно руками править код.
Только сочетание до скольки элементов ищем :D  
Страницы: 1
Наверх