Excel関数　～INDIRECT関数～

Excelシート上で使える関数の使い方を説明。
今回は　INDIRECT関数　について、使い方や活用例を紹介

　

INDIRECT関数とは

INDIRECT関数とは

　参照したいセルの場所を、直接文字列で入力して参照する

という機能を持った関数です。
、、、と言われてもこれだけだと分かりにくいので、もう少し詳しく説明します。

例えば下記のように、C1セルにA1セルの数値データを表示させたい、となったとき、
C1セルに
　＝A1
と記載します。

これはC1セルがA1セルを参照している状態になります。

この時C1セルに記載されているのは文字列データのように見えますが、
実際はセルの位置情報データ（のようなもの）が入力された状態になっています。
C1セルに入力する際は「＝A1」と記載するだけなので簡単に見えますが、
パソコン側から見ると少し特殊なデータ形式になっているのです。

なので、例えば下記のようにD1、D2、D3セルにそれぞれ「＝」、「A]、「１」を入力し、これをつなげた文字列としてC1セルに入力したとしても、この時C1セルに入力されたのは「＝A1」という文字列データでしかないため、A1セルを参照してくれないのです。

このような時に、文字列データを入力しただけでそれをセルの位置情報データのようなデータ形式に変換してくれるのがINDIRECT関数なのです。
例えば、上記のようにC1セルからA1セルを参照させたいとき、
C1セルに下記を入力すれば、書き方は全て違いますが、全て同じ動きをします。
　＝A1
　＝INDIRECT（”A1”,TRUE）
　＝INDIRECT（D1＆D2＆D3,TRUE）

ただ、どういう動きをするのかは理解できるかもしれませんが、それをじゃあどういうときに使うの？
というのが難解かと思います。

「＝A1」と入力すればいいのに、
なんでわざわざ「＝INDIRECT（”A1”,TRUE）」と書く必要があるの？

そこについて理解しやすいように後ほどいくつか例を紹介しようと思います。

その前にこの関数の使い方を説明しようと思います。
この関数は下記のように２つの情報を指定する必要があります。

　＝INDIRECT（参照文字列、参照形式）

詳しく説明していきましょう。

入力情報①　参照文字列

参照文字列はそのまま、参照したいセルのアドレスを文字列データで入力します。

文字列データであればOKなので、文字列が入力されたセルやそれを結合した計算式でもOKです。
　（記入例）
　＝INDIRECT（”A1”,・・・）
　＝INDIRECT（D1＆D2＆D3,・・・）
　＝INDIRECT（”R1C1”,・・・）
　＝INDIRECT（”R[“&E1&”]C[“&E2&”]”,・・・）

ただしこの時入力するセルのアドレスの書き方は、次に説明する『参照形式』の指定によって変わってくるので、入力する場合は注意が必要です。

入力情報②　参照形式

この参照形式の部分では、参照文字列で記載するセルのアドレスの書き方を指定します。
入力する場合はBoolean値（TRUE、FALSE）で入力します。

それぞれ下記で指定します。
　TRUE：A1形式で記載（A1、D1など）
　FALSE：C1R1形式で記載（R2C4、R[2]C[5]など）

この参照形式は省略も可能です。
その場合は「TRUE：A1形式」になります。

A1形式はExcelでデフォルトのセルアドレスの形式なので特に困らないと思いますが、C1R1形式はあまり使ったことが無い方も多いかもしれません。
こちらの形式についてだけ、もう少し詳しく説明します。

　

C1R1形式について

C1R1形式というのは、
　左上もしくは入力中のセルから、何行目（ROW）、何列目（COLUMN）
という形で入力する方式です。

Excelではなくただの表だと思えば、こちらの形式の方がイメージがしやすいかもしれません。
例えばA1形式で「C5」セルと表現されるセルは、
C1R1形式だと「R5C3」セルになります。

上記のようにR（数値）C（数値）という入力をすると、シートの一番左上を起点として、
そこから○行目、○列目に行ったセル、という意味合いになります。

これを、R [（数値）] C [（数値）]というように数値を大括弧”[]”で挟んだ書き方をすると、
入力しているセルを起点として、そこから○行目、○列目に行ったセル、という意味合いになります。
イメージとしてはOFFSET関数の動きに近く、OFFSET関数の参照セルを自セルに設定したときの動きのイメージです。

Excel関数　～OFFSET関数～

Excelシート上で使える関数の使い方を説明。今回は　OFFSET関数　について。OFFSET関数は、指定のセルから指定した数だけ移動した先にあるセル情報を表示する、という機能を持った関数です。

yorozuya-tanuki.com

相対参照や絶対参照の場合の表現の仕方についてですが、
A1形式だと絶対参照にしたい部分（行もしくは列）に”$”マークを入力すれば、その部分は絶対参照となります。
こちらについては過去投稿もご参考ください。

ExcelVBAで家計簿アプリを作ろう　その2-2

Excelマクロで作る家計簿アプリの作り方実践・説明の記事です。本記事では、条件付き集計方法について、Excel関数を使った場合（SUMIF）、マクロを使った場合（For文とIf文の組み合わせ）について説明。またExcel関数での文字入力や相対参照・絶対参照についても説明。

yorozuya-tanuki.com

じゃあC1R1形式だとどうするかですが、
C1R1形式の場合は相対参照・絶対参照を切り替えるための記号が大括弧”[]”になり、
　数値を大括弧”[]”で挟んだ書き方をすれば相対参照
　数値を直接Rの後ろ、Cの後ろに直接入力している場合は絶対参照
になります。

通常と違う表記の仕方なので最初は戸惑うかと思いますが、
慣れてくるとC1R1形式の方が書きやすくなってくるかもしれません。

　

INDIRECT関数の活用例について

使い方は上記で説明しましたが、
で、この関数をどういうときに使うか？について活用例を紹介したいと思います。

個人的に一番有効性を感じるのは、多数のシートから好きなデータを抽出できる、という点です。

例えば下記のようにシートが複数あって、
それぞれのシートに下記のように色々な場所にデータが点在しているとき、

ただ、下記のように各シート名やデータがある行と列が別に表として作られているとき、

このような時に直接入力しようと思うと、この表を見ながら「＝No1!R1C1」みたいな形で手入力する必要があります。

ただ、このようなときにINDIRECT関数を使えば、この表にあるデータを使って、各シートのデータを参照することができるのです。

具体的には下記のように記載すれば、各シートのデータを表示することができます。

↓

今回のようにシート数が8個程度であれば直接入力も可能かもしれませんが、
例えば顧客名簿・製品明細・見積明細等で、非常にたくさんのシートが作られており、各シート名がID名か顧客名で作られている、といった状況の場合、
そのIDのリストや顧客名のリストは別表で管理されているかと思いますので、
その別表を見ればシート名だけは分かる。
またフォーマットが全て同じであればデータがある場所も分かる。
ただ、データが直接取り出せない、、、
といった状況でこのINDIRECT関数は非常に重宝されるかと思います。