エクセルシートの参照について

質問

エクセル2013を使用しています。
エクセルシート「H26会費」に数字が羅列されており、それを参照して別シートに
領収証がつくられています。領収証は数十地区分が並んでいて、金額のところに
“H26会費”！B2　というように参照しているのですが、年度がかわるとそれぞれを
H27に変更しなければならず、簡単な方法を考えています。
例えば、あるセルに「H27会費」など参照したいシートの名前をうちこんでおいて、領収証シートではすべてそのセルの名前にあるシートから参照する、というような。そのような
ことはできるのでしょうか？もしできたら年度が変わっても簡単に参照できるなぁと考えているのですが・・・。また、別の方法でも簡単な方法がありましたら教えていただければと思います。

わかりづらい文章で申し訳ございませんが、よろしくお願いいたします。

回答No.3

kagakusuki · ベストアンサー

>“H26会費”！B2　というように参照しているのですが、

との事ですが、実際のシート名にも「“H26会費”」の様に「"」で囲まれた名称となっているのでしょうか？それとも「H26会費」の様に「"」が付いていない形式となっているのでしょうか？
　それと、実際のシート名において、「H26」の部分は半角文字なのでしょうか、それとも全角文字なのでしょうか？
　どちらなのか判りませんので、取り敢えずの話として、「H26会費」の様に「"」が付いていない形式となっていて、「H26」の部分は半角文字であるものとして話を進める事に致します。

　今仮に、「H27会費」など参照したいシートの名前が入力されているセルがA1セルであり、「H26会費!B2」や「H27会費!B2」などの様に参照したいシートのB2セルを参照するものとします。

　まず、H26会費!$B$2の様に、関数を入力するセルが変わった場合でも、参照先のセルが変化しない様にする(行・列ともに絶対参照)場合には、次の様な関数にされると良いと思います。

=IF(ISERROR(1/(INDIRECT("'"&A1&"'!B2")<>"")),"",INDIRECT("'"&A1&"'!B2"))

　又、H26会費!$B2の様に、関数を入力するセルが変わった場合、参照先のセルの行番号だけが変わり、列番号は変化しない様にする(行は相対参照、列は絶対参照)場合には、次の様な関数にされると良いと思います。

=IF(ISERROR(1/(INDIRECT("'"&A1&"'!B"&ROW(B2))<>"")),"",INDIRECT("'"&A1&"'!B"&ROW(B2)))

　又、H26会費!B$2の様に、関数を入力するセルが変わった場合、参照先のセルの列番号だけが変わり、行番号は変化しない様にする(行は絶対参照、列は相対参照)場合には、次の様な関数にされると良いと思います。

=IF(ISERROR(1/(INDIRECT("'"&A1&"'!R"&ROW(B$2)&"C"&COLUMN(B2),FALSE)<>"")),"",INDIRECT("'"&A1&"'!R"&ROW(B$2)&"C"&COLUMN(B2),FALSE))

　又、H26会費!B2の様に、関数を入力するセルが変わった場合、参照先のセルの行番号と列番号がともに変わる様にする(行・列ともに相対参照)場合には、次の様な関数にされると良いと思います。

=IF(ISERROR(1/(INDIRECT("'"&A1&"'!R"&ROW(B2)&"C"&COLUMN(B2),FALSE)<>"")),"",INDIRECT("'"&A1&"'!R"&ROW(B2)&"C"&COLUMN(B2),FALSE))

　それから、「『参照したいシートに関係する情報』が入力されているセルに入力されている値」が、「H27会費」などとは異なり、例えば「平成27」といった様な「シート名そのものを表す文字列」とはなっていない場合に対応する方法を幾つか回答させて頂きます。

　例えば、A1セルに入力されている値が「H27」の様な後ろに「会費」を付けるだけでシート名と同じ文字列となる場合には、上記の各関数の中の

"'"&A1&"'!

となっている箇所を

"'"&A1&"会費'!

に置き換えて、例えば行・列共に絶対参照の場合は次の様な関数にされると良いと思います。

=IF(ISERROR(1/(INDIRECT("'"&A1&"会費'!B2")<>"")),"",INDIRECT("'"&A1&"会費'!B2"))

　例えば、A1セルに入力されている値が「平成27」の様な「『年』の文字が付いていない和暦の年を表す文字列」である場合には、上記の「A1セルに参照したいシートの名前が入力されている場合」における各関数の中の

"'"&A1&"'!

となっている箇所を

"'"&TEXT(A1&"年1月1日","ge")&"会費'!

