英文:
What prevents a lambda function from evaluating on an array?
问题
我以为对标量输入值定义的lambda函数在面对数组输入时会自动迭代，但似乎并不是普遍如此。我想了解为什么，因为我对最终使它工作的定义不满意。
有一系列的值，我想测试每个单元格是否满足多个条件。例如，检查整数值是否大于最小值并且小于最大值。
使用AND函数，我得到了以下lambda函数：
=LAMBDA(x;AND(x>2;x<10))
然而，这个定义在用作数组函数时不起作用，只有在单独应用于每个单元格时才起作用。
我尝试了不同的方法，并将lambda函数纯粹定义为数值表达式：
=LAMBDA(x;(x>2)*(x<10)=1)
这个定义在数组上起作用，但我觉得它通常不够可读。
为什么带有AND的定义不起作用？
英文:
I thought that a lambda function defined for scalar input values would automatically iterate through an array when faced with an array input, but it seems that it's not universally true. I want to understand why, because I'm not happy with the definition I ended up with to make it work.
There is a range of values and I want to test each cell for multiple conditions. E.g. check whether an integer values is greater than min and less then max.
Using the AND function I ended up with the following lambda:
=LAMBDA(x;AND(x>2;x<10))
however, this definition won't work when used as an array function, only when applied on each individual cell separately.
I have tried a different approach and defined the lambda purely as a numerical expression:
=LAMBDA(x;(x>2)*(x<10)=1)
this definition works on an array, but I find it generally less readable.
Why does the definition with AND not work?
答案1
得分: 2
我认为您的问题源于对LAMBDA如何工作的误解。Lambda是一个自定义函数，由预定义的函数使用。该预定义函数遍历数组并将LAMBDA函数应用于迭代的每个元素。例如，如果您有一个二维数组，该预定义函数可以取每一列以获得每一列的结果。或者它可以取表格的每个元素以获得另一个表格作为结果。
在您的情况下，您想要迭代遍历表格的每一行以获得一列结果。在您的情况下，该预定义函数是BYROW。
因此，您的情况下的正确LAMBDA公式是：
=BYROW(A2:A6, LAMBDA(x, AND(x>2, x<10)))
函数AND评估所有单独结果的数组。要将该函数应用于每个元素，这种情况下是每一行，您必须使用另一个LAMBDA函数。
我建议观看这个视频。这是我所知道的最好的LAMBDA教程：
Excel is Fun: All Important Excel Lookup Formulas: Excel Worksheet, Power Query & DAX – 28 Examples!
祝您愉快！
英文:
I think that your problem stems from the misunderstanding on how LAMBDA works. Lambda is a custom function that is used by predefined function. That predefined function iterate thru an array and applies the LAMBDA function to each element of iteration. For example, if you have a 2D array, that predefined function can take each column to get a result per each column. Or it can take each element of the table to get another table as a result.
In your case, you'd want to iterate thru each row of your table to get a column of results. That predefined function in your case is BYROW.
Therefore, the correct LAMBDA formula for your situation is:
=BYROW(A2:A6,LAMBDA(x,
AND(x>2,x<10)))
The function AND evaluates the array of all individual results. To get the function applied to each element, in this case, a row, you have to use another LAMBDA function.
I suggest watching this video. Is the best LAMBDA tutorial I know:
Excel is Fun: All Important Excel Lookup Formulas: Excel Worksheet, Power Query & DAX – 28 Examples!
Enjoy!
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论