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

Excel には多数の組み込みワークシート関数が含まれていますが、実行する計算の種類ごとに関数があるわけではありません。 Excel のデザイナーは、すべてのユーザーの計算ニーズを予測することはできませんでした。 代わりに、Excel には、この記事で説明するカスタム関数を作成する機能が用意されています。

単純なカスタム関数を作成する

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

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

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

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

1. Alt+F11 キーを押して Visual Basic Editor を開き (Mac では fn + Alt + F11 キーを押し)、[挿入>モジュール] をクリックします。 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
   
   

カスタム関数の使用

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

=DISCOUNT(D7,E7)

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

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

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

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

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

マクロとカスタム関数の文書化

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

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

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

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

カスタム関数をどこでも利用できるようにする

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

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

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

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

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

著者について

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

補足説明

Excel 技術コミュニティの専門家にいつでも質問するか、コミュニティでサポートを受けることができます。