Предыстория: меня попросили исправить список данных с длинным текстовым столбцом, в который ежедневно добавляется значительное количество новых записей, поэтому мне нужно это автоматизировать.
Что: Мне нужно переместить части текста в начало на основе списка ОТ/ТО. Хитрость в том, что может быть перемещено более одного текста, и некоторые элементы в списке содержат символы, перекрывающие строки выше - проверьте ссылку на таблицу.
Попытка: я думал использовать функцию сокращения, чтобы в конце удалить тексты, найденные в FROM/TO, но в моей голове расчет начинается с поиска тестов в таблице Справочник и записи их в начале. Попробовал использовать для этого ПОИСК, но они поняли, что это, вероятно, должно быть итеративным, то есть я нахожу первый текст, затем обновляю исходный текст, чтобы удалить то, что я уже нашел, прежде чем искать следующий элемент в «Ссылка — для сравнения». " стол.
Примечания: а) Я могу добавить вспомогательные столбцы, чтобы облегчить расчеты в моей электронной таблице. б) Насколько я мог проверить, максимум — 4 замены текста в ячейке, но в подавляющем большинстве строк будет от 1 до 2 замен.
Доступен тестовый файл: https://file.io/bdcoaRMhsPvz
Опубликованная вами ссылка на файл показывает, что файл удален, поэтому пользователям приходится записывать его вручную, чтобы найти рабочее решение для вашего запроса.
Это странно, поскольку я проверил ссылку перед публикацией. В любом случае, я загрузил файл еще раз: file.io/tJB2/download/tIgVW09KulB0
Неважно, я сделал это вручную и опубликовал ответ. А скачать можно здесь
Возможно, есть более компактный и элегантный способ сделать это, однако я придумал следующую формулу, которая действительно работает с данными в ОП:
• Формула, используемая в ячейке B4
=LET(
α, TEXTSPLIT([@original],Reference[to compare]),
δ, TEXTSPLIT([@original],α,,1),
φ, TEXTJOIN(" + ",1,XLOOKUP(UNIQUE(δ,1),Reference[to compare],Reference[to replace],""))&" - ",
IFERROR(TRIM(CONCAT(TOCOL(VSTACK(φ,α),2,1))),[@original]))
Альтернативно, можно использовать VLOOKUP()
вместо XLOOKUP()
.
=LET(
α, TEXTSPLIT([@original],Reference[to compare]),
δ, TEXTSPLIT([@original],α,,1),
φ, TEXTJOIN(" + ",1,VLOOKUP(UNIQUE(δ,1),Reference,2,0))&" - ",
IFERROR(TRIM(CONCAT(TOCOL(VSTACK(φ,α),2,1))),[@original]))
Если не использовать Structured References
, то однажды можно было бы использовать следующее, чтобы разлить его с помощью LAMBDA()
вспомогательной функции MAP()
=MAP(List[original], LAMBDA(Σ, LET(
α, TEXTSPLIT(Σ,Reference[to compare]),
δ, TEXTSPLIT(Σ,α,,1),
φ, TEXTJOIN(" + ",1,XLOOKUP(UNIQUE(δ,1),Reference[to compare],Reference[to replace],""))&" - ",
IFERROR(TRIM(CONCAT(TOCOL(VSTACK(φ,α),2,1))),Σ))))
NOTES:There is typo in the given desired result for cell B6
, if i am not mistaken.
Эй, Маюх, только что проверил твои формулы, это просто великолепно, большое спасибо. Я вижу, что у меня было неправильное мышление, чтобы подойти к проблеме, а также я не знал, что textsplit принимает массив в качестве параметра и проверяет каждое значение — еще раз спасибо.
@FabricioAntonello рад это знать, никаких проблем. Попробуйте попрактиковаться, вы тоже научитесь! Да TEXTSPLIT()
если бы у вас было несколько разделителей, что бы вы сделали? Не сделаете ли вы что-нибудь подобное --> =TEXTSPLIT(A2,{"-","|","~"},,1)
Честно говоря, я никогда не пробовал несколько разделителей. Я думаю, что позволил помощнику функции Excel (всплывающее окно при нажатии на Fx) обмануть меня, поскольку массивы не упоминаются в качестве допустимого параметра для большинства функций, TEXTSPLIT — одна из них. Но в целом ваш подход был блестящим, не думаю, что я бы подошел к этому вопросу таким образом. Поздравляю.
@FabricioAntonello, посмотрите здесь, есть пример, опубликованный MSFT, он также вам поможет. Еще раз спасибо!
Возможно, было бы проще сделать это с помощью VBA вместо формул.