SCAN function. Create a cell range for cumulative calculations.(Microsoft Excel)
The SCAN function is a new function added to Excel in February 2022.
It is a helper function to the LAMBDA function and creates a range of cells for cumulative calculations.
How it works
=SCAN (initial_value, array, lambda(accumulator, value))
| Name | Omission | Explanation |
|---|---|---|
| initial_value | 0 | Initial value for cumulative calculations. |
| array | Required argument. Cell range to be calculated. |
Demonstrate
In the example below, the initial value of 10,000 is gradually added with values from A2 to A4.
The first argument of the LAMBDA function (total) is the previous cell value, and the second argument (value) is the current cell value.
=SCAN(10000,A2:A4,LAMBDA(total,value,total+value))
乗算も可能です。
変化や複利計算で順番に表示することも可能です。
=SCAN(10000,A2:A4,LAMBDA(total,value,total*(1+value)))
These are calculations that can be handled by creating intermediate cells.
The SCAN function is useful when you want to reduce the number of intermediate cells and formulas, such as in a book that is network-shared and has many updaters.
The result of the function can be specified as an argument to other functions.
Difference between SCAN and REDUCE functions.
The SCAN and REDUCE functions both perform cumulative calculations, but the results of the formulas are different.
The SCAN function retrieves a range of cells, including intermediate calculations, while the REDUCE function retrieves only the last result.
---