【スプレッドシート】列のアルファベットを抜き出す方法(address, match, substitute)｜セル番号ではない。指定したセル,スプシ,Excel（初心者向け、わかりやすく解説）

スプレッドシートやExcelで指定した内容と一致するセルのアルファベットのみを抜き出したい場合があります。

数式を記述するときに「あれ、この列の元となっている列のアルファベットなんだったっけ？」というようなときです。

列のアルファベットが抜き出せると、indirect関数を使えば動的に数式を変更することもできます。

結論

初めに結論から言うと以下の数式になります。

=substitute(address(row(検索対象のセル),match(検索対象のセル,検索範囲,0),4), row(検索対象のセル),"")

実例は以下のようになります。

=substitute(address(row(BD15),match(BD15,$15:$15,0),4), row(BD15),"")

この数式を使えば、以下のように検索範囲の中で同じ文字列を含むセルの列のアルファベットのみを抜き出すことができます。

数式の解説

=substitute(address(row(検索対象のセル),match(検索対象のセル,検索範囲,0),4), row(検索対象のセル),"")

この数式は大きく4つの関数でできています。

row: 行番号を返す

row関数は指定したセルの行番号の数値を返します。

例えば、row(B15)とすれば、15を返します。

単純に１とか、行番号を直接入力してもいいのですが、明示的にどのセルを検索対象としているかをわかりやすくしています。

address： 指定したセルのアドレス（A1や$Aなど）を取得

基本構文

addressは今回の数式の中で一番肝となる関数です。行番号と列番号を指定するとそのセルの位置（アドレス）を返してくれます。

ADDRESS(行番号, 列番号, [参照の型], [a1], [シート名])

第3引数以降はオプションです。今回は第3引数の[参照の型]まで指定しています。

アドレスとは、$A$1やA1などです。＄をつけるか（絶対参照とするか）は、第3引数の[参照の型]で指定します。

参照の型｜第3引数の指定

第3引数の参照の型は１～４までの数字を指定することができます。

[a1]｜第4引数

今回は使用していませんが、第4引数の[a1]はtrueかfalseで指定します。デフォルトはtrueなので、指定するとすればfalseになります。

falseを指定すると、R1C1形式で行は絶対参照、列は相対参照として結果を返します。

=ADDRESS(2,3,2,FALSE)

//結果
R2C[3]

実例

address(row(検索対象のセル),match(検索対象のセル,検索範囲,0),4)

今回は上記のような指定方法をしています。実際には以下のように使います。

address(row(BD15),match(BD15,$15:$15,0),4)

第1引数はBD15の行番号、すなわち「15」。第2引数はmatchの実行結果を列番号としています。

参照の型は「4」なので、$マークなしの相対参照で返します。

(参考) Microsoft ADRESS関数

match： 指定したセルが何番目か返す

基本構文

address関数の行番号の指定で使用しているのがmatch関数です。

「検索対象の値 or セル」と「検索範囲」を指定して、対象の範囲の中でその値を持つセルが何番目にあるかを整数で返してくれる関数です。

MATCH(検査値, 検査範囲, [照合の型])

※列番号や行番号ではなく、検索範囲の中の位置を返します。

[照合の型]｜第3引数

第3引数の[照合の型]が重要で、これによって検索方法や返ってくる値が異なるので注意が必要です。

-1, 0, 1のいずれかで指定します。最もよく使うのは「0」の完全一致かと思います。

検索値を基準として、完全一致かすぐ上か、すぐ下の値を返すということです。

match関数の注意点

ワイルドカードが使える

第3引数で「0」の完全一致を指定した場合、次のワイルドカードが使用できます。

実例

match(検索対象のセル,検索範囲,0)

今回は上記のような指定方法をしています。実際には以下のように使います。

match(BD15,$15:$15,0)

これは、BD15セルと完全に同じ値を持つセルの位置を行15全体の中から探すという処理になります。

(参考)　Microsoft MATCH 関数

substitute： 文字を置き換える

基本構文

最後に、substitute関数で文字列の置き換えをしています。

SUBSTITUTE(文字列, 検索文字列, 置換文字列, [置換対象])

第1引数の「文字列」では、置換を行う対象のデータが入ったセルを指定するのが一般的です。

[置換対象]｜第4引数

第4引数の置換対象は、マッチした何番目の要素を置換するかを整数で指定するものです。

指定したすべての文字列を置き換えたい場合は、第4引数は省略します。

SUBSTITUTE(文字列, 検索文字列, 置換文字列)

一番最初にでてきたものだけ置換して、それ以降は置換せずそのままにしたい場合は1とします。

SUBSTITUTE(文字列, 検索文字列, 置換文字列, 1)

2番目であれば２、3番目であれば3とします。

実例

substitute(address(row(検索対象のセル),match(検索対象のセル,検索範囲,0),4), row(検索対象のセル),"")

今回は上記のような指定方法をしています。実際には以下のように使います。

substitute(address(row(BD15),match(BD15,$15:$15,0),4), row(BD15),"")

これは、address関数の実行結果（例： S15）を検索対象として、row(BD15)すわわち「15」を空文字で置き換えるという処理になります。

adress関数の処理結果がS15であれば、substituteを行うと「S」だけを取得することができます。

（参考）Microsoft SUBSTITUTE関数

indirect関数と組み合わせる

ここからは参考ですが、このadress, match, substituteを使って得た列のアルファベットを有効活用する方法にindirect関数があります。

indirectはセルの指定を文字列で行える便利な関数です。

基本構文

indirectの基本構文は次のようになっています。

INDIRECT(参照文字列, [参照形式])

デフォルトはA1形式（A1やBC12など）でセルのアドレスを指定します。

例えば、C7セルの値を表示したいときは次のように記述します。

indirect( "C7" )

セルのアドレスを文字列で指定するというのがポイントです。

[参照形式]

[参照形式]はtrueかfalseを指定します。通常はC7といった指定を行うA1形式での指定となっています。R1C1形式にしたいときは、第二引数をfalseにします。

indirect(C7)とindirect(“C7”)は何が違うの？

indirectを使うときに混乱しがちなできごとですが、例えば次のような似た数式があるとします。

=indirect(C7)

=indirect("C7")

これはカッコの中を文字列にしたかそうでないかの違いです。実際の処理は大きく異なります。

文字列でセル番号を指定

=indirect("C7")

文字列でセル番号を指定するのは一般的な使い方です。

これでC7セルの値を表示することができます。

セル番号を直接指定

=indirect(C7)

セル番号を直接入力した場合、指定したセルの値に参照したいセルのアドレスが記載されている必要があります。

indirect(C7)とすることで、C7セルの値を文字列として呼び出します。

このため、参照先のセルの値がセル番号以外になっているとエラーになります。

応用編

adress, match, substituteを使って得た列のアルファベットとindirectを組み合わせると次のような記述ができます。

=indirect(BD$14&row())

これは、現在の行番号が「16」だとすると、セル番号「I16」の値を表示する数式です。

これを使うと間に列を追加したり、どこかで列が削除になったとしても、常に一定の値を参照することができます。

とても便利なのでぜひ活用してみてください。