• Excel能完成一般办公中绝大多数的数据分析工作，但是当数据量大、数据表格多时，可借助Python中功能丰富而强大的第三方模块来提高工作效率。本章将讲解如何利用pandas、xlwings等模块编写Python代码，快速完成排序、筛选、分类汇总、相关性分析、回归分析等数据分析工作。

105排序一个工作表中的数据（方法一）

• 如下图所示为工作簿“销售表.xlsx”的工作表“总表”中的数据表格。本案例要通过Python编程对表格按指定列进行排序。
  

• 第3行代码用于对读取的数据按照“利润”列进行降序排序，读者可根据实际需求修改列名。如果要做升序排序，则将参数ascending设置为True。如果想要先按“利润”列做降序排序，遇到相同的利润值时再按“销售金额”列做降序排序，可将该行代码修改为“data=data.sort_values(by=[‘利润’，‘销售金额’]，ascending=False)”。

106 排序一个工作表中的数据（方法二）

• 在案例105中，使用pandas模块操作数据后，数据的格式设置会丢失。如果想要保持格式不变，可结合使用pandas模块和xlwings模块来完成排序。

107 排序一个工作簿中所有工作表的数据

• 在案例105和案例106的基础上，可批量完成多个工作表数据的排序。现在需要对所有工作表的数据按“销售金额”列做降序排序。

108 排序多个工作簿中同名工作簿的数据

• 先要对工作表“销售金额”中的数据按“销售金额”列做降序排列。

109 根据单个条件筛选一个工作表中的数据

• 筛选是最常用的数据分析工具之一。本案例要通过Python编程对一个工作表中的数据按单个筛选条件进行筛选。

110 根据多个条件筛选一个工作表中的数据

111 筛选一个工作簿中所有工作表的数据

112 筛选一个工作簿中所有工作表的数据并汇总

• 案例111将筛选出的数据存放在不同的工作表中，本案例则要将筛选出的数据汇总存放在一个工作表中。

113 分类汇总一个工作表

• 案例059使用pandas模块中的groupby()函数实现了数据分组，本案例要在此基础上实现数据的分类汇总，即先分组，再对组内数据进行求和、求平均值、计数等汇总运算。

• 用groupby()函数对数据进行分组后，接着使用sum()函数对各组数据进行求和运算。如果要进行其他方式的汇总运算，如求平均值、计数、求最大值、求最小值，可以分别使用mean()、count()、max()、min()函数。

114 对一个工作表求和

• 如果不需要对工作表中的数据进行分类汇总，而是直接做求和运算，可以使用pandas模块中的sum()函数实现。

• 用于求和的sum()函数也可以修改为mean()、count()、max()、min()等函数来完成其他类型的统计运算。

115 对一个工作簿的所有工作表分别求和

• 将用于求和的sum()函数修改为mean()、count()、max()、min()等函数来完成其他类型的统计运算。本案例要将求和结果放在“采购金额”列最后一个单元格下方的单元格中，但是每个工作表的数据行数不一定相同，所以先通过第9行和第10行代码获取相关单元格的列号和行号，再通过第11行和第12行代码写入所需内容。

116 在一个工作表中制作数据透视表

• Excel中的数据透视表能快速汇总大量数据并生成报表，是工作中分析数据的好帮手。虽然在Excel中制作数据透视表的过程不算复杂，但是操作步骤也不少。如果想要通过Python编程制作数据透视表，就需要掌握pandas模块中的pivot_table()函数。

• 第7行代码是制作数据透视表的核心代码。其中“销售数量”和“销售金额”是数据透视表的值字段，“产品名称”是数据透视表的行字段，可根据实际需求修改；‘sum’是指使用pandas模块中的sum()函数对值字段进行求和，可根据实际需求修改为’mean’、‘count’、‘max’、'min’等其他统计函数。
• 第10行代码中的A1是指在工作表中写入数据透视表的起始单元格，读者可根据实际需求修改为其他单元格。
• 第7行代码中的pivot_table()是pandas模块中的函数，用于创建一个电子表格样式的数据透视表。函数的第1个参数用于指定数据透视表的数据源；参数values用于指定值字段；参数index用于指定行字段；参数aggfunc用于指定汇总计算的方式，如’sum’（求和）、‘mean’（求平均值），如果要设置多个值字段的计算方式，可使用字典的形式，其中字典的键是值字段，值是计算方式；参数fill_value用于指定填充缺失值的内容，默认不填充；参数margins用于设置是否显示行列的总计数据，为False时不显示；参数margins_name用于设置总计数据行的名称。
  

117 使用相关系数判断数据的相关性

