エクセルVBAでPythonを動かし、シミュレーションを実施しよう

前回の計２回で、Run Mainによる実行とRun Pythonによる実行を紹介しました。今回は、エクセルVBAでPythonを紹介していくのですが、最後の有料エリアで、実際のファイルを公開します。

（あまり関係ない話しをちょっとします）
2022年4月4日週時点で、Googleの検索で『エクセル Python実行』と『エクセル R実行』の検索数を比較すると前者のほうが圧倒的に検索数が少ないみたいです。約3倍も開きがある所からすると、Rをエクセルから実行したい人のほうが多いのでしょうか？？
エクセル Python実行：約 1,280,000 件
エクセル R実行：約 3,300,000 件

<Run Mainによる実行>

＜Run Pythonによる実行＞

今回はRun Pythonの機能を使ってこれまでの実行してきた内容を１つにして、エクセルのVBAからPythonの実行をして結果を確認していく所を紹介していきます。

まず完成したエクセルを実行するとできるイメージをご紹介します。

おーー！なんか動いている！！と最初は私もなりました。
では、ここからこれを実現するために、以下の手順で順を追って紹介していきます。

なお、今回は前回のhello.xlsxを使っていくことにします。余計なものはいらないので、ボタン等は削除した状態から始めます。（なお、今回参照するためVBAのモジュールは残しておいてください）

１．エクセルで実行の前提を整える
２．Pythonコードを記載する
３．コード実行用にVBAとエクセル更新
４．実行
５．平均のシミュレーション結果可視化
６．シュミレーション結果を見てみよう

のステップで進めていきます。

１．エクセルで実行の前提を整える

まず第一弾として、色をつけたか箇所に数値が入るようにエクセルの前提を整えていきます。

実行回数を100、平均を0、標準偏差を0としてB列とC列に入れた状態にします。なお、実行のボタンについては、最初の段階では不要ですのでまずは、前提条件とシミュレーション結果を入れるように形を整えておきます。

２．Pythonコードを記載する

次にPythonコードを書いていきます。最初から正解をいうのも、少しもったいない気もしますが、以下をhello.pyファイルに加えていきます。

import numpy as np
#正規分布での値を発生させるためにnumpyをimportします

def mean_simulation(): 
    wb = xw.Book.caller()
    sheet = wb.sheets[0]
    iteration = int(sheet.range("C2").value) #シミュレーション回数を整数として取得 
    mean = sheet.range("C3").value
    std = sheet.range("C4").value

    for i in range(iteration):
        sheet.range("C7").value = np.random.normal(mean, std)

少し解説になりますが、xlwingsの内容としては、

こちらになりますので、セルの値を取るためのコードや、その値を別のセルに入力する方法については上記の記事でカバーしています。

今回の処理では、エクセルで入力したシミュレーション回数と、平均・標準偏差を使って、平均の値を正規分布でシミュレーション実行しているということになります。

コードでその処理を実行しているのは以下の部分でfor文を使って正規分布( np.random.normal)に平均(mean)と標準偏差(std)を渡してシミュレーション回数(iteration)分実行しているという流れです。

 for i in range(int(iteration)):
        sheet.range("C7").value = np.random.normal(mean, std)

３．コード実行用にVBAとエクセル更新

VBAのコードも追加していきましょう。

Public Sub runPyMean()
    Runpython ("import hello; hello.mean_simulation()")
End Sub

今回はRunPythonでの実行になりますのでhello.pyにあるmean_simulation関数を()を最後につけるコードによって実行できる状態にします。

F8の実行で、今回コードがうまく動くかを確認することができるのですが、シミュレーション回数が100回になっているので、100回押す必要があるのでちょっと大変です。その際にはF5で全処理が実行できますので、そちらでも試してみてください。

では、次にエクセル上に実行ボタンを作っていきます。作成の仕方は以下の記事でカバーしていますので、こちらを参考に作成します。

なお、ボタンの名称を変える方法としては、Windowsの場合、Ctrlを押しながらボタンをクリックすることができるので、その選択した状態で右クリックをして名称を変更することで実施します。なお、例では”実行”としましたが、ご自身の好きなわかりやすいものに変更いただいて問題ないです。

