Добрый вечер всем, специально зарегистрировалась тут, дабы задать вопрос, в надежде, что тут помогут. Самой не хватает знаний:( Искала в поиске на данном форуме похожий вопрос, но вроде бы прямо такого нету. В общем, есть определенное количество строк со значениями. Значения вынесены в отдельный столбец(ячейку). Нужно найти строчки с нужными мне повторяющимися значениями и как то вынести их и отсортировать в порядке убывания. Приложу пример. Если что, объясню подробнее... Я не знаю, нужна тут формула или может быть макрос какой нибудь. Или утилита.
Мария, очень непросто понять вашу задачу. Поэтому и ответов до сих пор нет. Вы бы просто указали в своем примере, что дано (исходные данные) и что вы хотите получить (конечный результат). А все лишнее, включая радугу, лучше бы убрать.
Радуга просто чтобы указать на строчки с повторяющиеся значения Я не знаю как правильно объяснить. Есть несколько строчек, как в примере. Там 7 строчек. В каждой строчке есть значения вынесенные в отдельный столбец(ячейку). Строчка может быть и из 10 значений и из 20. Не важно. И вот в каждой строчке есть повторяющиеся значения. Я хотела как-то отсортировать эти строчки по кол-ву повторяющихся в них значений. В примере использовала просто английские буквы. Upd. Прикреплю ещё раз пример, без радуги Как бы не важна сама сортировка. Главное вынести строки с одинаковыми значениями отдельно. Ну если так будет удобнее. Как в примере, в 3 строчках 5 значений повторились. Потом ещё в 2 других строчках повторились так же 5 значений и тд. Затем строчки, где уже 4 значения повторились, затем 3 значения и тд.
, а сколько таких выборок? (s f e g j, a b c d e, и т.д.) а в приведеном Вами примере (желаемый результ) почему не содержит строку
f
s
e
g
j
для (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
' вместо
If x = 1 Then
arr3 = Array("s", "f", "e", "g", "j")
ElseIf x = 2 Then
arr3 = Array("a", "b", "c", "d", "e")
ElseIf x = 3 Then
arr3 = Array("a", "s", "x", "e", "f")
ElseIf x = 4 Then
arr3 = Array("a", "l", "e")
ElseIf x = 5 Then
arr3 = Array("a", "e")
ElseIf x = 6 Then
arr3 = Array("s", "e")
End If
' удобнее
Select Case x
Case 1: arr3 = Array("s", "f", "e", "g", "j")
Case 2: arr3 = Array("a", "b", "c", "d", "e")
Case 3: arr3 = Array("a", "s", "x", "e", "f")
Case 4: arr3 = Array("a", "l", "e")
Case 5: arr3 = Array("a", "e")
Case 6: arr3 = Array("s", "e")
End Select
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
А мне казалось, что комбинации, которые ищем, не указываются. Сначала находим все сочетания в каждой строке, потом количество повторений сочетаний во всех строках. На вывод все, что повторяется.
Ну да. Я там в верхнем макросе ещё не разобралась. По идее так и надо. Есть только строчки со значениями. Надо найти повторяющиеся значения в строках. Потом эти строки вывести так, чтобы наибольшое кол-во повторений было вверху. То есть на убывание.
P.S. Выше макрос работает, почему то в начала ошибку синтаксиса показывал, но всё же, значения то, повторяющиеся я не знаю:(
Добавил 3 разных варианта. В общем-то, разницы в них только в отсутствии/наличии List.Distinct в разных частях третьего шага. 1. Абсолютно все сочетания (неважно, есть ли повторения значений и сочетаний в одной строке; если 3 значения "a" в строке, то это будет 3 повторения "a, a")
surkenny, Спасибо огромное. Как раз пользовалась и вот как раз делала Ваш третий вариант:) Но так как в pq не бум-бум, заняло бы это долгое время. Не подскажете, возможно как-то ограничивать вывод повторяющихся значений, к примеру до 4. А то я добавила до 20 значений в строке и сделала чуть больше строк, уже начало дольше работать(намного). Я так понимаю, как раз из-за вывода повторяющихся значений один, два раза и т.д. И кстати, я ведь могу и цифры подставлять туда, не надо ведь нигде number прописывать и т.д.?
Мария Гончарова, Вы только третий запрос обновляете? Если по кнопке "Обновить все", то удалите первые 2 запроса.
Цитата
Мария Гончарова написал: добавила до 20 значений в строке и сделала чуть больше строк, уже начало дольше работать
Вы понимаете, что число сочетаний (если все уникальные) в вашем примере равно k(n-1)!, где n - количество элементов в строке, k - количество строк. То есть для одной строки в 5 элементов - это 24 сочетания, для 7 элементов - 720. Для 20 значений в одной строке 1,2 * 10^17, а для 8 строк это миллиард миллиардов сочетаний.
Цитата
Мария Гончарова написал: я ведь могу и цифры подставлять туда
написал: То есть для одной строки в 5 элементов - это 24 сочетания, для 7 элементов - 720. Для 20 значений в одной строке 1,2 * 10^17, а для 8 строк это миллиард миллиардов сочетаний.
Не поняла. В одной строке ведь не может быть сочетаний. 20 уникальных значений. Нужно же 2 минимум строчки. Запуталась в Ваших вычислениях . Но думаю, туплю я с утра. Раз так, можно как-то переделать так, чтобы можно было указать сколько комбинаций в этих строках я хочу найти. К примеру строчки с 4 комбинациями?
Мария Гончарова, 1. Нам нужно найти все сочетания уникальных значений (наборы значений по 1шт, по 2шт, по 3шт, по 5шт .. по 20шт) в каждой строке. 2. Вычислить количество повторений определенных сочетаний (по сути, если берем только уникальные значения - в каком количестве строк такие сочетания есть). В первом шаге количество сочетаний равно колСтрок * (колЗначений - 1)!. Для 8 строк и 20 уникальных значений в каждой это 1 000 000 000 000 000 000 сочетаний. 3. Этот миллиард миллиардов сочетаний мы должны проверить на повторения. Конечно, это будет долго
,Ещё раз огромнейшее спасибо. Работает как часы. Насчёт сочетаний поняла. Попробовала для проверки сделать 10 строк и 20 значений. Поискал комбо на 5 значениях. Вышло 150к строк. Поиск на 6 значениях, уже 300к.
Цитата
написал: Если нужно найти комбинации, которые повторяются более 3-х раз, то после шага group добавьте выбор нужных строк:
Не поняла вот это .
Да уж, строк выходит много. В каком шаге редактировать, чтобы количество повторений регулировать? Как раз таки случаем не filter2? Пробовала так:
Не получилось . Большинство строк имеет кол-во повторений-1. То есть это же значения, которые не повторяются вовсе? Хотелось бы вообще тогда не выводить их, дабы ускорить процесс. Так пк мой вроде бы хороший:)
Отбор только тех сочетаний, которые повторяются более 1 раза (присутствуют в 2-х и более строках):
Код
filter2 = Table.SelectRows ( group, each [Число повторений] >1 )
Немного изменил наименование столбца: "Количество значений" на "Количество элементов" Пример для поиска сочетаний уникальных значений строки из 2-х и более элементов (задается в шаге filterElements), повторяющихся 3 и более раз (задается в шаге filterRepeats):
Мария Гончарова, 1. На листе "Фильтр" левая таблица - это исключаемые значения. Можно указывать любое количество исключений. 2. Правая таблица - фильтр результатов (сочетания от скольких элементов ищем, от какого количества повторов выводим): не нужно руками править код. 3. В код добавлено исключение пустых ячеек из сочетаний. Любые другие (к примеру, пробел, можно добавить в таблицу из п.1). 4. В результат теперь выводится количество столбцов, равное максимальному количеству элементов в сочетании (не будет полностью пустых столбцов "ЗначениеN")/ 5. Немного оптимизировал код:
Скрытый текст
Код
let
src = Table.Buffer ( Excel.CurrentWorkbook(){[ Name = "data" ]}[Content] ),
clmnNames = List.Buffer ( Table.ColumnNames ( src ) ),
filterValues = List.Buffer ( Excel.CurrentWorkbook(){[ Name = "filterValues" ]}[Content][Значение] ),
addComb = Table.AddColumn (
src,
"comb",
each List.Accumulate (
List.Sort ( List.Difference ( List.RemoveNulls ( List.Distinct ( Record.ToList ( _ ) ) ), filterValues ) ),
{ {} },
( 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 ),
parameters = Record.FromTable (
Table.FromColumns ( Table.ToColumns ( Excel.CurrentWorkbook(){[ Name = "parameters" ]}[Content] ), type table [ Name = text, Value = number ] )
),
filterElements = Table.SelectRows (
addCount,
each [Количество элементов] >= Record.FieldOrDefault ( parameters, "Минимальное количество элементов в сочетании", null )
),
toRec = Table.TransformColumns ( filterElements, { "comb", ( x ) => Record.FromList ( x, List.FirstN ( clmnNames, List.Count ( x ) ) ) } ),
group = Table.Group ( toRec, { "comb", "Количество элементов" }, { { "Число повторений", each Table.RowCount ( _ ), Int64.Type } } ),
filterRepeats = Table.SelectRows ( group, each [Число повторений] >= Record.FieldOrDefault ( parameters, "Минимальное число повторений", null ) ),
sort = Table.Sort ( filterRepeats, { { "Количество элементов", Order.Descending }, { "Число повторений", Order.Descending } } ),
expandRecs = Table.ExpandRecordColumn ( sort, "comb", List.FirstN ( clmnNames, sort[Количество элементов]{0} ) )
in
expandRecs
P.S. Стыдно, что наврал Вам про количество сочетаний:) Если нам неважен порядок, то число разных сочетаний в одной строке равно 2^k-1, где k - количество уникальных элементов. Для 20 элементов это чуть больше миллиона.
surkenny, Вы лучший, спасибо ещё раз. Мастер своего дело. Довели до совершенства, стало удобнее регулировать мин. кол-во элементов и число повторений. Прямо искусство. Я теперь всё верно отображается, в плане пустых строк.
Цитата
написал: Стыдно, что наврал Вам про количество сочетаний:)
Ничего страшного, со всеми бывает. Я тогда тоже раз 50 подставляла под Вашу формулу и не понимала, почему не правильно. Но не стала спорить, ибо на практике главное, было верно:)
Всем привет и всех с новым годом:) Может кто пожалуйста добавить в код surkenny в лист "Фильтр" максимальное число элементов в сочетании. То есть так же, как он сделал сообщением выше в пункте 2:
Цитата
написал: 2. Правая таблица - фильтр результатов (сочетания от скольких элементов ищем, от какого количества повторов выводим): не нужно руками править код.