Code add-ins nội suy một chiều, hai chiều cho excel.

Code add-ins nội suy một chiều, hai chiều cho excel.

Toàn bộ code VB tạo add-ins nội suy một chiều phương ngang (N1N), nội suy một chiều phương đứng (N1D) và nội suy hai chiều N2C cho excel. Để sử dụng luôn tải file tại đây (pass thuyloivn.com)

Function N1N(vungtra As Range, X As Double, cot As Integer) As Double

'ham noi suy 1 chieu phuong ngang

Dim ktra As Boolean

Dim i As Integer

Dim x1 As Double, x2 As Double, y1 As Double, y2 As Double

    For i = 1 To vungtra.Cells.Count

    kiemtra = False

        If vungtra.Cells(i, 1) <= X And vungtra.Cells(i + 1, 1) > X Then

            x1 = vungtra.Cells(i, 1)

            x2 = vungtra.Cells(i + 1, 1)

            y1 = vungtra.Cells(i, cot)

            y2 = vungtra.Cells(i + 1, cot)

            N1N = (y2 - y1) * (X - x1) / (x2 - x1) + y1

            ktra = True

        Exit For

        End If

    Next i

If ktra = False Then

If MsgBox("www.thuyloivn.com: Gia tri can tim ko nam trong bang tra", vbOKOnly, "ThS. Doan Ngoc Tu") = vbOK Then

End

End If

Exit Function

End If

End Function

Function N1D(vungtra As Range, Y As Double, hang As Integer) As Double

'ham noi suy 1 chieu phuong dung

Dim ktra As Boolean

Dim i As Integer

Dim x1 As Double, x2 As Double, y1 As Double, y2 As Double

    For j = 1 To vungtra.Cells.Count

    kiemtra = False

        If vungtra.Cells(1, j) <= Y And vungtra.Cells(1, 1 + j) > Y Then

            y1 = vungtra.Cells(1, j)

            y2 = vungtra.Cells(1, j + 1)

            x1 = vungtra.Cells(hang, j)

            x2 = vungtra.Cells(hang + 1, j)

            N1D = (x2 - x1) * (Y - y1) / (y2 - y1) + x1

            ktra = True

        Exit For

        End If

    Next j

If ktra = False Then

If MsgBox("www.thuyloivn.com: Gia tri can tim ko nam trong bang tra", vbOKOnly, "ThS. Doan Ngoc Tu") = vbOK Then

End

End If

Exit Function

End If

End Function

Function N2C(vungtra As Range, X As Double, Y As Double) As Double

'ham noi suy 2 chieu

Dim ktra As Boolean

Dim i As Integer, j As Integer

Dim x1 As Double, x2 As Double, y1 As Double, y2 As Double

Dim a11 As Double, a12 As Double, a21 As Double, a22 As Double

Dim t1 As Double, t2 As Double

For j = 2 To vungtra.Cells.Count

    If vungtra.Cells(1, j) <= Y And vungtra.Cells(1, j + 1) > Y Then

            y1 = vungtra.Cells(1, j)

            y2 = vungtra.Cells(1, j + 1)

            For i = 2 To vungtra.Cells.Count

            ktra = False

                If (vungtra.Cells(i, 1) <= X And vungtra.Cells(i + 1, 1) > X) Then

                    x1 = vungtra.Cells(i, 1)

                    x2 = vungtra.Cells(i + 1, 1)

                    a11 = vungtra.Cells(i, j)

                    a12 = vungtra.Cells(i, j + 1)

                    a21 = vungtra.Cells(i + 1, j)

                    a22 = vungtra.Cells(i + 1, j + 1)

                    t1 = (a12 - a11) * (Y - y1) / (y2 - y1) + a11

                    t2 = (a22 - a21) * (Y - y1) / (y2 - y1) + a21

                    N2C = (t2 - t1) * (X - x1) / (x2 - x1) + t1

                Exit For

                End If

            Next i

            ktra = True

    Exit For

    End If

Next j

If ktra = False Then

If MsgBox("www.thuyloivn.com: Gia tri can tim ko nam trong bang tra", vbOKOnly, "ThS. Doan Ngoc Tu") = vbOK Then

End

End If

Exit Function

End If

End Function

Đoàn Ngọc Tứ