【Excel VBA】行の位置が変わる特定のセルを基準にして計算範囲を求める

知り合いの方から Excel VBA について質問をいただいたので、ブログネタにすることにしました。

質問内容

質問内容は次の通りです。

A 列に「合計」と出てきたら、その行と同じ B 列のセルに B 列の合計値を入れたい(C 列、D 列・・・も同様にしたい)。
でも「合計」の行が毎回違うので、どのようにすればいいのか分からない。

たとえば下記のようなデータのことですね。下記の表は、あるコンビニの1店舗を訪れた客ごとに何を何点買ったかというデータをまとめたもの(という想定)です。

このようなデータが20以上の支店から毎日、コンビニの本部長のところに送られてきて、品物の種類ごとの合計値を本部長が計算しなければならないとします。

本部長は VBA で合計値を求めるプログラムを作ろうとしましたが、支店ごとに客の数(行数)は違いますし、同じ支店でも客数は日によって変わります。つまり、データごとに計算範囲が変わるし、合計値を入力する行も変わってしまうので、本部長から「どうすれば良いの?」とご相談を受けた、と仮定します。

特定のテキストの行番号を調べて、縦方向の計算をする

やり方はいろいろ考えられますが、プログラムで「合計」テキストの行の位置を探して対応するコードの例を下記に掲載します。

 

ご自分の環境(データの状態)に合わせて設定を変えるところに★を入れておきました。

実行すると下記のように、合計値が書き込まれます。これで、データによって客の数(行数)が変わっても、同じ VBA プログラムで対応できます。

シート上の「合計」のテキスト位置を求めるには、他には Find を使う手もありますし、
または「合計」を探さなくても、データの終了行まで(そして終了列まで)をプログラムで判定して計算する方法も考えられますね。

上記のコードでは、テキスト位置の検索方法だけでなく、他にもいくつか使えるテクニックが入ってると思いますので、じっくり(のんびり)眺めてご参考にしてみてください。

不明点があれば遠慮なく聞いてください。またのご相談、お待ちしています!(^^)/

特定のテキストの行番号を調べて、横方向の計算をする

(2010年10月17日追記。鈴木様、コメント欄からありがとうございます!^^)

本部長から追加の質問がありました。

「お客様それぞれの合計購入商品数」も出したいのだが?

とのことです。つまり、今度は表の横方向の計算をすることになります。

前述のプログラムコードで必要な行番号や列番号の多くがすでに取得できているので、それを利用すれば楽ですね。「合計」のテキストの行番号を調べるコードも既にできているので、その行の直前の行まで計算式をセットするようにしてみました。

54行目からコードを追加したのでご参照ください。

 

これを実行すると下記のようになります。

機能をサブルーチンに分ける

本部長からまたご連絡をいただきました。(2010年10月17日追記)

お弁当からお菓子までの合計を「食品合計」として I 列、お酒から雑誌までの合計を「その他」として J 列に入れるにはどうしたら良いか?

というご質問です。

基本的には前述のコードの「お客様それぞれの合計購入商品数」の部分(54行目~67行目)と同じことをあと2回やれば良いわけですが、似たような処理のコードを3回も同じ関数内に書くのはあまりスマートなやり方ではないですね。そういう書き方をし続けるとコード全体が見辛くなり、あとあとメンテもやりにくくなります。

というわけで、そろそろ前述のコードをいくつかのサブルーチンに分けつつ、今回の本部長のご希望に沿うよう対応してみます。

コードをサブルーチンに分けると下記のようなメリットがあります。

  • コード全体が整理されて見やすくなる
  • 1つの関数の行数が短くなり、処理内容を把握しやすくなる
  • 似たような処理を何回も記述せずに済む
  • 特定の処理を行う関数を部品として再利用できるようになる(別のプログラムを作るときにも利用できる)

だいたいこんなところかな? サブルーチンに分割する際の注意点として、メインとなるルーチンを見るだけで全体の処理の流れを把握できるようにすることが大切です。

サブルーチンに分割して、本部長のご意向を反映したコードを下記に記載します。

 

このコードを実行すると下図のようになります。

 

分からないところがあれば聞いてください。また何かありましたら遠慮なくどうぞ!(*^-^*)

 

5
コメントの投稿

avatar
  購読する  
新しい順 古い順 評価順
通知を受け取る対象
鈴木
ゲスト
鈴木

質問者です。ありがとうございます!
なんとな~く理解できました(汗

応用しようと思って、列と行を変えた場合を考えてみたのですが、
まったく歯が立ちませんでした。

例えば上の表の場合、お客様それぞれの合計購入商品数を出したいです。
単純にH列にB~Gの合計数が入る式を入れると、
お客様があたってない「合計」以降の行のH列にも0と入ってしまいます。
数字がある行だけに合計購入商品数を入れる方法を教えていただけますか。