excel在生产管理中的应用(续)

1、EXCELEXCEL在在生生产产管管理理中中的的应应用用（续续）工工作作需需求求EXCELEXCEL在在生生产产管管理理中中的的应应用用1.到期排程自动高亮显示条件格式2.自动查询期末库存LOOKUP3.计划相容性-求交叉值indirect+address+SUBSTITUTE+index+match4.数据有效性设置共用料数据有效性5.物料清单引用计算数据有效性+数组公式6.日期转换星期几、周别/季度判断、周末、月工作天数。时间函数7.修正库存数量正、负数设置单元格数据格式8.工时计算-连乘间隔相加sumproduct+mod+column9.文本求和、按颜色汇总宏表函数应用（EVALUAT

2、E、GET.CELL）10.用公式进行自动物料编码、订单编码IF+TEXT+SUBSTITUTE+SUMPRODUCT+LEN11.计算库存天数IF+DATEDIF+TODAY()12.多表汇总数据SUM+N+INDIRECT13.按人员统计产量-分段求和IF+SUM14.依优先顺序及需求量分配【共用料】MIN+SUM15.累计求和与反累计求和SUM、N16.整包+零头计算总数量TEXT+FIND+SUBSTITUTE+LEFT+RIGHT17.计划总负荷率、计划总完成率SUM+IF+mod实实例例12lookup 多条件183.1、3.245.1、5.2678910.1、10.2111213

3、14151617By Duwei9981E2011/5/11到到期期排排程程高高亮亮提提醒醒当当前前日日期期2 20 01 12 2/ /2 2/ /9 9订单号料号订单数量 产能（天）5月11日 5月12日 5月13日 5月14日 5月15日 5月16日MO221475 91.72310.00450002000020002000100000MO221476 91.72310.005100012000006004000MO221477 91.72310.006600150000006000MO221478 91.72310.007200010000000 266.6671000MO221479

4、 91.72310.00830002000000000MO221480 91.72310.0093002000000001.选中E2:E8条件格式：公式=E$2=$B$12.用格式刷刷一下后面的格式3.3.可可修修改改B2B2日日期期看看排排程程颜颜色色变变化化5月17日 5月18日 5月19日 5月20日5月21日5月22日5月23日5月24日5月25日5月26日000002000200010000000000006004000000000006000733.3330000000 266.66671000533.3332000 466.667000000000 153.333 146.667

5、0000005月27日5月28日5月29日5月30日5月31日6月1日6月2日000020001000000000600400000000600733.333300000 266.6667533.33332000 466.6667000000 153.3333 146.6667000片片材材进进出出存存报报表表日日期期数数量量体体积积重重量量进料出料库存进料出料库存进料出料周别上上期期结结存存76174761743853.473853.47144月1日2016284575345146.70154.973845.214429.064030.26144月2日5162392976578157.231

6、71.373831.074427.604617.06154月3日3367562874317187.28240.753777.605800.556216.16154月4日2868509172094196.50213.393760.706079.226047.55154月5日00720940.000.003760.700.000.00154月6日4297459471797204.84172.243793.306001.564664.02154月7日5089481772069144.09213.213724.184290.575131.75154月8日4230412472175158.00190.5

7、53691.634679.285171.79154月9日4768462672317204.71154.663741.685952.944415.84164月10日3866280273381203.17156.493788.365892.764757.25164月11日4552315974774254.86138.333904.898192.324287.05164月12日2967271475027131.35198.183838.053983.286044.42164月13日164月14日164月15日164月16日174月17日174月18日174月19日174月20日174月21日174月

8、22日174月23日184月24日184月25日184月26日184月27日184月28日184月29日184月30日汇总4318244329750271989200438385972955383公公式式1 1 =LOOKUP(99,E5:E34)=LOOKUP(99,E5:E34)公公式式2 2 =LOOKUP(1,0/E5:E34,E5:E34)=LOOKUP(1,0/E5:E34,E5:E34)库存115397.34115397.34115796.14115606.68115191.06115222.73115222.73116560.27115719.09115226.57116763

9、.67117899.18121804.45119743.30119743对对应应表表（基基础础资资料料）17317151617418441874201397722424194017798-11181731234567891011171534353637383940414243441617676869707172737475767741841001011021031041051061071081091104187133134135136137138139140141142143420116616716816917017117217317417517639771992002012022032042

