假设x在A列,y在B列,n=10,则r为:
=SUMPRODUCT((A1:A10-AVERAGE(A1:A10))*(B1:B10-AVERAGE(B1:B10)))/SQRT(SUMPRODUCT((A1:A10-AVERAGE(A1:A10))^2)*SUMPRODUCT((B1:B10-AVERAGE(B1:B10))^2))
注:
分子:=SUMPRODUCT((A1:A10-AVERAGE(A1:A10))*(B1:B10-AVERAGE(B1:B10)))
分母:=SQRT(SUMPRODUCT((A1:A10-AVERAGE(A1:A10))^2)*SUMPRODUCT((B1:B10-AVERAGE(B1:B10))^2))