Excelの参照式について

質問

1～31という名前の31個のシートがあり、合計用のシートで
=SUM('1:31'!$D5)
と言う式を使ってシートのD5の値を足しているのですが、

この、1～31までにあたる'1:31'を別セルを参照する形で可変にしたいのです。

J1に10
L1に15
と入力すると=SUM('10:15'!$D5)になるようにしたいのですが、
'J1:L1'と入力しても上手くいきません。
値だけを参照して可変にする方法はありますでしょうか。
宜しくお願いします。

回答No.2

ok2007 · ベストアンサー

類似のＱ＆Ａをどこかで見たことがあるなぁと思って探してみたところ、次のURLにありました。
http://www.efcit.co.jp/cgi-bin2/exqalounge.cgi?print+200606/06060025.txt

このメジロさんのご回答が、aloe_bearさんのケースにもっとも合うかと思います。たぶん、こんな具合で上手くいくのではないかと・・・。
=SUMPRODUCT(SUMIF(INDIRECT(ROW(INDIRECT(J1&":"&L1))&"!D5"),"<>"))

お礼

恐ろしく完璧でした。
こういう発想がすっと出てくるようになりたいです。
ありがとうございました。

回答No.1

gutoku2

一般的に考えると　INDIRECT関数　を使いたい所です。

しかし
　　　　B5セルに　'1:31'!D5
　=SUM(INDIRECT(B5))は成立しません
　　　（関数の決まりですからどうしようもありません）
でも
　=SUM(INDIRECT(J8),INDIRECT(K8))なんて関数はOKです。
　　ならば、各々のシートが該当か否かをIF関数で判断させれば良い事になります

よって
　J1セルに　串刺し計算したい最初のシート名（今回は数字）
　L1セルに　串刺し計算したい最後のシート名（今回は数字）
=SUM(IF(AND(1>=J1,1<=L1),INDIRECT("1!D5"),0),IF(AND(2>=J1,2<=L1),INDIRECT("2!D5"),0),IF(AND(3>=J1,3<=L1),INDIRECT("3!D5"),0),・・・・・・・IF(AND(31>=J1,31<=L1),INDIRECT("31!D5"),0)
のような関数で目的は達成できます。

　※手入力すれば
　　=SUM('1：31'!D5)　とか　=SUM('3:25'!D5)
　　　　と入力するだけで、非常に簡単なのですが・・・

まあ、一回作成すれば後は計算結果を見るだけなのですが、これだと修正が
発生した場合に修正が非常に大変ですね。

よって私ならば、集計シートを一つ作ります。

J1セルとL1セルには開始シート名（数字）、終了シート名を入力

＜1～31（日にちでしょうか）の入力＞
A2セル　1
A3セル　2
　・・・
A32セル 31

＜該当のセル名を入力＞
B1セル　D5（半角英字の文字）

＜該当のセルが加算の対象外ならばゼロ＞
B2セル　=INDIRECT("'"&A2&"'!"&B$1)*IF(AND(A2>=$J$1,A2<=$L$1),1,0)
B3セル　=INDIRECT("'"&A3&"'!"&B$1)*IF(AND(A3>=$J$1,A3<=$L$1),1,0)
　・・・
B32セル =INDIRECT("'"&A32&"'!"&B$1)*IF(AND(A32>=$J$1,A3<=$L$1),1,0)

合計を求めたいセルに　=SUM(B2:B32)
これで目的を達成できます。
　　※これだと、表の修正が発生しても割と簡単に修正できます。

お礼

丁寧なご回答ありがとうございました。
今回はより適した数式が見つかりましたが、
&""&で繋ぐ発想はとても参考になりました。
実際使わせていただいています。ありがとうございました。