運(yùn)籌學(xué)實(shí)驗(yàn)8、用EXCEL進(jìn)行排隊(duì)問題仿真

1、實(shí)驗(yàn)八、基于Excel的排隊(duì)問題仿真排隊(duì)問題常常連續(xù)地或并行地發(fā)生（例如在裝配線和工作車間），通常無法用建立數(shù)學(xué) 模型的方法解決。然而，排隊(duì)問題通常容易在計(jì)算機(jī)上進(jìn)行仿真。下面我們通過一個(gè)兩階段 裝配線的例子闡述如何借助于Excel建立一個(gè)排隊(duì)問題的仿真模型。一、實(shí)驗(yàn)?zāi)康?、掌握如何用Excel建立排隊(duì)問題仿真模型；2、讀懂Excel輸出的運(yùn)算結(jié)果，并用于指導(dǎo)實(shí)踐。二、實(shí)驗(yàn)內(nèi)容兩階段裝配線問題一條裝配線所組裝的產(chǎn)品體積可能很大，例如：冰箱、空調(diào)機(jī)、汽車、電視機(jī)或家具、 圖1表示的是一條裝配線上的兩個(gè)工作站。產(chǎn)品的體積是裝配線分析和設(shè)計(jì)所要考慮的一個(gè)重要因素，因?yàn)槊總€(gè)工作站上所能存放 的產(chǎn)品數(shù)量

2、將會(huì)影響工人的工作。如果產(chǎn)品體積很大，那么相鄰的工作站存在著相互依賴的 關(guān)系。如圖1所示，鮑博和雷在一個(gè)兩階段裝配線上工作，鮑博在工作站1上裝配完的產(chǎn)品傳 遞給工作站2上的雷，雷再進(jìn)行加工。如果兩個(gè)工作站相連，中間沒有存入半成品的地方， 那么鮑博如果干得慢，雷就會(huì)被迫等待；相反，如果鮑博和干得快（或者說雷完成工作比鮑 博用時(shí)長），那么鮑博就得等雷。在這個(gè)仿真問題中，我們假設(shè)鮑博是組裝線上的第一個(gè)工人，他能夠在任何時(shí)候拿到需 組裝的半成品進(jìn)行工作。那么，我們把分析重點(diǎn)放在鮑博與雷彼此之間的相互影響上。1、研究的目標(biāo)：關(guān)于這條裝配線，我們希望能通過研究解決一些問題。下面是我們列 出的部分待解決的問

3、題：。每個(gè)工人的平均完工時(shí)間是多少？。這條組裝線的生產(chǎn)率是多少？。鮑博等待雷的時(shí)間是多少？。雷等待鮑博的時(shí)間是多少？。如果兩個(gè)工作站中間的空間加大，可以存儲(chǔ)半成品，從而增加了工人的獨(dú)立性，那么 這對(duì)于生產(chǎn)率、等待時(shí)間等問題會(huì)有什么影響？2、數(shù)據(jù)的采集：進(jìn)行系統(tǒng)仿真，我們需要鮑博和雷的裝配時(shí)間數(shù)據(jù)。要收集這些數(shù)據(jù)， 一種方法就是將總裝配時(shí)間分割成小段時(shí)間，在每段時(shí)間對(duì)工人進(jìn)行單獨(dú)觀測(cè)。對(duì)這些數(shù)據(jù) 進(jìn)行簡(jiǎn)單的匯總和分析，我們可以得到非常有用的直方圖。表1顯示的是觀測(cè)鮑博和雷兩人裝配時(shí)間后得到的數(shù)據(jù)收集表格。為了簡(jiǎn)化操作過程， 裝配時(shí)間以10秒為區(qū)間進(jìn)行劃分。對(duì)鮑博的工作我們進(jìn)行了 100次觀測(cè)，而

