Still suppose there is no factorial function, now we want to calculate the number of combinations .

n and k are filled in A1 and B1 respectively, and the second, third and fourth lines calculate n!, k! and (n-k)! respectively.

This calculation is OK, but the almost same code has been written three times.

If the problem is more troublesome, for example, we need to calculate the coefficient sequence of the binomial theorem (that is, a line of Pascal triangle). The loop function can calculate multiple values in one statement, but it must be easy to write in the expression. If it needs several lines of code to realize the calculation, it has to use the loop statements:

In fact, most programming languages, including SPL, allow us to write the code to be repeated as our own functions, and then call them over and over again.

The func statement of cell A1 defines a function, which is identified by A1. Its code is the code block of A1. When SPL encounters the func cell, it will skip this code block and execute the subsequent code.

There is func() function in cell A3. The first parameter is the position of the user-defined function to be called. Here is A1, which means to call the function defined at A1. The next parameter 10 is the parameter to be passed into the user-defined function A1. SPL will fill this parameter in A1, and then let the program jump to A1’s code block to execute, that is, start to execute B1:D2.

We can understand the code of B1:D1, which can calculate the factorial of A1 and fill in B1. When A1 fills in 10, this code calculates 10!, and fill it in B1. Then the return statement of cell B2 returns the value of B1. At this time, the program will jump back to the place where the function was called, that is, A3, and has already got the return value of func() 10!, the cell value of A3 is 10!.

That is to say, the custom function A1 will calculate the factorial value of its parameter and return it.

Similarly, B3 will calculate 4!, C3 will calculate 6!, and finally, the number of combinations is calculated in D3.

The whole process seems a bit complicated, but in any case, the three factorial values are calculated using the same code, and there is no need to write the code three times.

To sum up: func statement is used to define a function, in which return statement is required to return the calculation result. Then use the func() function to pass in the parameter to call this function.

func() is a function. It can be used in the same way as other functions. It can be put in the expression to participate in the calculation.

The result can be calculated correctly in this way.

Moreover, after the custom function is defined, it can be used in the loop function.

The calculation of binomial coefficients also looks clearer.

Let’s take another practical example: finding outliers in a batch of points on a plane. The method is as follows: calculate the sum of the distances from each point to other points, and then sort them from large to small. The top 10% of the points are considered as outliers because they are relatively far away from other points.

In A5, a function is defined to calculate the distance between two points. It has four parameters, which are the abscissa and ordinate of two points respectively. Using Pythagorean theorem, the distance between two points can be calculated. When a user-defined function has more than one parameter, it will be filled from the definition cell to the right. This function is defined in A5, and its four parameters will be filled in A5, B5, C5 and D5 respectively. Thus, we will write the function body to the next line, i.e., cell B6.

Function definition can be anywhere, in front of or behind the main program will not affect the code execution.

In the main program, the abscissa and ordinate of 100 points are generated randomly. Then in cell B2, the user-defined function A5 can be called to calculate the sum of distances. This is a two-layer loop function. The inner layer calculates 100 distances and the sum (the distance between one point and itself is 0, adding one more will not make mistakes, and we don’t have to exclude the point itself), and the outer layer loops the 100 points. Note the difference between A2.~ and ~ in the inner loop.

Then use psort to sort and get the sequence numbers of the top 10% points.

SPL Programming - Preface
SPL Programming - 5.7 [Sequence as a whole] Lambda syntax*
SPL Programming - 6.2 [Reuse] Recursion*