第143回目 ユーザー関数(条件付SUMPRO関数、その2)

○第143回目　ユーザー関数(条件付SUMPRO関数、その2)
　
　マクロの中での記述
　a　=　条件付SUMPRO_02("D20,A42,A20,42,B20,C21")
または、
　a　=　条件付SUMPRO_02(D18)　‘D18には、D20,A42,A20,42,B20,C21が入っている。
　
　先頭行、末尾行の記述
Function　条件付SUMPRO_02(ByVal　NN)　As　Double
　【中味】
End　Function

　中味の記述
　
　パラメータ='D20,A42,A20,42,B20,C21
　まずはパラメータの説明です。

　D20…セルの座標です
　A42…セルの座標です。場合によっては数値が入ってきます
　A20…セルの座標です
　42…行数です
　B20…セルの座標です
　C21…セルの座標です。省略された場合は、機能的には、B列に対するSUM関数となります。

　D20のセルにある年齢から
　A42のセルにある年齢までについて
　B20からはじまる項目と
　C21からはじまる項目とを掛け合わせた総和を求める。
　(参照すべき基準年齢は、A20からA42にはいっている)

　Range().Selectが使えませんので、違う方法で列･行を求めます。
　流れです。
　1.パラメータを分解します(mm(i))
　2.座標の場合、それぞれの項目から、列･行を求めます(mm_retu(i)、mm_gyo(i))
　3.列記号は列番号に変換します。(mm_retu(i)の置き換え)
　4.数値情報の場合、セルの内容を求めます(XX,XX9)
　5.積和をとる範囲(最初と最後の行数)を求めます(gyo91、gyo99)
　6.関数式を作ります

　1.パラメータを分解します(mm(i))
　nn0　=　NN
　for　i　=　1　to　5
　　p1　=　InStr(NN,　",")
　　mm(i)　=　Left(NN,　p1　-　1)
　　NN　=　Mid(NN,　p1　+　1)
　next
　mm(6)　=　NN
　If　mm(6)　=　""　Then　flag1　=　1

　2.座標の場合、それぞれの項目から、列･行を求めます(mm_retu(i)、mm_gyo(i))
　ii0　=　6
　If　flag1　=　1　Then
　　ii0　=　5
　　mm_retu(6)　=　""
　　mm_gyo(6)　=　""
　End　If
　For　i　=　1　To　ii0
　　mm(i)　=　UCase(mm(i))
　　If　Left(mm(i),　1)　Like　"[0-9]"　=　True　Then　'一桁目が数字
　　　mm_retu(i)　=　""
　　　mm_gyo(i)　=　Val(mm(i))
　　ElseIf　Mid(mm(i),　2,　1)　Like　"[A-Z]"　=　True　Then　'二桁目が英字
　　　mm_retu(i)　=　Left(mm(i),　2)
　　　mm_gyo(i)　=　Mid(mm(i),　3)
　　Else　'二桁目が数字
　　　mm_retu(i)　=　Left(mm(i),　1)
　　　mm_gyo(i)　=　Mid(mm(i),　2)
　　End　If
　Next　i

　3.列記号は列番号に変換します。(mm_retu(i)の置き換え)
　　省略
　4.数値情報の場合、セルの内容を求めます(XX,XX9)
　　変数を数値に置換えて、通常の変数名の変数に入れる
　パラメータ='D20,A42,A20,42,B20,C21
　　retu00　=　Val(mm_retu(1))　集計したい最初の参照値が入っているセルの列番号
　　gyo00　=　Val(mm_gyo(1))　　集計したい最初の参照値が入っているセルの行番号
　　retu09　=　Val(mm_retu(2))　集計したい最後の参照値が入っているセルの列番号　数値で入っていた場合、ここが0になります
　　gyo09　=　Val(mm_gyo(2))　　集計したい最後の参照値が入っているセルの行番号
　　retu11　=　Val(mm_retu(3))　集計の参照となる先頭セルの列番号
　　gyo11　=　Val(mm_gyo(3))　　集計の参照となる先頭セルの行番号
　　retu19　=　retu11　　　　　　集計の参照となる最後セルの列番号
　　gyo19　=　Val(mm_gyo(4))　　集計の参照となる最後セルの行番号
　　retu21　=　Val(mm_retu(5))　集計項目1の先頭セルの列番号
　　gyo21　=　Val(mm_gyo(5))　　集計項目1の先頭セルの行番号
　　retu31　=　Val(mm_retu(6))　集計項目2の先頭セルの列番号
　　gyo31　=　Val(mm_gyo(6))　　集計項目2の先頭セルの行番号
　　gyo_cnt1　=　gyo19　-　gyo11　+　1　集計する項目数

