Excel

【Excel】INDIRECT関数とVLOOKUP関数で参照シートを切り替えて値を取得する

ねぇもりさん、「VLOOKUP関数の検索範囲の指定」で、セルごとに参照シートを切り替えたいんだよね
もり
もり
んん?詳しくおしえて
こんな感じだよ~。

「参照シート」がセルごとに異なるから、1セルずつ検索範囲を変更しなきゃならないんだよね

VLOOKUP関数で検索範囲のシートを指定する
もり
もり
なるほどね。「参照シート」を「B列のセルの値」に指定できればよさそうだね
Excelでセルの文字列のシートを参照する
そうそう!そうすれば、データが増えても、セルの数式をコピーするだけでOKだよね
もり
もり
INDIRECT関数を使うと実現できるよ!紹介していくね
インダイレクト??

INDIRECT関数とは

英単語の「INDIRECT」には「間接的な」「遠回りな」という意味があります。

ExcelのINDIRECT関数の書式です。

INDIRECT(参照文字列)

簡単な使用例です。A1セルの値を「間接的に」参照しています。

ExcelのINDIRECT関数の使い方

この仕組みを使って「参照シートの切り替え」を行いますよ!

INDIRECT関数で参照シートを指定して値を取得する

集計元データの各シートの構成です。A列に「キー」B列に「値」というシンプルな表です。

カテゴリ1,2,3 すべて同一形式です。

VLOOKUP関数の「検索範囲」に「セルの値」を使用する

ここで行いたいのは「VLOOKUP関数の第2引数=検索範囲」を「セルの値」で指定することです。

2行目の集計は「カテゴリ1」シートを参照します。B2セルに”カテゴリ1”と入力されているので、このセルの値を使います。

Excelでセルの文字列のシートを参照する

 

※「B列の値」と「シート名」が異なるとエラーになります。ご注意ください。

【NG例】

  • カテゴリ(数値が全角)
  • カテゴリ1(数値が半角)

 

INDIRECT関数を使用した計算式がこちらです。

ExcelのINDIRECT関数で参照シートを指定する

C2セルの数式です。VLOOKUP関数の第2引数「検索範囲」に、INDIRECT関数を使用します。

=VLOOKUP(A2,INDIRECT(B2&”!A:B”),2,FALSE)

このように「検索範囲をセル参照で指定する」ことで、データが増えても計算式をコピーするだけでOKです。

ExcelのINDIRECT関数で値を集計する
へぇ~便利だね。計算式の仕組み、詳しく知りたいな

INDIRECT関数の仕組み解説

仕組みを解説していきます!

INDIRECT関数の仕組み

★ポイント

B2&”!A:B” = “カテゴリ1!A:B” の仕組み

Excelの計算式では、文字列をダブルクォート(”)で囲みます。「ダブルクォートで囲まれた文字列」と「何らかの値」を & 結合すると、値全体が文字列になります。

  • B2 = 参照セル
  • “!A:B” = 文字列

B2という参照セルに、ダブルクォートの文字列を & で結合することによって、全体が文字列になるのです。

VLOOKUP関数とINDIRECT関数の組み合わせです。

VLOOKUP関数の検索範囲にINDIRECT関数を使用する
もり
もり
これで、VLOOKUP関数の検索範囲を1セルずつ変更しなくてもOKだね!

ぜひ使ってみてね~

ノンプログラマーがプログラミングを学ぶ理由

ノンプログラマーがプログラミングを使えるようになると、「仕事の幅」が広がります。

日常生活にたとえると「車の運転」のイメージです。

多くの人は、大人になると自動車教習所に通って、運転の練習をしますね。F1レーサーやタクシードライバーなどの「プロ」になるわけではないのに。

車の運転ができれば、遠いところへ、速く・ラクに移動できて「生活の幅」が広がるからです。

プログラミングができるようになれば、今の仕事を速く・ラクに片づけて、「仕事の幅」を広げることができます。

「プロ」を目指す必要はありません。今の仕事をより豊かにするための「手段」、それがプログラミングです。

1週間のオンライン無料体験
いつでもどこでも今すぐ視聴OK