串刺し合計する同一セル番号を間接的に指定する方法

例えば、エクセルのシート名が1と2で、各シートのセル番号C40にある数値を合計する場合、=SUM('1:2'!C40) で合計できますが、合計を求めるシート1と2のセル番号であるC40を、=SUM('1:2'!C40)があるシート内の例えばセル番号B50に入力されている文字列C40を参照して、=SUM('1:2'!C40)と同一の計算を実行できるようにするには、、=SUM('1:2'!C40) のC40のところをどのように書き直せばよいのでしょうか。説明がわかりにくくてすみません。例えばB50に入力されている文字列をC40からC60に書き換えると、=SUM('1:2'!C60)と同じ計算結果にできる　=SUM('1:2'!？)の？のところの記載方法がわかりません。、=SUM('1:2'!C40)のC40をC60に書き換えるしかないのでしょうか。よろしくお願いします。

No.4 ベストアンサー 回答者： MarcoRossiItaly 回答日時： 2014/03/23 05:24

既存の関数のみを使ってマクロなしで求めるなら、次式。

=sumproduct(n(indirect(row(1:2)&"!"&b50)))
あるいは
=indirect(1&"!"&b50)+indirect(2&"!"&b50)

「'1:2'!」というのに INDIRECT 関数は対応できないようなので、代替の記法で乗り切ります。

この回答へのお礼

ご回答ありがとうございます。数字が出ました。すごく感激しています。既存の関数を使ってできるのですね。大変助かりました。

お礼日時：2014/03/23 10:19

No.3 回答者： kagakusuki 回答日時： 2014/03/23 04:02

　INDIRECT関数やINDEX関数、OFFSET関数の様な参照先のセルを変更できる類の関数は、串刺し計算には対応しておりません。

　ですが、VBAのマクロを使用しますと、「ユーザー定義関数」と言って、用途に合わせて新しい関数を自作する事が出来ますので、御質問の目的に合った関数を作ってみるというのも1つの方法です。
　但し、ユーザー定義関数を使う事が出来るのは、そのユーザー定義関数用のマクロを設定したファイル内だけであり、別のファイルで同じユーザー定義関数を使うためには、その別のファイルにも、同様のマクロを設定しなければなりませんので、注意して下さい。

　まず、Visual Basic Editorのウィンドウを開いて、標準モジュールの画面に次の様なVBAのマクロを記述して下さい。


Function STERICSUM(area, Optional firstsheet As String = "", Optional lastsheet As String = "")

　　Dim i, fsindex, lsindex, s As Integer
　　Dim n As Double

　　If firstsheet = "" Then firstsheet = Application.Caller.Parent.Name
　　If lastsheet = "" Then lastsheet = Application.Caller.Parent.Name
　　fsindex = Sheets(firstsheet).Index
　　lsindex = Sheets(lastsheet).Index
　　s = 1
　　If firstsheet > lastsheet Then s = -1
　　n = 0
　　For i = fsindex To lsindex Step s
　　　n = n + WorksheetFunction.Sum(Sheets(i).range(area))
　　Next i

　　STERICSUM = n

End Function


　その上で、串刺し合計の結果を表示させるセルに、次の様な形式の関数(ユーザー定義関数)を入力して下さい。

=STERICSUM(範囲,[シート名1],[シート名2])

　例えば、「Sheet1」、「Sheet2」、「Sheet4」、「Sheet3」、「Sheet5」、「Sheet5 (2)」、「Sheet6」という順番にシートが並んでいて、
「Sheet3」、「Sheet5」、「Sheet5 (2)」の3枚の連続したシートの各A2～C9のセル範囲の合計を求める際には、次の様に記述して下さい。

=STERICSUM("A2:C9","Sheet3","Sheet5 (2)")

　又、Sheet1とSheet2のC40セルの合計を求める場合には、次の様に記述して下さい。

=STERICSUM("C40","Sheet1","Sheet2")

　又、上記の範囲の部分を例えばB50セルに入力されている文字列によって、C40からC60に変更可能な様にする際には、次の様に記述した上で、B50セルに入力する「セル範囲を示す文字列」を「C40」から「C60」に変更して下さい。

=STERICSUM(B50,"Sheet1","Sheet2")

　尚、このユーザー定義関数では、引数として「範囲」、「シート名1」、「シート名2」の3個の引数を用いますが、その内の「シート名1」と「シート名2」の2個の引数は省略可能で、シート名を省略した場合には、その関数が入力されているシートが参照されます。

この回答へのお礼

ご回答ありがとうございます。早速VBEで貼り付けました。数字が出て感激です。関数をつくると柔軟に対応できて便利なのですね。今、VBAの勉強中でもあるので大変助かりました。ありがとうございました。

お礼日時：2014/03/23 10:15

No.2 回答者： tom04 回答日時： 2014/03/22 23:20

こんばんは！

串刺し計算でINDIRECT関数を使ってみましたが、ちゃんと計算できないようなので・・・
（当方の力量不足かもしれません）

VBAでやってみました。
仮にSheet名が「1」・「2」というSheetがSheet見出しの一番左と左から2番目にあるとします。
結果表示したいSheetの
B50セルに入力したSheet名「1」・「2」のセル番地の合計をA1セルに表示させる一例です。

画面左下のB50セルにセル番地を入力するSheetの
Sheet見出し上で右クリック　→　コードの表示　→　VBE画面（カーソルが点滅しているところ）に
↓のコードをコピー＆ペースト　→　Excel画面に戻り、B50セルにセル番地を入力してみてください。

Private Sub Worksheet_Change(ByVal Target As Range) 'この行から
Dim k As Long, str As String
If Target.Address = "$B$50" Then
On Error Resume Next '←念のため
With Range("A1") '←A1セルに結果表示
.Value = ""
str = Target
For k = 1 To 2 '←Sheet見出しの一番左から1番目～2番目まで
.Value = .Value + Worksheets(k).Range(str)
Next k
End With
End If
End Sub 'この行まで

※　お望みの方法でなかったらごめんなさいね。m(_ _)m

この回答へのお礼

エディターでコピーして数字がでました。今、VBAの勉強中なので大変助かりました。ありがとうございました。

お礼日時：2014/03/23 10:08

No.1 回答者： bin-chan 回答日時： 2014/03/22 21:11

セルB50に値［'1:2'!C40］を設定しておいて

=SUM(INDIRECT(B50))
かな？

この回答へのお礼

ご回答ありがとうございました。

お礼日時：2014/03/23 10:21