LAMBDA function.Create your own function.(Microsoft Excel)
The LAMBDA function is a new feature that was already added in February 2022 to create custom functions without the use of macros or VBA.
This is useful when the same formula is written in many cells.
- 1. How it works
- 2. Demonstrate
- 3. Helper functions.
- 4. Recursive function
- 5. Links
- 5.1. Microsoft Excel Functions Logical
- 5.2. Examples of Use
- 5.2.1. Formula to randomly extract a specified number of cells from a range of cells.
- 5.2.2. Formulas to extract text from a cell every specified number of letters.
- 5.2.3. Formulas to sort text in cells.
- 5.2.4. Formula to split text into cells one character at a time.
- 5.2.5. How to replace multiple texts with the SUBSTITUTE function.
- 5.2.6. Formula to split text into cells one character at a time.
- 5.2.7. Formula to extract only rows with values.
- 5.2.8. How to reverse text.
- 5.2.9. How to extract only numbers from text.
- 5.2.10. Calculating the median in a Pivot table. Its alternative method.
How it works
The areas in red are not described depending on the usage.
LAMBDA(Argument name1 to 253,Formula)(Argument1 to 253)
| Name | Omission | Explanation |
|---|---|---|
| Argument name1 to 253 | No argument | Argument names. |
| Formula | Required argument. It is possible to specify only the formula without arguments, but In that case, Name Manager is more effective. | |
| Argument1 to 253 | No argument |
Demonstrate
Basic
Validate by writing in the cell.
This is for verification purposes only, as the LET function is a better way to write formulas in cells.
After confirming that this method works properly, register it in the Name Manager as a function that can be used for the entire book.
Set up the following formula as an example.
The red part is the argument definition, the blue part is the formula, and the green part is the actual value given.
=LAMBDA(Price,Tax_rate,Price*Tax_rate)(1000,10%)
The argument part can also be cell-specified.
It is also possible to specify a range of cells and use a tally function.
=LAMBDA(Range,Rax_rate,SUM(Range)*Rax_rate)(D5:D7,C2)
Register in Name Manager and make available to the entire book.
Click on Formulas and then on Name Managemer.
Click New.
Register the contents of your own functions.
| Name | Explanation |
|---|---|
| Name | Specify a function name. |
| Scope | Available for the entire book or only for a specific sheet. |
| Comment | Specify a description. |
| Refers to | Specify the formula for the LAMBDA function. No validation argument is required. |
Click OK to make the custom function available.
Effective Situations (Benefits).
It is now possible to write multiple required formulas in a book with unique function names, reducing the time and risk of omission of changes and simplifying the formulas.
This simplifies formulas by reducing the time required to make changes and the risk of omissions.
Situations where other functions are more appropriate
If the formulas are lined up horizontally or vertically, the Spill function is more effective.
LET function is more effective if you only need to name the formulas.
Helper functions.
The LAMBDA function can be combined with Helper functions to create more sophisticated formulas.
- MAP Function. Decompose cell range and retrieve individual cells.
- REDUCE Function. Performs a cumulative calculation of any formula and obtains a result.
- SCAN function. Creates a range of cells to perform a cumulative calculation of any formula.
- MAKEARRAY Function. Create a range of cells and set formulas for individual cells.
- BYROW Function. Split a cell range by rows and tally by row.
- BYCOL Function. Split a cell range by columns and tally by column.
- ISOMITTED function. Function to define optional arguments
Recursive function
Although it is rarely used for general office purposes, it is possible to define a recursive function.a function that calls itself.
For example, to sum up a number from 1 to a specified number
The following function can be prepared. SumTotal is the function name.
=LAMBDA(value,IF(value>1,value + SumTotal(value-1),1))
Create your own functions available only in cells with the LET function.
For example, if the same formula appears many times in a cell, but the values or cell references are different, you may be able to simplify the formula by making it your own function.
In the following example, the tax amount is calculated using a custom function.
=LET(
TaxIncluded,LAMBDA(Sales,Rax_rate,Sales*Rax_rate),
TaxIncluded(D5,C2)+TaxIncluded(D6,C2)+TaxIncluded(D7,C2)
)
---