ユーザー定義関数 (UDFs)#

このチュートリアルではユーザー定義関数(User Defined Functions, UDFs)の作成方法を学びます。

注釈

  • UDFが使えるのは、現在Windowsのみです。

  • 引数と戻り値の制御方法の詳細については コンバーターおよびオプション をご覧ください。

  • デコレーターとそのオプションについての包括的な概要は、対応するAPIドキュメントをご覧ください: UDF デコレーター 。

Excelの準備（1回だけ）#

  1. ファイル > オプション > トラスト センター > トラスト センターの設定 > マクロの設定 にある VBA プロジェクト オブジェクト モデルへのアクセスを信頼する を有効化します。この作業は一度だけです。また、関数のインポートでのみ必要になります。つまり、エンド ユーザー（訳注: 開発者が作成したUDFを利用するユーザー）には関係ありません。

  2. コマンド プロンプトからアドインをインストールします: xlwings addin install (see アドインおよび設定)。

ワークブックの準備#

コマンドプロンプトから xlwings quickstart myproject を実行すれば、簡単に新しいプロジェクトを始められます (see コマンド ライン クライアント(CLI))。この方法で作成したワークブックは、xlwingsへの参照が設定されています。

シンプルなUDF#

quickstart で作成した場合、デフォルトの設定では、Pythonのソース ファイルは次のようになっていなければなりません:

  • Excel ファイルと同じディレクトリ

  • Excelファイルと同じ名前で、拡張子が .xlsm ではなく .py

デフォルトの設定以外では、xlwingsのリボンの UDF Modules で特定のモジュールを指定することもできます。

  • The Image below shows the correct input for the "UDF Modules" field in the xlwings ribbon with a module called "my_udf.py":

_images/udf_modules.png

  • If the module is not within the same directory as the Excel file, you point to it via the "PYTHONPATH" field. The image below shows input for if the module was in a folder under "C:\py_folder" (just an example so it fits in the field window):

_images/pythonpath.png

  • For reference, with those changes, this is how your xlwings.conf file should look:

_images/pythonpath_conf.png

myproject.xlsm というワークブックを例に説明します。 myproject.py というファイルに次のコードを書きます:

import xlwings as xw

@xw.func
def double_sum(x, y):
    """Returns twice the sum of the two arguments"""
    return 2 * (x + y)

  • 次に、xlwingsタブの Import Python UDFs をクリックして、 myproject.py への変更を取り込みます。

  • セルに数式 =double_sum(1, 2) を入力すれば、正しい結果を取得できます:

    _images/double_sum.png

  • （トリプル クォートで囲われた）docstringは、Excelでは関数の説明に表示されます。

注釈

  • 再度インポートする必要があるのは、関数の引数や名前を変えた時のみです。

  • コードの変更は自動的（例えば、 Ctrl-Alt-F9 でトリガーされる、数式の次回計算時）に取り込まれますが、コード内でインポートしたモジュールの変更は取り込まれません。これはPythonのインポートの挙動によるものです。全てをフレッシュな状態にするには、 Restart UDF Server をクリックします。

  • xlwingsが @xw.func デコレーターが使用するのは、関数をExcelに取り込むときにのみです。デコレーターは、どの関数に対してxlwingsがVBAラッパー関数を作るかを教えますが、関数のPythonでの挙動に影響を与えることはありません。

配列数式: 効率的に計算する#

多数のセルから個別に数式を呼び出すよりも、1つの大きな配列数式を呼び出す方が効率的です。一般的に配列数式の使用は良い方法であり、パフォーマンスに問題があるケースでは特に効果があります。

単独セル以外にも、ExcelのRangeを関数の引数にできます。その際、RangeはPythonではリストのリストになります。

例えば、Rangeに含まれる全てのセルに1を加える関数は次のようになります:

@xw.func
def add_one(data):
    return [[cell + 1 for cell in row] for row in data]

