Excel - Как мы можем заменить несколько символов или целых слов в ячейке с помощью LAMBDA ()
Рискуя быть не по теме, я решил задать несколько вопросов и ответов, так как я очень взволнован новой функцией, которую MS представляет в Excel365; функция LAMBDA(). Если общее мнение таково, что это не по теме, пожалуйста, дайте мне знать, и я могу снять вопросы и ответы.
Функция LAMBDA() — это, по сути, ваш способ в самом Excel создать свою собственную функцию. Затем вы можете продолжить и вызывать эту функцию во всей своей книге. Но самое замечательное (ИМХО) в этом то, что он может вызывать себя внутри функции, таким образом, будучи рекурсивным!
Все мы знаем утомительные вложенные функции SUBSTITUTE(), если нужно поменять местами несколько символов или очистить строку от определенных символов и даже целых слов. Итак, вопрос: как нам избежать этого и использовать LAMBDA() в наших интересах?
Ответы 1
РЕДАКТИРОВАТЬ 22-3-2022:
В соответствии с новой функциональностью можно выбрать:
=CONCAT(TEXTSPLIT(A1,{"+","#","%","*","(",")","!"}))
Я сохраню исходный ответ, используя LAMBDA() ниже:
Оригинальный ответ:
Итак, давайте создадим пример строки, которую нужно очистить; a+b#c%d*e(f)g!h.
Формула в B1:
=SUBALL(A1,"+#%*()!","")
Где SUBALL() — это имя нашей LAMBDA() функции, которую я создал через меню «менеджер имен» и читается следующим образом:
=LAMBDA(str,chrs,sub,IF(chrs = "",str,SUBALL(SUBSTITUTE(str,LEFT(chrs),sub),RIGHT(chrs,LEN(chrs)-1),"")))
Ядром этой формулы являются 3 переменные:
str— ссылка на очищаемую строку.chrs— строка символов для замены.sub- На что мы хотим заменить наших персонажей?
Четвертый параметр — это вложенный IF(). Из-за рекурсивных вызовов нам нужен выход из бесконечного цикла. Поэтому мы проверяем, если chrs = "". Если TRUE мы вернем конечную строку со всеми замененными символами. Если FALSE мы снова вызываем функцию. Самое замечательное здесь то, что мы можем изменить все переменные! Это важно, потому что таким образом мы можем SUBSTITUTE() вырезать самый левый символ и вырезать тот же символ из строки замен.
Мы также могли бы пойти дальше и заменить элемент из массива. Например:
Формула в B1:
=SUBALL(A1,{"STR1","STR2","STR3"},"-")
Обратите внимание, что вы также можете жестко закодировать одно значение или ссылку на одну ячейку (или любой вертикальный диапазон, если на то пошло). Очевидно, это повлияет на то, как мы обрабатываем рекурсию. Может быть, есть более красивый способ, но я придумал:
=LAMBDA(str,del,sub,IF(COUNTA(del)=1,SUBSTITUTE(str,@del,sub),SUBALL(SUBSTITUTE(str,@del,sub),INDEX(del,SEQUENCE(COUNTA(del)-1,,2)),sub)))
Ядро функции осталось прежним, но, как уже упоминалось, теперь мы использовали массив. Таким образом, наш IF() больше не будет проверять пустое значение, но если в нашем массиве есть только один элемент. Если это так, он сделает один SUBSTITUTE(), но если нет, то он будет рекурсивно вызывать SUBALL() до тех пор, пока мы не нарежем достаточное количество значений из нашего массива через INDEX(), чтобы все значения были заменены.
Вот она, рекурсивная функция, которую теперь можно вызывать во всей книге. Довольно круто.
@TobyPython, к сожалению, это проблема всех функций, основанных на версиях.
что означает @del - почему символ @?
Также я разместил вопрос о рекурсивных лямбда-выражениях на обзоре кода, возможно, опубликуйте свой там, если вы ищете другой способ сделать это - например, взять диапазон и использовать Offset для итерации по нему может быть более чистый подход, я' м не уверен
@Greedo, дело в том, что я знал о OFFSET(), но поскольку это изменчиво, я не знаю, сделает ли это LAMBDA() изменчивым. @ означает, что мы не используем весь массив, а берем первый элемент из нашего массива del (или строки). Без этого LAMBDA() портится. Надеюсь, это поможет. Спасибо за интерес к функции =)
Очень полезно, но единственная проблема будет, когда вы пишете для кого-то другого, а они не используют Office365.