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

ぜひ使ってみてね~

【100万人以上が使う】タスク管理・プロジェクト管理ツール「Backlog」の新規無料トライアル

「Backlog」は日本製のタスク管理・プロジェクト管理ツールです。

国内利用者は100万人を超え、エンジニア・デザイナー以外にも、営業・人事・経理など、幅広い職種で大人気!

日本製なので、親しみやすく直感的に使えるのがポイントです。

タスク管理・プロジェクト管理にかかせない主な機能がこちら。

  • 課題(タスク)の作成
  • ファイル共有
  • wiki
  • ガントチャート
  • カンバンボード
  • テンプレート機能

法人・個人問わず利用できるので、効率的にタスク管理したいフリーランスの方にもオススメですよ!