Home » ExcelVBA Rangeオブジェクト » セル数式 » 3D集計・3D参照する数式を入力するExcelマクロ

3D集計・3D参照する数式を入力するExcelマクロ

対象:Excel2007, Excel2010, Excel2013

「3D集計する数式をマクロから入力するにはどうしたらいいのでしょうか?」
といった、Excel VBA(Visual Basic for Applications)に関するご質問をいただくことがあります。

例えば、4月から9月までの月ごとの6枚のワークシートが並んでいるときに、集計用シートに、
「=SUM('4月:9月'!A1)」
という3D参照した数式を入力すれば、

3D集計・3D参照する数式を入力するExcelマクロ

「4月」シートから「9月」シートまでのA1セルの値の合計を計算できます。

この数式を入力するマクロをどのよう書けばいいのか、疑問を持った方からいただくご質問です。

実際に使うマクロではいろいろな要望がありそうですが、ここでは参考になりそうなマクロを2つご紹介しておきます。

[スポンサードリンク]

集計用シートが後ろにある場合に3D参照する数式を入力するサンプルマクロ

一つ目は、3D参照する集計用シートが、集計元となる個々のシートより後ろにある場合のサンプルマクロです。


Sub アクティブセルに3D参照の数式を入力する1()

 Dim addr As String
 Dim ws_a As String
 Dim ws_z As String

 addr = ActiveCell.Address(False, False)

 ws_a = Sheets(1).Name
 ws_z = ActiveSheet.Previous.Name

 ActiveCell.Formula = _
  "=SUM(" & ws_a & ":" & ws_z & "!" & addr & ")"

End Sub

上記のマクロを実行すると、1枚目(先頭)のシートから、アクティブシートの左隣のシートまでを3D参照して合計を求める数式が、アクティブなセルに入力されます。

3D集計・3D参照する数式を入力するExcelマクロ

ここでは、合計する元のセルアドレスをアクティブなセルと同じにしています。

数式を入力しているのは、最後の、
 ActiveCell.Formula = _
  "=SUM(" & ws_a & ":" & ws_z & "!" & addr & ")"
の部分です。

変数を使わなくても書くことはできますが、そうすると、かなり長い一行になってしまいますので、事前に変数へ各種値を代入するようにしました。

アクティブセルのアドレスを$マークのついていない相対参照の形で、変数・addrに代入しています。
 addr = ActiveCell.Address(False, False)

1枚目のシートの名前は、変数・ws_aに代入しています。
 ws_a = Sheets(1).Name

集計する最後のシート(この例では左隣のシート)の名前を、変数・ws_zに代入しています。
 ws_z = ActiveSheet.Previous.Name
WorksheetオブジェクトのPreviousプロパティで、左隣のシートを取得できるのがポイントでしょうか。

最後に上記の3つの変数を使って、アクティブセルに数式を入力しています。
 ActiveCell.Formula = _
  "=SUM(" & ws_a & ":" & ws_z & "!" & addr & ")"

集計用シートが前にある場合に3D参照する数式を入力するサンプルマクロ

二つ目のマクロは、3D参照する数式の入力される集計用のシートが、集計元となる個々のシートより前にある場合のサンプルです。


Sub アクティブセルに3D参照の数式を入力する2()

 Dim addr As String
 Dim ws_a As String
 Dim ws_z As String

 addr = ActiveCell.Address(False, False)

 ws_a = ActiveSheet.Next.Name
 ws_z = Sheets(Sheets.Count).Name

 ActiveCell.Formula = _
  "=SUM(" & ws_a & ":" & ws_z & "!" & addr & ")"

End Sub

上記のマクロを実行すると、右隣のシートから、最後のシートまでを3D参照して合計を求める数式が、アクティブセルに入力されます。

3D集計・3D参照する数式を入力するExcelマクロ

変数・addrは先のマクロと同じです。
 addr = ActiveCell.Address(False, False)

3D参照するシート名を取得する部分が、先のマクロとは異なっています。

このマクロでは、先頭のシートをアクティブシートの右隣のシートとしていますから、変数・ws_aには右隣のシート名を代入しています。
 ws_a = ActiveSheet.Next.Name
WorksheetオブジェクトのNextプロパティで、右隣のシートを取得することができます。

最後のシートまで集計するので、変数・ws_zには最後のシートの名前を代入しています。
 ws_z = Sheets(Sheets.Count).Name
Sheetsコレクションオブジェクトのデフォルトプロパティの引数に、シートの枚数(Sheets.Count)を指定することで、最後のシートを取得しています。

[スポンサードリンク]

Home » ExcelVBA Rangeオブジェクト » セル数式 » 3D集計・3D参照する数式を入力するExcelマクロ

TrackBack:0

TrackBack URL

Home » ExcelVBA Rangeオブジェクト » セル数式 » 3D集計・3D参照する数式を入力するExcelマクロ

「セル数式」の記事一覧

検索


Copyright © インストラクターのネタ帳 All Rights Reserved.

.