４．実行

ここまで来たら、ボタンを押して実際の数値が変わるか確認してみましょう！！
どうでしょうか？シミュレーション結果の平均の部分が変わっていっている状態になるかと思います。ちょっと実行に時間がかかる可能性はあります。

５．平均のシミュレーション結果可視化

最後に、可視化部分でPythonコードの整形と、エクセルへの連携を実施していくように整えていきましょう。まずは、Pythonコードの整形をしていきます。こちらをhello.pyの関数として転記していきます。

#可視化用に追加
import matplotlib.pyplot as plt

def mean_simulation(): 
    wb = xw.Book.caller()
    sheet = wb.sheets[0]
    iteration = int(sheet.range("C2").value)
    mean = sheet.range("C3").value
    std = sheet.range("C4").value

    for i in range(iteration):
        sheet.range("C7").value = np.random.normal(mean, std)

    value = np.random.normal(mean, std, iteration)    
    fig = plt.figure(figsize = (10, 3))
    plt.hist(value, bins=100)
    plt.title("Simulation Trial", fontsize = 20)
    plt.xlabel("Mean Value", fontsize = 15)
    plt.ylabel("Frequency", fontsize = 15)
    sheet.pictures.add(fig, name="pic", update=True, 
                       left = sheet.range("B10").left, 
                       top = sheet.range("B10").top)

追加した箇所は後半のvalue = np.random.normal(mean, std, iteration)以下の部分です。これは、可視化を表示するための各種コードで、matplotlibというPythonのライブラリーを使っています。そのため、冒頭の所でその天秤座チーをimportしています。

最終行の写真の追加の部分で今回追加しているのは、leftとtopの部分で、挿入位置を指定する方法を使っています。B10のセルの左側と上部を目掛けて図の挿入をすることで、ほしい箇所に図を挿入することを実現していることが今回のポイントです。以下のエクセルの矢印が交差するB10セルの位置に図を連携しています。

６．シュミレーション結果を見てみよう

では、最後にシュミレーション回数を1000回にしたときの平均がどう動くかを見てきたいと思います。

実行回数を1000回にして実行ボタンを押して実行をしてみます。

すると以下のようなシミュレーション結果が出できます。

平均を０にして、標準偏差を１にして1000回の平均を正規分布で値を発生させているため、０に値がより、その両脇で-1から1に値が集まっている状況が観察されている結果が出てきています。

このようにシミュレーションを行うことによるポイントは、-1より小さい値や、1より大きい値が一定程度出すことができることです。これによってリスク評価に役立てることができる点がメリットです。単純に確定値を１点だけ考えるのではなく、あるときには-1を大きく下回り、-2になることもあり、その逆もあるというブレのリスクを想定し、各種行動を実施していくことが重要になってきます。その線を入れてみましょう。具体的には平均値とMedianを入れることで、シミュレーション結果でその両者がずれていることを確認します。

def mean_simulation(): 
    wb = xw.Book.caller()
    sheet = wb.sheets[0]
    iteration = int(sheet.range("C2").value)
    mean = sheet.range("C3").value
    std = sheet.range("C4").value

    for i in range(iteration):
        sheet.range("C7").value = np.random.normal(mean, std)

    value = np.random.normal(mean, std, iteration)    
    fig = plt.figure(figsize = (10, 5))
    plt.hist(value, bins=100)
    plt.title("Simulation Trial", fontsize = 20)
    plt.xlabel("Mean Value", fontsize = 15)
    plt.ylabel("Frequency", fontsize = 15)
    plt.axvline(x=np.mean(value), ymin=0, ymax=50, ls='-')
    plt.axvline(x=np.median(value), ymin=0, ymax=50, ls='--')
    sheet.pictures.add(fig, name="pic", update=True, 
                       left = sheet.range("B10").left, 
                       top = sheet.range("B10").top)

今回ご紹介する機能は以上となります。

今回、ファイルを以下の有料エリアではアップロードしておきますので、ご興味ある方はご確認ください。