エクセルファイルのダウンロード
HOME 便利テク集
今回は、エクセルの標準機能として、利用できるVBAプログラムを応用した。ユーザ定義関数について、説明したいと思います。エクセルのセルに関数を使った数式を設定できますが、実は、この関数に自分で作成した独自の関数を使用することができます。このことを知らない方が多いのでは、ないかと思い、今回、このことをテーマに取り上げました。
課題は、以下のような身体検査による、身長と体重のデータ・テーブルがあったとします。この2つから、BMI(Body Mass Index)と呼ばれる肥満度を示す値を算出してみたいと思います。BMIは、VBAで作成した関数を使用して、算出するものとします。
エクセルファイルのダウンロード
BMIの算出式は、以下となっています。
BMI=体重【Kg】÷{身長【m】×身長【m】}
セルに単純に従来の数式を利用して、求めることも出来るのですが、今回は、自分で作成した関数を利用することをテーマに置いていますので、お付き合い頂ければと思います。
リボンの【開発】タブを選択し、【コードの表示】をクリックします。【開発】タブが表示されていないかたは、設定を変更してください。
メニューの【挿入】-【標準モジュール】を選択し、モジュールを追加します。
初期設定として、Module1というモジュール名で、プロジェクト エクスプローラー内に追加されていますので、まず、Module1を選択してください。
プロジェクト エクスプローラーが表示されていない場合は、メニューの【表示】-【プロジェクト エクスプローラー】を選択してください。
次に、プロパティ ウインドウから、このモジュールの名前を変更したいと思います。名前は、何を指定しても良い(変更しなくても構わない)のですが、ここではモジュール名を、mdl_bmiとして、話しを進めていきたいと思います。
プロパティ ウインドウが表示されていない場合は、メニューの【表示】-【プロパティ ウインドウ】を選択してください。
mdl_bmiモジュールに以下のプログラミングコードを実装してください。コピペでもOKです。
Option Explicit
'Body Mass Index(肥満指数)を返却します。
'BMI=【体重Kg】÷{【身長m】×【身長m】}
Public Function BMI(ByVal height As Range, ByVal weight As Range) As Double
Dim tmp As Double
'cmをmに変換します。
tmp = height / 100
BMI = weight / (tmp * tmp)
End Function
コーディングした関数は、BMI関数という名前で、身長と体重の値から、BMI値を算出する内容になっています。これにより、エクセルのシート上で、セルにこの関数を設定できるようになります。
一つ、大事なのが、セルからユーザ定義の関数を呼び出すには、標準モジュールに記述しなければ、なりません。
以下の赤字の箇所が、ユーザにより自由に定義できる箇所となっており、それ以外の単語が、プログラムを作成するために、あらかじめ用意されている予約語となります。
ある手続き・処理を実行するのが、関数と呼ばれるもので、ここに記述した名前が、セルに式を設定する際に、呼び出す関数名となります。ここでは、その関数名を、BMIとしています。
heightとweightと記述しているものを、引数と呼びます。これは、関数内で、ある処理を実行する時に、必要となる値などを指定することが出来ます。ここでは、BMIを算出したい人の、身長と体重がその引数にあたります。
Doubleは予約語の一つで、関数内である処理を実行し、その結果として返却する値のデータ型がDouble型であること示しています。Double型は少数点を含む数値データのことで、戻り値が、少数点を含まない整数値であれば、Long型などを指定します。また、返却される値のことを戻り値と呼びますので、覚えておいてください。
値を返却する関数には、関数宣言の文頭部に、関数の型としてFunctionと記述します。ちなみに、値を返却する必要が無い場合は、Subと記述し、文末部のAs Doubleも必要ありません。エクセルのセルから呼び出す関数は、Function型でなければなりません。
関数宣言の文頭にPublicという記述がありますが、これは関数のアクセス性を示す修飾詞で、これも予約語の一つとなっています。Publicと記述すれば、他のモジュールから呼び出すことが可能になり、Privateと記述すると、他のモジュールからは呼び出せず、同一モジュール内でしか呼びすことが出来なくなります。エクセルのセルから呼び出す場合は、必ず、Public属性でなければ、なりません。
モジュールの先頭に、記述している、Option Explicitステートメントですが、これは、プログラムの記述に厳密さを要求するものであり、これにより、コンパイル及び実行時に、プログラミングの文法エラーを、メッセージ表示してくれるようになります。
関数内の先頭に記述しているDimで始まる1行ですが、これは関数内で演算や処理を行うために、必要なデータを使用するための変数の宣言です。今回の場合は、身長を【m】単位に変換した値を、一時的に格納するためにtmpという名前の変数を宣言しています。余談ですが、私はプログラミングするとき、temporaryの省略として、よく変数名にtmpと名前をつけています。
Option Explicitステートメントを宣言していなければ、この宣言をすることなく、変数を使用することが出来ます。変数名を宣言しなく済むのなら、な～んて思ったりする方も、いると思うのですが、ちょっとした記述ミスに気付きにくくなるデメリットがあります。tmpとして、使用しているものが、途中でtempと記述ミスしたりすると結果が全然違うものになってしまい、ミスに非常に気付きにくくなります。
ですので、私は、必ずOption Explicitステートメントを宣言するようにしています。
関数の最後に記述している以下ですが、これは、処理結果としての戻り値を格納しています。関数名に値を格納することで、戻り値として、関数の呼び出し側に値が返却されるようになります。これにより、エクセルのセルから呼び出した関数に、BMI値を算出した結果が返ります。格納し忘れると0が返ります。
それでは、手順2で作成したBMI関数を実際に、使用してみましょう。まず、表の中から誰でもいいので、BMIを算出したい人を選択します。ここでは、廣瀬 吉太郎さんのBMI(F2セル)を選択して、説明していきます。
廣瀬さんの身長(height)と体重(weight)に該当するセルは、それぞれ、D2、E2セルなので、設定する式は以下となります。
=BMI(D2,E2)
同等の式を全ての人に設定してください。BMIが算出されるはずです。
今回は、BMIをサンプルに説明しましたが、BMI値であれば、関数を使用しなくても、四則演算の式を設定すれば算出できるのですが、あえて、ユーザ定義の関数を作成してみました。自分で作成した関数が呼び出せるということを知ってもらいたかったというのが一番の理由です。
関数を自分でどんどん作成できるようになると、非常に作業が効率的に行えるよう場面も多々あるかと思いますので、これを機会に挑戦してみてください。
サンプルのデータは、私の創作によるものです、サンプルデータに含まれる個人名などの情報は、実在の人物とはまったく関係ありません。
ご意見・ご要望等ありましたら、画面最下部のメールアドレスまでご連絡ください。
