Excel でカスタム関数を作成する

Excel には、多数の組み込みワークシート関数が含まれていますが、ユーザーが実行するすべての種類の計算に対応できる関数はおそらくありません。Excel の設計者は、すべてのユーザーの計算ニーズを予想できませんでした。代わりに、Excel には、カスタム関数を作成する機能があります。この記事ではこの機能について説明します。

マクロと同様に、カスタム関数では Visual Basic for Applications (VBA) プログラミング言語を使用します。カスタム関数は、次の 2 つの点でマクロと大きく異なります。まず、カスタム関数は Sub プロシージャではなく、Function プロシージャを使用します。つまり、Sub ステートメントではなく Function ステートメントで始まり、End Sub ではなく End Function で終わります。次に、カスタム関数はアクションではなく、計算を実行します。範囲を選択して書式設定を行うステートメントなど、ある種のステートメントはカスタム関数から除外されます。この記事では、カスタム関数を作成および使用する方法について説明します。関数とマクロを作成するには、Visual Basic Editor (VBE) を使用します。これは、Excel とは別の新しいウィンドウで開きます。

あなたの会社の製品販売で、注文が 100 個を超える場合、10% の数量割引を行うとします。以下の段落で、この割引を計算する関数を示します。

次の例は、各品目、数量、価格、割引 (ある場合)、および合計金額の一覧を表示する注文書を示します。

カスタム関数を使用しない注文書の例

このブックに DISCOUNT カスタム関数を作成するには、次の手順を実行します。

  1. Alt キーを押しながら F11 キー (Mac では fn キーと option キーを押しながら F11 キー) を押して Visual Basic Editor を開き、[挿入]、[モジュール] の順にクリックします。Visual Basic Editor の右側に新しいモジュール ウィンドウが表示されます。

  2. 次のコードをコピーして新しいモジュールに貼り付けます。

    Function DISCOUNT(quantity, price)
    If quantity >=100 Then
    DISCOUNT = quantity * price * 0.1
    Else
    DISCOUNT = 0
    End If

    DISCOUNT = Application.Round(Discount, 2)
    End Function

注: コードを読みやすくするために、Tab キーを使用して行をインデントすることができます。インデントは自分に役立つだけで、省略可能です。インデントの有無に関係なく、コードは実行されます。インデントした行を入力すると、Visual Basic Editor は、次の行も同様にインデントされると推測します。1 タブ文字だけ左に移動するには、Shift キーを押しながら Tab キーを押します。

これで、新しい DISCOUNT 関数を使用する準備が整いました。Visual Basic Editor を閉じて、セル G7 を選択し、次のように入力します。

=DISCOUNT(D7,E7)

Excel は、1 個あたり 47.50 ドルの製品 200 個に対する 10% の割引を計算し、950.00 ドルを返します。

VBA コードの 1 行目の Function DISCOUNT(quantity, price) で、DISCOUNT 関数には quantityprice の 2 つの引数が必要であることを示しました。ワークシートのセルでこの関数を呼び出す場合は、これら 2 つの引数を含める必要があります。数式 = DISCOUNT(D7,E7) では、D7 が quantity 引数で、E7 が price 引数です。これで、G8 から G13 に DISCOUNT 式をコピーすると、以下に示す結果を得ることができます。

Excel がこの Function プロシージャを解釈する方法について考えてみましょう。Enter キーを押すと、Excel は現在のブックで DISCOUNT という名前を検索し、それが VBA モジュールのカスタム関数だとわかります。かっこで囲まれた引数名の quantityprice は、割引の計算の基になる値のプレースホルダーです。

カスタム関数を使用した注文書の例

次のコード ブロック内の If ステートメントは quantity 引数を調べ、販売された品目の数が 100 以上かどうかを判定します。

If quantity >= 100 Then
DISCOUNT = quantity * price * 0.1
Else
DISCOUNT = 0
End If

販売された品目の数が 100 以上の場合、VBA は次のステートメントを実行します。このステートメントは quantity 値に price 値を掛けて、その結果に 0.1 を掛けます。

Discount = quantity * price * 0.1

