関数

【関数の組み合わせ】VLOOKUP関数とINDIRECT関数で参照先を変える技!

VLOOKUP関数とは

エクセルでデータの検索に欠かせない関数が、VLOOKUP関数ですよね。
ここで、少しVLOOKUP関数を思い出してみましょう。

VLOOKUP関数VLOOKUP関数

↑VLOOKUP関数の書式は、[=VLOOKUP(検索値,範囲,列番号,[検索の型]]になります。

引数[検索値]をもとに指定されたセル範囲の1列目で特定の値を検索し、その行の引数に[列番号]指定した列にあるセルの値を返します。

これがVLOOKUP関数でしたね。

ちょっと自信が無いという場合は、エクセル【VLOOKUP関数】で別シートから簡単に条件にあう値を参照しよう!で詳しく解説しているので、参考にしてみてください。

次項より、INDIRECT関数について詳しく解説するので、ぜひ読み進めてください。

INDIRECT関数とは

INDIRECT関数とは、セルに入力されている文字列を介して、間接的にセルを指定する関数です。

  • INDIRECT関数の書式
    =INDIRECT(参照文字列,[参照形式])
  • 参照文字列は必須
    文字列は『”』(ダブルクォーテーショ)で囲んで指定
  • 参照形式は省略できる
    TRUE または省略:A1 形式のセル参照
    FALSE:R1C1 形式のセル参照

それでは、実際にINDIRECT関数を使って名前検索をしてみましょう。

セルC3に[=INDIRECT(B3)]を入力セルC3に[=INDIRECT(B3)]を入力

↑下記の順に設定します。

  1. セルB3に[E7]を入力
  2. セルC3に[=INDIRECT(B3)]を入力
セルC3に[=INDIRECT(B3)]を入力した結果セルC3に[=INDIRECT(B3)]を入力した結果

↑セルC3に『小野 弥生』が表示されました。

どのような動きをしているかというと、INDIRECT関数によってセルB3に入力された値をセル番号として認識し、そのセル番号に入力されている内容をセルC3に返したことになります。

では、ここでINDIRECT関数に指定している『セルB3の値』を削除するとどうなるか、確認してみましょう。

セルB3の値を削除セルB3の値を削除

↑セルC3の数式は、[=INDIRECT(B3)]と同じですが、セルB3にセル番号と認識できる値が入力されていないためエラーとなりました。

次に、セルC3の数式を、[=INDIRECT(“E7”)]に変更してみましょう。E7をダブルクォーテーションで囲んでいることに注目してくださいね。

セルC3に[=INDIRECT("E7")]と入力した結果セルC3に[=INDIRECT(“E7”)]と入力した結果

↑[=INDIRECT(“E7”)]と入力したため、セルC3に『小野 弥生』が返されました。

このように、[参照文字列]にセル参照を表す文字列を指定する(セル番号やセルの名前を直接入力する)場合は、『”』(ダブルクォーテーショ)で囲んで指定します。

他にも文字列を結合し、シートを指定することもできます。
INDIRECT関数を使って以下の『会議室A』『会議室B』『会議室C』という3種類のシートを指定し、それぞれ集計されている利用人数を『集計』シートに集約してみましょう。

3種類のシート3種類のシート

↑それぞれの会議室の日付毎の利用人数の合計が、セルB8に集計されています。

『集計』シートのセルC3に[=INDIRECT(B3&"!B8")]を入力『集計』シートのセルC3に[=INDIRECT(B3&”!B8″)]を入力

↑下記の順に設定します。

  1. 『集計』シートのセルC3を選択
  2. セルC3に[=INDIRECT(B3&”!B8″)]を入力
セルC3に[=INDIRECT(B3&"!B8")]を入力した結果セルC3に[=INDIRECT(B3&”!B8″)]を入力した結果

↑シート『会議室A』のセルB8の値が表示されました。

セルB3の値とシート名が同一のため、セルを参照し、文字列を結合することで、シート『会議室A』のセルB8を指定することができました。オートフィルでセルC5までコピーしましょう。

『集計』シートに集約した結果『集計』シートに集約した結果

↑『会議室A』『会議室B』『会議室C』のセルB8の値を参照し、『集計』シートに集約することができましたね。

このようにINDIRECT関数は、[参照文字列]に文字列を指定でき、その文字列を『文字』ではなく、シート名やセル番号として認識します。

INDIRECT関数は、直接セルを指定せずに間接的にセルの参照をするので、使い方を理解するのには、少し時間がかかるかもしれませんが、使い方次第で作業範囲が広がるので、マスターしておきたい関数です。

Dr.オフィス
Dr.オフィス
[=A1]とセル参照する場合と、数式[=INDIRECT(“A1”)]や[=INDIRECT(A1)]とする場合の違いをしっかり理解しておこう!

次項では、VLOOKUP関数とINDIRECT関数で参照先を変更する方法を解説します。

1 2 3
ABOUT ME
Dr.オフィス
Dr.オフィス
エクセルの使い方や応用法を日々研究しています。 初心者から上級者までわかりやすく解説していきます。 エクセルについてわからないことや気になることなどございましたら是非、コメントやお問い合わせからご質問ください。
暑中見舞いの締め切りは8/7です!梅雨が明けたら忘れる前に送りましょう。おすすめの暑中見舞い作成サービスをまとめました。
おすすめランキングはコチラ
おすすめの暑中見舞い作成サービスをランキング形式でまとめました。
詳細はコチラ