Как найти наиболее распространенную последовательность из 6 букв в файле Excel?

У меня есть список из более чем 50 тыс. URL-адресов, и я ищу наиболее распространенные 3/4/5/6 букв в этих URL-адресах (за исключением .com/.org/etc).

Итак, если URL-адреса strings.com и string2.com, это подскажет мне, что string — самая распространенная последовательность букв.

Есть ли способ сделать это?

Я пробовал =INDEX(range, MODE(MATCH(range, range, 0 ))), но не получилось.

Требуется ли, чтобы буквы были смежными?

JvdV 14.11.2022 21:41

@MichaelL, не могли бы вы поделиться образцом входных данных вашего URL, чтобы мы могли что-то протестировать на основе некоторых ваших входных значений? Пожалуйста, предоставьте информацию, используя формат таблицы уценки. Вы можете использовать Генератор уценки таблиц

David Leal 14.11.2022 22:16

@MichaelL Я ответил на ваш вопрос, сгенерировав некоторые образцы данных для проблемы, которую, как я думаю, вы хотите решить, пожалуйста, просмотрите ее и дайте мне знать, если это то, что вы ищете. Спасибо

David Leal 15.11.2022 00:10
Преобразование HTML-таблицы в профессиональный документ Excel
Преобразование HTML-таблицы в профессиональный документ Excel
Это самый простой способ создания Excel из HTML-таблицы.
Импорт excel в laravel в базу данных
Импорт excel в laravel в базу данных
Здравствуйте, дорогой читатель, в этой статье я расскажу практическим и быстрым способом, как импортировать файл Excel в вашу базу данных с помощью...
1
3
111
2
Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Следующее выведет все возможные последовательные подстроки из 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 14.11.2022 23:42

Спасибо @p.b, что я попробовал и нашел здесь, так это то, что мы можем лучше всего уменьшить сами по себе, где обычно мы получаем ошибки при вложении лямбда-хелперов.

JvdV 15.11.2022 08:32
Ответ принят как подходящий

Я предполагаю, что вы хотели бы найти наиболее часто встречающиеся подстроки для набора разных размеров (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 15.11.2022 09:34

Спасибо @JvdV от вас, я очень ценю это.

David Leal 15.11.2022 14:44

Так приятно видеть, насколько универсальны эти лямбда-помощники!

JvdV 15.11.2022 14:51

Спасибо, @MichaelL, пожалуйста, проверьте эту ссылку: Что мне делать, когда кто-то ответит на мой вопрос?. Вот как выразить признательность в StackOverflow: проголосовать, принять или и то, и другое. Если вы считаете это.

David Leal 22.11.2022 22:46

Я пытался сделать это всеми тремя способами, которые вы предложили, но первый дает мне #CALC! ошибка, а две другие дают мне #ИМЯ? ошибка, то же самое с решением JvdV выше. Я знаю, что веду себя глупо, но могу ли я что-то пропустить? У меня Excel 2021 на MacOS. Это мой скриншот: i.ibb.co/xYnp8bh/excelcalc.png Извините за беспокойство и большое спасибо за помощь!

Michael L 30.11.2022 04:39

@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)))) )

David Leal 30.11.2022 06:00
DROP используется после FREQUENCY для удаления последних бинов (открытый диапазон), так как значение частоты будет для этого случая 0, то можно удалить его с помощью FILTER вместо этого. Пожалуйста, дайте мне знать, если это решит проблему, чтобы обновить ответ, чтобы предоставить альтернативу. Это исправит первую формулу и Альтернативу 2.
David Leal 30.11.2022 06:07

Попался! Предложенное вами решение работает! Мне было интересно, можно ли, например, увидеть 2-ю/3-ю/и т. д. наиболее частые фразы для подстроки из 8. Еще раз спасибо!

Michael L 01.12.2022 07:27

Я рад, что это сработало. Для подстроки из 8. Просто введите значение в ячейку C1. Для трех более частых, я думаю, это сработало бы с моей головы, меняя MAX(freq) на LARGE(freq, {1;2;3}), попробуйте поиграть с этим.

David Leal 01.12.2022 07:59

Другие вопросы по теме