XLOOKUPの範囲を可変にする方法

はじめに
手順
エラーの解消
テキストを加えて数式を仕上げる
数式を改行して可読性を改善する
まとめ

はじめに

こんなことをやりたいケースがどれくらいあるのか分かりませんが…

Googleスプレッドシート上にこのようなデシジョンテーブルがあったとして

テストケースの行に書かれたテストケース番号を利用して、因子と水準を参照したいとします。

このとき、どんな数式を使えばよいか考えてみます。

デシジョンテーブルはソフトウェアテストに関わる方以外は馴染みがないかもしれませんが、一通り流れを見ていただければXLOOKUPの範囲を可変にする方法が分かると思うので、お付き合いいただければと思います。

手順

手順を単純化するため、まずは条件1に絞って考えます。

参照したい値に対してどんな値を渡せばよいかを逆算して考えていきます。

XLOOKUP

まずは、XLOOKUPで「●」を検索して、「●」がついている行の水準を返すようにします。

検索キーを「●」にします。

「検索範囲」は仮で、条件1でケース1のところである「C2:C4」にします。

「結果の範囲」は水準の範囲である「B2:B4」に。

まずは水準の「A」という値が返ってきました。

XLOOKUP("●",C2:C4,B2:B4)

OFFSET

検索範囲を「C2:C4」とした部分ですが、実際にはケースの数字によってC～Gが切り替わるようにしたいです。

このように関数の引数にある範囲を可変にするには、OFFSET関数を使います。

「セル参照」には基準となるセルを入れれば良いです。今回は先ほどの範囲の開始セルであった「C2」とします。

オフセット行とオフセット列は、基準のセルとは別のセルを範囲の開始セルとする場合に、基準のセルからの距離を入力します。
今回は基準セルをそのまま範囲の開始セルとして利用するので、ここは「0,0」としておきます。

高さと幅ですが、「C2:C4」は高さ3幅1なので、「3,1」とします。

これで、先ほどまでと同じ「A」が返ってくる状態です。

OFFSET(C2,0,0,3,1)

INDIRECT

まだOFFSET関数の中に「C2」が残っていて、依然としてケース1にしか対応できていません。

この部分を改良していきます。

まずはINDIRECT関数を使って、「C2」だった部分を「”C2″」という文字列にしていきます。

結果はこちらです。

これによってセルを直接指定するのではなく、セルの名前の文字列を指定すればよくなりました。

これは、この後ADDRESS関数を使う準備となります。

INDIRECT("C2")

ADDRESS

いよいよADDRESS関数を使って「”C2″」の部分を可変にしていきます。つまり、現在はケース1の前提で「”C2″」と書いている部分を、ケース2のときは「”D2″」、ケース3のときは「”E2″」が返ってくるような形を目指します。

ADDRESS関数では、行、列を数字で指定することでセルの名前が返ってきます。C2セルは上から2行目、左から3列目ですから、「2,3」を指定してみましょう。

結果がこちらです。

ADDRESS(2,3)

MATCH

ついに大詰めです。先ほど「”C2″」を「2,3」に置き換えましたが、「列が左から3」というのはケースの番号によって変わってきますから、ここをMATCH関数を使って可変にしていきます。

MATCH関数は、指定した1行または1列の中でキーを検索し、何番目のセルにその値があるかを返してくれます。

最初に検索キーを指定するのですが、仮でケース1の「1」としておきます。

範囲はケースが記載された「C1:G1」とします。

このままだとエラーになってしまいます。

理由は、MATCH関数で「3」という値が返ってくることを期待していましたが、実際には「1」が返ってきているからです。

ずれた理由は範囲を「A1:G1」にせず、「C1:G1」にしたためですが、実際に検索に使うのはC列以降ですから、A列とB列を範囲に含めるのは好ましくありません（例では「因子」「水準」が入っているので問題になりませんが、検索に使う以外の部分に数字が入っていたら、やはり結果が期待と異なってしまうためです）。

そこで、対策としてA、B2列分の「+2」をして値を調整します。

これで期待通りの結果となります。

いよいよ最後のステップです。
MATCH関数のキー値を仮で「1」としていましたが、この数字が切り替わるようにします。

検索キーの「1」をセル参照の「A16」に置き換えるだけでOKです。

このようになりました。

MATCH(A16,C1:G1)

エラーの解消

できた数式をコピーしてケース2～ケース5に貼り付けてみると#N/Aエラーが発生してしまいます。

ケース2の数式を見ると、意図せず範囲が下にずれてしまった部分があるのが分かります。

1つはMATCH関数の「範囲」、もう一つはXLOOKUP関数の「結果の範囲」です。そこで、「$」を数字の前につけることで、コピーして貼り付けたときに数字が変わってしまわないようにします。

一方、MATCH関数の「検索キー」は数字が切り替わってほしいのでここはそのままにします。

MATCH関数の「範囲」を修正します。

XLOOKUP関数の「結果の範囲」も修正します。

この状態で改めてケース2～ケース5に数式をコピーすると、今度は意図通りの結果が得られました。

テキストを加えて数式を仕上げる

最後にテキストを加えたり、条件2、条件3についても同様に数式を作って目的であった情報がすべて表示されるようにします。

条件2と条件3

XLOOKUP("●",OFFSET(INDIRECT(ADDRESS(2,MATCH(A16,C$1:G$1)+2)),0,0,3,1),B$2:B$4)

に対して、条件2は

XLOOKUP("●",OFFSET(INDIRECT(ADDRESS(5,MATCH(A16,C$1:G$1)+2)),0,0,3,1),B$5:B$7)

条件3は

XLOOKUP("●",OFFSET(INDIRECT(ADDRESS(8,MATCH(A16,C$1:G$1)+2)),0,0,3,1),B$8:B$10)

で表示できます。詳細な説明は省きますが、ADDRESS関数の「行」と、XLOOKUP関数の「結果の範囲」のみ条件2または条件3に合わせて修正しています。

数式の結合

数式や文字列を結合するには「&」を使います。

改行

セルに直接テキストを打ち込んだ場合と違って、数式の結果を改行する場合には「CHAR(10)」という関数を使います。

完成した数式

以上を踏まえて完成した数式はこちらです。

="条件1："&XLOOKUP("●",OFFSET(INDIRECT(ADDRESS(2,MATCH(A16,C$1:G$1)+2)),0,0,3,1),B$2:B$4)&CHAR(10)&"条件2："&XLOOKUP("●",OFFSET(INDIRECT(ADDRESS(5,MATCH(A16,C$1:G$1)+2)),0,0,3,1),B$5:B$7)&CHAR(10)&"条件3："&XLOOKUP("●",OFFSET(INDIRECT(ADDRESS(8,MATCH(A16,C$1:G$1)+2)),0,0,3,1),B$8:B$10)

表示はこのようになります。