記事の目的
Excel－Python連携モジュールxlwingsを活用して、ExcelがもつGUIとしての利点を生かしつつ、Excelが苦手な処理をPythonに実行させるための1パッケージなツールを試作紹介する。学術研究やものづくりの現場などで便利な、ローカルなデータ解析ツールのひな型をイメージした。
試作するツールの概要
以下の機能を持つフーリエ解析ツールを試作する。
- CSVで保存された時系列データのフーリエ変換を実施する。
- プログラムはExcelシート上のボタンから実行される。
- Excelシート上でフーリエ変換の条件設定する。
- 結果をPythonのMatplotlibで描画が可能。
- 計算結果の数値、グラフ画像がExcelシートに出力される。
動作はこんな感じ。条件パラメータ設定・計算結果がすべて一つのExcelブックでクローズする。計算結果をそのままExcel側で後解析することも可能。
事前準備
使用環境
- Windows10(64bit OS)を前提。
- Excelがインストールされていること、マクロが使えること。
(無料のExcelWeb版ではマクロ・VBAが使用できない) - PythonディストリビューションはWinPythonを想定。
(筆者はWinpython64-3.8.6.0.exeを使用。Anacondaでもほとんど同様) - エディタはSpyderを使用。(これは何でもよい)
WinPythonは、オープンソースで商用利用可能な科学技術系Pythonディストリビューションの一つである。導入は以下の公式サイトから Winpython64-X.X.X.X.exe(Xはバージョン)をダウンロードして好みの場所(例えばCドライブ直下など)でダブルクリックして展開すればとりあえずOK。
使用ライブラリ
pandas, numpy, scipy, sysなどの定番モジュールに加えて、ExcelとPythonの橋渡しを行うモジュールxlwingsを使用する。これらは最近のWinPythonやAnacondaにはすでに入っているため、pip等で新たにインストールすることは不要。(社内イントラネットなどのローカル環境だと非常にありがたい話。)
xlwingsでできること
xlwingsでできることは以下の３つに大別される。
(1)Python側からのExcelの制御・データ入出力
(VBAでできることの簡単なことはほとんどできる(半分所感))
(2)Excel(VBA)からのPython関数の実行
(3)Excel内で使用できるPython定義関数の作成
(いわゆるUDF(User Defined Function))
今回は(1)、(2)の機能を使用することになる。
このxlwingsだが、ここ数年日々アップデートされるとともにドキュメントの日本語化も進んできており期待大。慣れてくるとPython APIのページを頻繁に参照するようになる。リンクを記載しておく。
なお、ExcelとPythonを連携するための手段としてはxwingsのほかにopenpyxlがあるが、筆者はopenpyxlの使用経験なし。以下の記事によると、xlwingsのほうが機能は充実してそう。
(読み飛ばしOK)Add-inよりもVBAモジュールを選択
xlwingsのようなExcel連携ソフト(筆者の知るところだと他にRefpropとか)には、以下の2つの方法がある。
- ExcelのAdd-inとして機能を取り込む
- WookBookのVBAモジュールへの機能追加をする
(xlwingsドキュメントでは「standaloneオプション」として控えめに記載)
xlwingsドキュメントではAdd-inをベースとした書き方がなされているが、筆者はVBAモジュール追加で対応することにする。理由は以下の２つ
- Add-inだと、xlwingsを使わない時でもExcelにxlwingsタブが表示されてしまう。
- VBAモジュールのほうが詳細なデバッグができる。
プログラム作成
作成手順
作成手順の概要を以下に整理する。
(1)Excel-Pythonを連携するためのマクロ有効ブック(.xlsm)と開発用スクリプト(.py)を作る。
(2)Python側でフーリエ解析の処理プログラムを準備する。
(3)Excelとの入出力インタフェースを作成する。
(4)作ったPythonスクリプトを実行関数化する(メイン関数化)。
(5)Pythonのメイン関数をVBAからコールするための記述をVBAに記述する。
(6)Excel側のGUIを整理する。
以下、順に説明する。
１．xlsm, pyのセットを作る
１．１．xlsm, pyのセットの作り方
「WinPython Command Prompt.exe」(AnacondaならAnaconda Promptでしたっけ？)を開き、cd (ファイルパス)で作業したいフォルダまで移動ののち、「xlwings quickstart (任意のプロジェクト名) --standalone」を入力する。ここでは、たとえば「フーリエ解析ツール」という名称にする。
tree /f で確認すると、同名フォルダに同名/拡張子違いのファイルが作成されていることが分かる。
C:\WPy64-3860\scripts>cd C:\Users\thash\OneDrive\デスクトップ\test_xlwings
C:\Users\thash\OneDrive\デスクトップ\test_xlwings>xlwings quickstart フーリエ解析ツール --standalone
Duplicate key in file WindowsPath('C:/WPy64-3860/python-3.8.6.amd64/lib/site-packages/matplotlib/mpl-data/matplotlibrc'), line 249 ('font.family : MS Gothic')
xlwings version: 0.20.8
C:\Users\thash\OneDrive\デスクトップ\test_xlwings>tree /f
フォルダー パスの一覧: ボリューム Windows7_OS
ボリューム シリアル番号は 30D730C3 7873:634E です
C:.
└─フーリエ解析ツール
フーリエ解析ツール.py
フーリエ解析ツール.xlsm
１．２．これで何が生成されたのか？
.xlsmのほうを開けば、Excelを普通に新規作成した時と比べて以下の違いに気づくはずだ。
- シート「_xlwings.conf」が存在する
→ ExcelからPythonをコールするときのパス設定等に使用、後でシート名の"_"を落とす。 - alt+F11でVBAエディタを開くと、すでに以下のモジュールが存在している。
- モジュール「xlwings」が存在
→ ExcelからPythonをコールする関数RunPythonなどが入っている - モジュール「Module 1」が存在、中にテスト関数(SampleCall) が入っている
→ ExcelからPythonをコールするテスト関数が入っている。
- モジュール「xlwings」が存在
これらは後々編集していくが、ここでは詳しく述べず先を急ごう。
なお、生成された.pyにも色々書かれているが、筆者はこの内容はガン無視して進める。
２．フーリエ解析の処理プログラムの準備
Python側でフーリエ解析の処理プログラムを準備する。ここは以下記事の前任者の美しいコードを丸ごと拝借させていただくこととする。（以下の記事を見に行かなくてもツール作成は進められるようになっているが、解析プログラムの細かい説明はな割愛する）
２．１．テスト信号の準備(xw.viewの活用)
スクリプト本体の作成に入る前に、テスト信号ファイルを準備しよう。pandasのデータフレームをxlwingsモジュールの関数viewに渡すだけで、新たなExcelブックが開きデータフレームの値が自動で張り付けられる。
例えば、
import numpy as np
import matplotlib.pyplot as plt
import pandas as pd
import xlwings as xw # xlwingsモジュールのインポート
# 波形データの作成
fs = 10e3
N = 1e5
amp = 2 * np.sqrt(2)
noise_power = 0.01 * fs / 2
time = np.arange(N) / float(fs)
mod = 500*np.cos(2*np.pi*0.25*time)
carrier = amp * np.sin(2*np.pi*3e3*time + mod)
noise = np.random.normal(scale=np.sqrt(noise_power), size=time.shape)
noise *= np.exp(-time/5)
x = carrier + noise
# データフレームとして整形
df_x = pd.DataFrame(x, index=time, columns=['sig[-]'])
df_x.index.name = 'time[s]'
## 新たなExcelに貼り付け
xw.view(df_x)
を実行すれば、データ張り付けされたExcelブックがポップアップする。これだけでもかなり便利。（グラフは後で手動追加している。）
ディレクトリ「フーリエ解析ツール」の中にデータインプット用ディレクトリ「input」を作成して、その中にこのデータをそのまま「sig1.csv」などの名前でUTF-8指定で保存しよう。
２．２．解析プログラムの作成
１．のコマンドで生成されたファイル「フーリエ解析ツール.py」にフーリエ解析本体プログラムを書いていく。
開発途中で「.pyファイル」の名前を変えていきたくなるところだが、現状はどんどん変えていってもよい。理由は後で書くとして、最終的なツールに仕上げるときにXXX.xlsmとXXX.pyのXXXが同じになっていればうまくExcelからPythonをコールできるようになる。
まず、.pyのスクリプトは全部消して、以下のような実行したいプログラムを書く。
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
from scipy import signal
import xlwings as xw # xlwingsのインポート
if __name__ == "__main__":
# 設定パラメータ
inp_file = "input\sig1.csv"
nperseg = 256 # 切り取り区間の点数
nfft = 256 # FFT解析点数(零埋め込み)
df_inp = pd.read_csv(inp_file) # DFとして取得
# 単独のnumpy配列に展開
t = df_inp.index.values # 時間[s]
x = df_inp['sig[-]'] # 信号
fs = 10e3 # サンプリング周波数[Hz]
#スペクトログラム分析の実施
f, t, Sxx = signal.spectrogram(x, fs, nperseg=nperseg, nfft=nfft)
#図の描画
fig_1, ax_1 = plt.subplots() # グラフ・軸オブジェクトを作
plt.pcolormesh(t, f, 10*np.log(Sxx)) #intensityを修正
ax_1.set_ylabel('Frequency [Hz]')
ax_1.set_xlabel('Time [sec]')
cbar = plt.colorbar() #カラーバー表示のため追加
cbar.ax.set_ylabel("Intensity [dB]") #カラーバーの名称表示のため追加
plt.show()
このとき出力は以下。これはExcel単体ではなかなか解析・表現できない。
warningみたいなのが出ているが、とりあえず無視する。。。
C:\Users\thash\OneDrive\デスクトップ\test_xlwings\フーリエ解析ツール\フーリエ解析ツール.py:42:
MatplotlibDeprecationWarning: shading='flat' when X and Y have the same
dimensions as C is deprecated since 3.3. Either specify the corners of the
quadrilaterals with X and Y, or pass shading='auto', 'nearest' or 'gouraud',
or set rcParams['pcolor.shading'].
This will become an error two minor releases later.
３．Excel入出力インタフェースの作成
３．１．Excelシートの準備
Excel上で入力パラメータ表を下記画像のように作成する。Pythonで取り込む場所を認識させるために、表の左上を「_setTabTop」、右下を「_setTabEnd」などとセル名を定義する。（セル名の定義は、「数式タブ」 → 「名前の管理」→ 「新規作成」から。）このとき、範囲をブックではなく、現在のシートを選択しておくと、のちにシートコピーしてもセル名が引き継がれ、ツールが"壊れない"。
更に、上記画像のように、右の空白のところに、データの出力先として「_outTabTop」なるセル名を定義しておく。これでExcel側はいったん完了。
３．２．Excelからのデータ取得
Excelシートから設定パラメータを取得するために、上記の「フーリエ解析ツール_開発中_1.py」に対して、設定パラメータの部分をさらに書き換えよう。xlwingsには、VBAライクなレンジ取得APIが用意されている。とにかく実装例から。
...
if __name__ == "__main__":
#%% 設定パラメータ
sht_1 = xw.sheets.active # Excelでアクティブなシートをオブジェクトとして取得
# 指定の範囲をpandasのSeriesとして取得する
ser_set = sht_1.range("_setTabTop:_setTabEnd").options(pd.Series).value
# 変数展開
inp_file = "input//" + str(ser_set['inp_file']) # "input\sig1.csv" # 入力ファイル名
nperseg = int(ser_set['nperseg']) # 256 # 切り取り区間の点数
nfft = int(ser_set['nfft']) # 256 # FFT解析点数(零埋め込み)
df_inp = pd.read_csv(inp_file) # DFとして取得
...
ポイントを整理する。
- 「xw.sheets.active」で、Excelのアクティブなシート(最後に触ったシート)のオブジェクトを取得。
- 「(シートオブジェクト).range("セル1:セル2").value」で指定のセル範囲の値が取れる。
(range("A1")でもrange("A1:B2")でもOK) - さらに、「.options(pd.Series)」を挟めばセルからSeriesで値が取れる。もちろん「.options(np.array)」や「.options(pd.DataFrame)」もできる。（めちゃ便利！）
これで、Excelシート上の表をpandsのSeriesで、辞書型みたいな感じで取得できた。
中身はこんな感じ。
In [24]: ser_set
Out[24]:
変数
inp_file sig1.csv
nperseg 256
nfft 256
NaN None
Name: 値, dtype: object
このとき、Seriesの各値はいろんな型がobject型としてばらばらに入っているので、int,float,str関数で所望の型にそろえながら変数展開しておく。
３．３．Excelへのデータ出力
さらに、解析結果(値)をデータフレームに整形してExcelシートに出力しよう。実装例を示す。結局Excel出力関係で必要なのはたったの2行。
...
#スペクトログラム分析の実施
f, t, Sxx = signal.spectrogram(x, fs, nperseg=nperseg, nfft=nfft)
# 解析結果をデータフレームに整形(縦軸が時間、横軸が周波数)
df_out = pd.DataFrame(10*np.log(Sxx).T, index=t, columns=f)
# シートの値出力先の値をいったん削除する
sht_1.range('_outTabTop').expand().clear_contents()
# シートに出力する
sht_1.range('_outTabTop').value = df_out
...
結果はこんな感じ。周波数と時間のラベルは後から手動追記した。
ポイントを整理する。
- 「(シートオブジェクト).range('セル名').value = (貼り付けたいもの)」で、シートの指定のセルが左上になるように値貼り付けができる。貼り付けたいものは、list、np.array、pd.Series、pd.DataFrameがそのまま使える。（めちゃ便利！）
- 値張り付けする前に出力先をいったん綺麗にするために、(シート).range('XXX').clear_contents()を使用する。
- さらにrange('XXX').expand()を使えば、セルXXXを選択してshift + cntl + ↓→ と同じ範囲選択になる。
３．３．Excelへの画像出力
肝心のスペクトログラムをシート上に画像グラフ出力するところを実装する。これは結局「sht_1.pictures.add(...)」を追加するだけ。
...
#図の描画
fig_1, ax_1 = plt.subplots() # グラフ・軸オブジェクトを作
plt.pcolormesh(t, f, 10*np.log(Sxx)) #intensityを修正
ax_1.set_ylabel('Frequency [Hz]')
ax_1.set_xlabel('Time [sec]')
cbar = plt.colorbar() #カラーバー表示のため追加
cbar.ax.set_ylabel("Intensity [dB]") #カラーバーの名称表示のため追加
plt.show()
# グラフ名「_fig_1」で図を張り付け(2回目以降は更新される)
# plt.show()の後に書くこと！！
sht_1.pictures.add(fig_1, update=True, name='_fig_1')
...
実行結果が以下。初めはグラフが指定の名前の画像オブジェクトとしてシートの左上に作成される。シート上の好きな位置・大きさに動かしておけば、２回目以降は位置が変わらずその図が更新(update=True)される。（オプション変数使えば、細かな出力位置や画像サイズも指定できたはず）
４． 解析プログラムのメイン関数化
最終的には関数にするにしても、これまでは「if name == "main":」の下でコード開発を実施していた。この方が、関数のように統合開発環境やipythonの%runなどで実行したときの変数が解放されないためデバッグに有利だったからである。一方、Excelからコールする場合には、これを関数化する必要がある。
と言っても、たいそうなことではなく、単に「if name == "main":」の上側にコード全体を持って行って、「def XXXX():」を付ければよい。最終的な.pyの実装結果を示す。
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
from scipy import signal
import xlwings as xw
import sys, os, warnings
warnings.simplefilter('ignore') # warnigsを無視する
# 実行しているスクリプトのディレクトリに移動
os.chdir(os.path.dirname(os.path.abspath(__file__)))
def main_STFT():
"""
短時間フーリエ変換を実行しExcelシート出力する関数
"""
print("!!%s 開始!!" % sys._getframe().f_code.co_name)
#%% 設定パラメータ
sht_1 = xw.sheets.active # Excelでアクティブなシートをオブジェクトとして取得
# 指定の範囲をpandasのSeriesとして取得する
ser_set = sht_1.range("_setTabTop:_setTabEnd")\
.options(pd.Series).value
print("\n## 設定パラメータ ##")
print(ser_set)
print("")
# 変数展開
inp_file = "input//" + str(ser_set['inp_file']) # "input\sig1.csv" # 入力ファイル名
nperseg = int(ser_set['nperseg']) # 256 # 切り取り区間の点数
nfft = int(ser_set['nfft']) # 256 # FFT解析点数(零埋め込み)
df_inp = pd.read_csv(inp_file) # DFとして取得
# 単独のnumpy配列に展開
t = df_inp.index.values # 時間[s]
x = df_inp['sig[-]'] # 信号
fs = 10e3 # サンプリング周波数[Hz]
#スペクトログラム分析の実施
print("解析実行中...")
f, t, Sxx = signal.spectrogram(x, fs, nperseg=nperseg, nfft=nfft)
# 解析結果をデータフレームに整形(縦軸が時間、横軸が周波数)
df_out = pd.DataFrame(10*np.log(Sxx).T, index=t, columns=f)
# シートの値出力先の値をいったん削除する
sht_1.range('_outTabTop').expand().clear_contents()
# シートに出力する
sht_1.range('_outTabTop').value = df_out
#図の描画
print("グラフ描画中...")
fig_1, ax_1 = plt.subplots() # グラフ・軸オブジェクトを作
plt.pcolormesh(t, f, 10*np.log(Sxx)) #intensityを修正
ax_1.set_ylabel('Frequency [Hz]')
ax_1.set_xlabel('Time [sec]')
ax_1.set_title("%s | fig_1" % sht_1.name)
cbar = plt.colorbar() #カラーバー表示のため追加
cbar.ax.set_ylabel("Intensity [dB]") #カラーバーの名称表示のため追加
plt.show()
# グラフ名「_fig_1」で図を張り付け(2回目以降は更新される)
# plt.show()の後に書くこと！！
sht_1.pictures.add(fig_1, update=True, name='_fig_1')
print("!!%s 終了!!" % sys._getframe().f_code.co_name)
if __name__ == "__main__":
main_STFT()
実行結果は以下。
runfile('C:/Users/thash/OneDrive/デスクトップ/test_xlwings/フーリエ解析ツール/フーリエ解析ツール.py', wdir='C:/Users/thash/OneDrive/デスクトップ/test_xlwings/フーリエ解析ツール')
!!main_STFT 開始!!
## 設定パラメータ ##
変数
inp_file sig1.csv
nperseg 256
nfft 256
NaN None
Name: 値, dtype: object
解析実行中...
グラフ描画中...
!!main_STFT 終了!!
いくつか注意点とTips(半分筆者の趣味)を述べる。
- (必須)関数群の上に「os.chdir(os.path.dirname(os.path.abspath(__file__)))」入れること。
これは実行ディレクトリをスクリプトのある場所に移す操作である。（Excelからpythonを呼び出したときの最初の実行ディレクトリがHomeになってしまうため。これでは相対パスで指定しようとしているinputフォルダが見つけられないとか、(今回はないが)pythonでファイル出力したときに、変なところに出てきてしまう。) - (必須)「plt.show()」を「(シート).pictures.add(XXX)」の後に持ってくること。
(後で出てくる「グラフポップアップ」の結果が出力画像に反映されるようにするため。) - (おすすめ)「warnings.simplefilter('ignore')」で警告をコンソール上で非表示にできる。
- (おすすめ)「print("!!%s 開始!!" % sys._getframe().f_code.co_name)」を入れておけば、どの関数が実行されているかコンソール上に表示できる。
- (おすすめ)適宜printして、コンソール上で状況説明する。
- (おすすめ)"sht_1.name"で実行シート名が取得できる。これをグラフタイトルに表示しておくと、解析条件をシート名に書くとグラフにも出るなど何かと便利。(「ax_1.set_title("%s | fig_1" % sht_1.name)」のところ)
(日本語を使いたい場合はmatplotlibのグラフを日本語化設定する必要がある、これは他記事を参照ください)
５．Pythonをコールするための設定(最後の仕上げ)
５．１．PythonをコールのためのVBA関数
ここでようやくExcelの「フーリエ解析ツール.xlsm」に戻って、alt+F11よりVBAエディタを開く。Module1に以下を追加する。(サンプル関数SampleCallを参考に作った)
Sub main_STFT()
mymodule = Left(ThisWorkbook.Name, (InStrRev(ThisWorkbook.Name, ".", -1, vbTextCompare) - 1))
RunPython ("import " & mymodule & ";" & mymodule & ".main_STFT()")
MsgBox ("!! main_STFT 実行完了!!")
End Sub
このプログラムのポイントを整理する。
- 「mymodule」に「xxx.xlsm」の「xxx」だけが文字列取得される。
- 「RunPython((文字列))」により、コマンドプロンプト上で「python (文字列)」としたのと同じ状況になる。(関数RunPythonはモジュールxlwings内に定義されている。)
→ つまり、pythonで「import ファイル名; ファイル名.関数名()」を実行させているだけ。
(ここにXXX.pyとXXX.xlsmのXXXを一致させておく理由がある。XXX.pyとXXX.xlsmは一心同体なのだ) - (おすすめ)VBAの関数名をPythonでコールする関数名を同じ名前にしておくと、悩まなくて楽。(今回は「main_STFT」)
- (おすすめ)msgboxで実行完了がお知らせされるようにしておくと便利。
５．２．PythonをVBAからコールするための初期設定
ただし、このままではパス設定など実行条件ができていないため、シート「_xlwings.conf」に戻って以下を実施する。
- シート「_xlwings.conf」の名前を「_」を落とした「xlwings.conf」に変更。(シートの設定の読み込みが有効化される)
- そのシートのセル「interpreter_Win」の右のセルに、使っている環境のpython.exeのフルパスを入力する。（(Anacondaの場合は「Conda Path」か「Conda Env」あたりを編集する必要があったはず。以下のドキュメント記載を参考のこと。)
- (おすすめ)「Show Console」のFALSEをTRUEに書き換える。(コンソールが出るようになってprintしているものが表示される。)
筆者のWinPython環境だとこんな感じ。
この状態で元の解析用のシートに戻って(アクティブシートを元に戻す意)、alt+F8から上で作ったmain_STFTを実行する。(マクロが有効化されていないときは、WorkBookを保存して開きなおすこと。)
コンソールが出てグラフがポップアップされたら成功。グラフを拡大縮小などして閉じれば、その状態でシートに画像出力される。
６．Excel側でGUI等体裁の整理(完成へ！)
最後の仕上げとして、VBA関数をコールするボタンを作成する。
タブ「開発」(出てない人は検索して出し方を調べてください)→ 「挿入」→「ボタンマーク」からドラッグ&ドロップし、今作った関数を選択しOKをクリック。
これで、Pythonで書いた解析プログラムがボタンから実行できるようになった（お疲れさまでした！！！！）
できたボタンの右クリック→「テキストの編集」から、ボタンを好きな名前に変える。
あとは適当に、全体の見栄えをよくして完成。
終わりに(感想)
アプリケーションのGUIのトレンドはWeb系の波に押されて久しいが、ものづくりや学術研究の現場におけるデータ処理・解析ツールは依然としてExcelやVBAがデファクトスタンダートである。なぜなら、誰でも気軽に編集でき、どのPCにもだいたい入っており、ローカルなネットワーク下で十分だからである。よって、Excelを、より高度な処理が簡単に実装できるPythonと連携しない手はないと筆者は考える。それの最適解の一つを今回は示したつもりだ。
かなりの文量になってしまったが、今回の記事は、長年xlwingsに付き合ってきた筆者が思う、xlwingsの素晴らしさや、それを使いこなす上で知っておきたい必要十分なTipsが盛り込まれた保存版とも言える出来になったと思う。
(これ関係で、いつかなんか仕事来ないかな～)