psim言語講座(第6回)psimとexcelを連携してみる(グラフ編)

  • HOME
  • psim言語講座(第6回)psimとexcelを連携してみる(グラフ編)

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

Excel 連携によるグラフ表示

前回は psim を使って書いたプログラムを excel から呼び出す方法を解説しました。

前回は計算結果を数値として表示するのみでしたが、今回はグラフを表示してみようと思います。グラフは、excel のグラフ機能ではなく、より細かいカスタマイズが可能な matplotlib を利用してみるという方針です。

サンプル

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

こちら

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

  • S4 6.0 以上がインストールされている必要があります。
  • Excel 2016 以降がインストールされている必要があります。

確認後、setup.bat を実行して下さい。必要な環境が自動的にダウンロードされます(インターネット接続環境が必要です)。

sim.xlsm を開き「実行」ボタンをクリックして下さい。M/M/1 シミュレーションの結果とグラフが表示されます。

ファイル構成

  • setup.bat: セットアップ起動ファイル
  • setup.py: セットアップスクリプト
  • sim.py: シミュレーション本体
  • sim.xlsm: シミュレーションを起動する excel ファイル

仕組み

基本的な仕組みは前回と同じです。

連携には、python パッケージ xlwings を利用しています。

excel 上の「実行」ボタンを押すと、最終的に、sim.py 上の runSimulation が実行されます。実行までの内部の仕組みは前回の記事を参照下さい。

sim.py は、以下のように構造になっています。グラフを埋め込むために修正された関連部分のみを抜粋して説明します。

from psim import *

def runSimulation():
    book = xw.Book.caller()
    sheet = book.sheets[0]
    plt.rcParams['font.sans-serif'].insert(0, "Meiryo")

    initialize()

    lmd = xw.Range("B3").value # 平均到着率
    mu = xw.Range("B4").value # 平均サービス率
    simtime = xw.Range("B5").value # シミュレーション時間

    # シミュレーションパラメータの計算
    # 実装コード省略(変更無し)

    # 到着プロセス
    def arrive():
        # 実装コード省略(変更無し)

    # サービスプロセス
    def service():
        # 実装コード省略(変更無し)

    # 到着プロセスの開始を登録する
    activate(arrive)()

    # シミュレーション開始
    start(until = simtime)

    # シミュレーション結果の計算
    # 実装コード省略(変更無し)

    # シミュレーション結果の書き込み
    xw.Range("B9").value = waitingTime
    xw.Range("B10").value = queue
    xw.Range("D9").value = W
    xw.Range("D10").value = Q

    # 以下、結果をグラフ化
    with Graph(sheet, "Plot1", "G2", width = 720, height = 360) as (fig, ax):
        x = f.monitor.time().toList()
        y = (f.monitor["要求待ち"] + f.monitor["バッファ"]).toList()
        ax.plot(x, y, label = "システム内客数")
        ax.set_xlabel("時間")
        ax.set_ylabel("システム内客数")
        ax.set_title("システム内客数")
        ax.legend()

    with Graph(sheet, "Plot2", "G17") as (fig, ax):
        ax.hist(monitor["waitingTime"].toList())
        ax.set_xlabel("待ち時間(秒)")
        ax.set_ylabel("頻度")
        ax.set_title("待ち時間の分布")

    with Graph(sheet, "Plot3", "L17") as (fig, ax):
        w = f.monitor.time().diff()
        v = (f.monitor["要求待ち"] + f.monitor["バッファ"]).toList()[:-1]
        ax.hist(v, weights = w, density = True)
        ax.set_xlabel("システム内客数")
        ax.set_ylabel("密度")
        ax.set_title("システム内客数の分布")

シミュレータ本体部分は、前回と同様のコードになっていて、変更ありません。

後半部分が 3 つのグラフを埋め込んでいるコードです。

xlwings の機能を使ってグラフを埋め込むためには、様々な手順が必要なので、今回はそれら簡単に埋め込むための Graph クラスを作成しました。汎用的に作っていますので、簡単にグラフを埋め込み可能です。ここでは利用方法を説明します。

Graph クラスは以下な形で利用します。

    with Graph(sheet, "Plot1", "G2", width = 720, height = 360) as (fig, ax):
        # ax にグラフを描画
        # 例: ax.plot(x, y)

これは、excel 上のシートに、G2 セルに、"Plot1" という名前で、720x360 の大きさで、グラフを埋め込みます。

excel にはグラフの名前という概念があります。前述のグラフの名前には任意の名前を設定できますが、同じシート上に同じ名前のグラフを描画を指示すると既存のグラフが上書きされます。

グラフへの描画は、fig と ax がバインドされていますので、ax に対し matplotlib の描画命令を行う事で、自由にグラフを描画する事ができます。

例えば、x と y に、python のリストが設定されている時に折れ線グラフを表示したい場合、

        ax.plot(x, y)

と書く事ができます。

例えば、v に観測値のリストが格納されている時にヒストグラムを表示したい場合、

        ax.hist(v)

と書く事ができます。

Graph クラス

Graph クラスは中身を理解せずとも使えるように設計していますが、実装は以下のようになっています。Python の with 構文での利用を想定しており、enter が初期化コード、exitが後始末コードです。

class Graph:
    def __init__(self, sheet, plotname, cell, width = 360, height = 360):
        self.sheet = sheet
        self.plotname = plotname
        self.cell = cell
        self.width = width
        self.height = height
    def __enter__(self):
        dpi = plt.rcParams["figure.dpi"]
        if self.plotname in self.sheet.pictures:
            pic = self.sheet.pictures[self.plotname]
            figsize = (pic.width / dpi, pic.height / dpi)
        else:
            figsize = (self.width / dpi, self.height / dpi)
        fig, ax = plt.subplots(1, 1, figsize = figsize)
        return fig, ax
    def __exit__(self, exc_type, exc_value, traceback):
        dirname = os.path.dirname(self.sheet.book.fullname)
        pngname = os.path.join(dirname, "temp.png")
        plt.tight_layout()
        plt.savefig(pngname)
        self.sheet.pictures.add(pngname,
                                name = self.plotname,
                                update = True,
                                left = self.sheet.range(self.cell).left,
                                top = self.sheet.range(self.cell).top)

まとめ

以上で、excel と psim を連携し、結果のグラフを埋め込む事できました。

グラフの描画は excel でも行えますが、今回は、matplotlib を使った方法をご紹介しました。

matplotlib は非常に多機能なグラフライブラリですので、是非とも色々なグラフ描画をお試し頂ければと思います。こちらより、matplotlib の利用例を参照できます。

監修:株式会社NTTデータ数理システム 機械学習、統計解析、数理計画、シミュレーションなどの数理科学を 背景とした技術を活用し、業種・テーマを問わず幅広く仕事をしています。
http://www.msi.co.jp NTTデータ数理システムができること
「数理科学の基礎知識」e-book無料ダウンロードはこちら

関連記事