Множественный поиск и замена с включениями и исключениями
Мне нужно найти и заменить несколько текстовых строк во фразах. Строки поиска следует выбирать по критериям, известным как «Сайты». Существует критерий «включения» и критерия «исключения».
Критерии
СтрокаНет Набор списков (А) Значения для поиска (B) Значения для замены (C) Включения (Д) Исключения (Е) Поиск (Ф) Заменить (G) (8) Аниме набор Сцены Аниме Сцены=IFERROR(ARRAYFORMULA("^("&QUERY(B8:B,"SELECT Col1 WHERE Col1 IS NOT NULL")&")$\|^("&QUERY(B8:B,"SELECT Col1 WHERE Col1 IS NOT NULL")&")(,)\|(,)("&QUERY(B8:B,"SELECT Col1 WHERE Col1 IS NOT NULL")&")(,)\|(,)("&QUERY(B8:B,"SELECT Col1 WHERE Col1 IS NOT NULL")&")$"),)=IFERROR(ARRAYFORMULA("$4$7"&QUERY(C8:C,"SELECT Col1 WHERE Col1 IS NOT NULL")&"$3$6"),)(9) Аниме набор Факты Факты об аниме Сайт 1 (10) Аниме набор Мемы Мемы из аниме Сайт 1 (11) Набор фильмов Сцены Сцены из фильма (12) Набор фильмов Факты Факты о кино Сайт 2 Сайт 3 (13) Набор фильмов Мемы Мемы из фильмов (14) Музыкальный набор Факты Факты о музыке (15) Музыкальный набор Фестиваль Музыкальный фестиваль Сайт 4 (16) Музыкальный набор Приложение Музыкальное приложение Сайт 4Исходные данные и желаемые результаты:
СтрокаНет Набор списков (H) Принадлежность (я) Тексты (Дж) Замененные тексты (К) (8) Аниме набор Сцены,Факты,Мемы Сцены из аниме,Факты,Мемы из аниме (9) Аниме набор Сайт 1 Сцены,Факты,Мемы Сцены из аниме,Факты об аниме,Мемы (10) Набор фильмов Сцены,Факты,Мемы Сцены из фильмов,Факты,Мемы из фильмов (11) Набор фильмов Сайт 2 Сцены,Факты,Мемы Сцены из фильмов,Факты о кино,Мемы из фильмов (12) Набор фильмов Сайт 3 Сцены,Факты,Мемы Сцены из фильмов,Факты,Мемы из фильмов (13) Музыкальный набор Факты, фестиваль, приложение Факты о музыке, фестивале, музыкальном приложении (14) Музыкальный набор Сайт 4 Факты, фестиваль, приложение Факты о музыке, музыкальном фестивале, приложении
Фразы для обработки находятся в столбце «Тексты» (J), а желаемые результаты — в столбце «Замененные тексты» (K).
Некоторые пояснения:
- В зависимости от набора и принадлежности списка к текстам применяются значения поиска и замены;
- Как видно выше в таблице, ячейки К8 и К9 различаются по принадлежности, а не по списочному набору - поэтому значение
Factsзаменяется наFacts about Animeтолько дляSite 1(К9, а не К8) (в Таблице 1 это значение во включениях и применяется к текстам только при наличии совпадений по принадлежности); - Другой пример с ячейками К11 и К12: в ячейке К11 заменяется значение Факты (т.к. оно находится во включениях), а в ячейке К12 это значение игнорируется (т.к. оно находится в исключениях по данной принадлежности).
Возможно связанный вопрос
Формула, которую я пробовал (К8):
=IFERROR(LAMBDA(data,re,with,BYROW(data,LAMBDA(r,IF(r = "","",REDUCE(r,SEQUENCE(COUNTA(re)),LAMBDA(ini,v,REGEXREPLACE(ini,INDEX(re,v),INDEX(with,v))))))))(QUERY(J8:J,"SELECT Col1 WHERE Col1 IS NOT NULL"),QUERY(A8:I,"SELECT Col6 WHERE Col6 IS NOT NULL AND Col1 IS NOT NULL AND Col1 MATCHES '("&$A$5&")'"),QUERY(A8:I,"SELECT Col7 WHERE Col7 IS NOT NULL AND Col1 IS NOT NULL AND Col1 MATCHES '("&$A$5&")'")),)
Исправил, что смог. Если найду ошибки, исправлю их снова.
Можете ли вы показать более реалистичный пример, например, campaign вместо S_Word1 и так далее? Можете ли вы также показать некоторые исходные данные и желаемый результат после их преобразования, как указано в строках поиска и замены, которые вы показываете в приведенных выше таблицах? Также убедитесь, что вы не задаете вопрос XY.
Добавьте таблицу ожидаемых выходных данных, содержащую значения, ожидаемые по формуле для данного входного сигнала.
Починил это. Извините, я сам немного запутался.
Намного лучше. Если можете, пожалуйста отредактируйте, чтобы объяснить, почему k8 — это Anime Scenes,Facts,Memes from Anime, а k9 — это Anime Scenes,Facts about Anime,Memes (любые два примера объяснения). Это поможет немного облегчить понимание вашего вопроса.
Добавил некоторые пояснения.
Не могли бы вы определить желаемый результат на основе ваших данных? (т. е. не на основе ваших пробных формул). Определение вашего вопроса с помощью неудавшейся формулы имело бы смысл, если бы ваш вопрос заключался в том, почему определенная формула не работает или почему она дает определенную ошибку. Это не вариант. Кроме того, если вы предпочитаете использовать query, вы все равно можете добавить это требование (в одну строку).
@Argyll Я считаю, что раздел «Что я ожидаю увидеть § некоторые объяснения» проясняет проблему на основе входных данных, а не формул.
@TheMaster: «поэтому значение «Факты» заменяется только для Сайта 1 (в Таблице 1 это значение находится во включениях и применяется только к текстам, если есть совпадение по принадлежности)» <-- Я не знаю, что это значит . Я предполагаю, что пробные формулы могут объяснить это через раздел «Формула 3 должна работать следующим образом». Но пробные формулы — это именно то, что нужно. Это было бы плохо для передачи намерений.
Для справки: вам всегда следует добавлять то, что вы пробовали, но в этом случае это очень длинно и отвлекает от реального вопроса, и выглядит лучше, если его удалить.
@TheMaster Спасибо за исправления и объяснения (впервые здесь). Решения в ответах пока не проверял, свободного времени пока нет.
Учитывает ли формула, что включения/исключения могут содержать несколько сайтов (в одной ячейке, например, перечисляются через запятую)? Я имею в виду, что фильтрация должна проверять соответствие сайта, а не совпадение точного текста ячейки включения/исключения. Это необязательно, но возможно ли это?
На мой ответ: нет, но это должно быть возможно.
Пожалуйста, не предъявляйте новых требований после получения ответа. Задавайте только один вопрос в посте . Вместо этого опубликуйте новый вопрос.
В вопросе упоминается совпадение сайта/принадлежности. В ранних версиях вопроса в Таблице 1 даже перечислялись несколько сайтов, включенных и исключенных. Этот момент просто был упущен в процессе монтажа.
Обратите внимание, что сайты Stack Exchange — это не «справочные форумы» или «доски обсуждений», а вики-сайты, использующие формат вопросов и ответов. Кроме того, в сообщении недостаточно информации, чтобы ответить на новый вопрос о нескольких сайтах. Было бы лучше опубликовать новый вопрос, показывающий реалистичные образцы данных и введенные вами вручную желаемые результаты.
Действительно, Версия №3 действительно показывает несколько сайтов, но она была удалена не другими редакторами, а вами. После публикации ответов на основе текущей версии обычно не рекомендуется редактировать вопросы или добавлять новые требования. Сказав это, я уверен, что то, что вам нужно, легко, и вы сможете понять это самостоятельно.
Ответы 2
Используйте filter(), reduce() и substitute(), например:
=let(
get_, lambda(a, filter(a, len(H8:H))),
map(get_(H8:H), get_(I8:I), get_(J8:J), lambda(set, site, phrase, let(
includedSearchWords, filter(
B8:C,
A8:A = set,
(D8:D = site) + (D8:D = ""),
(E8:E <> site) + (E8:E = "")
),
searchFor, choosecols(includedSearchWords, 1),
replaceWith, choosecols(includedSearchWords, 2),
join(",", map(split(phrase, ","), lambda(text,
reduce(text, sequence(rows(searchFor)), lambda(t, i,
substitute(t, index(searchFor, i), index(replaceWith, i))
))
)))
)))
)
Формула переходит в ячейку K8 таблицы:
Видеть let(), лямбда(), карта(), Split(), фильтр(), selectcols(), последовательность(), уменьшить(), replace() и join().
Этот вариант не совсем подходит: 1) он сдвигает массив, если в столбце H не задан список; 2) не работает с регулярными выражениями, что очень важно (с теми формулами в столбцах F и G, а не в столбцах B и C).
Хм... формула в ответе точно соответствует желаемым результатам, которые вы показываете. Он действительно полностью игнорирует строки выборочных данных, в которых столбец H пуст. В примере данных таких строк нет, и я не думаю, что вы указали, каким должен быть результат, когда набор списков пуст. Лучше всего опубликовать новый вопрос, в котором будут указаны новые требования и показаны реалистичные образцы данных вместе с введенными вручную желаемыми результатами.
- Создайте фильтр для фильтрации по набору списков, включениям и исключениям, чтобы создать необходимый поиск и заменить регулярные выражения.
=TOROW(
FILTER($B$8:$C$16,
H8=$A$8:$A$16,//List set filter
(I8=$D$8:$D$16)+($D$8:$D$16 = ""),//Inclusions filter
($E$8:$E$16<>I8)+(I8 = "")// Exclusions filter
))
- Используйте последовательную замену регулярных выражений, используя
REDUCE, как описано здесь
=ARRAYFORMULA(
MAP(J8:J14,I8:I14,H8:H14,
LAMBDA(j,i,h,
REDUCE(j,
BYROW(
FILTER($B$8:$C$16,
h=$A$8:$A$16,
(i=$D$8:$D$16)+($D$8:$D$16 = ""),
($E$8:$E$16<>i)+(i = "")
),
LAMBDA(r, JOIN("🏝",r))
),
LAMBDA(a,c,
LET(
arr,TOCOL(SPLIT(c,"🏝",0)),
rgx,INDEX(arr,1),
repl,INDEX(arr,2),
REGEXREPLACE(a,rgx,repl)
)
)
)
)
)
)
Отсутствует проверка на частичное, а не полное совпадение сайтов во включениях и исключениях. Остальное, на первый взгляд, работает отлично. Серьезных тестов я еще не делал.
@StesMus Вам просто нужно адаптировать = функцию сравнения к REGEXMATCH функции здесь (i=$D$8:$D$16) и здесь ($E$8:$E$16<>i) в расчетах фильтра.
Рассмотрите возможность предоставления входных данных и ожидаемого результата в виде текстовой таблицы в вопросе. Нажмите здесь , чтобы легко создать таблицу, которую легко скопировать/вставить. Не делитесь ссылками, такими как электронные таблицы, которые делают вопрос бесполезным для других , или изображениями, которые трудно скопировать . Также обратите внимание, что ваш адрес электронной почты также может быть доступен всем, если вы поделитесь файлами Google.