⽤python让excel飞起来（数据处理与分析操作）
本⽂来⾃《超简单：⽤Python让Excel飞起来》
Excel能完成⼀般办公中绝⼤多数的数据分析⼯作，但是当数据量⼤、数据表格多时，可借助Python中功能丰富⽽强⼤的第三⽅模块来提⾼⼯作效率。本章将讲解如何利⽤pandas、xlwings等模块编写Python代码，快速完成排序、筛选、分类汇总、相关性分析、回归分析等数据分析⼯作。
105排序⼀个⼯作表中的数据（⽅法⼀）
如下图所⽰为⼯作簿“销售表.xlsx”的⼯作表“总表”中的数据表格。本案例要通过Python编程对表格按指定列进⾏排序。
import pandas as pd
data = pd.read_excel('销售表.xlsx',sheet_name='总表')#读取要排序的⼯作表数据
data = data.sort_values(by='利润',ascending=False)#按“利润”列做降序排序
<_excel('销售表33.xlsx',sheet_name='总表',index=False)#将排序后的数据写⼊新⼯作簿的⼯作表
第3⾏代码⽤于对读取的数据按照“利润”列进⾏降序排序，读者可根据实际需求修改列名。如果要做升序排序，则将参数ascending 设置为True。如果想要先按“利润”列做降序排序，遇到相同的利润值时再按“销售⾦额”列做降序排序，可将该⾏代码修改为“data=data.sort_values(by=[‘利润’，‘销售⾦额’]，ascending=False)”。
106 排序⼀个⼯作表中的数据（⽅法⼆）
在案例105中，使⽤pandas模块操作数据后，数据的格式设置会丢失。如果想要保持格式不变，可结合使⽤pandas模块和xlwings模块来完成排序。
import xlwings as xw
import pandas as pd
app = xw.App(visible=False,add_book=False)#启动Excel程序
workbook = app.books.open('销售表.xlsx')#打开要排序的⼯作簿
worksheet = workbook.sheets['总表']#指定要排序的⼯作表
data = worksheet.range('A1').expand('table').options(pd.DataFrame).value #读取指定⼯作表的数据并转换为DataFrame格式
result = data.sort_values(by='利润',ascending=False)#按“利润”列做降序排列
worksheet.range('A1').value = result #将排序结果写⼊指定⼯作表，替换原有数据
workbook.save('销售表44.xlsx')#另存为⼯作簿
workbook.close()#关闭⼯作簿
app.quit()#退出Excel程序
107 排序⼀个⼯作簿中所有⼯作表的数据
在案例105和案例106的基础上，可批量完成多个⼯作表数据的排序。现在需要对所有⼯作表的数据按“销售⾦额”列做降序排序。
import pandas as pd
app = xw.App(visible=False,add_book=False)#启动Excel程序
workbook = app.books.open('销售表.xlsx')#打开要排序的⼯作簿
worksheet = workbook.sheets #获取⼯作簿中的所有⼯作表
for i in worksheet: #遍历⼯作簿中的⼯作表
data = i.range('A1').expand('table').options(pd.DataFrame).value  # 读取指定⼯作表的数据并转换为DataFrame格式
result = data.sort_values(by='销售⾦额', ascending=False)# 按“销售⾦额”列做降序排列
i.range('A1').value = result #将排序结果写⼊当前⼯作表，替换原有数据
workbook.save('销售表221.xlsx')#另存为⼯作簿
workbook.close()#关闭⼯作簿
app.quit()#退出Excel程序
108 排序多个⼯作簿中同名⼯作簿的数据
先要对⼯作表“销售⾦额”中的数据按“销售⾦额”列做降序排列。
from pathlib import Path
import xlwings as xw
import pandas as pd
app = xw.App(visible=False,add_book=False)#启动Excel程序
fold_path = Path('D:\各地区销售数量')#给出要排序的⼯作簿所在⽂件夹的路径
file_list = fold_path.glob('*.xlsx')#获取⽂件夹下所有⼯作簿的⽂件路径
for i in file_list:#遍历获取的⽂件路径
workbook = app.books.open(i)#打开要排序的⼯作簿
worksheet = workbook.sheets['总表']#指定要排序的⼯作表
data = worksheet.range('A1').expand('table').options(pd.DataFrame).value #读取指定⼯作表的数据并转换为DataFrame
result = data.sort_values(by='销售⾦额',ascending=False)#按“销售⾦额”列做降序排列
worksheet.range('A1').value = result #将排序结果写⼊指定⼯作表，替换原有数据
workbook.save()
workbook.close()
app.quit()#退出Excel程序
109 根据单个条件筛选⼀个⼯作表中的数据
筛选是最常⽤的数据分析⼯具之⼀。本案例要通过Python编程对⼀个⼯作表中的数据按单个筛选条件进⾏筛选。
import pandas as pd
data = pd.read_excel('销售表.xlsx',sheet_name='总表')#从⼯作表中读取要筛选的数据
pro_data = data[data['产品名称']=='离合器']#筛选"产品名称”为“离合器”的数据
num_data = data[data['销售数量']>=100]#筛选"销售数量“⼤于等于100的数据
_excel('离合器.xlsx',sheet_name='离合器',index=False)#将筛选出的数据写⼊新⼯作簿的⼯作表中
_excel('销售数量⼤于等于100的记录.xlsx',sheet_name='销售数量⼤于等于100的记录',index=False)#将筛选出的数据写⼊新⼯作簿的⼯作表中110 根据多个条件筛选⼀个⼯作表中的数据
import  pandas as pd
data = pd.read_excel('销售表.xlsx',sheet_name='总表')#从⼯作表中读取要筛选的数据
condition1 =(data['产品名称']=='转速表')&(data['销售数量']>50)#设置“与”筛选条件
condition2 =(data['产品名称']=='转速表')|(data['销售数量']>=50)#设置“或”筛选条件
data1 = data[condition1]#根据“与”筛选条件筛选数据
data2 = data[condition2]#根据“或”筛选条件筛选数据
<_excel('销售表51.xlsx',sheet_name='与条件筛选',index=False)#将筛选出的数据写⼊新⼯作簿的⼯作表中
<_excel('销售表52.xlsx',sheet_name='或条件筛选',index=False)#将筛选出的数据写⼊新⼯作簿的⼯作表中
111 筛选⼀个⼯作簿中所有⼯作表的数据
workbook = pd.ExcelWriter('筛选表.xlsx')#新建⼯作簿
all_data = pd.read_excel('销售表.xlsx',sheet_name=None)#读取⼯作簿中所有⼯作表的数据
for i in all_data: #提取单个⼯作表的数据
data = all_data[i]#提取单个⼯作表的数据
filter_data = data[data['产品名称']=='离合器']#筛选“产品名称”为“离合器”的数据
_excel(workbook,sheet_name=i,index=False)#将筛选出的数据写⼊新建⼯作簿的⼯作表中
workbook.save()#保存⼯作簿
112 筛选⼀个⼯作簿中所有⼯作表的数据并汇总
案例111将筛选出的数据存放在不同的⼯作表中，本案例则要将筛选出的数据汇总存放在⼀个⼯作表中。
import pandas as pd
workbook = pd.ExcelWriter('筛选表.xlsx')#新建⼯作簿
datas = pd.DataFrame()#创建⼀个空DataFrame
for i in all_data: #提取单个⼯作表的数据
data = all_data[i]#提取单个⼯作表的数据
filter_data = data[data['产品名称']=='离合器']#筛选“产品名称”为“离合器”的数据
datas = pd.concat([datas,filter_data],axis=0)#纵向合并筛选出的数据
<_excel('离合器.xlsx',sheet_name='离合器',index=False)#将合并后的数据写⼊新⼯作簿的⼯作表中
本案例的第7⾏代码可以修改为“datas=datas.append(filter_data)”。
113 分类汇总⼀个⼯作表
案例059使⽤pandas模块中的groupby()函数实现了数据分组，本案例要在此基础上实现数据的分类汇总，即先分组，再对组内数据进⾏求和、求平均值、计数等汇总运算。
import xlwings as xw
import pandas as pd
groupby是什么函数
app = xw.App(visible=False,add_book=False)#启动Excel程序
workbook = app.books.open('销售表.xlsx')#打开指定⼯作簿
worksheet = workbook.sheets['总表']#指定要读取数据的⼯作表
data = worksheet.range('A1').expand('table').options(pd.DataFrame,dtype=float).value #读取指定⼯作表的数据并转换为DataFrame格式
result = upby('产品名称').sum()#根据“产品名称”列对数据进⾏分类汇总，汇总运算⽅式为求和
worksheet1 = workbook.sheets.add(name='分类汇总')#新增⼀个名为“分类汇总”的⼯作表
worksheet1.range('A1').value = result[['销售数量','销售⾦额']]#将分类汇总结果写⼊⼯作表
workbook.save('分类汇总表.xlsx')#另存为⼯作簿
workbook.close()
app.quit()
⽤groupby()函数对数据进⾏分组后，接着使⽤sum()函数对各组数据进⾏求和运算。如果要进⾏其他⽅式的汇总运算，如求平均值、计数、求最⼤值、求最⼩值，可以分别使⽤mean()、count()、max()、min()函数。
114 对⼀个⼯作表求和
如果不需要对⼯作表中的数据进⾏分类汇总，⽽是直接做求和运算，可以使⽤pandas模块中的sum()函数实现。
import pandas as pd
app = xw.App(visible=False,add_book=False)#启动Excel程序
workbook = app.books.open('销售表.xlsx')#打开指定⼯作簿
worksheet = workbook.sheets['总表']#指定要读取数据的⼯作表
data = worksheet.range('A1').expand('table').options(pd.DataFrame,dtype=float).value #读取指定⼯作表的数据并转换为DataFrame格式
result = data['成本价'].sum()#对产品名称列的数据进⾏求和
worksheet.range('C13').value ='合计'#将⽂本“合计”写⼊单元格C13
worksheet.range('D13').value = result #将求和结果写⼊单元格D13
workbook.save('求和表.xlsx')#另存为⼯作簿
workbook.close()
app.quit()
⽤于求和的sum()函数也可以修改为mean()、count()、max()、min()等函数来完成其他类型的统计运算。
115 对⼀个⼯作簿的所有⼯作表分别求和
import xlwings as xw
import pandas as pd
app = xw.App(visible=False,add_book=False)#启动Excel程序
workbook = app.books.open('销售表.xlsx')#打开指定的⼯作簿
worksheet = workbook.sheets #获取⼯作簿中的所有⼯作表
for i in worksheet: #遍历⼯作簿中的⼯作表
data = i.range('A1').expand('table').options(pd.DataFrame).value #读取当前⼯作表的数据并转换为DataFrame格式
result = data['成本价'].sum()#对“成本价”列数据进⾏求和
column= i.range('A1').expand('table').value[0].index('成本价') + 1#获取“成本价”列的列号
row = i.range('A1').expand('table').shape[0]#获取数据区域最后⼀⾏的⾏号
i.range(row + 1,column - 1).value ='合计'#将⽂本“合计”写⼊“成本价”列的前⼀列最后⼀个单元格下⽅的单元格
i.range(row + 1,column).value = result # 将求和结果写⼊“成本价”列最后⼀个单元格下⽅的单元格
workbook.save('求和表1.xlsx')#另存为⼯作簿
workbook.close()
app.quit()
将⽤于求和的sum()函数修改为mean()、count()、max()、min()等函数来完成其他类型的统计运算。本
案例要将求和结果放在“采购⾦额”列最后⼀个单元格下⽅的单元格中，但是每个⼯作表的数据⾏数不⼀定相同，所以先通过第9⾏和第10⾏代码获取相关单元格的列号和⾏号，再通过第11⾏和第12⾏代码写⼊所需内容。
116 在⼀个⼯作表中制作数据透视表
Excel中的数据透视表能快速汇总⼤量数据并⽣成报表，是⼯作中分析数据的好帮⼿。虽然在Excel中制作数据透视表的过程不算复杂，但是操作步骤也不少。如果想要通过Python编程制作数据透视表，就需要掌握pandas模块中的pivot_table()函数。
import xlwings as xw #导⼊xlwings模块
import pandas as pd #导⼊pandas模块
app = xw.App(visible=False,add_book=False)#启动Excel程序
workbook = app.books.open('D:\python⾃动化office\销售表.xlsx')#打开指定⼯作簿
worksheet = workbook.sheets['总表']#指定读取数据的⼯作表
data = worksheet.range('A1').expand('table').options(pd.DataFrame,dtype=float).value # 读取指定⼯作表的数据并转换为DataFrame格式
pivot = pd.pivot_table(data,values=['销售数量','销售⾦额'],index=['产品名称'],aggfunc={'销售数量':'sum','销售⾦额':'sum'},fill_value=0,
margins=True,margins_name='合计')#⽤读取的数据制作数据透视表
worksheet1 = workbook.sheets.add(name='数据透视表')#新增⼀个名为“数据透视表”的⼯作表
worksheet1.range('A1').value=pivot #将制作的数据透视表写⼊新增的⼯作表
workbook.save('数据透视表.xlsx')#另存⼯作簿
workbook.close()
app.quit()
第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()。
import pandas as pd #导⼊pandas模块
data = pd.read_excel('销售额统计表.xlsx',sheet_name=0,index_col='序号')#读取⼯作簿中第1个⼯作表的数据
result = ()#计算任意两个变量之间的相关系数
print(result)#输出计算出的相关系数
第2⾏代码⽤于读取⼯作簿“销售额统计表.xlsx”的第1个⼯作表中的数据，并使⽤“序号”列的数据作为⾏索引。
第3⾏代码⽤于计算第2⾏代码读取的数据中任意两个变量之间的相关系数。如果只想判断某个变量与其他变量之间的相关性，可将第3⾏代码修改为“()[‘销售利润（万元）’]”，它表⽰计算销售利润与其他变量之间的相关系数
运⾏本案例的代码后，会得到如下所⽰的相关系数矩阵。第4⾏第2列的数值为0.985442，表⽰销售利润与⼴告费⽤的相关系数，其余数值的含义依此类推。需要说明的是，矩阵中从左上⾓⾄右下⾓的对⾓线上的数值都为1，这个1没有实际意义，因为它表⽰变量⾃⾝与⾃⾝的相关系数，⾃然是1。从该矩阵可以看出，销售利润与⼴告费⽤之间存在较强的线性正相关，⽽与成本费⽤之间的相关性较弱。
118 使⽤描述统计和直⽅图制定⽬标

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。