Excelで列番号をアルファベットに変換する方法｜COLUMN・ADDRESS・SUBSTITUTEの組み合わせ技

スポンサーリンク

Excelで「この列って何列目？」はCOLUMN関数ですぐわかります。でも「列番号をアルファベットに変換したい」となると、専用の関数がないので困りますよね。

たとえば、マクロで列を動的に指定したいとき、数式内で列名を文字列として扱いたいとき、表ヘッダーを自動生成したいとき。こういった場面で「列番号 → A, B, AA…」の変換が必要になります。

実はCOLUMN・ADDRESS・SUBSTITUTEの3つを組み合わせれば、たった1行の数式で列のアルファベットを取得できます。この記事ではコピペで使える数式から仕組みの解説、実務での応用パターン、VBAでの実装まで幅広く解説します。

コピペで使える数式2パターン

まず結論から。すぐに使える数式はこちらです。

パターン1：数式を入力したセルの列を取得する

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

数式を入力したセルの列アルファベットを返します。たとえばD列のセルに入力すると「D」、AN列なら「AN」が返ります。

パターン2：列番号を指定して変換する

=SUBSTITUTE(ADDRESS(1,列番号,4),1,"")

列番号を直接指定するパターンです。たとえば =SUBSTITUTE(ADDRESS(1,40,4),1,"") と書くと40列目の「AN」が返ります。

セル参照で列番号を渡すこともできます。A1セルに列番号が入っている場合は次のようにします。

=SUBSTITUTE(ADDRESS(1,A1,4),1,"")

NOTE
行番号は「1」に固定するのがポイントです。理由はこの記事の「よくあるミスと対処法」で説明しますね。

数式の仕組みをステップごとに解説

一見むずかしそうに見えますが、やっていることはシンプルです。3つの関数がそれぞれ1つずつ役割を担当しています。

ステップ1：COLUMN関数で列番号を取得する

COLUMN関数は、セルの列番号を数値で返す関数です。

=COLUMN()

D列のセルで実行すると「4」が返ります。引数を省略すると、数式を入力したセル自身の列番号になりますよ。

特定のセルを参照させるときは =COLUMN(G5) のように書きます。G5のG列は7列目なので「7」が返ります。

ステップ2：ADDRESS関数でセル番地を作る

次にADDRESS関数を使って、列番号からセル番地の文字列を組み立てます。

=ADDRESS(1, 4, 4)

この数式は「1行目・4列目・相対参照」を意味します。結果は文字列「D1」です。

ADDRESS関数の引数は次のとおりです。

引数意味この数式での値
行番号セル番地の行1（固定）
列番号セル番地の列COLUMN()の結果
参照の型$記号の有無4（相対参照＝$なし）

第3引数を「4」にするのがポイントです。「$D$1」のように$記号が付くと、あとで行番号だけを取り除くのが面倒になります。「4」を指定すれば「D1」のようにシンプルな形で返ってきますよ。

ステップ3：SUBSTITUTE関数で行番号を取り除く

最後にSUBSTITUTE関数で、セル番地から行番号部分を空文字に置換します。

=SUBSTITUTE("D1", 1, "")

「D1」から「1」を取り除いて「D」だけが残る、という仕組みです。

これら3つのステップをまとめると、冒頭の数式になります。

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

COLUMN()→ADDRESS()→SUBSTITUTE()の順にデータが流れていくイメージですね。

実務で使える応用パターン

基本の数式がわかったところで、実務で役立つ応用パターンを5つ紹介します。

パターン1：別のセルの列アルファベットを取得する

特定のセルの列アルファベットを知りたい場合は、COLUMN関数に参照を渡します。

=SUBSTITUTE(ADDRESS(1,COLUMN(G5),4),1,"")

G5セルの列番号（7）をCOLUMN関数が返し、結果は「G」になります。

パターン2：連番の列アルファベット一覧を作る

A1セルに「1」、A2に「2」…と列番号を並べておき、B列に次の数式を入れます。

=SUBSTITUTE(ADDRESS(1,A1,4),1,"")

下方向にコピーすれば、列番号に対応するアルファベットの一覧表が完成します。26列目まで入れればA〜Z、27列目以降はAA、AB…と2文字の列名も正しく返ってきますよ。

パターン3：見出し行と組み合わせて列名を自動表示する

データの1行目に項目名がある表で、「何列目が何の項目か」を確認したいケースです。

=SUBSTITUTE(ADDRESS(1,COLUMN(),4),1,"") & "列: " & B1

B1セルの上に配置すれば「B列: 売上」のように、列アルファベットと項目名をまとめて表示できます。

パターン4：INDIRECTと組み合わせてセル範囲を動的に指定する

INDIRECT関数と組み合わせると、列アルファベットを使ってセル範囲を文字列で組み立て、動的に参照できます。

=SUM(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),1,"") & "2:" & SUBSTITUTE(ADDRESS(1,COLUMN(),4),1,"") & "100"))

この数式は「数式を入力した列の2〜100行目を合計する」という意味です。列を増やしてもコピーするだけで自動的に集計列が切り替わるため、動的な集計表を作るときに便利です。

パターン5：MATCH関数と組み合わせて列名を逆引きする

