Go to Qiita Advent Calendar 2024 Top
LoginSignup
42
87

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@k_maki

VBAユーザーのためのPython入門 ～xlwingsでExcelからPythonを呼び出す～

Last updated at Posted at 2020-04-28

脱Excel VBAを目指して、xlwingsを使ってExcelからPythonを呼び出す方法を説明します。

Note: xlwings自体はmacOSにも対応していますが、本記事投稿時点ではUDF（ユーザー定義関数）を使えるのはWindowsのみとなっています。

1. はじめに

1.1. 対象読者

Excel VBAでいろいろとEUCツールを作っているけど、

  • もっと効率的にツールを作りたい
  • プログラミング技術を高めたい
  • VBAのしょぼさにうんざりしている
  • C++や.NETでdllを作るのは面倒or難しそう
  • Pythonで作ったプログラムをユーザーに配布したい

といった人を対象にしています。VBAしか使ったことがないと、Pythonの説明でよくあるコマンドラインでの実行や、pyファイルの直接実行に慣れないかもしれません。ですが、Pythonのライブラリーxlwingsを使えば、VBAライクにPythonを使い始めることができます。

まずはPythonを使ってみることを目指してやってみましょう。

1.2 Pythonって何？

VBAと同じくプログラミング言語です。VBAとの違いを挙げれば

  • 文法がシンプル: 初心者向けプログラミング言語としても使われます。
  • 現代的な機能を備えている: 配列ですら使い勝手が全然違います。VBAは、1999年のVBA6から20年以上進化していないので仕方ないのですが。
  • ライブラリーが豊富: 高度な科学計算、データ分析、AI開発など、できることが大幅に広がります。

等があります。

1.3 xlwingsって何？

ExcelからPythonを呼び出したり、PythonからExcelを操ったりするパッケージです。Excelを扱うパッケージは他にもopenpyxlなどがありますが、

  • 書き方がVBAに似ている
  • 既存のマクロを有効活用できる

という点が、VBAに嫌気がさしているけど使い続けなければならないVBAユーザーのステップアップに向いていると思います。
xlwingsを使ってExcelからPythonを呼び出す機能には、Run mainやRunPythonもありますが、UDFはそれらを包含しているので、UDFのみを説明します。

2. 準備

VBAならAlt+F11ですぐに始められますが、Pythonだといろいろと準備が必要になります。

2.1. Pythonのインストール

Pythonディストリビューション（Python本体+外部ライブラリーのセット）の定番Anacondaをインストールします。ここからインストーラーをダウンロードし、管理者権限でインストールします。やり方はググれば山ほど出てきます。

以下の画面では2つともチェックを入れます。1つめのチェックは環境変数PathにAnacondaのインストール先を登録するもので、後で説明するコマンドラインでの作業で必要になります。
Anaconda インストール画面.png

チェックを入れ忘れた場合には、以下をPowershellで実行すればユーザー環境変数Pathにパスを設定できます（C:\ProgramData\Anaconda3はデフォルトのインストール先なので適宜変更してください）:

PowerShell
> $newSystemPath = [System.Environment]::GetEnvironmentVariable("Path", "User")
> $newSystemPath += ";C:\ProgramData\Anaconda3"
> $newSystemPath += ";C:\ProgramData\Anaconda3\Scripts"
> $newSystemPath += ";C:\ProgramData\Anaconda3\Library\bin"
> [System.Environment]::SetEnvironmentVariable("Path", $newSystemPath, "User")

なお、ユーザー環境変数ではなく、システム環境変数のPathにパスを設定する場合は、PowerShellを管理者権限で起動し、1行目と最後の行の"User"を"Machine"として実行します。

2.2. xlwingsのインストール

Anacondaなら初めから入っていますので作業は不要です。以下のコマンドで確認することもできます:

PowerShell
> pip show xlwings
Name: xlwings
Version: 0.22.2
Summary: Make Excel fly: Interact with Excel from Python and vice versa.
Home-page: https://www.xlwings.org
Author: Zoomer Analytics LLC
Author-email: felix.zumstein@zoomeranalytics.com
License: BSD 3-clause
Location: c:\programdata\anaconda3\lib\site-packages
Requires: pywin32
Required-by:
>

Anaconda以外でPythonをインストールした場合には、pipでインストールします:

PowerShell
> pip install xlwings

2.3 Excelの設定変更

ファイル > オプション > トラスト センター > トラスト センターの設定 > マクロの設定を開きます（Exce 2016以前はトラスト センターをセキュリティ センターで読み替える）。マクロの設定でマクロを有効化し、VBA プロジェクト オブジェクト モデルへのアクセスを信頼するに✓を入れます。
マクロの設定.png

