INDIRECT/文字列で記載された式を参照し、別セルに計算結果を表示する

image18080502_s-1 基礎コース

INDIRECTの使い方

INDIRECT関数は、参照するセルのアドレスを「文字列」で指定できる関数です。

=INDIRECT(参照文字列, [参照形式])

[参照文字列] = 参照するセルのアドレスを指定します。
[参照形式] = 省略可。参照文字列で指定したセル参照の種類を論理値で指定します。

「TRUE、又は省略」・・・A1形式のセル参照。
「FALSE」・・・R1C1形式のセル参照。

関数の説明 文字列にてセルを参照する
EXCELバージョン Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

参照形式について・・・

エクセルでは、数式などでセルを参照する際に2つの方法が存在しています。
一般的に使用される方法が「A1形式」で、これは、Aから始まるアルファベットの列番号と、1から始まる数字の行番号を利用した参照方法です。
これとは別に、「R1C1」形式では、数式を入力したセルから左右上下にどれぐらい離れた位置のセルを参照するかを数値で指定します。

例えば、セルC2にてセルA1を参照したい式を入力する場合、次のような違いとなります。

A1形式・・・「=A1
R1C1形式・・・「=R[-1]C[-2]

R1C1形式では、実際に数式を入力しているセルC2から見てセルA1は、行数が1つ手前、列数が2つ手前という指定式となります。(※「R」→Row(行)、「C」→Column(列)を意味しています

通常はA1形式が使用されますが、エクセルの設定を変更することでR1C1形式を使用することも可能となります。

 

使い方例)INDIRECT

下の表は、ある学校の学期末テストの結果です。この表を使って、INDIRECT関数の使い方自体を見てみましょう。

index_match-6893705-471x270

通常、エクセルで特定のセルを参照するには、数式にて「=A1」のように入力しますが、INDIRECT関数では、「”」で囲んだ文字列として指定します。

① 表の中から「席番号5の氏名」を求める。

[参照文字列] : 参照するセルC8を「”」で囲んで指定します。  →”C8″
=INDIRECT(“C8”)

 

これだけですと、この関数の持っている特長が少し分かりづらいので、次の方法も見ておきましょう。

② 表の中から「席番号5の氏名」を求める。

MATCH関数を使って、席番号5が何行目にあるかを検索します。

MATCH(5,B4:B8,0)

MATCH関数の検索範囲[B4:B8]はエクセル自体の4行目から始まっていますので、エクセルの元の行番号とするために「+3」しておきます。

MATCH(5,B4:B8,0)+3

これによって、MATCH関数の結果の「5」と、補助的な「+3」を足して、「8」という数値が出ました。
この数値をINDIRECTに使用します。

[参照文字列] : 参照するセルの内列番号の「C」だけを「”」で囲み、&記号によってMATCH関数をつなぎ合わせてセル番号を指定します。
=INDIRECT(“C”&MATCH(5,B4:B8,0)+3)

 

つまり、「=A1」のような入力の場合では、セルA1しか参照できないのですが、INDIRECT関数を使用すればMATCHやVLOOKUP、その他の関数を用いて変化するセル参照が出来るというのがこの関数の強みとなります。

 

別シートのセルの文字列を式として参照する

INDIRECTを使用すれば、別シートの参照「=東京!C7」(東京シートのセルC7)といった方法とは少し違ってきます。

 

使い方例)別シートのセル参照

下の表は、ある商店の店舗別売上合計を計算する為の表です。
それぞれ「4半期売上合計」・「東京」・「大阪」・「名古屋」のシートが作成してあり、店舗別に合計額を「4半期売上合計」シートに入力する予定です。(※「東京」・「大阪」・「名古屋」のシートは同じセル位置に表が作成されています)

この時に、INDIRECT関数を使って各店舗毎の月合計を参照してみましょう。

4半期合計シート
image19030904_s

 

「東京」・「大阪」・「名古屋」のシート
kushidata58

 

INDIRECT関数での参照結果)

50058023401

① 東京シートの4月度合計を参照する。

[参照文字列] : シートを意味する「!」記号と東京シートの4月合計が表示されているセルC7を「”」で囲んで指定します。  →”!C7″