特定の見出し名が何列目にあるかをMATCH関数で調べ、その列名を取得するパターンです。

=SUBSTITUTE(ADDRESS(1,MATCH("売上",1:1,0),4),1,"")

1行目から「売上」という見出しを探し、その列アルファベットを返します。列の順番が変わっても自動追従するので、ダイナミックな表操作に使えます。

VBAで列番号をアルファベットに変換する

マクロを使う場合は、Cells(1, 列番号).Address(False, False) で取得できます。

Sub GetColumnLetter()
    Dim colNum As Long
    Dim colLetter As String
    
    colNum = 5  ' E列を取得したい場合
    colLetter = Left(Cells(1, colNum).Address(False, False), _
                    Len(Cells(1, colNum).Address(False, False)) - 1)
    
    MsgBox colLetter  ' "E" と表示される
End Sub

Cells(1, 列番号).Address(False, False) は「行=相対参照、列=相対参照」のセル番地（例: E1）を返します。そこから行番号の「1」を除けば列アルファベットだけが取れます。

もっとシンプルな方法として、Split関数を使う書き方もあります。

Function ColNumToLetter(colNum As Long) As String
    ColNumToLetter = Split(Cells(1, colNum).Address(True, False), "$")(0)
End Function

この関数はコードの中で ColNumToLetter(5) のように呼び出せばいつでも使えます。

よくあるミスと対処法

行番号にROW()を使うと誤置換が起きることがある

旧バージョンの数式 =SUBSTITUTE(ADDRESS(ROW(),COLUMN(),4),ROW(),"") には落とし穴があります。

たとえば11行目のA列で実行してみましょう。ADDRESS関数は「A11」を返します。ここでSUBSTITUTEが「11」を空文字に置換すると、結果は「A」で正しく動きます。

しかし問題が起きるケースがあります。行番号が変わるたびに置換対象の数値が変わるため、アルファベット部分に同じ数値が含まれる列では誤置換が起きる可能性があります。

行番号を「1」に固定しておけば、こうした心配は一切不要です。

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

行番号1なら、末尾の「1」を消すだけで確実にアルファベットだけが残りますよ。

#VALUE!エラーが出る場合

COLUMN関数に無効な参照を渡すと#VALUE!エラーになることがあります。引数を省略するか、有効なセル参照を指定しているか確認してみてください。

列番号に0以下を指定した場合

ADDRESS関数の列番号に0以下の値を渡すと#VALUE!エラーになります。列番号は1以上の整数を指定しましょう。

結果が数値になっている場合

数式バー上では正しく見えるのに、セルに数値が表示されている場合は、セルの書式設定が「数値」になっていることがあります。書式を「標準」か「文字列」に変更してください。

使用する3つの関数の役割まとめ

関数役割この数式での使い方
COLUMN関数列番号を取得セルの列を数値で返す
ADDRESS関数セル番地を文字列で生成行番号1＋列番号→「A1」形式の文字列
SUBSTITUTE関数文字列を置換セル番地から行番号「1」を除去

データの流れは 列番号（数値）→ セル番地（文字列）→ アルファベットのみ（文字列） です。

よくある質問（FAQ）

Q. XFD列（16,384列目）など、3文字の列名でも動きますか？

動きます。=SUBSTITUTE(ADDRESS(1,16384,4),1,"") を試すと「XFD」が返ります。Excelの最大列（XFD）まで正しく変換できますよ。

Q. Googleスプレッドシートでも同じ数式が使えますか？

使えます。COLUMN・ADDRESS・SUBSTITUTE関数はスプレッドシートにも同名で存在するため、まったく同じ数式が動きます。

Q. 逆変換（アルファベット → 列番号）はできますか？

できます。=COLUMN(INDIRECT(アルファベット & "1")) の形で、任意のアルファベットから列番号を取得できます。たとえば =COLUMN(INDIRECT("D1")) で「4」が返ります。INDIRECT関数と組み合わせるのがシンプルですよ。

Q. CELL関数では取得できないのですか？

=CELL("address", A1) で「$A$1」のような絶対参照の文字列は取れますが、列アルファベットだけを取り出すにはさらにSUBSTITUTEやLEFT関数で加工が必要です。SUBSTITUTE(ADDRESS(…))の数式のほうがシンプルに書けますよ。

まとめ

Excelで列番号をアルファベットに変換する数式は次のとおりです。

やりたいこと数式
自セルの列を取得=SUBSTITUTE(ADDRESS(1,COLUMN(),4),1,"")
列番号を指定して変換=SUBSTITUTE(ADDRESS(1,列番号,4),1,"")
別セルの列を取得=SUBSTITUTE(ADDRESS(1,COLUMN(セル),4),1,"")

ポイントは3つの関数の役割分担です。COLUMN関数で列番号を取得し、ADDRESS関数でセル番地の文字列を作り、SUBSTITUTE関数で行番号を除去します。行番号は「1」に固定しておくのが安全ですよ。

INDIRECT関数やMATCH関数との組み合わせでさらに柔軟な使い方もできます。列名の動的参照が必要になった場面で、ぜひ活用してみてください。

スポンサーリンク
タイトルとURLをコピーしました