139
7.1.采购计划编制的EXCEL模版建立与应用
7.2.订购批量经济性与ABC分类的EXCEL模版建立与应用
7.3.库存控制模型的EXCEL模版建立与应用
7.4.“从至表”分析的EXCEL模版建立与应用
7.5.作业排序的EXCEL模版操作与应用
7.6.工时定额核定与产能平衡的EXCEL模型建立与运用
7.7.生产能力核定与成型组生产线平衡的EXCEL模版操作与应用
7.8.产品产量决策的EXCEL模版建立与应用
7.9.年度生产计划编制的EXCEL模版操作与应用
7.10.生产进度计划编制的EXCEL模版操作与应用
数据统计与分析——
物流行业的核心职业技能
让EXCEL成为您职业成功的助力器
模模模块块块7生生生产产产物物物流流流EXCEL实实实验验验
目录
模块7生产物流实验
140
采购计划编制的EXCEL模版建立与应用
【实验目的】
(1)应用EXCEL，建立采购计划编制的管理模版。
(2)熟练掌握VLOOKUP()、INDEX()和MATCH()函数及组合的运用方式；
(3)了解单元格相对引用和绝对引用的特性；
(4)熟练掌握工作表、工作簿之间的数据引用。
【实验材料】
表01采购计划期初数据资料
物料编码物料名称及规格类别名称计量单位
2007年度库存资料
年初盘点
库存量
1-10月
实际收入
11-12月
预计收入
1-10月
实际发出
11-12月
预计发出
01-01-001薄钢板/2.5mm钢材t200400150360240
01-02-001钢锭钢材t80026005502000950
01-03-001工字钢钢材t400300150450150
01-04-001槽钢/12＃钢材t200400100450200
01-05-001角钢/10＃钢材t1002005040050
02-01-001木材/原木建材m3200400200150200
02-02-001水泥/425建材t5001600400200900
03-01-001电石/一级化工t5010002001200310
04-01-001工作服/棉布防护套1000300020008901000
05-01-001刀具工具把30010002003500300
06-01-001铜芯橡皮线/BY50电料m10003000150011001900
07-01-001轴承/55mm轴承套50015005001600400
表022008年度生产物料需求计划
需求
类别需求单位
产
品
计划
产量
单
位物料编码
消耗物料
名称与规格
物料
单位
供应
定额
定额
单位需求量
生产
用料
一分厂甲2500件01-02-001500kg/件△1
二分厂乙1000件01-02-0011000kg/件△2
三分厂丙1800件01-03-001500kg/件△3
丙05-01-0011500
丙07-01-0012500
△需求量=计划产量×供应定额
【生产物流实验1】
1、采购计划编制EXCEL模版
141
表032008年度基建与维修物料需求计划
需求类别需求单位产品计划产量单位物料编码物料名称与规格物料单位需求量
基建用料基建科02-01-00110002-02-001450
维修用料
一分厂01-01-001200
01-04-001200
02-01-00180
02-02-001350
07-01-001800
06-01-0012000
04-01-001600
二分厂06-01-0012000
02-02-001150
07-01-001400
04-01-001110
三分厂06-01-0011500
01-01-001110
03-01-00180
02-02-001120
02-01-001170
四分厂01-05-001300
01-04-001120
02-01-001110
02-02-001180
03-01-00160
04-01-001140
表042008年度技改项目物料需求计划
项目物料编码物料名称及规格计量单位需求量
1、高炉大修薄钢板/2.5mm80
槽钢/12＃90
水泥/42560
电石/一级10
2、厂房大修木材/原木120
水泥/42540
角钢/10＃50
电石/一级20
3、余热工程木材/原木40
水泥/425150
4、福利设施木材/原木400
水泥/425200
角钢/10＃30
模块7生产物流实验
142
表052008年底预计物料储备量估计
物料编码物料名称及规格计量单位期末储备量
01-02-001287.5
01-01-001200
01-03-001500
01-04-001150
01-05-001180
02-01-001250
02-02-001400
03-01-001120
04-01-001500
05-01-001100
06-01-001600
07-01-001600
表062008年度物料单价及订购次数
编码物料名称及规格计量单位单价(元)订购次数
01-02-001250015
01-01-001400020
01-03-001300010
01-04-001320010
01-05-001380010
02-01-001260020
02-02-00160020
04-01-00112012
05-01-0014012
06-01-001816
07-01-0015020
03-01-00180012
【实验步骤与要求】
步骤1物料编码管理——建立《物料编码》工作簿，用于对企业物料编码的统一管理
步骤1.1建立《物料编码》工作表，按照表01设置物料编码
表1.0物料编码
类别
编码类别名称物料编码物料名称及规格
计量
单位
步骤2库存数据统计及物料平衡——建立《库存管理》工作簿
步骤2.1建立《库存盘点》工作表，按照表01设置库存盘点
1、采购计划编制EXCEL模版
143
表2.12007年初物料库存盘点汇总表
物料编码物料名称及规格计量单位盘点库存量
要求：以物料编码为标志，从表1.0中引用表中“物料名称及规格”、“计量单位”（下同）
步骤2.2建立《物料收入统计》工作表，按照表01设置物料收入统计
表2.22007年度物料收入统计
物料编码物料名称及规格计量单位1-10月实际收入11-12月预计收入物料收入合计
步骤2.3建立《物料发出统计》工作表，按照表01设置物料发出统计
表2.32007度年物料收入统计
物料编码物料名称及规格计量单位1-10月发出收入11-12月发出收入物料发出合计
步骤2.4建立《物料平衡》工作表，按照表01设置物料平衡
表2.42007年度物料平衡表
物料编码物料名称及规格计量单位年初库存收入合计发出合计年底库存
要求：表中“物料名称及规格”、“计量单位”、“年初库存”、“收入合计”、“发出合计”从相关工作簿
（表）中引用并计算。
步骤3物料需求管理——建立《物料需求》工作簿
步骤3.1建立《生产物料需求》工作表，按照表02设置生产物料需求核定
表3.12008年度物料需求核算表
需求
类别
需求
单位产品名称
计划
产量
产量
单位物料编码物料名称与规格
物料
单位
供应
定额
定额
单位需求量
模块7生产物流实验
144
步骤3.2建立《基建与维修物料需求》工作表，按照表03设置基建与维修物料需求核定
表3.22008年度基建与维修物料需求核算表
需求
类别需求单位物料编码物料名称与规格
计量
单位需求量
步骤3.3建立《技改项目物料需求》工作表，按照表04设置技改项目物料需求核定
表3.32008年度技改项目物料需求核算表
技改项目物料编码物料名称及规格计量单位需求量
步骤3.4建立《技改项目物料需求汇总》工作表，从表3.3汇总技改项目需求
表3.42008年度技改项目物料需求汇总
编码物料名称及规格计量单位
2008年技改项目物料需求汇总
合计高炉大修厂房大修余热工程福利设施
要求：表中数据从有关工作表中引用并计算
步骤3.5建立《物料需求汇总》工作表，从表3.1~3.3、3.5汇总年度物料需求
表3.52008年度物料需求汇总
序
号用途类别物料编码物料名称及规格
计量
单位
生产单位
合计一分厂二分厂三分厂四分厂
01生产用料
02基建用料
03维修用料
04技改用料
要求：表中数据从有关工作表中引用，按用途类别排列
步骤3.6建立《期末物料储备》工作表，按照表05设置期末物料储备
表3.62008年度期末物料储备量核定
物料编码物料名称及规格计量单位期末储备量
1、采购计划编制EXCEL模版
145
步骤4物料采购计划——建立《物料采购计划》工作簿
步骤4.1建立《物料单价及订购次数》工作表，按照表06设置物料单价及订购次数
表4.12008年度物料单价及订购次数表
编码物料名称及规格计量单位单价(元)订购次数
步骤4.2建立《物料采购计划》工作表
表4.22008年度物料采购计划表
编码物料名称及规格计量单位期初库存2008年度需求量期末储备计划采购量合计生产基建维修技改其他
要求：表中数据从有关工作表中引用并计算
步骤4.3建立《采购资金计划》工作表
表4.22008年度物料采购资金计划表
编码物料名称及规格计量单位计划采购量单价(元)订购次数订购批量订购金额合计（元）全年物料采购资金需求总额（元）
【实验课时与考核标准】
1、实验课时：6课时
2、提交试验结果
(1)将上述工作簿保存在一个文件夹内，文件夹名称——“物料试验1——（学号）”（下同）；
(2)压缩文件夹（打包）；
(3)将压缩文件提交试验指导教师。
3、考核标准
(1)各工作簿（表）建立、数据引用正确：40%；
(2)各计算公式设置、计算结果正确：40%；
(3)界面美观：20%；
(4)拷贝他人文件者，不得分。
模块7生产物流实验
146
订购批量经济性与ABC分类的EXCEL模版建立与应用
【实验目的】
(1)掌握EXCEL折线图、直方图、主（次）坐标轴、数据标志设置的运用方法
(2)了解EXCEL误差线插入方法及应用
(3)观察订购批量与经济性的关系
(4)应用EXCEL表示批量经济性模型和ABC分类模型
【实验材料】
表1.1生产物料需求数据
项目数值备注
年产量600,000双／年(皮鞋，150,000双／季度)
年需要原料(D)1,200吨／年(牛皮，100吨／月，2Kg／双)
原料平均购入价(P)12,000元／吨
产品平均售价100元/双
银行贷款年利率(r)8%
库存日保管费用率0.3%按购入价计算
全年天数(T年)360天
表1.2物料采购供应方案
订购批量/周期订购费用(元／次)全年订货次数备注
方案A300吨／季度120004方案C：与专业物流公司签订
供应外包协议，每周供应1
次；按全年订购总额和服务费
率计付服务费。
方案B100吨／月600012
方案C25吨／周5%
【实验步骤与要求】
步骤1.1建立“库存与费用分析”工作簿
步骤1.2建立《生产物料需求》工作表，输入（或拷贝）表1.1数据
步骤1.3建立《物料采购供应方案》工作表，输入（或拷贝）表1.2数据
步骤1.4建立《平均库存》工作表
【生产物流实验2】
【物流实验2.1】平均库存与库存费用
2、订购批量经济性与ABC分类EXCEL模版
147
表1.3采购批量
全年需求量(D)1200吨采购周期3个月1个月1周
全年天数(T年)360天供应天数(T)
日需求量(R)天/吨采购批量(Q)
要求：计算表中数据
表1.4平均库存
采购周期3个月1个月1周
日
期
日消
耗量
日初
库存量
日末
库存量
日平均
库存量
日初
库存量
日末
库存量
日平均
库存量
日初
库存量
日末
库存量
日平均
库存量
平均
日库存量
要求：
①日期设置１~90；
②设置第1日“日初库存量”=Q，“日消耗需求量”=R；
③计算“日末库存量”和“日平均库存量”；“日初库存量”=上日“日末库存量”
④当日初库存降到0时，按Q补充库存量；
⑤计算各采购周期方案的平均日库存量。
步骤1.5建立《库存费用》工作表
表1.5库存费用分析表
采购方案
库存量（吨）费用（万元）
期初
库存
期末
库存
平均
日库存
平均库存
占用资金
平均存货
年利息费
日平均
保管费用
全年
保管费用
全年
订货费用
全年
总费用
①②③＝(①＋②)／2④＝③×P⑤＝④×r⑥＝③×P×r⑦＝⑥×T
年
⑧⑨＝⑤+⑦+⑧
A3个月
B1个月
C1周
全年订货费用⑧计算方式：
方案A、B＝全年订货次数×每次订货费用
方案C＝全年订货总额×物流服务费率
步骤1.6观察、比较不同采购（供应）方案的德全年总费用，得出批量与经济性关系的结论
模块7生产物流实验
148
【实验材料】
表2.1物料需求与采购数据
项目数值单位
需求量(D)＝7200件/年
单价为(P)＝160元
订货成本(K)＝800元/次
存储费率(H)＝10%元/年
【实验步骤与要求】
步骤2.1建立“批量经济性”工作表，输入（或拷贝）表2.1数据
步骤2.2计算订货批量与相关费用
供货周期年订货次数订货批量平均库存量年订货成本年存储成本订货存储总成本
TNＱ=D/NQ/2KNPHQ/2TC=KN+PHQ/2
1年1
半年2
1季度4
2个月6
1个半月8
1个月12
半个月24
10天36
要求：设置计算公式，计算表中相关数据
步骤2.3插入图表
（1）选中年订货成本、年存储成本、订货存储总成本三个数值区域，插入折线图；
（2）选择“订货批量”数值区域为横坐标；，
（3）输入图标选项，调整图表元素位置，显示以下图标效果：
【物流实验2.2】批量经济性
0
10000
20000
30000
40000
50000
60000
7200360018001200800600300200
采购
批量
成
本
年订货
成本
年存储
成本
订货存储
总成本经济订购
批量
采购批量与经济性关系图示
2、订购批量经济性与ABC分类EXCEL模版
149
【实验材料】
表3.1物料需求与价格数据
序号（i）品种名称数量单价(元)
1A2010
2B2020
3C2010
4D1020
5E305
6F1510
7G2010
8I10680
9J2520
10L12100
【实验步骤与要求】
步骤2.4建立《ABC分类管理》工作表
（1）输入（或拷贝）表3.1，并计算总费用；
（2）按总费用降序排列，即算费用、项目百分比及累计
表3.2物料费用分析表
序号
（i）
品种
名称数量
单价
(元)
总费用
(元)
占用资金
百分比
资金百分比
累计
品种项目比
重
比重百分比
累计
合计
步骤2.5插入图表
（1）选中“总费用”，“资金百分比累计”数值区域，插入簇状柱形图；
（2）在“源数据”选项中，选择“比重百分比累计”数值区域为横坐标；
（3）在“数值轴”选项中，修改刻度最大值为1；
（4）在“图表类型”选项中，修改“资金百分比累计”图表类型为“数据点折线图”
（5）在“数据系列格式”选项中，选择“总费用”系列坐标轴为“次坐标轴”
（6）在“源数据”选项中，选择“次分类(X)轴标志”为“品种名称”数值区域；
（7）在“数值轴”、“次数值轴”选项中，调整刻度最大值；
（8）输入图标选项，调整图表元素位置，显示以下图表效果。
【物流实验2.3】物料ABC分类管理
模块7生产物流实验
150
步骤2.6插入辅助线
（1）在工作表失当位置输入以下两组数据
XY
2.585%
5.595%
（2）在“源数据”选项中，分别添加上述两组数据；
（3）分别选中这两组数据点，在“图表类型”选项中，修改为“X-Y散点图”；
（4）在“源数据”中，修改这两个系列的X值，Y值
（5）分别选中这两个数据点，在“数据系列格式”的“坐标轴”选项中，选择“主坐标轴”，分别
确定误差线X、误差线Y的误差值（负，自定义选中相应的X值、Y值）；
（6）在“误差线格式”的图案选项中，修改线条形状与颜色，显示下图效果
物料ABC分类图示
I
L
JB
ACDGEF
0%
10%
20%
30%
40%
50%
60%
70%
80%
90%
100%
10%20%30%40%50%60%70%80%90%100%比重累计
资
金
百
分
比
累
计
0
1000
2000
3000
4000
5000
6000
7000
8000
9000
10000
总
费
用
【实验课时与考核标准】
1、实验课时：4课时
2、提交试验结果
3、考核标准：
(1)《平均库存》分析正确，20%
(2)《库存费用》分析正确，20%
(3)《批量经济性》分析、图表正确、美观，30%
(4)《ABC分类管理》分析、图表正确、美观，30%。
3、库存控制模型EXCEL模版
151
库存控制模型的EXCEL模版建立与应用
【实验目的】
(1)了解EXCEL单元格链接的方式
(2)运用EXCEL建立各类库存控制模型的分析摸版
【实验材料】
各类库存控制模型计算公式及实验数据
【实验步骤与要求】
步骤1.1建立《库存控制模型》工作簿
步骤1.2建立《目录》工作表（如下图）
步骤1.3在“工具/选项/视图”，取消“网格线”、“行号列标“选项。待下面步骤的工作表建立后，
设置单元格与相应的工作表的链接关系。
典型库存模型
一、不允许缺货，一次全部入库，在供应周期内均匀耗用的模型
定量订货定期订货
二、不允许缺货，一次订购，在供应周期内均匀进货、均匀耗用的模型
三、具有价格折扣的订货模型
单式折扣1单式折扣2复式折扣运输折扣
四、允许缺货的库存控制模型
五、离散型需求的库存控制模型
六、随机性库存控制系统
步骤2.1建立《定量订购控制》工作表
定量订购控制系统返回
项目及符号编号计算式数值单位
模型
数据
输入
需求量D7200件／年
订购费K1200元／次
价格P240元／件
年库存保管费用率Ｈ18%
订货周期LT14天
保险天数T保6天
全年天数T年360天
【生产物流实验3】
定量订购模型
模块7生产物流实验
152
（续前表）
(计算因子)①2DK/P/H
经济订货批量(Q)②
PHDKEOQ2?
订货次数(N)③N＝D/Q
平均日需用量(R)④R=D/360
经济供货周期(T)⑤T＝Q/R
保险储备(S)⑥S＝R×T保
订货点(B)⑦B＝R×LT＋S
全年订货费用(TCK)⑧TCK＝K×D/Q
全年存储费用(TCH)⑨TCP＝P×H×（Q/2+S）
全年总成本(TC)⑩TC＝TCK＋TCH
(1)在①~⑩行的数值单元格中，输入计算公式，求相应项目的数值
(2)“返回”单元格设置与《目录》工作表的链接
(3)在“工具/选项/视图”，取消“网格线”、“行号列标“选项。
(4)保护工作表（取消“模型数据输入”数值区域的“锁定”设置）
以下各步骤操作与要求，与(1)～(4)类似
步骤2.2建立《定期订购控制》工作表
定期订购控制系统返回
项目及符号编号计算式数值单位
模型
数据
输入
需求量D3600件／年
订购费K400元／次
价格P160元／件
年库存保管费用率Ｈ20%
订货周期LT7天
保险天数T保3天
全年天数N年360天
(计算因子)①2DK/P/H
经济订货周期(T)②
PHDKREOI21?
订货次数(N)③N＝360/T
平均日需用量(R)④R=D/360
经济订货批量(Q)⑤Q＝T×R
保险储备(S)⑥S＝R×T保
最高储备量(E)⑦E＝R×T＋RLT＋S
全年订货费用(TCK)⑧TCK＝K×D/Q
全年存储费用(TCH)⑨TCH＝P×H×Q/2
全年总成本(TC)⑩TC＝TCK＋TCH
订购量S=E－Ｗ（库存量）－Ｙ(在途订购量)
定期订购模型
3、库存控制模型EXCEL模版
153
步骤2.3建立《一次订购分批入库》工作表
一次订购分批入库——控制系统返回
项目及符号编号计算式数值单位
模型
数据
输入
需求量D3600吨／年
订购费K200元／次
价格P200元／吨
年库存保管费用率Ｈ20%
日入库量ρ20吨／天
全年天数T年360天
(计算因子)①2DK/P/H
平均日需用量(R)②R=D/360
(计算因子)③(ρ-R)/R
经济订货批量(Q)④??
?
???
?
???RPHDKEOQ??2
平均库存量(Q)⑤??
??????????RQQ21
订货次数(N)⑥N＝D/Q
全年订货费用(TCK)⑦TCK＝K×D/Q
全年存储费用(TCH)⑧TCP＝P×H×Q/2
全年总成本(TC)⑨TC＝TCK＋TCH
步骤2.4建立《单式价格折扣1》工作表
具有价格(单式)折扣的库存控制模型返回
项目及符号计算式编号单位方案（订货批量）＜650件≥650件
模型
数据
输入
优惠临界批量(Q△)件650
需求量(D)件1800018000
单价(P)元／件109
订货费用(K)元／次300300
年保管费用率(H)20%20%
(计算因子)2DK/P/H①
经济订货批量（Q）
PHDKEOQ2?
②件
采购批量(Q)=Min/Max(Q△，Q)③
订货次数(N)＝D/Q④
采购成本(TCP)＝D×P⑤元
订货成本(TCK)=N×K⑥元
保管成本(TCH)＝Q/2×P×H⑦元
采购总成本(TC)＝TCP＋TCK＋TCH⑧元
价格折扣模型
分批入库模型
模块7生产物流实验
154
(1)采购批量(Q)确定：
?如果计算的经济批量在优惠临界批量的范围内，则等于经济批量（Q）；
?如果计算的经济批量超出优惠临界批量范围，则按优惠临界批量——小于下界，采用下界Max
(Q△，Q)；超过上界，采用上界Min(Q△，Q)；
(2)以采购批量计算成本和费用；
(3)以采购总成本最低的方案为最佳采购批量。
步骤2.5建立《单式价格折扣2》工作表
具有价格(单式，多区段)折扣的库存控制模型返回
项目及符号计算式编号单位
方案（订货批量）
＜100100～250＞250
模型
数据
输入
优惠临界批量(Q△)件100250
需求量(D)套100010001000
单价(P)504847.5
订货费用(K)元／次404040
年保管费用率(H)0.250.250.25
(计算因子)2DK/P/H①
经济订货批量(Q)
PHDKEOQ2?
②件
采购批量(Q)=Min/Max(Q△，Q)③
订购次数(N)＝D／Q④
采购成本(TCP)＝D×P⑤元
订货成本(TCK)＝D／Q×K⑥元
保管成本(TCH)＝Q／2×P×H⑦元
采购总成本(TC)＝TCP＋TCK＋TCH⑧元
(1)采购批量(Q)确定：
?如果计算的经济批量在优惠临界批量的范围内，则等于经济批量（Q）；
?如果计算的经济批量超出优惠临界批量范围，则按优惠临界批量——小于下界，采用下界
Max(Q△，Q)；超过上界，采用上界Min(Q△，Q)；
?具有上、下界限制的区域（如100～250），计算式为Min（Max(Q△下，Q)，Q△上)，
其中Q△下为区域下界，Q△上为区域上界
(4)以采购批量计算成本和费用；
(5)以采购总成本最低的方案为最佳采购批量。
3、库存控制模型EXCEL模版
155
步骤2.6建立《复式价格折扣》工作表
具有价格（复式）折扣的库存控制模型返回
项目及符号计算式编号单位方案（订货批量）≤500501～2000≥2001
模型
数据
输入
n①②③
优惠临界批量(Q△n)套5002000
需求量(D)套720072007200
单价(Pn)1009590
订货费用(K)元／次100100100
年保管费用率(H)10%10%10%
订货
批量
分析
价差(r1)=Pn-1－Pn(n>1)①0
临界价格优惠(r2)=r1×Q△n-1②0
临界价格优惠累计(r3)=Σr2（累计计算）③0
订货费用(K＇)=K+r3④
(计算因子)2DK＇/P/H⑤
经济订货批量(Qn)
PHKDEOQ??2
⑥
订货批量(Qn)=Min/Max(Q△，Q)⑦
平均
价格
分析
采购总价（r4）=Pn×Qn+r3⑧
平均价格)(nPr4／Qn⑩
订货次数(N)nQD/?⑾
采购成本(TCP)nPD??⑿元
订货成本(TCK)KQDn??/⒀元
保管成本(TCH)HPQnn???2/⒁元
采购总成本(TC)=TCP+TCK+TCH⒂元
(1)r3含义：按照复式价格折扣的特点，应当以分段价格计算采购价；简便算法是以某一优惠区域
的价格统一计算，再加上前面区域的临界差价，如采购2500件，总价为：
500×100+1500×95+500×90=2500×90+500×（100-95）+2000×（95-90）
即r3=500×（100-95）+2000×（95-90）=12500，对采购批量≥2001的区域是一个常量，相当于每次订
购费用的增加额。
(2)采购批量确定，与步骤2.6类似；
(3)以采购批量计算成本和费用；
(4)以采购总成本最低的方案为最佳采购批量。
模块7生产物流实验
156
步骤2.7建立《运输价格折扣》工作表
具有运输折扣的库存控制模型返回
项目及符号计算式编号单位方案（订货批量）＜800件≥800件
模型
数据
输入
优惠批量(Q△)件800800
需求量(D)件12001200
单价(P)元／件1010
订货费用(K)元／次300300
年保管费用率(H)20%20%
运输价格(P运)元／件10.75
(计算因子)2DK/P/H①
经济订货批量(Q)
PHDKEOQ2?
②件
订货批量(Q)=Min/Max(Q△，Q)③
订货次数(N)＝D／Q④
订货成本(TCK)＝D／Q×K⑤元
保管成本(TCH)＝Q／2×P×H⑥元
运输成本(TCY)＝Ｄ×运输价格（P运）⑦元
订货库存总成本(TC)＝TCP＋TCK＋TCY⑧元
(1)采购批量确定，与步骤2.4类似；若分区段确定运输价格，则与步骤2.5类似；
(2)以采购批量计算成本和费用；
(3)以采购总成本最低的方案为最佳采购批量。
步骤2.8建立《允许缺货模型》工作表
允许缺货的库存控制模型返回
项目及符号编号计算式数值单位
模型
数据
输入
物料需求量D7200件/年
年日历天数T年年360天/年
订货费用K50元/次
订货周期L3天
物料存储费用率H72元/件·年
缺货损失j1.4元/件·天
日库存费用率h=H/T年年
日需求量R=D/T年年
(计算因子)①2DK/H
(调整因子)②(h+j)/j
允许缺货模型
运输价格折扣模型
3、库存控制模型EXCEL模版
157
（续前表）
经济订货批量(Q)③
jjhHKDEOQ??·2
经济保证供货量V④
jhjHKDV???·2
经济允许缺货量U⑤?????VQU
订货点B⑥????VLRB
订货次数(N)⑦N=D/Q
全年订货费用(TCK)⑧K·
QDTCK??
全年存储费用(TCH)⑨
??Q
DRVhTCH··22
全年缺货成本(TCS)⑩
?
??
Q
DRUhTC··S22
全年总成本(TC)⑾TCSTCHTCHTC???
步骤2.9建立《离散型需求模型》工作表
离散型需求库存控制模型返回
模型
数据
输入
周次123456789101112
需求量(件)127825132116821761485610819672
定货费用K＝100元／次期数(PeriodNum)＝12
库存费用H＝0.8元／件·周
在教师的指导下，完成以下步骤：
步骤2.9.1设置采购费用矩阵
步骤2.9.2设置采购组合可选方案矩阵
步骤2.9.3设置采购组合方案费用矩阵
步骤2.9.4采购组合方案费用分析
步骤2.9.5采购组合方案费用优化判断
步骤2.9.6采购期数优化判断
步骤2.9.7采购方案（达到以下效果）
采购方案
周次123456789101112合计
需求量
采购量
库存量
订货费用
存储费用
合计
离散型需求模型
模块7生产物流实验
158
步骤2.10建立《随机性库存模型》工作表——需求量随机，订货周期确定
随机性库存控制模型返回
项目数值单位
模型
数据
输入
售价(P)50元／件
库存管理费用率(h)4.0%
订购费用(K)100元／次
缺货损失费率(j)18元／件·次
订货周期(T)30天
每月需求量(Di)120140160180200220240260280300
出现概率(pi)0.020.030.050.150.270.240.120.070.030.02
（在教师指导下完成以下步骤）
随机性库存控制系统期望值分析
每月需求量(Di)120140160180200220240260280300
出现概率(pi)0.020.030.050.150.270.240.120.070.030.02
期望值(Ei)iipD?
每月平均需求量
（期望值,件／月）???300120iipDD
全年需求量N(件/年)DN??12
计算因子PhNK/2
经济采购批量（Q）
PhNKEOQ2?
单位存储费用
(H,元/件·年)hPH??
经济缺货概率
(设订货点为B)jNHQHQBDP???)(
缺货概率分析（确定订购点B）
B0120140160180200220240260280300
)(BDP?
)(BDP?
最佳订货点
【实验课时与考核标准】
1、实验课时：6课时；
2、提交实验成果
3、考核标准
(1)《定量订购控制》、《定期订购控制》、《一次订购分批入库》模型建立，40%；
(2)《单式价格折扣1》、《单式价格折扣2》、《单式复式价格》、《运输价格折扣》模型建立，40%；
(3)《允许缺货模型》、《离散型需求》、《随机性库存》模型运用，20%
随机性库存模型
4、“从至表”分析EXCEL模版
159
“从至表”分析的EXCEL模版建立与应用
【实验目的】
(1)《从至表》是生产物流和运输物流、配送物流十分重要的一个工具，要求学生能用EXCEL建
立和运用《从至表》分析摸版
(2)掌握EXCEL创建列表、名称定义、数据有效性定义得方法与运用
(3)了解Offset()、CountA()、函数功能与运用
【实验材料】
零件工艺路线图。某机械生产车间设备排列的初始顺序方案及加工零件的工艺路线如下，试用《从
至表》法，提出设备布置调整方案。
设备
零件号A毛坯库B铣床C车床D钻床E镗床F磨床G压床H检验台
001
002
003
004
【实验步骤与要求】
步骤1建立《从至表分析》工作簿
步骤2建立《工序系列》工作表
表1工序系列表
序号工序代码工序名称工序系列
1A毛坯库A毛坯库
2B铣床B铣床
3C车床C车床
4D钻床D钻床
5E镗床E镗床
6F磨床F磨床
7G压床G压床
8H内圆磨床H内圆磨床
9I锯床I锯床
10J检验台J检验台
【生产物流实验4】
①②③
④⑤
①②③④⑤⑥
①②③④
①②
③④⑤⑥
模块7生产物流实验
160
步骤2.1“工序系列”——用“&”符号连接工序代码和工序名称；
步骤2.2选中工序系列表区域，创建列表；
步骤2.3定义名称——选中“工序系列”，定义名称为“Process”
了解名称定义的方法：
?定义单元格名称(固定单元格、或由列表自动扩充）
?定义列名称（定长，确定工序数目）
?定义列名称（不定长，用Offset()、CountA()函数组合定义）
?用Rows()、Columns()函数，检查名称“Process”的长度与宽度
步骤3建立《零件加工路线》工作表
步骤3.1设置工序路线图表
表2工序路线图表
工序路线零件号
序号001002003004
1A毛坯库A毛坯库A毛坯库A毛坯库
2C车床G压床C车床G压床
3G压床C车床E镗床B铣床
4D钻床E镗床J检验台E镗床
5J检验台F磨床F磨床
6J检验台J检验台
7
（1）选中双线框区域，选择主菜单，设置“数据/有效性/序列来源=Process”；
（2）根据零件工艺路线图，利用数据有效性提供的下拉框，按工序路线依次输入工序。
步骤3.2提取工序路线符号
表3工序路线符号
工序路线符号零件号
序号001002003004
1AAAA
2CGCG
3GCEB
4DEJE
5JFF
6JJ
7
（3）用Index()、Match()函数组合，从表2、表中提取工序路线的工序符号；
（4）注意填充时的相对引用和绝对引用；超出表2各零件的工序范围时，显示错误值，将其单
元格内容清除。
4、“从至表”分析EXCEL模版
161
步骤3.3提取工序从至组合
表4工序从至组合
工序从至组合零件号
序号001002003004
1ACAGACAG
2CGGCCEGB
3GDCEEJBE
4DJEFEF
5FJFJ
（5）对表3的工序路线符号，用“&”符号依每一零件，从上至下连接，表示零件的从至路线；
（6）每一零件的从至组合数目为Ki－1（Ki为其工序数）
步骤4建立《从至表》工作表
表5初始从至表
工序顺序
A
毛
坯
库
B
铣
床
C
车
床
D
钻
床
E
镗
床
F
磨
床
G
压
床
J
检
验
台
从
至
数
运输量
至
从
工序
编码ABCDEFGJ合计
正向
从至
逆向
从至
工序编码12345678
A122416
B21130
C321380
D41140
E521350
F62240
G71113012
J800
合计01313234174012
运量合计52
步骤4.1“工序顺序”区域，设置“数据/有效性/序列/来源==Process”，按初始顺序选择工序；
步骤4.2在横向的“工序编码”区域内设置Left()函数公式，提取“工序顺序”区域数值的左边第一
个字符；纵向“工序编码”区域内设置Index()函数公式，引用横向“工序编码”区域相
应单元格的值；如此，改变“工序顺序”区域的值，工序编码将相应变化。
步骤4.3双线框区域设置Countif()函数公式，计算表4中符合表5横向与纵向“工序编码”组合的
工序从至组合数目；
步骤4.4合计用Sum()函数各行、列的从至数目；
步骤4.5运输量：用Sum()、Offset()函数组合，设置计算公式；“正向从至”是对角线之上的从至数
与对角线距离成绩之和；“负向从至”是对角线之下的从至数与对角线距离成绩之和；
步骤5从至表优化
模块7生产物流实验
162
步骤5.1运用“工序顺序”区域数据有效性提供的下拉框，两两调整工序顺序；
步骤5.2优化思路：从至数大的工序尽量向对角线靠拢；逆向（对角线以下）的从至数尽量减少，
并向对角线靠拢；
步骤5.3模型自动显示数据的变化，不断调整，使总运量达到最小，同时逆向运量达到最小值；
步骤5.4显示调整结果，即《满意从至表》如下所示：
表5满意从至表
工序顺序
A
毛
坯
库
C
车
床
G
压
床
E
镗
床
F
磨
床
J
检
验
台
D
钻
床
B
铣
床
从
至
数
运输量
至
从
工序
编码ACGEFJDB合计
正向
从至
逆向
从至
工序编码12345678
A12246
C212350
G3111391
E421340
F52220
J6000
D71101
B8114
合计0333241117266
运量合计32
?优化后的设备排序是：
A毛坯库?C车床?G压床?E镗床?F磨床?J检验台?D钻床?B铣床
?总运量降低38.5%，逆向运量减少50%。
【实验课时与考核标准】
1、实验课时：4课时
2、提交实验结果
3、考核标准：
(1)《工序系列》、《零件加工路线》工作表设置正确，30%；
(2)《初始从至表》设置、运行正确，40%；
(3)《满意从至表》调整正确，30%
5、作业排序EXCEL模版
163
作业排序的EXCEL模版操作与应用
【实验目的】
(1)运用Max()/Min()、Len()、Int()、Concatenate()、Large()/Small()函数及组合。
(2)熟练掌握作业排序和作业流程时间的计算模型建立、流程图绘制
【实验材料】
表1生产任务、加工时间与交货期限
零件名称??iJJ1J2J3J4J5J6J7J8Σ
加工时间（天）??iP10631487645
交货期限（天）??iD3520118625289
【实验步骤与要求】
步骤1.1建立《作业排序》工作簿，设置“n/1/F/Fmax”工作表
步骤1.2计算排序参数
表2n/1/F/Fmax作业排序——参数
参数(Parameters)
序号i12345678MaxNLenPrm
零件系列??iOJ1J2J3J4J5J6J7J8
加工时间（天）??iP106314876
交货期限（天）??iD3520118625289
紧迫性??i?
(1)紧迫性??)()(iPiDi???
(2)MaxN列：零件系列行MaxN=CountA(Oi)，为零件数目,Oi指选中??iO区域；
其他为各行MaxN=Max(Vi)，Vi指分别选中??iP、??iD；??i?数值区域.
(3)Len列：=len(MaxN)，数值长度，MaxN指选中MaxN数值区域单元格
(4)Prm列：计算因子，=Power(10,Len+1)-1
(5)要求：在EXCEL模版中，设置单元格公式计算和显示上述结果。
步骤1.3按先到先做原则(FCFS)安排作业顺序
表3按先到先做原则(FCFS)作业顺序
作业排序??iJΣ脱期项目数
加工时间（天）??iP—
交货期限（天）??iD——
完工期限（天）??iA——
脱期天数??in
【物流实验5.1】n/1/F/Fmax排序
【生产物流实验5】
模块7生产物流实验
164
(1)??iJ——设置Index()函数，按照初始顺序引用零件系列??iO
(2)??iP、??iD——设置Index()函数，以??iJ为标志，从表2中引用参数值（下同）
(3)完工期限
??
???
?
?
???
?
1)(
1)(
)(1
1
ikP
iiP
iAi
i
（下同）
(4)脱期天数
??
?????)()()()()()(0)(iAiDiDiAiAiDin（下同）；
(5)“脱期项目数”为脱期天数??0?in的项目数，’=CountIf(ni，’>0’)”，ni指选中??in数值区域。
步骤1.4按最小加工时间原则(SPT)作业顺序
步骤1.5按交货期优先原则(EDD)作业顺序
步骤1.6按紧迫性优先原则(SST)作业顺序
表4～6按(SPT／EDD／SST)原则作业顺序
排序参数nSΣ脱期项目数
作业排序??iJ
——
加工时间（天）??iP—
交货期限（天）??iD——
完工期限（天）??iA
——
脱期天数??in
（1）按SPT(表4)／EDD(表5)／SST(表6)原则，分别设置表4、表5、表6；
（2）设置排序参数(nS)区域的单元格公式：“=Int(CONCATENATE(PrmX－V(i),PrmI-i)”
式中：V（i）分别指选中表2的加工时间??iP数值区域单元格(表4)、交货期限??iD数值区域单
元格（表5）、紧迫性数值??i?区域单元格（表6）；PrmX指选中相应行的Prm数值区域单元格；
PrmI、i分别指向表2的序号（i）的Prm数值区域单元格和i数值区域单元格；
（3）设置作业排序??iJ区域的单元格公式：
“=INDEX(Oi,MATCH(LARGE(Sn,COLUMN(A:A)),Sn,0))”；
式中：Oi指向表2的零件系列??iO的区域；Sn分别指向表4、表5、表6的排序参数(nS)区域。
（4）注意单元格或区域的相对引用或绝对引用。
5、作业排序EXCEL模版
165
步骤1.7按Moore法则作业顺序，
表7按先到先做原则(FCFS)作业顺序
作业排序??iJΣ脱期项目数
加工时间（天）??iP—
交货期限（天）??iD——
完工期限（天）??iA——
脱期天数??in
?Moore法则，使脱期项目最少的安排施工顺序的法则。即按交货期优先法则逐项安排，先安排交
货限期最早的工作，如不脱期，按交货优无原则继续安排；如果脱期，则把此项任务放到最后施工，再按
交货期优先并无脱期原则继续安排。
（1）在??iJ区域内，设置数据有效性（“序列/来源”选中表2零件系列??iO区域）
（2）按照步骤1.3规则，设置??iP、??iD、??iA、??in区域单元格公式；如果显示出错信息，暂时
不予理会；
（3）作业排序??iJ——按照交货期优先原则(EDD)作业顺序依次选择、排列零件，若出现??0?in，
则将此项尽量移到最后，将后续任务前移，直至将任务全部安排完毕。
步骤1.8比较步骤1.2～步骤1.7，按不同原则排列的结果和脱期合计（或脱期项目数），
进行方案评价。
【实验材料】
表7零件工序矩阵
工件O(i)
工序M(k)J1J2J3J4J5J6
M1Pi13129456
M2Pi27468510
M3Pi34871512
M4Pi45711592
【实验步骤与要求】
步骤2.1设置“作业流程”工作表
步骤2.2设置“零件工序矩阵”区域，输入表7
步骤2.3设置“作业流程时间分析”区域
表8作业流程时间分析
j(i)
M(k)
J6J1J5J2J4J3
t1t2t3t1t2t3t1t2t3t1t2t3t1t2t3t1t2t3
M1Pi10220460410021201130316
M2Pi225704110415052007270633
M3Pi3751205170522083005350742
M4Pi41211344211325523233384446
【物流实验5.2】n/m(m≥2)作业流程时间及流程图
模块7生产物流实验
166
时间参数说明
（1）),(2ikt——工序)(kM对零件)(iJ的加工作业时间；
（2）),(1ikt——工序)(kM在加工零件)(iJ前的等待时间：
??
nimk
ikiktiktMax
iikt
k
ikt,...,1;,...,1
1,10),,2(3)1,(3
1),1(3
10
),(1??
??
???
?????
??
?
?
（3）),(3ikt——工序)(kM对加工零件)(iJ的加工结束时间
??
?
????
???
1),(2),(1)1,(3
1),(2),(1),(3
iiktiktikt
iiktiktikt
（4）nP——最大流程时间
),(3)(imtiPn?
即零件)(iJ在最后一个工序)(mM的加工结束时间),(3imt为其最大流程时间，即完工时间；
最后一零件的完工时间),(3nmt，即为整批零件的最大流程时间，即整批完工时间。
步骤2.4在)(iJ区域单元格设置数据有效性（序列来源选中表7的)(ij区域）
步骤2.5按照J6?J1?J5?J2?J4?J3作业排序，选择工件系列；
步骤2.6以)(iJ为标志从表7引用加工时间),(2ikt；
步骤2.7根据时间参数计算规则，设置t1，t3区域单元格的计算公式。
步骤2.8选中各)(iJ的t1、t2区域，插入堆积条线图，即为零件加工的流程图（输入、选择图表选项，
调整图面）。
n/m流程图
J6
J6
J6
J6
J1
J1
J1
J1
J5
J5
J5
J5
J2
J2
J2
J2
J4
J4
J4
J4
J3
J3
J3
J3
0246810121416182022242628303234363840424446
M1
M2
M3
M4
工
序
时间
步骤2.9变换作业排序，观察最大流程时间及流程图的变化。
5、作业排序EXCEL模版
167
【实验材料】
表9工时定额表
工件O(i)
工序M(i)ABCDE
M1车床加工（小时）P1(i)581047
M2铣床加工（小时）P2(i)119336
【实验步骤与要求】
步骤3.1设置“n/2/F/Fmax排序”工作表
步骤3.2设置“n/2/F/Fmax工序矩阵及作业排序(约翰逊算法)”区域
表10n/2/F/Fmax工序矩阵与作业排序(约翰逊算法)
序号(i)12345参数(Parameters)
工件O(i)
工序M(i)ABCDEmaxNlenPrm
M1车床加工（小时）P1(i)581047i5166
M2铣床加工（小时）P2(i)119336Pki112666
作业
排序
分组(Group)11222
排序参数nS65661656565864646696364669626467261
作业排序??iJABECD
步骤3.3工序分组：分组(Group)区域单元格公式：”=if(P1(i)<=P2(i),1,2”
式中：P1(i)、P2(i)指选中P1(i)、P2(i)数值单元格。
步骤3.4计算参数(Parameters)
i行，MaxN单元格公式：”=CountA(Oi)”，Oi指选中“工件O(i)”区域；
Pki行，MaxN单元格公式：”=Max(Pki)”，Pki指全部选中P1(i)、P2(i)数值区域；
Len单元格公式：”=Len(MaxN)”，MaxN指选中相关行的MaxN数值单元格。
Prm列单元格公式：“=(Power(10,len+1)-1)/96”，len指选中相关行的len数值单元格。
步骤3.5计算排序参数，设置nS区域公式：
“=INT(CONCATENATE(66-Group,PrmX-IF(Group=1,P1(i),-P2(i),PrmI-i))”
式中：Group、P1(i)、P2(i)指选中分组(Group)、P1(i)、P2(i)数值单元格；
PrmX、PrmI分别指选中i、Pki行的参数PRM数值单元格。
步骤3.6作业排序：
设置??iJ区域公式：“=INDEX(Oi,MATCH(LARGE(Sn,COLUMN(A:A)),Sn,0))”
式中：Oi指选中“工件O(i)”区域，Sn指选中排序参数nS区域
步骤3.7得到优化排序方案A?B?E?C?D
步骤3.8与【物流实验5.2】类似，分析作业流程时间，插入流程图。
【物流实验5.3】n/2/F/Fmax作业排序(约翰逊算法改进)
模块7生产物流实验
168
【实验材料】
表11工序矩阵单位：天
工件O(i)
工序M(k)J1J2J3J4
MAtiA158612
MBtiB3156
MCtiC41057
【实验步骤与要求】
步骤4.1设置“n/2/F/Fmax(约翰逊—贝尔曼法)”工作表
步骤4.2设置“n/2/F/Fmax工序矩阵及作业排序”区域
表12n/2/F/Fmax工序矩阵与作业排序(约翰逊—贝尔曼拓展法)
序号(i)1234条件判断
O(i)
M(k)J1J2J3J4最值判断
MAtiA158612MinTA6符合约翰逊—贝
尔曼拓展法条件MBtiB3156MaxTB6M
CtiC41057MinTC4
矩阵转
换
MGtiG1891118参数(Parameters)maxNlenPrm
MHtiH7111013i4166
作业排
序
分组(Group)2122Tik182666
排序参数(Sn)6467363656576564676616467960
作业排序J(i)J2J4J3J1
步骤4.3满足约翰逊—贝尔曼拓展法条件判断
最值，即计算MA、MC的最小值，MB的最大值
当MinTA≥MaxTB或MinTiC≥MaxTB，显示“符合条件”；否则显示“不符合条件”；
对不符合条件的，仍然可以使用约翰逊—贝尔曼拓展法近似地进行排序，得到近优解。
步骤4.4矩阵转化（假设工序）
将工序A+B合并为MG，C+D合并为MH
步骤4.5作业排序——分组(Group)、排序参数(Sn)、作业排序J(i)
与【物流实验5.3】类似，对假设工序G、H进行排序，得到优化排序J2?J4?J3?J1。
步骤4.6与【物流实验5.2】类似，分析作业流程时间，插入流程图。
【物流实验5.4】N/3/F/Fmax作业排序(约翰逊—贝尔曼拓展法)
5、作业排序EXCEL模版
169
【实验材料】
表13工序矩阵
工件O(i)
工序M(k)J1J2J3J4
M1Pi11263
M2Pi28429
M3Pi34582
【实验步骤与要求】
步骤5.1设置“n/m/F/Fmax帕尔姆法”工作表
步骤5.2设置“4/3/F/Fmax工序矩阵及作业排序(帕尔姆法）”区域
表14
序号(i)1234k＇
=k-(m+1)/2kJ(i)M(k)J1J2J3J4
1M1Pi11263-1
2M2Pi284290
3M3Pi345821
作业排序m=3λi=332-1
步骤5.3计算λi：按照帕尔姆法计算公式??
ik
m
ki
pmk?????
?1
2/)1(?
(1)m=3（工序数目），设置单元格计算公式，计算每个工序乘积因子k＇=k-(m+1)/2；
(2)设置λi区域单元格计算公式：”=SUMPRODUCT(Pik,k＇)”
式中：Pik指选中J(i)列的作业时间(Pik)数值区域；k＇指选中成绩因子k＇列的数值区域
步骤5.4按照λi不增的顺序排列，得出满意的作业排序：
排序方案1：J1?J2?J3?J4
排序方案2：J2?J1?J3?J4
步骤5.5与【物流实验5.2】类似，分析作业流程时间，插入流程图，评价作业排序方案。
【实验材料】
表15工序矩阵
工件O(i)
工序M(k)J1J2J3J4J5J6
M1Pi1388912
M2Pi21322475
M3Pi371610960
M4Pi41578101114
M5Pi53469111
【物流实验5.6】N/m/F/Fmax作业排序(关键工序法)
【物流实验5.5】N/m/F/Fmax作业排序(帕尔姆法)
模块7生产物流实验
170
【实验步骤与要求】
步骤6.1设置“n/m/F/Fmax关键工序法”工作表
步骤6.2设置“n/m/F/Fmax工序矩阵及作业排序(关键工序法）”区域
表166/5/F/Fmax工序矩阵与作业排序(关键工序法）
序号(i)123456工序负荷pk
kO(i)M(k)J1J2J3J4J5J6
1M1Pi138891231
2M2Pi2132247533
3M3Pi37161096048
4M4Pi4157810111465
5M5Pi5346911134
关键工序π=4maxPk65
组号
(g_No)符号条件合计数(Pg)项目数(Num)
1PAiPi1
2PBiPi1=Pim2300221403
3PCiPi1>Pim0460002
组别n1233221参数(Parameters)
计算系数n21-1-1111MaxNLenPrm
计算值n3234622147i6166
排序参数(Sn)n4664436637066372664446645266559Pm232666
工序排序J(i)J6J5J4J1J3J2
步骤6.3设置工序负荷Pk区域单元格计算公式
即等于各工序所承担工件的作业时间之和（横向求和）：?
?
?m
ikik
pp
1
步骤6.4确定关键工序(π)，π数值区域计算公式，”=MATCH(MAX(Pk),Pk,0)”
式中，Pk指选中Pk数值区域
步骤6.5以关键工序(π)为界限，对工件J(i)按三个条件求和：
(1)当imipp?1，??
?
?1
1
?
kkiiA
pP
PAi区域单元格公式，”{=SUM(Pik(Pi1
式中：乘积因子Pik指选中工件J(i)列的作业时间数值区域；
乘积因子”Pi1
小于末工序作业时间(Pim)，满足者返回逻辑数值1，不满足者返回逻辑数值0；
5、作业排序EXCEL模版
171
乘积因子”k<π”，k指选中k数值区域，π指选中π数值区域单元格，即工序系列中位于关键工序之前
的工序，满足者返回逻辑数值1，不满足者返回逻辑数值0
同时满足Pi1
之和；不足此条件，PAi则返回数值0。
“{}”表示数组公式，按Ctrl+Shift+Enter组合键确定，下同。
(2)当imipp?1，?
???
m
kkiiCpP1?
PCi区域单元格公式，”{=SUM(Pik(Pi1>Pim)(k>π))}”，公式含义与输入方法与(1)类似。
(3)当imipp?1，若第1组（A组）数目小于第3组(C组)时，按(1)求和；
若第1组（A组）数目大于第3组(C组)时，按(2)求和；
PBi区域单元格公式，”{=SUM(Pik(Pi1=Pim)(CHOOSE(IF(NumAπ)))}”
公式含义与输入方法与(1)，(2)类似；Choose()函数指当A组数目(NumA)小于C组数目(NumC)时，
满足k<π条件，否则满足k>π条件；
NumA、NumC指分别选中第1、3组的项目数(Num)的数值单元格。
步骤6.6设置项目数(Num)区域单元格公式：”{=SUM(IF(Pg>0,1,0))}”
式中，Pg指分别选中PAi、PBi、PCi数值区域。
步骤6.7设置“组别(n1)”区域单元格公式，”{=SUM(IF(Pgi>0,1,0)g_No)}”
式中，Pgi指选中工序J(i)列的PAi、PBi、PCi数值区域，g_No指选中组号g_No列区域；表示若PAi、PBi、
PCi大于0（并且只有其中之一大于0），就属于A、B或C组（用数值1、2或3表示）。
步骤6.8设置“计算系数(n2)”区域单元格公式，”=CHOOSE(n1,1,IF(NumA<=NumC,1,-1),-1)”
式中，n1指选中J(i)列的组别(n1)区域单元格
NumA、NumC指分别选中A、C组项目数的数值单元格。
步骤6.9设置“计算值(n3)”区域单元格公式，”{=SUMPRODUCT(Pgi,n1/n1))}”
式中，Pgi、n1含义与前面说明相同；
Sumproduct()是数值乘积之和，提取工件J(i)列按步骤4.5设置条件计算的数值。
步骤6.10与【物流实验5.3】类似，设置参数，进行作业排序，
Pm行的MaxN值为“计算值(n3)”的最大值；
排序参数(Sn)区域单元格计算公式：”=INT(CONCATENATE(PrmI-n1,PrmX-n2n3))”
式中：PrmX、PrmI含义与步骤5.3类似；
n1、n2、n3指选中j(i)列的n1、n2、n3的数值单元格。
以Sn为标准进行作业排序，得到优化排序方案：J6?J5?J4?J1?J3?J2
步骤6.11与【物流实验5.2】类似，分析作业流程时间，插入流程图。
模块7生产物流实验
172
【实验材料】
表17工序矩阵
工件O(i)
工序M(k)J1J2J3J4J5J6J7J8
M1Pi133945699
M2Pi274685101212
M3Pi3487151264
M4Pi45711592812
【实验步骤与要求】
步骤7.1设置“n/m/F/FmaxCDS排序法”工作表
步骤7.2设置“n/m/F/Fmax工序矩阵及作业排序(CDS排序法)”区域
表188/4/F/Fmax工序矩阵与作业排序(CDS排序法）
序号(i)123456
工件O(i)
工序M(k)J1J2J3J4J5J6
M1Pi13129456
M2Pi27468510
M3Pi34871512
M4Pi45711592
方案思路(X)3工序数目(m)4
工序G方案M1M2M3
工序H方案M2M3M4
PiG142422131528
PiH161924141924
参数(Parameters)
MaxNLenPrm
i6166
Pik282666
分组(Group)121112
排序参数（Sn)656526564685646564463656536265651616469060
作业排序J(i)J4J1J5J3J6J2
作业时间(Tik)4359612
8756104
1457128
5591127
结束时间（Fik）4712212739
121924304044
132329375260
182838495467
【物流实验5.7】N/m/F/Fmax作业排序(CDS排序法)
5、作业排序EXCEL模版
173
步骤7.3工序数目(m)数值单元格公式：”=CountA(Mk)”，式中：Mk指选中工序M(k)区域；
步骤7.4输入方案思路X(1至ｍ－１)
步骤7.5工序G方案区域：”=IF(COLUMN(A:A)<=X,INDEX(Mk,COLUMN(A:A)),"")”
工序H方案区域：”=IF(COLUMN(A:A)>X,INDEX(Mk,COLUMN(A:A)),"")”
式中：X指选中方案思路(X)数值单元格；Mk指选中工序M(k)区域；
步骤7.6PiG区域：”{=SUM(OFFSET(Pik,0,0,X,1))}”
PiH区域：”{=SUM(OFFSET(Pik,m-X,0,X,1))}”
式中：Pik指选中O(i)列的Pik区域；
m指选中工序数目(m)数值单元格；
X指选中方案思路(X)数值单元格。
步骤7.7计算参数与作业排序
与【物流实验5.3】类似，对假设工序G、H进行排序作业时间(Tik)：以作业排序J(i)为标志引
用Pik
步骤7.8结束时间（Fik）：
第1行(k=1)：F(1,1)=T(1,1)(i=1);F(i,1)=F(i-1,1)+T(i,1)(i>1);
第1列(i=1)：F(1,k)=F(1,k-1)+T(1,k)(k>1)
其他行列(i>1，k>1)：F(i，k)=MAX[F(i-1，k),F(i，k-1)]+T(i，k)
步骤7.9建立流程时间区域
i123456789101112
R(i)
k
J4J1J5J3J6J2
t1t2t1t2t1t2t1t2t1t2t1t2
10403050906012
24807050601004
312164151731208
413555190113267
?R(i)区域：”=IF(MOD(i,2)=0,"",INDEX(J(i),INT((i-1)/2)+1))”
式中：J(i)指选中作业排序J(i)区域；i指选中i区域单元格；
?t2区域：”=INDEX(Tik,k,INT(i/2))”
?t1区域：
当i=1，单元格公式：”=IF(k=1,0,INDEX(Fik,k-1,INT(i-1)/2)+1))”
当i>1，单元格公式：
模块7生产物流实验
174
”=IF(k=1,0,MAX(INDEX(Fik,k-1,INT((i-1)/2)+1)-INDEX(Fik,k,INT((i-1)/2)),0))”
式中：Tik选中作业时间Tik区域；Fik选中结束时间Fik区域；
k指选中k区域单元格，i指选中i区域单元格；
步骤7.10选中双线框区域，插入“堆积条形图”，确定图标选项，调整图标元素位置，绘制流程图：
作业排序(CDS排序法)
J4
J4
J4
J1
J1
J1
J1
J5
J5
J5
J5
J3
J3
J3
J3
J6
J6
J6
J6
J2
J2
J2
J2
J4
0510152025303540455055606570
M1
M2
M3
M4
工
序
时间
步骤7.11在方案思路(X)数值单元格分别输入1，2，3，可得到3种排序方案
方案思路(X)=1，作业排序J(i)={J1?J4?J5?J3?J2?J6}，最大作业流程时间63天；
方案思路(X)=2，作业排序J(i)={J5?J3?J2?J6?J1?J4}，最大作业流程时间68天；
方案思路(X)=3，作业排序J(i)={J4?J1?J5?J6?J3?J2}，最大作业流程时间67天。
经比较选择第1方案。
【实验课时与考核标准】
1、实验课时：8课时
2、提交实验成果
3、考核标准
(1)n/1/F/Fmax作业排序10%
(2)n/m(m≥2)作业流程时间及流程图20%
(3)n/2/F/Fmax作业排序(约翰逊算法改进)10%
(4)N/3/F/Fmax作业排序(约翰逊—贝尔曼拓展法)15%
(5)N/m/F/Fmax作业排序(帕尔姆法)15%
(6)N/m/F/Fmax作业排序(关键工序法)15%
(7)N/m/F/Fmax作业排序(CDS排序法)15%
6、工时定额核定与产能平衡EXCEL模型
175
工时定额核定与产能平衡的EXCEL模型建立与运用
【实验目的】
1、掌握运用EXCEL工具，建立工时定额核定、产能平衡模版
2、掌握选择产能平衡和方案的方法
【实验步骤与要求】
步骤1建立《工时定额核定》工作表，记录工时测定数据，计算公式定额
表1工时测定记录表分析日期年月日
作业编号A001
作业名称帮面缝制
设备工具名称针车
操作者×××××××××××××××合计工时定额
(m／双)工序数量时间数量时间数量时间数量时间数量时间数量
(双)
时间
(m)
工序1521527520528524
工序2536537540532530
工序3546548552541538
工序4527521524536532
步骤2建立《工段产能平衡与派工》工作表，核定工段生产能力，并提出派工计划
表2（针车）工段生产平衡及产量定额核定
配备工人数(S)48班工时(小时,H)8工时利用率(η)90%
有效班工时)(eF=H×η×60(m)
工序
工时定额
(m/双)工时比例安排人数
小时产量定额(双/小时)班产量定额(双/班)
个人工序个人工序
①②＝①/Σ①③＝②×S①④/eF?⑤＝④×③⑥＝8×④⑦＝⑦×⑥
工序1
工序2
工序3
工序4
工段100%——
【生产物流实验6】
模块7生产物流实验
176
步骤3建立《车间生产平衡》工作表
步骤3.1产能差异分析
生产任务与能力
项目与符号项目计算式数据及分析
生产
任务
D订单交货量(双)12000
N△业务外包量(双)
N实际生产任务(双)D－N△12000
T计划工期（天）4
R日计划产量（双/天）N／T3000
生产
能力
H制度班工时(小时)8
η工时利用率(%)90%
Dt工时定额(双／ｈ)26.4
S现有配备工人数(人)100
M日产能（产量定额）(双／日)H×η×S／Dt1636
产能
差异
T完工周期（天）N/M7.33
△T工期差异(天)T－T3.33
NT预期产量（双）M×T6545
N△T产量差异——欠产（双）N△－M×T5455
步骤3.2产能平衡
产能平衡
策略编号项目计算式方案Ⅰ方案Ⅱ方案Ⅲ方案Ⅳ
延长
工期
①计划延长工期（△T，天）1111
②计划完工周期（T＇，天）T+△T5555
延长
工时
③计划加班工时（△H，ｈ）0123
④实际班工时（H△，ｈ）H+△H891011
能力
平衡
⑤日产量定额(M△,双／日)④×η×S/Dt1636184120452250
⑥实际完成产量(N＇,双)⑤×②818292051022711250
⑦产量差异——欠产（N△T,双）N△－⑥381827951773750
⑧工期差异(△T＇，天)⑦／⑤1111
增派
工人
⑨需要增加日工时（h/日）⑦×Dt/60/η/②33624615666
⑩需增派工人数(△S,人)⑨/④4227166
步骤3.3改变方案，提出最合适的产能平衡策略和方案组合
【实验课时与考核标准】
1、实验课时：4课时
2、考核标准：
(1)建立模版60%
(2)提出产能平衡策略和方案40%
7、生产能力核定与成型线平衡EXCEL模型
177
生产能力核定与成型组生产线平衡的
EXCEL模版操作与应用
【实验目的】
1、掌握应用EXCEL计算生产能力的方法与技能
2、了解运用EXCEL模版进行成型组生产线平衡
【实验步骤与要求】
步骤1建立《生产能力核定》工作簿
【实验材料】
表1某鞋业公司生产工序及定额、配备数据
工序号工序定额产量定额单位(小时)工时效率配备人数配备机(组)数
010成型300(双/线)90%1353
020针车120(双/组)85%2358
030高频/电绣150(双/人)85%246
040冲裁/定位240(双/台)85%129
070配料120(双/台)85%2412
步骤2设置《单一品种产能核定》工作表
表2产能核定
工序号工序
定额
产量
定额单位
(小时)
额定
日工作时数
工时
效率
配备
人数
配备
机(组)数
生产能力
核定单位日产能
①②③④⑤⑥⑦⑧
步骤2.1根据表1，输入相关数据
步骤2.2第⑦列单元格，设置数据有效性，“序列/来源=人,机”
步骤2.3第⑧列单元格，设置公式：”=③④IF(⑦="机",⑥,⑤)①”
式中：数据序号指选中行内相应的单元格。
步骤2.4根据日产能，以成型线生产能力为基准，核定企业的日产能。
单一品种生产能力的核定
【生产物流实验7】
模块7生产物流实验
178
步骤3设置《代表产品法产能核定》工作表
表3代表产品法核定生产能力——(铣床组)
设备数(S)3日工作时数(H)15.5
设备停修率(q)5%全年工作日数(T0)280
单台设备有效工时
)1(00qHTF????
设备组有效工时
SFFe??0
产品序列
i
计划产量
iN
单位产品台时定额
itD
(小时／台)
换算系数
dtitiDDK/?
折合代表产品产量
diN
①②③=②／dtD④=③×①
A5020
B10030
C12540
D2580
合计(?
diN
)
代表产品
(Jd)
代表产品工时定额
dtD
生产能力
dt
edDSFM??
设备组负荷系数
d
diMN???
C
步骤3.1根据表3，设置工作表格式并输入数据；
步骤3.2设置0F、eF数值单元格计算公式；
步骤3.3在代表产品(Jd)数值区域单元格，设置数据有效性,”序列/来源=i”
式中i指选中产品序列i区域；
步骤3.4在数据有效性提供的下拉框中选择代表产品（本例为C）；
步骤3.5设置
dtD
数值区域单元格公式：”=INDEX(
itD
,MATCH(Jd,i,0))”
式中i指选中产品序列i区域；
itD
指选中台时定额
itD
区域；
Jd指选中代表产品(Jd)数值单元格。
步骤3.6按计算式提示，设置生产能力（dM）、换算系数（iK）、折合代表产品产量（
diN
）
及合计(?
diN
)、设备组负荷系数(?)等区域单元格的计算公式。
步骤3.7改变代表产品选择，观察有关数值的变化。
多品种生产能力的核定
7、生产能力核定与成型线平衡EXCEL模型
179
步骤4设置《比例系数法产能核定》工作表
表4比例系数法核定生产能力——(车床组)
设备数(S)15日工作时数(H)
设备停修率(q)全年工作日数(T0)
单台设备有效工时
)1(00qHTF????
4800（小时/年）设备组有效工时
SFFe??0
产品系列(Ji)ABCD
计划产量(iN)①1008016060
台时定额(
tiD
)②20027010040
加工总量
tiiiDNT??
③＝①×②
工时比例TTkii/?④＝③／Σ③
工时分配
ieikFF??
⑤＝
eF×④
生产能力
tiiiDFM/?
⑥＝⑤／②
总工作量??
iTT
比例系数
TFke/?
设备负荷率
eFT??
算法2
产品系列(Ji)ABCD
生产能力
iiNkM??
步骤4.1根据表4，设置工作表格式并输入数据；0F直接给出数值。
步骤4.2按计算式提示，设置相关数值单元格公式。
步骤5设置《假定产品法产能核定》工作表
表5假定产品法核定生产能力——(车床组)
设备数(S)15日工作时数(H)
设备停修率(q)全年工作日数(T0)
单台设备有效工时
)1(00qHTF????
4800（小时/年）设备组有效工时
SFFe??0
产品系列
(Ji)
计划产量
iN
台时定额
tiD
加工总量
tiiiDNT??
产量比重
fiiNNk/?
具体产品生产能力
fiiMkM??
①②③＝①×②④＝①／fN⑤＝④fM?
A100200
B80270
C160100
D6040
假定产品(Jf)??ifNNfftNTD/?????kitiiDNT1ftefDFM?
设备组负荷率
eFT/??
步骤5.1根据表5，设置工作表格式并输入数据；0F直接给出数值。
步骤5.2按计算式提示，设置相关数值单元格公式。
模块7生产物流实验
180
步骤6设置《成组生产线均衡》工作表
【实验材料】
表6工序标准工时及配备限制
No.工序名称标准工时（s）限制配备数No.工序名称标准工时（s）限制配备数
1鞋面擦糊12.5218大底擦胶6.25
2皮套擦糊10.0419贴大底13
3鞋头擦胶19.2320压底8.5
4入皮套11.4121鞋面清洁入冷却19
5后踵整形9.9122拔楦10.01
6钉中底5.9723钉跟4.89
7上中底及鞋面胶12.2424削内里9.18
8前帮17.29225擦中底胶8.47
9腰帮31.426贴中皮11.69
10后帮17.43227削边补漆12.38
11入烘箱4.2128塞纸团9.31
12去中底钉8.9829撑筷7.89
13按摩8.1130烫鞋面11.26
14鞋面打粗9.1131洗大底3.23
15放鞋、洗大底12.2332评检12.27
16鞋面擦第一次胶11.1133小包装13.6
17鞋面擦第二次胶9.19助理人员2
步骤6.1设置流水线特征值区域，输入数据。
表7成型组流水线数据
计划产量(Q)12000件轮班数(b)1
计划工期(T)4天班工时(H)8小时
工时利用率(p)90%
有效工时
pHbTT?????3600效
秒小时产量定额
rMh3600?
（件/小时）
节拍
QTr效?
秒/件班产量定额
HMMhb??
（件/班）
步骤6.2按照计算式提示，设置数值区域单元格公式。
步骤6.3设置流水线平衡区域
成型组流水线平衡
7、生产能力核定与成型线平衡EXCEL模型
181
表8成型流水线平衡
No.工序分解
标准工时
（s）
需要
配备数
初始配备生产线均衡工作地
负荷系数人数工序时间人数工序时间
①②③④⑤⑥⑦
（1）根据表6，输入（或拷贝）序号、工序名称、标准工时等数据；
（2）需要配备数②系列单元格公式：”=ROUNDUP(①/r,0)”
式中：①指选中本行标准工时①列的单元格；r指选中节拍r数值单元格
（3）初始配备人数③系列，每个工序按一个工作位（1名工人）安排，助理人员按表7种的限制数
安排；
（4）工序时间④系列单元格公式：”=①/③”
式中：①、③分别指选中本行标准工时①列、初始配备人数③列的单元格；
步骤6.4将初始配备人数③的数值拷贝至生产线均衡人数⑤；
步骤6.5将初始配备工序时间④的单元格公式拷贝至生产线均衡工序时间⑥
步骤6.6设置工作地负荷系数⑦列单元格公式：”=⑥/⑦”；设置区域单元格条件格式，单元格数值大于1
时，字体格式为红色，加粗。
步骤6.7设置流水线平衡分析区域
表9流水线均衡分析
序号项目初始配备均衡结果计算说明AB
x1总工作时间（分/双）＝标准时间合计(÷60)
x2配备人数合计＝各工序配备人数总和+助理人数
x3标准站时间（秒）＝“瓶颈”单件工时定额
x4总标准工时（分／双）＝标准站工时×配置人数合计/60
x5日产能（双）＝日工作时数／标准站时间
x6产量提高率（%）＝（均衡后产能－均衡前产能）／均衡前产能×100%
x7生产线平衡效率（%）＝总工作时间/总标准工时×100%
x8人均日产量（双／人）＝日产能／配备人数
x9工效提高率（%）＝（均衡后人均产量－均衡前人均产量）／均衡前人均产量×100%
模块7生产物流实验
182
（1）x1行单元格公式——“=Sum(①)/60”；式中：除以60，将时间单位转化为分钟；
（2）x2行单元格公式——A列”=Sum(③)”，B列“=Sum(④)”；
（3）x3行单元格公式——A列”=Max(④)”，B列“=Max(⑥)”；
（4）x4行单元格公式——A列”=x3Ax2A/60”，B列“=x3Bx2B/60”；
（5）x5行单元格公式——A列”=Hb3600/x3A”，B列”=Hb3600/x3B”；中：乘以3600，将时间
单位转化为秒；
（6）x6B单元格公式：”=(x5B-x5A)/x5A”
（7）x7行单元格公式——A列”=x1A/x3A”，B列”=x1A/x3B”，
（8）x8行单元格公式——A列”=x5A/x2A”，B列列”=x5A/x2B”
（9）x9B行单元格公式：”=(x8B-x8A)/x8A”
以上各式中，序号①、③、④、⑥之选中表8中相应列区域；xNA、xNB指选中表9的xN行A列或
B列单元格；H、b指选中表7的H、b数值单元格。
步骤6.8选中表8工序时间⑥列的数值区域，设置“条件格式”，单元格数值大于节拍r时，字体格式为绿色；
单元格数值等于标准站时间(x3B)时，字体格式为红色，加粗；
步骤6.9寻找表8工序时间⑥列单元格为红色的工序，均衡人数⑤依次加上1，直至工作地负荷系数在合理
的范围（小于或接近1）；
步骤6.10根据实际情况，对负荷系数偏小的工作地提出分解合并方案。
【实验课时与考核标准
1、实验课时：4课时
2、考核标准：
(1)单一品种生产能力的核定30%
(2)多品种生产能力的核定30%
(3)成型组流水线平衡40%
8、产品产量决策EXCEL模型
183
产品产量决策的EXCEL模版建立与应用
【实验目的】
1、掌握运用Excel进行产品产量决策的方法
2、熟练运用图表插入技术与方法
【实验步骤与要求】
步骤1建立《产品产量决策》工作簿
表1【实验材料】某企业产品1996～2007年度销售统计资料(单位：万元)
年度199619971998199920002001200220032004200520062007
销售额9147162685196326853708516151375891729147325814
步骤2设置《销售趋势预测》工作表
步骤2.1输入（或拷贝）表1数据；
步骤2.2选中销售额区域，插入图表（簇状柱形图）；
步骤2.3添加趋势线，在“选项”选中“公式”、“R2值”；依次选择趋势线类型，观察趋势线
与销售柱形图拟合情况，记录各类型公式和R2值；
步骤2.4理解R2值得含义
步骤2.5最后选中趋势线类型——4阶多项式（前推1期），预测2008年（X=13）的销售额预测
值；
步骤2.6根据趋势线，划分该产品的产品寿命周期阶段
步骤2.7依次修改图表类型为“折线图”、“X-Y散点图”，观察图形效果；
步骤2.8输入图表选项，调整图标位置，使图表最为美观。
图1销售预测图
y=-0.5167x
4
+0.2204x
3
+103.54x
2
+1.3941x+775.95
R
2
=0.8979
0
1000
2000
3000
4000
5000
6000
7000
8000
12345678910111213期数
销
售
额
【生产物流实验8】
实验8.1销售趋势预测
模块7生产物流实验
184
表2【实验材料】某产品成本数据(单位：元，件)
计划销售量Q6,000单位变动成本Vα360
固定成本F720,000单位产品价格P600
盈亏平衡销售量Q0盈亏平衡销售额S0
步骤3设置《盈亏平衡分析》工作表
步骤3.1输入（或拷贝）表2数据；
步骤3.2按照
?VP
FQ??0计算盈亏平衡点产销量
步骤3.3按照计算盈亏平衡点销售额PQS??00
步骤3.4建立01?Q、QQ2.12?，构建下列函数表，
表3盈亏平衡分析
序号项目1Q2Q
1产销量(Q)
2固定成本(F)
3变动成本?VQV??)(
4总成本FVQC????)(
5销售收入PQS??)(
6利润CSE??)(
步骤3.5选中项目、1Q、2Q数值区域，插入“X-Y散点图”；
步骤3.6添加“源数据”系列，X值=0Q，Y值=0S，名称=“盈亏平衡(B)”
步骤3.7添加“盈亏平衡(B)”点的误差线，自定义“X误差量”=0Q?，“Y误差量”=0S?
步骤3.8输入图表选项，调整图标位置，使图表最为美观。
图2盈亏平衡分析图
总成本(C)
固定成本(F)
变动成本(V)
销售收入(S)
利润(E)
盈亏平衡(B)
3000
1800000
-1,000,000
0
1,000,000
2,000,000
3,000,000
4,000,000
5,000,000
010002000300040005000600070008000产销量(Q)
金
额
(
Y
)
实验8.2盈亏平衡分析
8、产品产量决策EXCEL模型
185
表4【实验材料】产品规划数据
产品序列计划产量
产品利润分析资源需求分析
产品市场需求单
价
单位
成本
单位
利润
产品利润
总额车床铣床磨床
产品1x12501609090x12x15x17x11500≤x1≤2500
产品2x2400240160160x28x24x28x22000≤x2≤6000
产品3x34003604040x34x38x33x3x3＝2000
产品4x4300200100100x42x45x45x41000≤x4≤1500
目标函数与资源约束maxΣ410004300052500
步骤4设置《线性规划》工作表
步骤4.1如果第一次运行规划求解，必须先加载宏（按照EXCEL说明操作）：
步骤4.2根据表4数据，构建规划求解模版如图3格式
图3线性规划求解(Excel)模版
(1)区域1，为变量区域(C5:F5)
(2)区域2，为公式区域：
单位利润（合计）单元格公式：=SUMPRODUCT($C$5:$F$5,C8:F8)
车床行（合计）单元格公式：=SUMPRODUCT($C$5:$F$5,C9:F9)
………………
步骤4.3选择工具?规划求解，设置规划求解参数
实验8.3线性规划法
模块7生产物流实验
186
(1)目标单元格为单位利润（合计）单元格公式：$G$8
(2)目标函数等于“最大值”
(3)可变单元格为变量区域：$C$5:$F$5
(4)约束条件：选择添加按钮，按照表4表示的资源约束和产量约束条件，将其用EXCEL逻辑语
句完整、准确地表达；
(5)点击选项，进入“规划求解选项”，选中“采用线性模型”、“假定非负”二个选项：
(6)点击求解，显示“规划求解找到一解，可满足所有的约束即最优状况”，点击确定，则规划求
解结束。
步骤4.4求解结果：
规划求解的结果是：
?
?
?
???
?
?
?
?
?
11244
20003
34692
15001
x
x
x
x
最大利润额：maxE=882440
经验证，同时满足所有的资源约束和产量约束条件。
8、产品产量决策EXCEL模型
187
表5【实验材料】多品种产销量、成本、价格数据（单位：万件，元，元/件）
产品系列
(Ji)
销售单价单位变动成本计划产销量单位边际贡献边际利润率边际利润计算数
①②③④⑤⑥Pn
A201030
B201120
C302410
D251520
固定费用(F，万元)500目标利润(E,万元)600
MaxX=max(⑤)100LX=Power(10,len(MaxX)+1)-1
MaxI=countA(Ji)LI=Power(10,len(MaxI)+1)-1
步骤5设置《临界收益法》工作表
步骤5.1输入（或拷贝）表5，设置单元格公式并计算
(1)单位边际贡献④＝销售单价①－单位变动成本②；
(2)边际利润率⑤＝单位边际贡献④／销售单价①；
(3)边际贡献⑥＝单位边际贡献④×计划产销量③；
(4)按MaxX、MaxI、LX、LI计算式提示，设置数值区域单元格公式；
(5)计算数(Pn)“=INT(CONCATENATE(LX-⑤100,LI-ROW(1:1))”，向下填充。
步骤5.2设置临界收益分析区域
表6临界收益分析
序号
(i)
产品名称
(Ji)
计划
产销量
销售
单价
销售
收入
边际
利润率
边际
利润
边际利润
累计
临界
收益
保本
销额
保本
销量
保利
销额
保利
销量
⑦⑧⑨⑩⑾⑿⒀⒁⒂⒃⒄
1
2
3
4
合计
(6)表6，表7单元格计算公式和步骤
(1)表7中，产品按照边际利润率⑥降序排列；单元格公式：
”=INDEX(Ji,MATCH(SMALL(Pn,ROW(1:1)),Pn,0))”
式中：Ji指选中表6的J(i)区域；Pn指选中表6的Pn区域；
(2)在表7中，以产品系列J(i)为标志，运用INDEX()、MATCH()函数组合，分别引用表6的计
划产销量⑦、销售单价⑧、、边际利润率⑩、边际利润⑾
(3)销售收入⑨＝产销量⑦×销售单价⑧
实验8.4临界收益法
模块7生产物流实验
188
(4)第1行边际利润累计⑿＝第1行边际利润⑾；
其他行的边际利润累计⑿＝上1行边际利润累计⑿＋本行边际利润⑾
(5)临界收益⒀＝边际利润累计⑿－固定费用(F)
(6)保本销额⒁区域单元格公式：
”=IF(⒀<0,⑨,IF(i=1,F/⑩,IF(⒀i-1<0,-⒀i-1/⑩,0)))
⒀i-1指选中上1行的临界收益⒀的单元格；
在EXCEL模版中，理解上述共识所指向的单元格；
(7)保本销量⒂=保本销额⒁/销售单价⑧
(8)保利销额⒃区域单元格公式：
”=IF(⒀
⒀i-1指选中上1行的临界收益⒀的单元格；
在EXCEL模版中，理解上述共识所指向的单元格。
(9)保利销量⒄=保利销额⒃/销售单价⑧
步骤5.3合计栏，对保本销额⒁、保利销额⒃区域求和。
【实验课时与考核标准】
1、实验课时：4课时
2、考核标准：
(1)销售趋势预测30%
(2)盈亏平衡分析30%
(3)线性规划法20%
(4)临界收益法20%
9、年度生产计划编制的EXCEL模版操作与应用
189
年度生产计划编制的EXCEL模版操作与应用
【实验目的】
1、掌握生产均衡的图示技术与方法；
2、理解或掌握非均衡需求计划编制技术与方法。
【实验材料】
表01某年度销售需求与生产能力数据
月份123456789101112∑
销售需求(Q)1161963436044983022962321721351481803222
生产能力(M)3003003003003003003003003003003003003600
表02生产方式与成本
编号生产方式库存量或最大产量(件)单位成本(元/件)
1初始库存120100
2正常时间180100
3加班时间36107
4外包加工80113
单位产品存储费2
单位缺货成本20
【实验步骤与要求】
步骤1建立《年度生产计划》工作簿
步骤2设置《产需平衡》工作表
表1产需平衡分析表
月份123456789101112∑
销售需求(Q)1161963436044983022962321721351481883230
生产能力(M)3003003003003003003003003003003003003600
均衡计划(S1)
缺货量(S2)
库存量(S3)
【生产物流实验9】
实验9.1需求预测与产能平衡
模块7生产物流实验
190
步骤2.1选中销售需求(Q)数值区域，定义“名称”——“SalesDemand”
步骤2.2设置均衡计划(S1)区域单元格公式：”=MIN(Q,M)”
步骤2.3设置缺货量(S2)区域单元格公式：”=MAX(0,Q-M)”
步骤2.4设置库存量(S3)区域单元格公式：”=MAX(C4-C3,0)”
步骤2.5选中S1、S2、S3三行数据区域，插入堆积柱形图；分类间距设置为0；
步骤2.6在图表“源数据”中。添加“生产能力(M)”系列，将其图形类型改为“X-Y散点图”；显示
“系列名称”，添加趋势线，前移、倒退各0.5周期；
步骤2.7在图表“源数据”中。添加“销售需求(Q)”系列；设置“次坐标轴”，不显示“此坐标轴”；
分类间距设置为0；数据标志显示“值”。
步骤2.8修改图表选项、调整图表元素位置，插入标签、箭线，显示下图
图1生产-需求平衡图
步骤3设置《生产计划参数》工作表
表2生产计划参数设置
编号供应方式库存量或最大产量(件)单位成本(元/件)
1初始库存120100
2正常时间180100
3加班时间36107
4外包加工0113
单位产品存储费2
单位缺货成本20
期末储备量80
允许缺货（延迟交货）A.是/B.否
允许延迟交货期限（月）2
实验9.2非均衡需求生产计划编排
9、年度生产计划编制的EXCEL模版操作与应用
191
步骤3.1设置表3格式，输入数据
步骤3.2允许缺货（延迟交货）的数值单元格，设置数据有效性，“序列/来源=A.是,B.否”；
定义“名称”——“Delay”，引用位置”=Left(Delay,1)”（式中：Delay指选中设置的单元格）；
步骤3.3选中“编号”数值区域，定义“名称”——“ProDuctNO”
步骤3.4选中“生产方式”数值区域，定义“名称”——“ProDuctName”
步骤3.5选中“库存量或最大产量”数值区域，定义“名称”——“Yield”
步骤3.6选中“单位成本”数值区域，定义“名称”——“Cost”
步骤3.7选中“单位产品存储费”数值单元格，定义“名称”——“StockCost”
步骤3.8选中“单位缺货成本”数值单元格，定义“名称”——“DelayCost”
步骤3.9选中“期末储备量”数值单元格，定义“名称”——“EndStock”
步骤3.10选中“允许延迟交货期限”数值单元格，定义“名称”——“DelayPeriod”
步骤4设置《成本矩阵》工作表
表3成本矩阵
供应
期数
j
供应
方式
k
正常
成本
Nc
需求期数i
123456789101112
11100100102104106108110112114116118120122
12100100102104106108110112114116118120122
13107107109111113115117119121123125127129
14113113115117119121123125127129131133135
22100120100102104106108110112114116118120
23107127107109111113115117119121123125127
24113133113115117119121123125127129131133
MaxCost
步骤4.1需求期数(i)设置从1至12（根据计划期数变动）；以下用i表示需求期数单元格；
步骤4.2供应期数(j)设置从1至12（根据计划期数变动）；以下用j表示供应期数单元格；
每一期数按照供应方式重复设置（注：初始库存的供应期数安排在第1期；
步骤4.3供应方式(k)在每一期内重复设置；以下用k表示供应方式单元格；
步骤4.4正常成本Mc指需求期与供应期一致时的生产成本，单元格公式：”=INDEX(Cost,k)
选中“正常成本Nc”数值区域，定义“名称”——“NormalCost”
步骤4.5i、j、k组合的成本矩阵数值区域单元格公式：
“=IF(i
式中：Nc指选中“正常成本Nc”数值区域单元格，
步骤4.6选中i、j、k组合的成本矩阵数值区域，定义“名称”——“CostArray”
步骤4.7MaxCost数值区域单元格公式：“=Max(CostArrayI)”，
式中CostArrayI指选中各需求期数（i）列的成本数值区域，求最大值；
步骤4.8选中MaxCost数值区域，定义“名称”——“MaxCost”
模块7生产物流实验
192
步骤5设置《计划编制》工作表
表4计划编排矩阵(PlanArray)
计划期数(Period)1剩余
生产
能力
(Mk)
可用
生产
能力
(M)
成本
系列
(Cik)
成本
排序
(Pn)
供应
期数
(j)
供应
方式
(k)
需求期数(i)123456789101112
需求量（Di）
欠产量(S)
11初始库存
12正常时间
13加班时间
14外包加工
22正常时间
初始库存消耗
StockConsumption0maxX
maxI
lx
li
步骤5.1i，j，k设置同步骤4.1，步骤4.2，步骤4.3；
步骤5.2显示供应方式(k)名称(标签)的区域单元格公式：”=INDEX(ProDuctName,k)”
步骤5.3“计划期数Period”数值单元格，设置数据有效性，“序列/来源”选中i区域；
选中”Period”数值单元格，定义“名称”——“Period”
选择计划期数，则对该期数进行生产计划编排；
步骤5.4需求量(Di)单元格公式：”=INDEX(SalesDemand,i)+IF(i=12,EndStock,0)”
步骤5.5欠产量(Si)单元格公式：”=Di-SUM(PlanArrayI)”
式中：Di指选中期数为i的需求量(D)单元格；
PlanArrayI指选中计划矩阵的期数为i的列区域；
步骤5.6计划编排矩阵(PlanArray)区域，是计划编排输入区；
选中区域，定义“名称”——“PlanArray”
步骤5.7初始库存消耗StockConsumption数值单元格公式：
“=SUM(PlanArrayIF(k=1,1,0))”，式中：k指选中供应方式(k)区域
选中单元格，定义“名称”——“StockConsumption”
步骤5.8成本系列(Cik)显示成本矩阵中所选期数i列的区域,单元格公式：
“=IF(INDEX(CostArray,ROW(1:1),i)=0,INDEX(maxCost,i)+1,
INDEX(CostArray,ROW(1:1),i))”
向下填充；
步骤5.9成本排序参数：
maxX数值单元格公式：”=MAX(成本系列(Cik)区域)
maxI数值单元格公式：”=COUNTA(成本系列(Cik)区域)
9、年度生产计划编制的EXCEL模版操作与应用
193
lx数值单元格公式：”=POWER(10,LEN(maxX)+1)-1”
li数值单元格公式：”=POWER(10,LEN(maxI)+1)-1”
步骤5.10可用生产能力(M)区域单元格公式：”=INDEX(Yield,k)”
步骤5.11剩余生产能力(Mk)区域单元格公式：
”=IF(k=1,INDEX(Yield,1)-StockConsumption,IF(AND(j>i,
OR(Delay<>"A",j>Period+DelayPeriod)),0,Mjk-SUM(PlanArrayJK)))
式中：Mjk指选中本行可用生产能力(M)单元格；
PlanArrayJK指选中本行计划编排矩阵(PlanArray)区域；
步骤5.12成本排序(Pn)区域单元格公式：
”=INT(CONCATENATE(IF(Mk=0,"98","99"),Lx-Cik,Li-ROW(1:1)))”
式中：Mk指选中本行剩余生产能力(Mk)单元格；
Cik指选中本行成本系列(Cik)单元格；
Lx,Li指选中Lx,Li数值单元格；
向下填充；
步骤5.13成本排序(Pn)列maxX单元格公式：”=MAX(Pn区域)”
选中单元格，定义“名称”——“MaxPn”
步骤5.14设置剩余生产能力(Mk)、成本排序(Pn)区域单元格”条件格式”：
当Pn=MaxPn时，字体格式为红色，加粗；
设置计划编排矩阵(PlanArray)区域单元格格式”条件格式”：
当And(i=Period,Pn=MaxPn)时，图案颜色为浅蓝色；
步骤5.15依次选择计划编排期数；
当计划期数选择变化时，可以根据颜色提示，找到优先编排的单元格：
同时，根据剩余生产能力(Mk)、欠产量(Si)，输入适当的计划数；
输入计划数后，相应的区域数据发生变化，直至所有期数计划编排结束。
步骤6汇总生产计划
表5生产计划表
期数(i)123456789101112合计
需求量(Di)1161963436044983022962321721351481803222
供
应
方
式
k
1期初库存20432830521-157-139-115-27420
2正常时间3003003003003003003003001991351482603142
3加班时间2020202020202020160
4外包加工
生产计划合计(Production)320320320320320320320320199135148260
期末库存(EndStockNum)20432830521-157-139-115-2780
延迟交货(DelayNum)15713911527
欠产量(LessProduct)
模块7生产物流实验
194
步骤6.1第1期的期初库存等于初始库存，其它期数的期初库存等于上期的期末库存
步骤6.2供应方式区域(K<>1)数值单元格公式：
“{=SUM(PlanArrayIF(J=i,1,0)IF(JK=k,1,0))}”
式中：J、Jk指选中表4的供应期数(J)、供应方式(k)列区域
i指选中表5期数i数值单元格，k指选中表5供应方式k数值单元格；
步骤6.3生产计划合计是供应方式（k=2,3,4）计划产量的合计。
步骤6.4期末库存=期初库存+生产计划合计－需求量(Di)
步骤6.5当期末库存为负数时，表示需要延迟交货的数量；欠产量表示当前生产能力或供应方式无
法满足的需求数量；两者可以采用设置初始库存解决，或通过加班、外包加工解决，或
减少订货量。
步骤7分析生产计划成本
表6生产计划成本
期数(i)123456789101112合计
需求量(Di)1161963436044983022962321721351481803222
正
常
成
本
1初始库存
2正常时间116001960034300545004570028200276002120017200135001480026000314200
3加班时间6313438721402140214017120
4外包加工
延迟交货费用3140278023005408760
提前生产存储成本8614641661716
合计116001960034386622775339333120320402388017200135001480026000341796
步骤7.1正常成本区域单元格公式：”=SUM(PlanArrayIIF(Jk=k,1,0)NormalCost)”
步骤7.2延迟交货费用区域单元格公式：
“=SUM(PlanArrayIIF(J>i,1,0)(OFFSET(CostArray,0,i-1,,1)-NormalCost))”
步骤7.3提前生产存储成本区域单元格公式：
“=SUM(PlanArrayIIF(j
【实验课时与考核标准】
1、实验课时：4课时
2、考核标准
(1)需求预测与产能平衡30%
(2)非均衡需求生产计划编排70%
10、生产进度计划编制EXCEL模版
195
生产进度计划编制的EXCEL模版操作与应用
【实验目的】
1、掌握厂历表编制方法
2、了解生产进度计划编制流程
3、掌握生产进度计划编制模版运用
【实验材料】
表1工序组成
编码工序名称保险期编码工序名称保险期
010成型2060开版/验版2
020针车2070配料1
030高频/电绣1071面料1
040冲裁/定位0072大底1
050试作1073冲刀1
表2工序定额与配备
工序号工序定额产量定额单位(小时)工作日额定时数工时效率配备人数配备机(组)数
010成型300(双/线)890%1353
020针车120(双/组)885%2358
030高频/电绣150(双/人)885%246
040冲裁/定位240(双/台)885%2412
070配料120(双/台)885%2412
050试作3(天/批)
表3订单数量及交货期
货单号产品名称订单数量交货批次交货时间交货量
B01代尔塔安全鞋3600012008-10-1536000
B02羽毛球鞋4800012008-10-1224000
22008-10-2224000
B03网球鞋12000012008-10-1830000
22008-11-1830000
32008-11-2830000
42008-12-830000
【实验步骤与要求】
步骤1建立《生产进度计划》工作簿
步骤2设置《厂历编制》工作表
步骤2.1厂历编制参数确定
【生产物流实验10】
模块7生产物流实验
196
表4厂历编制参数
起始日期2008年1月1日
终止日期2008年12月31日
星期周休日设置年休日系列
日周休日元旦
一春节
二清明节
三劳动节
四端午节
五中秋节
六周休日国庆节
厂节厂假
(1)起止日期：
按照日期格式输入“起始日期”；插入/名称/定义——“StartDay”，
引用位置：”=DATE(Y1,M1,D1)”；
按照日期格式输入“终止日期”；插入/名称/定义——“FilishDaytDay”，
引用位置：”=DATE(Y2,M2,D2)”；
式中Y1、M1、D1，Y2、M2、D2指分别选中起始日期、终止日期的年、月、日单元格；
(2)计划期长度(日数)，插入/名称/定义——“daysLongth”
引用位置：”=DATEDIF(StartDay,FilishDay,"d")+1”；
(3)选中星期“日”～“六”区域，插入/名称/定义——“WeekDays”，
在周休日设置区域，设置数据有效性，“序列/来源=周休日”；
利用数据有效性的下拉框，确定周休日；
选中周休日设置区域，插入/名称/定义——“WeekRestDays”；
(4)选中年休日系列区域，插入/名称/定义——“YearRestDays”。
步骤2.2厂历编制
表5《厂历表》编制
日期
MDate
星期
WeekD
周休日
WRest
公休日
YRest
调休日
Hrest
工作日
WorkDay
厂历
WorkDays
计算数
Pn
工作日历
WorkDate
2008-1-1二元旦009992008-1-1
2008-1-2三119982008-1-2
2008-1-3四129972008-1-3
2008-1-4五139962008-1-4
2008-1-5六t调休日149952008-1-5
2008-1-6日t049952008-1-5
2008-1-7一159942008-1-7
(1)设置第1行单元格公式；以下公式中，用表格栏目的符号表示选中相应的单元格；
(2)日期MDate单元格公式：”=StartDay+ROW(1:1)-1”
10、生产进度计划编制EXCEL模版
197
插入/名称/定义——“DaysSet”,
引用位置：”=OFFSET(MDate,0,0,daysLongth,1)”
(3)星期WeekD单元格公式：”=INDEX(WeekDays,WEEKDAY(MDate))”
(4)周休日WRest单元格公式：
”=IF(INDEX(WeekRestDays,MATCH(WeekD,WeekDays,0),1)=’’,’’,’t’)”
(5)公休日YRest区域单元格设置数据有效性，“序列/来源=YearRestDays”
(6)调休日Hrest区域单元格设置数据有效性，“序列/来源=调休日”
(7)工作日WorkDay单元格公式：
”=IF(OR(AND(WRest=’’,JWRest=’’),Hrest<>’’),1,0)”
(8)厂历WorkDays单元格公式：”=SUM($WorkDay:WorkDay)”
式中$WorkDay表示对WorkDay单元格的绝对引用；后一个WorkDay表示对WorkDay
单元格的相对引用；
(9)计算数Pn单元格公式：“=999-WorkDays”
插入/名称/定义——“WorkDaysOrder”；
引用位置：”=OFFSET(Pn,0,0,daysLongth,1)”
(10)工作日历WorkDate单元格公式：
”=INDEX(DaysSet,MATCH(LARGE(WorkDaysOrder,ROW(1:1)),
WorkDaysOrder,0))”
(11)从起始日期开始，向下填充至终止日期。
步骤3设置《工序管理》工作表
步骤3.1设置工序编码
表6工序编码
编码(Code)工序名称(Name)工序系列(Series)提前期
010成型010-成型
020针车020-针车2
030高频/电绣030-高频/电绣2
040冲裁/定位040-冲裁/定位0
050试作050-试作2
060开版/验版060-开版/验版2
070配料070-配料1
071面料071-面料2
072大底072-大底2
073冲刀073-冲刀3
(1)输入编码(Code)、工序名称(Name)
(2)工序系列(Series)区域单元格公式：”=Code&’-‘&Name”
插入/名称/定义——“ProcessesSeries”,
引用位置：”=OFFSET(Series,0,0,COUNTA($Series:$Series)-1,1)”
式中，Code、Name、Series指选中相应单元格，$Series:$Series指选中Series整列；
模块7生产物流实验
198
步骤3.2核定工序生产能力
表7产能核定
工序
(Processes)
工序号
(ProID)
定额
产量
定额单位
(小时)
工作日
额定时数
工时
效率
配备
人数
配备
机(组)数
生产能力
核定单位日产能(ProDuctNum)
①②③④⑤⑥⑦
010-成型010300(双/线)890%1353机6480
020-针车020120(双/组)885%2358机6528
030-高频/电绣030150(双/人)885%246机6120
040-冲裁/定位040240(双/台)885%2412机19584
070-配料070120(双/台)885%2412人19584
050-试作0503(天/批)批0
(1)工序(Processes)区域单元格设置数据有效性，“序列/来源=ProcessesSeries”
选择需要确定产能的工序项目
(2)工序号(ProID)区域单元格公式：”=LEFT(Processes,3)”
(3)生产能力核定单位⑦区域单元格设置数据有效性，“序列/来源=人,机,批”
(4)日产能(ProDuctNum)区域单元格公式：
”=IF(⑦=’批’,0,IF(⑦="机",⑥,⑤)①③④”
式中：Processes、数字序号表示选中相应单元格。
步骤4设置《生产订单管理》工作表
步骤4.1订单目录
表8订单汇总表
订单号(ID)产品名称订单数量交货批数
B01代尔塔安全鞋360001
B02羽毛球鞋480002
B03网球鞋1200004
步骤4.2确认交货期与交货批量
表9订单交货期与交货批量
订单号交货批次交货期交货批量生产批次交货期厂历计算数(Pn)生产排序
①②③④⑤⑥⑦⑧
B0112008-10-1536000B01-1197802998B02-1
B0212008-10-1224000B02-1194805997B01-1
B0222008-10-2224000B02-2202797996B03-1
B0312008-10-1830000B03-1199800995B02-2
B0322008-11-1830000B03-2221778994B03-2
B0332008-11-2830000B03-3229770993B03-3
B0342008-12-830000B03-4235764992B03-4
(1)选中表8中订单号(ID)序列区域，插入/名称/定义——“OrderID”
10、生产进度计划编制EXCEL模版
199
(2)表9中，订单号①区域单元格设置数据有效性，“序列/来源=OrderID”
依次选择需要编排生产进度的订单号，输入交货批次、交货期和交货批量；
(3)生产批次⑤区域单元格公式：”=①&’-‘&②”
选中生产批次⑤列区域，插入/名称/定义——“ProDuct_ID”
(4)交货期厂历⑥区域单元格公式：”=INDEX(WorkDays,MATCH(③,WorkDate,1))”，
将出产日期转换位厂历
(5)计算数⑦区域单元格公式：”=INT(CONCATENATE(999-⑥,999-ROW(1:1)))”
(6)生产排序⑧区域单元格公式：
”=INDEX(ProDuctID,MATCH(LARGE(Pn:Pn),Pn:Pn,0))”
式中：数字序号表示选中相应的单元格，Pn:Pn表示选中计算数(Pn)区域。
(7)选中生产排序⑧区域，插入/名称/定义——“Product_Order”
步骤5设置《生产周期》工作表
表10批量生产周期
生产批次生产批量工序核定生产能力核定生产周期调整生产周期生产批次标识
①②③④⑤⑥⑦
B01-136000010-成型64805.65.5B01-1-010-成型
B01-136000020-针车65285.55.5B01-1-020-针车
B01-136000030-高频/电绣61205.96B01-1-030-高频/电绣
B01-136000040-冲裁/定位195841.82B01-1-040-冲裁/定位
B01-136000050-试作00.03B01-1-050-试作
B02-124000010-成型64803.74B02-1-010-成型
B02-124000020-针车65283.74B02-1-020-针车
B02-124000030-高频/电绣61203.94B02-1-030-高频/电绣
B02-124000040-冲裁/定位195841.21B02-1-040-冲裁/定位
B02-124000050-试作00.03B02-1-050-试作
B02-224000010-成型64803.74B02-2-010-成型
B02-224000020-针车65283.74B02-2-020-针车
B02-224000030-高频/电绣61203.94B02-2-030-高频/电绣
B02-224000040-冲裁/定位195841.21B02-2-040-冲裁/定位
B03-130000010-成型64804.64.5B03-1-010-成型
B03-130000020-针车65284.64.5B03-1-020-针车
B03-130000030-高频/电绣61204.95B03-1-030-高频/电绣
B03-130000040-冲裁/定位195841.51.5B03-1-040-冲裁/定位
B03-130000050-试作00.03B03-1-050-试作
模块7生产物流实验
200
（续前表）
B03-230000010-成型64804.64.5B03-2-010-成型
B03-230000020-针车65284.64.5B03-2-020-针车
B03-230000030-高频/电绣61204.95B03-2-030-高频/电绣
B03-230000040-冲裁/定位195841.51.5B03-2-040-冲裁/定位
B03-330000010-成型64804.64.5B03-3-010-成型
B03-330000020-针车65284.64.5B03-3-020-针车
B03-330000030-高频/电绣61204.95B03-3-030-高频/电绣
B03-330000040-冲裁/定位195841.51.5B03-3-040-冲裁/定位
B03-430000010-成型64804.64.5B03-4-010-成型
B03-430000020-针车65284.64.5B03-4-020-针车
B03-430000030-高频/电绣61204.95B03-4-030-高频/电绣
B03-430000040-冲裁/定位195841.51.5B03-4-040-冲裁/定位
(1)生产批次①区域单元格设置数据有效性，“序列/来源=ProDuct_ID”
(2)工序③区域单元格设置数据有效性，“序列/来源=ProcessesSeries”
(3)依次选择生产批次①和工序③
(4)生产批量②区域单元格公式：
”=INDEX(表9!④,MATCH(①,ProDuct_ID,0))”
式中：数字序号①表示选中生产批次①区域单元格；
表9!④指选中表9交货批量④区域（以下，类同）；
(5)核定生产能力④区域单元格公式：
”=INDEX(表7!ProDuctNum,MATCH(LEFT(③,3),表7!ProID,0))”
(6)核定生产周期⑤区域单元格公式：”=IF(④=0,0,②/④)”
(7)调整生产周期⑥区域单元格,依据核定生产周期⑤进行微调，使得所确定的生产周期能更
加符合实际情况，使得模版具有更大的灵活性和适应性。
(8)生产批次标识⑦区域单元格公式：”=①&’-‘&③”
步骤6设置《生产进度》工作表
步骤6.1按照反工艺顺序，编排生产进度（厂历），见表11
步骤6.2将厂历进度转换成生产日历进度，见表12
步骤6.3根据生产日历进度，插入生产进度图（甘特图），见图1
步骤6.4编制完整的生产排成表，下达生产指令。
【实验课时与考核标准】
1、实验课时：4课时
2、考核标准
(1)厂历编制30%
(2)生产进度图标70%
10、生产进度计划编制EXCEL模版
201
表11生产进度（厂历）编排
工序(i)
生产进度050-试作040-冲裁/定位030-高频/电绣020-针车010-成型交货期
生产
批次(k)
生产
批量t1t2t3St1t2t3St1t2t3St1t2t3St1t2t3SEdate
B02-124000163.53166.51167.51168.50168.54174.51175.54181218341922194
B01-136000166.53169.51170.52172.50172.56178.51179.55.518521875.51952197
B03-130000174.53177.51178.51.51800180518511864.5190.52192.54.51972199
B02-2240001850185018511860186419011914195219742011202
B03-230000201.50201.50201.51.52030203520812094.5213.52215.54.52201221
B03-330000209.50209.50209.51.52110211521612174.5221.52223.54.52281229
B03-430000215.50215.50215.51.52170217522212234.5227.52229.54.52341235
(1)工序(i)区域单元格设置数据有效性，“序列/来源=ProcessesSeries”；i=1，2，…，n
生产批次(k)区域单元格设置数据有效性，“序列/来源=Product_Order”；k=1，2，…，m
(2)按照工艺顺序，依次选择排列工艺(i)；按照表9的生产排序⑧，依次选择排列生产批量；
(3)Edate——交货期，以生产批次(k)为标志，从表9中引用交货期厂历⑥，引用交货批量④；
(4)t1(i,k)——k批次在i工序的投入期(开始生产)；t2(i,k)——k批次在i工序的生产周期；t3(i,k)——k
批次在i工序的出产期（完工期）；
(5)S(i,k)——保险期，表示各工序之间、或成品出产期与交货期之间设置的保险天数；从表1中引用或
调整输入，使得更加适应实际情况；
(6)t2(i,k)，用”生产批次(k)&’-‘&工序(i)”组合为标志，从表10中引用各批次、各工序的生产周期⑥;
(7)t3(i,k)，最后一道工序(i=n)，t3(n,k)=Edate(k)-S(n,k)；其余工序(i
(8)t1(i,k)，最后一个批次(k=m)，t1(i,m)=t3(i,m)-t2(i,m)；其余批次(k
(9)依照工艺顺序，设置单元格公式，计算生产进度厂历。利用厂历安排进度，已经排除休假日。
表12生产进度（生产日历）编排
工序(i)
生产进度050-试作040-冲裁/定位030-高频/电绣020-针车010-成型交货期
生产
批次
(k)
生产
批量t1t2St3Vt1t2St3Vt1t2St3Vt1t2St3Vt1t2St3VEdate
B02-1240008/22328/2718/28108/2908/29469/819/9459/1849/2241210/8210/10
B01-1360008/27329/119/2209/409/4629/1249/165.52.59/2429/265.511.510/13210/15
B03-1300009/8309/1119/121.53.59/1709/17529/2419/254.56.510/6210/84.52.510/15210/17
B02-2240009/24009/2409/24109/2509/254710/6110/74210/13210/154210/21110/22
B03-23000010/210010/21010/211.50.510/23010/235210/30110/314.51.511/6411/104.52.511/17111/18
B03-33000010/310010/31010/311.52.511/4011/45211/11111/124.51.511/18211/204.52.511/27111/28
B03-43000011/100011/10011/101.50.511/12011/125211/19111/204.51.511/26211/284.52.512/5112/8
模块7生产物流实验
202
(1)t1(i,k)、t2(i,k)、t3(i,k)、Edate(k)，分别表示k批次在i工序的投入日期、生产周期、出
产日期及交货日期
(2)直接从表11引用工序(i)、生产批次(k)、生产批量、t2(i,k)
(3)将表11中种t1(i,k)、t3(i,k)、Edate(k)的厂历转换成生产日历
——表12单元格公式：”=INDEX(WorkDate,MATCH(TIK,WorkDays,1))”，式中：TIK
表示选中表11相应单元格
(4)S(i,k)=t3(i,k)-t1(i,k)-t2(I,k)
(5)当i=n，V(n,k)=Edate(k)-t3(n,k)；当i
(6)选中表12的t1、t2、S、V系列，插入堆积条形图，即为生产进度图（甘特图）。
图1生产进度计划（甘特图）
8/228/279/19/69/119/169/219/2610/110/610/1110/1610/2110/2610/3111/511/1011/1511/2011/2511/3012/5
B02-1
B01-1
B03-1
B02-2
B03-2
B03-3
B03-4
批
次
日期
050-试作040-冲裁/定位030-高频/电绣020-针车010-成型
|
|
