Windows TIPS

［Office Master］

→ Windows TIPS TOPへ → Windows TIPS全リストへ ユーザー定義関数で任意のセル範囲の値を取得する ―― 色の付いたセルの合計値を求める例 ―― → 解説をスキップして操作方法を読む 山田 祥寛 2004/12/25 　 対象ソフトウェア Excel 2000 Excel 2002 Excel 2003

■ ワークシート関数に対して引数を渡す場合、「セルA:セルB」の形式で表されるセル範囲として表現することが多い。 ■ Rangeオブジェクトを利用すれば、ユーザー定義関数でセル範囲の指定を受け取ることが可能になる。

　

解説

　Windows TIPS「Excelでユーザー定義のワークシート関数を追加する」では、Excelワークシートに独自のユーザー定義関数を追加する方法について紹介した。Excelにはデフォルトでも多くの関数が用意されているが、Excelを駆使してさまざまな処理を行っていくと、標準的な関数では対応できない処理を実現したいケースもあるだろう。そのような場合にも、ユーザー定義関数を利用すれば、複雑な式をワークシート上に直接記述することなく実現できる。

　本TIPSでは、ユーザー定義関数を構築する上では欠かすことができない、「任意のセル範囲の値」を取得する方法について紹介する。同様のことは、先のTIPSでも紹介したParamArrayキーワードを利用すれば実現できる。しかしParamArrayキーワードによって宣言されたユーザー定義関数では、対象となるセルを1つずつ、すべて列挙しなければならない。つまり、A1〜A5までのセルを指定するにも、次のように記述しなければならないのである。

=UserFunction(A1,A2,A3,A4,A5)

　この例ではたかだか対象のセルが5つしかないが、これが10とか、100もあると、式の記述は冗長になってしまうし、なによりこのような指定の方法はExcelらしくない。一般的なExcelの関数式では、以下のように指定するのが普通であろう。

=UserFunction(A1:A5)

　このような指定を行うことで、対象のセル範囲が広くなっても式をコンパクトに記述することができる。本TIPSでは、このような範囲型のパラメータを操作する方法を紹介する。

操作方法

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

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

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

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

　それでは、セル範囲を引数として受け取るユーザー定義関数を記述してみることにしよう。関数自体はどんな内容でも構わないが、ここでは例として次のようなワークシート関数を定義してみる。

関数名	SumOfColoredCell(セル範囲［,色番号］)
機能	指定されたセル範囲で指定された色番号を持つセルの値のみを合計する。第2引数（色番号）が省略された場合には、背景が無色のセルのみを合計の対象とする
定義するSumOfColoredCell関数

　コード・エディタ上で、次のコードを入力してほしい。

Function SumOfColoredCell(範囲 As Range, _   Optional 色番号 As Integer = xlColorIndexNone)   ' 指定範囲から順番にセルを取り出す   For Each myCell In 範囲     ' 該当セルの色番号が引数の指定値に等しい場合のみ加算     If myCell.Interior.ColorIndex = 色番号 Then       result = result + myCell.Value     End If   Next   SumOfColoredCell = result End Function

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

　「セルA:セルB」の形式で指定されたセル範囲型データの実体は「Rangeオブジェクト」である。Rangeオブジェクトとして取得したセル範囲の内容は、For Eachステートメントを利用して順に取得することができる。ここでは、取得したセルの色番号（ColorIndexプロパティ）が第2引数の値と等しいかどうかを判定し、等しい場合にのみ加算処理を行っている。色番号が指定されていない場合は、色番号変数はxlColorIndexNoneという値になり、背景色が無色のセルと一致する。

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

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

　ワークシート上に、以下のように簡単な表を用意しておく。セルの一部には、背景色として黄色を設定しておこう。

あらかじめ用意しておくワークシート セル上にいくつかの数値を入れておき、同じ背景色のセルの数値の合計を求める。 　 このB6セルにフォーカスを合わせてから、定義したユーザー関数を書き込み、パラメータとしてセル範囲を指定する。 　 背景色が黄色の例。

　セルB6にフォーカスを合わせてから、数式バーの左にある［Fx］ボタンをクリックするか、［挿入］−［関数］メニューを実行すると、次のような［関数の貼り付け］ウィンドウが表示される。手順2で作成したユーザー定義関数は、左のリストボックスから［ユーザー定義］を選択することで参照できる。

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

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

ユーザー定義関数は、［関数の分類］上では「ユーザー定義」に分類される。 　 関数名の一覧から目的の関数を選択して［OK］をクリックする。

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

引数	値
範囲	B1:B5
色番号	（ブランクのままにしておく）
SumOfColoredCell関数へ与える引数の例

　対応するセルに結果として、B1〜B5のセルで背景色が無色のもののみの合計が返されれば成功だ。さらに引数indexに6（黄色）を指定した場合には、B1〜B5の中でセルの背景色が黄色のものだけの合計になることを確認してほしい。

　なお、このように作成したユーザー定義関数を複数のブックで共有したいという場合には、いくつかの方法がある。詳細な方法については、Windows TIPS「Excelでユーザー定義関数をエクスポート／インポートする」「Excelでユーザー定義関数をアドインとして提供する」などを参照していただきたい。

「Windows TIPS」

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