VLOOKUP関数③応用編～列番号にもセル参照を使う？！～【エクセル】

「エクセル使いこなせていますか？」

サラリーマン歴13年で培ったエクセルスキルを基に、エクセルの使いこなし方を紹介していきます。

今回は、エクセル関数のうち最初につまずきそうな関数No.1(個人の感想)の「VLOOKUP関数」の【応用編】です。

例えば、次のような人にぜひ読んでもらいたいです。

それでは一緒に見ていきましょう！

【応用編】VLOOKUP関数を楽に使う方法

【実践編】に続いて、今回は【応用編】ということで、VLOOKUP関数をもっと便利に使いたいという方に向けた、ちょっと応用的な使い方について紹介していきます。

具体的には、

1. 「列番号」にもセル参照を使う方法
2. 「INDIRECT関数」と組み合わせる

の2本立てです。

「列番号」もセル参照できる！

ひとつ目の技は「列番号をセル参照する方法」です。

一般的にVLOOKUP関数を使う場合、列番号には数値を入力すると思います。例えば、次のような感じですね。

=VLOOKUP(D2,店舗リスト!$A$1:$B$8,2,FALSE) 

しかしこの方法だと、同一の表からVLOOKUP関数で検索してたくさんの列に反映させたい場合に、列ごとに列番号を変えて入力しなければならず、意外と面倒なんですよね。。。

例えば、次のような販売リストと店舗リストがあったときに、販売リストの店舗コードを検索値として、郵便番号・電話番号・URLをVLOOKUP関数で返すことを考えてみましょう。

列番号に数値を入力するやり方

一般的な方法としては、「列番号に数値を入力する方法」かと思います。

つまり、販売リストのE2セル・F2セル・G2セルにそれぞれ次のようなVLOOKUP関数を書いて、3行目以下にコピペする、という方法です。

E2セル：=VLOOKUP(D2,店舗リスト!$A$2:$F$8,3,FALSE)　※郵便番号は先頭列から3列目
F2セル：=VLOOKUP(D2,店舗リスト!$A$2:$F$8,5,FALSE)　※電話番号は先頭列から5列目
G2セル：=VLOOKUP(D2,店舗リスト!$A$2:$F$8,6,FALSE)　※URLは先頭列から6列目

このような感じですね。さて、今回の例では3列分しか入力する必要がありませんので、あまり手間だと感じないかもしれませんが、これが10列分、20列分…と増えると明らかに手間です。さらに、もしミスしてエラー値が出た時にどこがミスの原因なのか見つけるのも一苦労です。

列番号にセル参照を入力するやり方

そこで「列番号をセル参照する方法」というのをご紹介します。

VLOOKUP関数の式を1つだけ書いてコピペすればOKなので、何十列あったとしても手間はないですし、もしミスしてエラーがあったときでもミスの原因を見つけやすいです。

では、早速VLOOKUP関数を入力…する前に一つ準備があります。それは、VLOOKUP関数を入力する列の1行目に列番号を入力しておくのです。つまり、次のような形です。

この準備をしておけば、式を1つ書いてコピペすればOKです。この例では、E3セルに次のVLOOKUP関数の式を書きます。

=VLOOKUP($D3,店舗リスト!$A$2:$F$8,E$1,FALSE)

このように列番号として、あらかじめ入力しておいた1行目のセル参照を入力するのです。さらに、この時にポイントが2つあります。

1. 検索値は列だけ絶対参照の複合参照にする（コピペしたときに横方向にセル参照がずれないようにするため）
2. 列番号は行だけ絶対参照の複合参照にする（コピペしたときに縦方向にセル参照がずれないようにするため）

こうしておけば、E3～G17まで一度にミスなくコピペできます。

この方法は、大きな表であればあるほど、効力を発揮する技です。その一方で、セル参照が増えるので、表が大きすぎると重くなる原因にもなりますので、要注意です。

「INDIRECT関数」と組み合わせる！