2.4 アドインのインストールと設定

2.4.1 アドインのインストール

コマンド ラインか手動でインストールします。アドインをインストールすれば、Excelのリボンにxlwingsタブが追加されます。
xlwingsのリボン.png

コマンド ライン

以下をPowerShellで実行します。

PowerShell
xlwings addin install
xlwings config create

2行目はアドインのリボンの設定（後述）を作成し、Interpreterに現在実行しているxlwingsコマンドの環境（C:\ProgramData\Anaconda3\python.exe、仮想環境の場合は%UserProfile%\.venvs\仮想環境名\Scripts\python.exe等）を設定します。
xlwingsとアドインのバージョンは一致していなければならないため、xlwingsをバージョン アップした際は、xlwings addin updateでアドインもアップデートしてください。

手動

Excel上でファイル > オプション > アドインの下段にあるプルダウンでExcel アドインを選択し、設定を押します。参照ボタンを押して現れるダイアログボックス上で、xlwings.xlamを選択します。xlwings.xlamは、以下のいずれかを使用。

  • xlwingsのGithubのリリースページから、インストールしているバージョンのxlwings.xlamをダウンロード
  • (Anacondaインストール先)\pkgs\xlwings-0.16.0-py37_0\Lib\site-packages\xlwings\addin にあるxlwings.xlam

（21/3 削除。この方法でアドインをインストールした場合、他のユーザーとブックを配布した際に不具合が起きるため。）

2.4.2 アドインの設定（グローバル）

ひとまずそのままで大丈夫ですので、読み飛ばしてください。
説明は以下のとおり。そのままで動かない場合には、必要に応じて設定してください。ここでの設定はユーザー単位で適用されます。詳細は公式ドキュメント。
xlwingsのリボン.png

  • Interpreter: python.exeのパスを設定（例: C:\ProgramData\Anaconda3\python.exe ）。仮想環境を使用している場合は、仮想環境のpython.exeのパスを設定します（例：%UserProfile%\.venvs\仮想環境名\Scripts\python.exe）。Windowsでconda環境を使う場合は空白にします。また、何も入力していなければ、環境変数で設定しているパスから探しに行きます。
  • Conda Base: Anacondaのインストール先（デフォルトはC:\ProgramData\Anaconda3）。
  • Conda Env: xlwingsから呼び出すcondaの仮想環境名（base、myenv等）。
  • Import Functions: Pythonの関数を、Excelファイル内のxlwings_udfsモジュールに取り込む際に押します。関数名や引数を変更した場合には再度押す必要があります。
  • UDF Modules: 呼び出し先のPythonファイル名（拡張子.pyを除く）。複数を設定する場合は";"で区切ります。何も入力しなければ、Excel スプレッドシートと同じディレクトリにある同じ名前のファイル（拡張子は .py）をインポートします。この項目は後述のワークブックで設定した方が使い勝手が良いです。
  • Debug UDFs: 後述のデバッグを行う際にチェックを入れます。
  • Restart UDF Server: UDFを実行するサーバーをシャットダウンします。サーバーは次のUDF実行時に再起動します。再起動が必要な場合として、Pythonファイルのインポート先の変更を反映したいときがあります。直接呼び出しているPythonファイルの変更（関数名と引数の変更を除く）は自動敵に反映されますが、そのファイルでインポートしているモジュールの変更はそうならないためです。

なお、ワークブックの設定がある場合にはそちらが優先されます。

2.4.3 アドインの設定（ワークブック）

ここもひとまず読み飛ばしてもらってかまいません。
xlwings.confシート.png
ワークブック内のxlwings.confシートで、ワークブック単位でアドインを設定できます。xlwings.confシートは、後述のquickstartで作成されるブックに_xlwings.confシートがありますので、シートごとコピーして名前を変更します。
A列の項目に対応する値をB列に入力して設定します。不要な設定行は削除可能で、設定行があればワークブックの、なければリボンの設定が反映されます。UDF Modulesのみ設定して、それ以外の行は消すのが実用的な使い方でしょうか。

2.5. テキスト エディター

後述のデバッグで必要となるため、デバッグ可能なテキスト エディターを用意します。Visual Studio Codeをお勧めします。これのインストール方法もググれば山ほどでてきます。Python拡張機能もインストールしておきます。

3. 使ってみよう

以下の3つのケースで説明します。順番に見ていくことで、UDFの使い方に慣れていきましょう。

3.1. UDFとは

UDFはユーザー定義関数（User Defined Function）の略で、Excelの標準モジュール内で定義されたVBA関数のことです。VBA関数なのでVBA内はもちろん、あまり知られていないのかもしれませんが、Exelのセル式でも使えます。こちらでわかりやすく説明されています。

