エクセル・コピー後の参照先の範囲が変わらない

質問

エクセル2007です。
VLOOK関数と条件付書式が入った表が2つあり、参照先（絶対参照）のリストは
それぞれ表の下部に作っています。

2つの表を合わせたいので、表2を挿入して貼り付けたのですが、
参照先のリストの範囲が変わらずエラーになってしまいます。
下のリスト内に値があればセルの色が変わるという条件付書式も
元のセルの範囲のままで色が変わりません。

貼り付け先の表1は参照先が自動的にずれてＯＫだったのですが、
表2の参照先は変えられないのでしょうか？

文章でわかりづらくてすみません。

回答No.1

nibuzhidao

図1の上にあるような、「4月」「5月」「6月」といった名前の別シートにある合計の値（B5セル）を、「集計用」のシートにまとめて表示したい――。

図1　他のシートのセルを参照する場合は「シート名!セル番地」のように指定する。そこで、「月名」が入力されているセルを使って、参照用の文字を作成してみた（左下）。しかし、文字を結合しても、ただの文字列としかみなされず、他のシートの値を表示できない。こんなときは、引数で指定した文字を“セル参照”扱いにするINDIRECT関数を利用する。すると、結合した文字がセル参照とみなされ、参照先の値を表示できるようになる（右下）
　エクセルでは、別シートの参照もできる。「4月」という名前のシートの「B5」セルの値を表示したいときは、「=4月!B5」のように、「シート名」に続けて「！」（半角のエクスクラメーションマーク）、セル番地と並べて書けばよい［注1］。

　ただし、他のシートを参照した式をコピーしても、シート名の部分は変わらない。このため、シートごとに参照式を入力する必要がある。参照するシートが多いと、非常に面倒だ。

　そこで、「集計用」シートにある「4月」「5月」…という項目名を利用して、文字の結合で参照式を作成してみた。例えば「4月」シートのB5セルを参照するには、A2セルに「4月」と入力されているので、「=A2&"!B5"」のように「!B5」という文字を＆で結合すれば、「4月!B5」となる。あとはこれを下にコピーすれば、「5月!B5」「6月!B5」という文字が自動でできるので、いちいち入力しなくて済むというわけだ。

　しかし、これだけでは単に文字がそのまま表示されるだけで、参照先の値が表示されない（図1左下）。式の中に「4月!B5」「A1:B5」のような文字を入力すると、エクセルはこれらを“セル参照”と自動的に判断してくれるのだが、文字を結合した結果については自動でセル参照とはみなしてくれない。これがセル参照であると、自分で指定する必要があるのだ。

　これに使うのが、引数「参照文字列」に指定した文字をセル参照とみなすINDIRECT（インダイレクト）関数だ。「=INDIRECT（A2&"!B5"）」のように、結合した文字を引数に指定すれば、セル参照とみなされ、参照先の値が表示されるようになる（図1右下）。

　このINDIRECT関数は、例えばVLOOKUP関数などで“参照先の表を切り替える”という応用ワザに利用できる。図2のように「本体」「周辺機器」の2つの表があるシートで、表の名前を入力することで、参照先を切り替えられるようになる。

図2　図のように「本体」「周辺機器」という区分名を入力して、VLOOKUP関数で参照する表を切り替えて価格を検索するといったことも、INDIRECT関数を使えば可能。あらかじめ表に「範囲名」を付けておく（→図3）。その名前をINDIRECT関数でセル参照に変換し、VLOOKUP関数の引数「範囲」に指定すればよい
　これには、まずそれぞれの表に、「範囲名」を付けておく（図3）［注2］。

図3　範囲名を指定するには、指定したいセルを選択し（1）、「名前ボックス」に範囲名を入力して「Enter」キーを押す（2）
　この後、VLOOKUP関数の引数で参照先の表を指定する「範囲」の部分を、表の名前を入力するセル（ここではB2セル）を使って「INDIRECT（B2）」と指定する。

　文字にINDIRECT関数を適用すると、文字が範囲名だった場合は、その部分のセル参照とみなされる。このためB2セルに入力した文字によって、参照先の表を切り替えられるわけだ。

［注1］ 「4月」などシート名に数値が使われているときは、式を入力すると、「='4月'!B5」のようにシート名が「'」（シングルクォーテーション）で囲まれて表示される

［注2］ 範囲名が正しく設定できると、名前ボックスの右にある「▼」をクリックしたとき、設定した範囲名の一覧が表示される。範囲名を選ぶと、その範囲が自動的に選択される