Python进行Excel数据统计 前言示例数据前期思路 实操遇到的问题和解决方案最终结果实现代码 总结
前言
貌似大家中使用到Excel数据统计和处理比较多。前两天GF发来指示，帮忙统计2019年的全年（1-12月）、全体员工（每月可能有新入职员工）的养老保险、医疗保险、失业保险、工伤和生育共五项中的公司缴纳部分。需要进行全年累加，最后放到区域公司发来的汇总表中。貌似不难，但也需要理一理思路。
示例数据
示例的原始数据如下所示（12张表格）。
需填的表格如下
前期思路
（1）python Excel的读写操作
（2）全体员工的数据提取统计操作，此处适合使用字典类型（字典为可变数据类型）。字典的key为员工的名字，对应的value为一个含有5个元素的列表（后续需要累加更改）
（3）python写Excel的时候，需要进行人员的判断匹配，因为最终的汇总表人员众多
实操 遇到的问题和解决方案
（1）如何提取数据？ 原始数据中，公司含有不通的部门，每个部门又有相应的汇总栏目，影响数据提取。通过观察，A列数据的序号，只在对应员工栏目有数值的序号标识，而汇总栏目为空值。因此可以通过判断A列是否是数值来提取员工信息。
（2）存在None值。 对应的员工信息中，存在空值，例如axdjzg的医疗保险部分。为不影响后续的累加运算，需要进行数据的处理，把空值转换为数字0。
（3）数据提取。 由于GF的示例数据表格中养老保险、医疗保险、失业保险、工伤和生育是使用公式计算的，字典的键值对的值（value）提取的结果为元素为 '=ROUND(D4*19%,2)'的字符串列表，无法进行数值的累加操作。
（a）解决方案A。同样提取对应列的数据，从而计算出对应的保险的数据，由于计算结果为浮点数，最终的12个月累加结果存在小尾数问题，并且结果与手动结果不匹配，失败告终。
（b）解决方案B。先对Excel表格进行处理，清除掉所有的公式，只保留数据。使用的方法为Excel的VBA编程，具体参考文献[1] [2].
（4）列表对应项相加。 提取的结果每个sheet提取出一个字典。由于字典的值（value）为列表，操作时需要对value列表累加求和。python中列表的加法（+）为拼接操作，不像MATLAB中的矩阵操作。此时需要可以使用Numpy库进行数组操作，或者使用循环进行对应项相加。在此本文使用循环。
最终结果
（1）测试输出
外向的大炮: [20900, 840, 8400, 65, 600]
（2）写表格数据
实现代码 #By WDL 2020-4-27import openpyxl#单元格数据判断函数，剔除空单元格中读取的空值def valuechange(sth): #判断是否为None if sth==None: sth=0 return sth#Excel表格读取函数def excelread(wb,sheetname):#wb工作簿 sheetname表格名称 ws=wb[sheetname] #打一个工作表 # 读取数据，把excel中的一个sheet按行读取出来，存入一个二维的list total_list=[] for row in ws.rows: row_list=[] for cell in row: row_list.append(cell.value) total_list.append(row_list) # 返回读取的数据列表，每个元素为sheet中的一行 return total_list#表格数据处理函数,数据提取def procSheet(wb,sheetname): #wb工作簿 sheetname表格名称 total_list=excelread(wb,sheetname) #利用字典存数据 infos={} for term in total_list: if isinstance(term[0],int): #判断标号是否是数值 # 使用sheet中列的标号 判断元组中每个单元格的索引 # term[2]对应姓名 #term[4] 养老，term[8] 失业，term[12] 医疗，term[16] 工伤，term[17] #生育 infos.setdefault(term[2], []).extend([valuechange(term[4]),valuechange(term[8]), valuechange(term[12]),valuechange(term[16]),valuechange(term[17])]) #以上为 一键 对列表的字典，如果键term[2]不存在，添加键值对，只默认为空列表[] 随后使用extend()方法扩展列表 return infos#----------------数据提取操作-------------wb=openpyxl.load_workbook("XXX公司应缴社会保险明细表（2019.1-12）.xlsx")data=procSheet(wb,"2019.1") #初始值print(data)sheetnames=["2019.{}".format(str(i)) for i in range(2,13)]for sheetname in sheetnames: infos=procSheet(wb,sheetname) for name in infos: #列表对应项累加 if name in data: for i in range(len(data[name])): data[name][i]+=infos[name][i] else: data[name]=infos[name]print(data) #最终的统计数据print("外向的大炮:",data["外向的大炮"])worker=set(data)print(worker) #统计XXX公司的所有员工#------------Excel写操作--------------#打开要写数据的工作表workbook=openpyxl.load_workbook("XXX区域2019年度年薪人员人工成本统计表.xlsx")worksheet=workbook["附表二、2019年度人工成本汇总"]#对每个单元格进行格式设置 总共385行for eachCommonRow in worksheet.iter_rows(min_row=1,min_col=1,max_row=400,max_col=47):# 每一行为元组类型 #使用sheet中列的标号 判断元组中每个单元格的索引 if eachCommonRow[8].value in data:#姓名是否在字典中 第I列对应索引9-1=8 eachCommonRow[37].value = data[eachCommonRow[8].value][0] #养老保险 eachCommonRow[38].value = data[eachCommonRow[8].value][2] #医疗保险 eachCommonRow[39].value = data[eachCommonRow[8].value][1] # 失业保险 eachCommonRow[40].value = data[eachCommonRow[8].value][3] # 工伤保险 eachCommonRow[41].value = data[eachCommonRow[8].value][4] # 生育保险#保存workbook.save("XXX区域2019年度年薪人员人工成本统计表.xlsx") 总结
在实践中不断的修改完善，善用搜索能力，不断总结提高，减小工作量。最后希望对大家有帮助。
参考文献
[1]两种excel 去掉公式保留数值的方法. http://www.ittribalwo.com/article/1369.html
[2]excel表格问题 怎么清除公式保留数据？https://www.zhihu.com/question/21638771