Office TANAKA - ワークシート関数[INDIRECT 関数]

書式

たとえば、任意のセルに「=A1」と入力すれば、そのセルからセルA1を参照できます。

INDIRECT関数を使うと、次のように、参照するセルのアドレスを文字列で指定できます。

「なんじゃ、そりゃ？」「はぁ？何に使うの？」と思ったあなた。その通りです。これだけだと、この関数がどれほど便利か分かりませんね。ポイントは、参照するセルのアドレスを文字列で指定できる、というところです。文字列で指定できるということは、参照したいセルのアドレスを、何とかして文字列で作成できれば、どこにあるセルでも参照できるということです。これ、超便利です。てか、INDIRECT関数を使えるようになると、Excelの数式作成スキルが256倍アップします(当社比)。

アドレスを文字列として作れればいいのですから、たとえば、文字列を結合できる＆演算子を使って、次のようなこともできます。

文字列の"A"と数値の2を結合して、"A2"というアドレスを作りました。Excelではこうして、文字列と数値を＆演算子で結合して、文字列を作ることができます。

ここまでだと、まだ「だから何？」なんですが、じゃ、これに別の関数を組み合わせてみましょう。たとえば、セルを検索するMATCH関数を。MATCH関数については、別のページで解説します。

「MATCH("佐藤",A1:A5,0)」は、セル範囲A1:A5内で"佐藤"を探して、上からいくつ目にあるかという、その位置を返します。よろしいですか？「MATCH("佐藤",A1:A5,0)」は数値の3を返すんです。だったら、次のようにしたらどうでしょう。

勘の良い方なら、このへんで「お！」って思うんですけどね。では、もう少しやってみましょう。今度は、次のようなデータで考えます。さっきまでA列に入力されていた名前がB列にあります。そして、A列には数値を入力しました。

今度は、名前をB列で検索します。たとえば"菊池"は、セル範囲B1:B5内で上から4番目ですね。この、MATCH関数が返す数値の4と、文字列の"A"を結合します。つまり、B列の"菊池"を調べて、見つかった位置(行)に該当するA列を参照します。

さて、MATCH関数で検索する名前は、何もMATCH関数内に直接記述しなくてもいいです。次のように、別のセルに入力してあっても、同じように動作します。

もちろん、セルC1を別の名前に変えれば、参照するセルも変わります。上図では、そのために入力規則のリストを設定してあります。さて、こうした参照、何かに似ていませんか？そう！みんな大好きVLOOKUP関数です。でもVLOOKUP関数って、表の左端を検索して、該当する行の右方向にあるセルしか参照できませんよね。表のレイアウトがそうなっていないとVLOOKUP関数は使えません。でも何とかして参照したい。そんなとき、このINDIRECT関数が活躍するんです。もちろん、ここでやったことは、INDIRECT関数でなくても、INDEX関数でも可能です。どちらがいいという話ではありません。できるだけ多くの選択肢を持つことが重要なんですよ。

もうひとつ、INDIRECT関数の例をご紹介しましょうか。A列に入力されている名前のうち、一番下の名前を参照してみましょう。

今だったら「=A4」です。でも、データは増減しますよね。こんなときもINDIRECT関数が重宝します。この「=A4」は、INDIRECT関数を使うと、次のようにできます。

INDIRECT関数の「"A4"」は、「"A"&4」のように文字列結合で作ってもいいんでしたよね。さて、この「4」とは何でしょう？今A列には全部で(タイトルを含めて)4つの文字列が入力されています。ということは、この「"A"&4」は次のように考えられます。

任意のセル範囲に入力されているデータの個数は、COUNTA関数で分かります。

ということは、

「A:A」というのは、A列全体という意味です。

こうすると、A列に新しいデータが追加されても、常に一番下の名前を参照できます。

数式は作るものです。望む計算を一発でやってくれる関数など、そうありません。数式は、複数の関数を組み合わせて作るんです。そんなとき、VLOOKUP関数やSUMIF関数のような、いわば一品料理ではなく、INDIRECT関数のような発想しだいで、いろいろなことに役立つ素材が重要なんです。ぜひ、INDIRECT関数を活用してください。

なお、INDIRECT関数を使うと、セルの移動や削除に影響されず、いつも同じセルを参照することができます。詳しくは「セルを移動しても同じセルを参照する」をご覧ください。