【ワレコVBA】列番号･列アルファベット文字列の相互変換-高速版【EXCEL】

エクセルVBAでプログラミングをする場合に、列アルファベット文字列を列番号数字に変換したい場合がある。

例えば、

と聞かれても直ぐには分からない。

あるいはその逆に、列番号をアルファベット文字列に変換したい場合もある。

と聞かれても分からない。

そんな質問に直ぐに答えられる人はフォンノイマンかラマヌジャンくらいかもしれない。

 

この記事では、EXCEL作業中にそう言う列番号と列アルファベットの相互変換が必要になった時に使えるお勧めの関数を紹介したい。

VBAの関数としても使えるし、セル関数としても使える。

ネットを検索すると、この手の変換には各種の手法があるが、ワテが使っている高速に変換できる関数を紹介したい。

サンプルVBAダウンロード

本ページで使用したEXCELファイルをダウンロードしたい人はここからどうぞ。

zipファイルがダウンロードされるので解凍するとxlsmファイルが得られます。

 

では、本題に入ろう。

EXCELのR1C1形式とA1形式

EXCELではセルやレンジの範囲を指定する場合に、行と列を数字で表現するR1C1形式で書くとプログラムは書き易い。

Cells(10, 5) = "単一セル"
Range(Cells(10, 20), Cells(20, 30)).Value = "長方形領域"

こんな風に数字で行番号と列番号を指定出来るので、VBAのFor Nextループを使う場合には、このR1C1形式がよく使われる。

 

一方、セルやレンジの範囲を文字列で与える事も可能だ。

Range("A1").Value = "単一セル"
Range("a1:f5").Value = "長方形領域"
Cells("a2").Value = "単一セル" 　' これは出来ない。

この方式は A1形式と呼ばれる。

 

VBAでプログラムを書いていると、これらのR1C1形式とA1形式との間で相互変換したくなる場合が良くある。

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

さて、先ずは列番号からアルファベット文字列への変換関数だ。

例：　1　→　”A”

Option Explicit

'初期版（再帰呼び出し式）
Function ColNum2Let(ByVal colNum As Long, Optional colStr As String = "") As String
 If colNum = 0 Then
 ColNum2Let = colStr
 Else
 colStr = Chr(65 + (colNum - 1) Mod 26) & colStr
 colNum = (colNum - 1) \ 26
 ColNum2Let = ColNum2Let(colNum, colStr)
 End If
End Function 

VBAのエディターを開いて新しいモジュールを一個追加して、この関数をその中に貼り付けておくと良い。

そうすると、Sheet1のセルに

=ColNum2Let(1000)

などと入力すると、列番号1000に対応するアルファベットは

ALL

と表示される。

関数名 ColNum2Let はColumn Number To Letterと言う意味で付けている。

なお、この関数は再帰的に実行している。

二番目の引数 Optional colStr As String = “” は、再帰呼び出しの時に利用されるので、自分で呼び出す場合には二番目の引数は不要で、上記の通り最初の引数（colNum As Long）に1000などの列数字を整数で与えるだけで良い。

改良版（再帰呼び出しを使わないループ式）

上記関数でも問題無く動作するが、再帰呼び出しを使わずにループ式で計算するバージョンを作ってみた。

'改良版（ループ式）
Function ColNum2Let_Fast(ByVal colNum As Long) As String
    Dim result As String
    result = ""
    Do While colNum > 0
        result = Chr(65 + (colNum - 1) Mod 26) & result
        colNum = (colNum - 1) \ 26
    Loop
    ColNum2Let_Fast = result
End Function

再帰式はよりもループ式のほうが計算の流れが分かりやすいので、現状ではこの改良版がお勧めだ。

この改良版ループ式はColNum2Let_Fastと名付けているように、若干ではあるが初期版再帰式ColNum2Letよりも高速だが、大きな差はない。記事後半に計算時間の比較がある。

 

次は、逆変換。

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

こちらの関数は引数で列アルファベットを文字列で与える。