この数式を使うには

  • Import Python UDFs ボタンをもう一度押します

  • A1:B2 に値を入力します

  • D1:E2 を選択します

  • 数式 =add_one(A1:B2) を入力します

  • Ctrl+Shift+Enter を押し、配列数式を作ります。全てを正しく行えば、次のスクリーンショットのように、数式が波括弧で囲われます。

_images/array_formula.png

配列の次元数: ndim#

上記の数式の問題点は、例えば [[1, 2], [3, 4]] のような入れ子状のリストのような"2次元の"インプットを前提としていることです。したがって、この数式を単独セルで使うと、次のエラーが出ます: TypeError: 'float' object is not iterable 。

引数が単独セル、列/行、2次元のRangeのいずれの場合でも、Excelから常に2次元の配列を取得するには、上記の数式を次のように拡張します:

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

NumpyおよびPandasでの配列数式#

科学計算分野のPythonのエコシステムを最大限に活用するために、Numpyの配列やPandasのDataFrameをUDFで使用する方法を紹介します。

Numpyの配列を使った行列の掛け算の数式を定義するには、次のように記述します:

import xlwings as xw
import numpy as np

@xw.func
@xw.arg('x', np.array, ndim=2)
@xw.arg('y', np.array, ndim=2)
def matrix_mult(x, y):
    return x @ y

注釈

Python 3.5以上、Numpy 1.10以上ではない場合、 x @ y の代わりに x.dot(y) を使ってください。

Pandasの活用方法の良い例は、配列ベースの CORREL 関数です。例えば、Excelの CORREL は、2つのデータセット間の相関係数は計算できますが、複数の時系列データの相関行列は手軽には計算できません。Pandasを使えば、配列ベースの CORREL2 関数をたった一行で書けます。

import xlwings as xw
import pandas as pd

@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と@xw.ret#

これらのデコレーターはUDFに対するもので、Range オブジェクトに対してどのような options メソッドを適用するかを指定します。デコレーターにより、関数の引数(@xw.arg)や戻り値(@xw.ret)に対してコンバーターとそのオプションを適用できます。例えば、引数 x をPandasのDataFrameに変換し、戻り値のインデックスを省略するには、次のようにします:

@xw.func
@xw.arg('x', pd.DataFrame)
@xw.ret(index=False)
def myfunction(x):
   # x is a DataFrame, do something with it
   return x

詳細は コンバーターおよびオプション のドキュメントをご覧ください

動的配列数式#

注釈

Excelが新しいネイティブ動的配列をサポートしているなら、特別な事は何も必要ありませんし、 expand デコレーターを使用すべきではありません！ =UNIQUE() 式が利用可能なら、Excelはネイティブ動的配列をサポートしています。ネイティブ動的配列は2018年9月末にOffice 365のInsider Fastに導入されました。

先述のとおり、Excelで配列数式を使うには、事前に特定した次元に応じて、結果配列を選択し、数式を入力し、 最後に Ctrl-Shift-Enter を押す必要があります。実際、時系列データのような動的配列を扱うときには特に、煩わしいプロセスです。v0.10から、xlwingsでは動的UDF拡張が利用可能になりました:

単純な例でシンタックスとUDF拡張の効果を説明してみます。以下をご覧ください。

import numpy as np

@xw.func
@xw.ret(expand='table')
def dynamic_array(r, c):
    return np.random.randn(int(r), int(c))
_images/dynamic_array1.png
_images/dynamic_array2.png

注釈

  • 配列数式の拡張はプロンプトせずにセルを上書きします。

  • v0.15.0より前では、Volatile関数、（例えば =TODAY のような関数）は引数には使えませんでした。v0.15から、Volatile関数を引数に使えるようになりましたが、UDFは1回以上呼び出されます。

  • 動的配列はv0.15.0からリファクタリングされ、適切なレガシー配列になりました:xlwings >= v0.15.0で動的配列を編集するには、左上セルで Ctrl-Shift-Enter を入力する必要があります。最初に数式を入力するときには、そのような必要はありません。