10、0520620720820922422322332342352362372382392402412424194265266267268269270271272273274275017729829930030130230330430530630730898-1118331332333334335336337338339340341105936436536636736836937037137237337402213973983994004014024034044054064075411430431432433434435436437438439440416646346446546646746846

11、947047147247384496497498499500501502503504505506845529530531532533534535536537538539142562563564565566567568569570571572543595596597598599600601602603604605229-60628629630631632633634635636637638229-70661662663664665666667668669670671569469569669769869970070170270370421572772872973073173273373473573

12、673758176076176276376476576676776876977020307937947957967977987998008018028035555826827828829830831832833834835836475859860861862863864865866867868869490892893894895896897898899900901902576925926927928929930931932933934935588958959960961962963964965966967968119099919929939949959969979989991000100128

13、03102410251026102710281029103010311032103310341190810571058105910601061106210631064106510661067105902215411416684845142543229-60229-705215581203012131415161718192021222324254546474849505152535455565758787980818283848586878889909111111211311411511611711811912012112212312414414514614714814915015115215

14、315415515615717717817918018118218318418518618718818919021021121221321421521621721821922022122222324324424524624724824925025125225325425525627627727827928028128228328428528628728828930931031131231331431531631731831932032132234234334434534634734834935035135235335435537537637737837938038138238338438538

15、638738840840941041141241341441541641741841942042144144244344444544644744844945045145245345447447547647747847948048148248348448548648750750850951051151251351451551651751851952054054154254354454554654754854955055155255357357457557657757857958058158258358458558660660760860961061161261361461561661761861

16、963964064164264364464564664764864965065165267267367467567667767867968068168268368468570570670770870971071171271371471571671771873873974074174274374474574674774874975075177177277377477577677777877978078178278378480480580680780880981081181281381481581681783783883984084184284384484584684784884985087087

17、187287387487587687787887988088188288390390490590690790890991091191291391491591693693793893994094194294394494594694794894996997097197297397497597697797897998098198210021003100410051006100710081009101010111012101310141015103510361037103810391040104110421043104410451046104710481068106910701071107210731

18、074107510761077107810791080108155554754905765881190928031190826272829303132335960616263646566929394959697989912512612712812913013113215815916016116216316416519119219319419519619719822422522622722822923023125725825926026126226326429029129229329429529629732332432532632732832933035635735835936036136236

19、338939039139239339439539642242342442542642742842945545645745845946046146248848949049149249349449552152252352452552652752855455555655755855956056158758858959059159259359462062162262362462562662765365465565665765865966068668768868969069169269371972072172272372472572675275375475575675775875978578678778

20、8789790791792818819820821822823824825851852853854855856857858884885886887888889890891917918919920921922923924950951952953954955956957983984985986987988989990101610171018101910201021102210231049105010511052105310541055105610821083108410851086108710881089求求交交叉叉值值 =INDIRECT(3.1!&SUBSTITUTE(ADDRESS(1,MA

21、TCH(A3,3.1!$B$1:$AH$1,)+1,4),1,)&MATCH(A4,3.1!$A$2:$A$34,)+1)105911B0492求求两两相相连连数数据据在在表表3.13.1的的交交叉叉值值17311B049312418711B0494133419411B049526917311B0496917311B0497198-111811B049833117311B049911418711B050013354311B0501599203011B0502811203011B0503817203011B0504817555511B0505850229-6011B050665354311B05

22、07614418711B0509151105911B051036817311B051112418711B0512133171511B05133817311B05142418711B0515133203011B0516797555511B0517850511B0518719418711B051915498-111811B0520335418711B052114317311B05225418711B0523133555511B0524830203011B0525818203011B0526817555511B052785014211B052858758111B052977754311B053061

23、88411B0531514105911B053237917311B053312418711B0534133555511B0535830418711B053615811 =INDIRECT(3.1!&SUBSTITUTE(ADDRESS(1,MATCH(A3,3.1!$B$1:$AH$1,)+1,4),1,)&MATCH(A4,3.1!$A$2:$A$34,)+1)数数据据有有效效性性设设置置共共用用料料料号库存数量rw-1134rw-2127rw-3133rw-4126rw-5115rw-6138rw-7134rw-7、rw-7+共用rw-7+300rw-8134rw-9134rw-10134

