Windows TIPS

［Office Master］

→ Windows TIPS TOPへ → Windows TIPS全リストへ Excelのユーザー定義関数で複数の結果値を返す → 解説をスキップして操作方法を読む 山田 祥寛 2005/01/08 　 対象ソフトウェア Excel 2000 Excel 2002 Excel 2003

■ ワークシート関数は必ずしも単一の値を返すとは限らない。 ■ 「配列数式」を利用することで、1つの関数で複数の値を返すワークシート関数を定義できる。

　

解説

　Windows TIPS「Excelでユーザー定義のワークシート関数を追加する」「ユーザー定義関数で任意のセル範囲の値を取得する」では、標準モジュールを利用してユーザー定義関数を構築する代表的な方法について紹介した。

　これらのTIPSでは、いずれも結果として1つの値を返すユーザー定義関数を紹介したが、Excelでは1つの関数で複数の値を返す「配列数式」という機能が用意されている。本稿では、この「配列数式」を利用して、指定されたセルの合計値と平均値とを同時に返すSumAvg関数を作成してみよう。

操作方法

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

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

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

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

　それでは、具体的なユーザー定義関数を記述してみることにしよう。本稿で扱うSumAvg関数の仕様は以下のとおりである。

関数名	機能
SumAvg(セル範囲)	指定されたセル範囲の合計値と平均値とを横方向（複数列）のセルに返す
定義するSumAvg関数

　コード・エディタ上に、以下のコードを入力してほしい。

Function SumAvg(範囲 As Range)   Dim result(1) As Double   cnt = 0   ' 指定範囲から順番にセルを取り出し、セル範囲の合計値を算出   For Each myCell In 範囲     cnt = cnt + 1     result(0) = result(0) + myCell.Value   Next   ' セル範囲の平均値を算出   result(1) = result(0) / cnt   ' 生成された配列を戻り値として返す   SumAvg = result End Function

• サンプル・ファイルのダウンロード
  （注：サンプル・ファイルをダウンロードするには、上のリンクを右クリックしてarrayfunc.xlsというファイル名で保存する）

　配列数式とはいっても、Functionプロシージャでは単に戻り値として配列を返すだけなので（配列result(0)とresult(1)に結果をセットして返す）、コード上で特別に意識することは何もない。

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

　それではさっそく、作成したSumAvg関数の挙動を確認してみよう。関数の実行に先立って、ワークシート上には以下のような簡単な表を作成しておくものとする。

あらかじめ用意しておくワークシート

　セルB6〜C6にフォーカスを合わせたうえで［Fx］ボタンをクリックすると、［関数の貼り付け］ウィンドウが表示される。手順2で作成したユーザー定義関数は、左のリストボックスから［ユーザー定義］を選択することで参照できる。

ユーザー定義関数の貼り付け

これはExcel 2000の例。［関数の貼り付け］ダイアログで、ユーザー定義関数のSumAvgを指定する。

ユーザー定義関数は、［関数の分類］上では「ユーザー定義」に分類される。 　 関数名の一覧から目的の関数を選択する。 　 ［OK］をクリックすると関数の引数を入力するダイアログが表示されるので、セルの範囲を指定後、最後に［Ctrl］と［Shift］を押しながら［Enter］を押すこと（もしくは［OK］をクリックすること）。

　関数名として「SumAvg」を選択して［OK］をクリックすると、関数の引数（セルの範囲）入力用のダイアログが表示されるのも普通の関数とまったく同じだ。ここでは、試しに次のような値を入力してみよう。配列式を入力する場合には、式を確定する際に単なる［Enter］（もしくは［OK］）ではなく、［Ctrl］＋［Shift］＋［Enter］（もしくは［Ctrl］と［Shift］を押しながら［OK］をクリック）で確定しなければならない点に注意すること。

引数	値
範囲	B2:B5
SumAvg関数へ与える引数の例

　セルB6にはB2〜B5の合計値、セルC6にはB2〜B5の平均値がそれぞれ表示されれば成功だ。もしもあらかじめ選択したセルが配列の要素数よりも少ない場合には、あふれた要素は無視されるし、多い場合には有効な戻り値が存在しないことを示すエラー値「#N/A」が表示される。

　ちなみに、この例では配列値を横並びのセルに返している。もしも誤って縦並びのセルを選択してしまった場合には、配列の最初の要素だけが連続して縦に表示されるはずだ。配列の値を縦並びのセルに表示したいという場合には、ユーザー定義関数SumAvgを以下のように2×1の2次元配列を返すように書き換える必要がある。

Function SumAvg(範囲 As Range)   Dim result(1, 0) As Double   cnt = 0   For Each myCell In 範囲     cnt = cnt + 1     result(0, 0) = result(0, 0) + myCell.Value   Next   result(1, 0) = result(0, 0) / cnt   SumAvg = result End Function

　

	関連記事
		Windows TIPS：Excelでユーザー定義のワークシート関数を追加する（Windows Server Insider）
		Windows TIPS：ユーザー定義関数で任意のセル範囲の値を取得する（Windows Server Insider）

「Windows TIPS」

＠ITメールマガジン　新着情報やスタッフのコラムがメールで届きます（無料）