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 user-defined functions, and then call them over and over again.

The func statement of cell A1 defines a function named factorial, which is identified by A1. Its code is the code block of A1, and n is its parameter, which can be referenced in the code block. When SPL encounters the func cell, it will skip this code block and execute the subsequent code.

SPL will automatically register the self-defined functions, and then they can be used as regular ones. For example, There is func()function the function is used to calculate 10! 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 assign the corresponding variable (here is the n) as the value of thefill this parameter in A1, and then let the program jump to functionA1’s code block(here is the A1) to execute, that is, start to execute B1:D2.

We can understand the code of B1:D1, which can calculate the factorial of An1 and fill in B1. When An=101 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 10! of faunctor(10) 10!, the cell value of A3 is 10!.

That is to say, the user-definedcustom 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 defined func() function name to pass in the parameter to call this function.

func() User-defined function is also a function. It can be used in the same way as other SPL native 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 user-defined 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 user-defined function distance 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, just give them different names.

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 distance 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*