Excelでユーザー定義のワークシート関数を追加する：Tech TIPS

Excelでは、標準で多くのワークシート関数が提供されている。しかし、実際にワークシート上でさまざまな演算を実行しようとした場合、必ずしも標準の関数だけでは用を足さないというケースもある。そこで、Excelではよく利用する機能をユーザーがワークシート関数として定義することができる。

» 2004年06月12日 05時00分公開

[山田祥寛，著]

連載目次

対象ソフトウェア：Excel 2000／Excel 2002／Excel 2003

解説をスキップして操作手順を今すぐ読む→

解説

　ワークシート関数とは、その名のとおり、Excelのワークシート上で利用できる関数のことだ。いまさら特筆するまでもなく、Excelはデフォルトで実に多くのワークシート関数を提供しており、基本的な数値演算から日付計算、文字列操作、財務計算、統計、果てはちょっとしたデータベース処理までを、簡単な記述で実現できる。恐らく一般的な表計算の処理を行う限りでは、標準的なワークシート関数で十分に用が足りるはずだ。

　しかしExcelを駆使してさまざまな表計算を行っていく中では、当然のことながら、なかなか標準関数では十分に賄いきれない局面も出てくるだろう。そのような場合に、いままでならばどうしていただろうか。複数の関数を組み合わせて、複雑な関数式を毎回記述していたかもしれない。あるいは、一時的なワークシートやセルに計算過程を退避させた上で、あらためて目的の値を導出する、といったようなわずらわしい作業を日常的に行っていたかもしれない。

　もちろん、このようなアプローチがだめだとはいわない。しかし、関数式が長くなれば、当然、使いまわす中で間違いが発生する可能性も高くなるし、ほかのユーザーと共有しようと思った場合にも困難がつきまとう。このような事情は、一時的なワークシートやセルを介して演算を行っている場合にはなおさらだ。

　そこで本稿では、日常的によく使用する（しかし、Excel標準では用意されていない）ワークシート関数を自前で追加する方法について紹介する。最初の手続きこそ煩雑に思えるかもしれないが、これによって、ワークシート上での記述を簡素化できるだけでなく、同様の機能を複数人で共有したいと思った場合にも容易に実現できる。

操作方法

●手順1―新規に標準モジュールを生成する

　ユーザー定義関数の実体は、標準モジュールで定義されたFunctionプロシージャだ。標準モジュールの追加は、Visual Basic Editorから行うことができる。

　メニュー・バーの［ツール］－［マクロ］－［Visual Basic Editor］から「Visual Basic Editor」を起動したら、［挿入］－［標準モジュール］で新しいモジュールを追加しよう。プロジェクト・ウィンドウに［標準モジュール］－［Module1］というモジュールが追加されるはずだ。モジュール名は任意に決めて構わないが、本稿では仮に「winTips」としておく。

●手順2―ユーザー定義関数のプログラム・コードを記述する

　それでは具体的なユーザー定義関数を記述してみることにしよう。どんな内容でも特に構わないが、ここでは仮に以下のようなワークシート関数を定義してみる。



関数名	SubStringAfter(文字列,部分文字列[,フラグ])
機能	文字列の後方から指定された部分文字列を検索し、出現位置以降の文字列を取得する。戻り値に検索文字列を含むことができるかどうかは、第3引数によって切り替え可能とする。「フラグ」がTrueならば検索文字列を含み、Falseならば検索文字列を除いたものとなる
定義するSubStringAfter関数

　コード・エディタ上に、以下のコードを入力してほしい。仮引数名に日本語を使用しているのは、ここで使用している名前がそのまま関数ウィザードで表示される名前となるからだ。もちろん、英数字のみの名前にしても構わないが、その場合も利用者ができるだけ直感的に分かりやすい名前を付けることが好ましい。

Function SubStringAfter(文字列, 検索文字列, Optional フラグ = False)
intSrch = InStrRev(文字列, 検索文字列)
   ' 引数フラグがTRUEの場合には検索文字列を含む部分文字列を、FALSEの場合には検索文字列を除く部分文字列を抜き出す