• 如下页图所示为某公司的产品销售利润、广告费用和成本费用数据，现要判断产品销售利润与哪些费用的相关性较大。在Excel中，可以使用CORREL()函数和相关系数工具来分析数据的相关性。在Python中，则可以使用pandas模块中DataFrame对象的相关系数计算函数——corr()。

• 第2行代码用于读取工作簿“销售额统计表.xlsx”的第1个工作表中的数据，并使用“序号”列的数据作为行索引。
• 第3行代码用于计算第2行代码读取的数据中任意两个变量之间的相关系数。如果只想判断某个变量与其他变量之间的相关性，可将第3行代码修改为“result=data.corr()[‘销售利润（万元）’]”，它表示计算销售利润与其他变量之间的相关系数
• 运行本案例的代码后，会得到如下所示的相关系数矩阵。第4行第2列的数值为0.985442，表示销售利润与广告费用的相关系数，其余数值的含义依此类推。需要说明的是，矩阵中从左上角至右下角的对角线上的数值都为1，这个1没有实际意义，因为它表示变量自身与自身的相关系数，自然是1。从该矩阵可以看出，销售利润与广告费用之间存在较强的线性正相关，而与成本费用之间的相关性较弱。
  

118 使用描述统计和直方图制定目标

• 某公司计划对销售员实行目标管理。为了制定出科学且合理的销售目标，销售主管从几百名销售员的销售额数据中随机抽取了部分数据，作为制定销售目标的依据，如下页图所示。通过仔细观察可以发现，有很大一部分数据都落在一定的区间内，因此，可以运用Excel中的描述统计工具获取这批数据的平均数、中位数等指标，从而估算出销售目标。本案例则要通过Python编程对销售额数据进行分组并绘制直方图，然后通过进一步分析，制定出合理的销售目标。
  

• 第5行代码用于计算数据的个数、平均值、最大值和最小值等描述统计数据。第6行代码用于将“销售额（万元）”列的数据分为6个均等的区间，第8行代码用于统计各个区间的人数。第9行和第10行代码将第5、6、8行代码的分析结果整理成数据表格。
• 第11～18行代码完成直方图的绘制。其中最核心的是第14行代码，它使用Matplotlib模块中的hist()函数绘制直方图，绘制时将数据平均划分为6个区间（bins=6），与第6行代码所做的分组统计保持一致，此外还适当设置了直方图中柱子边框的颜色和粗细，以提高图表的可读性。第15行代码将绘制直方图的过程中划分区间得到的端点值标注在x轴上。
• 第19～25行代码用于打开工作簿“员工销售业绩表.xlsx”，在第1个工作表中写入分析结果，并以图片的形式插入绘制的直方图。其中第22行和第23行代码中的单元格E1和H1为要写入数据的区域左上角的单元格，可根据实际需求修改为其他单元格。
• （1）第2行代码中导入的Matplotlib模块是一个绘图模块，第7章将详细介绍该模块的用法。
• （2）第5行代码中的describe()是pandas模块中的函数，对于一维数组，describe()函数会返回一系列描述统计数据，如count（个数）、mean（平均值）、std（标准差）、min（最小值）、25%（下四分位数）、50%（中位数）、75%（上四分位数）和max（最大值）。
• （3）第6行代码中的cut()是pandas模块中的函数，用于对数据进行离散化处理，也就是将数据从最大值到最小值进行等距划分。函数的第1个参数是要进行离散化的一维数组；第2个参数如果为整数，表示将第1个参数中的数组划分为多少个等间距的区间，如果为序列，表示将第1个参数中的数组划分在指定的序列中。

119　拟合回归方程并判断拟合程度

• 如下图所示为某公司某年每月的销售额和在两种渠道投入的广告费，如果现在需要根据广告费预测销售额，可以使用Excel中的回归分析工具拟合出线性回归方程，并通过计算R2值判断方程的拟合程度。本案例要使用Scikit-Learn模块的LinearRegression()函数快速拟合出线性回归方程，然后使用score()函数计算R2值。
  

120　使用回归方程预测未来值

• 案例119中通过计算R2值知道了方程的拟合程度较高，接下来就可以利用这个方程进行预测。假设某月在电视台和视频门户投入的广告费分别为30万元和40万元，下面通过Python编程预测该月的销售额。

• 第10行代码使用f-string方法拼接字符串。其中{model_intercept：+}表示在拼接截距时，不论截距值是正数还是负数，都显示相应的正号或负号。
  

现在有三个工作表，如下：

并且每个工作表中的内容如下：

需要将这三个工作簿中的分数这一列合并到一起，最终实现效果如下图：