第142回目 ユーザー関数

○第142回目　ユーザー関数

　便利なユーザー関数を作りましょう。

＝⇒ユーザー関数？それは何でしょうか。

　SUM関数と同じようなもので、処理内容を自分で作れるというものです。

＝⇒作った後の使い方は？

　SUM関数と同じように、セルに計算式として設定できます。例えば、与えられた数値まで1からの総和を求めるというユーザー関数を作ったとします。名前として、SUM_from1()としておきます。

　セルに、=SUM_from1(10)と入れますと、55という答えが表示されます。

　()の中は他のセルの座標でもかまいません。

＝⇒作り方は今までのマクロと違うのですか

　ほぼ同じです。SUBの変わりにFUNCTIONで囲んでやります。

　FUNCTION SUM_from1(NN) as Double

：

SUM_from1 = ……

　End FUNCTION

【具体例】

　Function 加重按分01(ByVal nn1, nn0, mm, keta As Double) As Double '；MJ013__fx

'nn1は個別値,nn0は合計値,mmは按分したいもの,ketaは四捨五入した時の桁

加重按分01 = Round(mm * (nn1 / nn0), keta)

End Function

　使えるコマンドに制限があります。Cells(lr007,lc026+jj*ll006+4).Selectはつかえませんでした。

＝⇒セルに設定する計算式以外にも使えるのですか。

　マクロの中で使えます。AA=SUM_from1(NN)というように使います。

　マクロ側からNNを渡して、計算結果をFUCTION側からマクロに渡すという流れです。

　関数が違う文字にある場合は、文字名を頭につける必要があります。

　AA = Module13.SUM_from1(NN)とか。

　

＝⇒これまでに便利なものはありましたか。

　列番号を列記号に変換するという関数は便利でした。

　他にも二、三あった気がします。

＝⇒サブルーチンとはどこが違うのですか。

　大仰ではないと点でしょうか。もちろんサブルーチン(SUBという位置づけ、変数はパブリック変数、モジュール変数とすることが必要)でも可能です。処理結果が複数の場合は、FUNCTIONではできません。

　メリットは、セルに書き込めるという点ではないでしょうか。つまり、通常のエクセル処理の中で処理可能という点でしょう。

＝⇒今回紹介してくれるものは、具体的にはどんなものですか。

　条件付SUMPRO関数です。SUMPRODUCT関数を思い浮かべてください。

　年齢(20-60歳)、人数、給料(平均)があります。

　40歳から60歳までの給料の総額を求めるのは、通常SUMPRODUCT関数を使いますね。

　このとき、範囲を自由に与えるということです。

　イメージとしては、

　SUMPRODUCT(bNN:b60,cNN:c60)　ということでしょうか。

　この場合、40歳を他から与えれば、SUMPRODUCT(b40:b60,c40:c60)　ということになります。

　

　SUMPRODUCT関数は2項目必要となりますが、1項目の指定であった場合は、SUM関数の機能とします。

＝⇒もっとイメージを得たいと思います。パラメータとして何を与えるのでしょうか。

　パラメータは、"D18,D19,A20,60,B20,C21"と与えます。

　この内容をセルに書き込んでおき、そのセルを参照することでも可能です。

　このパラメータは次のことを意味します。

'D18のセルにある年齢から

D19のセルにある年齢までについて

B20からはじまるデータセットの中のセルと

C21からはじまるデータセットの中のセルとを掛け合わせた総和を求める。

(参照すべき年齢は、A20からA60にはいっている)

＝⇒4項目目の60は何を意味しているのですか。A60とはどんな関係があるのですか

　これは、データセットの範囲を示すもので、行数を示します。

　データセットは、A20から始まって、A60まであるということです。

　A60の60と60が同じですが、これは、ある年齢から最後までの積和を求めるということを意味します。

＝⇒B20とC21が違うのはどういうことですか

第一項目目は基準年齢と行があっているはずですが、第二項目目はそうとも限りません。そこで第一項目と第二項目の先頭の位置が変わっている場合も可能ということです。通常は一緒です。

　また、パラメータの別の与え方として、

　"D18,60,A20,60,B20,C21"　も可能としました。

　2項目目の60は、この例では60歳を表しています。

ここまでのことを総合して、おおざっぱにいえば、指定された2つのセルに40、60と入れれば、40から60歳までの給料の総額が出るし、30、55と入れれば30から55歳までの給料の総額が出るということです。それぞれの場合の人数を出したかったら、一項目のみの指定をすればいいのです(すなわち、"D18,D19,A20,60,B20,")。

　問題点

　参照セルの内容が変わっても、自動計算されない。

＝⇒

　先頭に

　Application.Volatile　を入れることで自動計算されます。

Volatileメソッド　【注：WebMaster】

ユーザー定義関数を自動再計算関数にする。この関数にしておくと、セルで計算が行われるたびに再計算される。

構文

expression.Volatile(Volatile)

expression　　Applicationオブジェクトを表すオブジェクト式を必ず指定する

Volatile　　　True ： 自動再計算関数（規定値）、　False ：非自動再計算関数

ユーザー定義関数の中（冒頭）に記述する。

　

【参照先】

　http://www.asahi-net.or.jp/~ZN3Y-NGI/YNxv926.html

　中身はこんなもので、関数としては動きますが Excel2000で動かすと、再計算ができないんです。

　Micrsoftサポートには、「ユーザー定義関数内で名前作成した場合の再計算について」という題で、index関数を使うとできないみたいな感じなのですが、何かいい方法はないでしょうか？

　Answer　　 2001.6.6　ムラジロ～

　中身は見ていませんが

Application.Volatile

　を頭に入れてもダメでしょうか？

　できました

　ムラジロ～さんありがとうございました。もっと勉強します。