- HOME
- psim言語講座(第5回)psimとexcelを連携してみる
2022年1月 6日 15:20
本記事は当社が発行しているシミュレーションメールマガジンVol.5の記事です。
シミュレーションメールマガジンの詳細・購読申込はこちら
のサポートページから
- psim言語講座(第1回)M/M/1シミュレーションを書いてみる
- psim言語講座(第2回)M/M/1シミュレーションを読んでみる
- psim言語講座(第3回)M/M/1 シミュレーションを拡張してみる
- psim言語講座(第4回)生産ラインシミュレーションを書いてみる
- psim言語講座(第5回)psimとexcelを連携してみる
- psim言語講座(第6回)psimとexcelを連携してみる(グラフ編)
- psim言語講座(第7回)連続型シミュレーションを書いてみる
- psim言語講座(第8回)エージェントシミュレーションを書いてみる
- psim言語講座(第9回)エージェントシミュレーションを拡張してみる
- psim言語講座(第10回)チュートリアル編(1)
- psim言語講座(第11回)チュートリアル編(2)
Excel 連携
今まで、幾つかの待ち行列モデルの実装を行ってきました。
まだ、psim の一部の機能しか解説できていませんが、ここまで解説してきた機能だけでも、様々なモデルに応用できるはずです。
今回はちょっと脱線しますが、psim 本体の解説ではなく、psim を使って書いたプログラムを excel から呼び出す方法を解説しようと思います。
サンプル
色々説明するより、試して頂いた方が早いと思うので、サンプルを以下に置きます。
まず、以下をご確認下さい。
- S4 6.0 以上がインストールされている必要があります。
- Excel 2016 以降がインストールされている必要があります。
確認後、setup.bat を実行して下さい。必要な環境が自動的にダウンロードされます。(インターネット接続環境が必要です)
sim.xlsm を開き「実行」ボタンをクリックして下さい。M/M/1 シミュレーションの結果が表示されると思います。
ファイル構成
- setup.bat: セットアップ起動ファイル
- setup.py: セットアップスクリプト
- sim.py: シミュレーション本体
- sim.xlsm: シミュレーションを起動する excel ファイル
仕組み
連携には、python パッケージ xlwings を利用しています。
excel 上の「実行」ボタンを押すと、excel 上で、マクロ runSimulation が実行されます。
psim スクリプトの変更するだけなら、excel マクロの知識は必要ありませんが、もしマクロを確認したい場合は、excel の開発メニューから、Visual Basic を開いて下さい。なお、開発メニューはお手元の環境では表示されていないかもしれません。その場合は、適宜 excel のマニュアル等ご確認下さい。
マクロ runSimulation は、xlwings の RunPython 関数を使って、
import sim; sim.runSimulation()
を実行します。
実行される python 関数 sim.runSimulation は、sim.py に書かれています。
sim.py は、以下のようになっています。
from psim import * def runSimulation(): initialize() lmd = xw.Range("B3").value # 平均到着率 mu = xw.Range("B4").value # 平均サービス率 simtime = xw.Range("B5").value # シミュレーション時間 # 到着間隔を生成する乱数発生器 g1 = exponentialDistribution(1 / lmd) # サービス時間を生成する乱数発生器 g2 = exponentialDistribution(1 / mu) # 窓口を示すファシリティ資源 f = Facility(1, monitor = True) # 待ち時間を記録するモニター monitor = Monitor(["waitingTime"], ["f"], name = "wt") # 到着プロセス def arrive(): while True: # 次の到着まで待つ yield pause(next(g1)) # サービスプロセスを開始する # - この処理自体は非同期なので、一瞬で完了する yield subactivate(service)() # サービスプロセス def service(): t0 = now() # 窓口の待ち行列に並ぶ # - 窓口を示すファシリティ資源の利用を予約する # - 既に利用者がいる場合は、利用可能になるまで待ち受ける lock = (yield f.request(name = "lock"))["lock"] # 窓口サービスを開始する # - 窓口を示すファシリティ資源の利用権を取得した # - 利用権を開放するまで他の利用者は利用できない t1 = now() # 待ち時間を記録 monitor.observe(t1 - t0) # 窓口を利用する yield pause(next(g2)) # 窓口を示すファシリティ資源の利用権を開放する lock.release() # 到着プロセスの開始を登録する activate(arrive)() # シミュレーション開始 start(until = simtime) # M/M/1 の理論値 rho = lmd / mu # 平均利用率 Q = rho / (1.0 - rho) # 平均待ち行列 W = Q / mu # 平均待ち時間 waitingTime = monitor["waitingTime"].mean() # 平均待ち行列(窓口利用者をカウントしない) queue0 = f.monitor["要求待ち"].mean() # 平均システム内客数(窓口利用者をカウントする) queue = (f.monitor["要求待ち"] + f.monitor["バッファ"]).mean() #print(f"平均待ち時間: {waitingTime:.3f} (理論値: {W:.3f})") #print(f"平均システム内客数: {queue:.3f} (理論値: {Q:.3f})") xw.Range("B9").value = waitingTime xw.Range("B10").value = queue xw.Range("D9").value = W xw.Range("D10").value = Q
基本部分は、今までの解説の通りの M/M/1 のコードになっています。
その上で、excel 連携のため、先頭に以下のようなコードが加わっています。
lmd = xw.Range("B3").value # 平均到着率 mu = xw.Range("B4").value # 平均サービス率 simtime = xw.Range("B5").value # シミュレーション時間
これは、excel の B3, B4 セルから入力値を読み込んでいます。
その後、psim を使って、M/M/1 シミュレーションを行い、最後に、
xw.Range("B9").value = waitingTime xw.Range("B10").value = queue
にて、結果をB9, B10 セルに書き込んでいます。
まとめ
以上で、excel と psim の連携ができました。
excel は簡単な集計などには非常に便利なのですが、待ち行列モデルのシミュレーションを行う事は難しいです。
excel と psim の連携は慣れないと非常に苦労するのですが、本パッケージをテンプレートとして、シミュレーション部分を修正するだけで、様々なシミュレーションのフロントエンドになると思います。
難しいシミュレーション部分は psim を使い、前処理、集計処理、グラフ化は excel で行えば、使い勝手が格段に良くなると思います。是非ご活用下さい。