Функция за получаване на интерполирани стойности с кубичен сплайн

Option Base 1

'******************** Cubic_Spline ****************

'

Function cubic_spline(input_column As Range, output_column As Range, x As Range)

' and y values, this function will smoothly interpolate

' a resulting output (y) value from a given input (x) value
' This counts how many points are in "input" and "output" set of data

Dim input_count As Integer

Dim output_count As Integer

input_count = input_column.Rows.Count

output_count = output_column.Rows.Count

' Next check to be sure that "input" # points = "output" # points

If input_count <> output_count Then

cubic_spline = "Something's messed up! " & _

"The number of indeces number of output_columnues don't match!"

GoTo out

ReDim yin(input_count) As Single

Dim c As Integer

For c = 1 To input_count

xin(c) = input_column(c)

yin(c) = output_column(c)

Next c

'''''''''''''''''''''''''''''''''''''''

'''''''''''''''''''''''''''''''''''''''

Dim n As Integer 'n=input_count

Dim i, k As Integer 'these are loop counting integers

Dim p, qn, sig, un As Single

ReDim u(input_count - 1) As Single

ReDim yt(input_count) As Single 'these are the 2nd deriv values

n = input_count

yt(1) = 0

u(1) = 0

sig = (xin(i) - xin(i - 1)) / (xin(i + 1) - xin(i - 1))

p = sig * yt(i - 1) + 2

yt(i) = (sig - 1) / p

u(i) = (yin(i + 1) - yin(i)) / (xin(i + 1) - xin(i)) - (yin(i) - yin(i - 1)) / (xin(i) - xin(i - 1))

u(i) = (6 * u(i) / (xin(i + 1) - xin(i - 1)) - sig * u(i - 1)) / p

Next i
qn = 0

un = 0

For k = n - 1 To 1 Step -1

yt(k) = yt(k) * yt(k + 1) + u(k)

Next k
''''''''''''''''''''

'now eval spline at one point

'''''''''''''''''''''

Dim klo, khi As Integer

Dim h, b, a As Single

' first find correct interval

klo = 1

k = khi - klo

If xin(k) > x Then khi = k Else klo = k

k = khi - klo

Loop While k > 1
h = xin(khi) - xin(klo)

a = (xin(khi) - x) / h

b = (x - xin(klo)) / h

y = a * yin(klo) + b * yin(khi) + ((a ^ 3 - a) * yt(klo) + (b ^ 3 - b) * yt(khi)) * (h ^ 2) / 6

out:

A B C D

In	TrueData	In	Spline
1	0.75	0.8	0.81
2	0	1	0.75
3	-1	1.2	0.69
4	3	1.4	0.61
5	4.75	1.6	0.48
		1.8	0.28
		2	0.00
		2.2	-0.37
		2.4	-0.76
		2.6	-1.06
		2.8	-1.17
		3	-1.00
		3.2	-0.48
		3.4	0.31
		3.6	1.23
		3.8	2.17
		4	3.00
		4.2	3.63
		4.4	4.07
		4.6	4.37
		4.8	4.59
		5	4.75
		5.2	4.91

Графика: