Function NSZ(Xo, Yo, Z) As Single 'Ham Noi suy 2 bien
Dim i, j As Integer
cao = Z.Rows.Count 'Xac dinh so hang
Rong = Z.Columns.Count 'Xac dinh so cot
Dim A1, A2, B1, B2, Y, X, Tim As Single 'Cac diem chan
For i = 2 To cao
If Z(i, 1) <= Xo And Xo <= Z(i + 1, 1) Then
n = i
For j = 2 To Rong
If Z(1, j) <= Yo And Yo <= Z(1, j + 1) Then
m = j
Y = Z(1, m + 1) - Z(1, m)
X = Z(n + 1, 1) - Z(n, 1)
B1 = Z(n, m + 1)
B2 = Z(n + 1, m + 1)
A1 = Z(n, m)
A2 = Z(n + 1, m)
Tim = (A1 * (Z(1, m + 1) - Yo) * (Z(n + 1, 1) - Xo) + A2 * (Xo - Z(n, 1)) * (Z(1, m + 1) - Yo) + B1 * (Yo - Z(1, m)) * (Z(n + 1, 1) - Xo) + B2 * (Xo - Z(n, 1)) * (Yo - Z(1, m))) / (X * Y)
End If
Next j
Exit For
End If
Next i
NSZ = Tim
End Function
Function NS(SoX, X, Y) As Double
Dim i As Integer
Dim n, cao As Integer
cao = X.Rows.Count
For i = 1 To cao
If X(i) <= SoX And SoX <= X(i + 1) Then
n = i
NS = Y(n) + (SoX - X(n)) * (Y(n + 1) - Y(n)) / (X(n + 1) - X(n))
Exit For
End If
Next i
End Function
Function NSP(SoX, X, Y) As Double 'Ham noi suy parabol
Dim a, b, c, Ya, Yb, Yc, t As Double
Dim i, n, cao As Integer
cao = X.Rows.Count
For i = 1 To cao - 2
a = X(i)
b = X(i + 2)
If a <= SoX And SoX <= b Then
n = i
c = X(i + 1)
Ya = Y(n)
Yb = Y(n + 2)
Yc = Y(n + 1)
t = 2 * (SoX - a) / (b - a)
NSP = Ya + (Yc - Ya) * t + (Yb - 2 * Yc + Ya) * t * (t - 1) / 2
Exit For
End If
Next i
End Function
Option Explicit
Function NS_GPE(LookupRange As Range, X0 As Double, Y0 As Double) As Double
Dim Rd As Range, Rc As Range, Rng As Range
Dim Cot As Byte: Dim Yy As Double, Xx As Double
Set Rng = LookupRange
With Rng.Cells(1, 1)
Set Rd = .Resize(Rng.Rows.Count)
Set Rc = .Resize(, Rng.Columns.Count)
End With
With Application.WorksheetFunction
Cot = Rc.Find(.Lookup(Y0, Rc), , xlFormulas, xlWhole).Column
Set Rc = Rng.Cells(1, Cot)
Yy = Rng.Cells(1, Cot + 1).Value - Rng.Cells(1, Cot).Value
Set Rng = Rd.Find(.Lookup(X0, Rd))
Xx = Rng.Offset(1).Value - Rng.Value
Set Rd = Rng: Set Rng = Rd.Offset(, Cot - 1)
End With
NS_GPE = (Rng * (Rc.Offset(, 1) - Y0) * (Rng.Offset(1, 1 - Cot) - X0) _
+ Rng.Offset(1) * (X0 - Rd) * (Rc.Offset(, 1) - Y0) _
+ Rng.Offset(, 1) * (Y0 - Rc) * (Rd.Offset(1) - X0) _
+ Rng.Offset(1, 1) * (X0 - Rd) * (Y0 - Rc)) / (Xx * Yy)
End FunctionGiá trị 1.143 không có, nếu tìm gần đúng là 1.10, giá trị tương ứng cần tìm là 0.0194: dùng công thức
=LOOKUP(1.143,E9:E29,F9:F29)