ふたつ目の技は、「INDIRECT関数と組み合わせる方法」です。

さて、この記事を読んでいる方のうち、どのくらいの方がINDIRECT関数をご存知でしょうか？このINDIRECT関数は次のような関数です。

書き方	=INDIRECT(参照文字列, 参照形式)
引数「参照文字列」	A1形式、R1C1形式の参照、参照として定義されている名前が入力されているセルへの参照、または文字列としてのセルへの参照を指定します。
引数「参照形式」	「参照文字列」で指定されたセルに含まれる参照の種類を、理論値で指定します。 ・A1形式＝TRUEまたは省略 ・R1C1形式＝FALSE
戻り値	指定される文字列への参照を返します。

…この説明だけだといまいちわからないですよね。しかし、これ以上説明するよりも実際の例を見てもらったほうが効力を実感できると思うので、今のところは参照の形式をした文字列をセル参照として返す、という関数だということだけ頭に入れておいてください。

普通なら同じような式を何度も書かなければならない…

例えば、A君の期末テストの得点表があり、教科ごとに「優・良・可・不可」の評価をVLOOKUP関数で反映させたいとしましょう。

ただし、教科ごとに得点に応じた評価基準がバラバラに用意されているので、一筋縄ではいきません。楽に反映させる方法はないものでしょうか？

なおこの時、評価基準は教科ごとにシートに分かれており、シート名は教科名と一致しています。そのうえ、各教科の評価基準表のセル番地は一致しているものとします。

さて、普通はVLOOKUP関数を入力する場合は、次のように式を書くと思います。次の式は「評価表」シートのC2セルに入力する、国語の評価の場合です。

=VLOOKUP(B2,国語!A3:B6,2,TRUE)

このように、国語のシートのA3:B6を範囲としてVLOOKUP関数を書きます。そして他の教科についても同じように入力しますよね。つまり、5回同じような式を書きますよね。…とっても面倒ですねぇ。

INDIRECT関数の効力は地味だけどとっても便利！

次に、INDIRECT関数を使う場合の式をご紹介します。VLOOKUP関数の式を1つだけ書いてコピペすればOKなので、何シートあったとしても手間はないですし、もしミスしてエラーがあったときでもミスの原因を見つけやすいです。

それでは、INDIRECT関数を使った式は、次のとおりです！

=VLOOKUP(B2,INDIRECT(A2&"!A3:B6"),2,TRUE)　※A2セルには文字列として「国語」が入っている

…正しく反映されているけど、どうして？？これからちゃーんと説明しますから安心してくださいね。

INDIRECT関数の部分を取り出して、分解して説明しましょう。次の図をご覧ください。

いかがですか？前半はINDIRECT関数とは関係ない基本的な置き換えで、後半はINDIRECT関数が参照の形式をした文字列をセル参照として返す関数であることを使っているだけです。

実は、INDIRECT関数で表していた部分は、国語の評価基準の範囲を示していたんですね。あとは、国語以外の教科のセル(C3からC6)に関数をコピペすればOKです。

…でも、INDIRECT関数を含むVLOOKUP関数をコピペしても、範囲のセル参照がずれてエラーにならないのか？というのが気になるかと思います。…でも大丈夫なんです。実際にコピペした関数を見比べてみましょう。

ご覧の通り、検索値やINDIRECT関数内の前のセル参照は、相対参照のためコピペに応じて参照がずれていますが、INDIRECT関数内の後ろのセル参照は、文字列のためコピペしても参照がずれないんです。

このように、INDIRECT関数をVLOOKUP関数と組み合わせると、式を楽に入力して値を反映することが可能なんです。ただし、こちらもサイズが大きい表に用いると重くなる原因になるので、ご注意ください。

VLOOKUP関数をバリバリ使いこなそう！

ということで、VLOOKUP関数をバリバリ使いこなすための2つのスキルをご紹介しました。

【実践編】から少しレベルアップした内容だと感じたかもしれませんが、今回紹介したスキルはぜひ活用してみてくださいね。