先週の「ブック間の参照はしない」に関連して、初心者がよくおちいりがちなミスについて、取り上げておきたい。
ブック間のデータ参照方法
先週のBlogで「ブック間の参照はしない」を基本原則にすると書いたが、その場合、参照元ブックへ参照先ブックの情報をどのように紐づけるか？大きく方法は２つとなる。
ひとつは、①参照先ブックにある、参照先シートを「シートごとコピー」して、同一ブック内に置いて、そのシートを参照する方法だ。そしてもうひとつは、②参照先ブックにあるデータのうち、必要な項目のみ、参照元シートにVLOOKUP関数で「参照」という形で表示させ、その上で、表示させたデータのみを残し、参照する計算式を消去する方法がある。
どちらの方法を選択するかは、その後のブックの使い方で決まってくる。ブックで資料を作成するときのみ、参照元データを使用する場合には、後者②が簡単だ。一方で、作成したブックを使用して、定期的になんらかの処理を行うような場合には、①の方がつねに参照元シートのデータを参照できるので都合がいい。
VLOOKUP関数を入力する際には、ほとんどの読者は「関数ウィザード」を使うだろう。関数の引数や参照範囲など、指定された順序で入力していくだけなので簡単だ。
しかしこのVLOOKUP関数を関数ウィザードで入力する際に注意したい点がある。参照先が他のブックか、同じブック内かで、
『参照範囲指定時の「絶対参照」か「相対参照」かのデフォルト設定が異なる』
という点である。
具体的に見て行きたい。まず、他のブックからの参照である。
関数ウィザードの「範囲」指定の引数を見て欲しい。ダイアログボックス上の範囲をポインタで指定しただけで、絶対参照を表す「$」がついている。
一方で同じブック内のほかのシートを参照すると、範囲指定の引数は、範囲指定しても絶対参照を表す「$」マークはつかないのだ。したがって、同じブック内で範囲指定をする場合には、ファンクションキー「F4」を押して絶対参照の「$」マークを付ける必要がある。例題のシートであれば、以下のとおりとなる。
【相対参照】
'Sheet1'!B3:F7
【絶対参照】
'Sheet1'!$B$3:$F$7
同じ機能を使おうと関数ウィザードを使いながらも、引数の設定の際の絶対参照・相対参照のデフォルト設定が違うのだ。相対参照のままでVLOOKUP関数で参照先シートを参照すると、VLOOKUP関数式を入力した後、下の行以降にコピーした際、オートフィル機能が働いて、式をコピーした位置分だけ、参照先の範囲もズレてしまうのだ。
この関数で参照範囲をズラして参照する必要性は考えにくく、一方でデフォルトで絶対参照するかしないかが変化するのは、なかなか気がつきにくい。店主もExcel初心者時代にはこの理由がわからず、VLOOKUP関数がうまく計算してくれず、「#N/A」が消えてくれず途方に暮れた思い出がある。
VLOOKUP関数がうまく計算されない場合には、まっさきにチェックして欲しいポイントである。