また、シート名の指定は、直接入力ではなく、表内のセルC3を使用して[参照文字列]の前に追加します。
=INDIRECT(C3&”!C7″)

続けて読むと、「東京(C3)!C7」=東京シートのセルC7という参照となり、4月度合計が表示されます。
この参照方法の強みは、セルC7の指定が「文字列」であることです。つまり、オートフィルやコピーをしても、指定されたセルC7は変化しないことになります。

 

オートフィル結果)

シートを指定する部分のみがオートフィルされているのが分かります。

%d0%b1%d0%b5%d0%b7-%d0%bd%d0%b0%d0%b7%d0%b2%d0%b0%d0%bd%d0%b8%d1%8f-6-2

 

他ファイル(別ブック)からの参照は可能か?

INDIRECT関数では、制約付ではありますが、他のエクセルファイルのセルを参照することも可能となります。

 

使い方例)他のブックのセルを参照する

エクセルで他のファイルを参照する時には、『参照したいファイル名 + エクセルの拡張子(.xlsxなど)』は「[ ]」で、ファイルの場所を指定する時は「’(シングルクォーテーション)」で囲んで指定します。

→例)[会議資料.xlsx]、’C:Temp[会議資料.xlsx]’

拡張子について・・・

Windowsでは、ファイルを保存した場合に、作成したファイル名の後ろに「拡張子」と呼ばれる文字が付随します。

これは、ファイルの種類を区別させるために付けられるもので、それぞれのアプリケーションによって文字が異なります。 例えば、エクセルでは以下のような拡張子が自動的に選択されます。

Excel 97 – 2003 のブック → .xls
Excel 2007 以上のブック → .xlsx
Excel 2007 以上のマクロ付きブック → .xlsm

 

 

今回、上で使ったシートの内、「東京」・「大阪」・「名古屋」シートのみのファイルを別途作成し、「4-6月合計」という名前で保存しました。この時、「4半期売上合計」シートのみのファイルでの、INDIRECT関数は以下のようになります。

%d0%b1%d0%b5%d0%b7-%d0%bd%d0%b0%d0%b7%d0%b2%d0%b0%d0%bd%d0%b8%d1%8f-12

① 別のブック「4-6月合計」から、東京シートの4月度合計を参照する。

(1)ファイル名「4-6月合計」に拡張子「.xlsx」を付けて「[」で囲み、その外を「”」でさらに囲んで指定します。

“[4-6月合計.xlsx]”

(2)参照したいシート名の代わりに、前回と同じようにセルC3を指定し、「&」でブック名とつなぎます。

“[4-6月合計.xlsx]”&C3

(3)ここまでの入力を「’」で囲むために、「’(シングル)」自体も文字列として「”(ダブル)」で囲み、前と後ろにつなぎます。

” ‘ “& “[4-6月合計.xlsx]”&C3 &” ‘ “

特に「’(シングル)」自体の使い方が少し分かりづらいですが、これで「4-6月合計ブックの東京(C3)シート」という意味になります。
シートの指定には、まだ「!」記号が不足していますので、次の(4)で補いましょう。

(4)シートを意味する「!」記号と東京シートの4月合計が表示されているセルC7を「”」で囲んで指定し、これまでの後ろにつなぎます。

” ‘ “& “[4-6月合計.xlsx]”&C3 &” ‘ ” &“!C7”

(5)最後にこれらの参照式をINDIRECT関数に入れてあげれば完成となります。

=INDIRECT(” ‘ “& “[4-6月合計.xlsx]”&C3 &” ‘ ” &”!C7″)

 

POINT!

冒頭に、他ファイルからの参照は「制約付」とご説明しました。

この意味は、エクセルでは他のファイルを参照して表示したい場合には、その対象となるファイルをあらかじめ開いておかなければならない制約のある関数があるという事です。
今回のINDIRECT関数もその対象で、「4-6月合計.xlsx」が開かれていなければ「#VALUE(エラー)」の文字が表示されることになり、注意が必要です。

 

入力規則を利用し絞込入力規則リストを作成する

エクセルである程度、表の管理や入力をしていると、「○○○のグループのさらに×××という分野の内から、あらかじめ決めているリストが自動的に表示されるようにしたい」といったことが出てきます。
これが出来ると、入力の手間が省けてとても便利になってきますね。このような場合は、INDIRECT関数と入力規則、名前定義を駆使して設定するようにしましょう。

→入力規則や名前定義の詳細についてはこちら

 

使い方例)INDIRECT関数による、絞込み入力規則リスト

