EXCELで範囲を可変にするINDIRECT関数の使い方

EXCELで範囲を可変にするINDIRECT関数の使い方

前回OFFSETでの範囲を可変にさせる方法をご紹介しましたが、今回はINDIRECT関数とCOUNTA関数を使用して同じ事が出来ますので、ご紹介いたします。

INDIRECT関数

この関数についてMicrosoft社の説明を見てみると下記になります。

指定される文字列への参照を返します。セル参照はすぐに計算され、結果としてセルの内容が表示されます。INDIRECT 関数を使うと、数式自体を変更しないで、数式内で使用しているセル参照を変更することができます。
Officeサポート、INDIRECT関数から参照

この内容で何となくでもわかるのであれば、前回使用したOFFSET関数よりは理解しやすいかもしれません。

INDIRECT動作について

関数の作り方としては、「INDIRECT(参照文字列, [参照形式])」で指定をさせますが、参照形式については、特に気にしなくても問題ありません。EXCELの参照形式をR1C1形式にしている人は指定をする必要があると言う程度になります。

この参照文字列の指定について「=INDIRECT(“B”&D2)」と言う関数でどんな動きをするか見て下さい。

INDIRECT関数でセルの参照をさせるとこの様になります。

このとおり、D2に入れた数字をもとに、B列の参照範囲が変わる事がわかると思います。
これが数式自体を変更しないでセル参照を変更させるという事になります。

INDIRECT関数の参照について

=INDIRECT(“B”&D2)
①INDIRECT(D2)だけだと、D2に入った数字・文字を参照するので、「1」だとどの列を参照していいか分からない。
②そのため、B列参照が固定しているので「”B”」ダブルコーテーションで囲んで文字を参照させて、「&」演算子によって次のセルとあわせています。
③「”B”&D2」とする事で、B列の何行目という事が自由に指定し、INDIRECTによってそのセルが参照されます。

参照範囲を可変にするには

先程の参照範囲について何となくわかると、可変にする方法も分かりやすくなります。

この様な表の場合に合計部分を可変させるために、H1のセルにINDIRECTとCOUNTAを使用してこの様な関数を入力しております。

「=SUM(F2:INDIRECT(“E”&COUNTA(F:F)))」

先程範囲を指定したやり方にCOUNTAが足された感じになっただけになります。
OFFSETの時は「COUNTA」に「-1」してなかった?と思う方もいると思いますが、そこまで理解が出来れば色々な関数なども使っているのではないでしょうか。

可変範囲にさせた際の説明

=SUM(F2:INDIRECT(“F”&COUNTA(F:F)))
①=SUM(F2:
ここで合計をF2からスタートさせる
②INDIRECT(“F”&COUNTA(F:F))
E列のCOUNTA(F:F)とする事で、空白以外のセルの数を数えます。

青色部分がそうなるのですが、12個あります。前回OFFSETの時はスタートが「F2」からでしたが今回は「F」から指定をさせるので、「12」を返す事で「F12」と言うセルまでが参照されます。

注意点

COUNTAの範囲が列参照をさせているので、その列に合計をしたい数字以外の物や何か入ってしまう事で参照範囲がずれる事があります。
また、表の先頭行などが空いていた場合にはその分だけ行がずれまずので、「+1」などをして行をずらす必要があります。

まとめ

前回のOFFSET関数と比較して使い易い方を使って頂く事が一番です。
範囲を可変にするという事については、そこまで関数を入れ子にして使用する事もなく出来ますので色々と使って頂けると非常に便利になるかと思います。

  • シェア
  • twitter
The following two tabs change content below.

岩原正樹

コメントはこちらをクリック

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

  • twitter
  • このエントリーをはてなブックマークに追加
  • Pocket
PAGE TOP
LINE it!