例：　”A”　→　1

Function ColLet2Num(ByVal colStr As String) As Long
 Dim colNum As Long
 Dim i As Long
 colNum = 0
 For i = 1 To Len(colStr)
 colNum = colNum * 26 + (Asc(UCase(Mid(colStr, i, 1))) - 64)
 Next
 ColLet2Num = colNum
End Function

関数名 ColLet2Num はColumn Letter To Numberと言う意味で付けている。

使い方としては、以下のように引数で列アルファベット文字列を与えれば良い。

 =ColLet2Num("AAA")

その実行結果は以下の通り。

703

となる。

 

これらの関数で利用出来る最大の列数

これらの二つの関数の動作を試してみたい人は、EXCELのセルを使って以下のように計算してみると良い。

	A	B	C
1	1000	= ColNum2Let(A1)	= ColLet2Num(B1)
2	1001	= ColNum2Let(A2)	= ColLet2Num(B2)
3	1002	= ColNum2Let(A3)	= ColLet2Num(B3)

 

その結果、以下のように

	A	B	C
1	1000	ALL	1000
2	1001	ALM	1001
3	1002	ALN	1002

Ｂ列にアルファベット文字列が表示されて、Ｃ列で元の数字に戻れば成功だ。

ワテが試した限りでは、

までは問題なく相互に変換出来る。

 

ちなみに、

EXCEL 2007以上では、扱える行列の範囲は以下の通り。

EXCEL 2003や、それ以前は

 

一方、ワテが使っている方法では 2,147,483,647列まで対応している。

21億列だ。

35億ブルゾンちえみには及ばないが、まあ21億列もあるEXCELが発売されたとしても、ワテ方式は利用出来る。

従って、この記事で紹介した二つの関数を使う限りは、現行のエクセルだけでなく、今後発売される全てのエクセルでも利用出来ると思って良いだろう。

完璧や！

エクセルワレコと呼んでくれ！　21億

一般的な方式の変換関数との速度比較

高速版と銘打っている以上、一般的な変換方式に比べてどれくらい速いのか比較してみた。

エクセルの列番号と列アルファベット文字との相互変換で、良く見かけるのは以下のような関数だ。

要するに .Address プロパティとか、 .Columnプロパティを使う方式。

以下の関数はワテがネットのサンプルを見て即席で作ったやつなので改良の余地はあるかも知れない。

列番号を列アルファベット文字に変換する一般的な手法

Function ColNum2Let_一般方式(lngColNum As Long) As String
 On Error GoTo Error:
 Dim strAddr As String
 strAddr = Cells(1, lngColNum).Address(False, False)
 ColNum2Let_一般方式 = Left(strAddr, Len(strAddr) - 1)
 Exit Function
Error:
 ColNum2Let_一般方式 = "計算出来ませんでした。"
End Function

列アルファベット文字を列番号に変換する一般的な手法

Function ColLet2Num_一般方式(colStr As String) As Long
 On Error GoTo Error:
 Dim colAddress As String
 colAddress = colStr + "1"
 ColLet2Num_一般方式 = Range(colAddress).Column
 Exit Function
Error:
 ColLet2Num_一般方式 = -999 ' "計算出来ませんでした。"
End Function

再帰式初期版、ループ式高速版、一般的手法版との計算時間比較結果

テスト環境

• 自作パソコン（Ryzen 7 8700G, 64GBメモリ, SSD 4TB, HDD3TB,4TBなど)
• Windows 11 Pro(64)
• OFFICE Professional 2024

以下のような計算時間比較の為の関数を作ってみた。

列数字 i　=>　列アルファベットへ変換　=>　列数字へ変換

を100×1000=10万回計算している。

