Sub aaa()
lLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For n = 4 To lLastRow
On Error Resume Next
ActiveSheet.Range("P" & n) = Round(Application.AverageIfs(ActiveSheet.Range("C" & n & ":O" & n), ActiveSheet.Range("C" & n & ":O" & n), ">0"), 1)
ActiveSheet.Range("R" & n) = Sheets("Лист2").Range("Y" & n) + Sheets("Лист2").Range("AB" & n)
Next
End Sub

Sub aaa()
lLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For n = 4 To lLastRow
ActiveSheet.Range("R" & n) = Sheets("Лист2").Range("Y" & n) + Sheets("Лист2").Range("AB" & n)
Next
End Sub

=ЛЕВСИМВ(A1; ДЛСТР(A1)-4) & "-" & ПРАВСИМВ(A1; 3)

Sub DuplicateFile()
    newPath = ThisWorkbook.Path & "\" & "Волгоградский дивизион.xls"
    ThisWorkbook.SaveCopyAs newPath
    Workbooks.Open newPath
    
    'удаляем строки
    
    ActiveWorkbook.Close True
End Sub

=ЕСЛИ(И(C$1>=ИНДЕКС(Отпуск!$B$1:$B$74;ПОИСКПОЗ(ГОД(C$1);Отпуск!$A$1:$A$74;0));C$1<=ИНДЕКС(Отпуск!$K$1:$K$74;ПОИСКПОЗ(ГОД(C$1);Отпуск!$A$1:$A$74;0)));"";ЕСЛИ(B2="";ЕСЛИ(ПРОСМОТР(999999;$B2:B2)+1=21;1;ПРОСМОТР(999999;$B2:B2)+1);ЕСЛИ(B2+1=21;1;B2+1)))

=$F7<>$F6

Range("P2:Z" & Cells(Rows.Count, 1).End(xlUp).Row + 1).ClearContents
Range("F2:M" & Cells(Rows.Count, 1).End(xlUp).Row + 1).ClearContents
Range("A2:C" & Cells(Rows.Count, 1).End(xlUp).Row + 1).ClearContents

Set rngToClear = Union(Range("A2:C" & Cells(Rows.Count, 1).End(xlUp).Row + 1), _
                       Range("F2:M" & Cells(Rows.Count, 1).End(xlUp).Row + 1), _
                       Range("P2:Z" & Cells(Rows.Count, 1).End(xlUp).Row + 1))
rngToClear.ClearContents

=ФИЛЬТР($A$2:$A$12;ЕСЛИОШИБКА($A$2:$A$12;"")<>"")

=ЕСЛИОШИБКА(ИНДЕКС($A$2:$A$12;НАИМЕНЬШИЙ(ЕСЛИ(ЕСЛИОШИБКА(A2:A12;"")<>"";СТРОКА(A2:A12)-1;"");СТРОКА()-1));"")

Range("A3:P" & Cells(Rows.Count, 1).End(xlUp).Row + 1).ClearContents

Rows("3:250000").ClearContents

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True

=ИНДЕКС(Таблица1[[#Все];[Абсолютная глубина]]; ПОИСКПОЗ(N4;Таблица1[[#Все];[MD]];1))

Sub aaaaaaaabbb()
    Dim arr()
    Dim i As Long
    Dim q As Variant
    Dim matchFound As Boolean
    Dim numColumns As Long

    lLastRowA = Sheets("A").Cells(Rows.Count, 1).End(xlUp).Row
    lLastRowB = Sheets("B").Cells(Rows.Count, 1).End(xlUp).Row
    Set rngA = Sheets("A").Range("A2:C" & lLastRowA)
    Set rngB = Sheets("B").Range("A2:C" & lLastRowB)
    numColumns = rngA.Columns.Count
    
    ReDim arr(1 To lLastRowA, 1 To numColumns)
    
    For rA = 1 To rngA.Rows.Count
        matchFound = False
        For rB = 1 To rngB.Rows.Count
            If rngA(rA, 1) = q Then
                Exit For
            End If
            If rngA(rA, 1) = rngB(rB, 1) Then
                If rngA(rA, 3) - rngB(rB, 2) > 0 Then
                q = rngA(rA, 1)
                    Exit For
                End If
                matchFound = True
                Exit For
            End If
        Next rB
        
        If Not matchFound Then
            i = i + 1
            For j = 1 To numColumns
                arr(i, j) = rngA(rA, j)
            Next j
        End If
    Next rA
    
        Sheets("A").Range("A2:C" & lLastRowA).ClearContents
        Sheets("A").Range("A2").Resize(i, 3).Value = arr
End Sub