経理作業では欠かせないExcelですが、中でも特に便利なのは「セル参照」ができることです。数式を入れたセルを組み合わせることで複雑な計算もスッキリできますし、セルをコピーすることで条件を変化させた複数の計算結果を表示することができます。
さて、当サイトでは、これまで経理に使えるExcelの便利機能を紹介してきました。SUMIFやVLOOKUPといった重要な関数はすでに詳しく解説済みですので、未読の方はぜひ以下の記事も併せてご覧ください。
▶経理を高速化するSUMIF関数の基本と５つの超便利機能
▶経理の文書作成を高速化する【VLOOKUP関数】の使い方
▶経理の奥の手！Excel表からデータをクロス抽出する方法
さて、これらの関数を使いこなしていくうちに、１点大きな問題に直面するかもしれません。それは、通常の数式では、シート名やセル番号自体をセル参照できないということです。
つまり、「別セルにシート名を入力しておけば、その名前のシートのセルを参照してくれる」という仕組みが作れません（下図）。
部門別にシートを分けて損益表を作っている場合などでは、シート名をセル参照で取得できないとなかなか不便です。そこで今回は、本来できない「シート名の参照」を実現してしまうINDIRECT関数をご紹介します。シンプルでいて奥が深い関数ですので、ぜひ覚えておきましょう。
１．INDIRECT関数の概要
１－１．INDIRECT関数とは
INDIRECT関数は、「数式や関数の引数を文字列形式で作る関数」です。引数とは、関数のカッコ内の情報であり、たとえば
=SUM(A1:A5)
という数式であれば「A1:A5」の部分を指します。
このINDIRECT関数は、他の関数の引数を、文字列を作るようにして自由自在に作ることができるのです。
INDIRECT関数を使いこなすためには文字列操作の知識が必要です。文字列操作の基礎知識として、ぜひ事前に超便利！経理が文字列操作で抑えるべき７つのExcel技をご覧ください。
１－２．IDIRECT関数を試してみよう
INDIRECT関数は、文字列を作ることで、その文字列を別の関数の引数の内容とすることができます。下図はすべて「=SUM(A1:A5)」という計算結果になります。
もちろん、SUMIFやVLOOKUPの抽出結果を使うこともできます。「”」（ダブルクオーテーションマーク）で囲うことを忘れないようにしましょう。
なお、INDIRECT関数は引数をまたぐ文字列は作れません。言い換えると、文字列内に「,」（カンマ）を使用することはできません。たとえば、
=SUM(INDIRECT(“A1 , A5″))
という数式を作っても、「=SUM(A1,A5)」という式には変換されず、エラー値が返されます。
２．シート名をセル参照する計算式の作り方
それでは実際に、INDIRECT関数を使ってシート名のセル参照を組み込んだ数式を作ってみましょう。
まず、INIDRECT関数を使わずに直接セル参照するとしたら、以下のような数式になります。
=Sheet1!B3
次に、対象となる引数を文字列として考えます。今回は関数内ではないので、
Sheet1!B3
がそのまま対象となります。
このうち、A2セルの参照に切り替えたい部分は「Sheet1」です。したがって、
A2 & “!B3”
という文字列を作ります。
この文字列の頭に「＝」を付けただけでは、そのセルに「Sheet1!B3」という文字列ができるだけですので、INDIRECT関数を使って数式化してあげます。
=INDIRECT( A2 & “!B3” )
これにより、A2セルに「Sheet1」と入力されていれば
=Sheet1!B3
に読み返され、Sheet1のB3セルを参照してくれます。
３．INDIRECT関数を作る際のコツ
INDIRECT関数はシンプルながら奥の深い関数で、SUMIFやVLOOKUP、さらにはINDEXやMATCH関数にも組み込むことで、シート間を縦横無尽に駆け巡る自動計算も組むことができます。
しかし、INDIRECT関数はなかなか使うのが難しい（間違えやすい）ので、コツを１つ紹介しましょう。
それは、１つひとつ順序を踏んで慎重に作っていくということです。たとえば、
=SUM(Sheet1!B1:B5)
という計算式のうち、シート名をセル参照に切り替えて
=SUM(A2&”!B1:B5″)
という数式を作る方法を例に説明しましょう。
Step.1 普通に計算式を作ってみる
数式を入力したいセルに、まずは普通に
=SUM(Sheet1!B1:B5)
という計算式を入力し、正しく計算されるか確認します。
Step.2 そのままINDIRECT関数を入れる
Step.1の動作が確認できたら、対象引数をそのままINDIRECT関数に置き換えます。
=SUM(INDIRECT(“Sheet1!B1:B5”))
上記の計算式に書き換えたら、正しく計算されているか確認します。ただ単にINDIRECT関数を入れるだけで、文字列自体には一切手を加えないのがミソです。
Step.3 セル参照にしたい箇所とそうでない箇所を「＆」で区切る
Step.2の動作が確認できたら、変化させたい箇所とそうでない箇所を&で区切ります。「”」を忘れないようにしましょう。
=SUM(INDIRECT(“Sheet1“&”!B1:B5″))
上記の計算式に書き換えたら、正しく計算されているか確認します。区切る位置が複数ある場合は、１つひとつ区切りを入れるたびに動作を確認しましょう。
Step.4 セル参照にしたい箇所をセル参照にする
最後に、セル参照したい箇所を晴れてセル参照にします。
=SUM(INDIRECT(A2&”!B1:B5″))
Step.3と同様に、セル参照したい箇所が複数ある場合は、１つひとつ書き換えるたびに動作を確認しましょう。
これでようやく、目的の関数ができました。なぜ１つひとつの手順ごとに動作を確認するかというと、INDIRECT関数を組み込むとどうしても数式が煩雑になり、入力ミスが発生しやすいからです。
入力ミスを防ぐことはできませんが、すぐに見つけることで、原因を特定しやすくなります。私の経験として１つひとつを確認しながら入力した方が絶対に早く終わりますので、ぜひ真似してみてください。
コメント