以降、xlwingsを使ってPythonの関数をUDF（=VBAの関数）として使っていく方法を説明します。公式ドキュメントはこちら。

3.2. quickstart コマンド

以下のコマンドを実行すると、カレント ディレクトリーにmyprojectフォルダーが、フォルダー内には、Excelファイルmyproject.xlsmとPythonファイルmyproject.pyが作成されます（myprojectはお好きな名前に変更してください。）。

PowerShell
> xlwings quickstart myproject

myproject.pyの内容は次のとおりです（説明のため抜粋・コメント追加）:

myproject.py
import xlwings as xw       #①xlwingsのインポート

@xw.func                   #②デコレーター
def hello(name):           #③関数の宣言
    return "hello {0}".format(name)

コードを簡単に説明すると
① xlwingsをPythonで使えるようにインポートします。as xwで別名xwで使用できるようにします。
② @xw.funcはPythonの関数をUDFとして使えるようにするためのデコレーター（関数に機能を追加するための仕組み。詳しくはこちら）です。現時点でデコレーターの仕組みを理解する必要はありませんが、おまじない的にExcelで使いたい関数には頭に@xw.funcを付けると覚えておいてください。
③ defで関数を宣言します。:以下のインデントされている部分（上記だとreturn ...）が関数の中身です。
となっています。デコレーターだけちょっと取っ付きにくいですが、Pythonのシンプルさは何となくわかるかと思います。

次に、myproject.xlsmを開き、リボンxlwigsのImport Functionsを押して、myproject.pyのhello関数を取り込みます。
myproject_ImportFunctions.png

Alt + F11でVBAエディタを開いてください。取り込みが成功していれば、標準モジュールにxlwings_udfsが追加されています。このモジュール内でPythonのと同じ名前のhello関数が定義されます。
myproject_Import結果.png

hello関数を試してみましょう。任意のセルに=hello("Python")と入力すれば、hello Pythonと返されます。
myproject_hello関数.png

少し改変してみます。myproject.pyを開き、return ...の部分を次のように変更してみます:

myproject.py
@xw.func
def hello(name):
    return "Hello {0}!".format(name)

先ほどのセルを再度計算すると、Hello Python!となります。
myproject_hello関数2.png

今度は関数を追加してみましょう。以下をmyproject.pyに追加します。

myproject.py
@xw.func
def double_sum(x, y):
    return 2 * (x + y)

新しい関数を取り込む場合には、再度Import Functionsを押します。任意のセルに=double_sum(2, 3)と入力して、10が返されれば成功です。簡単ですね。
myproject_hello関数3.png

3.3. 公式のサンプル

先ほどよりも少し複雑な例を公式のサンプルで見ていきましょう。外部ライブラリーとの連携の強力さが少し分かるかと思います。

GitHubからudf.xlsmとudf.pyをダウンロードし、同じフォルダーに保存してudf.xlsmを開きます。
udf.png

いくつか説明してみます。

add_one

@xw.func
@xw.arg('data', ndim=2)
def add_one(data):
    """Adds 1 to every cell in Range"""
    return [[cell + 1 for cell in row] for row in data]

各セルの値に1を足したものを返します。ワークブックでは、セル範囲E11:G12にCtrl + Shift + Enterで入力されており、配列数式となっています。@xw.arg('data', ndim=2)は引数を制御するためのデコレーターで、引数dataが単独のセルや行/列であっても、常に2次元のリスト（配列）として読み込むようにします。[cell + 1 for cell in row]部分はリスト内方表記と呼ばれるもので、簡潔にリスト（配列）を作成することができます。

matrix_mult

@xw.func
@xw.arg('x', np.array, ndim=2)
@xw.arg('y', np.array, ndim=2)
def matrix_mult(x, y):
    """Alternative implementation of Excel's MMULT, requires NumPy"""
    return x.dot(y)

行列の積を返します。ExcelのMMULT関数と同じです。@xw.arg('x', np.array, ndim=2)は、引数xを2次元のNumpy Arrayとして読み込むようにするものです。Numpy Arrayとは、科学計算ライブラリーNumpyで定義されたリスト（配列）で、Numpy Arrayのdot関数を使って行列の積を計算しています。

CORREL2

@xw.func
@xw.arg('x', pd.DataFrame, index=False, header=False)
@xw.ret(index=False, header=False)
def CORREL2(x):
    """Like CORREL, but as array formula for more than 2 data sets"""
    return x.corr()

