0
380
VistasDefine a lambda function with infinite number of arguments
Some Excel native functions like VSTACK permit of infinite number of arguments, and they have an intellisense as follows:
I would like to know how to define such a function by LAMBDA.
I tried try = LAMBDA(array1, [array2], [array3], [array4], 123) by optional arguments, the number of arguments is not infinite, and the intellisense is not exactly the same:
1 Respuestas
Responde la pregunta0
Funny timing - I just worked on the same problem yesterday as I want to have access to the new functions, but I cannot do Early Adopters on my work PC. So I made a "home brew" version of each function. Here is VSTACK.
VSTACK =
LAMBDA( array1, array2, [array3], [array4], [array5], [array6], [array7], [array8],
LET(
pattern, MAX( 2,
3*NOT(ISOMITTED(array3)),
4*NOT(ISOMITTED(array4)),
5*NOT(ISOMITTED(array5)),
6*NOT(ISOMITTED(array6)),
7*NOT(ISOMITTED(array7)),
8*NOT(ISOMITTED(array8)) ),
stack, LAMBDA( array_1, array_2,
LET(
rows1, ROWS( array_1 ), rows2, ROWS( array_2 ),
columns1, COLUMNS( array_1 ), columns2, COLUMNS( array_2 ),
rSeq, SEQUENCE( rows1 + rows2 ),
cSeq, SEQUENCE(, MAX( columns1, columns2 ) ),
IF( ISOMITTED(array_1),
array_2,
IF( ISOMITTED(array_2),
array_1,
IF( rSeq <= rows1,
INDEX( IF( array_1 = "", "", array_1), rSeq, cSeq ),
INDEX( IF( array_2 = "", "", array_2), rSeq-rows1, cSeq ) ) ) ) )
),
SWITCH( pattern,
2, stack(array1,array2),
3, stack(stack(array1,array2),array3),
4, stack(stack(stack(array1,array2),array3),array4),
5, stack(stack(stack(stack(array1,array2),array3),array4),array5),
6, stack(stack(stack(stack(stack(array1,array2),array3),array4),array5),array6),
7, stack(stack(stack(stack(stack(stack(array1,array2),array3),array4),array5),array6),array7),
8, stack(stack(stack(stack(stack(stack(array1,array2),array3),array4),array5,array6),array7),array8), )
)
);
This is not an answer to your question. I know of no way to make a LAMBDA function with an infinite number of arguments, so I just made a crude but extensible way of adding more arguments without refactoring the whole thing.
NB: It is really crude, but this was a choice. I also thought of using recursion to avoid the nesting ad infinitum, but decided in the end that putting an iteration argument would be confusing and messy in comparison. My end objective was to produce "good enough".