　　XX　=　Cells(gyo00,　retu00)　集計したい最初の参照値
　　If　retu09　=　0　Then
　　　XX9　=　gyo09　集計したい最後の参照値。数値の場合
　　Else
　　　XX9　=　Cells(gyo09,　retu09)　集計したい最後の参照値。座標の場合
　　End　If

　5.積和をとる範囲(最初と最後の行数)を求めます(gyo_cnt91、gyo_cnt99)
　　For　i　=　1　To　gyo_cnt1
　　　If　XX　=　Cells(gyo11　+　i　-　1,　retu11)　Then　Exit　For
　　Next
　　gyo_cnt91　=　i　'先頭からの相対的な行数
　　For　i　=　1　To　gyo_cnt1
　　　If　XX9　=　Cells(gyo11　+　i　-　1,　retu11)　Then　Exit　For
　　Next
　　gyo_cnt99　=　i　'先頭からの行数

　6.関数式を作ります
　本体のSumProduct関数を使用します。
　If　flag1　=　1　Then　'1項目しか指定していない場合
　　条件付SUMPRO_02　=　Application.WorksheetFunction.Sum(Range(Cells(gyo21　+　gyo_cnt91　-　1,　retu21),　Cells(gyo21　+　gyo_cnt99　-　1,　retu21)))
　Else
　　条件付SUMPRO_02　=　Application.WorksheetFunction.SumProduct(Range(Cells(gyo21　+　gyo_cnt91　-　1,　retu21),　Cells(gyo21　+　gyo_cnt99　-　1,　retu21)),　Range(Cells(gyo31　+　gyo_cnt91　-　1,　retu31),　Cells(gyo31　+　gyo_cnt99　-　1,　retu31)))
　End　If

　こんなものでしょうか。

　ここでこんな問題が出てきました。
　仕事ではたまにシミュレーション系のものがあります。その場合は、諸々の機能が複数年必要であり、通常は計算式のコピーで対応可能です。
　しかし、この関数はパラメータとセットになっています。これが単純にコピーできないのです。パラメータ(D20,A42,A20,42,B20,C21)は文字ですからコピーしても座標は変わりません。ここでいうA列は変わらなくてもいいのですが、他のものは変わってほしいです。
　何とかならないのでしょうか。

第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

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

　できました

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

第141回目 列･行の操作、若干の余興も含む(その3)

○第141回目　列･行の操作、若干の余興も含む(その3)


列･行の並べ替えです。
こういう事例があります。
すでに、年度別(3年度)にみたグループ別(3グループで合計を入れて4グループ)の数値を列記した表があります。これらは横に展開されていて、下には項目(20項目ほど)が展開されています。
これはそのままとして、これから、グループ別にみた年度別の表にしたというニーズです。
　　16年度　　　　…
A　B　C　合計　…
　　　　↓
　　　 A　　　　　…
16　17　18年度　　…

　このような事例を一瞬(パラメータの設定を除く)で処理してしまおうというものです。
　パラメータの設定はそれほど難しくありません。表の上及び左に行、列をつくり、そこに順番を示すパラメータを入れるのです。入らない列は並べ替えの対象外です。
　(記述を簡単にするために列の並べ替えを前提とします)
　眼目は二つ。
　一つ目は、今回は2列ではないので、並べ替えたい列の計算式情報をすべて配列変数に格納します。
　二つ目は、パラメータを並べ替えることです。
　一つ目は前回の機能を複数繰り返すことになりますので、問題はありません。ただし、複数列対象なので、配列変数を二次元のものにしてください。パラメータが入っている列のみを対象として、計算式情報を取得していきます。
　パラメータを含めた表全体を指定してから実行
　1.指定範囲の場所情報を取得する
　2.列のパラメータを取得する。1以上99999以下を対象とする(文字が入っている列は対象外)
　3.パラメータをソートする
　4.一番目のパラメータを持つ列を探し、その列の計算式情報を配列変数の一番目に格納する。
　　二番目以降繰り返す。
　5.物理にみて最初の並べ替え対象列に、配列変数の一番目の計算式情報を移植する。
　　二番目以降繰り返す。
　(6.行でも同じことを繰り返す)

　2.列のパラメータを取得する。
　1から99999までの数値が入っている列を対象とします。
　対象となる列数(retu_cnt9)を求めます。
If 0 <>
ww(j) = Cells(gyo1, retu1 + i)
j = j + 1
End If

　3.パラメータをソートする
パラメータはww(i)に入っています。
対象列の数は、retu_cnt9となります。

