ユーザー定義関数 (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 で特定のモジュールを指定することもできます。

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で使えます。