Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Me.Range("B23:B24")) Is Nothing Then Exit Sub

    On Error GoTo ErrHandler
    Application.EnableEvents = False

    CalculatePowerCorrection

ExitHandler:
    Application.EnableEvents = True
    Exit Sub

ErrHandler:
    Me.Range("B25").Value = "Ошибка: " & Err.Description
    Resume ExitHandler

End Sub

Public Sub CalculatePowerCorrection()

    Dim G As Double, P As Double
    Dim r As Long, c As Long
    Dim exactPressureCol As Long

    Dim G1 As Double, G2 As Double
    Dim P1 As Double, P2 As Double

    Dim Q11 As Double, Q12 As Double
    Dim Q21 As Double, Q22 As Double
    Dim QG1 As Double, QG2 As Double

    If Not IsNumeric(Me.Range("B23").Value) Or Not IsNumeric(Me.Range("B24").Value) Then
        Me.Range("B25").ClearContents
        Exit Sub
    End If

    G = CDbl(Me.Range("B23").Value)
    P = CDbl(Me.Range("B24").Value)

    If G < Me.Range("A2").Value Or G > Me.Range("A21").Value Then
        Me.Range("B25").Value = "Расход вне диапазона"
        Exit Sub
    End If

    If P < Me.Range("B1").Value Or P > Me.Range("F1").Value Then
        Me.Range("B25").Value = "Давление вне диапазона"
        Exit Sub
    End If

    r = FindRowInterval(G)

    exactPressureCol = FindExactPressureColumn(P)

    If exactPressureCol > 0 Then
        G1 = Me.Cells(r, 1).Value
        G2 = Me.Cells(r + 1, 1).Value

        Q11 = Me.Cells(r, exactPressureCol).Value
        Q21 = Me.Cells(r + 1, exactPressureCol).Value

        Me.Range("B25").Value = Q11 + (G - G1) / (G2 - G1) * (Q21 - Q11)
        Exit Sub
    End If

    c = FindColumnInterval(P)

    G1 = Me.Cells(r, 1).Value
    G2 = Me.Cells(r + 1, 1).Value

    P1 = Me.Cells(1, c).Value
    P2 = Me.Cells(1, c + 1).Value

    Q11 = Me.Cells(r, c).Value
    Q12 = Me.Cells(r, c + 1).Value
    Q21 = Me.Cells(r + 1, c).Value
    Q22 = Me.Cells(r + 1, c + 1).Value

    QG1 = Q11 + (P - P1) / (P2 - P1) * (Q12 - Q11)
    QG2 = Q21 + (P - P1) / (P2 - P1) * (Q22 - Q21)

    Me.Range("B25").Value = QG1 + (G - G1) / (G2 - G1) * (QG2 - QG1)

End Sub

Private Function FindRowInterval(ByVal G As Double) As Long

    Dim i As Long

    If G = Me.Range("A21").Value Then
        FindRowInterval = 20
        Exit Function
    End If

    For i = 2 To 20
        If G >= Me.Cells(i, 1).Value And G <= Me.Cells(i + 1, 1).Value Then
            FindRowInterval = i
            Exit Function
        End If
    Next i

End Function

Private Function FindExactPressureColumn(ByVal P As Double) As Long

    Dim j As Long

    For j = 2 To 6
        If Abs(P - Me.Cells(1, j).Value) < 0.0000001 Then
            FindExactPressureColumn = j
            Exit Function
        End If
    Next j

End Function

Private Function FindColumnInterval(ByVal P As Double) As Long

    Dim j As Long

    If P = Me.Range("F1").Value Then
        FindColumnInterval = 5
        Exit Function
    End If

    For j = 2 To 5
        If P >= Me.Cells(1, j).Value And P <= Me.Cells(1, j + 1).Value Then
            FindColumnInterval = j
            Exit Function
        End If
    Next j

End Function