4、對(duì)雷的觀測(cè)我們 只進(jìn)行了50次。二者的觀測(cè)次數(shù)可以不同，但觀測(cè)次數(shù)越多，時(shí)間間隔的劃分越細(xì)，則研究 的準(zhǔn)確性越高。然而，時(shí)間間隔越小，觀測(cè)次數(shù)越多，需要投入的時(shí)間和精力也就越多。表2中包含了按照實(shí)際觀測(cè)數(shù)據(jù)的比率進(jìn)行分配的隨機(jī)數(shù)區(qū)間。例如，鮑博在100次操作 中有4次在10秒鐘內(nèi)完成。因此，如果我們用100個(gè)數(shù)進(jìn)行分配，那么我們應(yīng)該分配4個(gè)數(shù)與 10秒鐘相對(duì)應(yīng)。這4個(gè)數(shù)可以是任意的，例如，42、18、12和93，但是，這會(huì)使查找工作變 得 非常繁瑣，所以我們就分配連續(xù)數(shù)，比如00、01、02和03。我們得到了50個(gè)對(duì)雷的觀測(cè)的值。我們可用兩種方法來分配隨機(jī)數(shù)。第一種方法是，就 用50個(gè)數(shù)（如0

5、049）來進(jìn)行分配，并在仿真時(shí)忽略掉所有超過49的數(shù)。然而，這是一種浪 費(fèi)，我們將丟棄隨機(jī)數(shù)列中50%的數(shù)。另一種方法是將頻率次數(shù)加倍。例如，我們不是將0003分配給50次觀測(cè)中裝配時(shí)間為10秒的4次觀測(cè)，而是將0007分配給10 0次觀測(cè)中的8次觀 測(cè)，這樣的話，觀測(cè)次數(shù)加倍了但比例不變。表1工人觀測(cè)數(shù)據(jù)收集表時(shí)間（秒）鮑博雷次數(shù)次數(shù)09.9944019.9965029.99106039.99207049.994010059.99118069.9956079.9944次數(shù)合計(jì)10050表2鮑博和雷的隨機(jī)數(shù)區(qū)間秒鮑博的次數(shù)隨機(jī)數(shù)區(qū)間雷的次數(shù)隨機(jī)數(shù)區(qū)間1040003400072060409508

6、1730101019618294020203973043504040791044636011809086479705919568091804969949299次數(shù)合計(jì)100503、手工仿真：表3顯示的是對(duì)鮑博和雷裝配10件產(chǎn)品的手工仿真結(jié)果。隨機(jī)數(shù)來自于隨機(jī)數(shù)表，從二位數(shù)的第一列開始向下取數(shù)。表3鮑博和雷一一兩階段性裝配線的仿真鮑博和雷-鮑博-一兩階段裝配線的仿真雷工件隨機(jī)開始加工完工等待存儲(chǔ)隨機(jī)開始加工完工等待序號(hào)數(shù)時(shí)間時(shí)間時(shí)間時(shí)間空間數(shù)時(shí)間時(shí)間時(shí)間時(shí)間1560050500083507012050255505010020047120501700384120601800008180202001

7、04361804022000052201023020526220402600042260403003069526070330009533080410307663305038030018410204300803410104201002143030460095743050480003148040520201069480505300090530706001047060430170假設(shè)我們從00時(shí)間開始，接下來以秒來計(jì)算。第一個(gè)隨機(jī)數(shù)56對(duì)應(yīng)于鮑博第一個(gè)裝配工 作時(shí)間50秒。這個(gè)工件傳送給雷，他的開始時(shí)間是第0秒。接下來的隨機(jī)數(shù)是83,根據(jù)表2， 雷用70秒完成了工作。同時(shí)，鮑博開始裝配下一件產(chǎn)品，從

8、第50秒開始用時(shí)50秒(隨機(jī)數(shù)55)， 在第100秒完成。然而，鮑博無法開始第三件產(chǎn)品的工作，因?yàn)槔自诘?20秒才干完頭一件活 兒。因此，鮑博等了 20秒(如果鮑博與雷的工作站之間有存儲(chǔ)空間，鮑博干完的工件可以移 出工作站，在第100秒鮑博就可以干下一個(gè)活兒)。表里剩下的數(shù)據(jù)可以用同樣的方法來計(jì) 算：得到一個(gè)隨機(jī)數(shù)，找到對(duì)應(yīng)的加工時(shí)間，注意等待的時(shí)間(如果有的話)，并計(jì)算完工 時(shí)間。我們可以看出，由于鮑博與雷之間沒有存儲(chǔ)空間，兩位工人的等待時(shí)間都很長。現(xiàn)在， 我們可以回答一些問題，并且可以對(duì)系統(tǒng)進(jìn)行一些評(píng)述。例如：每個(gè)工作的平均加工時(shí)間為60秒(總共用時(shí)為600秒，平均分配給雷加工的10個(gè)工件

9、)。鮑博的利用率為470/530=88.7%雷的利用率為430/550=78.2% (除去開始的等待時(shí)間50秒)鮑博的平均加工時(shí)間為：470/10=47秒雷的平均加工時(shí)間為：430/10=43秒4、運(yùn)用Excel進(jìn)行仿真第一步，運(yùn)用RAND()函數(shù)產(chǎn)生隨機(jī)數(shù)任何仿真方法的一個(gè)基本步驟就是，生成與分布函數(shù)相關(guān)的隨機(jī)變量，在本例中分布函 數(shù)是關(guān)于鮑博和雷加工時(shí)間的分布。RANDBETWEEN ()函數(shù)可以生成任意指定數(shù)值之間的隨機(jī)數(shù)值。在本例中，我們需要產(chǎn)生 的是099的隨機(jī)數(shù)，因此我們可以用公式“二RANDBETWEEN(0,99)”來生成099的隨機(jī)數(shù)。 選定A2 : A101輸入099共10

