【コピペでOK】スプレッドシートの関数で『列のアルファベット』を取得する方法

「EXCELなんか無くなって、みんなGoogleスプレッドシートに移行すればいいのに」と、いつも本気で思っている、たぬ(@tanuhack)です!

仕事でEXCELやスプレッドシート使ってると、列の番号じゃなくて、列のアルファベットを取得したいときってありませんか?

COLUMN関数を使えば、列の番号は取得できますが、列のアルファベットは取得出来ませんね…。

get-column-alphabet1

ところが、これは関数を組み合わせると簡単に実現できるんですよ!

今回紹介するハックは、列のアルファベットがZからAAに移動したときにも対応できるので、けっこう使い勝手良くておすすめです♪

列のアルファベットだけ取得する方法

本日のゴール
=SUBSTITUTE(ADDRESS(ROW(),COLUMN(),4),ROW(),"")

主に、ADDRESS関数とSUBSTITUTE関数を組み合わせます。

今回は例として、『AB6』セルの『AB』だけを抽出したいとして、話を進めていきますね。

get-column-alphabet2

ADDRESS関数のおさらい

構文
=ADDRESS(①行番号, ②列番号, [③参照の型])

ADDRESS関数は、指定した行番号と列番号に対応するセルの参照(番地)を文字列で返します。

①行番号

=ADDRESS(①行番号, ②列番号, [③参照の型])

セル参照に使用する行番号。今回は動的に行番号が欲しいので、『ROW』関数を使います。

get-column-alphabet3

=ADDRESS(ROW(), ②列番号, [③参照の型])

②列番号

=ADDRESS(ROW(), ②列番号, [③参照の型])

セル参照に使用する列番号。今回は動的に列番号が欲しいので、『COLUMN』関数を使います。

get-column-alphabet4

=ADDRESS(ROW(), COLUMN(), [③参照の型])

[③参照の型]

=ADDRESS(ROW(), COLUMN(), [③参照の型])

1〜4の数字の中から選ぶことが出来ます。

  • 1または省略:絶対参照(AB6セルの場合『$AB$6』で返します)
  • 2:行は絶対参照、列は相対参照(AB6セルの場合『$AB6』で返します)
  • 3:行は相対参照、列は絶対参照(AB6セルの場合『AB$6』で返します)
  • 4:相対参照(AB6セルの場合『AB6』で返します)

今回は絶対参照の『$』がいらないので、『4』を指定します。

=ADDRESS(ROW(), COLUMN(), 4)

get-column-alphabet5

これで、『AB6』セルの参照(AB6)が取得出来たので、次に紹介する『SUBSTITUTE』関数で、『AB6』の『6』を空白に置換させ『AB』として値を返すようにします。

SUBSTITUTE関数のおさらい

構文
=SUBSTITUTE(①文字列, ②検索文字列, ③置換文字列, [④置換対象])

SUBSTITUTE関数を使うと、用意した文字列に含まれている特定の文字列別の文字列に置換することが出来ます。

今回は『AB6』という文字列から『6』を『空白』に置換させることで、本記事の目的である『列のアルファベットだけ』を取得しようという魂胆である。

①文字列

=SUBSTITUTE(①文字列, ②検索文字列, ③置換文字列, [④置換対象])

この文字列は、先程のADDRESS関数で出した数式を使います。

=SUBSTITUTE(ADDRESS(ROW(), COLUMN(), 4), ②検索文字列, ③置換文字列, [④置換対象])

②検索文字列

=SUBSTITUTE(ADDRESS(ROW(), COLUMN(), 4), ②検索文字列, ③置換文字列, [④置換対象])

検索する文字列は、動的な行番号なので『ROW』関数ですね。

これで『AB6』セルじゃなく、どこのセルでも文字列の中から行番号を検索することができます。

=SUBSTITUTE(ADDRESS(ROW(), COLUMN(), 4), ROW(), ③置換文字列, [④置換対象])

③置換文字列

=SUBSTITUTE(ADDRESS(ROW(), COLUMN(), 4), ROW(), ③置換文字列, [④置換対象])

行番号が検索できたら、その行番号を今度は『空白』に置換します。

スプレッドシートでは『""』を入力すると空白にすることができます。

ダブルクオーテーション『""』の間にスペース『" "』を入れてしまうと、空白ではなくスペースに置換されてしまうので注意しましょう。

=SUBSTITUTE(ADDRESS(ROW(), COLUMN(), 4), ROW(), "", [④置換対象])

[④置換対象]

=SUBSTITUTE(ADDRESS(ROW(), COLUMN(), 4), ROW(), "", [④置換対象])

置換対象は『②検索文字列』が何番目に出てきたら『③置換文字列』に置換するかを選ぶことが出来ます。

ただ、今回は不要なので使いません。

=SUBSTITUTE(ADDRESS(ROW(),COLUMN(),4),ROW(),"")

これで本記事の目的であるスプレッドシートの関数で『列のアルファベット』を取得する方法を実現することが出来ましたね♪

get-column-alphabet6

さいごに

今回は、スプレッドシートの関数で『列のアルファベット』を取得する方法を紹介させていただきました。

その動的な列のアルファベットと『INDIRECT』関数を組み合わせると、仕事で自動化できることが増えてきます。

【超便利】スプレッドシートの『INDIRECT』関数で動的にセルを指定する方法

動的なデータに対応できるように、少しでも手動で変更することを減らしていって、最高のスプレッドシートを作ろう(^o^)