他シートと連携に便利なINDIRECT関数の使い方

2017.09.05ファイル関数

INDIRECT関数はセルの参照を文字列で指定する関数です(INDIRECT関数の紹介記事はこちら)。
セル参照だけではなく、他のシートを参照する時にINDIRECT関数を使うとより便利なのです。
今回はその計算式の作成の手順を紹介します。

記事で使用しているファイルはこちらからダウンロードできます。

他シートの値を見る

まず、他のシートにある値を参照する方法ですが、とても簡単で、「=」入力後に、参照先のセルをクリックし、Enterキーで確定すれば、その値をセルに連携して表示させることができます。
ダウンロードファイルの「総計」シートをご覧ください。

B2からD2には、「大島」シートのB12からE12の合計が連携すればよいです。
「総計」シートのセルB2からD2には、「小山」シートのセルB12からE12が連携し、「総計」シートのセルB2からD2には「前川」シートのセルB12からE12が連携します。

概要

 

まず、「総計」シートのセルB2の計算式を作成します。
セルB2に「=」を入力し、「大島」シートのセルB12をクリックしEnterキーで確定しましょう。
すると、セルB2の計算式は、「 =大島!B12 」と入力されます。

別シート参照

 

「大島」シートのセルB12を参照しました。
今度は、セルB3にも同様に「小山」シートのセルB12を連携し、セルB4に「前川」シートのB12を連携することを考えるのですが、「総計」シートのA2からA4まで、シート名の一部が記載されています。せっかくシート名があるので、この内容を使って計算式を作ってそれをコピーしたいですね。
セルB2の時点で「 =大島!B12 」の計算式をセルA2のシート名を参照する計算式にしてコピーすることができます。
=大島!B12 」の「大島」の部分がシート名になればよいので、「シート名」「!B12」という文字になるものを作成します。
=A2&"!B12"
「&」は文字を連結する意味を持ち、「"」は文字を囲むのに使用します。つまり、この計算式は、A2のセルに書かれている「大島」という文字列と「!B12」という文字列を連結していることになります。実際にこの計算式を入力してみてください。

セル参照を文字として計算

 

「大島!B12」という参照するセルをあらわす文字になりました。
あとは、この計算式全体をINDIRECT関数で囲むだけです。
次のようにINDIRECT関数で囲みましょう。
=INDIRECT(A2&"!B12")

INDIRECT関数

 

「大島」シートのセルB12の「148」の数値が求まりました。
では、この計算式をB3からB4の範囲にもコピーして反映しましょう。

コピー

 

それぞれ求めることができました。
では、「総計」シートのセルC2とD2にも、同じ手順で計算式を作成し、それぞれ3、4行目にも計算式をコピーして結果を反映させましょう。

セルC2は「大島」シートのセルC12を参照し、セルD2は「大島」シートのセルD12を参照します。
計算式は、セルC2は「 =INDIRECT(A2&"!C12") 」、セルD2は「 =INDIRECT(A2&"!D12") 」です。

完成

他のブックとの連携

他のシートとの連携は上記の方法でできますが、他のブックとの連携も同様にできます。
例えばブック「売上」の「本社」シートのセルE10を参照する式は次の通りです。
=[売上]本社!$E$10 
ブック名が[ ]で囲まれ、シート名!、セル参照となります。セル参照の$は絶対参照になりますが、INDIRECT関数で使うのであれば" "で囲まれ単純な文字列になるため$が付いていてもいなくてもセルE10 を指定します。
つまり、ブック名がA3、シート名がB3に入力されている場合、そのブック、シートのセルE10を参照する計算式は
=INDIRECT("["&A3&"]"&B3&"!E10")
となります。
また、ブック「売上」の「本社」シートのE10を参照する式を作成後、参照元のブック「売上」を閉じると
='C:¥Users¥user¥Documents¥Data[売上.xlsx]本社'!$E$10 
に変わります。
C:¥Users¥user¥Documents¥Data 」まではファイルのあるフォルダの位置をあらわしています。
今はCドライブの中のUsersフォルダの中のuserフォルダの中のDocumentsフォルダの中のDataフォルダという位置にある売上.xlsxファイルの本社シートのE10のセルを参照するということになります。
DataフォルダをセルA1からA5に入力しているフォルダ一覧表で管理したい場合は、=INDIRECT("'C:¥Users¥user¥Documents¥"&A1&"[売上.xlsx]本社'!$E$10")
と式を作成し、その式を下に5つコピーします。
ブック名やフォルダ名をINDIRECT関数で入力した後、存在しないブックやフォルダの場合、#REFエラーになります。

まとめ

INDIRECT関数は、計算に使うセルやシート、ブックの位置をあらわす文字列を指定でき、その文字列は他のセルの値を使うことができ、コピーする前にシート名一覧やブック名一覧から参照する計算式にしておけば、一度にコピーすることができることを解説しました。
シート名をVLOOKUP関数などで適宜選択されるような仕組みにしておけば、複雑な計算元データの切り替えがより簡単にできます。

Excel業務をもっと「ラク」にするなら

Excel業務を自動化したい!
Excelをデータベース化したい!

 

Back to top