GoogleスプレッドシートやExcelを使っているとき、列情報の英語を関数に組み込みたいという場合があります。
図のようにＡ列には「Ａ」、Ｂ列には「Ｂ」の様に文字で入力せずに関数で抽出する場合、「="A"」「="B"」などで対応できますが、この場合、オートフィルを使用したり他のセルへコピー＆ペーストなどした場合、ただの文字の為都度変える必要があります。
関数を効率よく使うことで、セルのオートフィルや複数のセル（列や行）で列番号を効率よく抽出したい場合に有用に使えます。
| Googleスプレッドシート、Excel（エクセル）双方で使える手法です。 |
セル情報を取得するaddress関数
address（アドレス）関数は、指定したセルの位置情報を文字列として返してくれる関数です。
アドレス関数は以下の様な作りになっています。
| =ADDRESS(行番号, 列番号, [参照の種類], [参照形式], [シート名]) | |
| ①行番号 | セルの行番号を指定します。 |
| ②列番号 | セルの列番号を指定します |
| ③[参照の種類] | （オプション） アドレスの絶対参照形式を指定します。デフォルトは1で、$A$1のような絶対参照形式で返されます。0を指定すると、相対参照形式でアドレスが返されます。 ・1･･･ $A$1のような絶対参照形式でアドレスが返されます。これがデフォルトの値です。 |
| ④[参照形式] | （オプション） 論理値を指定します。デフォルトはTRUEで、A1形式のアドレスが返されます。FALSEを指定すると、R1C1形式のアドレスが返されます。 |
| ⑤[シート名] | （オプション） アドレスが存在するシートの名前を指定します。デフォルトは空白文字列で、現在のシートが使用されます。 |
| ※今回の記事では①行番号②列番号③[参照の種類]までを使用します。 |
| =ADDRESS(1,1,4) |
とした場合、
1･･･セルの行番号→1行目
1･･･セルの列番号→1列目「A列」
4･･･[参照の種類]→相対参照
となり「A1」と返ってきます。
しかしながら、この関数ですとオートフィルや、他のセルへのコピー＆ペーストをしても引数に変化が起こらないため、「=ADDRESS(1,1,4)」のままでどのセルでも「A1」と返ってきてしまいます。
列の抽出にCOLUMN関数を使ってみる
COLUMN関数は、指定したセルの列番号を返す関数で、以下の様に使用します。
| =COLUMN(A1) | A列は1列目なので「1」と返ってきます。 |
| =COLUMN(B10) | B列は2列目なので「2」と返ってきます。 |
| =COLUMN(H100) | H列は1列目なので「100」と返ってきます。 |
そのためアドレス関数の式の列番号の部分と組み合わせて使うことが叶います。
| =ADDRESS( 行番号,列番号,[参照の種類]) |
先程の式「=ADDRESS(1,1,4)」の列番号部分にCOLUMN関数を入れてみると、
| =ADDRESS(1,COLUMN(A1),4) |
となり、A1は1列目であるため「COLUMN(A1)」の結果は「1」であるため、「=ADDRESS(1,1,4)」となり、「A1」と返ってきます。
| =ADDRESS(1,COLUMN(A1),4) |
横にオートフィルをかけると、A1の部分が相対参照であるために、B1･C1･D1･E1･F1･G1となることで、返り値が「A1･B1･C1･D1･E1･F1･G1」となります。
これによりどのセルへコピー＆ペーストしても使えますが、「A1」の「1」の部分が相対参照ですので、行が固定されていません。
そのため、行数だけ絶対参照にすると、アクティブで使いやすくなります。その場合式は、
| =ADDRESS(1,COLUMN(A$1),4) |
となります。
「A1、B1」などの行番号「1」を取り除く
今回の目的は列の英語を抽出することですので、行番号が不要になります。行番号を先程の式を使って取り除いていきましょう。
| ①LEFT関数を使ってみる |
| ②SUBSTITUTEを使ってみる |
の2つの手法を取り上げていきます。
※ここでは基本式を「=ADDRESS(1,COLUMN(A$1),4)」とし、「A1」部分は行側を絶対参照「A$1」にしています。
LEFT関数を使ってみる
LEFT関数は、テキスト文字列の先頭から指定した数の文字を取得するために使用されます。
| =ADDRESS(1,COLUMN(A$1),4) |
の返り値は「A1」でしたので、
| =LEFT(ADDRESS(1,COLUMN(A$1),4),1) |
とすることで「A1」の左1つ目を取り出す指示となり「A」となります。
ただし、この場合列番号が1桁であることが必要となり、AA行などで行っても1つ目の取り出しとなるため「A」となってしまいます。
そのため、行数を「1」で絶対参照していますので、LEN関数と組み合わせて行ことでどのセルでも使うことが可能になります。
※LEN関数は、指定されたセルまたはテキスト文字列の長さ（文字数）を返す関数です。
組み合わせると以下の様になります。
| =LEFT(ADDRESS(1,COLUMN(A$1),4),LEN(ADDRESS(1,COLUMN(A$1),4))-1) |
まず、基本式「ADDRESS(1,COLUMN(A$1),4)」の返り値は「A1」ですので、文字数をLEN関数で計測します。式をばらすと
=LEFT(ADDRESS(1,COLUMN(A$1),4),LEN(ADDRESS(1,COLUMN(A$1),4))-1)
=LEFT(「A1」,「A1」の文字数は2文字-1)
=LEFT(「A1」,1)
=「A」
となります。
この場合、文字数から絶対参照している行の値「1」を引き算して消しているため、どのセルでも機能します。
例えばBD134のセルに入れたとしても
=LEFT(ADDRESS(1,COLUMN(A$1),4),LEN(ADDRESS(1,COLUMN(A$1),4))-1)
=LEFT(「BD1」,「BD1」の文字数は3文字-1)
=LEFT(「BD1」,2)
=「BD」
となります。
SUBSTITUTE関数を使ってみる
SUBSTITUTE関数は、テキスト文字列内の特定の文字や文字列を別の文字や文字列に置換することができます。
| =ADDRESS(1,COLUMN(A$1),4) |
の返り値は「A1」でしたので、
| =SUBSTITUTE(ADDRESS(1,COLUMN(A$1),4),1,"") |
とすることで「A1」の「1」を「""」（空欄）に置き換えるということになります。
この式の場合は行番号が絶対参照となるために、行の数値が「1」以外になることはありませんが、万が一相対参照で「1」などの固定値以外の数値が返ってくる場合には使えません。
しかしながら、絶対参照のルールを参考例のように守れていれば、「1」を空欄（なにもない）に変えるだけですので、LEFT関数の時のように列番号が「AA」や「BD」、「AAB」など2桁や3桁になる想定をする必要はなく、LEN関数を組み込む必要性がありません。
さいごに
LEFT関数とSUBSTITUTE関数をどう分けるかは任意ですが、私の場合は、行番号を絶対参照している場合は式が簡素になるためSUBSTITUTE関数を、列・行双方が相対参照の場合はLEFT関数を使用しています。
ただし、行も相対参照となる場合、行数が2桁以上になったことも加味しなければなりませんし、そもそもの宣言数式が
| =ADDRESS(ROW(),COLUMN(A1),2) |
の様に変化させなければなりません。
この場合LEFT関数の書き方も以下のように行番号の数字桁数をLEN関数でカウントする工程も加えて変わってきます。
| =LEFT(ADDRESS(ROW(),COLUMN(A1),4),LEN(ADDRESS(ROW(),COLUMN(A1),4))-LEN(ROW())) |
複雑な関数を組み合わせていてどうしても列の英語が必要になった際、このやり方は有用です。ただし数式が結構ごちゃごちゃしてしまいがちですので、GoogleスプレッドシートではGASを使用するなどして、数式の破損を防ぐことも重要な対策といるかもしれません。