If フラグ Then
    strResult = Right(文字列, Len(文字列) - (intSrch - 1))
Else
    strResult = Right(文字列, Len(文字列) - Len(検索文字列) - (intSrch - 1))
End If
SubStringAfter = strResult
End Function

　仮引数の前に指定しているキーワード「Optional」は、引数が省略可能であることを示す。キーワードOptionalを指定した場合には、必ず省略時のデフォルト値を指定する必要がある。また、Optionalを指定した引数のさらに後方に、省略不可の引数を指定することはできないので、注意すること。

　なお、仮引数に指定可能なキーワードとして、ほかにもParamArrayなどは覚えておくと便利なキーワードだ、ParamArrayが指定された場合、その引数が不特定な数の要素を持つ配列であることを表す。あらかじめ渡される引数の数が特定できないようなケースで利用するとよい。

　例えば、以下のSumPlus関数は引数で渡された値の中から正数だけを選択して合計値を求めるための関数だ。

※不特定の数の引数を取る関数の例

Function SumPlus(ParamArray 数値())
result = 0
For i = 0 To UBound(数値)
    If 数値(i) > 0 Then
      result = result + 数値(i)
    End If
Next
SumPlus = result
End Function

　上のような定義があった場合、SumPlus関数には次のように不特定数の引数を指定することができる。

=SumPlus(A1,B2,D5)
=SumPlus(A1,E16)

　ParamArrayを使用した場合、仮引数は必ず配列として指定しなければならない点、ほかのどの仮引数に対してもOptionalキーワードは使用できない点に注意すること。

●手順3―ユーザー定義関数の挙動を確認する

　それではさっそく、作成したユーザー定義関数の挙動を確認してみよう。

　メニューから［挿入］－［関数］を選択すると、次のような［関数の貼り付け］ウィンドウが表示される。手順2で作成したユーザー定義関数は、左のリストボックスから［ユーザー定義］を選択することで参照できる。

ユーザー定義関数の貼り付け
これはExcel 2000の例。［関数の貼り付け］ダイアログで、ユーザー定義関数のSubStringAfterを指定する。
　 （1）ユーザー定義関数は、［関数の分類］上では「ユーザー定義」に分類される。
　 （2）関数名の一覧から目的の関数を選択して［OK］をクリックする。

　関数名として「SubStringAfter」を選択すると、関数入力用のダイアログが表示されるのも普通の関数とまったく同じである。ここでは、試しに以下のような値を入力してみよう。


引数	値
文字列	http://www.wings.msn.to/index.php
検索文字列	/
フラグ	False
SubStringAfter関数へ与える引数の例

　実行すると、次のようになる。

関数値入力用のダイアログ
引数を指定すると、結果が表示される。
　 （1）検索される対象となる文字列。
　 （2）検索する文字列。
　 （3）結果。

　対応するセルに結果文字列として、「index.php」（最後の「/」以降の文字列）が表示されれば成功だ。

●手順4―ブック・テンプレート化する

　以上で、一通りの機能については実現できたが、これだけでは少々物足りない。一般的には、このようなユーザー定義関数は複数のブックで共有することで、より効果的なツールになり得るだろう。

　そこで本稿では、最後に、ユーザー定義関数を定義したブックを「テンプレート化」しておくことにする。ブック・テンプレートを利用することで、新規のブックを起動したときにも自動的にテンプレート内で定義されたユーザー定義関数を有効化することができる。

　ブック・テンプレートを作成する方法は簡単だ。メニューバーの［ファイル］－［名前を付けて保存］を選択し、ファイルの種類を［テンプレート（*.xlt）］、ファイル名を「book.xlt」として、現在のブックを「C:\Program Files\Microsoft Office\Office\XLStart」（Excelのスタートアップ・フォルダ）に保存する。これによって、次の新規ブックの起動から自動的にユーザー定義関数が利用できるようになる。

　なお、XLStartフォルダの場所はユーザー環境によって異なる可能性がある。もしも上記の場所にフォルダが存在しなければ、適宜、コンピュータ上のフォルダを検索してみてほしい。