ExcelのINDIRECT関数の使い方をお探しですね。
広告
Excel INDIRECT関数の使い方！セル参照を文字列で指定する基本
INDIRECT関数は、文字列で書いたセル番地を「実際のセル参照」として使える便利な関数です。
基本の書き方は「=INDIRECT(参照文字列, [参照形式])」で、最初の引数に「A1」や「Sheet2!A1」のような文字列を入れます。
2つ目の引数は省略できて、省略すると普段使っているA1形式になります。
まずは2つ目の引数は気にせず、シンプルに使ってみるのがおすすめです。
例えば、A1セルに「B1」と入力して、B1セルに「100」と入っているとします。
ここでC1セルに「=INDIRECT(A1)」と入力すると、A1に書かれている「B1」という文字列がセル参照として読み取られて、C1には「100」が表示されます。
ポイントは、A1の中身を「B2」や「C5」に変えるだけで、C1の数式はそのままで参照先を変えられることです。
つまりINDIRECT関数は、「数式に直接セル番地を書く」のではなく、「別のセルに書いてあるセル番地を使って参照する」ための関数なんです。
セル番地を文字列で組み立てるときは、「&」で文字をつなげます。
例えば、A1セルに「B」、A2セルに「3」と入力されているとき、「=INDIRECT(A1&A2)」とすれば「B3」という文字列ができて、B3セルの値を取得できます。
列と行を別々に管理できるので、集計表や検索表で参照先を柔軟に変えたいときに便利です。
ただし、存在しないセルやシートを指定すると「#REF!」エラーになるので、まずは参照文字列が正しいかチェックするのが大事です。
セルの入力に応じて参照範囲を自動で切り替える
INDIRECT関数は、1つのセルだけじゃなく範囲指定にも使えます。
例えば、A1セルに「B2:B10」と入力して、別のセルで「=SUM(INDIRECT(A1))」と書くと、B2からB10までの合計が計算されます。
A1セルの文字列を「C2:C10」に変えれば、同じSUM関数のままC列の合計に切り替わります。
普通なら数式の中身を書き換える必要がありますが、INDIRECT関数を使えば参照範囲をセルの入力値で管理できるんです。
月別や部門別など、集計範囲をよく変える表では特に役立ちます。
名前付き範囲と組み合わせる方法もよく使われます。
Excelでは、特定の範囲に「東京」「大阪」「商品A」といった名前を付けられます。
例えば、A1セルに「東京」と入力して「=SUM(INDIRECT(A1))」とすれば、「東京」という名前の範囲の合計を計算できます。
セル番地を直接使わず、わかりやすい名前で範囲を切り替えられるので、後から見たときに「何を集計してるのか」がすぐわかります。
ドロップダウンリストとINDIRECT関数を組み合わせると、もっと使いやすくなります。
例えば、A1セルに「データの入力規則」で「東京、名古屋、大阪」という選択肢を作って、それぞれと同じ名前の範囲を用意しておきます。
そして「=SUM(INDIRECT(A1))」とすれば、A1で選んだ地域に応じて集計対象が自動で切り替わります。
使う人は数式を触らず、リストから選ぶだけで結果が変わるので、他の人に渡すExcelファイルでも使いやすいです。
ただし、範囲名にはスペースや記号が使えないことがあるので、できるだけシンプルな名前にしておくと安全です。
INDIRECT関数で別シートを動的に参照する
別のシートを参照するとき、普通のExcel数式では「=Sheet2!A1」のように「シート名!セル番地」の形で書きます。
INDIRECT関数でも同じで、「=INDIRECT(“Sheet2!A1″)」と書けばSheet2のA1セルを参照できます。
さらに、A1セルに「Sheet2」というシート名を入れておいて、「=INDIRECT(A1&”!B2″)」と書けば、A1に入力されたシート名のB2セルを参照できます。
これが、別シートを動的に参照する基本的な使い方です。
シート名を縦に並べておけば、数式をコピーするだけで複数シートの同じセルを集計できます。
例えば、「総計」シートのA2からA4に「東京」「大阪」「福岡」というシート名が入っていて、それぞれのシートのB12セルに売上合計があるとします。
このとき、総計シートのB2に「=INDIRECT(A2&”!B12″)」と入力すれば、A2に書かれた「東京」シートのB12セルを参照できます。
その数式をB3、B4にコピーすると、A3やA4のシート名に応じて「大阪!B12」「福岡!B12」が自動で参照されます。
手作業で「=東京!B12」「=大阪!B12」と1つずつ書く必要がなくなるので、シートが多い集計ファイルほど効率的です。
シート名にスペースや記号が入っている場合は、シート名をシングルクォーテーションで囲む必要があります。
例えば、シート名が「月次 データ」のようにスペースが入っている場合、「=INDIRECT(“‘”&A1&”‘!B2”)」と書きます。
この式では、A1のシート名の前後に「’」をつなげて、「’月次 データ’!B2」という正しい参照文字列を作っています。
シート名が完全に一致していなかったり、余計なスペースが混ざっていると「#REF!」エラーになります。
エラーが出たときは、INDIRECT関数の中で作られている文字列が、実際のシート名とセル番地に合っているか確認してみてください。
VLOOKUPとの組み合わせ・エラー対策・注意点
INDIRECT関数は、VLOOKUPやXLOOKUPなどの検索関数と組み合わせると、参照する表やシートを自動で切り替えられます。
例えば、A2セルに検索したい商品名、B2セルに参照したいシート名が入っていて、各シートのA2:B100に商品名と価格の一覧がある場合、「=VLOOKUP(A2,INDIRECT(“‘”&B2&”‘!A2:B100″),2,FALSE)」と書けます。
この式では、B2のシート名をもとに検索範囲を作って、その範囲のA列からA2の商品名を探して2列目の価格を返します。
商品カテゴリ別、支店別、月別などでシートが分かれているブックでは、検索対象のシートをセル入力で切り替えられるのでとても便利です。
ただし、INDIRECT関数にはいくつか注意点があります。
まず、参照先の文字列をもとに評価するので、参照先が存在しないと「#REF!」エラーになります。
シート名の入力ミス、全角半角の違い、末尾のスペース、範囲指定の間違いなどが原因になりやすいです。
また、別のブックを参照する場合、INDIRECT関数は参照元のブックが閉じていると正しく取得できないことがあります。
普通の外部参照とは違って、閉じたブックの値を安定して参照するのには向いていないので、別ブックの集計にはPower Queryや通常のリンク参照も検討してみてください。
それから、INDIRECT関数は「揮発性関数」と呼ばれていて、ブック内で再計算が起こりやすい関数です。
少しの数式なら問題ないですが、数千行、数万行に大量のINDIRECT関数を入れると、Excelの動作が重くなることがあります。
参照範囲を必要以上に広くしない、同じ参照文字列を補助列で作って使い回す、あまり変わらない集計には普通の参照やINDEX関数を使うなど、場面に応じて使い分けるのが大切です。
INDIRECT関数はすごく柔軟ですが、すべての参照をINDIRECTに置き換えるのではなく、「参照先を文字列で切り替えたいとき」に絞って使うと、見やすくて安定したExcelファイルが作れます。
INDIRECT関数を使いこなすコツは、まず参照文字列を正しく作ることです。
いきなり複雑な式を書くのではなく、別のセルで「A1&”!B2″」のような文字列を作って、ちゃんと「東京!B2」や「’月次 データ’!B2」になっているか確認してからINDIRECT関数で囲むと、ミスを減らせます。
セル参照を文字列で指定する基本、別シートを動的に参照する方法、VLOOKUPとの組み合わせ、エラー対策を押さえておけば、月次集計や部門別集計などの作業がぐっと効率的になります。
Excelで複数シートのデータを扱うことが多いなら、INDIRECT関数をぜひ活用してみてください。
広告