Docstrings#

関数および引数x、yのdocstringを書いて、Excelの関数ウィザードに表示させる方法を以下の例で示します。

import xlwings as xw

@xw.func
@xw.arg('x', doc='This is x.')
@xw.arg('y', doc='This is y.')
def double_sum(x, y):
    """Returns twice the sum of the two arguments"""
    return 2 * (x + y)

"caller" 引数#

UDF呼び出し元セルが必要なこともあります。 xlwingsには予約済み引数 caller があり、UDFの呼び出し元セルを表すxlwingsのrangeオブジェクトとして利用できます:

@xw.func
def get_caller_address(caller):
    # caller will not be exposed in Excel, so use it like so:
    # =get_caller_address()
    return caller.address

注意: caller はExcelからは見えませんが、xlwingsが自動的に生成します。

"vba" キーワード#

vba キーワードを使えば、pywin32オブジェクトの形のあらゆるExcel VBAオブジェクトにアクセスすることができます。例えば、シート オブジェクトを CodeName で参照するには次のようにします:

@xw.func
@xw.arg('sheet1', vba='Sheet1')
def get_name(sheet1):
    # call this function in Excel with:
    # =get_name()
    return sheet1.Name

注意: vba 引数はExcelからは見えませんが、xlwingsが自動的に生成します。

マクロ#

Windowsでは、 RunPython でマクロを呼び出す代わりに、 @xw.sub デコレーターを使うこともできます。

import xlwings as xw

@xw.sub
def my_macro():
    """Writes the name of the Workbook into Range("A1") of Sheet 1"""
    wb = xw.Book.caller()
    wb.sheets[0].range('A1').value = wb.name

Import Python UDFs をクリックすれば、 Alt + F8 か、登録したボタン等からこのマクロを実行できます。後者の方法を説明します。まずは ファイル > オプション > リボンのユーザー設定 で、 開発 タブが選択されている事を確認してください。 開発 タブ内の 挿入 > フォーム コントロール から、ボタンを挿入します。ボタン描画後、マクロを割り当てるプロンプトが表示されるので、 my_macro を選択してください。

VBAからUDFを呼び出す#

インポートした関数はVBAからも利用できます。2次元配列を返す関数の例を以下に示します:

Sub MySub()

Dim arr() As Variant
Dim i As Long, j As Long

    arr = my_imported_function(...)

    For j = LBound(arr, 2) To UBound(arr, 2)
        For i = LBound(arr, 1) To UBound(arr, 1)
            Debug.Print "(" & i & "," & j & ")", arr(i, j)
        Next i
    Next j

End Sub

非同期UDF#

注釈

これは実験的な機能です

バージョン v0.14.0 で追加.

xlwingsには、Excelの非同期関数を簡単に書く方法もあります。 非期関数は、即時に　#N/A waiting... を返します。関数が戻り値を待つ間、Excelを使って他のことができます。まが、戻り値が取得でき次第、セルの値はアップデートされます。

現在利用可能なモードは async_mode='threading' のみで、例えば、ウェブからAPIでデータを利用する場合のようなI/Oバウンドなタスクに有用です。

関数のデコレーターに対応する引数を入れれば、簡単に非同期関数を作れます。次の例では、時間がかかるI/Oバウンドなタスクを、 time.sleep でシミュレートしています:

import xlwings as xw
import time

@xw.func(async_mode='threading')
def myfunction(a):
    time.sleep(5)  # long running tasks
    return a

他のxlwings関数と同様に、セルに =myfunction("abcd") を入力すればこの関数を利用できます（もちろん、関数をインポートした後でですが）

なお、xlwingsはExcel 2010で導入されたネイティブな非同期関数を使用していないので、xlwingsの非同期関数は全バージョンのExcelで使えます。