下の表は、各野球チームのピッチャーの中から全日本チームとして選抜したい人を入力する為に作成したものです。

左側の表は、各得意分野(「先発」「中継ぎ」「抑え」)から、さらにチーム毎に名前を選抜する表です。
右側の表は、それぞれの選抜候補者の得意分野別かつ、チーム別に分けた一覧表です。

左側の表で、[得意役割]や[チーム]のセルで選択すると、自動的にその対象となる候補者の名前が入力規則リストとして表示されるように、それぞれのセルを(1)~の手順で設定してみましょう。

image18080502_s

 

(1)得意役割のセルに入力規則リストを作成します。
image18060901_l

①得意役割のセル範囲[B5:B9]を範囲選択します。
②エクセル上部から、「データ」タブをクリックします。
③表示されるリボンの一覧から、「データの入力規則(文字の部分)」をクリックし、メニューから「データの入力規則」をさらに選択します。

 

(2)新しく表示される「データの入力規則」ダイアログボックスにて以下のように設定します。
image18080510_s

①[入力値の種類]にて、「リスト」を選択します。
②[元の値]に、「先発,中継ぎ,抑え」とカンマ区切りで直接入力します。
③最後に[OK]ボタンを押して設定を完了します。

 

(3)以下の手順であらかじめ、チーム名を名前定義しておきます。
20181118091306

①チーム名が入力されたセル範囲[F5:H5]を範囲選択します。
②エクセル左上にある[名前ボックス]に、「先発」と直接入力します。

 

(4)(3)と同様に、「中継ぎ」・「抑え」についてもそれぞれ名前定義します。

 

(5)チーム名が自動的にリストアップされるように、以下のように入力規則を設定します。
image18101201_l

①チーム名が入力されるセル範囲[C5:C9]を範囲選択します。
②データの入力規則ダイアログボックスにて、[入力値の種類]を「リスト」にします。
※ダイアログボックス表示については手順(1)をご確認ください。以後省略いたします。
③[元の値]に、次の数式を入力します。

=INDIRECT($B5)

これで、得意分野のB列にて「先発」が選択されれば、「先発」という名前定義されたチーム名のリストが自動的に表示されるようになります。

 

(6)以下の手順であらかじめ、氏名を名前定義しておきます。
column-285-5c9d9eb7-7c972

①「先発」の「読買ジャイアント」の候補者のセル範囲[F6:F7]を範囲選択します。
②[名前ボックス]にて、得意役割とチーム名をつなげた「先発読買ジャイアント」という名前を定義します。

 

(7)(6)と同様に、「各得意分野のチームごと」に名前定義します。以下の例を参考にしてください。(※合計9つの名前定義になります)
pic_20190626_04

 

(8)氏名が自動的にリストアップされるように、以下のように入力規則を設定します。
07-1

①氏名が入力されるセル範囲[D5:D9]を範囲選択します。
②データの入力規則ダイアログボックスにて、[入力値の種類]を「リスト」にします。
③[元の値]に、次の数式を入力します。

=INDIRECT(TEXT($B6&$C6,”@”))

※TEXT関数にて、セルB6(得意分野)とセルC6(チーム)を「&」で繋げて指定していることに注意してください。

これで、得意分野のB列にて「先発」、チームのC列にて「読買ジャイアント」が選択されれば、「先発」と「読買ジャイアント」の文字がつながった「先発読買ジャイアント」という名前定義された氏名のリストが自動的に表示されるようになります。

 

(9)これで絞込み入力規則が完成しました。

それぞれ「得意分野(B列)」、「チーム(C列)」もプルダウンリストで指定できるようになり、さらに下の表のように左側で選ばれた得意分野とチーム名の「氏名(D列)」が自動的にプルダウンリストで表示されるようになりました。

image18080502_s-1

 

 

 

Rate article