○第122回目 計算式作成のお手伝い
【第99回目 計算式の加工(DIV/0対応、端数処理とif文)も参照してください】
まずは、"計算式作成のお手伝い"の意味から。
エクセルの作業を行っていたところ、次のような計算式を作成する必要が出てきました。
=SUMPRODUCT(別のシート1!BB4:BB23,別のシート1!BA38:BA57)
(※SUMPRODUCTとは、各要素を掛け算した結果を足したものという意味の関数です)
この計算の作成は若干面倒なので、何か工夫はないかと考えていました。
改良すべき点は、
1.最初のセルの範囲の指定は必要であるが、二番目以降は先頭のセルのみ指定する。
2.掛ける要素が横とか縦に既に並んでいる場合は、全体の範囲を指定する。あわせて続けて掛ける要素の指定ができる。
3.次の処理でのカーソルの位置は、原則として前の処理のカーソルの位置とする。
4.シートの移動も可能とする。
5.一列指定(又は一行指定)の場合は、SUMPRODUCTの変わりにSUMとなる。
というくらいでしょうか。
※4.については、前回参照。5.についてはおまけ的要素なので省略。
さて、それでは、
マクロの実行は、既に最初に参照する範囲が指定されているところからです(計算式を入れる場所は一番最後に指定します)。
0.最初の処理
シート名、当該シート番号を取得しておきます。
1.最初の範囲の取得
これは定番の処理ですが、ちょっと問題なのは、列行とも複数の指定であった場合、縦に掛けるのか横に掛けるの判断です。正解はないのですが、指定した行数･列数が小さいほうを掛ける方向とします。例えば2行10列を指定していたら、下の方向に掛けていきます。
例えば、EB9:EK10と指定した場合。
xxx=SUMPRODUCT(EB9:EK9,EB10:EK10)
EP9:EQ17)と指定した場合。
xxx=SUMPRODUCT(EP9:EP17,EQ9:EQ17)
2.SUMPRODUCTの構成要素をつくる
1.での指定が1列または1行であれば、要素は座標そのままとなりますが、複数の指定である場合は、それをわける必要があります。また、最後に計算式を入れる場所がわかりませんので、シート名を付加しておきます。
座標に、『'シート名'!』というものを付加してください(『 』の中)。
範囲の分割では、列が問題になりますが、列記号を列番号に変換して、列番号ごとに一つの範囲をつくり、列番号を列記号に変換して、要素とします。
3.次の要素にかかるシートの指定
ループ処理になるので、続けて範囲を指定するかどうかを聞きます。ループを抜け出すのは、シートの指定後であり、4の直前となります。
4.次の要素の先頭座標の指定
カーソルで先頭座標を指定します。
5.SUMPRODUCTの構成要素をつくる
この段階の指定は、1列または1行なので、指定された座標から構成要素をつくります。
ここでも列の場合は、列記号から列番号、列番号から列記号へと変換します。
3.に戻る
6.計算式の貼付け
計算式を貼付ける座標を指定します(ブック、シートはこの処理の直前に指定されています)。
指定後、計算式を作り貼付けます。
ここで問題は、シート名がついているということです。したがって、この段階で、計算式を入れる場所と等しいシート名は削除します。
instr関数で当該シート名があるかどうか見てください。あったら削除です。なお、純粋なシート名には、'とか!はついていませんので注意してください。
最初は、貼付けた時、計算式だとエラーになる場合が考えられますので、計算式としてではなく、文字列として貼付けてください。=の頭に何か文字をつければ計算式ではなくなります。
※次は詳細に掘り下げます。
