

例えば、エクセルのシート名が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.3
- 回答日時:
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の勉強中でもあるので大変助かりました。ありがとうございました。
No.2
- 回答日時:
こんばんは!
串刺し計算で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
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【関数】3つのセルの中で最新...
-
【マクロ】数式を入力したい。...
-
【マクロ】変数に入れるコード...
-
エクセルの関数について
-
【マクロ】excelファイルを開く...
-
エクセルのリストについて
-
【マクロ】実行時エラー '424':...
-
【マクロ】左のブックと右のブ...
-
Office2021のエクセルで米国株...
-
【マクロ】元データと同じお客...
-
エクセルのVBAで集計をしたい
-
【マクロ】【相談】Excelブック...
-
vba テキストボックスとリフト...
-
【マクロ】【配列】3つのシー...
-
他のシートの検索
-
【画像あり】オートフィルター...
-
エクセルシートの見出しの文字...
-
LibreOffice Clalc(またはエク...
-
【関数】=EXACT(a1,b1) a1とb1...
-
Dir関数のDo Whileステートメン...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【マクロ】元データと同じお客...
-
エクセルの関数について
-
【画像あり】オートフィルター...
-
エクセルのVBAで集計をしたい
-
エクセルのリストについて
-
【マクロ】数式を入力したい。...
-
【マクロ】【相談】Excelブック...
-
Office2021のエクセルで米国株...
-
【マクロ】実行時エラー '424':...
-
他のシートの検索
-
エクセルの複雑なシフト表から...
-
【マクロ】【配列】3つのシー...
-
vba テキストボックスとリフト...
-
【マクロ】左のブックと右のブ...
-
【マクロ】変数に入れるコード...
-
エクセルシートの見出しの文字...
-
【マクロ】別ファイルへマクロ...
-
【関数】同じ関数なのに、エラ...
-
Amazonでマイクロソフトオフィ...
-
ページが変なふうに切れる
おすすめ情報