[Office Master] | ||||||||||||
ユーザー定義関数で任意のセル範囲の値を取得する―― 色の付いたセルの合計値を求める例 ――
|
||||||||||||
|
解説 |
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, _ |
- サンプル・ファイルのダウンロード
(注:今回のサンプル・ファイルmyrange.xlsをダウンロードするには、上のリンクを右クリックして、myrange.xlsというファイル名で保存すること)
「セルA:セルB」の形式で指定されたセル範囲型データの実体は「Rangeオブジェクト」である。Rangeオブジェクトとして取得したセル範囲の内容は、For Eachステートメントを利用して順に取得することができる。ここでは、取得したセルの色番号(ColorIndexプロパティ)が第2引数の値と等しいかどうかを判定し、等しい場合にのみ加算処理を行っている。色番号が指定されていない場合は、色番号変数はxlColorIndexNoneという値になり、背景色が無色のセルと一致する。
手順3―ユーザー定義関数の挙動を確認する
それではさっそく、作成したSumOfColoredCell関数の挙動を確認してみよう。
ワークシート上に、以下のように簡単な表を用意しておく。セルの一部には、背景色として黄色を設定しておこう。
![]() |
|
セルB6にフォーカスを合わせてから、数式バーの左にある[Fx]ボタンをクリックするか、[挿入]−[関数]メニューを実行すると、次のような[関数の貼り付け]ウィンドウが表示される。手順2で作成したユーザー定義関数は、左のリストボックスから[ユーザー定義]を選択することで参照できる。
![]() |
||||||
ユーザー定義関数の貼り付け | ||||||
これはExcel 2000の例。[関数の貼り付け]ダイアログで、ユーザー定義関数のSumOfColoredCellを指定する。 | ||||||
|
関数名として「SumOfColoredCell」を選択すると、関数入力用のダイアログが表示されるのも普通の関数とまったく同じだ。ここでは、試しに以下のような値を入力してみよう。
引数 | 値 |
範囲 | B1:B5 |
色番号 | (ブランクのままにしておく) |
![]() |
|
SumOfColoredCell関数へ与える引数の例 |
対応するセルに結果として、B1〜B5のセルで背景色が無色のもののみの合計が返されれば成功だ。さらに引数indexに6(黄色)を指定した場合には、B1〜B5の中でセルの背景色が黄色のものだけの合計になることを確認してほしい。
なお、このように作成したユーザー定義関数を複数のブックで共有したいという場合には、いくつかの方法がある。詳細な方法については、Windows TIPS「Excelでユーザー定義関数をエクスポート/インポートする」「Excelでユーザー定義関数をアドインとして提供する」などを参照していただきたい。
この記事と関連性の高い別の記事
- Excelのユーザー定義関数で既存のワークシート関数を利用する(TIPS)
- Excelでユーザー定義のワークシート関数を追加する(TIPS)
- Excelのユーザー定義関数で複数の結果値を返す(TIPS)
- Excelでユーザー定義関数をエクスポート/インポートする(TIPS)
- VLOOKUP関数でExcel帳票への自動入力を可能にする(TIPS)
このリストは、デジタルアドバンテージが開発した自動関連記事探索システム Jigsaw(ジグソー) により自動抽出したものです。
![Jigsaw](/fwin2k/win2ktips/images/jigsaws.gif)
![]() |
「Windows TIPS」 |
- Azure Web Appsの中を「コンソール」や「シェル」でのぞいてみる (2017/7/27)
AzureのWeb Appsはどのような仕組みで動いているのか、オンプレミスのWindows OSと何が違うのか、などをちょっと探訪してみよう - Azure Storage ExplorerでStorageを手軽に操作する (2017/7/24)
エクスプローラのような感覚でAzure Storageにアクセスできる無償ツール「Azure Storage Explorer」。いざというときに使えるよう、事前にセットアップしておこう - Win 10でキーボード配列が誤認識された場合の対処 (2017/7/21)
キーボード配列が異なる言語に誤認識された場合の対処方法を紹介。英語キーボードが日本語配列として認識された場合などは、正しいキー配列に設定し直そう - Azure Web AppsでWordPressをインストールしてみる (2017/7/20)
これまでのIaaSに続き、Azureの大きな特徴といえるPaaSサービス、Azure App Serviceを試してみた! まずはWordPressをインストールしてみる
![]() |
|
|
|
![]() |