Excel-Python对数据进行线性回归

一、利用Excel的数据分析实现线性回归

1.1、添加数据分析工具

选择分析工具库和分析工具库-VBA，点击转到后点击确定

1.2、使用数据分析库完成线性回归练习

上述分析工具添加完成后，点击工具栏的数据，然后点击右上角的数据分析

在弹出的选项框中选择回归

自行选择合适的输入数据作为XY值
残差选择线性拟合图

输出结果如下图：

若坐标轴起点为0，图像显示较为怪异，可以通过设置修改坐标轴起点，具体操作如下：
点击坐标轴，在弹出的选项中选择图片箭头指示，可自主调节最大最小值以及坐标轴单位间隔

1.3、选择添加趋势线

点击选中原始数据，单击鼠标右键，选择添加趋势线

选择线性，显示R值，显示公式

设置完成后图表显示如下

1.4、对200组、2000组数据的分析

结果如下：
200组：

2000组：

二、Python编程实现线性回归

输入以下代码：

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import math
p=pd.read_excel('D:\Pythonwork\weights_heights.xls','weights_heights')
#输入保存的excel路径
#读取20行数据
p1=p.head(20)
x=p1["Height"]
y=p1["Weight"]
# 平均值
x_mean = np.mean(x)
y_mean = np.mean(y)
#x（或y）列的总数（即n）
xsize = x.size
zi=((x-x_mean)*(y-y_mean)).sum()
mu=((x-x_mean)*(x-x_mean)).sum()
n=((y-y_mean)*(y-y_mean)).sum()
# 参数a b
a = zi / mu
b = y_mean - a * x_mean
#相关系数R的平方
m=((zi/math.sqrt(mu*n))**2)
# 这里对参数保留4位有效数字
a = np.around(a,decimals=4)
b = np.around(b,decimals=4)
m = np.around(m,decimals=4)
print(f'回归线方程:y = {a}x +({b})') 
print(f'相关回归系数为{m}')
#借助第三方库skleran画出拟合曲线
y1 = a*x + b
plt.scatter(x,y)
plt.plot(x,y1,c='r')

运行结果如下：

200组数据：

2000组数据：

三、Python借助skleran库

输入以下代码：

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import math
p=pd.read_excel('weights_heights.xls','weights_heights')
#读取20行数据
p1=p.head(20)
x=p1["Height"]
y=p1["Weight"]
# 平均值
x_mean = np.mean(x)
y_mean = np.mean(y)
#x（或y）列的总数（即n）
xsize = x.size
zi=((x-x_mean)*(y-y_mean)).sum()
mu=((x-x_mean)*(x-x_mean)).sum()
n=((y-y_mean)*(y-y_mean)).sum()
# 参数a b
a = zi / mu
b = y_mean - a * x_mean
#相关系数R的平方
m=((zi/math.sqrt(mu*n))**2)
# 这里对参数保留4位有效数字
a = np.around(a,decimals=4)
b = np.around(b,decimals=4)
m = np.around(m,decimals=4)
print(f'回归线方程:y = {a}x +({b})') 
print(f'相关回归系数为{m}')
#借助第三方库skleran画出拟合曲线
y1 = a*x + b
plt.scatter(x,y)
plt.plot(x,y1,c='r')

200组：

2000组

四、总结

本次实验分别用Excel、Python、Python skleran库对数据求解线性回归方程和回归系数。相比较而言，Excel上进行数据分析十分直观而且方便，而利用Python编程则略显繁琐。本次实验对线性回归有了更加深刻地理解，扩展了自己的知识面。

五、参考资料

https://blog.csdn.net/weixin_56102526/article/details/120495151
https://blog.csdn.net/jynyyhd/article/details/129461973