Excel - 我们如何使用 LAMBDA() 替换单元格中的多个字符或整个单词 [英] Excel - How can we replace multiple characters or whole words in a cell using LAMBDA()

问题描述

冒着跑题的风险，我决定做一些问答，因为我对 MS 向 Excel365 引入的新功能感到非常兴奋；LAMBDA() 函数.如果普遍认为这是题外话，请告诉我，我可以删除问答.

At risk of being off-topic I decided to do a little Q&A as I'm pretty excited about a new function MS is introducing to Excel365; the LAMBDA() function. If the general opinion is such that this is off-topic, please let me know and I can take down the Q&A.

LAMBDA() 函数基本上是您在 Excel 中创建自己的函数的方式.然后，您可以继续在整个工作簿中调用此函数.但是关于它的绝对伟大的事情(恕我直言)是它能够在函数内调用自己，因此是递归！

The LAMBDA() function is basically your way in Excel itself to create your own function. You then can go ahead and call this function throughout your entire workbook. But the absolute great thing (IMHO) about it is that it is able to call itself within the function, thus being recursive!

如果必须交换多个字符，或者从某些字符甚至整个单词中清除字符串，我们都知道繁琐的嵌套 SUBSTITUTE() 函数.所以问题是:我们如何避免这种情况并利用 LAMBDA() 来发挥我们的优势?

We all know the tedious nested SUBSTITUTE() functions if one has to swap multiple characters, or clear a string from certain characters and even whole words. So the question is: How do we avoid that and use LAMBDA() to our advantage?

推荐答案

那么让我们创建一个需要清理的字符串示例；a+b#c%d*e(f)g!h.

So let's create an example of a string that needs cleaning; a+b#c%d*e(f)g!h.

B1中的公式:

=SUBALL(A1,"+#%*()!","")

其中 SUBALL() 是我通过名称管理器"创建的 LAMBDA() 函数的名称；菜单，内容如下:

Where SUBALL() is the name of our LAMBDA() function I created through the "name manager" menu and reads as follows:

=LAMBDA(str,chrs,sub,IF(chrs="",str,SUBALL(SUBSTITUTE(str,LEFT(chrs),sub),RIGHT(chrs,LEN(chrs)-1),"")))

这个公式的核心是三个变量:

Core of this formula are the 3 variables:

• str - 对要清理的字符串的引用.
• chrs - 要替换​​的字符串.
• sub - 我们希望我们的字符被替换成什么?

• str - A reference to the string to be cleaned.
• chrs - A string of characters to be substituted.
• sub - What do we want our characters to be replaced with?

第四个参数是一个嵌套的IF().由于递归调用，我们需要摆脱无限循环的方法.因此我们测试是否chrs=".如果 TRUE 我们返回带有所有替换字符的最终字符串.如果FALSE，我们会再次调用该函数.最棒的是我们可以改变所有变量！这很重要，因为我们因此可以SUBSTITUTE()最左边的字符并且我们可以剪切替换字符串的相同字符.

The 4th parameter is a nested IF(). Because of the recursive calls we need a way out of an otherwise infinite loop. Therefor we test if chrs="". If TRUE we return the final string with all substituted characters. If FALSE we call the function again. The great thing here is we can alter all variables! This is important because we can thus SUBSTITUTE() the leftmost character and we can cut that same character of the string of replacements.

我们也可以将它提升一个档次并替换数组中的元素.例如:

We could also take it up a notch and replace element from an array. For example:

B1中的公式:

=SUBALL(A1,{"STR1","STR2","STR3"},"-")

请注意，您还可以硬编码单个值或引用单个单元格(或任何垂直范围).显然这会影响我们处理递归的方式.可能有更漂亮的方法，但我想出了:

Note, you can also hardcode a single value or reference a single cell (or any vertical range for that matter). Obviously this will impact the way we handle recursion. There may be a prettier way, but I came up with:

=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() 所以所有的值都被替换了.

The core of the function is still the same, but as mentioned we have now used an array. So our IF() will no longer check for empty value, but if there is only a single element in our array. If so, it will go do a single SUBSTITUTE(), but if not, then it will recursively call SUBALL() untill we have sliced enough values from our array through INDEX() so all values are substituted.

你有它，一个递归函数，你现在可以在整个工作簿中调用它.很酷.

There you have it, a recursive function that you can now call throughout your entire workbook. Pretty cool.

这篇关于Excel - 我们如何使用 LAMBDA() 替换单元格中的多个字符或整个单词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！