エクセルの参照について

質問

こんにちは。
みなさんのお知恵をお借りしたく、質問させていただきました。

エクセルで月別のシートを作っているのですが、
当月シートに前月のデータを参照させたいと思っています。
例えば11月セルＢ１に10月のＡ１のデータをかえすといったように。

もちろん10月シートを指定すればよいのでしょうが、
それではシート毎に個別に参照を設定しなければならず、
ちょっと面倒だなと感じております。

上記の方法では11月のシートをコピーして12月のシートを作成しても
前月参照セルは10月のシートを参照してしまうことになります。

常に前月のシートを参照するためにはどうすればよいのでしょうか？
シートを相対参照すればよいのかと思いますが、その方法がわかりません。

勉強不足で申し訳ありませんが、ご存知の方いらっしゃいましたら教えてください。
よろしくお願いいたします。

回答No.1

guchi_yama · ベストアンサー

シート名が、シートコピーをする度に変わらないといけないのなら、
絶えずどこかで変えてやらないと関数では難しいと思います。

私が思った例は、
シートコピーをした際にA1に絶えずその月数を入れてやれば
下記式で前月のシートを参照できます。
たとえばA1に"11"と入った11月のシートを
コピーして11月(2)を作ります。
そのシートのA1に"12"と入れてやれば10月では無く
11月のシートデータを参照します。
そして、11月(2)のシート名を12月とすれば、
次の1月になって12月シートをコピーしたときも
12月(2)のA1に"1"と入れ変えるだけで
参照先を12月のシートに出来ます。

=INDIRECT("'"&IF(A1=1,12,A1-1)&"月'!C2")

お礼

こんばんは。早々のご回答ありがとうございます。
教えていただいたとおり、入力してみると希望通りの結果に！
本当に助かりました。

今まで全くINDIRECT関数を使ったことがなかったのですが
（というより、どう使えばいいのかわからなかったです）
とても勉強になりました。
ありがとうございます！
まだINDIRECT関数がぼんやりわかった位なので
もっと活用できるように勉強させていただきます。

回答No.3

imogasi

関数におけるシートの指定は
（１）名前
によるだけです。
VBAではSheets(１)やActivesheetなどの指定がありますが、関数では
名前しかない。
ーー
そこで前月を算出できるのは
（１）シート名に数字がある命名の場合　シート名の月の部分は、前月はN-1です
（２）シート名の番号に頼れない命名の場合はそれがわかる表が必要です。
ーー
どちらもINDIRECT関数しかないと思う（ユーザー関数を除いて）。
ーー
（１）の場合A1に月数があるとして
A1 ーーA2ーーーーーーD2（繰越残・式）ーー　D2の値ーーー　D20（末算）
11　繰越残ーーーー略　------------------------ --123-----111
12 繰越残----=INDIRECT("Sheet"&(A1-11)&"!D20")--111-----222
1 繰越残----=INDIRECT("Sheet"&(A1-11+12)&"!D20")--222-----333
2 繰越残----=INDIRECT("Sheet"&(A1-11+12)&"!D20")--333-----444
3 繰越残----=INDIRECT("Sheet"&(A1-11+12)&"!D20")--444-----555
4 繰越残----=INDIRECT("Sheet"&(A1-11+12)&"!D20")--555-----666
以下略。
こういうのでコピーできたといえるのでしょうか。代替は前月のD2の式をコピーしてOKですが、１２－＞１でわずかな手直しが必要です。
ーーー
１１－翌１０月の各月のシート名を「あ、い、う、え、お、・・」とした場合
表をSheet6のI1:I12に、１月ずらしでもち
い
う
え
お
か
き
く
け
こ
さ
し
あ
と持った場合
１２月のシートのD2には
=INDIRECT(INDEX(Sheet6!$I$1:$I$12,A1,1)&"!D20")
と入れると、上記例と同じにできる。今度はシート間同一場所セルへコピーだけでできるよう。
ーー
ちょっと複雑になったのですが、こういうことになりませんか。

お礼

こんばんは。
とても詳しい説明ありがとうございます。
わかりやすくて、INDIRECT関数の意味が理解できました。

教えていただいたことを活かして他にも色々できそうで嬉しいです。
どうもありがとうございました！

回答No.2

Wendy02

こんばんは。

>それではシート毎に個別に参照を設定しなければならず、ちょっと面倒だなと感じております。

以下のような数式は結構知られていますし、かつて、手前のシートの番号を取るという、マクロ関数を使ったワザもあるのですが……。そちらは、一応、ExcelのVersion しだいといことになりそうです。

=INDIRECT(MOD(SUBSTITUTE(MID(CELL("filename",A1),FIND("]",CELL("filename"))+1,3),"月","")+10,12)+1&"月!A1")

お礼

こんばんは。
ご回答ありがとうございます。

すみません、勉強不足でせっかくおしえていただいた式を
まだ理解できていません。
これから考えさせてもらいますね。

ありがとうございました