重要なお知らせ

「教えて! goo」は2025年9月17日(水)をもちまして、サービスを終了いたします。詳細はこちら>

【GOLF me!】初月無料お試し

例えば、エクセルのシート名が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に書き換えるしかないのでしょうか。よろしくお願いします。

A 回答 (4件)

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



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

「'1:2'!」というのに INDIRECT 関数は対応できないようなので、代替の記法で乗り切ります。
    • good
    • 0
この回答へのお礼

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

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

 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個の引数は省略可能で、シート名を省略した場合には、その関数が入力されているシートが参照されます。
    • good
    • 0
この回答へのお礼

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

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

こんばんは!


串刺し計算で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
    • good
    • 0
この回答へのお礼

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

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

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


=SUM(INDIRECT(B50))
かな?
    • good
    • 0
この回答へのお礼

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

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

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!