個人的に好きなINDIRECT関数

| トラックバック(0)

Excel でINDIRECT関数というものがあります。

これ、意外と使い道があるのですが、説明がしにくい!
説明があまりにしにくいから流行らないのでしょう。

同じく説明が難しいのが=ROW()という使い方。
この後ろのかっこは何なのかと。

要領を得ない説明をしていると、なんだ酔っ払ってるのかと言われるので困ります。誤解です。お酒なんか飲んだこともない。

◆INDIRECT関数
=INDIRECT(参照文字列、参照形式)のように使います。
今まで
①計算の対象範囲を変えたい
②SUBTOTALの表示形式を変えたい
③VLOOKUPで参照元を変えたい
④入力規則のリストを変えたい
と思ったことはないでしょうか。
そのような場合に使えます。

例えば、毎月引かれる所得税は交通費を引いた給与の額と扶養人数によって変わります。扶養人数に関係なければVLOOKUP関数だけですみます。

indirect1

つまり、上の表であればセルB342 に
=VLOOKUP(B341,A2:B339,2,1) と入力すれば表示されます。
しかし、扶養人数が変わるとなると対応できなくなります。
計算式をその都度変えなければなりません。

その時に、INDIRECT関数を使うと、解決できます。

indirect2

上の表であればセルB343に
=VLOOKUP(B341,A2:I339,INDIRECT("B342")+2,1) と入力すれば表示されます。
扶養の人数を変える場合はセルB342 を変えるだけです。

といった具合です。

 

①計算の対象範囲を変えたい
合計を表示させるSUM関数はあらかじめどこからどこまで足すか決める必要があります。

indirect3

こんな感じですね。
しかし、実際には途中で変えたい場合も有るわけで、そのようなときに使えます。

indirect4

セルC3 に、A1からどこまで足すか、その最後のセルを入力します。
C4には =SUM(A1:INDIRECT(C3)) と入力しています。

もしA1 から E1 までにしたければC3 を E1 に変更するだけです。

indirect5

こんなこともできます。

②SUBTOTALの表示形式を変えたい
オートフィルターで表示されているセルのみ計算の対象にする場合SUBTOTAL関数を使います。この場合、あらかじめ合計なのか平均なのか最大なのか何を表示させるのか決めておく必要があります。
しかし、実際には途中で変えたい場合も有るわけで、そのようなときに使えます。

indirect6

セルA9 には=MATCH(B9,A13:A22,0)
セルC9 には=SUBTOTAL(INDIRECT("A9"),C2:C6)

と入力してあります。

③VLOOKUPで参照元を変えたい
VLOOKUP関数で、他のデータを参照することができます。この参照元はあらかじめ決めておく必要があります。しかし、実際には途中で変えたい場合も有るわけで、そのようなときに使えます。

例えば、月ごとにシートがある場合

indirect7

シート、つまり何月か指定して表示させることができます。

indirect8

④入力規則のリストを変える
入力規則のリストを使うと、入力内容をリストから選択できるようになります。この場合あらかじめリストを決めておく必要があります。しかし、実際には途中で変えたい場合も有るわけで、そのようなときに使えます。

indirect9

例えば、上の図で大項目で「漁業」を選んだ場合は詳細に「海面漁業」と「定置網漁業」がリストとして表示される、「鉱業」を選んだ場合はそれに関係するリストが表示されるようにします。

まずは海面漁業、定置網漁業のセルを選択し、「漁業」と名前をつけます。名前は左上のマスに書きます。

indirect10

同様に、「鉱業」「建設事業」も行います。
そして、「データ」-「入力規則」-「設定」-「リスト」で「元の値」のところに
=INDIRECT(B1) と入力します。

indirect13

すると、大項目によって選択できるリストが変わります。

indirect11

indirect12

 

注意点は
INDIRECT引数2つ目の参照文字列は、文字列でなければなりません。
その為、もし数字を参照する場合は前後を" "で囲んでください。

例えば、先ほどの例で SUBTOTAL関数にINDIRECTを組み合わせました。最初の数式を=SUBTOTAL(9,A1:A6)とすると、9の部分は数字ですから、ここにINDIRECTを組合わせる場合、=SUBTOTAL(INDIRECT("C1"),A1:A6)となります。※" "がつきます。

一方、A1:A6にINDIRECTを組合わせる場合、文字列ですから=SUBTOTAL(9,INDIRECT(C1))でよいことになります。

先の例でセル範囲に名前をつけました。名前をつけるときもあらかじめ範囲を設定する必要がありますが、これも変えることができます。

「挿入」-「名前」-「定義」の「参照範囲」に
=OFFSET(シート名!$B$2,0,0,COUNT(シート名!$B:$B))
これは縦の変更を行う場合ですから、横にも変更する場合は

=OFFSET(シート名!$B$2,0,0, COUNT(シート名!$B:$B),COUNT(シート名!$2:$2))
のようにします。
因みにOFFSET関数の引数は
OFFSET(基準,行数,列数,高さ,幅)
となります。具体的な使い方は
http://officetanaka.net/excel/function/function/offset.htm
などをご参照ください。

トラックバック(0)

トラックバックURL: http://lsconsul.com/mt/mt-tb.cgi/128