Excelを日常的に使っていると、複雑なデータ管理や高度な集計が必要になることがあります。そんなとき、定義済みの名前を使えば参照範囲の変更が簡単になり、とても効率的です。特にINDIRECT関数を組み合わせることで、動的なセル参照が可能になります。本記事では、QData1やQData2などの複数の定義済みの名前を使う場面を想定し、列番号に応じて自動的に切り替えるテクニックについて詳しく解説していきます。
INDIRECT関数で定義済みの名前を動的に参照する基本
定義済みの名前は、Excelシートのセル範囲や値にわかりやすいラベルを付ける機能です。例えば「QData1」「QData2」と名付ければ、セル範囲A1:A10を「QData1」、B1:B10を「QData2」というように管理できます。これにより、数式を読むときにも「QData1」というわかりやすい名前で範囲を特定できるため、メンテナンス性が向上します。
ただ、通常の参照では「QData1」「QData2」などの指定を都度手動で切り替える必要があります。そこでINDIRECT関数を使うと、列番号や行番号といった動的要素を文字列で連結し、それを評価して最終的な参照先を変化させることが可能になります。
INDIRECT関数の仕組みと特徴
INDIRECT関数は、文字列として表現された参照を評価する関数です。たとえばセルA1に「B2」という文字列が入力されていた場合、=INDIRECT(A1)はセルB2の値を取得します。定義済みの名前でも同様に、文字列として「”QData1″」を指定すれば、その定義済みの名前が指し示すセル範囲を参照することができます。
- 構文
=INDIRECT(参照文字列, [参照形式]) - 第2引数(参照形式)について
デフォルトはTRUEで、A1形式の参照として解釈されます。FALSEに設定すると、R1C1形式での参照となりますが、通常はTRUEのままでOKなケースが多いです。
INDIRECTを使うメリット
- 動的な参照先切り替え
たとえば列や行が増えるたびに別々の定義済み名前を利用したい場合でも、INDIRECT関数を使えば数式をコピーペーストするだけで切り替えを自動化できます。 - メンテナンスが容易
参照先のセル範囲を変更したいとき、定義済みの名前さえ更新しておけば、数式側は「QData1」「QData2」などの文字列結合部分を変更するだけで済みます。 - わかりやすい名前管理
計算式に直接セル番地が多数並ぶと可読性が下がりますが、定義済みの名前であれば何を意味する範囲なのかが一目瞭然です。
INDEX関数と組み合わせた応用例
INDIRECT関数は単独でも十分便利ですが、INDEX関数やMATCH関数などの他の関数と組み合わせることで、より柔軟な参照が実現します。たとえば定義済みの名前が複数あり、それを列や行の位置に応じて切り替えたい場合、INDEX(INDIRECT("名前"), 行番号, 列番号)という構文がよく使われます。
例: QData1, QData2, QData3を自動切り替え
「QData1」「QData2」「QData3」…と名前が連番で定義されているとします。このとき、列番号に応じて「QData1」を「QData2」「QData3」などに切り替えたい場合、以下のような式を用意できます。
=INDEX(INDIRECT("QData" & (COLUMN() - 2)), 1, 1)COLUMN()関数で、数式が入力されたセルの列番号を取得します。(COLUMN() - 2)で必要な分だけ列番号から引き算して、1や2、3といった数字を動的に生成します。"QData" & (COLUMN() - 2)という文字列結合で、最終的に「”QData1″」「”QData2″」といった文字列を作ります。INDIRECT関数がその文字列を実際の定義済みの名前と評価し、参照範囲を返します。INDEX関数が、その参照範囲から1行目1列目などの特定セルを取得します。
このように、列番号や行番号を調整するだけで好きなタイミングで定義済みの名前が変わるため、大量のデータを扱う際には非常に重宝します。
複数のシートにまたがる場合
複数のシートにまたがって同様の構成を持つデータ範囲を持っているときも、INDIRECT関数は役立ちます。たとえば「Sheet1」「Sheet2」「Sheet3」という複数のシートがあり、それぞれに「QData1」「QData2」という名前を設定したとします。
その場合、次のようにシート名まで文字列結合して動的に指定できます。
=INDIRECT("'" & "Sheet" & (ROW() - 1) & "'!" & "QData" & (COLUMN() - 2))'(シングルクォーテーション)の扱いに注意
シート名にスペースなどが入っていると、シート参照時にシングルクォーテーションで囲む必要があります。(ROW() - 1)や(COLUMN() - 2)はあくまで例です。シート切り替えのロジックに合わせて調整してください。
定義済みの名前の管理ポイント
Excelでは「数式」タブ→「名前の管理」で定義済みの名前を確認・編集できます。ここで一度に複数の名前を変更したり、範囲を確認したりできるので、INDIRECTと組み合わせる際にも管理しやすいです。
- 名前の付け方のルール
- 先頭文字には英字やアンダースコアなどを使用する（数字は不可）
- 名前の途中にスペースを含めない（アンダースコアやピリオドなどで代用）
- 記号などは基本的に使わないほうが無難
名前に空白や特殊文字を含む場合
もし定義済みの名前に空白が入ってしまった場合、'My Data1'など引用符入りで扱わなければならず、INDIRECT関数の文字列結合が煩雑になります。Excelの管理画面から名前を修正するか、あるいは引用符を適切に入れるように注意しましょう。
INDEX関数とMATCH関数を組み合わせた高度な例
もう少し発展的な例として、行番号や列番号も他のデータと連動させたいケースを考えます。例えば以下のようなテーブルがあって、そこから取得したい行・列を可変にしたいとします。
| 列番号 | 定義済み名前 | 補足 |
|---|---|---|
| 1 | QData1 | Sheet1のA列範囲 |
| 2 | QData2 | Sheet1のB列範囲 |
| 3 | QData3 | Sheet1のC列範囲 |
さらに、行番号に関しては別セルに「検索ワード」を入力し、どの行に当たるかをMATCH関数で求めたいとします。このときの例としては、次のようにできます。
=INDEX(
INDIRECT("QData" & MATCH($G$2, $H$2:$H$4, 0)),
MATCH($I$2, $A$1:$A$100, 0),
1
)MATCH($G$2, $H$2:$H$4, 0)
別セルに入力された検索ワードをもとに、該当する列番号を求めるイメージです。例えば「2」が返ってきたら、それに応じて「QData2」を参照できるようにしています。MATCH($I$2, $A$1:$A$100, 0)
別セル$I$2の値を、$A$1:$A$100から検索して行番号を返す。INDEX(INDIRECT("QData" & ～), 行, 列)
行と列を動的に切り替えつつ、定義済みの名前も文字列連結で変化させています。
このように、INDIRECT関数は他の検索系関数（MATCH・VLOOKUP・HLOOKUPなど）や集計系関数（SUMIF・SUMIFSなど）との組み合わせで、用途が飛躍的に広がります。
エラーやトラブルシューティング
INDIRECT関数は便利ですが、間違った文字列を渡すとエラーが発生します。特に多いのが以下のケースです。
- 名前が存在しない・スペルミス
「QData」のつづりを間違える、あるいは定義がない場合#REF!エラーになります。 - シート名や名前に空白・特殊文字が混在
シート名や名前に空白や記号が含まれ、シングルクォーテーション' 'で囲むのを忘れてエラーになることがよくあります。 - リンク先のブックが閉じている
外部ブックをINDIRECT関数で参照している場合、対象のブックが閉じていると参照できないことがあります。こうした場合、リンク先のブックを開けるか、別の方法で参照する必要があります。
他の関数・技法との比較
INDIRECTを使わずに定義済みの名前を切り替えたい場合、VBAマクロを組む方法や、数式中にIF文を多用して「IF(列番号=1, QData1, IF(列番号=2, QData2, …))」のように分岐させる方法もあります。しかし、マクロはハードルが高い場合があり、IF文の多用は数式が煩雑になります。
INDIRECT関数を使うと、文字列さえ上手に組み立てられれば名前を追加しても柔軟に対応可能になるため、総合的にメンテナンスもしやすいと言えます。
ピボットテーブルやPower Queryとの併用
場合によっては、データの集計や分析をピボットテーブルやPower Queryに任せることで、INDIRECTを使わなくても同等の作業ができるケースもあります。どの方法がベストかは、運用形態やデータの規模、使い慣れた手法によります。INDIRECT関数は、手軽に動的参照をしたいときに威力を発揮するので、複雑なデータ処理の一部で補助的に利用する方法も検討してみてください。
まとめ: INDIRECT関数でExcelを快適に
INDIRECT関数を使うと、文字列から定義済みの名前を評価し、動的にセルやセル範囲を参照することができます。特に「QData1」「QData2」「QData3」など、一定の規則に沿って名前を設定している場合には、列番号や行番号を連結するだけで自動的に名前を切り替えられるようになり、Excelの運用を格段に効率化できます。
- ポイント1: 定義済みの名前を活用すると、シートの可読性や保守性が向上する
- ポイント2: INDIRECT関数を組み合わせると、参照先が動的に切り替わる仕組みが作れる
- ポイント3: INDEXやMATCHなど他の関数とも相性がよく、自由度が高い
複雑なExcelワークシートを作り込む場合や、データ量が多くなる場合にも大いに役立つので、定義済みの名前とINDIRECT関数の組み合わせをぜひマスターしてみてください。効率的かつミスの少ない表計算環境を構築できるはずです。
コメント