EXCEL應用-棒球賽模擬

日期:2010-07-20

Excel有一項非常強大的功能,就是模擬隨機出現的情形,其應用非常廣大如品質管制、餐廳規畫、交通流量分析以及投資策略分析等。最近因為要開Excel的財務應用課程,Excel模擬也是教學重點,為了增加學習的趣味性,我做了一個棒球賽的模擬Excel檔案,做為上課的教材。

現實生活是多變的

在現實環境中很多事並不是固定的,會隨著時間變動。例如7/11的茶葉蛋,每天賣出去的數量一定不一樣,餐廳的來客數也不是每天一成不變,投資者每年的投資報酬率也不會都一樣。面對這些變動,7/11採購者如何知道要每天要採買多少茶葉蛋?餐廳老闆如何規劃外場大小?一般人如何估多少錢才足夠退休生活?利用統計學可以做好這些規劃,再透過模擬可以清楚看出規畫完成的系統是否有漏洞,有無需要調整的地方。

為何需要模擬

有一些系統是複雜的,不容易一開始就如所規劃般的運作,尤其當問題發現時,有時候想變更設計是非常困難的,這時事前的模擬就非常重要。例如道路、橋樑及收費站的規劃就是典型的例子,如果橋樑蓋好了才發現寬度不夠,會造成交通壅塞,這時再更改設計已經太晚了。如果設計前可以先做流量模擬,這問題或許有機會及早改善。

餐廳的規劃也是另一個例子,廚房需要幾個爐子、多少廚師,以及流程如何。外場可以放幾張桌子,同時容納多少客人,以及服務生的數目。若事先未規劃得宜,等裝潢完成開始營運後才發現不對,再修改就是大工程了。透過模擬可以先行預知廚房是否規劃太小或太大,平均每位客人等待時間會是多久,而且連每月收入也可以一併模擬分析。

財務的模擬應用更是多得不勝枚舉,退休規劃就是一個很典型的例子。退休規劃的一項重要參數是:平均報酬率(μ),未來每年所需的生活費用都用此報酬率去折成現值,然後求出退休前所需準備的金額是多少。這是屬於規畫部分,但現實情況下每年報酬率是變動的,會在平均值上下波動,其波動度可以用標準差(σ)來表示。利用模擬的方式,以平均報酬率及標準差就可以模擬30年後這些錢是否真的足夠。

股價也可以模擬,只要設定波動率及起始股價,就可以模擬未來股票可能發生狀況。股價模擬可以用來探討所規劃的投資策略是否正確,例如定值定額投資策略,或者選擇權的投資策略等。一般人喜歡用迴流分析來看投資策略,但畢竟過去的資料只是所有可能發生狀況的其中之一而已,然而模擬可以指出較多的盲點。

模擬無法取代規劃

現今電腦軟體發達,電腦模擬已經非常普遍,善用軟體模擬複雜系統,對營運系統調整是很有幫助的。但是模擬並無法取代設計規劃,模擬只能用來驗證整體表現(performance)是否和規劃一樣,然後再加以調整。

Excel模擬棒球賽

如果系統不是很複雜,利用Excel來做模擬是非常方便的。例如單通道排隊理論(Queuing Theory),就可以輕易的以Excel模擬出來。為了解釋Excel的模擬功能,我做了一個簡化版的棒球賽模擬。

簡化版棒球規則

這球賽是簡化的,規則如下所示:

  • 沒有盜壘、沒有暴投、所以也不會有失誤
  • 每次安打都只前進一壘,也就是沒有2、3壘安打,當然更沒有全壘打
  • 每位參賽者都可自訂打擊率 (S11:T20)
  • 只打九局,沒有延長賽,平手為和局

訂定球員特質

每位球員的特質可以由過去的平均打擊率來制訂,只要在儲存格(S11:T20)輸入兩隊球員之過去打擊率,每次打擊都會根據這表格模擬成「安打」或「出局」。整場比賽就是根據輸入的打擊率,賽完九局然後看得分數。

▼將隊員過去打擊率輸入至下表
打擊率

比賽過程

比賽過程都被詳盡的記錄著,每一局打者是誰、擊出安打或出局,以及壘包是否有跑者,都被詳盡的記錄著。可以完整的看到整個賽事的輪廓。

▼比賽過程明細比賽記錄

壘包說明
壘包說明

比賽結果

同時也模擬一個計分板,可以看到每一局的得分數。

得分版得分版

比賽統計

整場比賽的打數、以及平均打擊率都會清楚顯示。

▼本次賽局統計資料
統計數字

有何用途

大費周章的做了一個棒球賽的模擬,到底有何用途呢?如果真有棒球賽是按照上述規則來玩,那麼球隊教練就可以利用這Excel,事先模擬要錄取什麼樣打擊率的球員,才較有機會得冠軍。以及在已知的球員當中,如何安排打擊順序最有機會贏球。這模擬就好似沙盤推演一般,還沒上戰場就已知輸贏了。

當然不會有這種簡易規則的球賽,所以這Excel檔以教育為主,以球賽的趣味性來增加學習效果。當然整篇文章隱含著一個重要訊息:如果棒球賽也可以用Excel模擬、還有甚麼不行的!這個Excel沒有用到任何一行VBA程式,單純只使用Excel函數及技巧,是課堂裡很不錯的教材及作業。

Excel檔案下載

點選下列圖示可以下載棒球模擬的Excel檔案。這裡有兩個版本,請下載合適的版本。

檔案下載(Excel 2007)

檔案下載(Excel 97~2003)



■ 本網站內容儘可能精確完整,但不保證無誤。若做為投資依據,風險請自行斟酌 ,本網站不負賠償之責任。
■ 網站所有資料均為版權所有,非經書面允許請勿轉載或使用。