INDIRECT関数は貫通性能が高い
INDIRECT関数使ってる?
知らない人向けに説明をと思ってましたけど、詳しく説明して下さる素敵な記事に溢れてたので割愛しま…すと言いたいところですが、一応最低限はサクッと説明します。
=INDIRECT(参照文字列)
と、こんな記述をします。
ええと例えば
=INDIRECT("A1")
なんて書くと文字列として指定したA1の値を参照してくれたり、
=INDIRECT("A1:C3")
だとこの範囲を参照してくれます。
うーん、だから何?となります。
そうなんですよね、これ例えば参照するなら
=A1
とか
=A1:C3
と、これで解決してしまいますよね。
ただこのINDIRECT関数、「文字列」の範囲だったりを返すんですよね。
単なる関数での設計だと関数一つ一つの理解などが必要になりますが、
「文字列」はある程度の操作を覚えるだけで扱えるので、
作成者の「考え方」をダイレクトに反映できるものなのでは?と僕は考えます。
Excelにおける「文字列」について考えてみる
Excelって不思議なことに意識して使いだすと「数値」と「文字列」でいきなりつまづく事がありますよね。
ここでいろいろ調べていくと
「数字じゃなければ文字列でしかない」
とか
「 & でつなげときゃ文字列になる」
「とりあえず1掛ければ数値になる」
となかなか香ばしい暴論
と色んな意見に行きつきます。
そしてそこからまた深く学習してこれらはとても多用する大事な事だとも気づきます。
特に & での接続なんて基本操作の一つなので滅茶苦茶使います。
例えば、A1に何らかの数値(ここでは「1」とかにしてみます)が入っていたとして、
どこかしらのセル…ここではC1にでも
="文字列"& A1
と入れてみましょう。すると「文字列1」と返ってきますよね。じゃあこれを
="B"& A1
にするとどうでしょう?
C1はこの時点で「B1」という文字列になります。
では次にA1セルの値を「2」に変えてみましょう。
そうですね、C1の値はこれで「B2」という文字列になりました。
こういった感じに「&」を使って結合することで文字列をいろいろこねくり回すことが可能になります。
=MONTH(MAX(日付の入っている範囲))&"月集計"
一例としてこんなのももう毎回使ってるぐらいです。
文字列とINDIRECT関数の関係性についても考えてみる
じゃあ一旦A1は「1」に戻してC1は
=INDIRECT("B"& A1)
と書き直してみて下さい。おそらく「0」が返ってきます。
ここでピンと来た方はその考え、間違っていないと思います。
この記事はここでそっ閉じしてください。
そうですねこれはINDIRECT関数の中で文字列結合を行い、結果として出来上がったB1という文字列をアドレスとして値を参照しています。
これによって参照したいB列の行番号の指定をA1セルによってコントロールする簡易的な仕組みが出来上がるわけですねー。
INDIRECT関数の「考え方」
自論なので正解ではないのですが、INDIRECT関数の単純な参照に関してはまず、
参照元の「どれ」に用があるのかを考えると使い勝手が良くなるのでは?と考えています。
どういう事?ってその前に少し例として…
とあるセルに入力規則でリストを作りたい。
その参照元が別シートのテーブルだった場合に
と入れて上手く行かないはあるあるですね。僕だけ?
それはさておき、
='シート名'!範囲
とりあえずここはこれで解決ですね。
しかも範囲を名前定義すれば
=定義された名前
と、これはもう定番ですね。
ですが、これ実は
とINDIRECT関数で文字列参照にすると最短で参照出来ます。もちろん可変です。
(えっ?知ってる…?)
この辺りは解説なんかでよく見ますね。
さてこの話、
そもそもなぜ単なるテーブル範囲ではエラーになりINDIRECT関数だと上手く行くのか。
(実は同シート内なら上手くいく。なんで?)
ここからは技術的な正解というよりも、考え方に絞っていきます。
まずさっきの例を見てみます。
1.=テーブル2[name]
2.=INDIRECT("テーブル2[name]")
まずそれぞれは一体何を言ってるか。
1は「テーブル2[mame]」という「範囲」の事を指している。そのままですね。
じゃあ2はどうでしょう?INDIRECT関数の仕様も踏まえると
INDIRECT関数で指定した
"テーブル2[name]"という文字列に該当する範囲の「値」を指しています。
これらをVBAで例えるなら、
1は「オブジェクト」で、2はそのオブジェクトから取り出した「配列」と言えばしっくりくるでしょうか。
ここで少し話を戻して入力規則について、このリストは直接値を入れることも可能ですし、これが求めてるものは範囲だとしてもその実「値」を指示する必要があるって事がわかります。
(もちろんテーブルの構造化参照など、技術的な解説が必要なところなので、あくまで個人的な考え方として話を進めています。)
さて、話題はINDIRECT関数でしたね。
先ほどの話を踏まえ、改めて整理すると、
この関数は
「指定した文字列に該当するセルまたは範囲にある値を参照または取り出す」という考えが感覚として掴み易いのでは?と考えます。
INDIRECT関数は急にやってくる
この関数、正直意味わからんですね。
名前の割に値鷲掴みで取得するし、文字列だから絶対参照かと思えば絶対的な可変参照だし…
ここまでお話ししておいてアレですが、
もしこの関数で「どうやって使うの?」ってお悩みの方は是非この関数の事は今すぐ忘れて下さいね。
で、近い将来数式設計で悩んだ時、こいつを思い出してみてください。絶対刺さるんで。貫通するんで。
以上!
Discussion