相信大家都知道excel的vlookup函数,也会运用,这是财务审计工作中经常会用到的函数之一,那用python如何实现这一功能呢?
1.
目标介绍
刀哥想将2017年科目余额表中,应收账款明细的所有客户,以科目编码为索引,把所有客户对应的期初期末及发生额金额引入到2017AR表中。
即获取2017年科目余额表中的“科目名称”、“期初借方”、“期初贷方”、“本期发生借方”、“本期发生贷方”、“期末借方”及“期末贷方”数并引入到与2017AR表科目编码相对应的表格中,如下图。
2017年科目余额表截图
2017AR表截图
2.
代码实现
首先是调用需要用到的库,比如pandas库,它是python处理excel常用的库,它能处理大量表格数据,有非常强大的功能。
1import pandas as pd
2import openpyxl,xlrd
3from openpyxl import Workbook
第二步是读取原始数据来源表,即读取2017年科目余额表,代码如下。
1file_source = r'f:\Python\练习临时存放\实现vlookup功能\科目余额表\2017年科目余额表.xls' #原始数据来源表路径
2df_source = pd.read_excel(file_source) #读取原始数据来源表
第三步是读取导入目标表,即读取2017AR表,此处因科目编码数量多,手动输入不现实,故用代码实现输入读取,代码如下。
1#把所有科目编码写入到df_target中
2file_target = r'f:\Python\练习临时存放\实现vlookup功能\2017AR.xls' #导入目标表路径
3list_ar_code = [] #定义一个应收账款科目编码列表
4workbook = xlrd.open_workbook(file_target) #读取导入目标表
5balance_sheet = workbook.sheet_by_index(0)
6rows = balance_sheet.nrows
7for i in range(1,rows):
8 list_ar_code.append(balance_sheet.cell_value(i,0)) #将每一个科目编码添加到应收账款科目编码列表中
9data = {'科目编码':list_ar_code}
10df_target = pd.DataFrame(data)
第四步是将2017年科目余额表中需索引的全部信息与2017AR表进行合并,即实现vlookup功能,代码如下。
1#需要导入哪些数据,就将哪些数据的列名写上。
2dfneed = df_source[['科目编码','科目名称','期初借方','期初贷方','本期发生借方','本期发生贷方','期末借方','期末贷方']]
3#on='科目编码',表示以“科目编码”进行索引。
4df_target = pd.merge(df_target,dfneed,how='left',on='科目编码')
这里是代码实现的重点,需要索引哪些信息,就把哪些信息对应的列名添加进去即可。
比如本文需要“科目名称”、“期初借方”、“期初贷方”、“本期发生借方”、“本期发生贷方”、“期末借方”及“期末贷方”这些数据,如果还需要“年度”、“会计期间”或其他信息,就把对应的列名添加进去,然后进行数据合并。
最后一步是写入需要索引的信息并生成文件,代码如下。
1#生成文件
2df_target.to_excel(file_target,index=False)
以上所有步骤完成后,最终生成的文件内容如下。
最后的合计数和核对栏是刀哥手动完成的,可以看出合计数与2017年科目余额表原表数据合计数能对上,与科目编码也能对应上,说明引入过来的数据正确。
那多出来的那些科目编码和空白栏是怎么回事呢,那就是刀哥下次要分享的内容,敬请期待,记得点个关注和收藏哦。
学习python,刀哥正在路上,你要一起来吗?欢迎加入刀哥python学习交流群,来一起撸代码吧。