derligne = Sheets("Energie").Range("A" & Rows.Count).End(xlUp).Row
Application.Calculation = xlCalculationManual
Range("B2").FormulaArray = _
"=SUM(IF((Energie!R2C1:R" & derligne & "C1=""eclairage"")*(Energie!R2C3:R" & derligne & "C3=R1C)*(Energie!R2C2:R" & derligne & "C" & i & "=RC1),Energie!R2C4:R" & derligne & "C4,0))"
Range("B2").AutoFill Destination:=Range("B2:C2"), Type:=xlFillDefault
Range("B2:C2").AutoFill Destination:=Range("B2:C5"), Type:=xlFillDefault
Application.Calculation = xlCalculationAutomatic