前年の実績シートのセルを参照する方法

質問

30年度シートは、29年度シートのG5セルを参照するのに、
="前年"&TEXT('29'!G5,"#,#")
としたのですが、
31年度シート では、 '29'!を '30'! との書換えが面倒なので
$H$1セルに 30と入れて
="前年"&TEXT(INDIRECT($H$1&"!G5"),"#,#")
としたのですが、下のセルにコピーしてもG6になりません。

どう書けばよいのでしょうか？
基本を学んでないのでお恥ずかしいのですが、よろしくお願いします。

回答No.3

imogasi · ベストアンサー

たまに、この必要性に出会うことありますね。
すでに同じ事態にぶつった人の質問があります。
これでしょう。
googleで、「indirect関数の中でcellセルを変化させたい」で照会した。
ＯＫＷＥＢもいいが、その前に、もっとＷＥＢ照会を活用したら。
照会の「検索語」の組み合わせを創出するためには、経験が要ると思うが。
今では、エクセルの初心者の質問など、ＷＥＢ記事のどこかにあると思ったほうが良い。
http://www.excel.studio-kazu.jp/kw/20100307000806.html
ここに載っているやり方は、
（１）連続数字を別セル（縦方向、複数連続のセル）に用意する方法
（２）ＯＦＦＳＥＴ関数利用法
（３）Ｒｏｗ（）を使って、ＲＯＷ（）＋４のように４の部分で調節する方法。
ただし縦方向の式複写にのみ対応。
例　=INDIRECT($H$1 & "!G" & ROW()+4)　第５行から始まる場合
ＩＮＤＩＲＥＣＴ関数の（）中の””　””の間に、セル番地を入れると、式を複写しても、固定したままになる。(　)内は文字列なんだから。
ーー
質問の「前年」とは何？突然で説明不足。シート名の一部か？
シート名に前年を入れるものかな？
質問では
現状と、関数でこうなってほしい、とを対比して書けばわかりやすいのだ。
初心者の失敗ケース談は、ほどほどに記述。

お礼

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

補足

Excel Q&Aサロン
http://excelfactory.net/excelboard/excelvba/excel.cgi
で勉強してましたが、つい手軽なOkwaveで聞いてしまいました。
まず、Google検索でしたね。 それにしてもExcel文化は広大です。
前年については、

収入 支出
2,000　　　
前年11,260 11,150
の様に参考値を、専用欄を使わず見せたかったのです。
"前年 xxx"は文字列なので合計にひっかかりません。

回答No.2

SI299792

="前年"&TEXT(INDIRECT($H$1&"!G"&ROW(G5)),"#,#")
です。
但し、横コピーは考えていません。

H1を使わず、シート名が30なら29を参照する、以下の方法もあります。
="前年"&TEXT(INDIRECT(MID(CELL("filename",I5),FIND("]",CELL("filename",I5))+1,2)-1&"!G"&ROW(G5)),"#,#")

お礼

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

補足

>30なら29を参照する
オォ、そうですね、これ頂きます。
ちょっと長いので、H1に
MID(CELL("filename",I5),FIND("]",CELL("filename",I5))+1,2)-1&"!G"
を置く工夫をしてみます。

回答No.1

bunjii

>どう書けばよいのでしょうか？
下記のように書き直してみる。
="前年"&TEXT(INDIRECT($H$1&"!G5"),"#,#")
　　　　　　　　↓
="前年"&TEXT(INDIRECT($H$1&"!"&G5),"#,#")

未検証なので目的通りにならないかも知れませんが下へコピーしたときにG5がG6にならないのは"!G5"は文字列のためです。
G5セルの値を参照する場合は"!"とG5のセル値を&演算子で連結します。

お礼

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