婚活LABより、ブライダルネットの期間限定クーポン配布中!!

【EXCEL】INDIRECT関数の基本と便利な使い方。

【EXCEL】INDIRECT関数の基本と便利な使い方。

今回はEXCELについてです。覚えるの後回しにしがちなINDIRECT関数について、かなりのEXCEL時短テクニックとなるので基本的な使い方から具体的な使用例まで詳しく解説していこうと思います。

INDIRECT関数の基本

ではまずINDIRECT関数の基本から見ていきましょう。
INDIREDT関数は以下の引数を持ち、セルを間接的に参照する際に使用します。

INDIRECT(参照文字列,参照形式)

参照形式に関してはA1形式かR1C1形式にするかの指定です。A1形式であれば省略して構いません。

R1C1形式とは、row(行)とcolumn(列)の頭文字を取った、文字通り行数と列数でアドレスを示す形式。
例: A1 や B2

基本的な使用例

では参照文字列に様々な文字列を入力してみましょう。以下の図よりどのように出力するか確認します。
G3からG6のセルにINDIRECT関数を入力しています。

indirect関数のサンプル

出力
サンプル① =INDIRECT(B3) #REF!
サンプル② =INDIRECT("B3") 山田一郎
サンプル③ =INDIRECT(C3) 山田一郎
サンプル④ =INDIRECT("C3") B3

サンプル①解説

サンプル①では=INDIRECT(B3)と入力し、B3セルを参照した際、間接的に存在しない山田一郎というアドレスを探してしまった為エラーとなりました。

サンプル②解説

サンプル②ではB3セルを文字列として認識させる為、ダブルクォーテーションでB3セルを囲っています。この場合、直接参照される値が返される為、山田一郎と出力されました。

サンプル③解説

サンプル③では=INDIRECT(C3)と入力し、C3セルを参照した際、B3とアドレス番地が参照された為、間接的にB3セルを出力しています。そのため山田一郎と出力されました。

サンプル④解説

サンプル②と同じくダブルクォーテーションで囲って文字列として認識されている為、直接C3セルの値の『B3』が出力されました。

なかなか理解が難しいですが、上記の4パターンを理解し演算子と組み合わせて利用することで様々な時短テクニックへと応用させることが出来ます。

別シートを効率的に参照させる時短テクニック

次に演算子(&)と組み合わせて別シートの他のセルに入力されている値を参照する方法を解説したいとおもいます。

EXCELが使用できる環境から観覧されている方は以下よりSampleファイルをダウンロードするとわかりやすいと思います。

INDIRECT関数で別シートを参照

今回は上記の図の様に、複数の別シート(集計、東京本店、大阪支店、名古屋支店)を用意しました。
『東京本店、大阪支店、名古屋支店』シートのC3セルには各本店、支店の売上合計が入力されています。Sampleファイルを確認してください。

集計シートで各本店、支店の合計を出したいので、INDIRECT関数を使い別シートより売上を参照してみましょう。

上の図の集計シートのC3セルには以下の様に入力されています。

=INDIRECT(B3&”!C3″)

この式では、B3に入力された値を間接的に参照した『東京本店』と、文字列『”!C3″』を演算子で結合させます。

したがって『=’東京本店’!C3』と入力した場合と同じ状況をINDIRECT関数を使用して実現することができました。

今回あえて、INDIRECT関数を使用した理由は、オートフィルを使用した際に、シート名を自動で隣のセルの値を参照させる為です。
↑ ココがINDIRECT関数を使ったEXCEL時短テクニックです。

後はオートフィルで引っ張ってやればいちいちシート名を入力しなくても他シートを参照することが出来ます。
Sampleファイルでは式は入力されていませんので集計シートのC3セルにだけ式を入力しオートフィルを使用してみてください。

VLOOKUPやSUMIFなど様々な関数にネスト(入れ子)使うとさらに時短テクニックとなります。

INDIRECT関数をVLOOKUP関数にネストして使用する。

次の便利な使い方、INDIRECT関数をVLOOKUP関数にネストしてさらに便利に使用してみましょう。

例により、以下よりSampleファイルをダウンロードするとわかりやすいと思います。

今回は架空のレンタカーショップの料金の検索を簡単にINDIRECT関数を使用して計算してみましょう。

INDIRECT関数をVLOOKUP関数にネスト①

軽自動車、普通車、トラックの価格表には予め以下の範囲で名前の定義をしています。

範囲 定義名
B7 × C11 軽自動車
E7 × F11 普通車
H7 × I11 トラック

名前の定義についてわからない方はExcel時短テクニック!「名前の定義」を上手に使う方法。を参照してください。

次に以下のような検索フォームを作り、『レンタル時間』、『車種』を入力すると料金が表示されるようにINDIRECT関数とVLOOKUP関数を使って式を入力してみましょう。

INDIRECT関数をVLOOKUP関数にネスト3

Sampleファイルでは『レンタル時間』、『車種』の隣のセルはご入力を回避する為、プルダウンリストを使用しています。
プルダウンリストの作成方法がわからない方はExcelでプルダウンリストを使って入力ミスを防ぐ方法。を参照してください。

今回はINDIRECT関数をVLOOKUP関数にネストして「料金」の隣の黄色で塗りつぶされたセルに

=VLOOKUP(F17,INDIRECT(F18),2,0)

と入力しました。
※セル番地は以下の画像より確認してください。

VLOOKUP関数についてはここでは詳しくは解説しませんが、この様にINDIRECT関数を使用して先に名前の定義をした表の範囲を返すことも可能です。

INDIRECT関数をVLOOKUP関数にネスト4

上記画像の様に『レンタル時間』、『車種』を色んな値に変えてみると料金を簡単に表示させることが出来るようになりました。



グループサイト。婚活LABより!婚活無料診断実施中!

CTA-IMAGE 簡単な質問に答えるだけですあなたに最適な婚活タイプが丸わかり!