python实现存货批量计价测试

马上要迎来年审了，存货计价测试底稿是一道绕不过去的坎，其主要是对期末存货的计价与分摊认定获取合理保证。如果碰到期末存货余额较为分散的公司，需要手工复制、粘贴完成近百张计价测试表，整个过程的繁琐程度足以让人抓狂。有没有可能通过代码让整个过程变得更加简洁，只需点击几下鼠标，就可以自动生成近百张数据完备的存货计价测试表，并自动汇总每张表的计价测试差异呢？最近思源君通过不断尝试，通过python初步实现了这个功能。因为主要以解决问题为出发点，加之思源君也是初学者，所以代码编写过程中有些粗糙，还望各位读者多多包涵。

第一步：利用os.walk函数读取存货收发存明细表所在文件夹的文件列表，并存入name.txt文件夹

import os 
for root, dirs, files in os.walk("存货收发存文件夹"):
    for file in files:
        with open('name.txt','a') as f:
            f.write(file+'\n')
            f.close()

如果是获取代码所在的当前目录的所有文件列表，则使用os.listdir()函数

import os
for file in os.listdir('.'):
    with open('当前目录文件列表.txt','a') as f:
        f.write(file+'\n')
f.close()

第二步：利用for函数通过读取name.txt文件对1-12月存货收发存明细表进行插列处理，在首列插入月份列，并把月份列所有行设置为月份数，对指定列数进行空白值的向后填充-利用pd.read_excel读取sheet,利用df.insert进行插列处理，利用ffill函数向后填充

import pandas as pd
import os 
f=open('name.txt',encoding='utf-8')
names=f.readlines()
lists=[]
for name in names:
    lists.append(name.replace('\n',''))
for i in range(1,13):
    df1 = pd.read_excel(lists[i-1],'Sheet1')
    df1.insert(0,'月份',str(i))
    print(list(df1))
    col=['Unnamed: 0','Unnamed: 1']
    df1[col]=df1[col].ffill()
df1.to_excel(str(i)+'_'+lists[i-1])

第三步：把1-12月存货收发存明细表按照月份顺序合并成一个工作簿-利用pandas的pd.concat函数，在此之前，要利用第一步中的读取文件列表程序把第二步中生成的插入月份列的每月收发存数据表的名称存储到name.txt文件中

import pandas as pd
import os
f=open('name.txt',encoding='gbk')
names=f.readlines()
lists=[]   #用来装载插入月份列后的每月收发存数据表的名称
lists1=[]  #用来装载插入月份列后的每月收发存数据
for name in names:
    lists.append(name.replace('\n',''))
for i in range(len(lists)):
    object=pd.read_excel(lists[i],'Sheet1')
    lists1.append(object)
df = pd.concat(lists1) #合并1-12月收发存数据
df.to_excel(r'res-12个月.xlsx', 'Sheet1', index=None)

第四步：对于合并后的1-12月存货收发存明细表，插入1列新列，将1-12月份和物料号通过exceld的&功能组合成一个唯一标识符（AB=A&B）

第五步：将需要进行期末存货计价测试的存货物料号筛选出来，依次与1-12月进行拼接（生成的新名称要与第四步中的唯一标识符相同），并保存到new_name.txt文件，代码如下

f=open('存货测试名称.txt',encoding='utf-8')
names=f.readlines()
list=[]  #用来装载期末需进行计价测试的存货名称
new_list=[]  #用来装载拼接月份和名称后的唯一标识符
for name in names:
    name=name.replace('\n','')
    list.append(name)
for i in range(len(list)):
    for j in range(1,13):
        new_name=str(j)+'-'+list[i]
        new_list.append(new_name)
for i in range(len(new_list)):
    with open('new_name.txt','a') as f1:
        f1.writelines(new_list[i]+'\n')

第六步：新建表格，命名为样本收发存数据.xlsx,表头首列为第五步中生成的存货唯一标识符（月份+物料号），其后依次为期初、本期增加、本期发出的金额与数量

第七步：通过excel的iferror+vlookup函数从第四步中得到的1-12月拼接明细表中匹配得到存货唯一标识符对应的期初、本期增加、本期发出的金额与数量

第八步：在Excel中新建一个空白的带公式存货计价测试模板，利用wb.copy函数按照期末存货计价测试的品种的数量复制模板，生成相应sheet，同时读取第七步得到的存货收发存数据填充到相关品种存货计价测试的sheet中去（并同时填充上对应存货名称与索引号名称，这两项数据需提前存入txt文件），生成一张张单独的存货品种的计价测试表

import openpyxl as op
file='计价测试模板.xlsx'
wb=op.load_workbook(file)
ws=wb['模板']
wb1=op.load_workbook('样本收发存数据.xlsx',data_only=True)
ws1=wb1['数据']
f=open('存货测试名称.txt',encoding='utf-8')
names=f.readlines()
lists=[]
for name in names:
    lists.append(name.replace('\n',''))
f1=open('索引号.txt',encoding='utf-8')
suoyins=f1.readlines()
lists1=[]
for suoyin in suoyins:
    lists1.append(suoyin.replace('\n',''))
#print(names)
for i in range(1,11): #本次编程思源君选取了10个存货计价测试样本
    object=wb.copy_worksheet(wb['模板'])
    object.title=str(i)
    object.cell(3,14).value=lists1[i-1]
    object.cell(7,2).value=lists[i-1]
    object.cell(11,9).value=ws1.cell(2+12*(i-1),3).value   #期初数量
    object.cell(11,11).value=ws1.cell(2+12*(i-1),5).value  #期初金额
    for k in range(1,13):
        object.cell(k+11,3).value=ws1.cell(k+1+12*(i-1),6).value #本期增加数量
        object.cell(k+11,5).value=ws1.cell(k+1+12*(i-1),8).value #本期增加金额
        object.cell(k+11,6).value=ws1.cell(k+1+12*(i-1),9).value #本期发出数量
        object.cell(k+11,8).value=ws1.cell(k+1+12*(i-1),11).value #本期发出金额
wb.save(file)

第九步：将第八步中生成的一张张单独存货品种对应的计价测试表中的期末数量、期末单价、期末余额、测算差异通过for循环读取到汇总表中

import openpyxl as op
file='计价测试模板.xlsx'
wb1=op.load_workbook(file)
ws1=wb1['汇总表']
for i in range(1,11):   #本次编程思源君选取了10个存货计价测试样本
    wb2=op.load_workbook(file,data_only=True)
    ws1.cell(i+7,5).value=wb2[str(i)].cell(23,9).value #期末数量
    ws1.cell(i+7,6).value=wb2[str(i)].cell(23,10).value #期末单价
    ws1.cell(i+7,7).value=wb2[str(i)].cell(23,11).value #期末余额
    ws1.cell(i+7,8).value=wb2[str(i)].cell(24,16).value #测算差异
wb1.save('计价测试汇总.xlsx')

---

End

「思源审计」专属学习群已正式成立，扫描下方二维码添加思源君的个人微信，拉您入群交流学习。

感谢您阅读“思源审计”，如有点滴收获，请点击下方链接关注