Python自动化应用案例：一键生成工厂物料采购订单（精益办公案例之三）

前言：

本文描述一个Excel办公自动化的实践示例：模拟生产企业在获取客户订单后，根据相关的数据和信息，通过编程的方式（Python语言），快速建立采购订单的过程。这种精益办公的方式将大幅精简在实际工作中的数据重复输入、计算与信息沟通，改善企业信息流的效率，提升多部门统筹计算的效率和准确性。（本示例可适合普通的中小型企业的应用，即没有使用ERP或SAP系统的企业）

问题简述：

精准的物料供应是生产企业运营的一个重要环节，在确认客户订单（或生产计划）后，快速计算并获取原料采购订单已经成为了决定交付日期的关键要素之一。下面展示了一般工厂的价值流程示意图（“工厂价值流简图”），本示例将模拟实际的企业运作模式，根据获取的数据和信息，实现快速（一键生成）原料采购订单量的计算。

1. 输入数据：
   产品配方文件（xlsx文件或csv文件）-- 注意基本要求：sheetname是用产品名称来标识的
   客户订单文件（xlsx文件或csv文件）
   物料库存文件（xlsx文件或csv文件）
2. 输出结果：
   物料需求量（根据订单需求和配方计算）
   物料采购量（采购订单）–本示例解决的关键问题

流程步骤：

1. 导入客户订单文件，提取产品名称和产量值
2. 导入产品配方文件，提取Excel文件中的sheetname（即产品的名称）并组成列表，用于产品名称的匹配
3. 根据产品名称和产量值，循环配对配方单中的产品名称，提取相对应的产品的配方数据
4. 将提取到的配方数据，读取后并转换为dataframe，并与相对应的产品名称和产量值合并（concat函数），得到产品-物料需求总表（“df”）
5. 将新生成的表格的写入Excel中并检查结果（得到：按各个产品名称和产量值计算出来的“物料料需求表” – 供财务部和生产部使用）
6. 使用pivot_table对数据表进行合并计算，统计出各种物料（原料和半成品）的生产需求量
7. 将上述pivot_table写入Excel中（得到：所有物料的生产用量表 – 供应链/原料仓使用）
8. 导入物料库存数据，与生产需求量进行交互合并，提取出需要购买的原料及对应的数量值
9. 根据设定的“安全库存”和供应商的“最低购买量”计算出合适的物料采购量
10. 最后将物料采购量数据写入Excel中（得到：物料的采购订单量–供采购部使用）

示例代码（Python）

1. 导入客户订单文件，提取产品名称和产量值

import numpy as np
import pandas as pd
import matplotlib as mpl
import openpyxl

# 支持中文：一般企业的Excel文件的数据是以中文显示的
mpl.rcParams["font.family"] = "SimHei"
mpl.rcParams["axes.unicode_minus"] = False

# 导入的客户订单文件路径
input_order = r"D:\request_test\dataset\订单.xlsx"
order = pd.read_excel(input_order)
order_li = order["产品名称"]
volume = order["产量"]
order

小结：本示例是模拟生产企业接受了3个产品的客户订单。

2. 导入产品配方文件，提取产品的名称并组成列表
3. 根据产品名称和产量值，循环配对配方单中的产品名称，提取对应产品的配方数据
4. 读取配方数据，转换为dataframe，并与相对应的产品名称和产量值合并（concat函数），得到产品-物料需求总表（“df”）
5. 将df写入Excel中并检查结果（得到：按各个产品名称和产量值计算出来的“物料料需求表” – 供财务部和生产部使用）

# 2.导入产品配方数据
input_BOM = r"D:\request_test\dataset\配方单.xlsx"
outputfile = r"D:\request_test\report\需求量数据汇总.xlsx"

sheet_li = []
new_li = []
wb = openpyxl.load_workbook(input_BOM)
sheet_names = wb.sheetnames
sheet_li.extend(sheet_names)