結果は、変数 Discount として格納されます。変数に値を格納する VBA ステートメントは、代入ステートメントと呼ばれます。これは、このステートメントが等号 (=) の右側にある式を評価し、その結果を左側の変数名に代入するからです。変数 Discount は Function プロシージャと同じ名前なので、DISCOUNT 関数を呼び出したワークシート式に、この変数に格納されている値が返されます。

quantity が 100 未満の場合、VBA は次のステートメントを実行します。

Discount = 0

最後に、次のステートメントは、Discount 変数に割り当てた値を小数点以下 2 桁に四捨五入します。

Discount = Application.Round(Discount, 2)

VBA には ROUND 関数がありませんが、Excel にはあります。そのため、このステートメントで ROUND を使用するには、Application オブジェクト (Excel) 内で Round メソッド (関数) を探すように VBA に指示します。これには、Round という単語の前に Application という単語を追加します。VBA モジュールから Excel 関数にアクセスする必要があるときはいつでも、この構文を使用します。

カスタム関数は Function ステートメントで始まり、End Function ステートメントで終わる必要があります。Function ステートメントは通常、関数名に加えて、1 つまたは複数の引数を指定します。ただし、引数なしで関数を作成することもできます。Excel には、引数を使用しない組み込み関数がいくつか含まれています (たとえば、RAND や NOW)。

Function ステートメントに続いて、Function プロシージャには、関数に渡された引数を使用して判定を行い、計算を実行する 1 つまたは複数の VBA ステートメントが含まれています。最後に、Function プロシージャのどこかに、関数と同じ名前の変数に値を代入するステートメントを含める必要があります。この値は、関数を呼び出す式に返されます。

カスタム関数で使用できる VBA キーワードの数は、マクロで使用できる数より少なくなっています。カスタム関数では、ワークシート内の式、または別の VBA マクロや関数内で使用されている式に値を返す以外のことは許可されていません。たとえば、カスタム関数は、ウィンドウのサイズ変更、セル内の式の編集、セル内のテキストのフォント、色、パターンの各オプションの変更を行うことができません。Function プロシージャにこの種の “アクション“ コードを含めると、#VALUE! エラーが返されます。

Function プロシージャが実行できる (計算の実行とは別の) 1 つのアクションは、ダイアログ ボックスの表示です。関数を実行しているユーザーから入力を取得する手段として、カスタム関数内で InputBox ステートメントを使用することができます。ユーザーに情報を伝える手段として、MsgBox ステートメントを使用することができます。また、カスタム ダイアログ ボックスの UserForms を使用することもできますが、この概要の範囲を超えた話題です。

単純なマクロとカスタム関数でも読みにくい可能性があります。コメントの形式で説明文を入力して、理解しやすくすることができます。コメントを追加するには、説明文の前にアポストロフィを付けます。たとえば、次の例では、コメント付きの DISCOUNT 関数を示しています。このようなコメントを追加すると、作成者または他のユーザーが将来 VBA コードを保守しやすくなります。今後コードを変更する必要がある場合、元の処理を容易に理解できます。

コメントを含む VBA 関数の例

アポストロフィは、同じ行の右にあるすべてを無視するように Excel に通知するので、行に単独でまたは VBA コードを含む行の右側にコメントを作成することができます。まず、比較的長いコード ブロックに全体的な目的を説明するコメントを追加し、次に文書の個々のステートメントにインライン コメントを使用することができます。

マクロとカスタム関数を文書化する別の方法は、わかりやすい名前を付けることです。たとえば、マクロに Labels という名前を付けるのではなく、MonthLabels という名前を付けると、マクロが果たす目的をより具体的に説明することができます。マクロとカスタム関数にわかりやすい名前を付けると、特に多数のプロシージャを作成した場合、または目的は似ているが同一ではないプロシージャを作成する場合に役立ちます。

マクロとカスタム関数をどのように文書化するかは、個人の好みの問題です。重要なのは、ある文書化の方法を採用し、一貫して使用することです。

カスタム関数を使用するには、作成した関数が入っているモジュールを含むブックを開く必要があります。そのブックが開いていない場合、その関数を使用しようとすると、#NAME? エラーが発生します。別のブックで関数を参照する場合は、関数名の前に、その関数が属するブックの名前を付ける必要があります。たとえば、Personal.xlsb というブックに DISCOUNT という関数を作成し、その関数を別のブックから呼び出す場合は、単に =discount() と入力するのではなく、=personal.xlsb!discount() と入力する必要があります。