10、0個(gè)隨機(jī)數(shù)，在B2 : B101輸入各隨機(jī)數(shù)對(duì)應(yīng)的加工時(shí)間，并將區(qū) 間A2 : B101命名為隨機(jī)數(shù)區(qū)間。第二步，運(yùn)用查找函數(shù)VLOOKUP建立隨機(jī)數(shù)與加工時(shí)間之間的關(guān)系圖1綜述了模擬鮑博和雷完成800個(gè)工件產(chǎn)品后的結(jié)果。將這些數(shù)據(jù)與我們手工模擬的10 個(gè)工件數(shù)據(jù)相比，手工模擬的結(jié)果還不是太糟。鮑博的平均工作時(shí)間為46秒，這非常接近于 長期運(yùn)行時(shí)你所期望的平均值。鮑博工作時(shí)間的期望值是(10X4+20X6+30X10+) /100=45.9秒。雷的工作時(shí)間的期望值是(10X4+20X5+30X6+)/50=46.4秒。A3LL：H1-七LF1兔期數(shù)區(qū)間時(shí)間:JUK工件阻1湖俊開拍JUT導(dǎo)待旋h

11、it軟開角y完工等待莓工件枝 EE,311L=號(hào)數(shù)取整f - I iN-：|h|時(shí)間H問H l |hH|h時(shí)間同if平均用時(shí)用N10iUU乂utu4U9“bJ川.J亂15310225EU1Cw:葺77tu50LJCj70. j凱G2：三云葵5CIFu:由150502：C_ j=6. 7一 0T204:LIE1LjHliUu4?史2uu和於：j蒙M6202E2C04C心10LJId250302EC?56. ?勺7F4s史泓由偵:2J23商402?汝71 X7-vn巧ilKiV=11FlF4K1嘰R1 1|r：.F4747Wi飛ill/h勺il時(shí)4?r1咬Z1 zmr4141 nT5ii795ii

12、心r17. 4r11LC31470WF2DF2Dg薩r洛】)11r芒加Wrvninmn質(zhì)r-i77. 31_inr12-r第丁CiX(前:1：irrm即G-C-7. )1:14K5W513C05C711):373C71D402D丁2017IFme141Z12710正74Did土7EDCD以Z7. 1co1Lu，LLL;LL7UUdi匕LUUJ上, (仙LLiiLt上叢AU4匚 L40)(Jt L40出J偵攵LU乂17認(rèn)乂加1出ID9 AJ乂.(知LUso1日LrLrJLU1:Lb列處LZJ乂. ：1bU圖1鮑博和雷兩階段性裝配線的Excel仿真表1圖1中的Excel單元格公式D5=100*RAND()L5=MAX(N4,H5)E5=INT(D5)M5=VLOOKUP(K5,隨機(jī)數(shù)區(qū)間,2,FALSE)F5=H4+I4N5=L5+M5G5=VLOOKUP(E5,隨機(jī)數(shù)區(qū)間，2,FALSE)O5=MAX(0,H5-N4)H5=F5+G5P5=N5/C5I5=MAX(0,N4-H5)Q5=G5+I5+M5J5=100*RAND()R5=AVERAGE($Q$4:Q5)K5=INT(J5)三、課外練習(xí)1、練習(xí)查找函數(shù)VLOOKUP的使用；2、試著利用上述實(shí)驗(yàn)中提供的方法對(duì)現(xiàn)實(shí)中的一些排隊(duì)問題建 立仿真