# 3. 循环配对配方单中的产品名称，提取对应产品的配方数据
for sn, vol in zip(order_li, volume):
    dataframe = pd.read_excel(input_BOM, sheet_name=sn, header=0)
    dataframe["产品名称"] = sn
    dataframe["产量"] = vol
    dataframe["物料需求量"] = vol * dataframe["配方量"] / 100
    new_li.append(dataframe)

# 4. 合并产品名称、产量值和配方数据（使用concat函数来完成）    
df = pd.concat(new_li)

# 5. 将df写入Excel中并检查结果（得到：按各个产品名称和产量值计算出来的“物料料需求表” -- 供财务部和生产部使用）  
df.to_excel(outputfile, index=False)
print("配方数量为:", len(sheet_li)) 
print("产品名称为:", sheet_li)
print("本次执行一共合并表格数为:", len(order_li))
df

小结：我们已经成功导入4个产品的配方；根据客户订单的要求，抽取出其中的3个产品，连接上对应的产品订单需求量，由此计算出各种物料的对应的生产需求数量。注意: 这里为了简化示例流程，没有计算各种产品的收率和生产损耗量，真实的场景可根据实际的情况在配方单（BOM）中加入。

6.使用pivot_table对数据表进行合并计算，统计出各种物料（原料和半成品）的生产需求量
7.将上述pivot_table写入Excel中（得到：所有物料的生产用量表 – 供应链/原料仓使用）

# 6. 对各个产品的需求量进行合并计算，并写入excel中
new_file = r"D:\test\Data\request_test\report\request_Qty.xlsx"

request = pd.pivot_table(df, values=["物料需求量"], index="物料名称", aggfunc="sum", fill_value=0)
request.reset_index(inplace=True)
request.to_excel(new_file, sheet_name="物料需求量")
request

小结：经过合并计算，得到各种物料在完成上述客户订单时所需要的物料需求量。这个“物料的生产用量表”会自动写入Excel中，这些数据信息主要是给供应链或原料仓作为查询或盘点计算等使用。

8. 导入物料库存数据，与生产需求量进行交互合并，提取出需要购买的原料及对应的数量值

# 8.1 导入物料库存量的数据
inventory_input = r"D:\test\Data\request_test\dataset\库存量.xlsx"
inventory = pd.read_excel(inventory_input)
inventory

# 8.2 与生产需求量进行交互合并，提取出需要购买的原料及对应的数量值
#  将物料缺口量与库存量两张表进行连接（左连接）并做“去重”处理，
purchase = pd.merge(left=request, right=inventory, how="left")
purchase

小结：
8.1 模拟企业有7种物料的库存，并且根据经验设定了为满足日常生产的“安全库存”数量；根据供应商的要求建立的“最小采购量”。
8.2 通过把表格“物料库存量”与表格“生产物料的需求量”进行交互合并，提取出需要购买的原料及对应的数量值

9. 根据设定的“安全库存”和供应商的“最低购买量”计算出合适的物料采购量

# 9.1 计算物料的缺口量，即生产后的“生产余量”（varation）
# 本测试考虑按照安全库存备货的问题，“生产余量”的计算公式为：，
purchase["生产余量"] = purchase["库存量"] - purchase["物料需求量"]
purchase

# 9.2 提取所有生产余量(varation) < 安全库存的物料信息
# 即：生产后的物料水平低于安全库存的物料是需要进行采购的
purchase = purchase[purchase["生产余量"] < purchase["安全库存"]]
purchase

# 9.3 考虑到采购时，供应商一般有最低购买量的要求，因此首先让购买量满足“最小采购量”的条件
purchase["采购量"] = purchase["最小采购量"]

# 9.4 对于没有“最小采购量”要求的物料，“采购量”=“物料需求量”-“库存量”
purchase["采购量"][purchase["采购量"] == 0] = purchase["物料需求量"] - purchase["库存量"]
purchase

