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だね!

ぜひ使ってみてね~

2020年・小学校でプログラミング教育が必修化

2020年、いよいよ小学校でプログラミング授業が始まります。続く2021年からは「中学校」で開始。2022年からは「高校」でも導入されます。

あと何年かしたら、あたりまえにプログラミングを使いこなす新入社員が入ってくるかもしれません。2020年、大人も一緒に学んでみませんか?

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