時系列データ同士の相関行列を返します。@xw.arg('x', pd.DataFrame, index=False, header=False)は、引数xをインデックス、ヘッダー無しのPandas DataFrameとして読み込むようにするものです。Pandas DataFrameとは、データ分析用ライブラリーPandasで定義された2次元の配列のようなもので、Pandas DataFrameのcorr関数で相関行列を計算しています。そして、@xw.ret(index=False, header=False)が、corr関数の計算結果のDataFrameからインデックスとヘッダー除き、値のみにしてExcelに返しています。

同じことをVBAでやろうとすると、forループを書いたりするので長くなりそうですね。また、NumpyやPandasにはこれ以外にも様々な機能があります。これもVBAにはない強みです。

@xw.argと@xw.ret

上記の関数で現れた@xw.argや@xw.retは、コンバーターと呼ばれるもので、ExcelとPython間のデータの型変換などを行います。コンバーターを使ったデータ入出力方法については、こちらをご参照ください。

3.4. 既存のExcelファイルに追加

既存の.xlsmからPythonを呼び出せるようにすることもできます。

既存のExcelファイル「既存.xlsx」から、先ほどのudf.pyを呼び出せるようしてみましょう。
まずはファイルの形式を変更します。F12を押して、形式はマクロ有効ワークブック(*.xlsm)を選択します。

既存.xlsmとudf.pyを同じフォルダーに置きます。既存.xlsmを開き、3.1.で作成したmyproject.シートから、_xlwings.confシートをコピーし、シート名をxlwings.confに変更します。UDF Modulesにudfと入力します。
既存1.png

Alt + F11でVBAのエディターを開き、メニューバーのツール > 参照設定を開き、xlwingsにチェックを入れます。この操作はよく忘れてしましますので気を付けてください。
既存_参照設定.png

リボンでImport Functionsボタンを押します。取り込みが成功すれば、VBAエディタ上で標準モジュールにxlwings_udfsが追加されます。
既存_モジュール追加.png

double_sum関数を試してみましょう。任意のセルに=double_sum(2,3)と入力すれば、10が返されます。
既存_double_sum.png

なお、xlwings.confシートをコピーせずに、Pythonのファイル名を「既存.py」としてもかまいません。ただ、同じPythonファイルを複数のExcelファイルで使ったり、Excelファイルの末尾とかにv2とかを付けてバージョンを分けたりする場合には、xlwings.confシートを使ってモジュールを指定しておくと便利です。

4. デバッグ

UDFは実行だけではなくデバッグもできます。

udf.pyをデバッグしてみましょう。udf.pyの場合は始めから入っていますが、他のコードをデバッグする場合は末尾に以下を追加します。

if __name__ == '__main__':
    xw.serve()

次に、Visual Studio Code上でブレークポイント等を設定します。matrix_mult関数のreturn文に設定してみましょう。
デバッグ1_ブレークポイント.png

エディタでF5を押すと、デバッグの構成を選ぶプルダウンが現れるので、「Python File」を選択すれば、デバッグが実行されます。
デバッグ2_実行.png

なお、左上の歯車マークをクリックして、同じく「Python File」を選択すれば、デバッグ構成のファイルが作成されるので、以降はF5を押すだけでデバッグが実行されます。（launch.jsonのタブはそのまま閉じていい）
デバッグ3_歯車.png

Excelの画面に戻って、リボンのDebug UDFsにチェックを入れます（xlwings.confシートに'Debug UDFs'の行があれば、値Trueを設定）。
デバッグ4_リボン.png

シートを再計算（Alt + Shift + F9）すると、先ほどのブレークポイントで実行が止まります。左上の変数で、変数の中身の確認ができます。
デバッグ5_実行.png

小技ですが、デバッグ コンソールから変数を直列化すれば、JupyterNotebookとかで中身を確認したりできて便利です。例えば、Pandas DataFrameの変数dfを、デバッグ コンソールでdf.to_pickle('df.pickle')で直列化し、JupyterNotebookのセルでdf = pd.read_pickle('df.pickle')で読み込めば、デバッグ中の変数をJupyteNotebook上でいろいろ見ることができます。

5. おわりに

今回の記事では、xlwingsを使うところまでを説明しました。UDFの注意点ですが、Excel関数のように大量のセルで使用してはいけません。セルごとにUDFサーバーとやりとりする上、マルチスレッド処理が行えないため、遅いです。

では、どう使うのかというと、ボタンに登録します。この時、Python側へのデータの入力は、UDFの引数ではなくPythonの関数の内部で行い、処理結果をExcelのテーブルやCSVファイルに出力します。

この辺の話を含めた、発展的な内容は以下で投稿しています（今後順次投稿するつもりですので、お楽しみに）。

参考

42
87
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
42
87

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?