# 9.5 因为不但要考虑满足生产需求，也有按照“安全库存”适当被料，因此计算“库存余量”时需要考虑物料的“安全库存”要求
purchase["库存余量"] = purchase["库存量"] + purchase["采购量"] - purchase["物料需求量"] - purchase["安全库存"]
purchase

# 9.6 提取“库存余量”<“安全库存”的物料：代表生产后的物料水平是低于“安全库存”的要求
# 由于这些物料的“采购量”应考虑增加不足“安全库存”的差值
# 因此这些物料的采购量的计算公式：“采购量”=原来的“采购量”-“库存余量”（这里使用减法是因为这个库存余量是个“负数”） 
purchase["采购量"][purchase["库存余量"] < 0] = purchase["采购量"] - purchase["库存余量"]
purchase["库存余量"] = purchase["库存量"] + purchase["采购量"] - purchase["物料需求量"] - purchase["安全库存"]
purchase

小结：检查各种物料在生产后，并减去“安全库存”后，所有的值都是大于或等于零值，即代表在生产后的剩余库存是能达到安全库存的要求的。另外，整个第9步的计算也可以使用If条件语句和循环语句来解决。

10. 最后将物料采购量数据写入Excel中（得到：物料的采购订单量–供采购部使用）

# 10. 经过上述计算后，采购量已经生成，这些采购数据同时满足了多方要求： 
# 1.不同产品对同一物料的需求量；2.现有库存情况；3.供应商的最低采购量的要求；4.生产对“安全库存”的要求
# 最后将数据写入Excel中，便于采购部下采购订单时直接使用
outfile = r"D:\test\Data\request_test\report\purchase_list.xlsx"
purchase.to_excel(outfile)
print("执行完成，请打开Exce检查物料采购订单的情况！")

检查Excel, 已经生产并保存了计算好的各种需求物料的采购订单量，问题解决了！

结束语

本案例选择的“物料采购量的计算”是生产企业运营中的一个日常操作，它的覆盖面很广，涉及包括了业务、研发、财务、计划、生产、供应链（原料仓库）和采购等众多部门的参与；并且它的快速和准确建立向来都是影响“订单达成率”的关键因素之一，它的是成功实现是基于高效的多部门沟通，准确的数据交换与计算，快速决策，因此受到企业的高度关注。对于大多数的中小型企业，如果仅仅依靠手工计算的方法，要做好这些工作很不容易，因为常常受到一下几个主要原因的制约：
1.产品配方：产品配方往往是涵盖着重要的商业信息，是属于保密的或有限度接触的企业信息，因此在沟通和应用配方计算时会存在保密管理的问题和沟通受限的制约。
2. 产品种类多，订单变化快：当前的市场竞争激烈，客户的要求高，企业都在尽其最大的努力来满足客户的各种各样的要求，因此企业常常要管理复杂的产品系列，并且不断地调整生产节奏，如果是依靠手动计算，很容易出现漏算、误算的情况，导致货期的延误。
3.统筹计算和沟通：物料需求量的计算是需要跨部门合作建立的，各部门有特定的分工，数据的收集和使用是独立的。因此需要各部门各种准备和提供相应的数据，彼此交换，再逐步依次计算出来。这样的传统计算方式不但耗时费力，并且在遇到交叉数据很多的时候就容易出差
4.大多数的企业是使用Excel（或WPS）进行数据的收集和分析，因此结果需要转换为Excel文件，以便于企业内部的沟通和使用。

对此，大公司一般会应用SAP这样的（ERP）管理软件，不过价格昂贵（从几万块到上千万），一般的中小企业是难以支付这样的操作成本。我们将根据普通企业的特点，投入少量的费用，通过一些小程序来逐步解决这些问题。

这是应用Python的编程的一次实践尝试，解决的方案和编写的程序中仍存在很多不完美的地方，欢迎各位读者建设性的建议和反馈，以帮助我们不断地成长，谢谢！