У меня есть список из более чем 50 тыс. URL-адресов, и я ищу наиболее распространенные 3/4/5/6 букв в этих URL-адресах (за исключением .com/.org/etc).
Итак, если URL-адреса strings.com
и string2.com
, это подскажет мне, что string
— самая распространенная последовательность букв.
Есть ли способ сделать это?
Я пробовал =INDEX(range, MODE(MATCH(range, range, 0 )))
, но не получилось.
@MichaelL, не могли бы вы поделиться образцом входных данных вашего URL, чтобы мы могли что-то протестировать на основе некоторых ваших входных значений? Пожалуйста, предоставьте информацию, используя формат таблицы уценки. Вы можете использовать Генератор уценки таблиц
@MichaelL Я ответил на ваш вопрос, сгенерировав некоторые образцы данных для проблемы, которую, как я думаю, вы хотите решить, пожалуйста, просмотрите ее и дайте мне знать, если это то, что вы ищете. Спасибо
Следующее выведет все возможные последовательные подстроки из 3-6 символов и их количество в порядке их соответствующего количества:
Формула в C2
:
=LET(x,TOCOL(DROP(REDUCE(0,SEQUENCE(4,,3),LAMBDA(a,b,HSTACK(a,LET(c,REDUCE(0,A1:A3,LAMBDA(d,e,VSTACK(d,LET(f,MID(e,SEQUENCE(LEN(e)-b+1),b),f)))),c)))),1),2),y,UNIQUE(x),UNIQUE(SORT(HSTACK(y,MAP(y,LAMBDA(z,SUM(--(x=z))))),2,-1)))
Ты сделал это снова. Невероятный. Вероятно, это не будет работать с большой базой данных, но мне нравится решение.
Спасибо @p.b, что я попробовал и нашел здесь, так это то, что мы можем лучше всего уменьшить сами по себе, где обычно мы получаем ошибки при вложении лямбда-хелперов.
Я предполагаю, что вы хотели бы найти наиболее часто встречающиеся подстроки для набора разных размеров (6/5/4/3). Допустим, ваши данные находятся в столбце A
. Вы хотели бы знать наиболее распространенные подстроки для всех возможных размеров в списке.
В ячейке C1
генерируем ожидаемые размеры:
=SEQUENCE(1,4,6,-1)
Если хотите в обратном порядке, то: SEQUENCE(1,4,3,1)
Теперь для каждой длины мы найдем наиболее частый шаблон в пределах этой длины из столбца A
.
В ячейке C2
мы будем использовать следующую формулу (формула 1):
=LET(maxLength, C1, strings, $A$2:$A$13, substr, LEFT(strings, maxLength),
match, XMATCH(substr, UNIQUE(substr)),
matchUX, UNIQUE(match), freq, DROP(FREQUENCY(match, matchUX), -1),
maxFreq, MAX(freq), matchIdx, FILTER(matchUX, ISNUMBER(XMATCH(freq, maxFreq))),
UNIQUE(FILTER(substr, ISNUMBER(XMATCH(match, matchIdx))))
)
а затем разверните его вправо, чтобы учесть разную длину в каждом столбце.
В предыдущей формуле используется шаблон XMATCH/FREQUENCY
, описанный здесь:
Как посчитать количество совершенных сделок в таблице Excel по пользовательской условной формуле? в ответе @DavidLeal.
Следуя идее из ответа на вопрос: Найдите день, когда больше всего времени было потрачено в excel, предоставленный @DavidLeal, вы можете использовать ту же идею, если у вас нет функции DROP
, доступной в вашей версии excel ( Формула 2).
=LET(maxLength, C1, strings, $A$2:$A$13, substr, LEFT(strings, maxLength),
match, XMATCH(substr, UNIQUE(substr)), matchUX, UNIQUE(match),
matrix, IF(TOROW(match)=matchUx, 1,0), ones, SEQUENCE(ROWS(match),,1,0),
freq, MMULT(matrix, ones), maxFreq, MAX(freq),
matchIdx, FILTER(matchUX, ISNUMBER(XMATCH(freq, maxFreq))),
UNIQUE(FILTER(substr, ISNUMBER(XMATCH(match, matchIdx))))
)
Другая альтернатива, если вы не можете использовать DROP
, используемую в Формуле 1, заменяет следующую строку:
freq, DROP(FREQUENCY(match, matchUX), -1)
с
freqRes, FREQUENCY(match, matchUX), freq, FILTER(freqRes, freqRes<>0)
Функция DROP
используется для удаления последней строки freq
в формуле 1, которая имеет значение 0
. Это относится к частоте последних открытых бинов, сгенерированных FREQUENCY
, поэтому мы используем FILTER
, чтобы удалить такое значение.
Если вы хотите получить результат без расширения вправо, вы можете использовать следующее и получить весь результат всего одной формулой с помощью одной из следующих альтернатив:
Альтернатива 1: использование DROP/REDUCE/HSTACK
следующим образом:
=LET(maxLengths, C1:F1, strings, A2:A13,
DROP(REDUCE(0, maxLengths, LAMBDA(acc,length, HSTACK(acc, LET(
substr, LEFT(strings, length), match, XMATCH(substr, UNIQUE(substr)),
matchUX, UNIQUE(match), freq, DROP(FREQUENCY(match, matchUX), -1),
maxFreq, MAX(freq), matchIdx, FILTER(matchUX, ISNUMBER(XMATCH(freq, maxFreq))),
UNIQUE(FILTER(substr, ISNUMBER(XMATCH(match, matchIdx))))
)))),,1)
)
Альтернатива 2: Использование TEXTSPLIT/TEXTJOIN
:
=LET(maxLengths, C1:F1, strings, A2:A13,
bc, BYCOL(maxLengths, LAMBDA(length, LET(
substr, LEFT(strings, length), match, XMATCH(substr, UNIQUE(substr)),
matchUX, UNIQUE(match), freq, DROP(FREQUENCY(match, matchUX), -1),
maxFreq, MAX(freq), matchIdx, FILTER(matchUX, ISNUMBER(XMATCH(freq, maxFreq))),
TEXTJOIN(";",,UNIQUE(FILTER(substr, ISNUMBER(XMATCH(match, matchIdx)))))
))), TRANSPOSE(TEXTSPLIT(TEXTJOIN(",",, bc), ";", ","))
)
Примечание. В обоих вариантах нет необходимости использовать $
-обозначение.
Вот ожидаемый результат с использованием Формулы 1:
У вас может быть более одной подстроки с наибольшей частотой, поэтому она может возвращать массив вместо одного значения. В предыдущем примере выводом является только одно значение, но формула предполагает, что максимальную частоту может иметь более одной подстроки.
(на основе Формулы 1)
На каждой итерации (в каждом столбце) мы выбираем из имени strings
соответствующую подстроку (исходя из максимальной длины). Мы используем для этого функцию LEFT
.
Поскольку substr
name — это массив (а не диапазон, мы не можем использовать никакую функцию из семейства COUNTIF*
). Вместо этого мы будем использовать XMATCH/FREQUENCY
, потому что оба принимают массивы.
Имя match
:
XMATCH(substr, UNIQUE(substr))
Помогает найти количество через: XMATCH(lookup_value, lookup_array)
каждый раз, когда значение lookup_array
находится в lookup_value
, оно ставит одну и ту же позицию индекса.
Теперь с уникальным количеством совпадений (matchUX
) мы можем сделать подсчет через FREQUENCY
. Нам не нужны последние открытые бины (проверьте документацию этой функции), поэтому мы удаляем последнюю строку через DROP
функцию.
Теперь у нас есть частота (freq
), и нам нужно найти максимальную частоту. Функция FREQUENCY
возвращает частоту для каждого бина (matchUX
). Итак, нас интересуют все значения из matchUX
, которые имеют максимальную частоту (maxFreq
). Для этого мы используем функцию FILTER
. У нас может быть более одного отфильтрованного результата, то есть сценарий, в котором две подстроки имеют максимальную частоту. Вот как мы вычисляем имя matchIdx
:
FILTER(matchUX, ISNUMBER(XMATCH(freq, maxFreq)))
Тогда имя matchIdx
имеет все индексы из имени match
, соответствующие максимальной частоте. Теперь нам нужно найти соответствующие substr
значения. Имена match
и substr
имеют одинаковый размер, поэтому мы можем снова использовать FILTER
, чтобы найти такие значения substr
через matchIdx
используя функцию XMATCH
:
FILTER(substr, ISNUMBER(XMATCH(match, matchIdx)))
Наконец, мы используем UNIQUE
в случае, если максимальная частота была достигнута одной и той же подстрокой, поэтому нас интересуют только уникальные подстроки.
Это очень обширно! респект за вашу аккуратность
Спасибо @JvdV от вас, я очень ценю это.
Так приятно видеть, насколько универсальны эти лямбда-помощники!
Спасибо, @MichaelL, пожалуйста, проверьте эту ссылку: Что мне делать, когда кто-то ответит на мой вопрос?. Вот как выразить признательность в StackOverflow: проголосовать, принять или и то, и другое. Если вы считаете это.
Я пытался сделать это всеми тремя способами, которые вы предложили, но первый дает мне #CALC! ошибка, а две другие дают мне #ИМЯ? ошибка, то же самое с решением JvdV выше. Я знаю, что веду себя глупо, но могу ли я что-то пропустить? У меня Excel 2021 на MacOS. Это мой скриншот: i.ibb.co/xYnp8bh/excelcalc.png Извините за беспокойство и большое спасибо за помощь!
@MichaelL Наверное, из-за DROP
доступности. Вам необходимо указать любые ограничения версии excel с помощью тега excel. Попробуйте следующее: =LET(maxLength, C1, strings, $A$2:$A$13, substr, LEFT(strings, maxLength), match, XMATCH(substr, UNIQUE(substr)), matchUX, UNIQUE(match), freqRes, FREQUENCY(match, matchUX), freq, FILTER(freqRes, freqRes<>0), maxFreq, MAX(freq), matchIdx, FILTER(matchUX, ISNUMBER(XMATCH(freq, maxFreq))), UNIQUE(FILTER(substr, ISNUMBER(XMATCH(match, matchIdx)))) )
DROP
используется после FREQUENCY
для удаления последних бинов (открытый диапазон), так как значение частоты будет для этого случая 0
, то можно удалить его с помощью FILTER
вместо этого. Пожалуйста, дайте мне знать, если это решит проблему, чтобы обновить ответ, чтобы предоставить альтернативу. Это исправит первую формулу и Альтернативу 2.
Попался! Предложенное вами решение работает! Мне было интересно, можно ли, например, увидеть 2-ю/3-ю/и т. д. наиболее частые фразы для подстроки из 8. Еще раз спасибо!
Я рад, что это сработало. Для подстроки из 8. Просто введите значение в ячейку C1
. Для трех более частых, я думаю, это сработало бы с моей головы, меняя MAX(freq)
на LARGE(freq, {1;2;3})
, попробуйте поиграть с этим.
Требуется ли, чтобы буквы были смежными?