psim言語講座（第5回）psimとexcelを連携してみる

本記事は当社が発行しているシミュレーションメールマガジンVol.5の記事です。
シミュレーションメールマガジンの詳細・購読申込はこちら のサポートページから

Excel 連携

今まで、幾つかの待ち行列モデルの実装を行ってきました。

まだ、psim の一部の機能しか解説できていませんが、ここまで解説してきた機能だけでも、様々なモデルに応用できるはずです。

今回はちょっと脱線しますが、psim 本体の解説ではなく、psim を使って書いたプログラムを excel から呼び出す方法を解説しようと思います。

サンプル

色々説明するより、試して頂いた方が早いと思うので、サンプルを以下に置きます。

こちら

まず、以下をご確認下さい。

• S⁴ 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 で行えば、使い勝手が格段に良くなると思います。是非ご活用下さい。