[関数の挿入] ダイアログ ボックスからカスタム関数を選択すると、いくつかのキー操作を省略できます (また、入力ミスが減る可能性があります)。カスタム関数は、[ユーザー定義] カテゴリに表示されます。

[関数の挿入] ダイアログ ボックス

カスタム関数をいつでも利用できるようにするより簡単な方法は、それらを別のブックに格納し、そのブックをアドインとして保存することです。その後、Excel を実行するたびに、アドインを利用可能にすることができます。その手順は次のとおりです。

  1. 必要な関数を作成したら、[ファイル]、[名前を付けて保存] の順にクリックします。

    Excel 2007 では、[Microsoft Office ボタン] をクリックし、[名前を付けて保存] をクリックします。

  2. [名前を付けて保存] ダイアログ ボックスで、[ファイルの種類] ドロップダウン リストを開き、[Excel アドイン] を選択します。AddIns フォルダーに MyFunctions などのわかりやすい名前でブックを保存します。[名前を付けて保存] ダイアログ ボックスによってそのフォルダーが提案されるので、既定の場所を適用するだけです。

  3. ブックを保存したら、[ファイル]、[Excel のオプション] の順にクリックします。

    Excel 2007 では、[Microsoft Office ボタン] をクリックし、[Excel のオプション] をクリックします。

  4. [Excel のオプション] ダイアログ ボックスで、[アドイン] カテゴリをクリックします。

  5. [管理] ドロップダウン リストで、[Excel アドイン] を選択します。次に、[検索開始] ボタンをクリックします。

  6. [アドイン] ダイアログ ボックスで、次に示すように、ブックの保存に使用した名前の横にあるチェック ボックスをオンにします。

    [アドイン] ダイアログ ボックス

  1. 必要な関数を作成したら、[ファイル]、[名前を付けて保存] の順にクリックします。

  2. [名前を付けて保存] ダイアログ ボックスで、[ファイルの種類] ドロップダウン リストを開き、[Excel アドイン] を選択します。MyFunctions などのわかりやすい名前でブックを保存します。

  3. ブックを保存したら、[ツール]、[Excel アドイン] の順にクリックします。

  4. [アドイン] ダイアログ ボックスで、[参照] ボタンをクリックして目的のアドインを見つけて、[開く] をクリックし、[有効なアドイン] ボックスの目的のアドインの横にあるボックスをオンにします。

これらの手順を実行すると、Excel を実行するたびに、カスタム関数が利用可能になります。関数ライブラリに追加する場合は、Visual Basic Editor に戻ります。Visual Basic Editor のプロジェクト エクスプローラーの VBAProject 見出しの下に、作成したアドイン ファイルに基づいて名前が付けられたモジュールが表示されます。アドインには、拡張子 .xlam が付けられます。

VBE の名前が付けられたモジュール

プロジェクト エクスプローラーのそのモジュールをダブルクリックすると、Visual Basic Editor に関数のコードが表示されます。新しい関数を追加するには、コード ウィンドウで最後の関数を終了させる End Function ステートメントの後に挿入ポイントを配置し、入力を開始します。この方法で必要な数の関数を作成することができます。これらの関数は常に、[関数の挿入] ダイアログ ボックスの [ユーザー定義] カテゴリに表示されます。

当初、このコンテンツは、Mark Dodge と Craig Stinson による本『Microsoft Office Excel 2007 Inside Out.』の一部として作成されました。それ以来、Excel の新しいバージョンにも適合するように更新されています。

補足説明

Excel Tech Community では、いつでも専門家に質問できます。Microsoft コミュニティでは、サポートを受けられます。また、Excel User Voice では、新機能についての提案や改善案を送信することができます。

Office のスキルを磨く
トレーニングの探索
新機能を最初に入手
Office Insider に参加する

この情報は役に立ちましたか?

ご意見をいただきありがとうございます。

フィードバックをお寄せいただき、ありがとうございます。Office サポートの担当者におつなぎいたします。

×