第8回 項目数可変の小計/合計欄の作り方

診断(8)
項目数可変の小計/合計欄の作り方

「先生，お仕事ですよ」
「タバコ屋のミーちゃんが…」
「なに落ち込んでいるんですか。いつものことじゃないですか」
「い，いつものことって，そんな言い方…」
「先生らしくもない。♪別れたら～次の人～じゃないですか。元気出してくださいよ」
「うー，でもなぁ…」
「次の相談者の方，さっきちらっと見たら，とてもきれいな人でしたよ」
「なに！それを早く言いなさい」
「もう！何ですか，その立ち直り具合は」
「いいから，早く呼びなさい」
「次の方，ど～ぞ～」

図1●自作の家計簿 [画像のクリックで拡大表示]

「うーむ，基本的に集計を必要とする表は，フォーマットを統一するのが原則じゃ。そうすれば集計は関数で簡単にできる」
「でも，フォーマットを統一すれば，集計は簡単かもしれませんけど，項目欄を多めにとるので，空欄が多くて無駄に大きな表になりますよね。ファイルのサイズも大きくなるし」
「うむ，確かに，無駄な空欄を嫌う人は結構いるな。表をきっちり埋めたいという気持ちもわからんではない」
「先生，対処できないんですか？」
「馬鹿を言っちゃあいかん。規則性さえあれば，マクロで処理できないことはない，たぶん」
「たぶん？」
「何事にも限界はある。マクロだって万能じゃない。さて，ゲール君，この表に規則性はあるかな？」
「規則性ですか？ そうですねぇ，日付，品名，値段の順に並んでいます」
「うむ，間違いではないが，正解でもない。相談者の方は『集計したい』と言っておる。それを踏まえて，もう一度ようく表を見たまえ」
「そうでしたね。集計に使える規則性と言うと…あ，一日の最後に必ず『小計』の欄があるということですか？」
「そうじゃ！ ただし，今回の場合は，小計の欄が重要なのではなく，『小計』という文字列がキーワードなんじゃ」
「キーワード？」
「そうじゃ。項目数が可変ということは，小計と小計の間隔はまちまちになる。つまり，計算で小計欄の行番号をはじき出すことはできん。したがって，C列のセルを一つずつ取り出して，セルの値が『小計』の場合は一日分の小計を計算するというマクロを作るんじゃ」
「なるほど。で，どこからどこまでという計算の範囲はどうやって出すんですか？」
「いろいろな方法が考えられるが，今回は変数を使おう。一日分の表の先頭と最後の行番号を変数に代入するんじゃ。8月1日は先頭の行番号が3。これは固定じゃな。翌月にシートが替わったとしても，この行番号は変わらん。逆に言えば，変えてはならんのじゃ。これが統一されていないと，マクロは汎用性を失う」
「なるほど，複数のシート，つまり毎月使えるマクロにするんですね」
「その通り。次に最後の行番号じゃが，C列のセルの値を検証して，最初に見つかった『小計』の一つ上のセル，つまり5が一日の最後の行番号になる」
「8月2日の場合は，どうなるんですか？」
「8月1日の『小計』セルの1行下が8月2日の先頭の行になり，2番目に見つかった『小計』セルの1行上が最後の行になる。後は，その繰り返しじゃ」
「じゃあ先生，そろそろ実際のマクロをお願いします」

　リスト1を見てください。最初に，表の一番下の行番号を取得し，変数myLAST_ROWに代入します(1)。この数値は，(3)のステートメントで，ループの上限に使用します。そして小計の計算をする範囲の一番上の行番号を格納する変数myTOP_ROWに数値を代入します(2)。

Sub SYOUKEI()
  Dim i As Long
  Dim myLAST_ROW As Long
  Dim myTOP_ROW As Long
  Dim myBOTTOM_ROW As Long
  Dim myRANGE As Range

  With ActiveSheet
    myLAST_ROW = .Cells(Rows.Count, 3).End(xlUp).Row  '----(1)
    myTOP_ROW = 3  '----(2)

    For i = 4 To myLAST_ROW - 1  '----(3)
      If .Cells(i, 3).Value = "小計" Then  '----(4)
        myBOTTOM_ROW = i - 1  '----(5)
        Set myRANGE = _
          .Range(.Cells(myTOP_ROW, 4), .Cells(myBOTTOM_ROW, 4)) '--(6)
        .Cells(i, 4).Value = WorksheetFunction.Sum(myRANGE)  '----(7)
        myTOP_ROW = i + 1  '----(8)
      End If
    Next i
  End With

  Set myRANGE = Nothing  '----(9)
End Sub

　(3)は，C列の中で，値が「小計」セルを探すループです。カウンタ変数iが4からmyLAST_ROW－1になるまでループします。図1のサンプルの場合，myLAST_ROWの値は32なので，カウンタ変数iの値は，4～31になります。

　(4)では，カウンタ変数iの値をCellsプロパティの行番号に指定して，C列のセルを順番に取り出し，値が「小計」の場合は(5)～(8)のステートメントを実行します。まず，変数myBOTTOM_ROWに「小計」セルの1行上の行番号を代入します(5)。この値が集計する範囲の下側の行番号になります。

