问题描述
由于我对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.
在Excel本身中，LAMBDA()函数基本上是您创建自己的 own 函数的方法.然后，您可以继续在整个工作簿中调用此函数.但是，关于它的绝对妙处(IMHO)是它能够在函数中调用自身，从而成为 recursive ！
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),"")))
此公式的核心是3个变量:
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()替换单元格中的多个字符或整个单词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！