=СУММ(C4:C440) /СЧЁТЕСЛИ(C4:C440;">0")

 =ВПР(C$5;ДВССЫЛ("'[УРВ_2016_" & Name & ".xlsx]2016'!$A$5:$D$404");3;ЛОЖЬ) 

Workbooks.Open
Range().Find

Set ws1 = Worksheets("1")
Set ws2 = Worksheets("2")
ws2.Range("....").Value = ws1.Range("....").Value
.....
ws1.Rows("....").Delete Shift:=xlUp

i=1
For each R in wb1.Sheets("название").Range("D1:D" & lastRow)
If InStr("родс",R.Value)<>0 then 
arrR(i) = R.Value
i=i+1
end if
next R

Set wb2 = Workbooks.Open("C:\Users\Documents\Учет РОДС.xlsx")

wb2.Sheets("...").Range("C1:C" & i-1).Value = arrR

File_tg = Dir(address & "*xls")
Do While File_tg <> ""
    Workbooks.Open address & File_tg
    ...
    Workbooks(File_tg).Close False
    File_tg = Dir
Loop  

For Each H In ws.ChartObjects
H.Chart.Axes(xlCategory).MaximumScale = vmax: On Error Resume Next
H.Chart.Axes(xlCategory).MinimumScale = vmin: On Error Resume Next
Next H

 For Each H In ActiveSheet.ChartObjects
   H.Axes(xlCategory).MaximumScale = 42460
   H.Axes(xlCategory).MinimumScale = 42430
   Next H 

With ws.QueryTables.Add(Connection:= _
        "TEXT;\\...\" & file_date & ".csv" _
        , Destination:=ws.Range("J2"))
        .Name = file_date
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 866
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(4, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=True
    End With

ws.QueryTables(file_date).Delete

For i = 1 To ActiveWorkbook.Connections.Count
ActiveWorkbook.Connections.Item(1).Delete
Next