　変数myTOP_ROWとmyBOTTOM_ROWを使って，集計する範囲のセルを，オブジェクト型変数myRANGEに代入します(6)。この変数は(7)のステートメントで使いますが，(7)のSUM関数の引数に直接集計するレンジを指定しても構いません。

　その(7)で1日の小計を計算し，「小計」の右のセルに代入します。そして翌日の小計を計算するセルの範囲を特定するmyTOP_ROWに値を代入します(8)。最後に，オブジェクト型myRANGEを空にします(9)。

「先生，(1)のステートメントで使っているEndって何ですか？」
「Endプロパティじゃ。キーボードに矢印キーがあるじゃろ？」
「はい。『→』とか『↑』とかですよね」
「そう，そのキーを押す前に『End』を押して，次に矢印キーを押すとどうなる？」
「表の右端とか，上端とかに移動します」
「そう，その『End』キーと同じ働きをするのがEndプロパティじゃ。引数には移動する方向を指定する」
「納得！ もう一つ，(7)のステートメントで使ってるSum関数なんですが，これはワークシートで使うSum関数と同じ働きをするんですか？」
「いや，これはワークシートで使うSum関数そのものなんじゃ」
「そのもの？」
「そうじゃ，Sum関数の前にWorksheetFunctionと書いてあるじゃろ？」
「はい」
「WorksheetFunctionプロパティは，VBAの中でワークシート関数を使うためのコマンドなんじゃ。この関数の引数には，関数の対象となるオブジェクトを指定しなければならない。ここで使っているSum関数の引数は，合計する対象となるRangeオブジェクトじゃ。Rangeオブジェクトのアドレスを渡すとエラーになるから注意するのじゃぞ」
「わかりました。先生，最後の合計を計算する処理は？」
「うむ，合計を計算するマクロは，別に作成した。ループと条件分岐の部分は，小計のマクロと同じじゃから省略する」

　合計を計算するマクロは，リスト2のようになります。

Sub GOUKEI()
  Dim i As Long
  Dim myLAST_ROW As Long
  Dim mySUM As Integer

  With ActiveSheet
    myLAST_ROW = .Cells(Rows.Count, 3).End(xlUp).Row

    For i = 4 To myLAST_ROW
      If .Cells(i, 3).Value = "小計" Then
        mySUM = mySUM + .Cells(i, 4).Value  '------(1)
      End If
    Next i

    .Cells(myLAST_ROW, 4).Value = mySUM  '------(2)
  End With
End Sub

　変数mySUMに，D列の小計セルの値を加算します(1)。ループが終了すると，変数mySUMの値は，全小計の値を合算した合計になります。それを合計のセルに代入します(2)。

「先生，小計と合計のマクロをどうして別々にしたんですか？」
「それはじゃな，ゲール君にも理解できるようにじゃよ」
「じゃあ，小計のマクロ（リスト1）に，リスト2の(1)と(2)のステートメントを入れ込めば，小計と合計が同時にできるんですね？」
「うむ，変数mySUMの宣言文も忘れずに」
「はい！ ところで先生，この表は，小計の欄が他の項目の欄に埋没して見分けにくいですね」
「なるほど，その通りじゃな。ものはついでじゃ，小計の欄の文字を太字にして，背景を薄い黄色にするステートメントを追加するとしよう」

　リスト1とリスト2をそれぞれ，リスト3とリスト4のように変更しました。赤字の部分で，指定したセルの文字を太字に，背景を薄い黄色に塗りつぶしています。実行結果は，図2のようになります。

Sub SYOUKEI()
  Dim i As Long
  Dim myLAST_ROW As Long
  Dim myTOP_ROW As Long
  Dim myBOTTOM_ROW As Long
  Dim myRANGE As Range

  With ActiveSheet
    myLAST_ROW = .Cells(Rows.Count, 3).End(xlUp).Row
    myTOP_ROW = 3
  
    For i = 4 To myLAST_ROW - 1
      If .Cells(i, 3).Value = "小計" Then
        .Range(.Cells(i, 3), .Cells(i, 4)). _
          Font.Bold = True
        .Range(.Cells(i, 3), .Cells(i, 4)). _
          Interior.ColorIndex = 36
        myBOTTOM_ROW = i - 1
        Set myRANGE = _
          .Range(.Cells(myTOP_ROW, 4), .Cells(myBOTTOM_ROW, 4))
        .Cells(i, 4).Value = WorksheetFunction.Sum(myRANGE)
        myTOP_ROW = i + 1
      End If
    Next i
  End With

  Set myRANGE = Nothing
End Sub

Sub GOUKEI()
  Dim i As Long
  Dim myLAST_ROW As Long
  Dim mySUM As Integer

  With ActiveSheet
    myLAST_ROW = .Cells(Rows.Count, 3).End(xlUp).Row

    For i = 4 To myLAST_ROW
      If .Cells(i, 3).Value = "小計" Then
        mySUM = mySUM + .Cells(i, 4).Value
      End If
    Next i

    .Cells(myLAST_ROW, 4).Value = mySUM
    .Range(.Cells(myLAST_ROW, 3), .Cells(myLAST_ROW, 4)). _
      Font.Bold = True
    .Range(.Cells(myLAST_ROW, 3), .Cells(myLAST_ROW, 4)). _
      Interior.ColorIndex = 35
  End With
End Sub

図2●色付けをした家計簿 [画像のクリックで拡大表示]