一番上の入り口へ > excel > ちょいわざ > ちょっとかっこ良く柔軟にすっきりと参照先シートを文字で指定するちょいわざ

ちょっとかっこ良く柔軟にすっきりと参照先シートを文字で指定するちょいわざ

セルに直接 E4 などと 参照先を指定する場合は、手で直接入力すれば良いのですが セルの値を間接的に参照したい場合などがたまにあります。 そういう時に知っておくと便利なワザです。

まずその時によく使用する関数を説明します。
関数 特徴 使用例
index セルの場所を数値として指定したい場合に使います。そのためmatch関数の結果を使いたいときによく使います。 offset関数も似ています。 例えば、index(A1:A15 , 5,3)とすると、 C5セルに "りんご"って書いてあると、"りんご"が取得できます。 5,3は 範囲の相対指定であるため、 B3:A15などとずらすと、始点が ↓2 →1 ずれます。
indirect セルの場所を文字列として指定したい場合に使います。 address関数が似ています。 =indirect("C5") と書くと、同じようにりんごが取得できます。
さらに、B5セルに C5 と書いてあり、 =indirect(B5)としても { → indirect("C5" :B5セルの値) → =indirect("C5") というように Excelが自動的に計算してくれて最終的に} りんごが取得できます。
このような間接的な指定方法を知っておくと多少面倒ですが、使い勝手の柔軟性がとても上がります。

その他、offset , areas もあります。 場所を表す関数はこちら。

他のファイルに対しても indirectできますが、ファイルを開かなければエラー参照となります。

index , indirect関数の使用例(表の上側)です。
いずれも、表内の C5の値を表示できます。



indirect と indexの違いと、 間接参照の応用例

複合サンプル

例えば、 xlsの1シート目に目次があり、 2〜10シート目に同じフォーマットのシートがあるとします。 それぞれのシートで売上などが集計されており、 2シート目以降C4 セルに必ず"合計の欄のセル"がある場合 ひとつひとつ、シート名を入れるのが面倒です。

そんな時は、indirectのように文字列で参照できるようにしておくと便利です。
※シートの指定は ! を後ろに置きます。

参照に関する補足

意味 指定方法 補足
絶対参照 $ $E$13 や A$13など
範囲 : A1:A13 , $A1:$E13, E:E など Range関数(VBA)、areas関数がおすすめ
シート名 ! シート名!A13
ファイル名 '' 'ファイル名.xls'シート名!A1 ファイル名指定はメンテナンス上あまりお勧めしません。 絶対パスになってしまうので、ファイルが移動しにくいのと、他ファイル参照のリンク対応はマニアックな知識なので。




その他のちょいわざはこちら