たとえば、100店舗からなる商店街の構成員のデータがエクセルのブックに構築されているとします。
まじめに考えると、For・Next のループを駆使して力づくの計算をさせることになりますが、ワークシート上では
=C11:L15+C18:L22
といったような計算式が可能なので、これを利用してうまくできないものか、とやってみました。
スピル(動的配列数式)に対応していない、365または2021以前の Excel ではちょっと手間がかかりますが、とにかくやってみました。
まず、いずれにしてもワークシートをスキャンすることになるので、計算対象となるワークシートを判定する必要があります。ここでは、"A1" セルに "*" がはいっているワークシートが計算対象だということにして、それ以外はスルーすることにしました。このマークは、SheetStamp 定数で定義しておきます。
各シートでの集計対象の行列のサイズを決めておきます(ここでは、5行10列としました)。その位置を示すために、行列の左肩のセルに "DataTop" という名前を付けました。これらも、Public 定数として定義しておきます。
次に、合計結果を集計・表示するワークシートとして、次のようなものを用意します。ここでは、その名前を "SumupMatrix" としました。集計対象と同じサイズのエリアを3個用意して、それぞれの左肩のセルに "Total" "Storage" "Source" という名前をつけます。
Totalで始まるエリアには、後で紹介する SumupMatrix() によって Storage と Source とを加算する動的配列数式が書き込まれます。
ただし、古い Excel の場合はこれが機能しないので、あらかじめこの部分をコメントアウトし、 totalRange 全体を選択したうえで、"=C11:L15+C18:L22" というような、Storage と Source を合計する数式を数式バーに記入し、Ctrl + Shift + Enter キーで確定しておく準備が必要です。
古い Excel に対応するためには、もう少し研究の余地がありそうです。今後の宿題にしたい。
SumupMatrix() が対象となるワークシートをスキャンして、合計を Total エリアに記載します。ワークシート SumupMatrix の Activate イベントでこの関数を呼び出すようにしておけばいいでしょう。
集計作業は、次のように行われます。
(1) Source に 対象ワークシートのデータを転記します。Source と Storage との合計が Total になっています。
(2) Total の値を Storage に転記します。次に Source に新たな値が転記されると、Total にはそれが追加加算されます。
(3) (1)(2) を繰り返す。
最後に、Source をクリアして終了。
標準モジュール SumupMatrix()
'----- 集計対象となるワークシート '対象として有効なワークシートの印 Public Const SheetStamp = "*" '集計するデータ範囲の先頭セル名 Public Const DataTopCellName = "DataTop" '----- 集計する行列のサイズ Public Const RowCount = 5 Public Const ColumnCount = 10 '-----合計欄を作成するワークシート名とセル名 Private Const sumupSheetName = "SumupMatrix" Private Const totalCellName = "Total" Private Const storageCellName = "Storage" Private Const sourceCellName = "Source"
'----- 個別ワークシートの合計を作成する Public Sub SumupMatrix() Dim sheet, totalCell, totalRange, storageRange, sourceRange With sumupSheet Set totalCell = .Range(totalCellName) Set totalRange = expandRange(totalCell) Set storageRange = expandRange(.Range(storageCellName)) Set sourceRange = expandRange(.Range(sourceCellName)) End With '******** 古い Excel の場合は、次の行をコメントアウトして 'あらかじめ計算式を手で入力しておく ****************** setFormula totalCell, storageRange, sourceRange 'storage と source を 0 にしておく storageRange.Clear sourceRange.Clear For Each sheet In ThisWorkbook.Worksheets If sheet.Cells(1, 1) = SheetStamp Then sourceRange.value = expandRange(sheet.Range(DataTopCellName)).value storageRange.value = totalRange.value End If Next sourceRange.Clear End Sub '----- 合計を記載するワークシート Private Property Get sumupSheet() Set sumupSheet = ThisWorkbook.Worksheets(sumupSheetName) End Property '----- 行列の統一されたサイズに拡張したセル範囲を取得 Private Property Get expandRange(topRange) Set expandRange = topRange.Resize(RowCount, ColumnCount) End Property '----- 加算する式を合計欄の先頭に記載する Private Sub setFormula(totalCell, storageRange, sourceRange) Dim storageAddress, sourceAddress, formulaStr storageAddress = storageRange.Address(False, False) sourceAddress = sourceRange.Address(False, False) formulaStr = "=" & storageAddress & "+" & sourceAddress totalCell.Formula2 = formulaStr End Sub
他に流用しやすいよう、定数やプロパティに気を使ったので、少し行数を稼いでしまいましたが、手はずそのものはシンプルで、何より高速です。
ちなみに、7行40列のサイズの表を50のワークシートに入れて集計するようにしましたが、合計のワークシートに移動しようとシートタブをクリックすると、体感的にはマウスボタンを離した瞬間に集計が終わっています。