Microsoft announces 'LAMBDA' that allows you to define your own custom functions in Excel's formal language
Microsoft's Excel development team announced on Friday, December 4, 2020 that the beta version of LAMBDA, which allows you to define your own custom functions using Excel's formal language, has been added.
Announcing LAMBDA
It was already possible to define custom functions using Excel, but in that case it was necessary to write in a completely different language such as JavaScript. On the other hand, LAMBDA is able to define custom functions in Excel's own formal language. Also, one function can call another.
In LAMBDA, if you give the defined function a unique name such as 'MYFUNCTION', you can call it just by writing MYFUNCTION on the sheet. In addition, LAMBDA is recursive, and you can call MYFUNCTION within the definition of MYFUNCTION. Microsoft says this mechanism allows you to build things that you couldn't do without JavaScript before.
For example, if you want to extract only the location value from the station ID where the location is encoded, as shown below ...
So far, you have to enter the following function. However, in this case, you have to find the error in the logic and manually correct the value each time, increasing the chance of making a mistake in the process. There was also the problem that the purpose was difficult to understand when viewed by anyone other than the creator of the formula.
= LEFT (RIGHT (B18, LEN (B18) -FIND ('-', B18)), FIND ('-', RIGHT (B18, LEN (B18) -FIND ('-', B18)))-1)
However, if you create the following function with the name 'GETLOCATION' using the LAMBDA function, you only need to correct one place even if an error occurs.
= LAMBDA (stationID, LEFT (RIGHT (stationID, LEN (stationID) -FIND ('-', stationID)), FIND ('-', RIGHT (stationID, LEN (stationID) -FIND ('-', stationID))) ) -1))
You can see the actual processing using the LAMBDA function in animation by clicking the image below.
You can also create a function with additional logic like this:
= XLOOKUP (GETLOCATION (B18), table1 [locations], table1 [tax])
In addition, LAMBDA can be recursed, so if you want to delete a specific character string, you can define the function 'REPLACECHARS' that 'refers to itself' to 'illegal characters'. If) is not included, it returns to textString, otherwise it is deleted every time the character of illegalChars appears '.' You can create an IF statement.
= LAMBDA (textString, illegalChars,
IF (illegalChars = '', textstring,
REPLACE CHARS (
SUBSTITUTE (textString, LEFT (illegalChars, 1), ''),
RIGHT (illegalChars, LEN (illegalChars) -1)
)))
You can see how LAMBDA uses recursive functions by clicking the image below.
To use LAMBDA, you need to apply for the Office Insider Program and access the beta version of Excel from the following.
Join the Office Insider Program
https://insider.office.com/ja-jp/join/windows
Related Posts:
in Software, Posted by darkhorse_log