EXCEL在生产管理中的应用

EXCEL在生产管理中的应用总结：序号PMC工作需求1安全库存计算2设置安全库存提醒3按客户、制程等汇总4去除重复值（料号...）5依料号、制令号..查找对应值6求和或按条件(料号、客户...)求和7依条件求和8多条件求和9计数统计、条件计数(排程天数…)10求开工日、完工日11提取料号等部分位数12合并不同单元格数据13自动显示料号、序列号长度14判断条件返回不同值15查找判断包含特定字符的内容16ABC分类17计算物料用量第几大或第几小18单阶BOM物料需求19生产计划排程20最小包装量求订购量21存货库龄分析22多阶BOM物料需求计算23计算物料配套率24到期排程自动高亮显示25自动查询期末库存26计划相容性-求交叉值27数据有效性设置共用料28物料清单引用计算29日期转换星期几、周别/季度判断、周末、月工作天数。。。30修正库存数量正、负数31工时计算-连乘间隔相加32文本求和、按颜色汇总33用公式进行自动物料编码、订单编码34计算库存天数35多表汇总数据36按人员统计产量-分段求和37依优先顺序及需求量分配【共用料】38累计求和与反累计求和39整包+零头计算总数量40计划总负荷率、计划总完成率EXCEL在生产管理中的应用例题min、max、average1条件格式2分类汇总3高级筛选、公式4Vlookup、index+match5sum一般或数组sumifsumproductcountif、count、counta-含文本MIN+IF、MAX+IFleft、mid、mid+find、right、分列&、CONCATENATElenif、chooseif+find、if+countifRANK、HLOOKUP+PERCENTRANK16small、larger17去重复值、vlookup、sumif、MMULT、数据透视表18vlookup、if、sum…,VBA19if+int+mod20VLOOKUP模糊查找+TODAY21INDEX+MATCH模糊匹配、IF+OFFSET+MAX+ROW22去重复值、min+index+match(精确+模糊匹配)23条件格式24LOOKUP25indirect+address+SUBSTITUTE+index+match26.1、26.2数据有效性27数据有效性+数组公式28.1、28.2时间函数29设置单元格数据格式30sumproduct+mod+column31宏表函数应用（EVALUATE、GET.CELL）32IF+TEXT+SUBSTITUTE+SUMPRODUCT+LEN33.1、33.2IF+DATEDIF+TODAY()34SUM+N+INDIRECT35IF+SUM36MIN+SUM37SUM、N38TEXT+FIND+SUBSTITUTE+LEFT+RIGHT39SUM+IF+mod406、7、89、1011、12、1314、151.安全庫存表--min、average、maxA:平均日用量A*L/TL/TMINAVERAGEMAX物料編碼品名單位安全庫存采購前置期(DAY)最小用月量月平均用量最大月用量1月2月3月4月5月6月7月A1A05001建滔0.5T板材SH951590190400100200150150300400200A1A10001建滔1.0T板材SH24415200488800500400600500300800200A1A15001建滔1.5T板材SH492154009832000100015005002000400400800A1L05001长春0.5T板材SH8730508715010050100506550100A1L10001长春1.0T板材SH20930100209400200300200250400150100A1L15001长春1.5T板材SH1093306001093171010001200800100014008001000PS：1.依安全库存并结合最大、最小月用量订料2.淡旺季会影响库存的高低水平返回总表8月9月10月11月12月150180200160905004506005005001200150050010001000150907012010020020015016020017106001100100015002.设置安全库存提醒--条件格式例题一：条件格式设置物料編碼品名單位库存安全库存A1A05001建滔0.5T板材SH10095A1A10001建滔1.0T板材SH200244A1A15001建滔1.5T板材SH1000492A1L05001长春0.5T板材SH75087A1L10001长春1.0T板材SH1000209A1L15001长春1.5T板材SH8001093例题二：现有库存12月28日1月4日1月11日1月18日需求1500010050002000供应3000100020004000结余160004000490019003900DSI天数1112712例题1.在库存数量列设置条件格式--小于安全库存的会自动显示不同颜色（料号多的时候可迅速确认出预短缺物料需及时安排进料）例题2.DSI天数设置：正常库存天数10-20天-绿色；低库存10天-红色；高库存20天-蓝色返回总表1月25日2月1日2月8日2月15日2月22日3500200010003000200050002000540054004400140020例题1.在库存数量列设置条件格式--小于安全库存的会自动显示不同颜色（料号多的时候可迅速确认出预短缺物料需及时安排进料）3.按客户、制程等汇总--分类汇总客户料号/名称Unisource汇总仁宝电子FJ07C000100仁宝电子FJ080000100仁宝电子FJ080000200仁宝电子FJ0AF000510仁宝电子FJ0AF000610仁宝电子FJ0AF000700仁宝电子FJ0AW000300仁宝电子FJ0AW000400仁宝电子FJ0AY000110仁宝电子FJ0AY000210仁宝电子FJ0AY000300仁宝电子FJ0B0000110仁宝电子FJ0B0000210仁宝电子汇总大众汇总伟创力汇总仁宝电脑汇总纬新汇总总计PS:1.数据-分类汇总-选分类项、汇总项目-确认2.若想只提取汇总项目：选中A2:C103-按F5定位条件选可见单元格-复制/黏贴如下表：客户料号/名称Unisource汇总仁宝电子汇总大众汇总伟创力汇总仁宝电脑汇总纬新汇总总计返回总表订单数量5054020714071401362913629516602486424864513245132414987075675639697683842290017171161087412613852.若想只提取汇总项目：选中A2:C103-按F5定位条件选可见单元格-复制/黏贴如下表：订单数量505403969768384229001717116108741261385#REF!4.去除重复值（料号...）--高级筛选、公式客户代码客户名称客户PO号产品名称订单数量交货日期001方正20101208001ABC20002011/2/5002华硕20101208001ACB10002011/2/6001方正2010121002ABC50002011/2/5001方正2010120005ABC30002011/2/6002华硕201054155AAB50002011/2/7输产品名称输产品名称输产品名称输产品名称输产品名称输产品名称ps：1.高级筛选：数据-筛选-高级筛选-选择不重复记录2.数组公式：=INDEX($D$4:$D$14,MATCH(,COUNTIF($H$3:H3,$D$4:$D$14),))，可建立动态引用（在输入产品处试试）返回总表高级筛选--静态、一次性公式--动态取不重复产品名称产品名称不重复值2订单总数ABCABCACBACBAABAAB输产品名称输产品名称#N/A#N/A#N/A#N/A#N/A#N/A#N/A2.数组公式：=INDEX($D$4:$D$14,MATCH(,COUNTIF($H$3:H3,$D$4:$D$14),))，可建立动态引用（在输入产品处试试）5.依料号、制令号..查找对应值--Vlookup、index+match例一：基础数据料号描述需求量库存差异AB001上盖100010000AB002下盖1000200(800)AB003前挡板8001200400AB004后挡板500200(300)…查询对应值VLOOKUPINDEX+MATCH料号对应描述对应需求量对应库存对应差异AB002下盖1000200-800AB004AB001…例一：输入公式后下拉可自动显示对应值公式：1.=VLOOKUP(A12,$A$4:$E$8,2,FALSE)2.=INDEX($E$5:$E$8,MATCH(A12,$A$5:$A$8,),)例二：生产管理中用途较广:1.依FORECAST或PO中的料号查找引用BOM里对应的资料--用于一般单物料制造业2.依BOM基础资料引用查找FORECAST或PO中成品需求，再算原料需求---用于组装例二：1.一般单物料制造BOM基础数据产品需求数据产品原料料号取片数产品A02A1A001M110A02A1A002A02A1A002M220A02A1A003A02A1A003M330A02A1A005A02A1A004M440A02A1A005M550A02A1A006M660例二：2.组装BOM基础数据辅助列产品原料原料描述用量配比成品需求-VLOOKUPAB100BS01BaseShampoo4.8001000AB100BT100Bottle100ml48.0001000AB100CAPFFlipcap48.0001000AB100CARSCartonsmall1.0001000AB100ESAAppleessence0.0481000AB100LAB10LabelAppleBlossom100ml50.0001000AB250BS01BaseShampoo12.0002000AB250BT250Bottle250ml48.0002000AB250CAPFFlipcap48.0002000AB250CARLCartonlarge1.0002000AB250ESAAppleessence0.1202000AB250LAB25LabelAppleBlossom250ml50.0002000AB500BS01BaseShampoo12.0003000AB500BT500Bottle500ml24.0003000AB500CAPSScrewcap24.0003000AB500CARLCartonlarge1.0003000AB500ESAAppleessence0.1203000AB500LAB50LabelAppleBlossom500ml26.0003000FL100BS01BaseShampoo4.8004000FL100BT100Bottle100ml48.0004000FL100CAPFFlipcap48.0004000FL100CARSCartonsmall1.0004000FL100ESLLemonessence0.0484000FL100LFL10LabelFragrantLemon100ml50.0004000FL250BS01BaseShampoo12.0005000FL250BT250Bottle250ml48.0005000FL250CAPFFlipcap48.0005000FL250CARLCartonlarge1.0005000FL250ESLLemonessence0.1205000FL250LFL25LabelFragrantLemon250ml50.0005000FL500BS01BaseShampoo12.0006000FL500BT500Bottle500ml24.0006000FL500CAPSScrewcap24