Sub 計算時間比較()
    'ストップウオッチclassを使ってミリ秒まで計測する
    Dim sw As StopWatch
    Dim colLet As String
    Dim colNum As Long
    Dim n As Long, i As Long
    Dim t1 As Long, t2 As Long, t3 As Long
    Dim iEnd As Long
    iEnd = 1000 'パソコン性能に応じて1000, 10000など試す。

    ' --- 初期版（再帰式）---
    Set sw = New StopWatch
    sw.StartTimer
    For n = 1 To 100
        For i = 1 To iEnd
            colLet = ColNum2Let(i)
            colNum = ColLet2Num(colLet)
        Next i
    Next n
    t1 = sw.EndTimer

    ' --- 改良版（ループ式）---
    Set sw = New StopWatch
    sw.StartTimer
    For n = 1 To 100
        For i = 1 To iEnd
            colLet = ColNum2Let_Fast(i)
            colNum = ColLet2Num(colLet)
        Next i
    Next n
    t2 = sw.EndTimer

    ' --- 一般方式 ---
    Set sw = New StopWatch
    sw.StartTimer
    For n = 1 To 100
        For i = 1 To iEnd
            colLet = ColNum2Let_一般方式(i)
            colNum = ColLet2Num_一般方式(colLet)
        Next i
    Next n
    t3 = sw.EndTimer

    ' --- 結果表示 ---
    Dim msg As String
    msg = "【計算時間比較結果】" & vbCrLf & vbCrLf
    msg = msg & "初期版（再帰式）　：" & t1 & " ms" & vbCrLf
    msg = msg & "改良版（ループ式） ：" & t2 & " ms" & vbCrLf
    msg = msg & "一般方式　　　　　：" & t3 & " ms" & vbCrLf & vbCrLf
    If t1 > 0 And t2 > 0 Then
        msg = msg & "ループ式は再帰式の " & Format(t1 / t2, "0.00") & " 倍の速さ" & vbCrLf
        msg = msg & "一般方式は改良版の " & Format(t3 / t2, "0.0") & " 倍の時間がかかりました。"
    Else
        msg = msg & "速すぎて倍率計算できません。iEndを増やして再試行してください。"
    End If
    Debug.Print msg
    MsgBox msg
End Sub

計算結果（ループ回数 iEnd=1000）

	計算方式	計算時間	列番号 → 列アルファベット	列アルファベット → 列番号
1	初期版 (再帰式)	63 ms	ColNum2Let	ColLet2Num
2	改良版 (ループ式)	62 ms	ColNum2Let_Fast	ColLet2Num
3	一般方式	688 ms	ColNum2Let_一般方式	ColLet2Num_一般方式

表　3種類の計算方式の計算時間比較

上表のように、お勧めは2番目の ColNum2Let_Fast と ColLet2Num のペアを使う方式だ。

まとめ

EXCELに於いて、列番号の数字をアルファベット文字列に変換する方法とその逆変換の方法を紹介した。

EXCELのセル関数やVBA関数として利用出来る。

この記事で紹介した方法では、純粋に計算式で求める手法なので、高速に計算できる。

ワテの場合、これらの変換関数に関してはかなり長期間ネット検索して、各種のサンプルプログラムを実際に試してみて、そして最終的に採用したのが当記事で紹介した関数だ。

なので、列番号数字と列アルファベットの変換・逆変換に関しては当記事で紹介した関数は、世の中にある各種の方式と比べても最速レベルだと思っている。

質問募集

EXCEL VBAあるいはプログラミング一般に関して何か質問とか相談などありましたら、お気軽にお問い合わせください。

下のほうにあるコメント送信欄などをご利用下さい。

「こんな処理をVBAでやりたいんだが、どうやったら良いか分からない。」

など。

ワテの時間が有る限り、無料でお答えします。無料サンプルコードもお作りしますよ！

 

おかきを食う

全然話が変わるが、ワテはおかきが大好き。

そんなワテが各社のおかきを食べ比べて皆さんにお勧めするのがこの素焼茶屋だ。

これが物凄く美味い。

素焼きおかきは薄い塩味の素朴な味だ。

お米そのものの味をじっくりと味わえる。

この素焼茶屋はおかきの最高傑作だと思う。

熱いお茶を煎れて、おかきをバリバリ。

ああ、落ち着く。