　4.一番目のパラメータを持つ列を探し、その列の計算式情報を配列変数の一番目に格納する。二番目以降繰り返す。
For i = 1 To retu_cnt9 '処理対象列数だけ繰り替えす
For j = 0 To retu_cnt - 1 'i番目に該当する列かどうか
If Cells(gyo1, retu1 + j) = ww(i) Then Exit For
Next
For k = 1 To gyo_cnt - 1 '処理対象列の計算式情報を取得する
shiki(i, k) = Cells(gyo1 + k, retu1 + j).Formula
Next
Next i

　5.物理にみて最初の並べ替え対象列に、配列変数の一番目の計算式情報を移植する。
　　二番目以降繰り返す。
j = 1
For i = 0 To retu_cnt - 1 '表の列数分繰り返す
If 0 <>
For k = 1 To gyo_cnt - 1 '計算式情報の移植
Cells(gyo1 + k, retu1 + i) = shiki(j, k)
Next
j = j + 1
End If
Next
　同様に行についても処理してください。直列に処理すればいいです。

　上記の例では、次のようにパラメータを設定します。
11 21 31 41 12 22 32 42 13 23 33 43
　　16年度　　　　 　　17年度　　 　　18年度
A　B　C　合計　　 A　B　C　合計　　 A　B　C　合計

見出しが結合セルになっている場合は、結合を解除してから処理してください。そのほうが無難です。
見出し等の修正は処理後にしてください。

【処理直後のイメージ】

16年度17年度18年度
A 　A 　A 　B　B　B　C　C　C　合計　合計　合計

　※B以降の年度見出しはもともとないので、でてこないと思います。
見出し部分に関して行の並べ替えのパラメータを入れておくと、その後の処理が一手間なくなります。

A 　A 　A 　B　B　B　C　C　C　合計　合計　合計
16年度17年度18年度

　となります。
　
　注意点
　このマクロは指定したエリアのセルを移動させてしまいますので、そのエリアの中のセルを参照する計算式がおかしくなって、意図しない計算結果となってしまいます。
　表の中での計算式、表の外での計算式で、表の中のセルを参照しているものについては、あとで修正する必要がありますので注意してください。　
　上の例では、移動後の合計の計算式は、表内のセルを使用していると当てになりません。

第140回目 列･行の操作、若干の余興も含む(その2)

○第140回目　列･行の操作、若干の余興も含む(その2)


比較的簡単な、列･行の逆転をやります。
記述が面倒になりますので、用語として、"列･行"を"列"で代表させます。

　このマクロは範囲を指定してからはじめます。その範囲とは、逆転したい範囲です。つまり、2列Ｎ行となります。一列だけ指定して、その右隣の列を逆転させるという指示の仕方もありますが、自分の意思で逆転したい範囲を明示したほうがいいと思います。
　2列2行の場合は、ダミー行を挿入して3行にするなど、工夫してください。

　流れです。どちらが2であるかで方向を判断します。
　1.指定範囲の座標情報を取得する。逆転の方向は指定範囲の数が2のほうをとる。
　2.最初の1列目から、計算式情報(ない場合は数値情報)を配列変数に格納します。
　3.2列目の計算式情報を1列目に移します。
　4.格納してある配列変数の内容を、2列目に移します。
　眼目は計算式情報を取得して移植するところにあります。

　２.の部分ですが、次の記述で、計算式がない場合は値が取り込まれます。
ww(i) = Cells(gyo1 + i * yoko, retu1 + i * tate).Formula
列の逆転という前提ですので、yokoとtateには、それぞれ1と0が入っています。

　3.の部分です。
Cells(gyo1 + i * yoko, retu1 + i * tate) = Cells(gyo1 + i * yoko + tate, retu1 + i * tate + yoko).Formula
入れ先は計算式の指定をしなくても大丈夫でした。
ここでの"+ tate"と"+ yoko"の使い方は邪道でしょうね。

4.の部分です。
Cells(gyo1 + i * yoko + tate, retu1 + i * tate + yoko) = ww(i)
　なお、iは0から始まっています。

＝⇒これを使う事例はどのくらいあるのでしょうか。
　・エクセルで表を作り、一通りできた後で、よくよく考えてみれば、この部分を逆にしておいたほうが体裁がいいな、とか、体系が崩れているのここを逆にしたいな、ということはあります。問題の頻度は…、それなりにあるかもしれない、といった程度でしょうか。
　複雑な計算式が絡まない場合であれば、通常の操作で気にしないでやっているのではないでしょうか。複雑な計算式が絡むとそうはいきません。多分、あきらめてしまう場合もあるのではないでしょうか。それゆえ記憶に残らないという場合もあると思います。

　実は、列の逆転は、既存のマクロで可能なのです。すなわち、計算式を文字化してコピーするという機能を使うのです。でもこのマクロのほうがはるかにすっきりいきます。
　一時的にどこかに計算式を移植しておいて、該当する箇所に計算式を移植していけば、よいのです。