スポンサーリンク

VLOOKUPで複数の別シートを参照する時、式の作成楽をする方法

関数の応用

VLOOKUP関数で、他のシートを参照したい時ってありますよね。

1つ2つなら、まぁ手入力でいいかなってなりますが、これが10シート、20シートとなると、手入力なんてやってられません。

この作業、楽にしちゃいましょう。

今回のお題はコチラ

例題として、10年間以上の身体測定結果を記録してるファイルから、指定した人の結果を過去分全部表示させる表を作ってみましょう。

オートフィルでいいんじゃないの?

最終的にはオートフィルでいいんですが、VLOOKUPだけの式ではダメなんです。

うまくいかなかったり、エラーになっちゃいます。

例えば、2010年と2011年の部分だけ式を作って、その先をオートフィルすると、2010年と2011年を参照する式が繰り返されるだけになります。

すると次の対策として、シート名=年度なのだから、=VLOOKUP($B$2,’2010′!A:C,2,FALSE)という式のうち、年度にあたる部分を=VLOOKUP($B$2,A5 & “!A:C”,2,FALSE)のようにセル参照にしてからオートフィルすればよいのでは?という方法が浮かんでくるかと思いますが、ダメです。エラーです。

ではどうすれば良いのか。

答えはINDIRECT関数

別シートを参照させる部分の式を作るときに、INDIRECT関数を使いましょう。

すると、同じような事をしているはずなのに、エラーが出ずにVLOOKUP関数が機能するようになります。

式:=VLOOKUP($B$2,INDIRECT(“‘” & A5 & “‘!A:C”),2,FALSE)

次に、体重の方(C5セル)にも式を作りましょう。

今回の場合、列番号を2から3に変えるだけですね。

最後は2010年の身長と体重が表示されるように式が完成したら、B5とC5を選択し、オートフィルで最終行まで式を反映させるだけです。

組み合わせてこそのINDIRECT関数

今回使用したINDIRECT関数ですが、単体ではちょっと魅力に欠ける関数です。

説明を見ても、指定される文字列への参照を返します。と書かれているだけで、実際に単体で使ってみても、例えば=A1と=INDIRECT(“A1”)で何が違うのかって言われたら、結果については違いなんてないわけで、いつ使うんだよコレ・・・って思われがちが関数です。

ですが、今回のようにVLOOKUP関数はもちろん、SUMIFやCOUNTIFといった、何かを集計するための関数とも非常に相性が良く、上手く組み合わせるとすごく便利な関数へと化けますので、ぜひ試しに使ってみてください。

 

 

 

コメント

タイトルとURLをコピーしました