24、成品1需求量50原料料号用量原料需求库存rw-1150134rw-22100127rw-3150133rw-4150126rw-52100115rw-6150138rw-75250134rw-8150134rw-92100134rw-102100134A1A2A3料号规格用量料号规格用量料号规格用量rw-11111 rw-111212 rw-211311rw-21122 rw-121222 rw-221322rw-31131 rw-131231 rw-231331rw-41141 rw-141241 rw-241342rw-51152 rw-151251 rw-251351rw-61161 r

25、w-161261 rw-261361rw-71175 rw-171272 rw-271371rw-81181 rw-181284 rw-281382rw-91192 rw-191291 rw-291394rw-101202 rw-201301 rw-301401物物料料清清单单基基础础资资料料A4A5料号规格用量料号规格用量rw-311412 rw-411512rw-321421 rw-421522rw-331432 rw-431531rw-341441 rw-441541rw-351455 rw-451551rw-361461 rw-461564rw-371471 rw-471571rw-3

26、81481 rw-481585rw-391492 rw-491591rw-401502 rw-501601成品需求A1A2A3A4A510005002001000A2A25 50 00 0料号规格用量原料需求rw-1112121000rw-1212221000rw-131231500rw-141241500rw-151251500rw-161261500rw-1712721000rw-1812842000rw-191291500rw-2013015001.依BOM1下拉自动显示对应物料清单。 绿色部分多单元格数组公式=OFFSET(INDIRECT(5.1!&ADDRESS(1,MATCH(A

27、5,5.1!A1:O1,),4),2,10,3)2.自动显示成品需求3.自动算出原料需求 绿色部分多单元格数组公式=OFFSET(INDIRECT(5.1!&ADDRESS(1,MATCH(A5,5.1!A1:O1,),4),2,10,3)排排程程日日期期转转换换- -星星期期、周周别别、季季度度日期公式5月1日5月2日5月3日5月4日5月5日10月1日星期几 =TEXT(C1,aaaa)星期日星期一星期二星期三星期四星期六 =TEXT(C1,dddd)SundayMondayTuesdayWednesdayThursday Saturday =WEEKDAY(C2,2)712346是否周末

28、=IF(MOD(C1,7)2,周末,工作日)周末工作日工作日工作日工作日周末周别 =ROUNDUP(C1/7,0)-ROUNDUP(DATE(YEAR(C1),1,1)/7,0)+1191919191940季度 =TEXT(MONTH(C1)/3+0.7,dbnum1D季度)二季度二季度二季度二季度二季度四季度 =ROUNDUP(MONTH(C1)/3,0)&季度2季度2季度2季度2季度2季度4季度10月2日10月3日10月4日星期日星期一星期二SundayMondayTuesday712周末工作日工作日414141四季度四季度四季度4季度4季度4季度杂杂乱乱库库存存进进出出数数据据修修正正1

29、00-35170-305-305用ABS在另外区域全变正数10050-2090-160-1605020-50120-190-1902030-5001030-1560-15603020-100220-340-340201000-8002600-4400-4400100010-2050-80-80105-3065-100-1005500-90680-1270-1270500100-40180-320-320100如何操作使绿色区域数据在原单元格处变成：1.全部正数 2.全部负数 3.正数变负数同时负数变正数？选选中中A1:E10A1:E10自自定定义义格格式式：1.#,#0;#,#01.#,#0;

30、#,#0全部正数2.-#,#0;-#,#02.-#,#0;-#,#0全部负数3.-#,#0;#,#03.-#,#0;#,#0正负互换35170305305209016016050120190190500103015601560100220340340800260044004400205080803065100100906801270127040180320320如何操作使绿色区域数据在原单元格处变成：1.全部正数 2.全部负数 3.正数变负数同时负数变正数？工工时时计计算算- -连连乘乘间间隔隔相相加加 =SUMPRODUCT(D3:N3*E3:O3*MOD(COLUMN(D:N),2)/10

31、00序号 料号/名称试算数量（片）大刀小刀冲床粘贴检验盖章/包装总总工工时时/1000/1000大刀时间人数小刀时间人数冲床时间人数粘贴时间人数检验时间人数盖章/包装时间人数单单片片总总制制程程工工时时1FJ0BB0002101000112233464.51230.0492FJ0EP00011010000.510.710.843631240.03353PHX-1889417-1-FOM-1A10000.51111.255541330.0502413994810000.51220.414331120.01685HTV01E001-L10000.511.511.535241110.0276HTV0

32、1E001-R10000.511.51133241110.02057072-0231-010000.510.510.310.310.00088072-0232-010000.810.81110.310.00189072-0418-010000.510.510.410.310.000910072-0593-010000.510.510.410.310.000911072-0643-010000.510.510.810.310.001312FJSL374500I10000.510.510.530.510.00113FJ0CF00010010000.510.510.552.5421220.02351

33、4FJ0CF00020010000.510.510.552.5421220.023515FJ0CN00050010000.5112132.542.51220.022516FJ0DJ00010010000.510.510.552.542.51220.025517FJ0DJ00020010000.510.510.552.542.51220.025518FJ0GT00010010000.8111142.542.51220.02419FJ0GT00020010000.8111142.542.51220.02420FJ0HO00010010000.410.41021FJ0HQ00010010000.41

34、0.4101.1.根根据据字字体体颜颜色色或或底底色色筛筛选选2.2.文文本本求求和和GET.CELL(24/38,表表!$A4)EVALUATE($E4)数据颜色底色字体颜色文本数据结果1 #NAME? #NAME?1+2#NAME?2 #NAME? #NAME?1*2*4#NAME?3 #NAME? #NAME?1+2*3#NAME?100 #NAME? #NAME?3*100*200#NAME?10 #NAME? #NAME?300/5/2#NAME?2 #NAME? #NAME?2+3-5#NAME?10 #NAME? #NAME?92#NAME?底色黄色求和#NAME?蓝色字体求和#

35、NAME?说明：宏表函数需插入名称，不能直接应用物物料料编编码码 =IF(D31,B3&C3&TEXT(SUBSTITUTE(D3,.,IF(LEN(D3)5,0,),0000)&TEXT(SUMPRODUCT($B$2:B2=B3)*($C$2:C2=C3)*($D$2:D2=D3)+1,0000),B3&C3&(LEFT(-SUBSTITUTE(D3&000,.,IF(LEN(D3)5,0,),4)*104)+(SUMPRODUCT($B$2:B2=B3)*($C$2:C2=C3)*($D$2:D2=D3)+1)物物料料名名称称( (整整理理后后）一级类型码(一位数)二级类型代码(三位数)

36、规规格格代代码码字字段段1(41(4位位数数) )物料编码(示例)合金铣刀23.5C50L53332533320000001球头刀R0.25QCT53330.25533300250001铰刀(加长）47.880C200L533347.8533347080001球头刀R0.5QCT53330.5533300050001合金铣刀100.535C75L5333100.5533310050001合金铣刀1240C80L533312533312000001合金铣刀1020C100L533310533310000001合金铣刀1025C75L533310533310000002合金铣刀1030C75L53

37、3310533310000003合金铣刀1050C100L533310533310000004合金铣刀1230C75L533312533312000002铣刀R0.75球形STK白钢53330.75533300750001球刀R0.7534D50L-2F53330.75533300750002合金铣刀1.53.5C50L53331.5533310500001铣刀R1.5球形STK白钢53331.5533310500002铣刀1.5 4FQCT53331.5533310500003铣刀1.5STK白钢53331.5533310500004白钢铣刀1.5-4F53331.5533310500005

38、螺纹刀片1.553331.5533310500006球刀1.5R0.755C65L-2F 53331.5533310500007球头刀R1.5QCT53331.5533310500008合金锥度刀1.5330C65L-4F53331.5533310500009合金锥度刀1.53R0.530C65L-4F53331.5533310500010合金锥度刀1.5438c65L-4F53331.5533310500011合金锥度刀1.54R0.538C65-4F53331.5533310500012合金铣刀2.58C50L53332.5533320500001深钩铣刀2.54850L53332.553

39、3320500002深钩铣刀2.58C28L150L 53332.5533320500003铣刀2.5 4FQCT53332.5533320500004长颈铣刀2.55L25L260LD4-4F53332.5533320500005球刀R2.553332.5533320500006合金铣刀4.510C60L53334.5533340500001铰刀4.522C4S60L53334.5533340500002铰刀4.725C75L53334.7533340700001铣刀R10.75球形STK白钢533310.75533310750001铰刀2.018C45L 53332.01533320010

40、001合金绞刀2.218C45L-4F53332.21533320210001合金绞刀2.318C45L-4F53332.31533320310001合金绞刀2.418C45L-4F53332.41533320410001绞刀2.9453332.94533320940001绞刀4.1153334.11533340110001合金绞刀4.516C60L-4F53334.51533340510001铰刀4.5111C60L53334.51533340510002问题说明：1、如规格代码字段1是整数,不足4位时,后面加0补充到4位;2、如规格代码字段1有小数点且加上小数点刚好是4位且小于0,则以0代

41、替小数点,并且0在数字前面.如E3单元格的示例3、如果规格代码字段1加上小数只有三位且小于0,则在规格前面加0补充;如E5单元格的示例.4、如果加上小数点有5位数,则只取4位数字,小数点不再用0替代.5、因为前4条组成的编码可能会有重复的，未位识别码用四位数字区分，可按“0001”为开始进行识别，以确保编码的唯一性总长度12位不变,如何用函数同时满足上述5个条件(E列中涂黄色的是已满足的)。如能解决，不胜感谢。 =IF(D31,B3&C3&TEXT(SUBSTITUTE(D3,.,IF(LEN(D3)5,0,),0000)&TEXT(SUMPRODUCT($B$2:B2=B3)*($C$2:C

42、2=C3)*($D$2:D2=D3)+1,0000),B3&C3&(LEFT(-SUBSTITUTE(D3&000,.,IF(LEN(D3)5,0,),4)*104)+(SUMPRODUCT($B$2:B2=B3)*($C$2:C2=C3)*($D$2:D2=D3)+1) =IF(D31,B3&C3&TEXT(SUBSTITUTE(D3,.,IF(LEN(D3)5,0,),0000)&TEXT(SUMPRODUCT($B$2:B2=B3)*($C$2:C2=C3)*($D$2:D2=D3)+1,0000),B3&C3&(LEFT(-SUBSTITUTE(D3&000,.,IF(LEN(D3)5

43、,0,),4)*104)+(SUMPRODUCT($B$2:B2=B3)*($C$2:C2=C3)*($D$2:D2=D3)+1) =IF(D31,B3&C3&TEXT(SUBSTITUTE(D3,.,IF(LEN(D3)5,0,),0000)&TEXT(SUMPRODUCT($B$2:B2=B3)*($C$2:C2=C3)*($D$2:D2=D3)+1,0000),B3&C3&(LEFT(-SUBSTITUTE(D3&000,.,IF(LEN(D3)5,0,),4)*104)+(SUMPRODUCT($B$2:B2=B3)*($C$2:C2=C3)*($D$2:D2=D3)+1)订订单单编编

44、码码 =IF(E3,TEXT(F3,yymm)&TEXT(COUNTIF($C$2:C2,TEXT(F3,yymm*)+1,000),)序序号号厂厂家家订订单单号号本本厂厂型型号号订订单单量量日日期期1A1103001DB-1666346003/03/112A1103002DD-289128003/03/113A1103003DD-279128003/03/114A加厚0.203/03/11A要环保材料03/10/115A现金03/10/116A1103004DB-1666346003/10/117A1104001DD-289128004/03/118A1104002DD-279128004/

45、03/119B04/03/1110B1104003DB-1666346004/21/1111B1104004DD-289128004/21/1112B1104005DD-279128004/21/11问题：在录入订单中输入订单量后订单号会按日期栏中的年份和月份加3位数递增自动生成，没输入订单量的为空，C列用什么函数可以实现？ =IF(E3,TEXT(F3,yymm)&TEXT(COUNTIF($C$2:C2,TEXT(F3,yymm*)+1,000),)库库龄龄计计算算 =IF(B3=,0,DATEDIF(B3,TODAY(),D)料号入库日期数量库存天数A15月1日134284A25月2日1

46、27283A35月3日133282A45月4日126281A55月5日115280A65月6日138279A75月7日134278A85月8日13427700indirectindirect多多表表求求和和1.1.求求表表1 1、2 2、3.13.1 J6J6的的和和？注：indirect多表求和：1、2有多余字节4758.060033如2原公式：=SUM(N(INDIRECT(ROW(1:2)&!&J6)可简化成： =SUM(N(INDIRECT(ROW(1:2)&!J6)数组公式 =SUM(N(INDIRECT(1,2,3.1&!&J6)一般公式 =SUMPRODUCT(N(INDIREC

47、T(1,2,3.1&!&J6)另，表名称中若有空格和一些特殊字符（$%!#&()+=,|; ）表名需加引号，才可得出正确结果。 =SUM(N(INDIRECT(1,2,3.1&!J6)写法如：=SUM(N(INDIRECT(&ROW(1:2)&!J6)为防止出错这样写更严谨。2.2.求求表表1 1、2 2。 J6J6的的和和？再另，若想下拉求多表J6、J7、J8、可写成：=SUM(N(INDIRECT(&ROW($1:$2)&!J&ROW(6:6) 下拉4617.0600334617.0600337216.157082数组公式 =SUM(N(INDIRECT(ROW(1:2)&!&J6)一般公

48、式 =SUMPRODUCT(N(INDIRECT(ROW(1:2)&!&J6) =SUM(N(INDIRECT(ROW(1:2)&!J6) =SUM(N(INDIRECT(&ROW(1:2)&!J6)indirect(&A2&!B3)重复记录求个数14 412334indirect多表求和：1、2有多余字节如2原公式：=SUM(N(INDIRECT(ROW(1:2)&!&J6)可简化成： =SUM(N(INDIRECT(ROW(1:2)&!J6)另，表名称中若有空格和一些特殊字符（$%!#&()+=,|; ）表名需加引号，才可得出正确结果。写法如：=SUM(N(INDIRECT(&ROW(1:

49、2)&!J6)为防止出错这样写更严谨。再另，若想下拉求多表J6、J7、J8、可写成：=SUM(N(INDIRECT(&ROW($1:$2)&!J&ROW(6:6) 下拉按按人人员员统统计计产产量量- -分分段段求求和和名单日期数量每人总量刘汗珠5月1日1349075月2日1275月3日1335月4日1265月5日1155月6日1385月7日134刘青云5月1日15510525月2日1585月3日1495月4日1505月5日1565月6日1475月7日137李通5月1日15210845月2日1615月3日1625月4日1585月5日1495月6日1365月7日166李巧红5月1日865805月2

50、日805月3日845月4日895月5日905月6日765月7日75王元明5月1日1128415月2日1105月3日1355月4日1285月5日1145月6日1145月7日128肖高法5月1日1339195月2日1305月3日1285月4日1315月5日1325月6日1355月7日130总计53835383#REF!依依优优先先顺顺序序及及需需求求量量分分配配【共共用用料料】已知條件料号优先顺序 需求量 共用料库存分分配配模模拟拟將共用料按C列数量分配，從上到下，直至把共用料分完。MI5B10290111000650001000 =MIN(SUM(D$3:D3)-SUM(F$2:F2),C3)M

51、I5B102902265646564MI5B102903345124512MI5B102904412311231MI5B10290551231312313MI5B10290665555MI5B102907754565456MI5B10290884546433869MJ0A10B9051200003520020000MJ0A10B91521500015000MJ0A10B91631000200MJ0A10B9174100000將共用料按C列数量分配，從上到下，直至把共用料分完。 =MIN(SUM(D$3:D3)-SUM(F$2:F2),C3)料号日期数量依C列累计求和再再依依D D列列反反累计求

52、和问问题题：已已知知累累计计求求和和列列，如如何何用用公公式式在在E E列列得得出出和和C C列列相相同同的的数数量量？a10月1日100100100a10月2日5015050a10月3日150016501500a10月4日2001850200a10月5日4002250400a10月6日8603110860a10月7日9004010900a10月8日2004210200a10月9日100052101000a10月10日130065101300a10月11日200085102000a10月12日5009010500a10月13日4009410400问问题题：已已知知累累计计求求和和列列，如如何何用用公公式式在在E E列列