に置き換えて、例えば行・列共に絶対参照の場合は次の様な関数にされると良いと思います。

=IF(ISERROR(1/(INDIRECT("'"&TEXT(A1&"年1月1日","ge")&"会費'!B2")<>"")),"",INDIRECT("'"&TEXT(A1&"年1月1日","ge")&"会費'!B2"))

　尚、もし年が「平成2年」の様な1桁台の場合のデータが記載されているシート名が、例えば「H02会費」の様に2桁表示で表す形式となっている場合には、上記の関数中で

"ge"

となっている箇所を

"gee"

に置き換えて、例えば行・列共に絶対参照の場合は次の様な関数として下さい。

=IF(ISERROR(1/(INDIRECT("'"&TEXT(A1&"年1月1日","gee")&"会費'!B2")<>"")),"",INDIRECT("'"&TEXT(A1&"年1月1日","gee")&"会費'!B2"))

　例えば、A1セルに入力されている値が「平成27年」の様な「後に『年』が付いた和暦の年を表す文字列」である場合や、
「2015年」の様な「後に『年』が付いた西暦の年を表す文字列」である場合や、
「平成27年○○○」や「2015年○○○」の様に「後に『年』が付いた年を表す文字列」の後に更に何らかの文字列(例えば「度」、「度　会費報告」、「3月」等)が続いている形式となっている場合には、上記の「A1セルに参照したいシートの名前が入力されている場合」における各関数の中の

"'"&A1&"'!

となっている箇所を

"'"&TEXT(LEFT(A1,FIND("年",A1))&"1月1日","ge")&"会費'!

に置き換えて、例えば行・列共に絶対参照の場合は次の様な関数にされると良いと思います。

=IF(ISERROR(1/(INDIRECT("'"&TEXT(LEFT(A1,FIND("年",A1))&"1月1日","ge")&"会費'!B2")<>"")),"",INDIRECT("'"&TEXT(LEFT(A1,FIND("年",A1))&"1月1日","ge")&"会費'!B2"))

　例えば、A1セルに入力されている値が「平成27年3月13日」や「H27.3.13」、「2015年3月13日」、「2015/3/13」の様な、Excelで文字列データではなく日付データとして扱われるデータ(シリアル値)である場合(尚、「平成27年3月」という形式のものは日付データではなく、文字列データです)には、上記の「A1セルに参照したいシートの名前が入力されている場合」における各関数の中の

"'"&A1&"'!

となっている箇所を

"'"&TEXT(A1,"ge")&"会費'!

に置き換えて、例えば行・列共に絶対参照の場合は次の様な関数にされると良いと思います。

=IF(ISERROR(1/(INDIRECT("'"&TEXT(A1,"ge")&"会費'!B2")<>"")),"",INDIRECT("'"&TEXT(A1,"ge")&"会費'!B2"))

　例えば、A1セルに入力されている値が「2015」の様な「『年』の文字が付いていない西暦の年を表す整数値」である場合には、上記の「A1セルに参照したいシートの名前が入力されている場合」における各関数の中の

"'"&A1&"'!

となっている箇所を

"'"&TEXT(A1&"/1","ge")&"会費'!

に置き換えて、例えば行・列共に絶対参照の場合は次の様な関数にされると良いと思います。
(或いは、「平成27」の様な「『年』の文字が付いていない和暦の年を表す文字列」である場合と同じ関数でもOKです)

=IF(ISERROR(1/(INDIRECT("'"&TEXT(A1&"/1","ge")&"会費'!B2")<>"")),"",INDIRECT("'"&TEXT(A1&"/1","ge")&"会費'!B2"))

お礼

ご回答ありがとうございます。
試してみたいとおもいます。

回答No.2

bunjii

>領収証は数十地区分が並んでいて、金額のところに“H26会費”！B2　というように参照しているのですが、年度がかわるとそれぞれをH27に変更しなければならず、簡単な方法を考えています。
数式のSheet名を手作業で修正しているのですか？
そのようなことをしなくても「編集」グループの「置換」で「検索する文字列」にH26会費!、「置換後の文字列」にH27会費!とすれば「すべて置換」ボタンで数式内の文字列も入れ替わります。

お礼

ご回答ありがとうございます。
試してみたいとおもいます。

回答No.1

askaaska

=“H26会費”！B2

H26会費がA1に入っていてそれを書き換えるのなら

=INDIRECT("'"&$A$1&"'!AF9")

これでいけるわ

単純に領収書のシートを一括置換（「H26会費」→「H27会費」）する手もあるわね

お礼

ご回答ありがとうございます。
試してみたいとおもいます！